-
Notifications
You must be signed in to change notification settings - Fork 0
/
7Northwoods(des03).sql
314 lines (234 loc) · 8.32 KB
/
7Northwoods(des03).sql
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
314
-- script to create NORTHWOODS database
-- revised 09/01/2022 Huu Con Nguyen
connect sys/sys as sysdba;
DROP USER des03 CASCADE;
CREATE USER des03 IDENTIFIED BY des03;
GRANT connect , resource, create view TO des03;
connect des03/des03;
DROP TABLE enrollment CASCADE CONSTRAINTS;
DROP TABLE course_section CASCADE CONSTRAINTS;
DROP TABLE term CASCADE CONSTRAINTS;
DROP TABLE course CASCADE CONSTRAINTS;
DROP TABLE student CASCADE CONSTRAINTS;
DROP TABLE faculty CASCADE CONSTRAINTS;
DROP TABLE location CASCADE CONSTRAINTS;
CREATE TABLE LOCATION
(loc_id NUMBER(6),
bldg_code VARCHAR2(10),
room VARCHAR2(6),
capacity NUMBER(5),
CONSTRAINT location_loc_id_pk PRIMARY KEY (loc_id));
CREATE TABLE faculty
(f_id NUMBER(6),
f_last VARCHAR2(30),
f_first VARCHAR2(30),
f_mi CHAR(1),
loc_id NUMBER(5),
f_phone VARCHAR2(10),
f_rank VARCHAR2(8),
f_pin NUMBER(4),
f_image BLOB,
CONSTRAINT faculty_f_id_pk PRIMARY KEY(f_id),
CONSTRAINT faculty_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id));
CREATE TABLE student
(s_id NUMBER(6),
s_last VARCHAR2(30),
s_first VARCHAR2(30),
s_mi CHAR(1),
s_address VARCHAR2(25),
s_city VARCHAR2(20),
s_state CHAR(2),
s_zip VARCHAR2(10),
s_phone VARCHAR2(10),
s_class CHAR(2),
s_dob DATE,
s_pin NUMBER(4),
f_id NUMBER(6),
time_enrolled VARCHAR2(30),
CONSTRAINT student_s_id_pk PRIMARY KEY (s_id),
CONSTRAINT student_f_id_fk FOREIGN KEY (f_id) REFERENCES faculty(f_id));
CREATE TABLE TERM
(term_id NUMBER(6),
term_desc VARCHAR2(20),
status VARCHAR2(20),
CONSTRAINT term_term_id_pk PRIMARY KEY (term_id),
CONSTRAINT term_status_cc CHECK ((status = 'OPEN') OR (status = 'CLOSED')));
CREATE TABLE COURSE
(course_id NUMBER(6),
call_id VARCHAR2(10),
course_name VARCHAR2(25),
credits NUMBER(2),
CONSTRAINT course_course_id_pk PRIMARY KEY(course_id));
CREATE TABLE COURSE_SECTION
(c_sec_id NUMBER(6),
course_id NUMBER(6) CONSTRAINT course_section_courseid_nn NOT NULL,
term_id NUMBER(6) CONSTRAINT course_section_termid_nn NOT NULL,
sec_num NUMBER(2) CONSTRAINT course_section_secnum_nn NOT NULL,
f_id NUMBER(5),
c_sec_day VARCHAR2(10),
c_sec_time DATE,
c_sec_duration VARCHAR2(30),
loc_id NUMBER(6),
max_enrl NUMBER(4) CONSTRAINT course_section_maxenrl_nn NOT NULL,
CONSTRAINT course_section_csec_id_pk PRIMARY KEY (c_sec_id),
CONSTRAINT course_section_cid_fk FOREIGN KEY (course_id) REFERENCES course(course_id),
CONSTRAINT course_section_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id),
CONSTRAINT course_section_termid_fk FOREIGN KEY (term_id) REFERENCES term(term_id),
CONSTRAINT course_section_fid_fk FOREIGN KEY (f_id) REFERENCES faculty(f_id));
CREATE TABLE ENROLLMENT
(s_id NUMBER(6),
c_sec_id NUMBER(6),
grade CHAR(1),
CONSTRAINT enrollment_pk PRIMARY KEY (s_id, c_sec_id),
CONSTRAINT enrollment_sid_fk FOREIGN KEY (s_id) REFERENCES student(s_id),
CONSTRAINT enrollment_csecid_fk FOREIGN KEY (c_sec_id) REFERENCES course_section (c_sec_id));
---- inserting into LOCATION table
INSERT INTO location VALUES
(1, 'CR', '101', 150);
INSERT INTO location VALUES
(2, 'CR', '202', 40);
INSERT INTO location VALUES
(3, 'CR', '103', 35);
INSERT INTO location VALUES
(4, 'CR', '105', 35);
INSERT INTO location VALUES
(5, 'BUS', '105', 42);
INSERT INTO location VALUES
(6, 'BUS', '404', 35);
INSERT INTO location VALUES
(7, 'BUS', '421', 35);
INSERT INTO location VALUES
(8, 'BUS', '211', 55);
INSERT INTO location VALUES
(9, 'BUS', '424', 1);
INSERT INTO location VALUES
(10, 'BUS', '402', 1);
INSERT INTO location VALUES
(11, 'BUS', '433', 1);
INSERT INTO location VALUES
(12, 'LIB', '217', 2);
INSERT INTO location VALUES
(13, 'LIB', '222', 1);
--- inserting records into FACULTY
INSERT INTO faculty VALUES
(1, 'Cox', 'Kim', 'J', 9, '7155551234', 'ASSO', 1181, EMPTY_BLOB());
INSERT INTO faculty VALUES
(2, 'Blanchard', 'John', 'R', 10, '7155559087', 'FULL', 1075, EMPTY_BLOB());
INSERT INTO faculty VALUES
(3, 'Williams', 'Jerry', 'F', 12, '7155555412', 'ASST', 8531, EMPTY_BLOB());
INSERT INTO faculty VALUES
(4, 'Sheng', 'Laura', 'M', 11, '7155556409', 'INST', 1690, EMPTY_BLOB());
INSERT INTO faculty VALUES
(5, 'Brown', 'Philip', 'E', 13, '7155556082', 'ASSO', 9899, EMPTY_BLOB());
--- inserting records into STUDENT
INSERT INTO student VALUES
(1, 'Miller', 'Sarah', 'M', '144 Windridge Blvd.', 'Eau Claire',
'WI', '54703', '7155559876', 'SR', TO_DATE('07/14/1985', 'MM/DD/YYYY'), 8891, 1, '3-2');
INSERT INTO student VALUES
(2, 'Umato', 'Brian', 'D', '454 St. John''s Place', 'Eau Claire',
'WI', '54702', '7155552345', 'SR', TO_DATE('08/19/1985', 'MM/DD/YYYY'), 1230, 1, '4-2');
INSERT INTO student VALUES
(3, 'Black', 'Daniel', NULL, '8921 Circle Drive', 'Bloomer',
'WI', '54715', '7155553907', 'JR', TO_DATE('10/10/1982', 'MM/DD/YYYY'), 1613, 1, '3-0');
INSERT INTO student VALUES
(4, 'Mobley', 'Amanda', 'J', '1716 Summit St.', 'Eau Claire',
'WI', '54703', '7155556902', 'SO', TO_DATE('09/24/1986', 'MM/DD/YYYY'), 1841, 2, '2-2');
INSERT INTO student VALUES
(5, 'Sanchez', 'Ruben', 'R', '1780 Samantha Court', 'Eau Claire',
'WI', '54701', '7155558899', 'SO', TO_DATE('11/20/1986', 'MM/DD/YYYY'), 4420, 4, '1-11');
INSERT INTO student VALUES
(6, 'Connoly', 'Michael', 'S', '1818 Silver Street', 'Elk Mound',
'WI', '54712', '7155554944', 'FR', TO_DATE('12/4/1986', 'MM/DD/YYYY'), 9188, 3, '0-4');
--- inserting records into TERM
INSERT INTO term VALUES
(1, 'Fall 2005', 'CLOSED');
INSERT INTO term VALUES
(2, 'Spring 2006', 'CLOSED');
INSERT INTO term VALUES
(3, 'Summer 2006', 'CLOSED');
INSERT INTO term VALUES
(4, 'Fall 2006', 'CLOSED');
INSERT INTO term VALUES
(5, 'Spring 2007', 'CLOSED');
INSERT INTO term VALUES
(6, 'Summer 2007', 'OPEN');
--- inserting records into COURSE
INSERT INTO course VALUES
(1, 'MIS 101', 'Intro. to Info. Systems', 3);
INSERT INTO course VALUES
(2, 'MIS 301', 'Systems Analysis', 3);
INSERT INTO course VALUES
(3, 'MIS 441', 'Database Management', 3);
INSERT INTO course VALUES
(4, 'CS 155', 'Programming in C++', 3);
INSERT INTO course VALUES
(5, 'MIS 451', 'Web-Based Systems', 3);
--- inserting records into COURSE_SECTION
INSERT INTO course_section VALUES
(1, 1, 4, 1, 2, 'MWF', TO_DATE('10:00 AM', 'HH:MI AM'), '0 00:00:50.00', 1, 140);
INSERT INTO course_section VALUES
(2, 1, 4, 2, 3, 'TR', TO_DATE('09:30 AM', 'HH:MI AM'), '0 00:01:15.00', 7, 35);
INSERT INTO course_section VALUES
(3, 1, 4, 3, 3, 'MWF', TO_DATE('08:00 AM', 'HH:MI AM'), '0 00:00:50.00', 2, 35);
INSERT INTO course_section VALUES
(4, 2, 4, 1, 4, 'TR', TO_DATE('11:00 AM', 'HH:MI AM'), '0 00:01:15.00', 6, 35);
INSERT INTO course_section VALUES
(5, 2, 5, 2, 4, 'TR', TO_DATE('02:00 PM', 'HH:MI PM'), '0 00:01:15.00', 6, 35);
INSERT INTO course_section VALUES
(6, 3, 5, 1, 1, 'MWF', TO_DATE('09:00 AM', 'HH:MI AM'), '0 00:00:50.00', 5, 30);
INSERT INTO course_section VALUES
(7, 3, 5, 2, 1, 'MWF', TO_DATE('10:00 AM', 'HH:MI AM'), '0 00:00:50.00', 5, 30);
INSERT INTO course_section VALUES
(8, 4, 5, 1, 5, 'TR', TO_DATE('08:00 AM', 'HH:MI AM'), '0 00:01:15.00', 3, 35);
INSERT INTO course_section VALUES
(9, 5, 5, 1, 2, 'MWF', TO_DATE('02:00 PM', 'HH:MI PM'), '0 00:00:50.00', 5, 35);
INSERT INTO course_section VALUES
(10, 5, 5, 2, 2, 'MWF', TO_DATE('03:00 PM', 'HH:MI PM'), '0 00:00:50.00', 5, 35);
INSERT INTO course_section VALUES
(11, 1, 6, 1, 1, 'MTWRF', TO_DATE('08:00 AM', 'HH:MI AM'), '0 00:01:30.00', 1, 50);
INSERT INTO course_section VALUES
(12, 2, 6, 1, 2, 'MTWRF', TO_DATE('08:00 AM', 'HH:MI AM'), '0 00:01:30.00', 6, 35);
INSERT INTO course_section VALUES
(13, 3, 6, 1, 3, 'MTWRF', TO_DATE('09:00 AM', 'HH:MI AM'), '0 00:01:30.00', 5, 35);
--- inserting records into ENROLLMENT
INSERT INTO enrollment VALUES
(1, 1, 'A');
INSERT INTO enrollment VALUES
(1, 4, 'A');
INSERT INTO enrollment VALUES
(1, 6, 'B');
INSERT INTO enrollment VALUES
(1, 9, 'B');
INSERT INTO enrollment VALUES
(2, 1, 'C');
INSERT INTO enrollment VALUES
(2, 5, 'B');
INSERT INTO enrollment VALUES
(2, 6, 'A');
INSERT INTO enrollment VALUES
(2, 9, 'B');
INSERT INTO enrollment VALUES
(3, 1, 'C');
INSERT INTO enrollment VALUES
(3, 12, NULL);
INSERT INTO enrollment VALUES
(3, 13, NULL);
INSERT INTO enrollment VALUES
(4, 11, NULL);
INSERT INTO enrollment VALUES
(4, 12, NULL);
INSERT INTO enrollment VALUES
(5, 1, 'B');
INSERT INTO enrollment VALUES
(5, 5, 'C');
INSERT INTO enrollment VALUES
(5, 9, 'C');
INSERT INTO enrollment VALUES
(5, 11, NULL);
INSERT INTO enrollment VALUES
(5, 13, NULL);
INSERT INTO enrollment VALUES
(6, 11, NULL);
INSERT INTO enrollment VALUES
(6, 12, NULL);
COMMIT;