Контрольная работа по "Программированию"

Задание:

По аналогии с примерами, приведенными в п. 1 реализовать для своей базы данных триггеры для всех событий (insert,delete, update) до и после. Часть из которых будет обеспечивать ссылочную целостность, остальные могут иметь другое назначение из 3 предложенных. Вычислимые поля можно добавить при необходимости.

вариант 5

расписание экзаменов/зачетов: даты экзаменов, дисциплина, преподаватель, группа, аудитория

а. аудитории, в которых проходят экзамены по дисциплинам, начинающихся со слова «систем», но это не единственное слово названия

б. дата, когда в одной аудитории проходит несколько экзаменов

в. дисциплины, по которым не бывает экзаменов

г. преподаватели, принимающие самые последние экзамены

д. группа, у которой меньше всего экзаменов

е. дисциплина, по которой есть экзамены у всех групп

ж. аудитория, в которой не проходят экзамены у группы 4031

Физическая модель БД:

[pic 1]

Текст и результат запросов на SQL:

1. Добавим в таблицу Student_Group столбец count_ex.

USE Exam_Schedule;

ALTER TABLE Student_Group ADD count_ex INT DEFAULT 0;

Таблица Student_Group после изменения:

[pic 2]

2. Триггер AFTER DELETE

 /*очистка справочника*/

/*удаление из родительской таблицы, если у него больше нет экзамена*/

DELIMITER //

CREATE TRIGGER trig_del_in_ex

AFTER DELETE ON Exam

FOR EACH ROW        

BEGIN

IF NOT EXISTS (SELECT * FROM Exam WHERE id_teach = OLD.id_teach)

THEN DELETE FROM Teacher WHERE id_teach = OLD.id_teach;

END IF;

IF NOT EXISTS (SELECT * FROM Exam WHERE id_discip = OLD.id_discip)

THEN DELETE FROM Discipline WHERE id_discip = OLD.id_discip;

END IF;

IF NOT EXISTS (SELECT * FROM Exam WHERE id_audit = OLD.id_audit)

THEN DELETE FROM Auditorium WHERE id_audit = OLD.id_audit;

END IF;

IF NOT EXISTS (SELECT * FROM Exam WHERE id_group = OLD.id_group)

THEN DELETE FROM Student_Group WHERE id_group = OLD.id_group;

END IF;

END //

DELIMITER ;

Запрос:

DELETE FROM Exam WHERE id_exam = 3;

Таблица Exam до и после запроса:

[pic 3]

[pic 4]

Таблица Discipline до и после запроса:

[pic 5]

[pic 6]

3. Логирование событий, триггеры BEFORE UPDATE и BEFORE DELETE

Дополнительно создаётся таблица ExBackup.

/*логирование событий*/

/*резернвные копии из Экзамена*/

CREATE TABLE ExBackup

(

    id_back int NOT NULL AUTO_INCREMENT PRIMARY KEY,

    id_exam int DEFAULT NULL,

    Exam_Date date NULL,

    Exam_Time time NULL,

    id_teach int DEFAULT NULL,

    id_discip int DEFAULT NULL,

    id_audit int DEFAULT NULL,

    id_group int DEFAULT NULL

);

DELIMITER //

CREATE TRIGGER back_upd_ex

BEFORE UPDATE ON Exam

FOR EACH ROW

BEGIN

INSERT INTO ExBackup SET id_exam = OLD.id_exam, Exam_Date = OLD.Exam_Date, Exam_Time = OLD. Exam_Time,

                                                id_teach = OLD.id_teach, id_discip = OLD.id_discip, id_audit = OLD.id_audit, id_group = OLD.id_group;

END //

CREATE TRIGGER back_del_ex

BEFORE DELETE ON Exam

FOR EACH ROW

BEGIN

INSERT INTO ExBackup SET id_exam = OLD.id_exam, Exam_Date = OLD.Exam_Date, Exam_Time = OLD. Exam_Time,

                                                id_teach = OLD.id_teach, id_discip = OLD.id_discip, id_audit = OLD.id_audit, id_group = OLD.id_group;

END //

DELIMITER ;

Запросы:

DELETE FROM Exam WHERE id_exam = 3;