RMtudHTf

1-- DROP DATABASE IF EXISTS std_db_lab5;
2create database std_db_lab5;
3
4-- DROP SCHEMA IF EXISTS lab_5;
5create schema lab_5;
6
7set search_path = lab_5;
8-- show search_path;
9create table students (
10    id bigserial primary key,
11    name varchar(255) not null,
12    group_code varchar(100) not null
13                     check ( name is not null and
14                             group_code is not null )
15);
16create table disciplines (
17    id bigserial primary key,
18    name varchar(255) not null
19                         check (name is not null )
20);
21create table academic_performance (
22    student_id bigserial references students(id) not null ,
23    discipline_id bigserial references disciplines(id) not null ,
24    group_code varchar(100),
25    course integer,
26    number_of_points integer not null,
27    rating varchar(100)
28                                  check ( student_id is not null and
29                                          discipline_id is not null and
30                                          number_of_points is not null)
31);
32
33-- 2.	Создать триггеры для таблицы успеваемость:
34-- 2.1.	При внесении группы автоматически определяет и вносит курс (Например: Группа БПИ-14-01 – 1 курс, БИС-13-01 – 2 курс, т.е. курс определяется по году поступления)
35create function calculate_group_and_course() returns trigger as $$
36    begin
37
38        new.group_code := (select group_code from "lab_5"."students" as s where s.id = new.student_id);
39        new.course := cast(date_part('year', current_date) as integer) - 2000 - cast(split_part(new.group_code, '-', 2) as integer);
40        if(date_part('month', current_date) >= '9') then
41            new.course = new.course+1;
42        end if;
43        return new;
44    end
45    $$ language plpgsql;
46drop trigger calculate_group_and_course on academic_performance;
47create trigger calculate_group_and_course before insert or update on academic_performance
48    for each row execute procedure calculate_group_and_course();
49-- 2.2.	При внесении количества баллов вносит оценку (>=61 Удовлетворительно, >=76 Хорошо, >= 91 Отлично, < 61 Не аттестован)
50create function calculate_rating() returns trigger as $$
51    begin
52        case
53            when new.number_of_points >= 91 then new.rating := 'Отлично';
54            when new.number_of_points >= 76 then new.rating := 'Хорошо';
55            when new.number_of_points >= 61 then new.rating := 'Удовлетворительно';
56            when new.number_of_points <= 61 then new.rating := 'Не аттестован';
57            end case;
58        return new;
59    end
60    $$ language plpgsql;
61drop trigger calculate_rating on academic_performance;
62create trigger calculate_rating before insert or update on academic_performance
63    for each row execute procedure calculate_rating();
64-- 2.3.	Рассчитывает средний балл по группе
65create or replace function calculate_avg_points_in_group()
66returns trigger as $$
67    begin
68        create or replace view "lab_5"."avg_points_in_group" as (
69        select aca.group_code as group_code,
70                    (select avg(number_of_points)
71                    from "lab_5"."academic_performance"
72                    where group_code = aca.group_code)
73            from "lab_5"."academic_performance" as aca
74            group by group_code);
75        return null;
76    end
77    $$ language plpgsql;
78drop trigger calculate_avg_points_in_group on academic_performance;
79create trigger calculate_avg_points_in_group after insert or update or delete on academic_performance
80    for each row execute procedure calculate_avg_points_in_group();
81-- 2.4.	Рассчитывает средний балл по дисциплине
82create or replace function calculate_avg_points_in_disciplines()
83returns trigger as $$
84    begin
85        create or replace view "lab_5"."avg_points_in_disciplines" as (
86        select (select name from "lab_5"."disciplines" where id = aca.discipline_id),
87                    (select avg(number_of_points)
88                    from "lab_5"."academic_performance"
89                    where discipline_id = aca.discipline_id)
90            from "lab_5"."academic_performance" as aca
91            group by aca.discipline_id);
92        return null;
93    end
94    $$ language plpgsql;
95create trigger calculate_avg_points_in_disciplines after insert or update or delete on academic_performance
96    for each row execute procedure calculate_avg_points_in_disciplines();
97-- 2.5.	Считает количество студентов, неаттестованных хотя бы по одному предмету, на каждом курсе
98create or replace function calculate_not_attested_students_in_courses()
99returns trigger as $$
100    begin
101        create or replace view "lab_5"."not_attested_students_in_courses" as (
102        select count(*), course
103        from "lab_5"."academic_performance"
104        where number_of_points < 61
105        group by course);
106        return null;
107    end
108    $$ language plpgsql;
109create trigger calculate_not_attested_students_in_courses after insert or update or delete on academic_performance
110    for each row execute procedure calculate_not_attested_students_in_courses();*/
111