v0LeDhVb

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);
129CREATE TABLE Professor_Info(
130	Prof_ID INT PRIMARY KEY,
131	Field_of_Expertise VARCHAR(255)
132
133	FOREIGN KEY (Prof_ID) REFERENCES Professor(Prof_ID)
134	ON UPDATE CASCADE
135	ON DELETE CASCADE,
136);
137
138CREATE TABLE TimeTable(
139	Person_ID INT FOREIGN KEY REFERENCES Professor(Person_ID)
140	Data_time DATETIME,
141	Course_ID INT FOREIGN KEY REFERENCES Course_Info(Course_ID),
142
143	PRIMARY KEY (Person_ID,Data_time,Course_ID)
144
145);
146
147CREATE TABLE Take_Course(
148	Prof_Person_ID INT FOREIGN KEY REFERENCES Professor(Person_ID),
149	Student_Person_ID INT FOREIGN KEY REFERENCES Student(Person_ID),
150	Course_ID INT FOREIGN KEY REFERENCES Course_Info(Course_ID),
151
152	PRIMARY KEY (Prof_Person_ID,Student_Person_ID,Course_ID)
153);
154
155CREATE TABLE Course_Info(
156	Course_ID INT PRIMARY KEY ,
157	Course_TIME DATETIME NOT NULL,
158
159
160);
161#==============LZCAI==============
162
163
164CREATE TABLE Supervise(
165	Prof_Person_ID INT,
166	Grad_Person_ID INT,
167	Research_Topic CHAR(20),
168
169	PRIMARY KEY (Prof_Person_ID, Grad_Person_ID),
170	FOREIGN KEY (Prof_Person_ID) REFERENCES Professer(Person_ID)
171	FOREIGN KEY (Grad_Person_ID) REFERENCES Student(Student_ID)
172	ON UPDATE CASCADE
173	ON DELETE CASCADE
174);
175
176GO
177
178
179
180CREATE TABLE StakeHolder(
181	Person_ID INT PRIMARY KEY,
182	Domain CHAR(20),
183
184	FOREIGN KEY (Person_ID) REFERENCES Person(Person_ID)
185	ON UPDATE CASCADE,
186	ON DELETE CASCADE,
187);
188
189GO
190
191
192CREATE TABLE Comments(
193	Person_ID INT,
194	Dates DATE,
195	Topic CHAR(20),
196	Content VARCHAR(100),
197
198	PRIMARY KEY (Person_ID, Dates, Topic)
199	FOREIGN KEY (Person_ID) REFERENCES StakeHolder(Person_ID)
200	ON UPDATE CASCADE
201	ON DELETE CASCADE
202);
203
204GO
205
206
207CREATE TABLE Lab(
208	Lab_Name CHAR(20),
209	Lab_School CHAR(20),
210	Location CHAR(20),
211	Is_Research BIT,
212	Is_Teaching BIT,
213
214	PRIMARY KEY (Lab_Name, Lab_School)
215	ON UPDATE CASCADE
216	ON DELETE CASCADE
217);
218
219GO
220
221CREATE TABLE Experiment(
222	Lab_Name CHAR(20), 
223	Lab_School CHAR(20),
224	Person_ID INT,
225	Dates DATE,
226	Attendance BIT,
227
228	PRIMARY KEY (Lab_Name, Lab_School, Person_ID)
229	FOREIGN KEY (Lab_Name, Lab_School) REFERENCES Lab(Lab_Name, Lab_School)
230	FOREIGN KEY (Person_ID) REFERENCES Student(Person_ID)
231	ON UPDATE CASCADE
232	ON DELETE CASCADE
233);
234
235GO
236
237
238CREATE TABLE Equipment(
239	Equipment_ID INT,
240	Lab_Name CHAR(20), 
241	Lab_School CHAR(20),
242	Name CHAR(20),
243	Model_ID CHAR(20),
244	Date_Purchased DATE,
245
246	PRIMARY KEY (Equipment_ID, Lab_Name, Lab_School)
247	FOREIGN KEY (Lab_Name, Lab_School) REFERENCES Lab(Lab_Name, Lab_School)
248	ON UPDATE CASCADE
249	ON DELETE CASCADE
250);
251
252GO
253
254
255
256CREATE TABLE Graduate_Assign_ResLab(
257	Lab_Name CHAR(20), 
258	Lab_School CHAR(20),
259	Person_ID INT,
260
261	PRIMARY KEY (Lab_Name, Lab_School, Person_ID)
262	FOREIGN KEY (Lab_Name, Lab_School) REFERENCES Lab(Lab_Name, Lab_School)
263	FOREIGN KEY (Person_ID) REFERENCES Student(Person_ID) 
264	ON UPDATE CASCADE
265	ON DELETE CASCADE
266);
267
268GO