{"classroom": {"columns": [[0, "building", "varchar(15)", 0, null, 1], [1, "room_number", "varchar(7)", 0, null, 2], [2, "capacity", "numeric(4,0)", 0, null, 0]], "primary_keys": ["building", "room_number"], "foreign_keys": [], "unique_constraints": [[[0, 0, "building"], [1, 1, "room_number"]]], "check_constraints": []}, "department": {"columns": [[0, "dept_name", "varchar(20)", 0, null, 1], [1, "building", "varchar(15)", 0, null, 0], [2, "budget", "numeric(12,2)", 0, null, 0]], "primary_keys": ["dept_name"], "foreign_keys": [], "unique_constraints": [[[0, 0, "dept_name"]]], "check_constraints": ["budget\t\t        numeric(12,2) check (budget > 0),"]}, "course": {"columns": [[0, "course_id", "varchar(8)", 0, null, 1], [1, "title", "varchar(50)", 0, null, 0], [2, "dept_name", "varchar(20)", 0, null, 0], [3, "credits", "numeric(2,0)", 0, null, 0]], "primary_keys": ["course_id"], "foreign_keys": [[0, 0, "department", "dept_name", "dept_name", "NO ACTION", "SET NULL", "NONE"]], "unique_constraints": [[[0, 0, "course_id"]]], "check_constraints": ["credits\t\tnumeric(2,0) check (credits > 0),"]}, "instructor": {"columns": [[0, "ID", "varchar(5)", 0, null, 1], [1, "name", "varchar(20)", 1, null, 0], [2, "dept_name", "varchar(20)", 0, null, 0], [3, "salary", "numeric(8,2)", 0, null, 0]], "primary_keys": ["ID"], "foreign_keys": [[0, 0, "department", "dept_name", "dept_name", "NO ACTION", "SET NULL", "NONE"]], "unique_constraints": [[[0, 0, "ID"]]], "check_constraints": ["salary\t\t\tnumeric(8,2) check (salary > 29000),"]}, "section": {"columns": [[0, "course_id", "varchar(8)", 0, null, 1], [1, "sec_id", "varchar(8)", 0, null, 2], [2, "semester", "varchar(6)", 0, null, 3], [3, "year", "numeric(4,0)", 0, null, 4], [4, "building", "varchar(15)", 0, null, 0], [5, "room_number", "varchar(7)", 0, null, 0], [6, "time_slot_id", "varchar(4)", 0, null, 0]], "primary_keys": ["course_id", "sec_id", "semester", "year"], "foreign_keys": [[0, 0, "classroom", "building", "building", "NO ACTION", "SET NULL", "NONE"], [0, 1, "classroom", "room_number", "room_number", "NO ACTION", "SET NULL", "NONE"], [1, 0, "course", "course_id", "course_id", "NO ACTION", "CASCADE", "NONE"]], "unique_constraints": [[[0, 0, "course_id"], [1, 1, "sec_id"], [2, 2, "semester"], [3, 3, "year"]]], "check_constraints": ["check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),", "year\t\t\tnumeric(4,0) check (year > 1701 and year < 2100),"]}, "teaches": {"columns": [[0, "ID", "varchar(5)", 0, null, 1], [1, "course_id", "varchar(8)", 0, null, 2], [2, "sec_id", "varchar(8)", 0, null, 3], [3, "semester", "varchar(6)", 0, null, 4], [4, "year", "numeric(4,0)", 0, null, 5]], "primary_keys": ["ID", "course_id", "sec_id", "semester", "year"], "foreign_keys": [[0, 0, "instructor", "ID", "ID", "NO ACTION", "CASCADE", "NONE"], [1, 0, "section", "course_id", "course_id", "NO ACTION", "CASCADE", "NONE"], [1, 1, "section", "sec_id", "sec_id", "NO ACTION", "CASCADE", "NONE"], [1, 2, "section", "semester", "semester", "NO ACTION", "CASCADE", "NONE"], [1, 3, "section", "year", "year", "NO ACTION", "CASCADE", "NONE"]], "unique_constraints": [[[0, 0, "ID"], [1, 1, "course_id"], [2, 2, "sec_id"], [3, 3, "semester"], [4, 4, "year"]]], "check_constraints": []}, "student": {"columns": [[0, "ID", "varchar(5)", 0, null, 1], [1, "name", "varchar(20)", 1, null, 0], [2, "dept_name", "varchar(20)", 0, null, 0], [3, "tot_cred", "numeric(3,0)", 0, null, 0]], "primary_keys": ["ID"], "foreign_keys": [[0, 0, "department", "dept_name", "dept_name", "NO ACTION", "SET NULL", "NONE"]], "unique_constraints": [[[0, 0, "ID"]]], "check_constraints": ["tot_cred\t\tnumeric(3,0) check (tot_cred >= 0),"]}, "takes": {"columns": [[0, "ID", "varchar(5)", 0, null, 1], [1, "course_id", "varchar(8)", 0, null, 2], [2, "sec_id", "varchar(8)", 0, null, 3], [3, "semester", "varchar(6)", 0, null, 4], [4, "year", "numeric(4,0)", 0, null, 5], [5, "grade", "varchar(2)", 0, null, 0]], "primary_keys": ["ID", "course_id", "sec_id", "semester", "year"], "foreign_keys": [[0, 0, "student", "ID", "ID", "NO ACTION", "CASCADE", "NONE"], [1, 0, "section", "course_id", "course_id", "NO ACTION", "CASCADE", "NONE"], [1, 1, "section", "sec_id", "sec_id", "NO ACTION", "CASCADE", "NONE"], [1, 2, "section", "semester", "semester", "NO ACTION", "CASCADE", "NONE"], [1, 3, "section", "year", "year", "NO ACTION", "CASCADE", "NONE"]], "unique_constraints": [[[0, 0, "ID"], [1, 1, "course_id"], [2, 2, "sec_id"], [3, 3, "semester"], [4, 4, "year"]]], "check_constraints": []}, "advisor": {"columns": [[0, "s_ID", "varchar(5)", 0, null, 1], [1, "i_ID", "varchar(5)", 0, null, 0]], "primary_keys": ["s_ID"], "foreign_keys": [[0, 0, "student", "s_ID", "ID", "NO ACTION", "CASCADE", "NONE"], [1, 0, "instructor", "i_ID", "ID", "NO ACTION", "SET NULL", "NONE"]], "unique_constraints": [[[0, 0, "s_ID"]]], "check_constraints": []}, "time_slot": {"columns": [[0, "time_slot_id", "varchar(4)", 0, null, 1], [1, "day", "varchar(1)", 0, null, 2], [2, "start_hr", "numeric(2)", 0, null, 3], [3, "start_min", "numeric(2)", 0, null, 4], [4, "end_hr", "numeric(2)", 0, null, 0], [5, "end_min", "numeric(2)", 0, null, 0]], "primary_keys": ["time_slot_id", "day", "start_hr", "start_min"], "foreign_keys": [], "unique_constraints": [[[0, 0, "time_slot_id"], [1, 1, "day"], [2, 2, "start_hr"], [3, 3, "start_min"]]], "check_constraints": ["start_hr\t\tnumeric(2) check (start_hr >= 0 and start_hr < 24),", "start_min\t\tnumeric(2) check (start_min >= 0 and start_min < 60),", "end_hr\t\t\tnumeric(2) check (end_hr >= 0 and end_hr < 24),", "end_min\t\tnumeric(2) check (end_min >= 0 and end_min < 60),"]}, "prereq": {"columns": [[0, "course_id", "varchar(8)", 0, null, 1], [1, "prereq_id", "varchar(8)", 0, null, 2]], "primary_keys": ["course_id", "prereq_id"], "foreign_keys": [[0, 0, "course", "prereq_id", "course_id", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "course", "course_id", "course_id", "NO ACTION", "CASCADE", "NONE"]], "unique_constraints": [[[0, 0, "course_id"], [1, 1, "prereq_id"]]], "check_constraints": []}}
CREATE TABLE classroom (
    building varchar(15)   PRIMARY KEY,
    room_number varchar(7)   PRIMARY KEY,
    capacity numeric(4,0),
    PRIMARY KEY (building, room_number),
    UNIQUE (building, room_number)
);

