7r9BJhZr

1DROP TABLE IF EXISTS Technical_Staff
2DROP TABLE IF EXISTS Staff_Info
3DROP TABLE IF EXISTS Staff
4DROP TABLE IF EXISTS Person
5DROP TABLE IF EXISTS Addr
6DROP TABLE IF EXISTS Location
7
8CREATE TABLE Location (
9	Zip INT PRIMARY KEY,
10	City CHAR(20),
11	State CHAR(20)
12);
13
14GO
15
16CREATE TABLE Addr(
17	Address VARCHAR(100) PRIMARY KEY,
18	Zip INT,
19
20	FOREIGN KEY (Zip) REFERENCES Location(Zip)
21	ON DELETE SET NULL
22	ON UPDATE CASCADE
23);
24
25GO
26
27CREATE TABLE Person(
28	Person_ID INT PRIMARY KEY,
29	Name CHAR(20),
30	Schools CHAR(30),
31	Phone CHAR(20),
32	Email CHAR(30),
33	Address VARCHAR(100),
34	
35	FOREIGN KEY (Address) REFERENCES Addr(Address)
36	ON UPDATE SET NULL
37	ON DELETE SET NULL
38);
39
40GO
41
42CREATE TABLE Staff(
43	Person_ID INT PRIMARY KEY,
44	Staff_ID INT UNIQUE NOT NULL,
45
46	FOREIGN KEY (Person_ID) REFERENCES Person(Person_ID)
47	ON UPDATE CASCADE
48	ON DELETE CASCADE,
49);
50
51GO
52
53
54/* 
55In MS SQL Server, a foreign key constraint can also be defined to 
56reference the columns of a UNIQUE constraint in another table.
57*/
58CREATE TABLE Staff_Info(
59	Staff_ID INT PRIMARY KEY,
60	Date_Hired DATE,
61	Position CHAR(15),
62	Is_Admin BIT,
63
64	FOREIGN KEY (Staff_ID) REFERENCES Staff(Staff_ID)
65	ON UPDATE CASCADE
66	ON DELETE CASCADE
67);
68
69GO
70
71
72
73
74CREATE TABLE Technical_Staff(
75	Staff_ID INT PRIMARY KEY,
76	Lab_Name CHAR(20),
77	Lab_School CHAR(20),
78
79	FOREIGN KEY (Staff_ID) REFERENCES Staff_Info(Staff_ID)
80	ON UPDATE CASCADE
81	ON DELETE CASCADE,
82);
83
84GO
85
86
87CREATE TABLE Student(
88	Person_ID INT PRIMARY KEY,
89	Student_ID INT UNIQUE NOT NULL
90
91	FOREIGN KEY (Person_ID) REFERENCES Person(Person_ID)
92	ON UPDATE CASCADE
93	ON DELETE CASCADE,
94)
95
96GO
97
98/* 
99In MS SQL Server, a foreign key constraint can also be defined to 
100reference the columns of a UNIQUE constraint in another table.
101*/
102CREATE TABLE Student_Info(
103	Student_ID INT PRIMARY KEY,
104	Admission_Date DATE,
105	Majors_Minors CHAR(40),
106	Is_Grad BIT,
107	Is_UnderGrad BIT
108
109	FOREIGN KEY (Student_ID) REFERENCES Student(Student_ID)
110	ON UPDATE CASCADE
111	ON DELETE CASCADE,
112
113	CHECK (Is_Grad <> Is_UnderGrad)
114);
115
116GO
117
118/*==============youlin==============*/
119
120CREATE TABLE Professor(
121	Person_ID INT PRIMARY KEY,
122	Prof_ID INT UNIQUE NOT NULL,
123
124	FOREIGN KEY (Person_ID) REFERENCES Person(Person_ID)
125	ON UPDATE CASCADE
126	ON DELETE CASCADE,
127
128);
129
130GO
131
132
133CREATE TABLE Professor_Info(
134	Prof_ID INT PRIMARY KEY,
135	Field_of_Expertise VARCHAR(255)
136
137	FOREIGN KEY (Prof_ID) REFERENCES Professor(Prof_ID)
138	ON UPDATE CASCADE
139	ON DELETE CASCADE,
140);
141
142GO
143
144
145CREATE TABLE Course_Info(
146	Course_ID INT PRIMARY KEY ,
147	Course_TIME TIME NOT NULL,
148);
149GO
150
151
152CREATE TABLE TimeTable(
153	Person_ID INT FOREIGN KEY REFERENCES Professor(Person_ID),
154	Data_time DATETIME,
155	Course_ID INT FOREIGN KEY REFERENCES Course_Info(Course_ID),
156
157	PRIMARY KEY (Person_ID,Data_time,Course_ID)
158
159);
160
161GO
162
163
164CREATE TABLE Take_Course(
165	Prof_Person_ID INT FOREIGN KEY REFERENCES Professor(Person_ID),
166	Student_Person_ID INT FOREIGN KEY REFERENCES Student(Person_ID),
167	Course_ID INT FOREIGN KEY REFERENCES Course_Info(Course_ID),
168
169	PRIMARY KEY (Prof_Person_ID,Student_Person_ID,Course_ID)
170);
171
172GO
173
174/*==============LZCAI==============*/
175
176
177CREATE TABLE Supervise(
178	Prof_Person_ID INT,
179	Grad_Person_ID INT,
180	Research_Topic CHAR(20),
181
182	PRIMARY KEY (Prof_Person_ID, Grad_Person_ID),
183	FOREIGN KEY (Prof_Person_ID) REFERENCES Professor(Person_ID),
184	FOREIGN KEY (Grad_Person_ID) REFERENCES Student(Student_ID)
185	ON UPDATE CASCADE
186	ON DELETE CASCADE
187);
188
189GO
190
191
192
193CREATE TABLE StakeHolder(
194	Person_ID INT PRIMARY KEY,
195	Domain CHAR(20),
196
197	FOREIGN KEY (Person_ID) REFERENCES Person(Person_ID)
198	ON UPDATE CASCADE
199	ON DELETE CASCADE,
200);
201
202GO
203
204
205CREATE TABLE Comments(
206	Person_ID INT,
207	Dates DATE,
208	Topic CHAR(20),
209	Content VARCHAR(100),
210
211	PRIMARY KEY (Person_ID, Dates, Topic),
212	FOREIGN KEY (Person_ID) REFERENCES StakeHolder(Person_ID)
213	ON UPDATE CASCADE
214	ON DELETE CASCADE
215);
216
217GO
218
219
220CREATE TABLE Lab(
221	Lab_Name CHAR(20),
222	Lab_School CHAR(20),
223	Location CHAR(20),
224	Is_Research BIT,
225	Is_Teaching BIT,
226
227	PRIMARY KEY (Lab_Name, Lab_School)
228);
229
230GO
231
232CREATE TABLE Experiment(
233	Lab_Name CHAR(20), 
234	Lab_School CHAR(20),
235	Person_ID INT,
236	Dates DATE,
237	Attendance BIT,
238
239	PRIMARY KEY (Lab_Name, Lab_School, Person_ID),
240	FOREIGN KEY (Lab_Name, Lab_School) REFERENCES Lab(Lab_Name, Lab_School),
241	FOREIGN KEY (Person_ID) REFERENCES Student(Person_ID)
242	ON UPDATE CASCADE
243	ON DELETE CASCADE
244);
245
246GO
247
248
249CREATE TABLE Equipment(
250	Equipment_ID INT,
251	Lab_Name CHAR(20), 
252	Lab_School CHAR(20),
253	Name CHAR(20),
254	Model_ID CHAR(20),
255	Date_Purchased DATE,
256
257	PRIMARY KEY (Equipment_ID, Lab_Name, Lab_School),
258	FOREIGN KEY (Lab_Name, Lab_School) REFERENCES Lab(Lab_Name, Lab_School)
259	ON UPDATE CASCADE
260	ON DELETE CASCADE
261);
262
263GO
264
265
266
267CREATE TABLE Graduate_Assign_ResLab(
268	Lab_Name CHAR(20), 
269	Lab_School CHAR(20),
270	Person_ID INT,
271
272	PRIMARY KEY (Lab_Name, Lab_School, Person_ID),
273	FOREIGN KEY (Lab_Name, Lab_School) REFERENCES Lab(Lab_Name, Lab_School),
274	FOREIGN KEY (Person_ID) REFERENCES Student(Person_ID) 
275	ON UPDATE CASCADE
276	ON DELETE CASCADE
277);
278
279GO