BRszw1Hi

1DROP DATABASE IF EXISTS Sample_University;
2CREATE DATABASE Sample_University;
3
4CREATE TABLE classroom (
5    building varchar(50),
6    room_number int,
7    capacity int DEFAULT 0,
8    PRIMARY KEY (building, room_number)
9);
10
11CREATE TABLE department (
12    dept_name varchar(50),
13    building varchar(50),
14    budget int NOT NULL,
15    PRIMARY KEY (dept_name),
16    FOREIGN KEY (building) REFERENCES classroom(building) ON DELETE CASCADE
17);
18
19CREATE TABLE course (
20    course_id int,
21    title varchar(50),
22    dept_name varchar(50),
23    credits int,
24    PRIMARY KEY (course_id),
25    FOREIGN KEY (dept_name) REFERENCES department(dept_name) ON DELETE CASCADE
26);
27
28CREATE TABLE instructor (
29    ID int,
30    name varchar(50),
31    dept_name varchar(50),
32    salary int,
33    PRIMARY KEY (ID),
34    FOREIGN KEY (dept_name) REFERENCES department (dept_name) ON DELETE CASCADE
35);
36
37CREATE TABLE section (
38    course_id int, 
39    sec_id int,
40    semester int,
41    year int,
42    building varchar(50),
43    room_number int,  
44    time_slot_id int,
45    PRIMARY KEY (sec_id, semester, year),
46    FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE CASCADE,
47    FOREIGN KEY (building, room_number) REFERENCES classroom (building, room_number) ON DELETE CASCADE
48);
49
50CREATE TABLE teaches (
51    ID int, 
52    course_id int, 
53    sec_id int,
54    semester int,
55    year int, 
56    PRIMARY KEY (ID, course_id, sec_id, semester, year),
57    FOREIGN KEY (ID) REFERENCES instructor (ID) ON DELETE CASCADE,
58    FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE CASCADE,
59    FOREIGN KEY (sec_id, semester, year) REFERENCES section (sec_id, semester, year) ON DELETE CASCADE
60);
61
62CREATE TABLE student (
63    ID int PRIMARY KEY,
64    name varchar(50),
65    dept_name varchar(50),  
66    tot_cred int, 
67    FOREIGN KEY (dept_name) REFERENCES department (dept_name) ON DELETE CASCADE
68);
69
70CREATE TABLE takes (
71    ID int, 
72    course_id int,
73    sec_id int, 
74    semester int, 
75    year int,
76    grade int,
77    PRIMARY KEY (ID, course_id, sec_id, semester, year),
78    FOREIGN KEY (ID) REFERENCES student (ID) ON DELETE CASCADE,
79    FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE CASCADE,
80    FOREIGN KEY (sec_id, semester, year) REFERENCES section (sec_id, semester, year) ON DELETE CASCADE
81);
82
83CREATE TABLE advisor (
84    s_ID int,
85    i_ID int,
86    PRIMARY KEY (s_ID, i_ID)
87);
88
89CREATE TABLE time_slot (
90    time_slot_id int,
91    day varchar(20),
92    start_time varchar(20),
93    end_time varchar(20),
94    PRIMARY KEY (time_slot_id, day, start_time)
95);
96
97CREATE TABLE prereq (
98    course_id int, 
99    prereq_id int, 
100    PRIMARY KEY (course_id, prereq_id),
101    FOREIGN KEY (course_id) REFERENCES course (course_id) ON DELETE CASCADE
102);