7zeSUADm

1---------------------------------------------------------
2-- ДРОПЫ В ЗАПИСКУ НЕ ВСТАВЛЯТЬ
3---------------------------------------------------------
4DROP SEQUENCE IF EXISTS ПолПослед CASCADE;
5DROP SEQUENCE IF EXISTS СемейноеПоложениеПослед CASCADE;
6DROP SEQUENCE IF EXISTS КафедраПослед CASCADE;
7DROP SEQUENCE IF EXISTS ПредметПослед CASCADE;
8DROP SEQUENCE IF EXISTS ПреподавательПослед CASCADE;
9DROP SEQUENCE IF EXISTS ТипНагрузкиПослед CASCADE;
10DROP SEQUENCE IF EXISTS НагрузкиПослед CASCADE;
11
12
13DROP TABLE IF EXISTS Пол CASCADE;
14DROP TABLE IF EXISTS СемейноеПоложение CASCADE;
15DROP TABLE IF EXISTS Кафедры CASCADE;
16DROP TABLE IF EXISTS Предметы CASCADE;
17DROP TABLE IF EXISTS Преподаватели CASCADE;
18DROP TABLE IF EXISTS ТипыНагрузки CASCADE;
19DROP TABLE IF EXISTS Нагрузки CASCADE;
20
21
22DROP FUNCTION IF EXISTS ОбщНагруз();
23DROP FUNCTION IF EXISTS Кураторство();
24DROP FUNCTION IF EXISTS УдалитьНагрузки();
25
26
27DROP FUNCTION IF EXISTS РассчётСуммарнойНагрузки(код_преподавателя int);
28DROP FUNCTION IF EXISTS РассчётНалога(код_преподавателя int);
29
30---------------------------------------------------------
31-- А ВОТ ВСЁ, ЧТО НИЖЕ - ВСТАВЛЯТЬ
32---------------------------------------------------------
33
34
35--Пол препода
36CREATE SEQUENCE IF NOT EXISTS ПолПослед;
37CREATE TABLE IF NOT EXISTS Пол
38(
39  Код int primary key default nextval('ПолПослед'),
40  Тип varchar(64) NOT NULL
41);
42
43--Семейное положение
44CREATE SEQUENCE IF NOT EXISTS СемейноеПоложениеПослед;
45CREATE TABLE IF NOT EXISTS СемейноеПоложение
46(
47  Код       int primary key default nextval('СемейноеПоложениеПослед'),
48  Положение varchar(64) NOT NULL
49);
50
51--Кафедра
52CREATE SEQUENCE IF NOT EXISTS КафедраПослед;
53CREATE TABLE IF NOT EXISTS Кафедры
54(
55  Код      int primary key default nextval('КафедраПослед'),
56  Название varchar(128) NOT NULL
57);
58
59--Предмет
60CREATE SEQUENCE IF NOT EXISTS ПредметПослед;
61CREATE TABLE IF NOT EXISTS Предметы
62(
63  Код      int primary key default nextval('ПредметПослед'),
64  Название varchar(128) NOT NULL
65);
66
67--Препод
68CREATE SEQUENCE IF NOT EXISTS ПреподавательПослед;
69CREATE TABLE IF NOT EXISTS Преподаватели
70(
71  Код                   int primary key default nextval('ПреподавательПослед'),
72  ФИО                   varchar(128) NOT NULL,
73  КодПол                int          NOT NULL, --Мужской, женский или не определился?
74  Адрес                 varchar(256),
75  КодСемейногоПоложения int,
76  КодКафедры            int          NOT NULL,
77  НомерГруппы           char(6),
78  Кураторство           boolean,
79  foreign key (КодКафедры) references Кафедры (Код),
80  foreign key (КодПол) references Пол (Код),
81  foreign key (КодСемейногоПоложения) references СемейноеПоложение (Код)
82);
83
84--Тип академической нагрузки
85CREATE SEQUENCE IF NOT EXISTS ТипНагрузкиПослед;
86CREATE TABLE IF NOT EXISTS ТипыНагрузки
87(
88  Код          int primary key default nextval('ТипНагрузкиПослед'),
89  НазваниеТипа varchar(64) NOT NULL,
90  КоефНагрузки int         not null
91);
92
93--Академические нагрузки
94CREATE SEQUENCE IF NOT EXISTS НагрузкиПослед;
95CREATE TABLE IF NOT EXISTS Нагрузки
96(
97  Код              int primary key default nextval('НагрузкиПослед'),
98  КодТипа          int NOT NULL,
99  КодПреподавателя int NOT NULL,
100  КодПредмета      int NOT NULL,
101  foreign key (КодТипа) references ТипыНагрузки (Код),
102  foreign key (КодПреподавателя) references Преподаватели (Код),
103  foreign key (КодПредмета) references Предметы (Код)
104);
105
106--Триггер куратор
107create function Кураторство() returns trigger as
108$$
109begin
110  IF new.НомерГруппы is not null THEN
111    new.Кураторство = true;
112  else
113    new.Кураторство = false;
114  end if;
115  RETURN NEW;
116end;
117$$ language 'plpgsql';
118
119create trigger куратор_ли
120  before insert
121  on Преподаватели
122  for each row
123execute procedure Кураторство();
124
125
126-- Проверка, чтобы преподаватель не перетрудился
127create function ОбщНагруз() returns trigger as
128$$
129begin
130  IF РассчётСуммарнойНагрузки(new.Код) > 3 THEN
131    RETURN NULL;
132  end if;
133  RETURN NEW;
134end;
135$$ language 'plpgsql';
136
137create trigger провер_общ_нагруз
138  before insert
139  on Нагрузки
140  for each row
141execute procedure ОбщНагруз();
142
143-- Подсчёт нагрузки на преподавателя
144CREATE FUNCTION РассчётСуммарнойНагрузки(код_преподавателя int) returns float as
145$$
146declare
147  общ_нагруз float;
148  строка     record;
149begin
150  общ_нагруз := 0;
151  for строка in
152    select КодТипа from Нагрузки where КодПреподавателя = код_преподавателя
153    loop
154      общ_нагруз := общ_нагруз + (select КоефНагрузки from ТипыНагрузки where Код = строка.КодТипа);
155    end loop;
156  return общ_нагруз;
157end;
158$$ language 'plpgsql';
159
160-- Рассчёт зарплаты преподавателя в зависимости от его нагрузки
161  -- Премия каждый месяц, если преподаватель ведёт больше одного предмета
162CREATE FUNCTION РассчётНалога(код_преподавателя int) returns float as
163$$
164declare
165  налог float;
166  зарплата float;
167begin
168  зарплата := 10 * РассчётСуммарнойНагрузки(код_преподавателя);
169  IF (код_преподавателя in (select Код from ШирокийПрофиль)) THEN
170    зарплата := зарплата + 1900;
171  end if;
172  IF ((select Кураторство from Преподаватели where Код = код_преподавателя) = true) THEN
173    зарплата := зарплата + 1700;
174  end if;
175  налог := (зарплата/100) * 18;
176
177  return налог;
178end;
179$$ language 'plpgsql';
180
181
182create or replace function УдалитьНагрузки() returns trigger as
183$$
184begin
185  delete from Нагрузки where (КодПреподавателя = old.Код);
186end;
187$$ language 'plpgsql';
188
189create trigger удал_нагруз
190  before delete
191  on Преподаватели
192  for each row
193execute procedure УдалитьНагрузки();
194
195
196-- Преподаватели, которые ведут больше одного предмета
197CREATE VIEW ШирокийПрофиль AS
198select *
199from Преподаватели
200where ((select count(*) from Нагрузки where Преподаватели.Код = Нагрузки.КодПреподавателя) > 2);
201
202
203-- Преподаватели, которые выставлят зачёт или принимают экзамен:
204CREATE VIEW Экзаменаторы AS
205SELECT *
206from Преподаватели
207where Код in (select КодПреподавателя
208              from Нагрузки
209              where КодТипа in (select Код from ТипыНагрузки where НазваниеТипа = 'Зачёт' or НазваниеТипа = 'Экзамен'));
210
211-- Кого поздравить с 8 марта
212CREATE VIEW С8Марта AS
213select *
214from Преподаватели
215where КодПол in (select Код from Пол where Тип = 'Женский' or Тип = 'Не определился');
216
217
218
219INSERT INTO Пол
220VALUES (default, 'Мужской');
221INSERT INTO Пол
222VALUES (default, 'Женский');
223INSERT INTO Пол
224VALUES (default, 'Не определился');
225INSERT INTO СемейноеПоложение
226VALUES (default, 'Не в браке');
227INSERT INTO СемейноеПоложение
228VALUES (default, 'В браке');
229INSERT INTO СемейноеПоложение
230VALUES (default, 'Разведён');
231
232INSERT INTO Кафедры
233VALUES (default, 'Кафедра Информационных систем');
234INSERT INTO Кафедры
235VALUES (default, 'Кафедра Системного программного обеспечения');
236
237INSERT INTO Предметы
238VALUES (default, 'Организация баз данных и знаний');
239INSERT INTO Предметы
240VALUES (default, 'Алгоритмизация и программирование');
241INSERT INTO Предметы
242VALUES (default, 'Украинский язык');
243
244INSERT INTO Преподаватели
245VALUES (default, 'Андрей Петрович', (SELECT Код
246                                     FROM Пол
247                                     WHERE Тип
248                                       = 'Не определился'), 'ул. Канатная, 74',
249        (SELECT Код FROM СемейноеПоложение WHERE Положение = 'В браке'),
250        (SELECT Код FROM Кафедры WHERE Название = 'Кафедра Информационных систем'), 'АИ-173');
251
252INSERT INTO ТипыНагрузки
253VALUES (default, 'Лекция', 80);
254INSERT INTO ТипыНагрузки
255VALUES (default, 'Практическая работа', 60);
256INSERT INTO ТипыНагрузки
257VALUES (default, 'Лабораторная работа', 50);
258INSERT INTO ТипыНагрузки
259VALUES (default, 'Проверка курсовой работы', 80);
260INSERT INTO ТипыНагрузки
261VALUES (default, 'Зачёт', 90);
262INSERT INTO ТипыНагрузки
263VALUES (default, 'Экзамен', 100);
264
265INSERT INTO Нагрузки
266VALUES (default, 5, 1, 2);
267
268insert into Нагрузки (КодТипа, КодПреподавателя, КодПредмета)
269values (6, 1, 1);
270
271insert into Нагрузки (КодТипа, КодПреподавателя, КодПредмета)
272values (1, 1, 1);
273
274insert into Нагрузки (КодТипа, КодПреподавателя, КодПредмета)
275values (1, 1, 2);
276
277insert into Нагрузки (КодТипа, КодПреподавателя, КодПредмета)
278values (1, 1, 3);
279
280select РассчётСуммарнойНагрузки(1);
281select РассчётНалога(1);