Skip to content

Database lab

Otto Visser requested to merge database_lab into master

In this merge request, the following is changed:

  • The CourseDescription table is moved to the CourseEdition table.
  • The tables dealing with Labs are altered to support both the number ta's per slot as per course.
  • The code is altered to use this new database schema

In order to update the database, the following code can be used:

ALTER TABLE CourseEdition ADD COLUMN description varchar(1023) CHARACTER SET utf8mb4 NULL;
UPDATE CourseEdition INNER JOIN CourseDescription ON id = ce_id SET CourseEdition.description = CourseDescription.description;
DROP TABLE CourseDescription;

DROP TABLE LabAssignment;
DROP TABLE LabTimeslot;
DROP TABLE LabCourse;

-- 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,
    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 (
    lab_id int NOT NULL,
    ce_id int NOT NULL,
    date date NOT NULL,
    slot_id int NOT NULL,
    num_ta int NOT NULL,
    CONSTRAINT LabSession_pk PRIMARY KEY (ce_id,date,slot_id)
);

-- 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);
Edited by Otto Visser

Merge request reports

Loading