DROP DATABASE IF EXIST `viu_itas`;

CREATE SCHEMA IF NOT EXISTS `viu_itas` DEFAULT CHARACTER SET utf8;
USE `viu_itas`;

DROP TABLE IF EXISTS `viu_itas`.`student_t`;
CREATE TABLE IF NOT EXISTS `student_t` (
  `student_id` INT NOT NULL AUTO_INCREMENT,
  `student_fn` VARCHAR(30) NOT NULL,
  `student_ln` VARCHAR(30) NOT NULL,
  `student_dob` DATE NOT NULL,
  `student_phone` VARCHAR(15) NULL,
  PRIMARY KEY (`student_id`),
  CONSTRAINT `student_id_unique` UNIQUE (`student_id`));
 ALTER TABLE `viu_itas`.`student_t` AUTO_INCREMENT = 500000000;

DROP TABLE IF EXISTS `viu_itas`.`instructor_t`;
CREATE TABLE IF NOT EXISTS `instructor_t` (
  `instructor_id` INT NOT NULL AUTO_INCREMENT,
  `instructor_fn` VARCHAR(30) NOT NULL,
  `instructor_ln` VARCHAR(30) NOT NULL,
  `instructor_onum` VARCHAR(7) NOT NULL,
  `instructor_ophone` VARCHAR(15) NULL,
  `instructor_hphone` VARCHAR(15) NULL,
  PRIMARY KEY (`instructor_id`),
  CONSTRAINT `instructor_id_UNIQUE` UNIQUE (`instructor_id`));
 ALTER TABLE `viu_itas`.`instructor_t` AUTO_INCREMENT = 300000000;

DROP TABLE IF EXISTS `viu_itas`.`course_t`;
CREATE TABLE IF NOT EXISTS `viu_itas`.`course_t` (
  `course_id` VARCHAR(15) NOT NULL,
  `instructor_id` INT NOT NULL,
  `course_name` VARCHAR(45) NULL,
  `classroom` VARCHAR(45) NULL,
  `course_credits` VARCHAR(45) NULL,
  PRIMARY KEY (`course_id`),
  CONSTRAINT `instructor_id`
    FOREIGN KEY (`instructor_id`)
    REFERENCES `instructor_t` (`instructor_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

DROP TABLE IF EXISTS `viu_itas`.`transcript_t`;
CREATE TABLE IF NOT EXISTS `viu_itas`.`transcript_t` (
  `student_id` INT NOT NULL,
  `course_id` VARCHAR(15) NOT NULL,
  `course_fg` FLOAT NULL,
  `course_lg` VARCHAR(2) NULL,
  CONSTRAINT
    PRIMARY KEY (`student_id`, `course_id`),
  CONSTRAINT
    FOREIGN KEY (`student_id`)
    REFERENCES `student_t`(`student_id`),
  CONSTRAINT
    FOREIGN KEY (`course_id`)
    REFERENCES `course_t`(`course_id`));

INSERT INTO `student_t` (
    student_id,
    student_fn,
    student_ln,
    student_dob,
    student_phone
    )
    VALUES (
        DEFAULT,
        'Jeandre',
        'Tony',
        '1999-06-06',
        '2505555555'
    );