schema-jet.sql

DROP TABLE IF EXISTS Course_areas,
Courses,
Offerings,
Rooms,
Sessions CASCADE;

CREATE TABLE Course_areas (
    area_name TEXT,

    PRIMARY KEY (area_name)
);

/*
 duration
 - refers to number of hours, (0, 7]
 - can only be conducted from 9am to 6pm
 - cannot be conducted during 12pm to 2pm */
CREATE TABLE Courses (
    course_id   SERIAL,
    area_name   TEXT    NOT NULL,
    title       TEXT    NOT NULL,
    description TEXT,
    duration    INTEGER NOT NULL
                CONSTRAINT duration_between_1_and_7
                CHECK (duration BETWEEN 1 AND 7),

    PRIMARY KEY (course_id),
    FOREIGN KEY (area_name) REFERENCES Course_areas
);

/* eid is the administrator id */
CREATE TABLE Offerings (
    course_id        INTEGER,
    offering_id      INTEGER,
    launch_date      DATE    NOT NULL,
    start_date       DATE,
    end_date         DATE,
    reg_deadline     DATE    NOT NULL
                     CONSTRAINT launch_date_before_reg_deadline
                          CHECK (launch_date <= reg_deadline),
    fees             INTEGER NOT NULL
                     CONSTRAINT non_negative_fees
                          CHECK (fees >= 0),
    seating_capacity INTEGER DEFAULT 0,
    target_num_reg   INTEGER NOT NULL
                     CONSTRAINT non_negative_target_num_reg
                          CHECK (target_num_reg >= 0),
    eid              INTEGER NOT NULL,

    PRIMARY KEY (course_id, offering_id),
    FOREIGN KEY (course_id) REFERENCES Courses,
    FOREIGN KEY (eid)       REFERENCES Administrators,

    CONSTRAINT offerings_cand_key
        UNIQUE (course_id, launch_date)
);

CREATE TABLE Rooms (
    rid              INTEGER,
    location         TEXT,
    seating_capacity INTEGER NOT NULL
                     CONSTRAINT non_negative_seating_capacity
                          CHECK (seating_capacity >= 0),

    PRIMARY KEY (rid)
);

/* Sessions can take lunch break, e.g. 4 hour session from 11am to 5pm */
/* eid is the instructor id */
CREATE TABLE Sessions (
    course_id    INTEGER,
    offering_id  INTEGER,
    session_id   INTEGER,
    session_date DATE    NOT NULL,
    start_time   TIME    NOT NULL
                 CONSTRAINT valid_start_time
                      CHECK (start_time BETWEEN '09:00' AND '11:00'
                         OR  start_time BETWEEN '14:00' AND '17:00'),
    end_time     TIME
                 CONSTRAINT valid_end_time
                    CHECK (end_time > start_time),
    eid          INTEGER,
    rid          INTEGER NOT NULL,

    PRIMARY KEY (course_id, offering_id, session_id),
    FOREIGN KEY (course_id, offering_id) REFERENCES Offerings
        ON DELETE CASCADE,
    FOREIGN KEY (eid)                    REFERENCES Instructors,
    FOREIGN KEY (rid)                    REFERENCES Rooms,

    CONSTRAINT sessions_cand_key
        UNIQUE (course_id, offering_id, session_date, start_time)
);