CREATE TABLE IF NOT EXISTS Courses (
    course_number   INTEGER PRIMARY KEY,
    course_name     VARCHAR(255),
    course_subject  VARCHAR(255)
        -- the following check ensures that subject will be one of the following 3:
        CHECK (course_subject IN ("Optimization", "Probability", "Statistics")),
    semester        VARCHAR(255)

    -- TODO Add the following (if needed)
    -- 1. foreign key information
    -- 2. UNIQUE / NON-NULL constraints
);

CREATE TABLE IF NOT EXISTS Faculty (
    last_name       VARCHAR(255),
    first_name      VARCHAR(255),
    netID           VARCHAR(255),
    specialization  VARCHAR(255)
    CHECK (specialization IN ("Optimization", "Probability", "Statistics"))

    -- TODO Add the following (if needed):
    -- 1. primary / foreign key information
    -- 2. UNIQUE / NON-NULL constraints
);


CREATE TABLE IF NOT EXISTS Students (
    last_name       VARCHAR(255),
    first_name      VARCHAR(255),
    netID           VARCHAR(255),
    enrolled_sem    INTEGER         -- semester enrolled in

    -- TODO Add the following (if needed):
    -- 1. primary / foreign key information
    -- 2. UNIQUE / NON-NULL constraints
);

-- TODO: add all necessary junction tables for M:N relationships