CREATE TABLE department (
    dept_name varchar(20)   PRIMARY KEY,
    building varchar(15),
    budget numeric(12,2),
    UNIQUE (dept_name),
    budget		        numeric(12,2) check (budget > 0),
);

CREATE TABLE course (
    course_id varchar(8)   PRIMARY KEY,
    title varchar(50),
    dept_name varchar(20),
    credits numeric(2,0),
    UNIQUE (course_id),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name),
    credits		numeric(2,0) check (credits > 0),
);

CREATE TABLE instructor (
    ID varchar(5)   PRIMARY KEY,
    name varchar(20) NOT NULL,
    dept_name varchar(20),
    salary numeric(8,2),
    UNIQUE (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name),
    salary			numeric(8,2) check (salary > 29000),
);

CREATE TABLE section (
    course_id varchar(8)   PRIMARY KEY,
    sec_id varchar(8)   PRIMARY KEY,
    semester varchar(6)   PRIMARY KEY,
    year numeric(4,0)   PRIMARY KEY,
    building varchar(15),
    room_number varchar(7),
    time_slot_id varchar(4),
    PRIMARY KEY (course_id, sec_id, semester, year),
    UNIQUE (course_id, sec_id, semester, year),
    FOREIGN KEY (building) REFERENCES classroom(building),
    FOREIGN KEY (room_number) REFERENCES classroom(room_number),
    FOREIGN KEY (course_id) REFERENCES course(course_id),
    check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),,
    year			numeric(4,0) check (year > 1701 and year < 2100),
);

