forked from rlawrenc/cosc_304
-
Notifications
You must be signed in to change notification settings - Fork 0
/
university.ddl
314 lines (301 loc) · 16 KB
/
university.ddl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
DROP TABLE IF EXISTS enroll;
DROP TABLE IF EXISTS section;
DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS course;
DROP TABLE IF EXISTS prof;
CREATE TABLE student (
sid CHAR(8) NOT NULL,
sname VARCHAR(50),
sex CHAR(1),
birthdate DATE,
gpa DECIMAL(3,2),
PRIMARY KEY (sid));
CREATE TABLE prof (
pname VARCHAR(50) NOT NULL,
dname VARCHAR(20),
PRIMARY KEY (pname));
CREATE TABLE course (
cnum CHAR(8) NOT NULL,
dname VARCHAR(20),
cname VARCHAR(50),
PRIMARY KEY (cnum));
CREATE TABLE section (
cnum CHAR(8) NOT NULL,
secnum CHAR(3) NOT NULL,
pname VARCHAR(50),
PRIMARY KEY (cnum,secnum),
FOREIGN KEY (cnum) REFERENCES course(cnum) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (pname) REFERENCES prof(pname) ON DELETE SET NULL ON UPDATE CASCADE);
CREATE TABLE enroll (
sid CHAR(8) NOT NULL,
cnum CHAR(8) NOT NULL,
secnum CHAR(3) NOT NULL,
grade DECIMAL(3,2),
PRIMARY KEY (cnum,secnum,sid),
FOREIGN KEY (cnum) REFERENCES course(cnum) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (sid) REFERENCES student(sid) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (cnum,secnum) REFERENCES section(cnum,secnum) ON DELETE CASCADE ON UPDATE CASCADE);
INSERT student VALUES('00005465','Joe Smith','M','1997-5-1',3.20);
INSERT student VALUES('00112233','Trisha Cavanugh','F','1994-10-31',2.96);
INSERT student VALUES('00324534','Tony Tenson','M','1997-3-21',3.21);
INSERT student VALUES('00546343','Sarah Deacon','F','1996-1-9',2.78);
INSERT student VALUES('00567454','Scott Lawrence','M','1997-5-11',2.58);
INSERT student VALUES('00573453','Ryan Hackert','M','1995-7-7',3.10);
INSERT student VALUES('00612354','Elizabeth Guillum','F','1998-3-6',3.70);
INSERT student VALUES('00934353','James Miller','M','1997-5-29',2.82);
INSERT student VALUES('00983124','Chris Buckley','M','1996-6-12',2.67);
INSERT student VALUES('01239874','Beth Bohan','F','1995-4-17',2.67);
INSERT student VALUES('11111111','Matt Blair','M','1994-8-4',2.71);
INSERT student VALUES('11563098','Steve Striker','M','1998-8-9',2.77);
INSERT student VALUES('12456789','Dan Benson','M','1997-11-12',2.82);
INSERT student VALUES('22222222','Stephanie Stevens','F','1999-08-15',null);
INSERT student VALUES('22903424','Jie Wang','F','1996-7-4',2.96);
INSERT student VALUES('45671234','Adam Scott','M','1993-7-22',2.96);
INSERT student VALUES('55980348','Brian Brooks','M','1995-1-1',3.22);
INSERT student VALUES('63112345','Yan Yang','F','1997-8-4',3.09);
INSERT student VALUES('77777777','Sheldon Cooper','M','1994-03-08',null);
INSERT student VALUES('98123434','Jill Patten','F','1992-9-12',2.74);
INSERT student VALUES('99234353','Jamie Stokes','M','1990-8-23',3.05);
INSERT student VALUES('99999999','Wang Liu','M','1994-9-30',2.94);
INSERT prof VALUES('Art Funk','Computer Science');
INSERT prof VALUES('David Stewart','Mathematics');
INSERT prof VALUES('Don Epley','Computer Science');
INSERT prof VALUES('Eugene Madison','Mathematics');
INSERT prof VALUES('Fred Funk','Chemistry');
INSERT prof VALUES('Jessup Jacobs','Chemistry');
INSERT prof VALUES('Joe Smith','Chemistry');
INSERT prof VALUES('Ken Ackers','Mathematics');
INSERT prof VALUES('Ramon Lawrence','Computer Science');
INSERT prof VALUES('Bowen Hui','Computer Science');
INSERT prof VALUES('Steve Spureir','Physics');
INSERT course VALUES('CHEM 111','Chemistry','Principles of Chemistry I');
INSERT course VALUES('CHEM 113','Chemistry','Principles of Chemistry II');
INSERT course VALUES('COSC 222','Computer Science','Data Structures');
INSERT course VALUES('COSC 310','Computer Science','Software Engineering');
INSERT course VALUES('COSC 304','Computer Science','Introduction to Database Systems');
INSERT course VALUES('COSC 449','Computer Science','Directed Studies');
INSERT course VALUES('COSC 499','Computer Science','Capstone Project');
INSERT course VALUES('MATH 100','Mathematics','Differential Calculus');
INSERT course VALUES('MATH 101','Mathematics','Integral Calculus');
INSERT section VALUES('CHEM 111','001','Fred Funk');
INSERT section VALUES('CHEM 111','002','Fred Funk');
INSERT section VALUES('CHEM 111','003','Jessup Jacobs');
INSERT section VALUES('CHEM 113','001','Jessup Jacobs');
INSERT section VALUES('CHEM 113','002','Joe Smith');
INSERT section VALUES('CHEM 113','003','Joe Smith');
INSERT section VALUES('CHEM 113','004','Joe Smith');
INSERT section VALUES('COSC 222','001','Ramon Lawrence');
INSERT section VALUES('COSC 222','002','Ramon Lawrence');
INSERT section VALUES('COSC 310','001','Art Funk');
INSERT section VALUES('COSC 304','001','Ramon Lawrence');
INSERT section VALUES('COSC 449','001',null);
INSERT section VALUES('COSC 499','001','Bowen Hui');
INSERT section VALUES('MATH 100','001','Eugene Madison');
INSERT section VALUES('MATH 100','002','Eugene Madison');
INSERT section VALUES('MATH 100','003','Eugene Madison');
INSERT section VALUES('MATH 100','004','Eugene Madison');
INSERT section VALUES('MATH 100','005','Eugene Madison');
INSERT section VALUES('MATH 101','001','David Stewart');
INSERT section VALUES('MATH 101','002','David Stewart');
INSERT section VALUES('MATH 101','003','David Stewart');
INSERT section VALUES('MATH 101','004','David Stewart');
INSERT enroll VALUES('00005465','CHEM 111','001',2.00);
INSERT enroll VALUES('00112233','CHEM 111','001',3.33);
INSERT enroll VALUES('00546343','CHEM 111','001',2.00);
INSERT enroll VALUES('00567454','CHEM 111','001',2.00);
INSERT enroll VALUES('00934353','CHEM 111','001',4.33);
INSERT enroll VALUES('11563098','CHEM 111','001',3.00);
INSERT enroll VALUES('45671234','CHEM 111','001',4.33);
INSERT enroll VALUES('55980348','CHEM 111','001',4.00);
INSERT enroll VALUES('99234353','CHEM 111','001',4.00);
INSERT enroll VALUES('00112233','CHEM 111','002',3.67);
INSERT enroll VALUES('00324534','CHEM 111','002',3.67);
INSERT enroll VALUES('00567454','CHEM 111','002',3.33);
INSERT enroll VALUES('00573453','CHEM 111','002',4.00);
INSERT enroll VALUES('00612354','CHEM 111','002',3.00);
INSERT enroll VALUES('00934353','CHEM 111','002',1.00);
INSERT enroll VALUES('01239874','CHEM 111','002',2.00);
INSERT enroll VALUES('22903424','CHEM 111','002',2.00);
INSERT enroll VALUES('55980348','CHEM 111','002',3.00);
INSERT enroll VALUES('63112345','CHEM 111','002',2.33);
INSERT enroll VALUES('99234353','CHEM 111','002',3.00);
INSERT enroll VALUES('99999999','CHEM 111','002',3.00);
INSERT enroll VALUES('00112233','CHEM 111','003',2.00);
INSERT enroll VALUES('00324534','CHEM 111','003',2.00);
INSERT enroll VALUES('00567454','CHEM 111','003',3.67);
INSERT enroll VALUES('00934353','CHEM 111','003',3.00);
INSERT enroll VALUES('00983124','CHEM 111','003',2.00);
INSERT enroll VALUES('11111111','CHEM 111','003',3.67);
INSERT enroll VALUES('11563098','CHEM 111','003',2.33);
INSERT enroll VALUES('45671234','CHEM 111','003',2.33);
INSERT enroll VALUES('55980348','CHEM 111','003',3.00);
INSERT enroll VALUES('98123434','CHEM 111','003',3.00);
INSERT enroll VALUES('99234353','CHEM 111','003',2.66);
INSERT enroll VALUES('99999999','CHEM 111','003',2.00);
INSERT enroll VALUES('00112233','CHEM 113','001',2.00);
INSERT enroll VALUES('00324534','CHEM 113','001',3.00);
INSERT enroll VALUES('00546343','CHEM 113','001',2.33);
INSERT enroll VALUES('00567454','CHEM 113','001',2.00);
INSERT enroll VALUES('00573453','CHEM 113','001',2.00);
INSERT enroll VALUES('00983124','CHEM 113','001',1.00);
INSERT enroll VALUES('11563098','CHEM 113','001',2.33);
INSERT enroll VALUES('12456789','CHEM 113','001',3.67);
INSERT enroll VALUES('45671234','CHEM 113','001',3.33);
INSERT enroll VALUES('99999999','CHEM 113','001',4.33);
INSERT enroll VALUES('00112233','CHEM 113','002',4.00);
INSERT enroll VALUES('00324534','CHEM 113','002',3.33);
INSERT enroll VALUES('00546343','CHEM 113','002',3.00);
INSERT enroll VALUES('00934353','CHEM 113','002',1.00);
INSERT enroll VALUES('63112345','CHEM 113','002',3.00);
INSERT enroll VALUES('98123434','CHEM 113','002',4.00);
INSERT enroll VALUES('99234353','CHEM 113','002',3.33);
INSERT enroll VALUES('00324534','CHEM 113','003',3.67);
INSERT enroll VALUES('00612354','CHEM 113','003',4.33);
INSERT enroll VALUES('00934353','CHEM 113','003',3.33);
INSERT enroll VALUES('00983124','CHEM 113','003',3.67);
INSERT enroll VALUES('11563098','CHEM 113','003',2.33);
INSERT enroll VALUES('12456789','CHEM 113','003',3.67);
INSERT enroll VALUES('22903424','CHEM 113','003',2.00);
INSERT enroll VALUES('45671234','CHEM 113','003',4.00);
INSERT enroll VALUES('00112233','CHEM 113','004',3.67);
INSERT enroll VALUES('00324534','CHEM 113','004',2.00);
INSERT enroll VALUES('00567454','CHEM 113','004',3.00);
INSERT enroll VALUES('00573453','CHEM 113','004',3.33);
INSERT enroll VALUES('00934353','CHEM 113','004',3.00);
INSERT enroll VALUES('11563098','CHEM 113','004',2.33);
INSERT enroll VALUES('22903424','CHEM 113','004',2.00);
INSERT enroll VALUES('63112345','CHEM 113','004',3.00);
INSERT enroll VALUES('00324534','COSC 222','001',3.00);
INSERT enroll VALUES('00567454','COSC 222','001',2.66);
INSERT enroll VALUES('00573453','COSC 222','001',2.33);
INSERT enroll VALUES('11111111','COSC 222','001',3.00);
INSERT enroll VALUES('12456789','COSC 222','001',4.00);
INSERT enroll VALUES('99999999','COSC 222','001',3.33);
INSERT enroll VALUES('00112233','COSC 222','002',2.00);
INSERT enroll VALUES('00324534','COSC 222','002',3.67);
INSERT enroll VALUES('00573453','COSC 222','002',2.00);
INSERT enroll VALUES('00612354','COSC 222','002',4.33);
INSERT enroll VALUES('01239874','COSC 222','002',4.00);
INSERT enroll VALUES('11563098','COSC 222','002',4.33);
INSERT enroll VALUES('55980348','COSC 222','002',4.00);
INSERT enroll VALUES('63112345','COSC 222','002',3.00);
INSERT enroll VALUES('98123434','COSC 222','002',2.00);
INSERT enroll VALUES('99234353','COSC 222','002',2.33);
INSERT enroll VALUES('00112233','COSC 310','001',4.00);
INSERT enroll VALUES('00324534','COSC 310','001',4.33);
INSERT enroll VALUES('00546343','COSC 310','001',3.00);
INSERT enroll VALUES('00567454','COSC 310','001',2.00);
INSERT enroll VALUES('00612354','COSC 310','001',2.66);
INSERT enroll VALUES('00934353','COSC 310','001',3.33);
INSERT enroll VALUES('63112345','COSC 310','001',3.00);
INSERT enroll VALUES('98123434','COSC 310','001',3.00);
INSERT enroll VALUES('99234353','COSC 310','001',3.00);
INSERT enroll VALUES('99999999','COSC 310','001',2.66);
INSERT enroll VALUES('00005465','COSC 304','001',3.33);
INSERT enroll VALUES('00112233','COSC 304','001',2.33);
INSERT enroll VALUES('00546343','COSC 304','001',3.00);
INSERT enroll VALUES('00567454','COSC 304','001',3.00);
INSERT enroll VALUES('00573453','COSC 304','001',4.33);
INSERT enroll VALUES('00612354','COSC 304','001',3.33);
INSERT enroll VALUES('00934353','COSC 304','001',3.33);
INSERT enroll VALUES('12456789','COSC 304','001',4.33);
INSERT enroll VALUES('45671234','COSC 304','001',3.00);
INSERT enroll VALUES('99234353','COSC 304','001',3.00);
INSERT enroll VALUES('00112233','MATH 100','001',2.00);
INSERT enroll VALUES('00324534','MATH 100','001',2.66);
INSERT enroll VALUES('00567454','MATH 100','001',2.66);
INSERT enroll VALUES('00612354','MATH 100','001',4.33);
INSERT enroll VALUES('11563098','MATH 100','001',2.66);
INSERT enroll VALUES('12456789','MATH 100','001',3.00);
INSERT enroll VALUES('45671234','MATH 100','001',3.00);
INSERT enroll VALUES('63112345','MATH 100','001',4.33);
INSERT enroll VALUES('98123434','MATH 100','001',2.00);
INSERT enroll VALUES('99999999','MATH 100','001',3.00);
INSERT enroll VALUES('00005465','MATH 100','002',3.33);
INSERT enroll VALUES('00112233','MATH 100','002',3.00);
INSERT enroll VALUES('00324534','MATH 100','002',4.00);
INSERT enroll VALUES('00934353','MATH 100','002',2.66);
INSERT enroll VALUES('00983124','MATH 100','002',2.00);
INSERT enroll VALUES('01239874','MATH 100','002',2.66);
INSERT enroll VALUES('11111111','MATH 100','002',3.00);
INSERT enroll VALUES('22903424','MATH 100','002',2.33);
INSERT enroll VALUES('63112345','MATH 100','002',3.00);
INSERT enroll VALUES('98123434','MATH 100','002',4.33);
INSERT enroll VALUES('99999999','MATH 100','002',2.66);
INSERT enroll VALUES('00112233','MATH 100','003',2.33);
INSERT enroll VALUES('00324534','MATH 100','003',2.00);
INSERT enroll VALUES('00546343','MATH 100','003',4.00);
INSERT enroll VALUES('00934353','MATH 100','003',2.00);
INSERT enroll VALUES('00983124','MATH 100','003',3.67);
INSERT enroll VALUES('11111111','MATH 100','003',3.00);
INSERT enroll VALUES('12456789','MATH 100','003',1.00);
INSERT enroll VALUES('55980348','MATH 100','003',3.00);
INSERT enroll VALUES('63112345','MATH 100','003',2.00);
INSERT enroll VALUES('98123434','MATH 100','003',2.00);
INSERT enroll VALUES('99234353','MATH 100','003',3.00);
INSERT enroll VALUES('99999999','MATH 100','003',3.00);
INSERT enroll VALUES('00005465','MATH 100','004',3.00);
INSERT enroll VALUES('00112233','MATH 100','004',2.66);
INSERT enroll VALUES('00324534','MATH 100','004',4.33);
INSERT enroll VALUES('00546343','MATH 100','004',2.00);
INSERT enroll VALUES('00567454','MATH 100','004',1.00);
INSERT enroll VALUES('00573453','MATH 100','004',4.33);
INSERT enroll VALUES('00612354','MATH 100','004',3.00);
INSERT enroll VALUES('00934353','MATH 100','004',2.33);
INSERT enroll VALUES('11111111','MATH 100','004',2.66);
INSERT enroll VALUES('12456789','MATH 100','004',1.00);
INSERT enroll VALUES('22903424','MATH 100','004',4.33);
INSERT enroll VALUES('55980348','MATH 100','004',4.00);
INSERT enroll VALUES('63112345','MATH 100','004',2.66);
INSERT enroll VALUES('98123434','MATH 100','004',2.00);
INSERT enroll VALUES('99234353','MATH 100','004',3.00);
INSERT enroll VALUES('99999999','MATH 100','004',4.33);
INSERT enroll VALUES('00112233','MATH 100','005',4.00);
INSERT enroll VALUES('11563098','MATH 100','005',3.33);
INSERT enroll VALUES('99234353','MATH 100','005',3.00);
INSERT enroll VALUES('00112233','MATH 101','001',3.00);
INSERT enroll VALUES('00546343','MATH 101','001',2.00);
INSERT enroll VALUES('00573453','MATH 101','001',3.00);
INSERT enroll VALUES('00983124','MATH 101','001',2.66);
INSERT enroll VALUES('01239874','MATH 101','001',1.00);
INSERT enroll VALUES('11111111','MATH 101','001',2.33);
INSERT enroll VALUES('55980348','MATH 101','001',3.67);
INSERT enroll VALUES('98123434','MATH 101','001',2.33);
INSERT enroll VALUES('99234353','MATH 101','001',2.33);
INSERT enroll VALUES('00324534','MATH 101','002',3.33);
INSERT enroll VALUES('00934353','MATH 101','002',4.33);
INSERT enroll VALUES('00983124','MATH 101','002',3.00);
INSERT enroll VALUES('11111111','MATH 101','002',1.00);
INSERT enroll VALUES('12456789','MATH 101','002',3.67);
INSERT enroll VALUES('22903424','MATH 101','002',4.33);
INSERT enroll VALUES('45671234','MATH 101','002',1.00);
INSERT enroll VALUES('63112345','MATH 101','002',4.33);
INSERT enroll VALUES('99234353','MATH 101','002',4.00);
INSERT enroll VALUES('99999999','MATH 101','002',2.33);
INSERT enroll VALUES('00324534','MATH 101','003',4.00);
INSERT enroll VALUES('00546343','MATH 101','003',4.00);
INSERT enroll VALUES('00567454','MATH 101','003',3.00);
INSERT enroll VALUES('00573453','MATH 101','003',3.00);
INSERT enroll VALUES('00612354','MATH 101','003',4.00);
INSERT enroll VALUES('00934353','MATH 101','003',3.00);
INSERT enroll VALUES('00983124','MATH 101','003',3.33);
INSERT enroll VALUES('01239874','MATH 101','003',3.00);
INSERT enroll VALUES('22903424','MATH 101','003',4.00);
INSERT enroll VALUES('45671234','MATH 101','003',2.66);
INSERT enroll VALUES('55980348','MATH 101','003',2.33);
INSERT enroll VALUES('99999999','MATH 101','003',2.33);
INSERT enroll VALUES('00005465','MATH 101','004',4.33);
INSERT enroll VALUES('00112233','MATH 101','004',3.33);
INSERT enroll VALUES('00324534','MATH 101','004',2.33);
INSERT enroll VALUES('00546343','MATH 101','004',2.66);
INSERT enroll VALUES('00567454','MATH 101','004',2.66);
INSERT enroll VALUES('00573453','MATH 101','004',2.66);
INSERT enroll VALUES('00612354','MATH 101','004',4.33);
INSERT enroll VALUES('01239874','MATH 101','004',3.33);
INSERT enroll VALUES('11111111','MATH 101','004',3.00);
INSERT enroll VALUES('11563098','MATH 101','004',2.33);
INSERT enroll VALUES('12456789','MATH 101','004',1.00);
INSERT enroll VALUES('22903424','MATH 101','004',2.66);
INSERT enroll VALUES('55980348','MATH 101','004',2.00);
INSERT enroll VALUES('63112345','MATH 101','004',3.33);
INSERT enroll VALUES('99999999','MATH 101','004',2.33);