Лабораторные_БД_ЭВМ_20 (AutoRecovered). Методические указания по выполнению лабораторных работ по дисциплине (модулю)
 Скачать 0.75 Mb.
|
|
Часть А 1. Создайте три обновляемых представления (по одному представлению для каждой из ваших сущностей). 2. Дайте для каждого представления понятную формулировку его смысла. 3. Проверьте, что эти представления обновляемые. Часть Б Создайте пять не обновляемых представлений. Каждое представление должно быть не обновляемым по какой-то одной из следующих причин: • Представление строится на основе нескольких таблиц. • Представление не содержит первичного ключа базовой таблицы. • Представление содержит агрегирующие функции. • Представление содержит DISTINCT в своем определении. • Представление использует подзапросы. 2. Дайте для каждого представления понятную формулировку его смысла. 3. Проверьте, что все эти представления не обновляются. Часть В 1. Создайте материализованное представление. 2. Измените данные в исходной таблице. 3. Добейтесь, чтобы данные изменились также и в материализованном представлении. 7. Контрольные вопросы Дано представление. CREATE VIEW view4 AS SELECT Книга.Название, Книга.Издательство, Автор.Фамилия FROM Автор INNERJOIN Книга ON Автор.Код_Автора = Книга.Код_Автора Предпринимается попытка удалить из представления запись. DELETE FROM view4 WHERE Фамилия='Л.Толстой' Какой будет результат выполнения этой команды? Дано представление. CREATE VIEW view5 AS SELECT Билет.Номер_места, Билет.Номер_рейса, Рейс.Конечный_пункт FROM Билет INNER JOIN Рейс ON Билет.Номер_рейса = Рейс.Номер_рейса Предпринимается попытка удалить из представления запись. DELETE FROM view5 WHERE Конечный_пункт ='Москва' Какой будет результат выполнения этой команды? Дано представление. CREATE VIEW view3 AS SELECT Название, Тариф, Тариф*0.05 AS Налог FROM Город WHERE Регион='Поволжье' Предпринимается попытка удалить из представления запись. UPDATE view3 SET Тариф=Тариф*1.5 WHERE Тариф>10 Какой будет результат выполнения этой команды? Дано представление. CREATE VIEW view4 AS SELECT Книга.Название, Книга.Издательство, Автор.Фамилия FROM Автор INNERJOIN Книга ON Автор.Код_Автора = Книга.Код_Автора Предпринимается попытка удалить из представления запись. DELETE FROM view4 WHERE Фамилия='Л.Толстой' Какой будет результат выполнения этой команды? Дано представление. CREATE VIEW view1 AS SELECT Название, Цена, Цена*Количество AS Стоимость FROM Книга WHERE Издательство='Мир' Предпринимается попытка удалить из представления запись. UPDATE view1 SET Цена=Цена*1.5 WHERE Цена>50 Какой будет результат выполнения этой команды? Дано представление. CREATE VIEW AS SELECT Город.Название, Разговор.Фамилия, Разговор.Продолжительность FROM Город INNER JOIN Разговор ON Город.Код_Города = Разговор.Код_Города; Предпринимается попытка удалить из представления запись. DELETE FROM VIEW WHERE Название='Самара' Какой будет результат выполнения этой команды? Дано представление. CREATE VIEW view4 CREATE VIEW view4 AS SELECT Регион, Avg(Тариф) AS Средн_Тариф FROM Город GROUP BY Регион HAVING Регион='Поволжье' Предпринимается попытка удалить из представления запись. DELETE FROM view4 WHERE Регион='Урал' Какой будет результат выполнения этой команды? Лабораторная работа №10 Триггеры 1. Цель и задачи работы Целью лабораторной работы является изучение и практическое применение триггеров. 2. Порядок выполнения работы - ознакомится с теоретическими сведениями; - выполнить задание; - оформить отчет; - ответить на контрольные вопросы, заданные преподавателем. 3. Оформление отчета Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе. 4. Теоретические сведения Триггер базы данных соотносится с таблицей, представлением, схемой или базой данных. Триггер представляет собой процедуру, которая срабатывает автоматически, когда с определенным объектом происходит определенное событие. Рассмотрим триггеры, присоединенные к таблицам, которые возбуждаются при выполнении команд INSERT, DELETE или UPDATE. Триггер компилируется и записывается в словарь данных. Триггеры позволяют реализовать сложный алгоритм для проверки вводимых или редактируемых данных, выполнять определенные действия, сопутствующие указанной команде. Например, при продаже определенного товара по какой-то накладной, количество данного товара на определенном складе должно быть уменьшено на проданное количество. Триггеры особенно полезны, когда потребуется предотвратить появление неверных записей в таблицах, выполнить проверку допустимости значений столбца или обеспечить безопасность доступа. Другой областью применения триггеров является отслеживание модификаций таблиц базы данных. При любом изменении таблицы можно сохранять информацию о том, кто его выполнил, в какой момент времени, тип модификации и другие данные в специальной контрольной таблице. Триггеры могут срабатывать до, после или вместо операции DML Триггер, разработанный для срабатывания до наступления события, может собрать данные о строке до начала модификации, триггер, срабатывающий после события, имеет доступ к новым значениям строки, а триггер типа «INSTEAD OF» полезен, если требуется запретить какое-то действие по модификации таблицы. Триггер может быть создан таким образом, что он будет запускаться каждый раз для каждой строки, участвующей в модификации. Такой триггер называется обработчиком событий на уровне строк. При этом в команде триггера должна присутствовать опция «FOR EACH ROW». Если указанная фраза отсутствует, это означает, что триггер сработает один раз. Такой триггер называется обработчиком событий на уровне выражений. Команда создания триггера Общий вид команды создания триггера: CREATE TRIGGER имя_триггера BEFORE| AFTER |INSTEAD OF DELETE [OR INSERT [OR UPDATE [OF списокстолбцов]]] ON имя_таблицы [FOR EACH ROW] [ WHEN (условие)] [ DECLARE /* переменные, константы, курсоры и т.п. */] BEGIN /* блок PL/SQL */END; Предложение триггера определяет: 1-тип команды SQL, при выполнении которой запускается триггер (DELETE, INSERT или UPDATE). В спецификацию предложения триггера могут быть включены одна, две или все три эти команды. Список столбцов для UPDATE Если триггер должен реагировать на команду UPDATE, то в спецификацию триггера может быть включен необязательный список столбцов. При наличии списка столбцов данный триггер запускается по команде UPDATE лишь тогда, когда она обновляет один из перечисленных столбцов. Если список столбцов отсутствует, то триггер возбуждается при обновлении любого столбца ассоциированной таблицы. Список столбцов не может быть специфицирован для предложений триггера INSERT или DELETE Опция WHEN В определение триггера строки может быть включено необязательное ограничение триггера с помощью условия, указанного в опции WHEN. Выражение в опции WHEN, если она присутствует, вычисляется для каждой строки, затрагиваемой триггером. Если результат выражения для строки дает TRUE, то тело триггера исполняется для этой строки. Однако если это выражение вычисляется для строки как FALSE или NULL, то тело триггера не исполняется для этой строки. Тело триггера Тело триггера - это блок PL/SQL, который может содержать предложения SQL и PL/SQL. Эти предложения исполняются тогда, когда сработала соответствующая команда, запускающая триггер, и ограничение триггера (если оно есть) вычислено как TRUE. Для триггеров строк тело триггера имеет некоторые специальные конструкции, которые могут быть включены в код этого блока PL/SQL: корреляционные имена, условные предикаты INSERTING, DELETING и UPDATING. Доступ к значениям столбцов в триггерах строки Внутри тела триггера строк, код PL/SQL и предложения SQL имеют доступ как к старым, так и к новым значениям столбцов текущей строки, затрагиваемой предложением триггера. Для каждого столбца модифицируемой таблицы определены два корреляционных имени: одно для старого(old), другое – для нового значения столбца (new). В зависимости от типа предложения триггера, то или иное корреляционное имя может быть лишено смысла. Условные предикаты Если триггер может быть возбужден более чем одним типом предложения DML (например, "INSERT OR DELETE OR UPDATE OF fio"), то в теле триггера можно использовать условные предикаты INSERTING, DELETING и UPDATING, для того чтобы выполнять различные участки кода в зависимости от типа команды, запустившей триггер. Предположим, что предложение триггера определено следующим образом: INSERT OR UPDATE OF fio ON manager Создание и использование триггера BEFORE Для таблиц, в которых используются последовательности для создания полей-счетчиков, очень удобно использовать триггер типа BEFORE для команды INSERT. Данный триггер будет формировать поле-счетчик, а в команде добавления новой записи, это поле вообще не нужно будет указывать. Тем самым разработчик будет избавлен от необходимости вспоминать название последовательности каждый раз, как требуется добавить новую запись. Например, для таблицы manager мы создали последовательность man_km, которую будем использовать в теле триггера. Кроме того, предусмотрим в триггере исправление возможной ошибки при вводе значения поля fio с маленькой буквы. Триггер, срабатывающий до выполнения команды INSERT, может иметь следующий вид: CREATE OR REPLACE TRIGGER bi_trg_manager BEFORE INSERT ON manager FOR EACH ROW BEGIN SELECT m_km.nextvalINTO :new.kod_men FROM DUAL; :new.fio:=INITCAP(:new.fio); END; Создание и использование триггера AFTER Триггер AFTER срабатывает после наступления события. При этом старое значение изменяемого поля можно получить из переменной с именем :old.имя_поля, а новое – из переменной :new.имя_поля. Обе переменные доступны при операции UPDATE, переменная :new.имя_поля – при операции INSERT, а переменная :old.имя_поля – при операции DELETE. Триггер, который запускается после выполнения команды UPDATE, будет реагировать на попытку изменить любое поле таблицы manager. При изменении поля kod_men, которое формируется с помощью последовательности и является первичным ключом, должно возбудиться исключение, которое выведет сообщение о том, что данное поле менять нельзя, и транзакция будет отменена. При любых других изменениях будет добавлена соответствующая запись в таблицу man_hist. CREATE OR REPLACE TRIGGER au_trg_manager AFTER UPDATE ON manager FOR EACH ROWDECLAREupd_kod EXCEPTION;BEGIN IF UPDATING('kod_men') THEN RAISE upd_kod; ELSE INSERT INTO man_hist VALUES (user, sysdate, :old.fio,:new.fio,:old.oklad,:new.oklad, :old.kod_men); END IF; EXCEPTION WHEN upd_kod THEN RAISE_APPLICATION_ERROR(-20005,'Код менеджера изменять нельзя');END; Ограничения при создании табличных триггеров Триггеры, разработанные для таблицы, не могут использовать следующие команды: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSATION. Процедуры и функции, которые вызываются в триггере, также не должны содержать внутри себя указанные команды. В триггерах запрещено объявлять переменные типа LONG или LONG ROW. Следующие ограничения связаны с понятиями «мутирующей таблицы» и таблицы с ограничениями целостности типа PRIMARY KEY, UNIQUE, FOREIGN KEY. Мутирующей является таблица, которая в данный момент модифицируется одной из DML команд (INSERT, DELETE или UPDATE). Таблица, которая может модифицироваться за счет каскадного ограничения целостности типа DELETE CASCADE, также является мутирующей. В триггере типа «FOR EACH ROW» нельзя ни читать, ни изменять данные мутирующей таблицы внутри триггера. Эта ошибка выявляется только во время выполнения триггера, а не на этапе компиляции. Исключением является только триггеры типа BEFORE INSERT и AFTER INSERT, которые позволяют считывать и изменять данные с помощью корреляционных переменных :new и :old при условии добавления единственной строки. В теле триггера запрещено изменять любые ключи (primary, unique, foreign) таблиц, поддерживающих ограничения ссылочной целостности. Другие поля данных таблиц могут быть модифицированы триггером. 5. Оборудование персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL. 6. Задание на работу Создайте: триггер для INSERT, триггер для DELETE, триггер для UPDATE. Один из них должен быть BEFORE, один – AFTER, один – INSTEAD OF. Два из них должны быть определены уровне строк (FOR EACH ROW), один – на уровне оператора. Какой-то из триггеров должен проверять возможность действия (вызывать исключение в зависимости от определенных условий), какой-то – модифицировать ту же таблицу, для которой создается триггер, оставшийся должен быть определен ДЛЯ ПРЕДСТАВЛЕНИЯ и модифицировать таблицу, на базе которой построено это представление. В каком-то из триггеров должна использоваться псевдострокаnew, в каком-то – псевдострокаold. Приведите: исходное содержимое таблиц тексты триггеров формулировки того, что делает триггер операторы, проверяющие действия триггеров содержимое таблиц после этих операторов, с выделением (шрифтом или цветом) того, что изменилось. 7. Контрольные вопросы Дана таблица Книга. Разработать триггер, который выполняется вместо изменения цены одной книги в этой таблице. Изменение цены выполнить только в том случае, если книга издается в издательстве 'Мир'. CREATE TRIGGER trig_upd ON Книга INSTEAD OF UPDATE AS Напишите операторы для продолжения текста триггера. Даны таблицы Город и Разговор. Пусть они не связаны внешним ключом. Для обеспечения целостности данных создать триггер, обрабатывающий удаление информации о некотором городе из таблицы Город. При этом в таблице Разговор необходимо также удалить записи обо всех телефонных разговорах с удаленным городом. CREATE TRIGGER trig_del ON Город FOR DELETE AS Напишите операторы для продолжения текста триггера. Даны таблицы Рейс и Билет. Пусть они не связаны внешним ключом. Для обеспечения целостности данных создать триггер, обрабатывающий удаление записи о некотором рейсе из таблицы Рейс. При этом в таблице Билет необходимо его номер заменить на номер запасного рейса 111 для билетов, проданных на удаленный рейс. CREATE TRIGGER trig_del ON Рейс FOR DELETE AS Напишите операторы для продолжения текста триггера. Даны таблицы Книга и Автор. Пусть они не связаны внешним ключом. Для обеспечения целостности данных создать триггер, обрабатывающий удаление информации о некотором авторе из таблицы Автор. При этом необходимо запретить удаление, если общий тираж упомянутого автора больше 1000 экземпляров.. CREATE TRIGGER trig_del ON Автор FOR DELETE AS Напишите операторы для продолжения текста триггера. Дана таблица Разговор. Разработать триггер, который выполняется вместо изменения продолжительности одного разговора. Выполнить изменение продолжительности только в том случае, если общая продолжительность телефонных соединений абонента, в разговоре которого меняется продолжительность, не превышает 300 мин. CREATE TRIGGER trig_upd ON Разговор INSTEAD OF UPDATE AS Напишите операторы для продолжения текста триггера. Дана таблица Билет. Разработать триггер, который выполняется вместо изменения стоимости одного билета. Изменение стоимости билета выполнить только в том случае, если на этот рейс продано не менее 20 билетов. CREATE TRIGGER trig_upd ON Билет INSTEAD OF UPDATE AS Напишите операторы для продолжения текста. |