· 6 years ago · Nov 20, 2019, 12:50 AM
1DROP DATABASE IF EXISTS University;
2
3CREATE DATABASE IF NOT EXISTS University;
4
5USE University;
6
7DROP TABLE IF EXISTS Student;
8
9DROP TABLE IF EXISTS Faculty;
10
11DROP TABLE IF EXISTS Class;
12
13DROP TABLE IF EXISTS Enroll;
14
15CREATE TABLE Student (
16
17 stuId VARCHAR(10) PRIMARY KEY,
18
19 lastName VARCHAR(15),
20
21 firstName VARCHAR(15),
22
23 major VARCHAR(15),
24
25 credits INT);
26
27
28
29CREATE TABLE Faculty (
30
31 facId VARCHAR(10) PRIMARY KEY,
32
33 name VARCHAR(15),
34
35 department VARCHAR(15),
36
37 rank VARCHAR(15));
38
39
40
41CREATE TABLE Class (
42
43 classNumber VARCHAR(10) PRIMARY KEY,
44
45 facId VARCHAR(10),
46
47 schedule VARCHAR(10),
48
49 room VARCHAR(10),
50
51 FOREIGN KEY(facId) REFERENCES Faculty(facId));
52
53
54
55CREATE TABLE Enroll (
56
57 stuId VARCHAR(10) NOT NULL,
58
59 classNumber VARCHAR(10) NOT NULL,
60
61 grade VARCHAR(2),
62
63 PRIMARY KEY(stuId, classNumber),
64
65 FOREIGN KEY(stuId) REFERENCES Student(stuId),
66
67 FOREIGN KEY(classNumber) REFERENCES Class(classNumber));
68
69
70
71INSERT INTO Student (stuId, lastName, firstName, major, credits)
72
73VALUES ('S1001','Smith','Tom','History',90);
74
75INSERT INTO Student (stuId, lastName, firstName, major, credits)
76
77VALUES ('S1002','Chin','Ann','Math',36);
78
79INSERT INTO Student (stuId, lastName, firstName, major, credits)
80
81VALUES ('S1005','Lee','Perry','History',3);
82
83INSERT INTO Student (stuId, lastName, firstName, major, credits)
84
85VALUES ('S1010','Burns','Edward','Art',63);
86
87INSERT INTO Student (stuId, lastName, firstName, major, credits)
88
89VALUES ('S1013','McCarthy','Owen','Math',0);
90
91INSERT INTO Student (stuId, lastName, firstName, major, credits)
92
93VALUES ('S1015','Jones','Mary','Math',42);
94
95INSERT INTO Student (stuId, lastName, firstName, major, credits)
96
97VALUES ('S1020','Rivera','Jane','CSC',15);
98
99
100
101INSERT INTO Faculty (facId, name, department, rank)
102
103VALUES ('F101','Adams','Art','Professor');
104
105INSERT INTO Faculty (facId, name, department, rank)
106
107VALUES ('F105','Tanaka','CSC','Instructor');
108
109INSERT INTO Faculty (facId, name, department, rank)
110
111VALUES ('F110','Byrne','Math','Assistant');
112
113INSERT INTO Faculty (facId, name, department, rank)
114
115VALUES ('F115','Smith','History','Associate');
116
117INSERT INTO Faculty (facId, name, department, rank)
118
119VALUES ('F221','Smith','CSC','Professor');
120
121
122
123INSERT INTO Class (classNumber, facId, schedule, room)
124
125VALUES ('ART103A','F101','MWF9','H221');
126
127INSERT INTO Class (classNumber, facId, schedule, room)
128
129VALUES ('CSC201A','F105','TuThF10','M110');
130
131INSERT INTO Class (classNumber, facId, schedule, room)
132
133VALUES ('CSC203A','F105','MThF12','M110');
134
135INSERT INTO Class (classNumber, facId, schedule, room)
136
137VALUES ('HST205A','F115','MWF11','H221');
138
139INSERT INTO Class (classNumber, facId, schedule, room)
140
141VALUES ('MTH101B','F110','MTuTh9','H225');
142
143INSERT INTO Class (classNumber, facId, schedule, room)
144
145VALUES ('MTH103C','F110','MWF11','H225');
146
147
148
149INSERT INTO Enroll (stuId, classNumber, grade)
150
151VALUES ('S1001','ART103A','A');
152
153INSERT INTO Enroll (stuId, classNumber, grade)
154
155VALUES ('S1001','HST205A','C');
156
157INSERT INTO Enroll (stuId, classNumber, grade)
158
159VALUES ('S1002','ART103A','D');
160
161INSERT INTO Enroll (stuId, classNumber, grade)
162
163VALUES ('S1002','CSC201A','F');
164
165INSERT INTO Enroll (stuId, classNumber, grade)
166
167VALUES ('S1002','MTH103C','B');
168
169INSERT INTO Enroll (stuId, classNumber)
170
171VALUES ('S1010','ART103A');
172
173INSERT INTO Enroll (stuId, classNumber)
174
175VALUES ('S1010','MTH103C');
176
177INSERT INTO Enroll (stuId, classNumber, grade)
178
179VALUES ('S1020','CSC201A','B');
180
181INSERT INTO Enroll (stuId, classNumber, grade)
182
183VALUES ('S1020','MTH101B','A');