-- MySQL Script generated by MySQL Workbench
-- Thu Jan 16 19:07:21 2020
-- Model: New Model    Version: 1.0
-- 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 Assessment
-- -----------------------------------------------------
-- Draft database to support a version of an AbOut-like webserivce for multiple departments. 

-- -----------------------------------------------------
-- Schema Assessment
--
-- Draft database to support a version of an AbOut-like webserivce for multiple departments. 
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `Assessment` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;
USE `Assessment` ;

-- -----------------------------------------------------
-- Table `Assessment`.`semesters`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`semesters` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`semesters` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` CHAR(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `semesterID_UNIQUE` (`id` ASC) VISIBLE,
  UNIQUE INDEX `semesterName_UNIQUE` (`name` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Assessment`.`intervals`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`intervals` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`intervals` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `begin` INT NOT NULL,
  `end` INT NOT NULL COMMENT 'Readly I want too allow nulls.',
  PRIMARY KEY (`id`),
  INDEX `begin_idx` (`begin` ASC) VISIBLE,
  CONSTRAINT `begin`
    FOREIGN KEY (`begin`)
    REFERENCES `Assessment`.`semesters` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Assessment`.`programs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`programs` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`programs` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `abbrev` CHAR(5) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `current_semester_id` INT NOT NULL,
  `interval_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `current_semester_id_idx` (`current_semester_id` ASC) VISIBLE,
  INDEX `interval_id_idx` (`interval_id` ASC) VISIBLE,
  CONSTRAINT `current_semester_id`
    FOREIGN KEY (`current_semester_id`)
    REFERENCES `Assessment`.`semesters` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `interval_id`
    FOREIGN KEY (`interval_id`)
    REFERENCES `Assessment`.`intervals` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Assessment`.`roles`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`roles` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`roles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` CHAR(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `adminRoleID_UNIQUE` (`id` ASC) VISIBLE,
  UNIQUE INDEX `roleName_UNIQUE` (`name` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Assessment`.`users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`users` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `cas` CHAR(25) NOT NULL,
  `first` CHAR(25) NOT NULL,
  `last` CHAR(25) NOT NULL,
  `interval_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `userID_UNIQUE` (`id` ASC) VISIBLE,
  INDEX `intervals_id_idx` (`interval_id` ASC) VISIBLE,
  CONSTRAINT `intervals_id`
    FOREIGN KEY (`interval_id`)
    REFERENCES `Assessment`.`intervals` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Assessment`.`permissions`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`permissions` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`permissions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `program_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `role_id` INT NOT NULL,
  `interval_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `program_id_idx` (`program_id` ASC) VISIBLE,
  INDEX `user_id_idx` (`user_id` ASC) VISIBLE,
  INDEX `role_id_idx` (`role_id` ASC) VISIBLE,
  INDEX `permissions_interval_id_idx` (`interval_id` ASC) VISIBLE,
  CONSTRAINT `program_id`
    FOREIGN KEY (`program_id`)
    REFERENCES `Assessment`.`programs` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `Assessment`.`users` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `role_id`
    FOREIGN KEY (`role_id`)
    REFERENCES `Assessment`.`roles` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `permissions_interval_id`
    FOREIGN KEY (`interval_id`)
    REFERENCES `Assessment`.`intervals` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Assessment`.`subjects`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`subjects` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`subjects` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `text` CHAR(5) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Assessment`.`courses`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`courses` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`courses` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `subject_id` INT NOT NULL,
  `number` CHAR(5) NOT NULL,
  `name` VARCHAR(70) NOT NULL,
  `interval_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `courses_interval_id_idx` (`interval_id` ASC) VISIBLE,
  INDEX `subject_id_idx` (`subject_id` ASC) VISIBLE,
  CONSTRAINT `courses_interval_id`
    FOREIGN KEY (`interval_id`)
    REFERENCES `Assessment`.`intervals` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `subject_id`
    FOREIGN KEY (`subject_id`)
    REFERENCES `Assessment`.`subjects` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Assessment`.`program_courses`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`program_courses` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`program_courses` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `program_id` INT NOT NULL,
  `course_id` INT NOT NULL,
  `interval_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `program_id_idx` (`program_id` ASC) VISIBLE,
  INDEX `course_id_idx` (`course_id` ASC) VISIBLE,
  INDEX `program_courses_interval_id_idx` (`interval_id` ASC) VISIBLE,
  CONSTRAINT `program_courses_program_id`
    FOREIGN KEY (`program_id`)
    REFERENCES `Assessment`.`programs` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `course_id`
    FOREIGN KEY (`course_id`)
    REFERENCES `Assessment`.`courses` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `program_courses_interval_id`
    FOREIGN KEY (`interval_id`)
    REFERENCES `Assessment`.`intervals` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Assessment`.`prefixes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`prefixes` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`prefixes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `text` CHAR(5) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `outcomePrefix_UNIQUE` (`text` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Assessment`.`outcomes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`outcomes` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`outcomes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `prefix_id` INT NOT NULL,
  `identifier` CHAR(5) NOT NULL,
  `text` VARCHAR(300) NOT NULL,
  `interval_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `outcomeID_UNIQUE` (`id` ASC) VISIBLE,
  INDEX `interval_id_idx` (`interval_id` ASC) VISIBLE,
  CONSTRAINT `prefix_id`
    FOREIGN KEY (`prefix_id`)
    REFERENCES `Assessment`.`prefixes` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `outcomes_interval_id`
    FOREIGN KEY (`interval_id`)
    REFERENCES `Assessment`.`intervals` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Assessment`.`program_outcomes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`program_outcomes` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`program_outcomes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `program_id` INT NOT NULL,
  `outcome_id` INT NOT NULL,
  `interval_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `programOutcomeID_UNIQUE` (`id` ASC) VISIBLE,
  INDEX `program_id_idx` (`program_id` ASC) VISIBLE,
  INDEX `outcome_id_idx` (`outcome_id` ASC) VISIBLE,
  INDEX `interval_id_idx` (`interval_id` ASC) VISIBLE,
  CONSTRAINT `program_outcomes_program_id`
    FOREIGN KEY (`program_id`)
    REFERENCES `Assessment`.`programs` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `outcome_id`
    FOREIGN KEY (`outcome_id`)
    REFERENCES `Assessment`.`outcomes` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `program_outcomes_interval_id`
    FOREIGN KEY (`interval_id`)
    REFERENCES `Assessment`.`intervals` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Assessment`.`measures`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Assessment`.`measures` ;

CREATE TABLE IF NOT EXISTS `Assessment`.`measures` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `program_outcomes_id` INT NOT NULL,
  `program_courses_id` INT NOT NULL,
  `interval_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `program_outcomes_id_idx` (`program_outcomes_id` ASC) VISIBLE,
  INDEX `program_courses_id_idx` (`program_courses_id` ASC) VISIBLE,
  INDEX `interval_id_idx` (`interval_id` ASC) VISIBLE,
  CONSTRAINT `program_outcomes_id`
    FOREIGN KEY (`program_outcomes_id`)
    REFERENCES `Assessment`.`program_outcomes` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `program_courses_id`
    FOREIGN KEY (`program_courses_id`)
    REFERENCES `Assessment`.`program_courses` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `measures_interval_id`
    FOREIGN KEY (`interval_id`)
    REFERENCES `Assessment`.`intervals` (`id`)
    ON DELETE RESTRICT
    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;
