-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema test_university
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema test_university
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `test_university` DEFAULT CHARACTER SET utf8 ;
USE `test_university` ;

-- -----------------------------------------------------
-- Table `User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `User` ;

CREATE TABLE IF NOT EXISTS `User` (
  `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(16) NOT NULL,
  `email` VARCHAR(255) NULL,
  `password` VARCHAR(32) NOT NULL,
  `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) VISIBLE,
  PRIMARY KEY (`user_id`),
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) VISIBLE,
  UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE);


-- -----------------------------------------------------
-- Table `Person`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Person` ;

CREATE TABLE IF NOT EXISTS `Person` (
  `Person_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `FirstName` VARCHAR(45) NULL,
  `LastName` VARCHAR(45) NULL,
  `user_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`Person_ID`, `user_id`),
  UNIQUE INDEX `Person_ID_UNIQUE` (`Person_ID` ASC) VISIBLE,
  INDEX `fk_Person_user1_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `fk_Person_user1`
    FOREIGN KEY (`user_id`)
    REFERENCES `User` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Employee`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Employee` ;

CREATE TABLE IF NOT EXISTS `Employee` (
  `Person_ID` INT UNSIGNED NOT NULL,
  `RetirementType` INT NULL,
  PRIMARY KEY (`Person_ID`),
  INDEX `fk_Employee_Person1_idx` (`Person_ID` ASC) VISIBLE,
  CONSTRAINT `fk_Employee_Person1`
    FOREIGN KEY (`Person_ID`)
    REFERENCES `Person` (`Person_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Advisor`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Advisor` ;

CREATE TABLE IF NOT EXISTS `Advisor` (
  `Person_ID` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`Person_ID`),
  INDEX `fk_Advisor_Employee1_idx` (`Person_ID` ASC) VISIBLE,
  CONSTRAINT `fk_Advisor_Employee1`
    FOREIGN KEY (`Person_ID`)
    REFERENCES `Employee` (`Person_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Student`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Student` ;

CREATE TABLE IF NOT EXISTS `Student` (
  `Student_ID` INT UNSIGNED NOT NULL,
  `Advisor_ID` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`Student_ID`),
  INDEX `fk_Student_Advisor1_idx` (`Advisor_ID` ASC) VISIBLE,
  CONSTRAINT `fk_Student_Person1`
    FOREIGN KEY (`Student_ID`)
    REFERENCES `Person` (`Person_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Student_Advisor1`
    FOREIGN KEY (`Advisor_ID`)
    REFERENCES `Advisor` (`Person_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Major`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Major` ;

CREATE TABLE IF NOT EXISTS `Major` (
  `Major_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Major_ID`),
  UNIQUE INDEX `ID_UNIQUE` (`Major_ID` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Student_has_Major`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Student_has_Major` ;

CREATE TABLE IF NOT EXISTS `Student_has_Major` (
  `Student_ID` INT UNSIGNED NOT NULL,
  `Major_ID` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`Student_ID`, `Major_ID`),
  INDEX `fk_Student_has_Major_Major1_idx` (`Major_ID` ASC) VISIBLE,
  INDEX `fk_Student_has_Major_Student1_idx` (`Student_ID` ASC) VISIBLE,
  CONSTRAINT `fk_Student_has_Major_Student1`
    FOREIGN KEY (`Student_ID`)
    REFERENCES `Student` (`Student_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Student_has_Major_Major1`
    FOREIGN KEY (`Major_ID`)
    REFERENCES `Major` (`Major_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Person_has_telephone`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Person_has_telephone` ;

CREATE TABLE IF NOT EXISTS `Person_has_telephone` (
  `Person_ID` INT UNSIGNED NOT NULL,
  `telephone` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`Person_ID`, `telephone`),
  CONSTRAINT `fk_Person_has_telephone_Person1`
    FOREIGN KEY (`Person_ID`)
    REFERENCES `Person` (`Person_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
