Courses Management System

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='TRADITIONAL';

DROP SCHEMA IF EXISTS `uni` ;
CREATE SCHEMA IF NOT EXISTS `uni` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `uni` ;

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

CREATE  TABLE IF NOT EXISTS `uni`.`Person` (
  `PersonID` INT NOT NULL ,
  `firstName` VARCHAR(255) NOT NULL ,
  `middleName` VARCHAR(255) NULL ,
  `lastName` VARCHAR(255) NOT NULL ,
  `DOB` DATE NOT NULL ,
  `address` VARCHAR(255) NOT NULL ,
  `email` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`PersonID`) )
ENGINE = InnoDB;


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

CREATE  TABLE IF NOT EXISTS `uni`.`Student` (
  `StudentID` INT NULL AUTO_INCREMENT ,
  `PersonID` INT NULL ,
  `matriculationYear` YEAR NULL ,
  INDEX `personID` (`PersonID` ASC) ,
  PRIMARY KEY (`StudentID`) ,
  CONSTRAINT `personID`
    FOREIGN KEY (`PersonID` )
    REFERENCES `uni`.`Person` (`PersonID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `uni`.`Course`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `uni`.`Course` ;

CREATE  TABLE IF NOT EXISTS `uni`.`Course` (
  `CourseID` INT(8) ZEROFILL NULL AUTO_INCREMENT ,
  `title` VARCHAR(255) NOT NULL ,
  `semester` VARCHAR(15) NOT NULL ,
  `startDate` DATE NOT NULL ,
  `endDate` DATE NOT NULL ,
  `examDate` DATETIME NOT NULL ,
  `semesterHours` INT NOT NULL ,
  PRIMARY KEY (`CourseID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `uni`.`Lecturer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `uni`.`Lecturer` ;

CREATE  TABLE IF NOT EXISTS `uni`.`Lecturer` (
  `LecturerID` INT(8) ZEROFILL NULL AUTO_INCREMENT ,
  `PersonID` INT NOT NULL ,
  PRIMARY KEY (`LecturerID`) ,
  INDEX `fk_personID` (`PersonID` ASC) ,
  CONSTRAINT `fk_personID`
    FOREIGN KEY (`PersonID` )
    REFERENCES `uni`.`Person` (`PersonID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `uni`.`Department`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `uni`.`Department` ;

CREATE  TABLE IF NOT EXISTS `uni`.`Department` (
  `DepartmentID` INT(3) ZEROFILL NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `headOfDepartment` INT(8) ZEROFILL NULL ,
  PRIMARY KEY (`DepartmentID`) ,
  INDEX `fk_HOD` (`headOfDepartment` ASC) ,
  CONSTRAINT `fk_HOD`
    FOREIGN KEY (`headOfDepartment` )
    REFERENCES `uni`.`Lecturer` (`LecturerID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `uni`.`CoursesLecturers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `uni`.`CoursesLecturers` ;

CREATE  TABLE IF NOT EXISTS `uni`.`CoursesLecturers` (
  `CourseLecturerID` INT NULL ,
  `LecturerID` INT(8) ZEROFILL NULL ,
  `CourseID` INT(8) ZEROFILL NULL ,
  PRIMARY KEY (`CourseLecturerID`) ,
  INDEX `fk_Lecturer` (`LecturerID` ASC) ,
  INDEX `fk_Course` (`CourseID` ASC) ,
  CONSTRAINT `fk_Lecturer`
    FOREIGN KEY (`LecturerID` )
    REFERENCES `uni`.`Lecturer` (`LecturerID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Course`
    FOREIGN KEY (`CourseID` )
    REFERENCES `uni`.`Course` (`CourseID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `uni`.`StudentsCourses`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `uni`.`StudentsCourses` ;

CREATE  TABLE IF NOT EXISTS `uni`.`StudentsCourses` (
  `StudentCourseID` INT NOT NULL AUTO_INCREMENT ,
  `StudentID` INT NULL ,
  `CourseID` INT(8) UNSIGNED ZEROFILL NOT NULL ,
  `score` INT NULL ,
  PRIMARY KEY (`StudentCourseID`) ,
  INDEX `fk_courseID` (`CourseID` ASC) ,
  INDEX `fk_StudentID` (`StudentID` ASC) ,
  CONSTRAINT `fk_courseID`
    FOREIGN KEY (`CourseID` )
    REFERENCES `uni`.`Course` (`CourseID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_StudentID`
    FOREIGN KEY (`StudentID` )
    REFERENCES `uni`.`Student` (`StudentID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `uni`.`PersonsDepartments`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `uni`.`PersonsDepartments` ;

CREATE  TABLE IF NOT EXISTS `uni`.`PersonsDepartments` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `PersonID` INT NOT NULL ,
  `DepartmentID` INT(3) ZEROFILL NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX `fk_Person` (`PersonID` ASC) ,
  INDEX `fk_Department` (`DepartmentID` ASC) ,
  CONSTRAINT `fk_Person`
    FOREIGN KEY (`PersonID` )
    REFERENCES `uni`.`Person` (`PersonID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Department`
    FOREIGN KEY (`DepartmentID` )
    REFERENCES `uni`.`Department` (`DepartmentID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


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