jQaC0FKP

1DROP SEQUENCE IF EXISTS s_countries CASCADE;
2DROP SEQUENCE IF EXISTS s_cities CASCADE;
3DROP SEQUENCE IF EXISTS s_streets CASCADE;
4DROP SEQUENCE IF EXISTS s_addresses CASCADE;
5DROP SEQUENCE IF EXISTS s_school_types CASCADE;
6DROP SEQUENCE IF EXISTS s_schools CASCADE;
7DROP SEQUENCE IF EXISTS s_concessions CASCADE;
8DROP SEQUENCE IF EXISTS s_departments CASCADE;
9DROP SEQUENCE IF EXISTS s_specialities CASCADE;
10DROP SEQUENCE IF EXISTS s_awards CASCADE;
11DROP SEQUENCE IF EXISTS s_secondary_education_types CASCADE;
12DROP SEQUENCE IF EXISTS s_abiturients CASCADE;
13DROP SEQUENCE IF EXISTS s_disciplines CASCADE;
14DROP SEQUENCE IF EXISTS s_zno_results CASCADE;
15DROP SEQUENCE IF EXISTS s_spec_disc CASCADE;
16
17DROP TABLE IF EXISTS countries CASCADE;
18DROP TABLE IF EXISTS cities CASCADE;
19DROP TABLE IF EXISTS streets CASCADE;
20DROP TABLE IF EXISTS addresses CASCADE;
21DROP TABLE IF EXISTS school_types CASCADE;
22DROP TABLE IF EXISTS schools CASCADE;
23DROP TABLE IF EXISTS concessions CASCADE;
24DROP TABLE IF EXISTS departments CASCADE;
25DROP TABLE IF EXISTS specialities CASCADE;
26DROP TABLE IF EXISTS awards CASCADE;
27DROP TABLE IF EXISTS secondary_education_types CASCADE;
28DROP TABLE IF EXISTS abiturients CASCADE;
29DROP TABLE IF EXISTS disciplines CASCADE;
30DROP TABLE IF EXISTS zno_results CASCADE;
31DROP TABLE IF EXISTS spec_disc CASCADE;
32
33
34------------------------------------------------------
35--      Дропы в записку курсача не вставлять!!!     --
36------------------------------------------------------
37
38--Страны
39CREATE SEQUENCE s_countries;
40CREATE TABLE countries
41(
42  id      int primary key default nextval('s_countries'),
43  country varchar(100)
44);
45
46--Города
47CREATE SEQUENCE s_cities;
48CREATE TABLE cities
49(
50  id   int primary key default nextval('s_cities'),
51  city varchar(100)
52);
53
54--Улицы
55CREATE SEQUENCE s_streets;
56CREATE TABLE streets
57(
58  id     int primary key default nextval('s_streets'),
59  street varchar(100)
60);
61
62--Адреса (собираем из таблиц выше)
63CREATE SEQUENCE s_addresses;
64CREATE TABLE addresses
65(
66  id         int primary key default nextval('s_addresses'),
67  country_id int not null,
68  city_id    int not null,
69  street_id  int not null,
70  build      int not null,
71  appartment int,
72  index      varchar(5),
73  foreign key (country_id) references countries (id),
74  foreign key (city_id) references cities (id),
75  foreign key (street_id) references streets (id)
76);
77
78--Типы школ (коледж/техникум/общеобразовательная и т.д.)
79CREATE SEQUENCE s_school_types;
80CREATE TABLE school_types
81(
82  id   int primary key default nextval('s_school_types'),
83  type varchar(100)
84);
85
86--Таблица школ
87CREATE SEQUENCE s_schools;
88CREATE TABLE schools
89(
90  id             int primary key default nextval('s_schools'),
91  num            int check ( num > 0 ),
92  address_id     int,
93  school_type_id int,
94  foreign key (address_id) references addresses (id),
95  foreign key (school_type_id) references school_types (id)
96);
97
98--Таблица возмодных льгот
99CREATE SEQUENCE s_concessions;
100CREATE TABLE concessions
101(
102  id   int primary key default nextval('s_concessions'),
103  type varchar(100)
104);
105
106--Факультет (специальность может быть представлена на разных)
107CREATE SEQUENCE s_departments;
108CREATE TABLE departments
109(
110  id         int primary key default nextval('s_departments'),
111  department varchar(100)
112);
113
114--Специальности
115CREATE SEQUENCE s_specialities;
116CREATE TABLE specialities
117(
118  id            int primary key default nextval('s_specialities'),
119  speciality    varchar(100),
120  department_id int,
121  foreign key (department_id) references departments (id)
122);
123
124
125--Награды (диплом с отличием/золотая медаль и т.д.)
126CREATE SEQUENCE s_awards;
127CREATE TABLE awards
128(
129  id    int primary key default nextval('s_awards'),
130  award varchar(100),
131  coef  int check ( coef > 0 )
132);
133
134--Типы среднего образования (полное среднее, неполное среднее, среднее специализированное и т.д.)
135CREATE SEQUENCE s_secondary_education_types;
136CREATE TABLE secondary_education_types
137(
138  id   int primary key default nextval('s_secondary_education_types'),
139  type varchar(100)
140);
141
142--Таблица абитуриентов
143CREATE SEQUENCE s_abiturients;
144CREATE TABLE abiturients
145(
146  id                          int primary key default nextval('s_abiturients'),
147  name                        varchar(40),
148  born                        date,
149  concession_id               int, --льготы
150  school_id                   int,
151  secondary_education_type_id int,
152  award_id                    int,
153  address_id                  int,
154  speciality_id               int,
155  average_score               float           default 0,
156  foreign key (concession_id) references concessions (id),
157  foreign key (school_id) references schools (id),
158  foreign key (secondary_education_type_id) references secondary_education_types (id),
159  foreign key (award_id) references awards (id),
160  foreign key (address_id) references addresses (id),
161  foreign key (speciality_id) references specialities (id)
162);
163
164--Таблица предметов, по которым проводится ЗНО
165CREATE SEQUENCE s_disciplines;
166CREATE TABLE disciplines
167(
168  id         int primary key default nextval('s_disciplines'),
169  discipline varchar(100)
170);
171
172--Результаты ЗНО по предмету на студента
173CREATE SEQUENCE s_zno_results;
174CREATE TABLE zno_results
175(
176  id            int primary key default nextval('s_zno_results'),
177  abiturient_id int,
178  discipline_id int,
179  score         int check ( score between 0 and 200),
180  foreign key (abiturient_id) references abiturients (id),
181  foreign key (discipline_id) references disciplines (id)
182);
183
184--Предметы для специальностей (сопоставляем предмет для сдачи экзамена и спецаильность)
185CREATE SEQUENCE s_spec_disc;
186CREATE TABLE spec_disc
187(
188  id            int primary key default nextval('s_spec_disc'),
189  speciality_id int,
190  discipline_id int,
191  foreign key (speciality_id) references specialities (id),
192  foreign key (discipline_id) references disciplines (id)
193);
194
195
196-- Триггерные функции
197
198-- 1 Автоматический перерасчёт среднего балла,
199  -- при добавлении в таблицу результатов
200create or replace function auto_update_score() returns trigger as
201$$
202begin
203  update abiturients set average_score = (select count_ave_score(new.abiturient_id)) where id = new.abiturient_id;
204  return new;
205end;
206$$ language 'plpgsql';
207
208create trigger t_auto_update_score
209  after insert or update
210  on zno_results
211  for each row
212execute procedure auto_update_score();
213
214-- 2 Проверить, чтобы у этого абитуриента не было 2 результата
215  -- по одному предмету ЗНО
216create or replace function check_zno() returns trigger as
217$$
218begin
219  if (exists(
220      select * from zno_results where (abiturient_id = new.abiturient_id and discipline_id = new.discipline_id))) then
221    raise exception 'У абитуриента уже добавлена информация по этому предмету';
222    return null;
223  end if;
224  return new;
225end;
226$$ language 'plpgsql';
227
228create trigger t_check_zno
229  before insert
230  on zno_results
231  for each row
232execute procedure check_zno();
233
234
235-- 3 Проверить, нет ли абитуриента с таким же ФИО и датой рождения:
236create or replace function check_abit() returns trigger as
237$$
238begin
239  if (exists(select * from abiturients where (name = new.name and born = new.born))) then
240    raise exception 'Ошибка! Такой пользователь уже добавлен';
241    return null;
242  else
243    return new;
244  end if;
245end;
246$$ language 'plpgsql';
247
248create trigger t_check_abit
249  before insert
250  on abiturients
251  for each row
252execute procedure check_abit();
253
254-- 4 Бонусные баллы к результатам ЗНО за награды
255create or replace function count_awards() returns trigger as
256$$
257declare
258  sc int;
259begin
260  select coef from awards where id in (select award_id from abiturients where id = new.id) into sc;
261  update zno_results set score = score + sc where abiturient_id = new.id;
262  return new;
263end;
264$$ language 'plpgsql';
265
266create trigger t_count_awards
267  after insert
268  on abiturients
269  for each row
270execute procedure count_awards();
271
272-- 5 Если по ЗНО балл < 100
273create or replace function not_enrolled() returns trigger as
274$$
275begin
276  if (new.score < 100) then
277    raise notice 'Проходной бал по данному предмету не набран';
278  end if;
279  return new;
280end;
281$$
282  language 'plpgsql';
283
284create trigger t_not_enrolled
285  after insert or update
286  on zno_results
287  for each row
288execute procedure not_enrolled();
289
290-- Функции
291
292-- 1 Рассчёт среднего балла студента по результатам ЗНО:
293create or replace function count_ave_score(abit_id int) returns float as
294$$
295begin
296  return (select avg(score) from zno_results where abiturient_id = abit_id);
297end;
298$$ language 'plpgsql';
299
300
301-- 2 Получить список предметов, для поступления на данную специальность:
302create or replace function get_disciplines(spec varchar(100), dep varchar(100)) returns table
303                                                                                        (
304                                                                                          discipline varchar(100)
305                                                                                        ) as
306$$
307declare
308  spec_id int;
309begin
310  select id
311  from specialities
312  where speciality = spec
313    and department_id = (select id from departments where department = dep) into spec_id;
314
315  return query (select disciplines.discipline
316                from disciplines
317                where id in (select discipline_id from spec_disc where speciality_id = spec_id));
318end;
319$$ language 'plpgsql';
320
321-- select get_disciplines('Менеджмент', 'ИБЕИТ');
322
323
324-- Функция, котороая возвращает таблицу поступивших в зависимости от колва мест на специальность
325  -- аргументы функции: специальность, колво мест, иностранцы (иностранцев - в отдельный рейтинг)
326create or replace function get_enrolled(spec varchar(100), dept varchar(100), places int,
327                                        foreigners boolean) returns table
328                                                                    (
329                                                                      id            int,
330                                                                      name          varchar(100),
331                                                                      born          date,
332                                                                      average_score float,
333                                                                      special       varchar(100),
334                                                                      depart        varchar(100)
335                                                                    ) as
336$$
337declare
338  spec_id int;
339begin
340  select specialities.id
341  from specialities
342  where (speciality = spec and
343         department_id = (select departments.id from departments where departments.department = dept)) into spec_id;
344
345  if (foreigners = true) then
346    return query select abiturients.id,
347                        abiturients.name,
348                        abiturients.born,
349                        abiturients.average_score,
350                        specialities.speciality,
351                        departments.department
352                 from abiturients,
353                      specialities,
354                      departments
355                 where departments.id = specialities.department_id
356                   and abiturients.speciality_id = specialities.id
357                   and abiturients.speciality_id = spec_id
358                   and abiturients.address_id in (select addresses.id
359                                                  from addresses
360                                                  where country_id <>
361                                                        (select countries.id from countries where country = 'Украина'))
362                 order by average_score
363                 limit places;
364  else
365    return query select abiturients.id,
366                        abiturients.name,
367                        abiturients.born,
368                        abiturients.average_score,
369                        specialities.speciality,
370                        departments.department
371                 from abiturients,
372                      specialities,
373                      departments
374                 where departments.id = specialities.department_id
375                   and abiturients.speciality_id = specialities.id
376                   and abiturients.speciality_id = spec_id
377                   and abiturients.address_id in (select addresses.id
378                                                  from addresses
379                                                  where country_id =
380                                                        (select countries.id from countries where country = 'Украина'))
381                 order by average_score
382                 limit places;
383  end if;
384end;
385$$ language 'plpgsql';
386
387-- select get_enrolled('Менеджмент', 'ИБЕИТ', 10, true);
388
389
390-- Представления
391
392-- Топ 10 абитуриентов с максимальными баллами
393create view best_abits as
394select *
395from abiturients
396order by average_score desc
397limit 10;
398
399-- Прдставление со студентами, которым надо будет предоставить
400-- общежитие (которые не из Одессы)
401create view need_hostel as
402select *
403from abiturients
404where address_id in (select id from addresses where city_id <> (select id from cities where city = 'Одесса'));
405
406-- Количество абитуриентов на каждую специальность
407create view spec_abit as
408select speciality_id, specialities.speciality, count(speciality_id) as students_count
409from abiturients,
410     specialities
411where speciality_id = specialities.id
412group by speciality_id, specialities.speciality
413order by students_count desc;
414
415
416-- Ввод данных
417insert into disciplines (discipline)
418values ('Физика');
419insert into disciplines (discipline)
420values ('Математика');
421insert into disciplines (discipline)
422values ('Украинский язык');
423
424insert into cities (city)
425values ('Одесса');
426insert into cities (city)
427values ('Киев');
428insert into cities (city)
429values ('Кировоград');
430
431insert into countries (country)
432values ('Украина');
433insert into countries (country)
434values ('Зимбабве');
435
436insert into streets (street)
437values ('ул. Маршала Говорова');
438insert into streets (street)
439values ('ул. Армейская');
440insert into streets (street)
441values ('ул. Ленина');
442
443insert into addresses (country_id, city_id, street_id, build, appartment, index)
444values (1, 1, 1, 11, 407, null);
445insert into addresses (country_id, city_id, street_id, build, appartment, index)
446values (1, 3, 2, 14, 22, null);
447insert into addresses (country_id, city_id, street_id, build, appartment, index)
448values (2, 2, 3, 29, 117, null);
449
450insert into school_types (type)
451values ('Общеобразовательная');
452insert into school_types (type)
453values ('Коллежд');
454insert into school_types (type)
455values ('С углублённым изучением английского');
456
457insert into secondary_education_types (type)
458values ('Полное');
459insert into secondary_education_types (type)
460values ('Неполное');
461insert into secondary_education_types (type)
462values ('Специализированное');
463
464insert into concessions (type)
465values ('Многодетная семья');
466insert into concessions (type)
467values ('Инвалид');
468insert into concessions (type)
469values ('Сирота');
470
471insert into departments (department)
472values ('ИКС');
473insert into departments (department)
474values ('ХТФ');
475insert into departments (department)
476values ('ИБЕИТ');
477
478insert into specialities (speciality, department_id)
479values ('Компьютерные науки', 1);
480insert into specialities (speciality, department_id)
481values ('Физическая химия', 2);
482insert into specialities (speciality, department_id)
483values ('Менеджмент', 3);
484
485insert into spec_disc (speciality_id, discipline_id)
486values (1, 3);
487insert into spec_disc (speciality_id, discipline_id)
488values (1, 2);
489insert into spec_disc (speciality_id, discipline_id)
490values (2, 2);
491insert into spec_disc (speciality_id, discipline_id)
492values (2, 3);
493insert into spec_disc (speciality_id, discipline_id)
494values (3, 2);
495insert into spec_disc (speciality_id, discipline_id)
496values (3, 3);
497
498insert into awards (award, coef)
499values ('Золотая медаль', 5);
500insert into awards (award, coef)
501values ('Серебряная медаль', 3);
502insert into awards (award, coef)
503values ('Диплом почёта', 1);
504
505insert into schools (num, address_id, school_type_id)
506values (65, 1, 1);
507insert into schools (num, address_id, school_type_id)
508values (35, 2, 3);
509
510insert into abiturients (name, born, concession_id, school_id, secondary_education_type_id, award_id, address_id,
511                         speciality_id, average_score)
512values ('Иванов Иван Иванович', '13.12.1998', null, 1, 1, null, 3, 2, null);
513
514insert into abiturients (name, born, concession_id, school_id, secondary_education_type_id, award_id, address_id,
515                         speciality_id, average_score)
516values ('Петров петр Петрович', '3.1.2000', 2, 2, 3, 3, 3, 3, null);
517
518insert into abiturients (name, born, concession_id, school_id, secondary_education_type_id, award_id, address_id,
519                         speciality_id, average_score)
520values ('Олегов Олег Олегович', '13.12.1998', null, 1, 1, null, 3, 1, null);
521
522insert into zno_results (abiturient_id, discipline_id, score)
523values (1, 3, 178);
524insert into zno_results (abiturient_id, discipline_id, score)
525values (2, 1, 188);
526insert into zno_results (abiturient_id, discipline_id, score)
527values (3, 3, 190);
528insert into zno_results (abiturient_id, discipline_id, score)
529values (2, 3, 168);
530insert into zno_results (abiturient_id, discipline_id, score)
531values (3, 2, 121);
532
533
534-- Пользователи базы данных:
535
536-- У ректора полный доступ ко всем таблицам
537-- И возможность назначать права
538-- create user rector;
539grant all on all tables in schema public to rector with grant option;
540
541-- У пользователя веб-сайт (например, какое-нибудь веб-приложение)
542-- доступ только на чтение таблицы с лучшими абитуриентами
543-- (для отображения на сайте)
544-- create user website;
545grant select on best_abits to website;
546
547
548-- Вывод информации
549
550-- 1 Средний балл студентов из Киева по математике:
551select avg(score)
552from zno_results
553where discipline_id = (select disciplines.id from disciplines where discipline = 'Математика')
554  and abiturient_id in (select abiturients.id
555                        from abiturients
556                        where address_id in (select addresses.id
557                                             from addresses
558                                             where city_id = (select id from cities where city = 'Киев')));
559
560-- 2 Подсчитать количество студентов с низкими баллами (от 120 до 160)
561select count(abiturient_id) from zno_results where score between 120 and 160;
562
563-- 3
564
565-- 4
566
567-- 5
568
569-- 6
570
571-- 7
572
573-- 8
574
575-- 9
576
577-- 10