bv862x2N

1SET FOREIGN_KEY_CHECKS=0;
2
3create table if not exists classroom
4(building varchar (15),
5room_number varchar (7),
6capacity numeric (4,0),
7primary key (building, room_number));
8
9create table if not exists department
10(dept_name varchar (20),
11building varchar (15),
12budget numeric (12,2) check (budget > 0),
13primary key (dept_name));
14
15create table if not exists course
16(course_id varchar (8),
17title varchar (50),
18dept_name varchar (20),
19credits numeric (2,0) check (credits > 0),
20primary key (course_id),
21foreign key (dept_name) references department(dept_name)
22on delete set null);
23
24create table if not exists instructor
25(ID varchar (5),
26name varchar (20) not null,
27dept_name varchar (20),
28salary numeric (8,2) check (salary > 29000),
29primary key (ID),
30foreign key (dept_name) references department(dept_name)
31on delete set null);
32
33create table if not exists section
34(course_id varchar (8),
35sec_id varchar (8),
36semester varchar (6) check (semester in
37('Fall', 'Winter', 'Spring', 'Summer')),
38year numeric (4,0) check (year > 1701 and year < 2100),
39building varchar (15),
40room_number varchar (7),
41time_slot_id varchar (4),
42primary key (course_id, sec_id, semester, year),
43foreign key (course_id) references course(course_id)
44on delete cascade,
45foreign key (building, room_number) references classroom(building, room_number)
46on delete set null);
47
48create table if not exists teaches
49(ID varchar (5),
50course_id varchar (8),
51sec_id varchar (8),
52semester varchar (6),
53year numeric (4,0),
54primary key (ID, course_id, sec_id, semester, year),
55foreign key (course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year)
56on delete cascade,
57foreign key (ID) references instructor(ID)
58on delete cascade);
59
60create table if not exists student
61(ID varchar (5),
62name varchar (20) not null,
63dept_name varchar (20),
64tot_cred numeric (3,0) check (tot_cred >= 0),
65primary key (ID),
66foreign key (dept_name) references department(dept_name)
67on delete set null);
68
69create table if not exists takes
70(ID varchar (5),
71course_id varchar (8),
72sec_id varchar (8),
73semester varchar (6),
74year numeric (4,0),
75grade varchar (2),
76primary key (ID, course_id, sec_id, semester, year),
77foreign key (course_id, sec_id, semester, year) references section(course_id, sec_id, semester, year)
78on delete cascade,
79foreign key (ID) references student(ID)
80on delete cascade);
81
82create table if not exists advisor
83(s_ID varchar (5),
84i_ID varchar (5),
85primary key (s_ID),
86foreign key (i_ID) references instructor (ID)
87on delete set null,
88foreign key (s_ID) references student (ID)
89on delete cascade);
90
91create table if not exists prereq
92(course_id varchar(8),
93prereq_id varchar(8),
94primary key (course_id, prereq_id),
95foreign key (course_id) references course(course_id)
96on delete cascade,
97foreign key (prereq_id) references course(course_id));
98
99create table if not exists time_slot
100(time_slot_id varchar (4),
101day varchar (1),
102start_hr numeric (2) check (start_hr >= 0 and end_hr < 24),
103start_min numeric (2) check (start_min >= 0 and start_min < 60),
104end_hr numeric (2) check (end_hr >= 0 and end_hr < 24),
105end_min numeric (2) check (end_min >= 0 and end_min < 60),
106primary key (time_slot_id, day, start_hr, start_min));
107
108insert into classroom (building, room_number, capacity)
109values
110  ('Packard', '101', 500),
111  ('Painter', '514', 10),
112  ('Taylor', '3128', 70),
113  ('Watson', '100', 30),
114  ('Watson', '120', 50);
115
116insert into department(dept_name, building, budget)
117values
118  ('Biology', 'Watson', 90000),
119  ('Comp. Sci.', 'Taylor', 100000),
120  ('Elec. Eng.', 'Taylor', 85000),
121  ('Finance', 'Painter', 120000),
122  ('History', 'Painter', 50000),
123  ('Music', 'Packard', 80000),
124  ('Physics', 'Watson', 70000);
125
126insert into course (course_id, title, dept_name, credits)
127values
128  ('BIO-101', 'Intro. to Biology', 'Biology', 4),
129  ('BIO-301', 'Genetics', 'Biology', 4),
130  ('BIO-399', 'Computational Biology', 'Biology', 3),
131  ('CS-101', 'Intro. to Computer Science', 'Comp. Sci.', 4),
132  ('CS-190', 'Game Design', 'Comp. Sci.', 4),
133  ('CS-315', 'Robotics', 'Comp. Sci.', 3),
134  ('CS-319', 'Image Processing', 'Comp. Sci.', 3),
135  ('CS-347', 'Database System Concepts', 'Comp. Sci.', 3),
136  ('EE-181', 'Intro. to Digital Systems', 'Elec. Eng.', 3),
137  ('FIN-201', 'Investment Banking', 'Finance', 3),
138  ('HIS-351', 'World History', 'History', 3),
139  ('MU-199', 'Music Video Production', 'Music', 3),
140  ('PHY-101', 'Physical Principles', 'Physics', 4);
141
142insert into instructor (ID, name, dept_name, salary)
143values
144  ('10101', 'Srinivasan', 'Comp. Sci.', 65000),
145  ('12121', 'Wu', 'Finance', 90000),
146  ('15151', 'Mozart', 'Music', 40000),
147  ('22222', 'Einstein', 'Physics', 95000),
148  ('32343', 'El Said', 'History', 60000),
149  ('33456', 'Gold', 'Physics', 87000),
150  ('45565', 'Katz', 'Comp. Sci.', 75000),
151  ('58583', 'Califieri', 'History', 62000),
152  ('76543', 'Singh', 'Finance', 80000),
153  ('76766', 'Crick', 'Biology', 72000),
154  ('83821', 'Brandt', 'Comp. Sci.', 92000),
155  ('98345', 'Kim', 'Elec. Eng.', 80000);
156
157insert into section (course_id, sec_id, semester, year, building, room_number, time_slot_id)
158values
159  ('BIO-101', '1', 'Summer', 2009, 'Painter', '514', 'B'),
160  ('BIO-301', '1', 'Summer', 2010, 'Painter', '514', 'A'),
161  ('CS-101', '1', 'Summer', 2009, 'Packard', '101', 'H'),
162  ('CS-101', '1', 'Spring', 2010, 'Packard', '101', 'F'),
163  ('CS-190', '1', 'Spring', 2009, 'Taylor', '3128', 'E'),
164  ('CS-190', '2', 'Spring', 2009, 'Taylor', '3128', 'A'),
165  ('CS-315', '1', 'Spring', 2010, 'Watson', '120', 'D'),
166  ('CS-319', '1', 'Spring', 2010, 'Watson', '100', 'B'),
167  ('CS-319', '2', 'Spring', 2010, 'Taylor', '3128', 'C'),
168  ('CS-347', '1', 'Fall', 2009, 'Taylor', '3128', 'A'),
169  ('EE-181', '1', 'Spring', 2009, 'Taylor', '3128', 'C'),
170  ('FIN-201', '1', 'Spring', 2010, 'Packard', '101', 'B'),
171  ('HIS-351', '1', 'Spring', 2010, 'Painter', '514', 'C'),
172  ('MU-199', '1', 'Spring', 2010, 'Packard', '101', 'D'),
173  ('PHY-101', '1', 'Fall', 2009, 'Watson', '100', 'A');
174
175insert into teaches (ID, course_id, sec_id, semester, year)
176values
177  ('10101', 'CS-101', '1', 'Fall', 2009),
178  ('10101', 'CS-315', '1', 'Spring', 2010),
179  ('10101', 'CS-347', '1', 'Fall', 2009),
180  ('12121', 'FIN-201', '1', 'Spring', 2010),
181  ('15151', 'MU-199', '1', 'Spring', 2010),
182  ('22222', 'PHY-101', '1', 'Fall', 2009),
183  ('32343', 'HIS-351', '1', 'Spring', 2010),
184  ('45565', 'CS-101', '1', 'Spring', 2010),
185  ('45565', 'CS-319', '1', 'Spring', 2010),
186  ('76766', 'BIO-101', '1', 'Summer', 2009),
187  ('76766', 'BIO-301', '1', 'Summer', 2010),
188  ('83821', 'CS-190', '1', 'Spring', 2009),
189  ('83821', 'CS-190', '2', 'Spring', 2009),
190  ('83821', 'CS-319', '2', 'Spring', 2010),
191  ('98345', 'EE-181', '1', 'Spring', 2009);
192
193insert into student (ID, name, dept_name, tot_cred)
194values
195  ('00128', 'Zhang', 'Comp. Sci.', 102),
196  ('12345', 'Shankar', 'Comp. Sci.', 32),
197  ('19991', 'Brandt', 'History', 80),
198  ('23121', 'Chavez', 'Finance', 110),
199  ('44553', 'Peltier', 'Physics', 56),
200  ('45678', 'Levy', 'Physics', 46),
201  ('54321', 'Williams', 'Comp. Sci.', 54),
202  ('55739', 'Sanchez', 'Music', 38),
203  ('70557', 'Snow', 'Physics', 0),
204  ('76543', 'Brown', 'Comp. Sci.', 58),
205  ('76653', 'Aoi', 'Elec. Eng.', 60),
206  ('98765', 'Bourikas', 'Elec. Eng.', 98),
207  ('98988', 'Tanaka', 'Biology', 120);
208
209insert into takes (ID, course_id, sec_id, semester, year, grade)
210values
211  ('00128', 'CS-101', '1', 'Fall', 2009, 'A'),
212  ('00128', 'CS-347', '1', 'Fall', 2009, 'A-'),
213  ('12345', 'CS-101', '1', 'Fall', 2009, 'C'),
214  ('12345', 'CS-190', '2', 'Spring', 2009, 'A'),
215  ('12345', 'CS-315', '1', 'Spring', 2010, 'A'),
216  ('12345', 'CS-347', '1', 'Fall', 2009, 'A'),
217  ('19991', 'HIS-351', '1', 'Spring', 2010, 'B'),
218  ('23121', 'FIN-201', '1', 'Spring', 2010, 'C+'),
219  ('44553', 'PHY-101', '1', 'Fall', 2009, 'B-'),
220  ('45678', 'CS-101', '1', 'Fall', 2009, 'F'),
221  ('45678', 'CS-101', '1', 'Spring', 2010, 'B+'),
222  ('45678', 'CS-319', '1', 'Spring', 2010, 'B'),
223  ('54321', 'CS-101', '1', 'Fall', 2009, 'A-'),
224  ('54321', 'CS-190', '2', 'Spring', 2009, 'B+'),
225  ('55739', 'MU-199', '1', 'Spring', 2010, 'A-'),
226  ('76543', 'CS-101', '1', 'Fall', 2009, 'A'),
227  ('76543', 'CS-319', '2', 'Spring', 2010, 'A'),
228  ('76653', 'EE-181', '1', 'Spring', 2009, 'C'),
229  ('98765', 'CS-101', '1', 'Fall', 2009, 'C-'),
230  ('98765', 'CS-315', '1', 'Spring', 2010, 'B'),
231  ('98988', 'BIO-101', '1', 'Summer', 2009, 'A'),
232  ('98988', 'BIO-301', '1', 'Summer', 2010, null);
233
234insert into advisor (s_ID, i_ID)
235values
236  ('00128', '45565'),
237  ('12345', '10101'),
238  ('23121', '76543'),
239  ('44553', '22222'),
240  ('45678', '22222'),
241  ('76543', '45565'),
242  ('76653', '98345'),
243  ('98765', '98345'),
244  ('98988', '76766');
245
246insert into time_slot (time_slot_id, day, start_hr, start_min, end_hr, end_min)
247values
248  ('A', 'M', 8, 0, 8, 50),
249  ('A', 'W', 8, 0, 8, 50),
250  ('A', 'F', 8, 0, 8, 50),
251  ('B', 'M', 9, 0, 9, 50),
252  ('B', 'W', 9, 0, 9, 50),
253  ('B', 'F', 9, 0, 9, 50),
254  ('C', 'M', 11, 0, 11, 50),
255  ('C', 'W', 11, 0, 11, 50),
256  ('C', 'F', 11, 0, 11, 50),
257  ('D', 'M', 13, 0, 13, 50),
258  ('D', 'W', 13, 0, 13, 50),
259  ('D', 'F', 13, 0, 13, 50),
260  ('E', 'T', 10, 30, 11, 45),
261  ('E', 'R', 10, 30, 11, 45),
262  ('F', 'T', 14, 30, 15, 45),
263  ('F', 'R', 14, 30, 15, 45),
264  ('G', 'M', 16, 0, 16, 50),
265  ('G', 'W', 16, 0, 16, 50),
266  ('G', 'F', 16, 0, 16, 50),
267  ('H', 'W', 10, 0, 12, 30);
268
269insert into prereq (course_id, prereq_id)
270values
271  ('BIO-301', 'BIO-101'),
272  ('BIO-399', 'BIO-101'),
273  ('CS-190', 'CS-101'),
274  ('CS-315', 'CS-101'),
275  ('CS-319', 'CS-101'),
276  ('CS-347', 'CS-101'),
277  ('EE-181', 'PHY-101');
278
279SET FOREIGN_KEY_CHECKS=1;