createtables.sql

-- Created by Vertabelo (http://vertabelo.com)
-- Last modification date: 2018-06-04 15:45:06.88

-- tables
-- Table: Availability
CREATE TABLE Availability (
    netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    date date NOT NULL,
    slot_id int NOT NULL,
    CONSTRAINT Availability_pk PRIMARY KEY (netid,date,slot_id)
);

-- Table: CourseAssignment
CREATE TABLE CourseAssignment (
    netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    ce_id int NOT NULL,
    schedule_id int NOT NULL,
    last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT CourseAssignment_pk PRIMARY KEY (netid,ce_id,schedule_id)
);

-- Table: CourseEdition
CREATE TABLE CourseEdition (
    id int NOT NULL AUTO_INCREMENT,
    owner varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    year int NOT NULL,
    quarter int NOT NULL,
    course_code varchar(50) CHARACTER SET utf8mb4 NOT NULL,
    name varchar(255) CHARACTER SET utf8mb4 NOT NULL,
    teacher varchar(255) CHARACTER SET utf8mb4 NOT NULL,
    study_year int NOT NULL,
    description varchar(1023) CHARACTER SET utf8mb4 NULL,
    deleted bool NOT NULL DEFAULT false,
    last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX CourseEdition_course_code (course_code,year,quarter),
    CONSTRAINT CourseEdition_pk PRIMARY KEY (id)
);

-- Table: CoursePreference
CREATE TABLE CoursePreference (
    netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    ce_id int NOT NULL,
    p_rank int NOT NULL DEFAULT 3,
    last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT CoursePreference_pk PRIMARY KEY (netid,ce_id)
);

-- Table: Feedback
CREATE TABLE Feedback (
    subject varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    ce_id int NOT NULL,
    author varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    feedback varchar(1023) CHARACTER SET utf8mb4 NOT NULL,
    last_modifed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT Feedback_pk PRIMARY KEY (subject,ce_id)
);

-- Table: Lab
CREATE TABLE Lab (
    id int NOT NULL AUTO_INCREMENT,
    deleted bool NOT NULL DEFAULT false,
    last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT Lab_pk PRIMARY KEY (id)
);

-- Table: LabAssignment
CREATE TABLE LabAssignment (
    netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    schedule_id int NOT NULL,
    ce_id int NOT NULL,
    slot_id int NOT NULL,
    date date NOT NULL,
    lab_id int NOT NULL,
    last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT LabAssignment_pk PRIMARY KEY (netid,schedule_id,ce_id,slot_id,date)
);

-- Table: LabSession
CREATE TABLE LabSession (
    ce_id int NOT NULL,
    date date NOT NULL,
    slot_id int NOT NULL,
    lab_id int NOT NULL,
    num_ta int NOT NULL,
    CONSTRAINT LabSession_pk PRIMARY KEY (ce_id,date,slot_id)
);

-- Table: Limits
CREATE TABLE Limits (
    netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    year int NOT NULL,
    quarter int NOT NULL,
    hour_limit int NOT NULL,
    course_limit int NOT NULL,
    last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT Limits_pk PRIMARY KEY (netid,year,quarter)
);

-- Table: Manages
CREATE TABLE Manages (
    netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    ce_id int NOT NULL,
    last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT Manages_pk PRIMARY KEY (netid,ce_id)
);

-- Table: Note
CREATE TABLE Note (
    netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    year int NOT NULL,
    quarter int NOT NULL,
    content varchar(1023) CHARACTER SET utf8mb4 NOT NULL,
    last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT Note_pk PRIMARY KEY (netid,year,quarter)
);

-- Table: PersonalPreference
CREATE TABLE PersonalPreference (
    netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    ce_id int NOT NULL,
    last_year bool NOT NULL,
    v_rank int NULL,
    last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT PersonalPreference_pk PRIMARY KEY (netid,ce_id)
);

-- Table: Prerequisites
CREATE TABLE Prerequisites (
    netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    ta_training bool NOT NULL DEFAULT false,
    english_test bool NOT NULL DEFAULT false,
    is_accepted bool NOT NULL DEFAULT false,
    payscale int NULL,
    last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT Prerequisites_pk PRIMARY KEY (netid)
);

-- Table: Quarter
CREATE TABLE Quarter (
    year int NOT NULL,
    quarter int NOT NULL,
    interest boolean NOT NULL DEFAULT false,
    availability boolean NOT NULL DEFAULT false,
    CONSTRAINT Quarter_pk PRIMARY KEY (year,quarter)
);

-- Table: Role
CREATE TABLE Role (
    id int NOT NULL AUTO_INCREMENT,
    name varchar(14) CHARACTER SET utf8mb4 NOT NULL,
    CONSTRAINT Role_pk PRIMARY KEY (id)
);

-- Table: Schedule
CREATE TABLE Schedule (
    id int NOT NULL AUTO_INCREMENT,
    year int NOT NULL,
    quarter int NOT NULL,
    final bool NOT NULL,
    deleted bool NOT NULL DEFAULT false,
    last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT Schedule_pk PRIMARY KEY (id)
);

-- Table: Slot
CREATE TABLE Slot (
    id int NOT NULL,
    start_time time NOT NULL,
    end_time time NOT NULL,
    CONSTRAINT Slot_pk PRIMARY KEY (id)
);

-- Table: Timeslot
CREATE TABLE Timeslot (
    date date NOT NULL,
    slot_id int NOT NULL,
    year int NOT NULL,
    quarter int NOT NULL,
    CONSTRAINT Timeslot_pk PRIMARY KEY (date,slot_id)
);

-- Table: User
CREATE TABLE User (
    netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    display_name varchar(512) CHARACTER SET utf8mb4 NOT NULL,
    first_name varchar(255) CHARACTER SET utf8mb4 NOT NULL,
    last_name varchar(255) CHARACTER SET utf8mb4 NOT NULL,
    email varchar(255) CHARACTER SET utf8mb4 NOT NULL,
    tshirt_gender char(1) NULL,
    tshirt_size varchar(10) NULL,
    gdpr_accepted bool NOT NULL DEFAULT false,
    gdpr_time timestamp NULL,
    last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT User_pk PRIMARY KEY (netid)
);

-- Table: User_Role
CREATE TABLE User_Role (
    netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
    role_id int NOT NULL,
    CONSTRAINT User_Role_pk PRIMARY KEY (netid,role_id)
);

-- foreign keys
-- Reference: Availability_Timeslot (table: Availability)
ALTER TABLE Availability ADD CONSTRAINT Availability_Timeslot FOREIGN KEY Availability_Timeslot (date,slot_id)
    REFERENCES Timeslot (date,slot_id);

-- Reference: Availability_User (table: Availability)
ALTER TABLE Availability ADD CONSTRAINT Availability_User FOREIGN KEY Availability_User (netid)
    REFERENCES User (netid);

-- Reference: CourseAssignment_CourseEdition (table: CourseAssignment)
ALTER TABLE CourseAssignment ADD CONSTRAINT CourseAssignment_CourseEdition FOREIGN KEY CourseAssignment_CourseEdition (ce_id)
    REFERENCES CourseEdition (id);

-- Reference: CourseAssignment_Schedule (table: CourseAssignment)
ALTER TABLE CourseAssignment ADD CONSTRAINT CourseAssignment_Schedule FOREIGN KEY CourseAssignment_Schedule (schedule_id)
    REFERENCES Schedule (id);

-- Reference: CourseAssignment_User (table: CourseAssignment)
ALTER TABLE CourseAssignment ADD CONSTRAINT CourseAssignment_User FOREIGN KEY CourseAssignment_User (netid)
    REFERENCES User (netid);

-- Reference: CourseEdition_Quarter (table: CourseEdition)
ALTER TABLE CourseEdition ADD CONSTRAINT CourseEdition_Quarter FOREIGN KEY CourseEdition_Quarter (year,quarter)
    REFERENCES Quarter (year,quarter);

-- Reference: CourseEdition_User (table: CourseEdition)
ALTER TABLE CourseEdition ADD CONSTRAINT CourseEdition_User FOREIGN KEY CourseEdition_User (owner)
    REFERENCES User (netid);

-- Reference: CoursePreference_CourseEdition (table: CoursePreference)
ALTER TABLE CoursePreference ADD CONSTRAINT CoursePreference_CourseEdition FOREIGN KEY CoursePreference_CourseEdition (ce_id)
    REFERENCES CourseEdition (id);

-- Reference: CoursePreference_User (table: CoursePreference)
ALTER TABLE CoursePreference ADD CONSTRAINT CoursePreference_User FOREIGN KEY CoursePreference_User (netid)
    REFERENCES User (netid);

-- Reference: Feedback_CourseEdition (table: Feedback)
ALTER TABLE Feedback ADD CONSTRAINT Feedback_CourseEdition FOREIGN KEY Feedback_CourseEdition (ce_id)
    REFERENCES CourseEdition (id);

-- Reference: Feedback_UserAuthor (table: Feedback)
ALTER TABLE Feedback ADD CONSTRAINT Feedback_UserAuthor FOREIGN KEY Feedback_UserAuthor (author)
    REFERENCES User (netid);

-- Reference: Feedback_UserSubject (table: Feedback)
ALTER TABLE Feedback ADD CONSTRAINT Feedback_UserSubject FOREIGN KEY Feedback_UserSubject (subject)
    REFERENCES User (netid);

-- Reference: LabAssignment_LabSession (table: LabAssignment)
ALTER TABLE LabAssignment ADD CONSTRAINT LabAssignment_LabSession FOREIGN KEY LabAssignment_LabSession (ce_id,date,slot_id)
    REFERENCES LabSession (ce_id,date,slot_id);

-- Reference: LabAssignment_Schedule (table: LabAssignment)
ALTER TABLE LabAssignment ADD CONSTRAINT LabAssignment_Schedule FOREIGN KEY LabAssignment_Schedule (schedule_id)
    REFERENCES Schedule (id);

-- Reference: LabAssignment_User (table: LabAssignment)
ALTER TABLE LabAssignment ADD CONSTRAINT LabAssignment_User FOREIGN KEY LabAssignment_User (netid)
    REFERENCES User (netid);

-- Reference: LabTimeslot_CourseEdition (table: LabSession)
ALTER TABLE LabSession ADD CONSTRAINT LabTimeslot_CourseEdition FOREIGN KEY LabTimeslot_CourseEdition (ce_id)
    REFERENCES CourseEdition (id);

-- Reference: LabTimeslot_Lab (table: LabSession)
ALTER TABLE LabSession ADD CONSTRAINT LabTimeslot_Lab FOREIGN KEY LabTimeslot_Lab (lab_id)
    REFERENCES Lab (id);

-- Reference: LabTimeslot_Timeslot (table: LabSession)
ALTER TABLE LabSession ADD CONSTRAINT LabTimeslot_Timeslot FOREIGN KEY LabTimeslot_Timeslot (date,slot_id)
    REFERENCES Timeslot (date,slot_id);

-- Reference: Limits_Quarter (table: Limits)
ALTER TABLE Limits ADD CONSTRAINT Limits_Quarter FOREIGN KEY Limits_Quarter (year,quarter)
    REFERENCES Quarter (year,quarter);

-- Reference: Limits_User (table: Limits)
ALTER TABLE Limits ADD CONSTRAINT Limits_User FOREIGN KEY Limits_User (netid)
    REFERENCES User (netid);

-- Reference: Manages_CourseEdition (table: Manages)
ALTER TABLE Manages ADD CONSTRAINT Manages_CourseEdition FOREIGN KEY Manages_CourseEdition (ce_id)
    REFERENCES CourseEdition (id);

-- Reference: Manages_User (table: Manages)
ALTER TABLE Manages ADD CONSTRAINT Manages_User FOREIGN KEY Manages_User (netid)
    REFERENCES User (netid);

-- Reference: Note_Quarter (table: Note)
ALTER TABLE Note ADD CONSTRAINT Note_Quarter FOREIGN KEY Note_Quarter (year,quarter)
    REFERENCES Quarter (year,quarter);

-- Reference: Note_User (table: Note)
ALTER TABLE Note ADD CONSTRAINT Note_User FOREIGN KEY Note_User (netid)
    REFERENCES User (netid);

-- Reference: PersonalPreference_CourseEdition (table: PersonalPreference)
ALTER TABLE PersonalPreference ADD CONSTRAINT PersonalPreference_CourseEdition FOREIGN KEY PersonalPreference_CourseEdition (ce_id)
    REFERENCES CourseEdition (id);

-- Reference: PersonalPreference_User (table: PersonalPreference)
ALTER TABLE PersonalPreference ADD CONSTRAINT PersonalPreference_User FOREIGN KEY PersonalPreference_User (netid)
    REFERENCES User (netid);

-- Reference: Prerequisites_User (table: Prerequisites)
ALTER TABLE Prerequisites ADD CONSTRAINT Prerequisites_User FOREIGN KEY Prerequisites_User (netid)
    REFERENCES User (netid);

-- Reference: Schedule_Quarter (table: Schedule)
ALTER TABLE Schedule ADD CONSTRAINT Schedule_Quarter FOREIGN KEY Schedule_Quarter (year,quarter)
    REFERENCES Quarter (year,quarter);

-- Reference: Timeslot_Quarter (table: Timeslot)
ALTER TABLE Timeslot ADD CONSTRAINT Timeslot_Quarter FOREIGN KEY Timeslot_Quarter (year,quarter)
    REFERENCES Quarter (year,quarter);

-- Reference: Timeslot_Slot (table: Timeslot)
ALTER TABLE Timeslot ADD CONSTRAINT Timeslot_Slot FOREIGN KEY Timeslot_Slot (slot_id)
    REFERENCES Slot (id);

-- Reference: User_Role_Role (table: User_Role)
ALTER TABLE User_Role ADD CONSTRAINT User_Role_Role FOREIGN KEY User_Role_Role (role_id)
    REFERENCES Role (id);

-- Reference: User_Role_User (table: User_Role)
ALTER TABLE User_Role ADD CONSTRAINT User_Role_User FOREIGN KEY User_Role_User (netid)
    REFERENCES User (netid);

-- End of file.