Create database University3 use University3 Create table FACULTY FacPK

  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
Create database University3
use University3
Create table FACULTY (
FacPK int PRIMARY KEY,
[Name] varchar(50) UNIQUE NOT NULL,
DeanFK int,
Building char(2)
CHECK(Building BETWEEN 1 AND 10),
Fund decimal(9,2)
CHECK (Fund > 100000.00)
)
/*FOREIGN KEY (DeanFK) REFERENCES TEACHER(TchPK) ON DELETE SET NULL,*/
Create table DEPARTMENT (
DepPK int PRIMARY KEY,
FacFK int,
[Name] varchar(50) UNIQUE NOT NULL,
HeadFK int,
Building char(2)
CHECK(Building BETWEEN '1' AND '10'),
Fund decimal(8,2)
CHECK (Fund > 20000.00 AND FUND < 100000.00)
)
Create table TEACHER (
TchPK int PRIMARY KEY,
DepFK int,
[Name] varchar(50) UNIQUE NOT NULL,
Post varchar(15)
CHECK(Post IN('ассистент' ,'преподаватель' ,'доцент', 'профессор')),
Tel char(7),
Hiredate date NOT NULL
Check(Hiredate > '01.01.1950'),
Salary decimal(6,2) NOT NULL
Check(Salary > 1000),
Commission decimal(6,2) default 0
Check (Commission >= 0),
ChiefFK int
)
Create table SGROUP (
GrpPK int PRIMARY KEY,
DepFK int,
Course decimal(1)
Check(Course >= 1 AND Course <= 6 ),
Num decimal(3)
CHECK(Num BETWEEN 0 AND 700),
Quantity decimal(2)
CHECK(Quantity BETWEEN 1 AND 50),
Curator int,
Rating decimal(3) default 0
CHECK(Rating BETWEEN 0 AND 100),
)
Create table [SUBJECT] (
SbjPK int PRIMARY KEY,
[Name] varchar(50) UNIQUE NOT NULL
)
Create table ROOM (
RomPK int PRIMARY KEY,
Num decimal(4) NOT NULL,
Seats decimal(3)
Check(Seats BETWEEN 1 AND 300),
[Floor] decimal(2)
Check([Floor] BETWEEN 1 AND 16),
Building char(5) NOT NULL
CHECK(Building BETWEEN 1 AND 10),
)
Create table LECTURE (
TchFK int,
GrpFK int,
SbjFK int,
RomFK int,
[Type] varchar(15) NOT NULL
Check([Type]IN( 'лекция', 'лабораторная','семинар' , 'практика' )),
[Day] char(3) NOT NULL
Check([Day] IN( 'пон', 'втр', 'срд','чет', 'пят', 'суб','вск')),
[Week] decimal(1) NOT NULL
Check([Week] = 1 OR [Week] = 2),
Lesson decimal(1) NOT NULL
Check(Lesson BETWEEN 1 AND 8)
FOREIGN KEY (TchFK) REFERENCES TEACHER(TchPK) ON DELETE SET NULL,
FOREIGN KEY (GrpFK) REFERENCES SGROUP(GrpPK) ON DELETE CASCADE,
FOREIGN KEY (SbjFK) REFERENCES [SUBJECT](SbjPK) ON DELETE CASCADE ,
FOREIGN KEY (RomFK) REFERENCES ROOM(RomPK) ON DELETE SET NULL
)
/*Добавление ограничений в FACULTY*/
ALTER TABLE FACULTY
ADD CONSTRAINT fk_faculty_dep
FOREIGN KEY (DeanFK)
REFERENCES Teacher(TchPK);
/*Добавление ограничений в DEPARTMENT*/
ALTER TABLE DEPARTMENT
ADD CONSTRAINT fk_dep_fac
FOREIGN KEY (FacFK)
REFERENCES FACULTY(FacPK);
ALTER TABLE DEPARTMENT
ADD CONSTRAINT fk_dep_teach
FOREIGN KEY (HeadFK )
REFERENCES TEACHER(TchPK);
ALTER TABLE DEPARTMENT
ADD CONSTRAINT Name_FK_Uniq UNIQUE (FacFK, [Name]);
/*Добавление ограничений в TEACHER*/
ALTER TABLE TEACHER
ADD CONSTRAINT fk_teach_dep
FOREIGN KEY (DepFK)
REFERENCES DEPARTMENT(DepPK);
ALTER TABLE TEACHER
ADD CONSTRAINT fk_teach_teach
FOREIGN KEY (ChiefFK )
REFERENCES TEACHER(TchPK );
/*Добавление ограничений в SGROUP*/
ALTER TABLE SGROUP
ADD CONSTRAINT fk_skgr_dep
FOREIGN KEY (DepFK)
REFERENCES DEPARTMENT(DepPK);
ALTER TABLE SGROUP
ADD CONSTRAINT fk_skgr_teach
FOREIGN KEY (Curator)
REFERENCES TEACHER(TchPK);
ALTER TABLE SGROUP
ADD CONSTRAINT Dep_FK_Num_Uniq UNIQUE (DepFK, Num);
ALTER TABLE SGROUP
ADD CONSTRAINT Dep_FK_Curator_Uniq UNIQUE (DepFK, Curator);
/*Добавление ограничений в ROOM*/
ALTER TABLE ROOM
ADD CONSTRAINT Num_Building_Uniq UNIQUE (Num, Building);
/*Добавление ограничений в LECTURE */
ALTER TABLE LECTURE
ADD CONSTRAINT GrpFK_Day_Week_Lesson_Uniq UNIQUE (GrpFK, [Day], [Week], Lesson);
ALTER TABLE LECTURE
ADD CONSTRAINT TchFK_Day_Week_Lesson_Uniq UNIQUE (TchFK, [Day], [Week], Lesson);
--Lab 1.3 INSERT INTO, UPDATE
INSERT INTO FACULTY
VALUES (1, 'informatics',NULL,5, 500008.00)
INSERT INTO FACULTY
VALUES (2, 'economy', NULL, 3 , NULL)
INSERT INTO FACULTY
VALUES (3, 'linguistics', NULL, 4, NULL)
INSERT INTO DEPARTMENT VALUES
(1,1,'SE',NULL,5,20378.00),
(2,1,'CAD',NULL,5,21000.00),
(3,1,'DBMS',NULL,4,22000.00),
(4,2,'Accounts',NULL,3,NULL)
INSERT INTO TEACHER VALUES
(1,1,'Andrew','ассистент','2281319','01.02.2001',1250,80,NULL),
(2,1,'John','профессор','2281550 ','01.07.2001',1400,150,1),
(3,2,'Bill','преподаватель',NULL,'17.11.2002',1240,80,1),
(4,2,'Albert','ассистент',NULL,'11.11.2001',1260,100,4)
INSERT INTO SGROUP VALUES
(1,1,1,101,33,4,20),
(2,1,1,102,35,3,22),
(3,3,2,205,20,1,15),
(4,3,3,305,25,NULL,40),
(5,3,4,405,25,2,37)
INSERT INTO [SUBJECT] VALUES
(1,'pascal'),
(2,'C'),
(3,'OS'),
(4,'inernet'),
(5,'dbms')
INSERT INTO ROOM VALUES
(1,101,20,1,5),
(2,316,150,3,5),
(3,201,150,2,2),
(4,202,30,2,5)
INSERT INTO LECTURE VALUES
(1,1,1,1,'лекция','пон',1,1),
(1,2,2,1,'лабораторная','пон',1,2),
(2,3,3,1,'лекция','втр',1,3),
(2,4,4,2,'практика','срд',1,3),
(4,4,5,2,'практика','чет',2,4),
(4,4,5,3,'семинар','пят',2,1)
/*ABOUT NATIONAL AVIATION UNIVERSITY*/
INSERT INTO FACULTY VALUES
(4,'ФККПІ', NULL,6, 1000000.00)
INSERT INTO DEPARTMENT VALUES
(5,4,'Кафедра інженеріїї програмного забезпечення',NULL,6,21000.00)
INSERT INTO TEACHER VALUES
(5,5,'Азаренко Олена Василівна','профессор','880005553535','01.02.2010',2000,80,NULL),
(6,5,'Писарчук Олексій Олександрович','доцент','123456','01.02.2009',1900,50,5),
(7,5,'Безкоровайна Юлія Миколаївна','преподаватель','1234567','01.02.2001',1900,50,6),
(8,5,'Белозерова Яна Андріївна','преподаватель','12345678','01.02.2010',1800,80,7)
UPDATE FACULTY
SET DeanFK = 5
WHERE FacPK = 4
UPDATE DEPARTMENT
SET HeadFK = 6
WHERE DepPK = 5
INSERT INTO SGROUP VALUES
(6,5,3,320,28,NULL,80)
INSERT INTO [SUBJECT] VALUES
(6,'Documents'),
(7,'SQL'),
(8,'Visualization'),
(9,'Artificial Intelligence'),
(10,'Software Design')
INSERT INTO ROOM VALUES
(5,6200,180,2,6),
(6,6201,150,2,6),
(7,6313,30,3,6),
(8,6311,30,3,6)
INSERT INTO LECTURE VALUES
(7,6,6,5,'лекция','пон',1,1),
(8,6,6,8,'лабораторная','втр',1,2)
INSERT INTO TEACHER VALUES
(9,1,'Bob','профессор','2281319','01.02.2001',1250,80,NULL)
UPDATE FACULTY
SET DeanFK=9,Fund=346700.00
WHERE [Name] = 'economy'
INSERT INTO TEACHER VALUES
(10,1,'Frank','профессор','2281319','01.02.2001',1100,80,9)
UPDATE DEPARTMENT
SET HeadFK=10, Building=3
WHERE DepPK =3
UPDATE TEACHER
SET Commission = Salary*0.25
WHERE Post = 'ассистент'
UPDATE SGROUP
SET Rating = 0
WHERE Course = 1
UPDATE SUBJECT
SET [Name] = 'html'
WHERE [Name] = 'internet'