Реализовать запросы, спроектированные в лабораторной работе №1, на языке Transact-SQL.

Лабораторная работа № 6

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

Проектирование представлений

Задание

1. Создать необновляемое представление и обновляемое представление для определенных категорий пользователей.

2. Привести примеры команд, подтверждающих, что данное представление является необновляемым / обновляемым.

3. Создать агрегирующее представление.

4. Создать представление, основанное на нескольких таблицах.

Проектирование хранимых процедур

Задание

Разработать процедуры, в которых предусмотрены операции добавления, удаления, изменения данных, а также какие-либо вычисления.

Проектирование триггеров

Задание.

Создать триггеры, срабатывающие при добавлении, удалении, обновлении данных. Ниже приведены примеры триггеров для различных предметных областей.

Примеры триггеров

Предметная область: Библиотека

Триггер: при удалении читателя, на руках у которого имеются книги, уменьшить количество экземпляров этих книг.

Предметная область: Оптовая база

Триггер: при удалении поставщика удаляются товары, поставлявшиеся только этим поставщиком.

Предметная область: Производство

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

Предметная область: Автомастерская

Триггер: при досрочном завершении ремонта премия автомеханика увеличивается на 1 % стоимости ремонта за каждый выигранный день.

Предметная область: Сессия

Триггер: общий объем часов для группы вычисляется вновь при изменении объема часов дисциплины или при переназначении дисциплины на сессию.

Предметная область: Поликлиника

Триггер: больной-пенсионер направляется к врачу не ниже 2-й ка­тегории.

Предметная область: Телефонизация

Триггер: контроль - не может быть более 2 сблокированных телефонов.

Предметная область: Спорт

Триггер: исправление мирового рекорда при появлении соответствующего результата.

Предметная область: Сельскохозяйственные поставки

Триггер: при удалении предприятия, являющегося единственным поставщиком какой-то продукции, удаляется и эта продукция.

Предметная область: Городской транспорт

Триггер: количество машин в парке не может превышать сумму количеств машин на маршрутах.

Предметная область: Аэропорт

Триггер: количество проданных билетов не должно превышать числа мест на рейсе.

Предметная область: Деканат

Триггер: количество студентов, зачисленных на I курс, не может превышать число мест на факультете.

Предметная область: Автотранспортное предприятие

Триггер: при зачислении контролируется превышение числа штатных единиц и фонда зарплаты.

Предметная область: Театр

Триггер: если при назначении актера на роль оказывается, что роль уже «занята», в старом назначении делается отметка о снятии.

Предметная область: Справочная аптек

Триггер: при изменении количества медикаментов запись удаляется, если количество становится 0.

Предметная область: Отдел кадров

Триггер: при зачислении контролируется превышение числа штатных единиц и фонда зарплаты.

Лабораторная работа №7

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

Представления, или просмотры (VIEW), – это временные, производные (иначе - виртуальные) таблицы. Они являются объектами БД, информация в которых не хранится постоянно, как в базовых таблицах, а формируется динамически при обращении к ним. Обычные таблицы относятся к базовым, т.е. содержащим данные и постоянно находящимся на устройстве хранения информации. Представление не может существовать само по себе, а определяется только в терминах одной или нескольких таблиц. Применение представлений позволяет разработчику БД обеспечить каждому пользователю или группе пользователей наиболее подходящие способы работы с данными, что решает проблему простоты их использования и безопасности. Содержимое представлений выбирается из других таблиц с помощью выполнения запроса, причем при изменении значений в таблицах данные в представлении автоматически меняются. Представление - это фактически тот же запрос, который выполняется всякий раз при участии в какой-либо команде. Результат выполнения этого запроса в каждый момент времени становится содержанием представления. У пользователя создается впечатление, что он работает с настоящей, реально существующей таблицей.

Создания и изменения представлений представлены следующей командой:

<определение_просмотра> ::= { CREATE| ALTER} VIEW имя_просмотра [(имя_столбца [,...n])] [WITH ENCRYPTION] AS SELECT_оператор [WITH CHECK OPTION]

По умолчанию имена столбцов в представлении соответствуют именам столбцов в исходных таблицах. Явное указание имени столбца требуется для вычисляемых столбцов или при объединении нескольких таблиц, имеющих столбцы с одинаковыми именами. Имена столбцов перечисляются через запятую, в соответствии с порядком их следования в представлении.

Параметр WITH ENCRYPTION предписывает серверу шифровать SQL-код запроса, что гарантирует невозможность его несанкционированного просмотра и использования. Если при определении представления необходимо скрыть имена исходных таблиц и столбцов, а также алгоритм объединения данных, необходимо применить этот аргумент.

Параметр WITH CHECK OPTION предписывает серверу исполнять проверку изменений, производимых через представление, на соответствие критериям, определенным в операторе SELECT. Это означает, что не допускается выполнение изменений, которые приведут к исчезновению строки из представления. Такое случается, если для представления установлен горизонтальный фильтр и изменение данных приводит к несоответствию строки установленным фильтрам. Использование аргумента WITH CHECK OPTION гарантирует, что сделанные изменения будут отображены в представлении. Если пользователь пытается выполнить изменения, приводящие к исключению строки из представления, при заданном аргументе WITH CHECK OPTION сервер выдаст сообщение об ошибке и все изменения будут отклонены.

Представление можно использовать в команде так же, как и любую другую таблицу. К представлению можно строить запрос, модифицировать его (если оно отвечает определенным требованиям), соединять с другими таблицами. Содержание представления не фиксировано и обновляется каждый раз, когда на него ссылаются в команде. Представления значительно расширяют возможности управления данными. В частности, это прекрасный способ разрешить доступ к информации в таблице, скрыв часть данных.