CREATE TABLE teaches (
    ID varchar(5)   PRIMARY KEY,
    course_id varchar(8)   PRIMARY KEY,
    sec_id varchar(8)   PRIMARY KEY,
    semester varchar(6)   PRIMARY KEY,
    year numeric(4,0)   PRIMARY KEY,
    PRIMARY KEY (ID, course_id, sec_id, semester, year),
    UNIQUE (ID, course_id, sec_id, semester, year),
    FOREIGN KEY (ID) REFERENCES instructor(ID),
    FOREIGN KEY (course_id) REFERENCES section(course_id),
    FOREIGN KEY (sec_id) REFERENCES section(sec_id),
    FOREIGN KEY (semester) REFERENCES section(semester),
    FOREIGN KEY (year) REFERENCES section(year)
);

CREATE TABLE student (
    ID varchar(5)   PRIMARY KEY,
    name varchar(20) NOT NULL,
    dept_name varchar(20),
    tot_cred numeric(3,0),
    UNIQUE (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name),
    tot_cred		numeric(3,0) check (tot_cred >= 0),
);

CREATE TABLE takes (
    ID varchar(5)   PRIMARY KEY,
    course_id varchar(8)   PRIMARY KEY,
    sec_id varchar(8)   PRIMARY KEY,
    semester varchar(6)   PRIMARY KEY,
    year numeric(4,0)   PRIMARY KEY,
    grade varchar(2),
    PRIMARY KEY (ID, course_id, sec_id, semester, year),
    UNIQUE (ID, course_id, sec_id, semester, year),
    FOREIGN KEY (ID) REFERENCES student(ID),
    FOREIGN KEY (course_id) REFERENCES section(course_id),
    FOREIGN KEY (sec_id) REFERENCES section(sec_id),
    FOREIGN KEY (semester) REFERENCES section(semester),
    FOREIGN KEY (year) REFERENCES section(year)
);

CREATE TABLE advisor (
    s_ID varchar(5)   PRIMARY KEY,
    i_ID varchar(5),
    UNIQUE (s_ID),
    FOREIGN KEY (s_ID) REFERENCES student(ID),
    FOREIGN KEY (i_ID) REFERENCES instructor(ID)
);

CREATE TABLE time_slot (
    time_slot_id varchar(4)   PRIMARY KEY,
    day varchar(1)   PRIMARY KEY,
    start_hr numeric(2)   PRIMARY KEY,
    start_min numeric(2)   PRIMARY KEY,
    end_hr numeric(2),
    end_min numeric(2),
    PRIMARY KEY (time_slot_id, day, start_hr, start_min),
    UNIQUE (time_slot_id, day, start_hr, start_min),
    start_hr		numeric(2) check (start_hr >= 0 and start_hr < 24),,
    start_min		numeric(2) check (start_min >= 0 and start_min < 60),,
    end_hr			numeric(2) check (end_hr >= 0 and end_hr < 24),,
    end_min		numeric(2) check (end_min >= 0 and end_min < 60),
);

CREATE TABLE prereq (
    course_id varchar(8)   PRIMARY KEY,
    prereq_id varchar(8)   PRIMARY KEY,
    PRIMARY KEY (course_id, prereq_id),
    UNIQUE (course_id, prereq_id),
    FOREIGN KEY (prereq_id) REFERENCES course(course_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);