JyRXrufC

1create database lab3;
2
3use lab3;
4
5create table Faculty
6(
7	FacPK int, 
8	Name varchar(50) unique not null, 
9	DeanFK int, 
10	Building char(2), 
11	Fund float(9,2),
12	PRIMARY KEY(FacPK)
13	/*CONSTRAINT checkBuildingFac check(Building in('1','2','3','4','5','6','7','8','9','10')),
14	CONSTRAINT checkFundFac check(Fund > 100000.00)*/
15);
16
17create table Department
18(
19	DepPK int,
20	FacFK int,
21	Name varchar(50) not null,
22	HeadFK int,
23	Building char(3),
24	Fund float(8,2),
25	PRIMARY KEY(DepPK),
26	FOREIGN KEY(FacFK) REFERENCES Faculty(FacPK) ON DELETE RESTRICT,
27	/*CONSTRAINT checkBuildingDep check(Building in('1','2','3','4','5','6','7','8','9','10')),
28	CONSTRAINT checkFundDep check(Fund BETWEEN 20000.00 and 100000.00),*/
29	CONSTRAINT uniqueDep unique(FacFK,Name)
30);
31
32create table Teacher
33(
34	TchPK int,
35	DepFK int,
36	Name varchar(50) NOT NULL,
37	Post varchar(15),
38	Tel char(7),
39	Hiredate date NOT NULL,
40	Salary float(6,2) NOT NULL,
41	Commission float(6,2) DEFAULT 0,
42	ChiefFK int,
43	PRIMARY KEY(TchPK),
44	FOREIGN KEY(DepFK) REFERENCES Department(DepPK) ON DELETE SET NULL,
45	FOREIGN KEY(ChiefFK) REFERENCES Teacher(TchPK) ON DELETE SET NULL
46	/*CONSTRAINT checkPost check(Post in('assistant','professor','docent','teacher')),
47	CONSTRAINT checkDate check(Hiredate > '1950-01-01'),
48	CONSTRAINT checkSalary check(Salary > 1000),
49	CONSTRAINT checkCommis check(Commission >= 0),
50	CONSTRAINT checkComSalary check(Commission < (Salary/2)),
51	CONSTRAINT checkCommisSalary check((Commission+Salary) >= 1000 AND (Commission+Salary) <= 3000),
52	CONSTRAINT checkChiefNOTTchPK check(ChiefFK <> TchPK)*/
53);
54
55create table Sgroup
56(
57	GrpPK int,
58	DepFK int,
59	Course int(1),
60	Num int(3),
61	Quantity int(2),
62	Curator int,
63	Rating int(3) default 0,
64	PRIMARY KEY(GrpPK),
65	FOREIGN KEY(DepFK) REFERENCES Department(DepPK) ON DELETE SET NULL,
66	FOREIGN KEY(Curator) REFERENCES Teacher(TchPK) ON DELETE SET NULL,
67	/*CONSTRAINT checkCourse check(Course in('1','2','3','4','5','6')),
68	CONSTRAINT checkNum check(Num > 0 and num < 700),
69	CONSTRAINT checkQuantity check(Quantity between 1 and 50),
70	CONSTRAINT checkRating check(Rating between 0 and 100),*/
71	CONSTRAINT uniqueDefFKNum unique(DepFK,Num),
72	CONSTRAINT uniqueDefFKCurator unique(DepFK,Curator)
73);
74
75create table Subject
76(
77	SbjPK int,
78	Name varchar(50) unique not null,
79	PRIMARY KEY(SbjPK)
80);
81
82
83create table Room
84(
85	RomPK int,
86	Num int(4) not null,
87	Seats int(3),
88	Floor int(2),
89	Building char(5) not null,
90	PRIMARY KEY(RomPK),
91	/*CONSTRAINT checkBuildingRoom check(Building in('1','2','3','4','5','6','7','8','9','10')),
92	CONSTRAINT checkFloorRoom check(Floor BETWEEN 1 and 16),
93	CONSTRAINT checkSeatsRoom check(Seats BETWEEN 1 and 300),*/
94	CONSTRAINT uniqueNumBuilding unique(Num,Building)
95);
96
97create table Lecture
98(
99	TchFK int,
100	GrpFK int,
101	SbjFK int,
102	RomFK int,
103	Type varchar(15) not null,
104	Day char(3) not null,
105	Week int(1) not null,
106	Lesson int(1) not null,
107	foreign key(TchFK) references Teacher(TchPK) on delete set null,
108	foreign key(GrpFK) references sgroup(GrpPK) on delete cascade,
109	foreign key(SbjFK) references subject(SbjPK) on delete restrict,
110	foreign key(RomFK) references room(RomPK) on delete set null,
111	/*CONSTRAINT checkType check(Type in('lecture','laboratory','seminar','practice')),
112	CONSTRAINT checkDay check(Day in('mon','tue','wed','thu','fri','sat','sun')),
113	CONSTRAINT checkWeek check(Week in(1,2)),
114	CONSTRAINT checkLesson check(Lesson between 1 and 8),*/
115	CONSTRAINT uniqueLecure1 unique(GrpFK, Day, Week, Lesson),
116	CONSTRAINT uniqueLecture2 unique(TchFK, Day, Week, Lesson)
117);
118
119alter table faculty add foreign key(DeanFK) references teacher(TchPK) on delete set null;
120alter table department add foreign key(HeadFK) references teacher(TchPK) on delete set null;
121
122
123
124
125DROP TRIGGER IF EXISTS Faculty_before_insert;
126DROP TRIGGER IF EXISTS Department_before_insert;
127DROP TRIGGER IF EXISTS Teacher_before_insert;
128DROP TRIGGER IF EXISTS Room_before_insert;
129DROP TRIGGER IF EXISTS Lecture_before_insert;
130DROP TRIGGER IF EXISTS Sgroup_before_insert;
131
132DELIMITER ???
133CREATE TRIGGER Faculty_before_insert BEFORE INSERT ON Faculty 
134FOR EACH ROW BEGIN 
135	IF (!(NEW.Building in('1','2','3','4','5','6','7','8','9','10'))) THEN 
136		SIGNAL SQLSTATE "10001"
137		SET MESSAGE_TEXT = "Builing isn't between 1 and 10.";
138	END IF;
139	
140	IF (NEW.Fund < 100000.00) THEN 
141		SIGNAL SQLSTATE "10002"
142		SET MESSAGE_TEXT = "Fund should be 100,000.00 as minimum value.";
143	END IF;
144END;
145???
146
147DELIMITER ???
148CREATE TRIGGER Department_before_insert BEFORE INSERT ON Department 
149FOR EACH ROW BEGIN 
150	IF (!(NEW.Building in('1','2','3','4','5','6','7','8','9','10'))) THEN 
151		SIGNAL SQLSTATE "20001"
152		SET MESSAGE_TEXT = "Builing isn't between 1 and 10.";
153	END IF;
154	
155	IF (NEW.Fund < 20000.00 AND NEW.Fund > 100000.00) THEN 
156		SIGNAL SQLSTATE "20002"
157		SET MESSAGE_TEXT = "Fund should be more than 20,000.00 and less than 100,000.00.";
158	END IF;
159END;
160???
161
162DELIMITER ???
163CREATE TRIGGER Teacher_before_insert BEFORE INSERT ON Teacher 
164FOR EACH ROW BEGIN 
165	IF (!(NEW.Commission <= (NEW.Salary / 2) AND NEW.Commission > 0)) THEN 
166		SIGNAL SQLSTATE "30001"
167		SET MESSAGE_TEXT = "Comission should be less than the half-salary and more than a zero.";
168	END IF;
169	IF (!(NEW.Post in ("assistant", "professor", "docent", "teacher"))) THEN 
170		SIGNAL SQLSTATE "30002"
171		SET MESSAGE_TEXT = "Post should be one of the list: assistant, professor, docent, teacher.";
172	END IF;
173	IF (!(NEW.Hiredate > '1950-01-01')) THEN 
174		SIGNAL SQLSTATE "30003"
175		SET MESSAGE_TEXT = "Hiredate can't be less then 01.01.1950.";
176	END IF;
177	IF (!(NEW.Salary > 1000 AND NEW.Salary < 3000)) THEN 
178		SIGNAL SQLSTATE "30004"
179		SET MESSAGE_TEXT = "Salary should be between 1000 and 3000.";
180	END IF;
181	IF (!(NEW.ChiefFK != NEW.TchPK)) THEN 
182		SIGNAL SQLSTATE "30005"
183		SET MESSAGE_TEXT = "Foreign key CHIEFFK can't be equal with primary key TCHPK.";
184	END IF;
185END;
186???
187
188DELIMITER ???
189CREATE TRIGGER Room_before_insert BEFORE INSERT ON Room 
190FOR EACH ROW BEGIN 
191	IF (!(NEW.Seats BETWEEN 1 AND 300)) THEN 
192		SIGNAL SQLSTATE "60001"
193		SET MESSAGE_TEXT = "Seats should be between 1 and 300.";
194	END IF;
195	
196	IF (!(NEW.Floor BETWEEN 1 AND 16)) THEN 
197		SIGNAL SQLSTATE "60002"
198		SET MESSAGE_TEXT = "Floor should be between 1 and 16.";
199	END IF;
200	
201	IF (!(NEW.Building in ('1','2','3','4','5','6','7','8','9','10'))) THEN 
202		SIGNAL SQLSTATE "60003"
203		SET MESSAGE_TEXT = "Building should be char between 1 and 10.";
204	END IF;
205END;
206???
207
208DELIMITER ???
209CREATE TRIGGER Sgroup_before_insert BEFORE INSERT ON Sgroup
210FOR EACH ROW BEGIN 
211	declare msg varchar(128);
212IF NEW.Course not in('1','2','3','4','5','6') then
213		set msg = 'MyTriggerError: Course not in(1,2,3,4,5,6)';
214        signal sqlstate '45000' set message_text = msg;
215		END IF;
216if (NEW.Num not between 0 and 700) then
217		set msg = 'MyTriggerError: Num not between 0 and 700';
218        signal sqlstate '45000' set message_text = msg;
219		END if;
220if (NEW.Quantity not between 0 and 50) then
221		set msg = 'MyTriggerError: Quantity not between 1 and 50';
222        signal sqlstate '45000' set message_text = msg;
223		END IF;
224if (NEW.Rating not between 0 and 100)then
225		set msg = 'MyTriggerError: Rating not between 0 and 100';
226        signal sqlstate '45000' set message_text = msg;
227		END IF;
228END;
229???
230
231
232DELIMITER ???
233CREATE TRIGGER Lecture_before_insert BEFORE INSERT ON Lecture 
234FOR EACH ROW BEGIN 
235	IF (!(NEW.Type in ("lecture", "laboratory", "seminar", "practice"))) THEN 
236		SIGNAL SQLSTATE "70001"
237		SET MESSAGE_TEXT = "Type should be in (\"lecture\", \"laboratory\", \"seminar\", \"practice\".";
238	END IF;
239	
240	IF (!(NEW.Day in ('mon','tue','wed','thu','fri','sat','sun'))) THEN 
241		SIGNAL SQLSTATE "70002"
242		SET MESSAGE_TEXT = "Type should be in ('mon','tue','wed','thu','fri','sat','sun').";
243	END IF;
244	
245	IF (!(NEW.week in (1,2))) THEN 
246		SIGNAL SQLSTATE "70003"
247		SET MESSAGE_TEXT = "Week should be or 0 or 1.";
248	END IF;
249	
250	IF (!(NEW.Lesson BETWEEN 1 AND 8)) THEN 
251		SIGNAL SQLSTATE "70004"
252		SET MESSAGE_TEXT = "Lesson should be between 1 and 8.";
253	END IF;
254	
255END;
256???
257
258DELIMITER ;
259
260
261
262
263DELETE FROM Department;
264DELETE FROM Faculty;
265
266INSERT INTO Faculty 
267			(FacPK, Name, Building, DeanFK, Fund)
268	VALUE	(1, 'NNIKIT', 6, NULL, 200000.00),
269			(2, "NNGI", 8, NULL, 150000.00),
270			(3, "NNIEM", 8, NULL, 150000.00);
271			
272INSERT INTO Department 
273			(DepPK, FacFK, Name, HeadFK, Building, Fund)
274	VALUE	(1, 1, "Software engineering", NULL, 6, 90000.00),
275			(2, 2, "Ukrainian language", NULL, 8, 50000.00),
276			(3, 2, "Philosophy", NULL, 8, 50000.00),
277			(4, 3, "Economy", NULL, 8, 50000.00);
278			
279INSERT INTO Teacher
280			(TchPK, DepFK, Name, Post, Tel, Hiredate, Salary, Commission, ChiefFK)
281	VALUE	(13, 1, "Udin O. K.", "professor", "1122343", "13.01.2000", 2999, 1400, NULL),
282			(14, 2, "Gudmanyan A. G.", "professor", "1122344", "14.01.2000", 2998, 1400, NULL),
283			(15, 3, "Aref'eva A. V.", "professor", "1122345", "15.01.2000", 2997, 1400, NULL),
284			(1, 1, "Sidoriv M. O.", "professor", "1122331", "01.01.2000", 2900, 500, 13),
285			(5, 1, "Popereshnyak S. V.", "docent", "1122335", "05.01.2000", 2500, 500, 1),
286			(11, 1, "Reznichenko V. A.", "docent", "1122341", "11.01.2000", 2000, 500, 1),
287			(2, 1, "Bezkorovainy U. M.", "assistant", "1122332", "02.01.2000", 2000, 500, 1),
288			(3, 1, "Grinenko O. O.", "assistant", "1122333", "03.01.2000", 2000, 500, 11),
289			(4, 1, "Nagornyak T. V.", "assistant", "1122334", "04.01.2000", 2000, 500, 5),
290			(6, 1, "Makaricheva V. V.", "assistant", "1122336", "06.01.2000", 2000, 500, 5),
291			(7, 1, "Kirhar N. V.", "docent", "1122337", "07.01.2000", 2000, 500, 1),
292			(8, 4, "Snezhko A. E.", "docent", "1122338", "08.01.2000", 2000, 500, 3),
293			(9, 3, "Poda T. E.", "docent", "1122339", "09.01.2000", 2000, 500, 2),
294			(10, 3, "Iskakova N. G.", "docent", "1122340", "10.01.2000", 2000, 500, 2),
295			(12, 2, "Senchilo N. O.", "docent", "1122342", "12.01.2000", 2000, 500, 2);
296
297UPDATE Faculty SET  DeanFK = 13 WHERE FacPK = 1;
298UPDATE Faculty SET  DeanFK = 14 WHERE FacPK = 2;
299UPDATE Faculty SET  DeanFK = 15 WHERE FacPK = 3;
300
301UPDATE Department SET  HeadFK = 1 WHERE DepPK = 1;
302UPDATE Department SET  HeadFK = 12 WHERE DepPK = 2;
303UPDATE Department SET  HeadFK = 9 WHERE DepPK = 3;
304UPDATE Department SET  HeadFK = 8 WHERE DepPK = 4;
305		
306INSERT INTO Subject 
307			(SbjPK, Name)
308	VALUE	(1, "Basics of economic theory"),
309			(2, "Philosophy"),
310			(3, "Fundamentals of Artificial Intelligence"),
311			(4, "Visualization Software"),
312			(5, "Database"),
313			(6, "Prof. Software Engineering Practice"),
314			(7, "Construction Software"),
315			(8, "Ukrainian language"),
316			(9, "Political science");
317
318INSERT INTO SGroup
319			(GrpPK, DepFK, Course, Num, Quantity, Curator, Rating) 
320	VALUE	(1, 1, 3, 315, 10, 2, 100);
321	
322INSERT INTO Room 
323			(RomPK, Num, Seats, Floor, Building)
324	VALUE 	(1, 1001, 20, 10, 8),
325			(2, 201, 200, 2, 5),
326			(3, 201, 200, 2, 6),
327			(4, 104, 15, 1, 6),
328			(5, 110, 15, 1, 6),
329			(6, 311, 15, 3, 6),
330			(7, 200, 200, 2, 6),
331			(8, 315, 15, 3, 6),
332			(9, 309, 20, 3, 3),
333			(10, 201, 250, 2, 4),
334			(11, 105, 200, 1, 8),
335			(12, 415, 150, 4, 1);
336			
337INSERT INTO Lecture
338			(TchFK, GrpFK, SbjFK, RomFK, Type, Day, Week, Lesson) 
339	VALUE	/* Неделя 2.
340			  Понедельник */
341			(8, 1, 1, 1, "practice", "mon", 2, 3),
342			(8, 1, 1, 2, "lecture", "mon", 2, 4),
343			(9, 1, 2, 3, "lecture", "mon", 2, 5),
344			/* Вторник */
345			(6, 1, 3, 4, "laboratory", "tue", 2, 2),
346			/* Среда */
347			(7, 1, 4, 5, "laboratory", "wed", 2, 4),
348			(3, 1, 5, 6, "laboratory", "wed", 2, 5),
349			(4, 1, 6, 4, "laboratory", "wed", 2, 6),			
350			/* Четверг */
351			(2, 1, 7, 7, "lecture", "thu", 2, 1),
352			(5, 1, 7, 11, "lecture", "thu", 2, 2),
353			(2, 1, 7, 8, "laboratory", "thu", 2, 3),
354			/* Пятница */
355			(12, 1, 8, 9, "practice", "fri", 2, 1),
356			(9, 1, 2, 9, "practice", "fri", 2, 2),
357			(11, 1, 9, 10, "lecture", "fri", 2, 3),
358			(11, 1, 5, 7, "lecture", "fri", 2, 4),
359			/* Неделя 1.
360			  Понедельник */
361			(6, 1, 3, 4, "laboratory", "mon", 1, 3),
362			(8, 1, 1, 10, "lecture", "mon", 1, 4),
363			(9, 1, 2, 3, "lecture", "mon", 1, 5),
364			/* Вторник */
365			(5, 1, 3, 2, "lecture", "tue", 1, 3),
366			/* Среда */
367			(10, 1, 9, 9, "lecture", "wed", 1, 2),
368			(3, 1, 5, 6, "laboratory", "wed", 1, 3),
369			(2, 1, 7, 5, "laboratory", "wed", 1, 4),
370			(4, 1, 6, 4, "laboratory", "wed", 1, 5),			
371			/* Четверг */
372			(2, 1, 7, 7, "lecture", "thu", 1, 1),
373			(5, 1, 6, 11, "lecture", "thu", 1, 2),
374			(3, 1, 5, 4, "laboratory", "thu", 1, 3),
375			/* Пятница */
376			(7, 1, 4, 12, "lecture", "fri", 1, 1),
377			(11, 1, 5, 7, "lecture", "fri", 1, 2),
378			(9, 1, 2, 9, "practice", "fri", 1, 3);