DROP SCHEMA IF EXISTS `teach_manager` ;

CREATE SCHEMA IF NOT EXISTS `teach_manager` ;

USE `teach_manager` ;

-- -----------------------------------------------------

-- Table `teach_manager`.`department`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `teach_manager`.`department` ;

CREATE  TABLE IF NOT EXISTS `teach_manager`.`department` (

`department` VARCHAR(45) NOT NULL ,

PRIMARY KEY (`department`) )

ENGINE = InnoDB;

CREATE UNIQUE INDEX `department_UNIQUE` ON `teach_manager`.`department` (`department` ASC) ;

-- -----------------------------------------------------

-- Table `teach_manager`.`student`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `teach_manager`.`student` ;

CREATE  TABLE IF NOT EXISTS `teach_manager`.`student` (

`id` INT NOT NULL AUTO_INCREMENT ,

`name` VARCHAR(45) NOT NULL ,

`student_department` VARCHAR(45) NOT NULL ,

PRIMARY KEY (`id`) ,

CONSTRAINT `department`

FOREIGN KEY (`student_department` )

REFERENCES `teach_manager`.`department` (`department` )

ON DELETE CASCADE

ON UPDATE RESTRICT)

ENGINE = InnoDB;

CREATE UNIQUE INDEX `StudentID_UNIQUE` ON `teach_manager`.`student` (`id` ASC) ;

CREATE INDEX `department_idx` ON `teach_manager`.`student` (`student_department` ASC) ;

-- -----------------------------------------------------

-- Table `teach_manager`.`teacher`

-- -----------------------------------------------------

DROP TABLE IF EXISTS `teach_manager`.`teacher` ;

CREATE  TABLE IF NOT EXISTS `teach_manager`.`teacher` (

`id` INT NOT NULL AUTO_INCREMENT ,

`name` VARCHAR(45) NOT NULL ,

`teacher_department` VARCHAR(45) NULL ,

PRIMARY KEY (`id`) ,

CONSTRAINT `department`

FOREIGN KEY (`teacher_department` )

REFERENCES `teach_manager`.`department` (`department` )

ON DELETE CASCADE

ON UPDATE RESTRICT)

ENGINE = InnoDB;

CREATE UNIQUE INDEX `TeacherID_UNIQUE` ON `teach_manager`.`teacher` (`id` ASC) ;

CREATE INDEX `department_idx` ON `teach_manager`.`teacher` (`teacher_department` ASC) ;