2.5 Створення sql-запитів для маніпулювання даними, згідно з функціями потенціальних користувачів інформаційної системи.

2.5.1 Тригери

Проверка добавления предмета – тригер для таблиці «Предмет», що перевіряє чи існує предмет котрий ви додаєте

Код тригерної функції

CREATE OR REPLACE FUNCTION public."Проверка добавления предмета"() RETURNS trigger AS

$BODY$

BEGIN

IF (SELECT * FROM "Предмет"

WHERE "Название" = NEW."Название") IS NOT NULL THEN

RAISE EXCEPTION 'Такой предмет уже создан';

END IF;

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql

Проверка номера телефона – тригер для таблиці «Місце проживання», перевіряє корректність номеру телефона

Код тригерної функції

CREATE OR REPLACE FUNCTION public."Проверка номера телефона"() RETURNS trigger AS

$BODY$

BEGIN

IF char_lrngth(NEW."Телефон") != 7 THEN

RAISE EXCEPTION 'Номер лелефона имеет отличное от 7 количество знаков';

END IF;

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql;

Проверка совп. добавл. оценок – тригер для таблиці «Оценка», перевіряє запит на можливість дублювання даних

Код тригерної функції

CREATE OR REPLACE FUNCTION public."Проверка совп. добавл. оценок"() RETURNS trigger AS

$BODY$

BEGIN

IF (SELECT * FROM "Оценка ЗНО"

WHERE "Предмет" = NEW."Пердмет" AND "Абитуриент" = NEW."Абитуриент") IS NOT NULL THEN

RAISE EXCEPTION 'Оценка по этому предмету уже присутствует';

END IF;

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql;

Проверка средн. обр – тригер для таблиці «Абитуриент», перевіряє корректність данних про середню освіту

Код тригерної функції

CREATE OR REPLACE FUNCTION public."Проверка средн. обр"()

RETURNS trigger AS

$BODY$

BEGIN

IF NEW."Дата получения средн. обр." > CONVERT(date, GETDATE()) OR NEW."Среднеобр. заведение" IS NULL THEN

RAISE EXCEPTION 'Дата некорректна';

END IF;

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql

Удаление оценок абитур. – тригер для таблиці «Абитуриент», при видаленні запису з цієї таблиці видаляє всі записи з таблиці оцінок пов’язані з нею

Код тригерної функції

CREATE OR REPLACE FUNCTION public."Удаление оценок абитур."()

RETURNS trigger AS

$BODY$

BEGIN

DELETE FROM "Оценка ЗНО" WHERE "Абитуриент" = OLD."Рег. номер";

RETURN OLD;

END;

$BODY$

LANGUAGE plpgsql

2.5.2 Представлення

10 лучших абитуриентов – відображає інформацію про десять абітурієнтів з найвищим сумарним балом

Код представлення

CREATE OR REPLACE VIEW public."10 лучших абитуриентов" AS

SELECT * FROM "Абитуриент" "А"

WHERE "А"."Рег. номер" IN (

SELECT "Оценка ЗНО"."Абитуриент" AS "Сумма баллов"

FROM "Оценка ЗНО"

GROUP BY "Оценка ЗНО"."Абитуриент"

ORDER BY (sum("Оценка ЗНО"."Оценка")) DESC

LIMIT 10);

Льготники – представлення відображає інформацію про всіх студентів що мають пільги

Код придставлення

CREATE OR REPLACE VIEW public."Льготники" AS

SELECT * FROM "Абитуриент" "А"

WHERE "А"."Льготы" IS NOT NULL;

Предметы для специальностей –відображає інформацію про всі спеціальності і пов’язані з ними предмети

Код представлення

CREATE OR REPLACE VIEW public."Предметы для специальностей" AS

SELECT "Сп"."Код" AS "Спец_Код",

"Сп"."Название" AS "Спец_Название",

"ПС"."Предмет" AS "Предм_Код",

"Пр"."Название" AS "Предм_Название"

FROM "Специальность" "Сп"

JOIN "Предм_Спец" "ПС" ON "ПС"."Специальность" = "Сп"."Код"

JOIN "Предмет" "Пр" ON "ПС"."Предмет" = "Пр"."Код";