· 8 years ago · May 13, 2018, 07:30 PM
1/*==============================================================*/
2/* DBMS name: Microsoft SQL Server 2012 */
3/* Created on: 5/13/2018 9:19:55 PM */
4/*==============================================================*/
5
6
7if exists (select 1
8 from sysindexes
9 where id = object_id('Advisor')
10 and name = 'Must be2_FK'
11 and indid > 0
12 and indid < 255)
13 drop index Advisor."Must be2_FK"
14go
15
16if exists (select 1
17 from sysobjects
18 where id = object_id('Advisor')
19 and type = 'U')
20 drop table Advisor
21go
22
23if exists (select 1
24 from sysindexes
25 where id = object_id('ChairProf')
26 and name = 'Could be2_FK'
27 and indid > 0
28 and indid < 255)
29 drop index ChairProf."Could be2_FK"
30go
31
32if exists (select 1
33 from sysobjects
34 where id = object_id('ChairProf')
35 and type = 'U')
36 drop table ChairProf
37go
38
39if exists (select 1
40 from sysindexes
41 where id = object_id('Course')
42 and name = 'Teach_FK'
43 and indid > 0
44 and indid < 255)
45 drop index Course.Teach_FK
46go
47
48if exists (select 1
49 from sysindexes
50 where id = object_id('Course')
51 and name = 'Offers_FK'
52 and indid > 0
53 and indid < 255)
54 drop index Course.Offers_FK
55go
56
57if exists (select 1
58 from sysobjects
59 where id = object_id('Course')
60 and type = 'U')
61 drop table Course
62go
63
64if exists (select 1
65 from sysindexes
66 where id = object_id('Dean')
67 and name = 'Might_FK'
68 and indid > 0
69 and indid < 255)
70 drop index Dean.Might_FK
71go
72
73if exists (select 1
74 from sysobjects
75 where id = object_id('Dean')
76 and type = 'U')
77 drop table Dean
78go
79
80if exists (select 1
81 from sysindexes
82 where id = object_id('Departement')
83 and name = 'Chairs_FK'
84 and indid > 0
85 and indid < 255)
86 drop index Departement.Chairs_FK
87go
88
89if exists (select 1
90 from sysindexes
91 where id = object_id('Departement')
92 and name = 'Assigned_FK'
93 and indid > 0
94 and indid < 255)
95 drop index Departement.Assigned_FK
96go
97
98if exists (select 1
99 from sysindexes
100 where id = object_id('Departement')
101 and name = 'Consits_FK'
102 and indid > 0
103 and indid < 255)
104 drop index Departement.Consits_FK
105go
106
107if exists (select 1
108 from sysobjects
109 where id = object_id('Departement')
110 and type = 'U')
111 drop table Departement
112go
113
114if exists (select 1
115 from sysindexes
116 where id = object_id('Enroll')
117 and name = 'Enroll2_FK'
118 and indid > 0
119 and indid < 255)
120 drop index Enroll.Enroll2_FK
121go
122
123if exists (select 1
124 from sysobjects
125 where id = object_id('Enroll')
126 and type = 'U')
127 drop table Enroll
128go
129
130if exists (select 1
131 from sysindexes
132 where id = object_id('Professor')
133 and name = 'Might2_FK'
134 and indid > 0
135 and indid < 255)
136 drop index Professor.Might2_FK
137go
138
139if exists (select 1
140 from sysindexes
141 where id = object_id('Professor')
142 and name = 'Could be_FK'
143 and indid > 0
144 and indid < 255)
145 drop index Professor."Could be_FK"
146go
147
148if exists (select 1
149 from sysobjects
150 where id = object_id('Professor')
151 and type = 'U')
152 drop table Professor
153go
154
155if exists (select 1
156 from sysindexes
157 where id = object_id('School')
158 and name = 'admin_FK'
159 and indid > 0
160 and indid < 255)
161 drop index School.admin_FK
162go
163
164if exists (select 1
165 from sysobjects
166 where id = object_id('School')
167 and type = 'U')
168 drop table School
169go
170
171if exists (select 1
172 from sysindexes
173 where id = object_id('Section')
174 and name = 'Have_FK'
175 and indid > 0
176 and indid < 255)
177 drop index Section.Have_FK
178go
179
180if exists (select 1
181 from sysobjects
182 where id = object_id('Section')
183 and type = 'U')
184 drop table Section
185go
186
187if exists (select 1
188 from sysindexes
189 where id = object_id('Student')
190 and name = 'Hass_FK'
191 and indid > 0
192 and indid < 255)
193 drop index Student.Hass_FK
194go
195
196if exists (select 1
197 from sysindexes
198 where id = object_id('Student')
199 and name = 'Has_FK'
200 and indid > 0
201 and indid < 255)
202 drop index Student.Has_FK
203go
204
205if exists (select 1
206 from sysobjects
207 where id = object_id('Student')
208 and type = 'U')
209 drop table Student
210go
211
212/*==============================================================*/
213/* Table: Advisor */
214/*==============================================================*/
215create table Advisor (
216 ProfesserID int not null,
217 AdvisorID int not null,
218 AdvisorName varchar(100) null,
219 constraint PK_ADVISOR primary key nonclustered (ProfesserID, AdvisorID)
220)
221go
222
223/*==============================================================*/
224/* Index: "Must be2_FK" */
225/*==============================================================*/
226create index "Must be2_FK" on Advisor (
227ProfesserID ASC
228)
229go
230
231/*==============================================================*/
232/* Table: ChairProf */
233/*==============================================================*/
234create table ChairProf (
235 ChairProfID int not null,
236 ProfesserID int not null,
237 ChairProfName varchar(100) null,
238 constraint PK_CHAIRPROF primary key nonclustered (ChairProfID)
239)
240go
241
242/*==============================================================*/
243/* Index: "Could be2_FK" */
244/*==============================================================*/
245create index "Could be2_FK" on ChairProf (
246ProfesserID ASC
247)
248go
249
250/*==============================================================*/
251/* Table: Course */
252/*==============================================================*/
253create table Course (
254 CourseName varchar(100) not null,
255 CourseID int not null,
256 ProfesserID int not null,
257 DeeptID int not null,
258 constraint PK_COURSE primary key nonclustered (CourseID)
259)
260go
261
262/*==============================================================*/
263/* Index: Offers_FK */
264/*==============================================================*/
265create index Offers_FK on Course (
266DeeptID ASC
267)
268go
269
270/*==============================================================*/
271/* Index: Teach_FK */
272/*==============================================================*/
273create index Teach_FK on Course (
274ProfesserID ASC
275)
276go
277
278/*==============================================================*/
279/* Table: Dean */
280/*==============================================================*/
281create table Dean (
282 deanid int not null,
283 ProfesserID int null,
284 Deanname varchar(100) not null,
285 constraint PK_DEAN primary key nonclustered (deanid)
286)
287go
288
289/*==============================================================*/
290/* Index: Might_FK */
291/*==============================================================*/
292create index Might_FK on Dean (
293ProfesserID ASC
294)
295go
296
297/*==============================================================*/
298/* Table: Departement */
299/*==============================================================*/
300create table Departement (
301 DeptName varchar(100) not null,
302 DeeptID int not null,
303 ChairProfID int not null,
304 ProfesserID int not null,
305 SchoolName varchar(100) not null,
306 Major varchar(100) not null,
307 constraint PK_DEPARTEMENT primary key nonclustered (DeeptID)
308)
309go
310
311/*==============================================================*/
312/* Index: Consits_FK */
313/*==============================================================*/
314create index Consits_FK on Departement (
315SchoolName ASC
316)
317go
318
319/*==============================================================*/
320/* Index: Assigned_FK */
321/*==============================================================*/
322create index Assigned_FK on Departement (
323ProfesserID ASC
324)
325go
326
327/*==============================================================*/
328/* Index: Chairs_FK */
329/*==============================================================*/
330create index Chairs_FK on Departement (
331ChairProfID ASC
332)
333go
334
335/*==============================================================*/
336/* Table: Enroll */
337/*==============================================================*/
338create table Enroll (
339 StudentID int not null,
340 CourseID int not null,
341 constraint PK_ENROLL primary key (StudentID, CourseID)
342)
343go
344
345/*==============================================================*/
346/* Index: Enroll2_FK */
347/*==============================================================*/
348create index Enroll2_FK on Enroll (
349CourseID ASC
350)
351go
352
353/*==============================================================*/
354/* Table: Professor */
355/*==============================================================*/
356create table Professor (
357 ProfesserID int not null,
358 deanid int null,
359 AdvisorID int null,
360 ChairProfID int null,
361 Rank varchar(100) not null,
362 Salary money null,
363 ProfessorName varchar(100) null,
364 constraint PK_PROFESSOR primary key nonclustered (ProfesserID)
365)
366go
367
368/*==============================================================*/
369/* Index: "Could be_FK" */
370/*==============================================================*/
371create index "Could be_FK" on Professor (
372ChairProfID ASC
373)
374go
375
376/*==============================================================*/
377/* Index: Might2_FK */
378/*==============================================================*/
379create index Might2_FK on Professor (
380deanid ASC
381)
382go
383
384/*==============================================================*/
385/* Table: School */
386/*==============================================================*/
387create table School (
388 SchoolName varchar(100) not null,
389 deanid int not null,
390 NoOfDept int not null,
391 constraint PK_SCHOOL primary key nonclustered (SchoolName)
392)
393go
394
395/*==============================================================*/
396/* Index: admin_FK */
397/*==============================================================*/
398create index admin_FK on School (
399deanid ASC
400)
401go
402
403/*==============================================================*/
404/* Table: Section */
405/*==============================================================*/
406create table Section (
407 SectionName char(100) not null,
408 SectionID int not null,
409 CourseID int not null,
410 constraint PK_SECTION primary key nonclustered (SectionID)
411)
412go
413
414/*==============================================================*/
415/* Index: Have_FK */
416/*==============================================================*/
417create index Have_FK on Section (
418CourseID ASC
419)
420go
421
422/*==============================================================*/
423/* Table: Student */
424/*==============================================================*/
425create table Student (
426 StudentName varchar(100) not null,
427 StudentID int not null,
428 DeeptID int not null,
429 ProfesserID int not null,
430 AdvisorID int not null,
431 Address varchar(100) null,
432 MobileNumber varchar(100) null,
433 constraint PK_STUDENT primary key nonclustered (StudentID)
434)
435go
436
437/*==============================================================*/
438/* Index: Has_FK */
439/*==============================================================*/
440create index Has_FK on Student (
441DeeptID ASC
442)
443go
444
445/*==============================================================*/
446/* Index: Hass_FK */
447/*==============================================================*/
448create index Hass_FK on Student (
449ProfesserID ASC,
450AdvisorID ASC
451)
452go