Master

Keep dev up to date with master

src/main/resources/sql/createtables.sql

 -- Created by Vertabelo (http://vertabelo.com)
--- Last modification date: 2018-05-30 08:11:15.315
+-- Last modification date: 2018-05-30 13:26:29.23
 
 -- tables
 -- Table: Availability
 CREATE TABLE Availability (
-    timeslot_id int NOT NULL,
     netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
-    CONSTRAINT Availability_pk PRIMARY KEY (timeslot_id,netid)
+    date date NOT NULL,
+    slot_id int NOT NULL,
+    CONSTRAINT Availability_pk PRIMARY KEY (netid,date,slot_id)
 );
 
 -- Table: CourseAssignment
@@ -79,11 +80,12 @@ CREATE TABLE Lab (
 CREATE TABLE LabAssignment (
     netid varchar(100) CHARACTER SET utf8mb4 NOT NULL,
     schedule_id int NOT NULL,
-    timeslot_id int NOT NULL,
+    date date NOT NULL,
+    slot_id int 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,timeslot_id)
+    CONSTRAINT LabAssignment_pk PRIMARY KEY (netid,schedule_id,date,slot_id)
 );
 
 -- Table: LabCourse
@@ -95,10 +97,11 @@ CREATE TABLE LabCourse (
 
 -- Table: LabTimeslot
 CREATE TABLE LabTimeslot (
-    timeslot_id int NOT NULL,
     lab_id int NOT NULL,
+    date date NOT NULL,
+    slot_id int NOT NULL,
     num_ta int NOT NULL,
-    CONSTRAINT LabTimeslot_pk PRIMARY KEY (timeslot_id,lab_id)
+    CONSTRAINT LabTimeslot_pk PRIMARY KEY (lab_id,date,slot_id)
 );
 
 -- Table: Limits
@@ -194,12 +197,11 @@ CREATE TABLE Slot (
 
 -- Table: Timeslot
 CREATE TABLE Timeslot (
-    id int NOT NULL AUTO_INCREMENT,
+    date date NOT NULL,
+    slot_id int NOT NULL,
     year int NOT NULL,
     quarter int NOT NULL,
-    slot_id int NOT NULL,
-    date date NOT NULL,
-    CONSTRAINT Timeslot_pk PRIMARY KEY (id)
+    CONSTRAINT Timeslot_pk PRIMARY KEY (date,slot_id)
 );
 
 -- Table: User
@@ -226,8 +228,8 @@ CREATE TABLE User_Role (
 
 -- foreign keys
 -- Reference: Availability_Timeslot (table: Availability)
-ALTER TABLE Availability ADD CONSTRAINT Availability_Timeslot FOREIGN KEY Availability_Timeslot (timeslot_id)
-    REFERENCES Timeslot (id);
+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)
@@ -286,8 +288,8 @@ ALTER TABLE LabAssignment ADD CONSTRAINT LabAssignment_Schedule FOREIGN KEY LabA
     REFERENCES Schedule (id);
 
 -- Reference: LabAssignment_Timeslot (table: LabAssignment)
-ALTER TABLE LabAssignment ADD CONSTRAINT LabAssignment_Timeslot FOREIGN KEY LabAssignment_Timeslot (timeslot_id)
-    REFERENCES Timeslot (id);
+ALTER TABLE LabAssignment ADD CONSTRAINT LabAssignment_Timeslot FOREIGN KEY LabAssignment_Timeslot (date,slot_id)
+    REFERENCES Timeslot (date,slot_id);
 
 -- Reference: LabAssignment_User (table: LabAssignment)
 ALTER TABLE LabAssignment ADD CONSTRAINT LabAssignment_User FOREIGN KEY LabAssignment_User (netid)
@@ -306,8 +308,8 @@ ALTER TABLE LabTimeslot ADD CONSTRAINT LabTimeslot_Lab FOREIGN KEY LabTimeslot_L
     REFERENCES Lab (id);
 
 -- Reference: LabTimeslot_Timeslot (table: LabTimeslot)
-ALTER TABLE LabTimeslot ADD CONSTRAINT LabTimeslot_Timeslot FOREIGN KEY LabTimeslot_Timeslot (timeslot_id)
-    REFERENCES Timeslot (id);
+ALTER TABLE LabTimeslot ADD CONSTRAINT LabTimeslot_Timeslot FOREIGN KEY LabTimeslot_Timeslot (date,slot_id)
+    REFERENCES Timeslot (date,slot_id);
 
 -- Reference: Limits_User (table: Limits)
 ALTER TABLE Limits ADD CONSTRAINT Limits_User FOREIGN KEY Limits_User (netid)