G3HTCPsi

1drop table if exists teacher cascade;
2drop table if exists strain cascade;
3drop table if exists thing cascade;
4drop table if exists pulpit cascade;
5drop table if exists group_of_student cascade;
6drop table if exists post cascade;
7drop table if exists academic_title cascade;
8drop table if exists clock cascade;
9
10drop sequence if exists teacher_ids cascade;
11drop sequence if exists strain_ids cascade;
12drop sequence if exists thing_ids cascade;
13drop sequence if exists pulpit_ids cascade;
14drop sequence if exists group_of_student_ids cascade;
15drop sequence if exists post_ids cascade;
16drop sequence if exists academic_title_ids cascade;
17drop sequence if exists clock_ids cascade;
18drop function if exists delete_strain();
19
20drop view if exists docent;
21drop view if exists rich_teacher;
22drop view if exists Dep_Design;
23
24
25--Таблицы
26
27--количество часов
28create sequence clock_ids;
29create table if not exists clock
30(
31  id              int primary key default nextval('clock_ids'),
32  number_of_hours int not null
33);
34
35
36--Группа
37create sequence group_of_student_ids;
38create table if not exists group_of_student
39(
40  id           int primary key default nextval('group_of_student_ids'),
41  number_group varchar(64) not null,
42  specialty    varchar(64) not null
43);
44
45
46--Кафедра
47create sequence pulpit_ids;
48create table if not exists pulpit
49(
50  id          int primary key default nextval('pulpit_ids'),
51  name_pulpit varchar(64) not null
52);
53
54
55--Должность
56create sequence post_ids;
57create table if not exists post
58(
59  id        int primary key default nextval('post_ids'),
60  post_name varchar(50) not null
61);
62
63
64--Научная степень
65create sequence academic_title_ids;
66create table if not exists academic_title
67(
68  id          int primary key default nextval('academic_title_ids'),
69  name_degree varchar(64) not null
70);
71
72
73--Преподаватель
74create sequence teacher_ids;
75create table if not exists teacher
76(
77  id                int primary key default nextval('teacher_ids'),
78  name              varchar(64) not null,
79  gender            varchar(64) not null,
80  adress            varchar(64) not null,
81  family_camp       varchar(64) not null,
82  salary            int         not null,
83  pulpit_id         int         not null,
84  post_id           int         not null,
85  academic_title_id int,
86  foreign key (pulpit_id) references pulpit (id),
87  foreign key (post_id) references post (id),
88  foreign key (academic_title_id) references academic_title (id)
89);
90
91
92--Предмет
93create sequence thing_ids;
94create table if not exists thing
95(
96  id                  int primary key default nextval('thing_ids'),
97  name_thing          varchar(64) not null,
98  clock_id            int         not null,
99  group_of_student_id int         not null,
100  foreign key (group_of_student_id) references group_of_student (id),
101  foreign key (clock_id) references clock (id)
102);
103
104
105--Нагрузка
106create sequence strain_ids;
107create table if not exists strain
108(
109  id          int primary key default nextval('strain_ids'),
110  type_strain varchar(64) not null,
111  teacher_id  int         not null,
112  thing_id    int         not null,
113  foreign key (teacher_id) references teacher (id),
114  foreign key (thing_id) references thing (id)
115);
116
117-- Триггеры:
118
119-- 1 Триггер проверки формата номера группы
120create or replace function validate_group() returns trigger as
121$$
122begin
123  IF (new.number_group not like 'A%-%%%') THEN
124    RAISE EXCEPTION 'Wrong group!';
125    return null;
126  end if;
127  return new;
128end;
129$$ language 'plpgsql';
130
131create trigger t_validate_group
132  before insert or update
133  on group_of_student
134  for each row
135execute procedure validate_group();
136
137-- 2 При удалении препода, удаляет все его нагрузки
138create or replace function del_teacher() returns trigger as
139$$
140begin
141  delete from strain where teacher_id = old.id;
142end;
143$$ language 'plpgsql';
144
145create trigger t_del_teacher
146  before delete
147  on teacher
148  for each row
149execute procedure del_teacher();
150
151-- 3 Триггер проверки зарплаты
152-- (у доктора наук должна быть больше
153-- средней зарплаты кандидатов в доктора)
154create or replace function check_salary() returns trigger as
155$$
156begin
157  IF (new.academic_title_id = (select id from academic_title where name_degree = 'PhD')) THEN
158    return new;
159  end if;
160  IF (new.salary > (select avg(salary)
161                    from teacher
162                    where academic_title_id = (select id from academic_title where name_degree = 'PhD'))) THEN
163  return new;
164  else
165    RAISE exception 'Salary is too small';
166    return null;
167  end if;
168end;
169$$ language 'plpgsql';
170
171create trigger t_check_salary
172  before insert or update
173  on teacher
174  for each row
175execute procedure check_salary();
176
177-- 4 Проверить, добавлен ли предмет к академ группе
178create or replace function check_thing() returns trigger as
179$$
180begin
181  IF exists(select * from thing where name_thing = new.name_thing and group_of_student_id = new.group_of_student_id) THEN
182    RAISE EXCEPTION 'Group already has this thing to learn';
183    return null;
184  end if;
185  return new;
186end;
187$$ language 'plpgsql';
188
189create trigger t_check_thing
190  before insert
191  on thing
192  for each row
193execute procedure check_thing();
194
195-- 5 Проверить нет ли такой нагрузки по такому предмету
196create or replace function check_strain() returns trigger as
197$$
198begin
199  IF exists(select * from strain where type_strain = new.type_strain and thing_id = new.thing_id) THEN
200    RAISE EXCEPTION 'Такая нагрузка по данному предмету уже была добавлена';
201    return null;
202  end if;
203  return new;
204end;
205$$ language 'plpgsql';
206
207create trigger t_check_strain
208  before insert
209  on strain
210  for each row
211execute procedure check_strain();
212
213
214--Ввод данных
215
216--Группа
217insert into group_of_student
218values (nextval('group_of_student_ids'), 'AI-173', 'IT-developer');
219
220insert into group_of_student
221values (nextval('group_of_student_ids'), 'AI-174', 'IT-developer');
222
223insert into group_of_student
224values (nextval('group_of_student_ids'), 'AE-165', 'Web-desiqner');
225
226
227--Кафеда
228insert into pulpit
229values (nextval('pulpit_ids'), 'Department of Computer Science');
230
231insert into pulpit
232values (nextval('pulpit_ids'), 'Department of Design');
233
234
235--Должность
236insert into post
237values (nextval('post_ids'), 'docent');
238
239insert into post
240values (nextval('post_ids'), 'teacher');
241
242insert into post
243values (nextval('post_ids'), 'assistant');
244
245
246--Ученное звание
247insert into academic_title
248values (nextval('academic_title_ids'), 'PhD');--кандидат наук
249
250insert into academic_title
251values (nextval('academic_title_ids'), 'Ph.D');--доктор наук
252
253
254--Учителя
255insert into teacher
256values (nextval('teacher_ids'), 'Petrovich Aleksandr Aleksandrovich', 'male', 'Zabolotnogo 54', 'married', 10500, 1, 1,
257        1);
258
259insert into teacher
260values (nextval('teacher_ids'), 'Dmitrenko Sergey Olkseevich', 'male', 'Deribasovskaya 14 ', 'not married', 750000, 2, 2,
261        2);
262
263insert into teacher
264values (nextval('teacher_ids'), 'Vorotnyak Elena Viktorovna', 'female', 'Kanatnaya 25', 'not married', 20500, 2, 2,
265        null);
266
267
268--Количество часов
269insert into clock
270values (nextval('clock_ids'), 5);
271
272insert into clock
273values (nextval('clock_ids'), 10);
274
275insert into clock
276values (nextval('clock_ids'), 15);
277
278
279--Предмет
280insert into thing
281values (nextval('thing_ids'), 'Computer science', 1, 1);
282
283insert into thing
284values (nextval('thing_ids'), 'Web-development', 2, 3);
285
286insert into thing
287values (nextval('thing_ids'), 'OOP', 3, 2);
288
289
290--Нагрузка
291insert into strain
292values (nextval('strain_ids'), 'lecture', 1, 1);
293
294
295insert into strain
296values (nextval('strain_ids'), 'course work', 2, 2);
297
298insert into strain
299values (nextval('strain_ids'), 'exam', 3, 3);
300
301
302--Вывод таблиц--
303
304--1
305-- select * from group_of_student;
306--2
307-- select * from  pulpit;
308--3
309-- select * from post;
310--4
311-- select * from academic_title;
312--5
313-- select * from teacher;
314--6
315-- select * from clock;
316--7
317-- select * from thing;
318--8
319-- select * from strain;
320
321
322--Запросы
323
324--1
325-- select name_thing from thing where clock_id =1;--вывод предмета,которыц имеет кол-во часов =5
326--2
327-- select * from pulpit where name_pulpit ='Department of Computer Science';
328--3
329-- select * ,salary from teacher order by (salary) desc ;
330--4
331-- select * from group_of_student where number_group like 'AI%';
332--5
333-- select * from teacher order by name  ;
334--6
335-- select AVG(salary) as salaryAVG from teacher;--средняя зарплата преподователей
336--7
337-- select max(salary) from teacher;
338--8
339-- select min(salary) from teacher;
340--9
341-- select  * from group_of_student where number_group like'AI%' and  specialty like 'IT-developer%';
342--10
343-- select * from  teacher where pulpit_id in (1,2);
344--11
345-- select * from thing where clock_id between 2 and 3;
346--12
347-- select  count(*) from teacher;
348--13
349-- select sum(salary) from teacher ;
350
351
352--Подзапросы
353
354--1
355-- select name from teacher where id in(select teacher_id from strain where thing_id=(select id from thing where name_thing='OOP'));
356-- select name from teacher where id in(select teacher_id from strain where thing_id=(select id from thing where name_thing='Computer science'));
357-- select name from teacher where id in(select teacher_id from strain where thing_id=(select id from thing where name_thing='Web-development'));
358--2
359-- select * from teacher where academic_title_id =(select id from academic_title  where id='1');--кандидат наук
360-- select * from teacher where academic_title_id =(select id from academic_title  where id='2');--доктор наук
361--3
362-- select * from teacher where id in(select id from strain where  type_strain='course work');
363-- select * from teacher where id in(select id from strain where  type_strain='lecture');
364-- select * from teacher where id in(select id from strain where  type_strain='exam');
365--4
366-- select * from teacher where exists(select id from pulpit where id = 3);--так как поле пустое ,то подзапрос ничего не выведет
367
368
369--Триггеры/Функции
370
371
372--Представление
373--1
374create view docent as
375select *
376from teacher
377where id in (select id from post where post_name = 'docent');
378-- select * from docent;
379--2
380create view rich_teacher as
381select *
382from teacher
383where salary > 10500;
384-- select * from rich_teacher;
385--3
386create view Dep_Design as
387select *
388from teacher
389where id in (select teacher_id
390             from strain
391             where pulpit_id = (select id from pulpit where name_pulpit = 'Department of Design'));
392-- select * from Dep_Design;
393
394
395--Пользователи
396-- create user administrator;
397-- create user client;
398
399-- grant all on all tables in schema public to admimistrator;
400-- grant select on all tables in schema  to public;