1.3 Необходимые предполагаемые запросы к БД

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

a) Скидки до 20%?

b) Скидки с 10% до 30% ?

c) Товары на которые действует скидка?

d) Товары на которые действует скидка 10% ?

e) Товары на которые действует скидка 20% до 40 %?

f) Какие товары были проданы в 2014 году?

g) Какие товары были проданы в период с 2014 по 2015 год?

h) Есть ли менеджеры с фамилией Иванов?

i) Есть ли менеджер с номером телефона 89093212080?

j) Количество поставщиков которые доставляют товар за 3 дня?

k) Количество товара меньше 20 ?

1.4 Выводы по разделу

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

Одно из требований -- реляционная база данных должна быть нормализована. Процесс нормализации имеет своей целью устранение избыточности данных и заключается в приведении к третьей нормальной форме (или к нормальной форме Бойса-Кодда).

Нормализацией называется формальная процедура, в ходе которой атрибуты данных группируются в таблицы, а таблицы группируются в базу данных (БД).

Анализ предметной области обычно осуществляется на основании известных сведений о ней с учетом целей проектирования программной системы.

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

Обычно БД создается для хранения и доступа к данным, содержащим сведения о некоторой предметной области, т.е. некоторой области человеческой деятельности или области реального мира.

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

Единицей, хранящейся в БД информации, является таблица. Каждая таблица представляет собой совокупность строк и столбцов, где строки соответствуют экземпляру, а столбцы - атрибутам (признакам, характеристикам, параметрам объекта, события, явления).

В терминах БД столбцы таблицы называются полями, а ее строки - записями.

Нормализация выполняется поэтапно.

Первая нормальная форма (1НФ). Для нее требуется, чтобы таблица была плоской и не содержала повторяющихся групп. У плоской таблицы есть только две характеристики - длина (количество записей или строк) и ширина (количество полей или столбцов). Такая таблица не должна содержать ячеек, включающих несколько значений.

Никакую из систем управления базами данных (СУБД) не удовлетворяет только 1НФ, так как в этом случае необходимо определить большое число полей, многие из которых остаются в основном пустыми. Избыточные данные могут послужить причиной проблем целостности и снижение эффективности при внесении изменений, поэтому подобных решений при проектировании баз данных необходимо избегать.

Вторая нормальная форма (2НФ). Для 2НФ требуется, чтобы все поля таблицы зависели от первичного ключа, то есть, чтобы первичный ключ однозначно определял запись и не был избыточен.

В каждой таблице БД может существовать первичный ключ - поле или набор полей, однозначно идентифицирующий запись.

Значение первичного ключа в таблице БД должно быть уникальным, т.е. в таблице не должно существовать двух и более записей с одинаковым значением первичного ключа.

Те поля, которые зависят только от части первичного ключа, должны быть выделены в составе отдельных таблиц. 2НФ позволяет удалить большую часть повторяющихся данных, которые часто остаются после первого этапа нормализации.

Для третьей нормальной формы (ЗНФ) требуется, чтобы все не ключевые столбцы таблицы зависели от первичного ключа таблицы, но были независимы друг от друга. Для этого требуется, чтобы таблицы были приведены к 1НФ и 2НФ.

Отношение находится в четвертой нормальной форме (4НФ), если в нем не присутствуют функциональные многозначные зависимости. Для 4НФ требуется, чтобы в одной таблице не содержались независимые элементы данных, если между ними существует отношение "многие-ко-многим".

Если в отношении имеется много функциональных зависимостей, 4НФ не устраняет избыточность, то применяют пятую нормальную форму (ЗНФ).

Разложение отношений из 4НФ в пятую нормальную форму должно быть выполнено так, чтобы каждая проекция, полученная из 4НФ, содержала не менее одного возможного ключа и хотя бы один неключевой атрибут из исходного отношения. Для 5НФ требуется, чтобы можно было восстановить исходную таблицу на основе информации таблиц, на которые она была разбита. Кроме того, необходимо, чтобы таблицы соответствовали ЗНФ, а при наличии отношений "многие-ко-многим" - 4НФ.

Для большинства существующих СУБД необходимо представить проект базы данных в ЗНФ, так как этого вполне достаточно практически для всех обычных приложений. При разработке исключительно больших систем, когда необходимо максимальное сокращение объемов хранимых данных, желательно произвести дальнейшую нормализацию. Но в этом случае можно столкнуться с недостатками нормализации. Поскольку порог человеческого восприятия не позволяет одновременно анализировать большое число объектов с учетом их взаимосвязей, можно утверждать, что с увеличением числа нормализованных таблиц уменьшается целостное восприятие базы данных как системы взаимосвязанных данных. Другим недостатком нормализованной базы данных является необходимость считывать связанные данные из нескольких таблиц при выполнении одного запроса.

2. МОДЕЛИРОВАНИЕ ПРЕДМЕТНОЙ ОБЛАСТИ

2.1 Общие сведения

Моделирование данных -- это процесс создания логического представления структуры базы данных.

Эта структура должна удовлетворять представления пользователя данными. Достижение соответствия (адекватности) структуры базы данных представлениям пользователя является наиболее важной задачей при разработке эффективного приложения баз данных и основой для всей последующей работы при разработке базы данных. Если этого не достичь, то пользователи оценят базу данных неудобной, неполной и то, что она вообще не оправдала их ожидания.

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

1. модель базы данных типа «сущность - связь» (entity-relationship model), имеющая значительное количество сторонников среди профессионалов;

2. семантическая объектная модель (некоторые считают ее более простой и точной).

При проектировании базы данных используем первый, наиболее распространенный метод моделирования данных -- «сущность - связь».

Этот выбор объясняется в первую очередь наглядностью представления отношений между объектами и поведения элементов базы данных. На этом подходе к моделированию данных основываются большинство средств автоматизации проектирования (CASE), применение которых значительно сокращает время разработки, а так же предотвращают возникновение многих ошибок.

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

2.2 Выделение сущностей, атрибутов, ключей, связей

Сущность - это что-то такое, о чем нужно хранить информацию в базе данных.

При проектировании баз данных достаточно описать происходящую ситуацию - и большинство существительных и часть глаголов будут кандидатами на сущности.

При проектировании БД главный источник информации о сущностях - это беседа с заказчиком в целях уяснения его бизнес-процессов. Кроме того, анализируются стандартные документы, используемые в бизнес-процессах: бланки, отчеты, инструкции и т.п. После получения такого списка необходимо проверить его на полноту и связность, а также выявить дубли - одинаковые сущности, которые называются разными словами, и сущности, которые на самом деле отличаются, но описываются один и тем же термином.

В данной БД сущностями являются следующие понятия:

a. manager;

b. sale;

c. saleitem;

d. wares;

e. discount;

f. period;

g. provider;

h. order;

i. orderitem.

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

Выбор нужного комплекта атрибутов - одна из самых больших проблем при проектировании баз данных. Очень часто в реальной базе данных нужный комплект атрибутов в итоге не хранится - просто по той причине, что пользователи не смогли сообщить в процессе сбора информации, что он действительно нужен. Иногда в базе, наоборот, попадают лишние атрибуты, заполнение которых требует дополнительного времени. Очень часто возникает проблема с форматом вводимых данных, например, на какие части делить адрес и что делать с нестандартными случаями.

Общее правило при выборе набора атрибутов: нужно начинать с результата и стараться упрощать модель, а не усложнять ее. Первое, на что нужно ответить - на какие вопросы пользователей должны отвечать ваша база данных?

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

Атрибуты данной базы данных представлены в виде рисунков (1-9)

Рис. 1 - Атрибуты таблицы discount

Рис. 2 - Атрибуты таблицы manager

Рис. 3 - Атрибуты таблицы order

Рис. 4 - Атрибуты таблицы orderitem

Рис. 5 - Атрибуты таблицы period

Рис. 6 - Атрибуты таблицы provider

Рис. 7 - Атрибуты таблицы sale

Рис. 8 - Атрибуты таблицы saleitem

Рис. 9 - Атрибуты таблицы wares

Ключ -- минимальный набор атрибутов, по значениям которых можно однозначно найти требуемый экземпляр сущности. Минимальность означает, что исключение из набора любого атрибута не позволяет идентифицировать сущность по оставшимся. В сущности manager ключом является idmanager.

Потенциальный ключ -- в реляционной модели данных -- подмножество атрибутов отношения, удовлетворяющее требованиям уникальности и минимальности (несократимости).

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

Минимальность (несократимость) означает, что в составе потенциального ключа отсутствует меньшее подмножество атрибутов, удовлетворяющее условию уникальности. Иными словами, если из потенциального ключа убрать любой атрибут, он утратит свойство уникальности.

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

В отношении может быть одновременно несколько потенциальных ключей. Один из них может быть выбран в качестве первичного ключа отношения, тогда другие потенциальные ключи называют альтернативными ключами.

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

Первичный ключ (англ. Primary key) -- в реляционной модели данных один из потенциальных ключей отношения, выбранный в качестве основного ключа (или ключа по умолчанию).

Если в отношении (таблица) имеется единственный потенциальный ключ, он является и первичным ключом. Если потенциальных ключей несколько, один из них выбирается в качестве первичного, а другие называют «альтернативными».

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

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

Таблица 3. Определение первичного ключа для типов сущностей:

2.3 Проектирование диаграммы сущность-связь в EA

Для создания БД нужны таблицы, которые в дальнейшем будем заполнять. Для этого открываем специальную программу: Enterprise Architect (Рис. 6).

В программе создаем нужные нам таблицы. Указываем названия таблицы, так же создаем атрибуты, у которых указываем их домены («вид» данных) .

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

После того, как мы соединили все таблицы, вписали все нужные нам атрибуты в таблице и указали их домены, можно сгенерировать код для дальнейшей работы в MySQL.

2.4 Подготовка диаграммы сущность-связь в EA к переносу на целевую СУБД и автоматизированная генерация кода SQL

Генерация кода из EAпроизводится следующим образом: Tools-Database Engineering-Generate PackageDDL. Открывается нужное окно с опциями (Рис. 12).

Рис. 12 -GeneratePackageDDL

Указываем нужные нам опции, нажимая на определенные галочки, и жмем на кнопку «Generate». В итоге мы получаем файл кода в формате sql, который потом запускаем через MySQL

2.5 Создание диаграммы средствами MS SQL SERVER и MS SQL SERVER Management Studio

Для создания диаграммы, кликаем правой кнопкой мыши на папку «DatabaseDiagrams» и нажимаем на «New…» (Рис. 13).

Рис. 13 - Создание новой диаграммы в MySQL

Затем появляется рабочее окно, в котором указываем, какие таблицы должны быть добавлены в новую диаграмму (выделяем и нажимаем на кнопку «Add»)

После добавлений таблиц, программа представляет диаграмму, созданную в EA, с сущностями и их атрибутами со всеми ключами и связями, прописанные в «Архитекторе».

Нормализация - это процесс, позволяющий гарантировать эффективность структур данных в реляционной базе данных.

Отношения находятся во второй нормальной форме, и соответствуют требованиям пользовательских транзакций.

Вторая нормальная форма обязана столбцу saleprice, так как он является вычисляемым столбцом и зависит от другого, не ключевого, столбца.

Рис. 14 - Схема БД

2.6 Начальное заполнение БД

Для заполнения таблицы выполняем следующие действия: Правой кнопкой нажимаем на нужную нам таблицу и нажимаем на кнопку «EditTop 200 Rows» (Рис. 15).

Рис. 15 - Открытие окна для заполнения таблицы кортежами

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

Рис. 16 - Заполнение таблицы кортежами

Очень часто бывают случаи, когда в таблице нужно проводить различные действия с атрибутами. Для их редакции есть специальное окно, открывается оно как показано на Рис. 17:

Рис. 17 - Design - Редакция атрибутов данной таблицы

В данном окне можно проводить все те же действия, что и в EA. Мы можем поменять название нужной нам колонки, удалить её или добавить новую. Так же программа позволяет указать тип колонки и поставить галочку о допуске нулевых значений (Рис. 18).

Рис. 18 - Рабочее окно по редактированию атрибутов таблицы

2.7 Выводы по разделу

Модель предметной области -- это визуальное представление концептуальных классов или объектов реального мира в терминах предметной области. Моделирование предметной области -- один из начальных этапов проектирования системы, необходимый для выявления, классификации и формализации сведений обо всех аспектах предметной области, определяющих свойства разрабатываемой системы.

Для создания модели предметной области выполняются следующие этапы.

1. Выявляются концептуальные классы на основе списка категорий и метода анализа текстового описания для текущей итерации разработки.

2. Концептуальные классы отображаются в модели предметной области.

3. Добавляются необходимые ассоциации, отражающие связи, для которых требуется выделение памяти.

4. Добавляются атрибуты, необходимые для выполнения информационных требований

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

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

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

3. СОЗДАНИЕ И ЗАПУСК БАЗОВЫХ ЗАПРОСОВ SQL

SELECT -- оператор DML языка SQL, возвращающий набор данных (выборку) из базы данных, удовлетворяющих заданному условию.

В большинстве случаев, выборка осуществляется из одной или нескольких таблиц. В последнем случае говорят об операции слияния JOIN. В тех СУБД, где реализованы представления и хранимые процедуры, также возможно получение соответствующих наборов данных.

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

Запрос выполняется следующим образом: сначала извлекаются все записи из таблицы, а затем для каждой записи набора проверяется её соответствие заданному критерию. Если осуществляется слияние из нескольких таблиц, то сначала составляется произведение таблиц, а уже затем из полученного набора отбираются требуемые записи.

Особую роль играет обработка NULL-значений, когда при слиянии, например, двух таблиц -- главной и подчинённой -- имеются или отсутствуют соответствия между записями таблиц, участвующих в слиянии. Для решения этой задачи используются механизмы внутреннего и внешнего слияния.

Один и тот же набор данных может быть получен при выполнении различных запросов. Поиск оптимального плана выполнения данного запроса является задачей оптимизатора.

Основные ключевые слова, относящиеся к запросу SELECT:

1. WHERE -- используется для определения, какие строки должны быть выбраны или включены в GROUP BY.

2. GROUP BY -- используется для объединения строк с общими значениями в элементы меньшего набора строк.

3. HAVING -- используется для определения, какие строки после GROUP BY должны быть выбраны.

4. ORDER BY -- используется для определения, какие столбцы используются для сортировки результирующего набора данных.

Рис. 19 - Поиск таблицы manager

Рис. 20 - Поиск Oz менеджера с именем Иванов Максим из таблицы manager

Рис. 21 - Поиск таблицы sale

Рис. 22 - Поиск фирмы поставщика с названием «sony» из таблицы provider

Рис. 23 - Поиск поставщика с номером ID равное 5

Рис. 24 - Поиск поставщика, чье время поставки больше 3 дней

Рис. 25 - Поиск поставщика, чье время поставки больше или равно 3 дням и ID которого 1

Рис. 26 - Поиск названий поставщиков и их телефоны, чьи Id больше или равно 2

Рис. 27 - Поиск поставщиков, чьё время доставки равно 4 или чей номер товара равен 3

Рис. 28 - Поиск названия товара и его описания, чье ID скидки равен «1» или чья цена закупки равна «3»

Запросы по добавлению строки производятся с помощью оператора insert.

INSERT -- оператор языка SQL, который позволяет добавить строки в таблицу, заполняя их значениями. Значения можно вставлять перечислением с помощью слова values и перечислив их в круглых скобках через запятую или оператором select.

Во время выполнения оператора могут возникнуть ошибки:

1. если при создании таблицы для поля был указан параметр not null и не было определено значение по умолчанию, то при отсутствии для него вставляемого значения возникнет ошибка. Решение очевидно:

a) либо убрать параметр not null;

b) либо указать значение по умолчанию;

c) либо вставить значение.

2. если произойдет попытка вставки в поле с типом identity (автоинскремент), то также произойдет ошибка. Решить проблему можно двумя способами:

a) не вставлять значение в это поле;

b) указать опцию identity_insert on после чего вставить уникальное значение для этого столбца.

Примеры:

Рис. 29 - Добавляем новую строку в таблицу discount

Рис. 30 - Таблица discount после добавлений

Изменение строки производятся с помощью команды UPDATE.

UPDATE - Оператор UPDATE изменяет имеющиеся данные в таблице. С помощью одного оператора могут быть заданы значения для любого количества столбцов. Однако в одном и том же операторе UPDATE можно вносить изменения в каждый столбец указанной таблицы только один раз. При отсутствии предложения WHERE будут обновлены все строки таблицы.

Если столбец допускает NULL-значение, то его можно указать в явном виде. Кроме того, можно заменить имеющееся значение на значение по умолчанию (DEFAULT) для данного столбца.

Ссылка на "выражение" может относиться к текущим значениям в изменяемой таблице. Разрешается также значения одних столбцов присваивать другим столбцам. Для вычисления значений столбцов допускается также использование подзапросов.

Рис. 31 - Таблица discount, после изменений.

Удаление строки осуществляется с помощью команды DELETE.

DELETE -- в языках, подобных SQL, DML-операция удаления записей из таблицы. Критерий отбора записей для удаления определяется выражением where. В случае, если критерий отбора не определён, выполняется удаление всех записей.

1. В СУБД, поддерживающих триггеры, операция Delete может вызывать их срабатывание;

2. При наличии на таблице внешних ключей все дочерние к удаляемым записи в подчинённых таблицах также должны быть удалены для обеспечения ссылочной целостности;

3. В СУБД, поддерживающих транзакции, выполнение операции Delete должно быть подтверждено (COMMIT), либо опровергнуто (ROLLBACK) вызовом соответствующих операций.

Удаление всех записей из таблицы при наличии внешних ключей и механизме транзакций может занять продолжительное время. Для полной очистки таблицы может быть использована операция TRUNCATE.

Рис. 32 - Удаляем строку, где quantity имеет значение «2», в таблице saleitem.

Рис. 33 - Таблица saleitem, после удаления строки.

4. СОЗДАНИЕ И ЗАПУСК ПРОДВИНУТЫХ ЗАПРОСОВ SQL

Рис. 34 - Выводим данные из двух связанных таблиц

Рис. 35 - Выводим данные из двух связанных таблиц

Рис. 36 - Выводим данные из трех связанных таблиц

Рис. 37 - Выводим кортежи, где Idsale имеет значение (NULL)

Рис. 38 - Выводим кортежи, где Idsale имеют значения

Рис. 39 - Выводим номер Idsale

Рис. 40 - Считаем количество кортежей

Рис. 41 - Создаем новую таблицу photo

Рис. 42 - Добавляем кортеж Idmanager в таблицу photo

Рис.43 - Изменяем кортеж idmanager не позволяющее иметь пустое значение

Рис.44 - Заполняем таблицу значениями

Рис.45 - Добавляем атрибут name в таблицу photo

Рис.46 - Добавляем файл в таблицу

Рис.47 - Создаем новую таблицу best manager

Рис.48 - Выводим таблицу, где в атрибуте manager удовлетворяют условие.

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

Рис.50 - Выводим таблицу asd

Рис.51 - Назначаем Первичный ключ

Рис.52 - Удаляем Первичный ключ в таблице RIP

Рис.53 - Создаем Индексы

Рис.54 - Создает уникальный индекс

Рис.55 - Удаляем Индексы таблицы sale

Рис.56 - Создаем новое view

Рис.57 - Удаление view shop

Рис.58 - Создаем view из нескольких таблиц

Рис.59 - Выводим Clients

5. РАБОТА С ПРЕДСТАВЛЕНИЯМИ

5.1 Общие сведения

Представление это виртуальная таблица, хранящая запрос к БД. С помощью представления можно быстро получить доступ к определенным данным, не составляя запрос. Так как таблица виртуальна то сами данные в ней не хранятся, а лишь беруться из основных таблиц и при изменении данных в таблице данные в представлении так же изменятся.

5.2 Создание и использование представлений

Для составления отчётов потребуется сводная таблица по товару магазина .

SQL код

SELECT sale.Idmanager,manager.Idmanager,sale.Idsale,saleitem.Idsale

From saleitem, manager, sale

Where sale.Idmanager = manager.Idmanager

and sale.Idsale = saleitem.Idsale

Рисунок 43- «Сводная таблица по товару»

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

SQL код

Create view all as

SELECT saleitem.Idsale,sale.Idsale,sale.Idmanager, manager.Idmanager

From saleitem, sale, manager

Where saleitem.Idsale = sale.Idsale

and sale.Idmanager = manager.Idmanager

Рисунок 44- «Выборка из представления»

Для того чтобы удалить представление также используестя DROP

SQL код

Drop view all

5.3 Выводы по разделу

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

6. ХРАНИМЫЕ ПРОЦЕДУРЫ

Хранимые процедуры представляют собой группы связанных между собой операторов SQL, применение которых делает работу программиста более легкой и гибкой, поскольку выполнить хранимую процедуру часто оказывается гораздо проще, чем последовательность отдельных операторов SQL. Хранимые процедуры представляют собой набор команд, состоящий из одного или нескольких операторов SQL или функций и сохраняемый в базе данных в откомпилированном виде. Выполнение в базе данных хранимых процедур вместо отдельных операторов SQL дает пользователю следующие преимущества:

a) необходимые операторы уже содержатся в базе данных;

b) все они прошли этап синтаксического анализа и находятся в исполняемом формате; перед выполнением хранимой процедуры SQL Server генерирует для нее план исполнения, выполняет ее оптимизацию и компиляцию;

c) хранимые процедуры поддерживают модульное программирование, так как позволяют разбивать большие задачи на самостоятельные, более мелкие и удобные в управлении части;

d) хранимые процедуры могут вызывать другие хранимые процедуры и функции;

e) хранимые процедуры могут быть вызваны из прикладных программ других типов;

f) как правило, хранимые процедуры выполняются быстрее, чем последовательность отдельных операторов;

g) хранимые процедуры проще использовать: они могут состоять из десятков и сотен команд, но для их запуска достаточно указать всего лишь имя нужной хранимой процедуры. Это позволяет уменьшить размер запроса, посылаемого от клиента на сервер, а значит, и нагрузку на сеть.

Хранение процедур в том же месте, где они исполняются, обеспечивает уменьшение объема передаваемых по сети данных и повышает общую производительность системы. Применение хранимых процедур упрощает сопровождение программных комплексов и внесение изменений в них. Обычно все ограничения целостности в виде правил и алгоритмов обработки данных реализуются на сервере баз данных и доступны конечному приложению в виде набора хранимых процедур, которые и представляют интерфейс обработки данных. Для обеспечения целостности данных, а также в целях безопасности, приложение обычно не получает прямого доступа к данным - вся работа с ними ведется путем вызова тех или иных хранимых процедур.

Подобный подход делает весьма простой модификацию алгоритмов обработки данных, тотчас же становящихся доступными для всех пользователей сети, и обеспечивает возможность расширения системы без внесения изменений в само приложение: достаточно изменитьхранимую процедуру на сервере баз данных. Разработчику не нужно перекомпилировать приложение, создавать его копии, а также инструктировать пользователей о необходимости работы с новой версией. Пользователи вообще могут не подозревать о том, что в систему внесены изменения.

Хранимые процедуры существуют независимо от таблиц или каких-либо других объектов баз данных. Они вызываются клиентской программой, другой хранимой процедурой или триггером. Разработчик может управлять правами доступа к хранимой процедуре, разрешая или запрещая ее выполнение. Изменять код хранимой процедуры разрешается только ее владельцу или члену фиксированной роли базы данных. При необходимости можно передать права владения ею от одного пользователя к другому.

При работе с SQL Server пользователи могут создавать собственные процедуры, реализующие те или иные действия. Хранимые процедуры являются полноценными объектами базы данных, а потому каждая из них хранится в конкретной базе данных. Непосредственный вызов хранимой процедуры возможен, только если он осуществляется в контексте той базы данных, где находится процедура.

В SQL Server имеется несколько типов хранимых процедур.

a) Системные хранимые процедуры предназначены для выполнения различных административных действий. Практически все действия по администрированию сервера выполняются с их помощью. Можно сказать, что системные хранимые процедуры являются интерфейсом, обеспечивающим работу с системными таблицами, которая, в конечном счете, сводится к изменению, добавлению, удалению и выборке данных из системных таблиц как пользовательских, так и системных баз данных. Системные хранимые процедуры имеют префикс sp_, хранятся в системной базе данных и могут быть вызваны в контексте любой другой базы данных.

b) Пользовательские хранимые процедуры реализуют те или иные действия. Хранимые процедуры - полноценный объект базы данных. Вследствие этого каждая хранимая процедура располагается в конкретной базе данных, где и выполняется.

c) Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на локальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При создании такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

Создание хранимой процедуры предполагает решение следующих задач:

a) определение типа создаваемой хранимой процедуры: временная или пользовательская. Кроме этого, можно создать свою собственную системную хранимую процедуру, назначив ей имя с префиксом sp_ и поместив ее в системную базу данных. Такая процедура будет доступна в контексте любой базы данных локального сервера;

b) планирование прав доступа. При создании хранимой процедуры следует учитывать, что она будет иметь те же права доступа к объектам базы данных, что и создавший ее пользователь;

c) определение параметров хранимой процедуры. Подобно процедурам, входящим в состав большинства языков программирования, хранимые процедуры могут обладать входными и выходными параметрами;

d) разработка кода хранимой процедуры. Код процедуры может содержать последовательность любых команд SQL, включая вызов других хранимых процедур.

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

<определение_процедуры>::=

{CREATE | ALTER } PROC[EDURE] имя_процедуры

[;номер]

[{@имя_параметра тип_данных } [VARYING ]

[=default][OUTPUT] ][,...n]

[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,

ENCRYPTION }]

[FOR REPLICATION]

AS

sql_оператор [...n]

Рассмотрим параметры данной команды.

Используя префиксы sp_, #, ##, создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

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

Для передачи входных и выходных данных в создаваемой хранимой процедуре могут использоваться параметры, имена которых, как и имена локальных переменных, должны начинаться с символа @. В одной хранимой процедуре можно задать множество параметров, разделенных запятыми. В теле процедуры не должны применяться локальные переменные, чьи имена совпадают с именами параметров этой процедуры.

Для определения типа данных, который будет иметь соответствующий параметр хранимой процедуры, годятся любые типы данных SQL, включая определенные пользователем. Однако тип данных CURSOR может быть использован только как выходной параметр хранимой процедуры, т.е. с указанием ключевого слова OUTPUT.

Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры. Однако это вовсе не означает, что параметр не подходит для передачи значений в хранимую процедуру. Указание ключевого слова OUTPUT предписывает серверу при выходе из хранимой процедуры присвоить текущее значение параметра локальной переменной, которая была указана при вызове процедуры в качестве значения параметра. Отметим, что при указании ключевого слова OUTPUT значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Не разрешается использование любых выражений или констант, допустимое для обычных параметров.

Ключевое слово VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR. Оно определяет, что выходным параметром будет результирующее множество.

Ключевое слово DEFAULT представляет собой значение, которое будет принимать соответствующий параметр по умолчанию. Таким образом, при вызове процедуры можно не указывать явно значение соответствующего параметра.

Так как сервер кэширует план исполнения запроса и компилированный код, при последующем вызове процедуры будут использоваться уже готовые значения. Однако в некоторых случаях все же требуется выполнять перекомпиляцию кода процедуры. Указание ключевого слова RECOMPILE предписывает системе создавать план выполнения хранимой процедуры при каждом ее вызове.

Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию.

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

Ключевое слово AS размещается в начале собственно тела хранимой процедуры, т.е. набора команд SQL, с помощью которых и будет реализовываться то или иное действие. В теле процедуры могут применяться практически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры. Выход из хранимой процедуры можно осуществить посредством команды RETURN.

Удаление хранимой процедуры осуществляется командой:

DROP PROCEDURE {имя_процедуры} [,...n]

Для выполнения хранимой процедуры используется команда:

[[ EXEC [ UTE] имя_процедуры [;номер]

[[@имя_параметра=]{значение | @имя_переменной}

[OUTPUT ]|[DEFAULT ]][,...n]

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

Использование ключевого слова OUTPUT при вызове процедуры разрешается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT.

Когда же при вызове процедуры для параметра указывается ключевое слово DEFAULT, то будет использовано значение по умолчанию. Естественно, указанное слово DEFAULT разрешается только для тех параметров, для которых определено значение по умолчанию.

Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры. Присвоить параметру значение по умолчанию, просто пропустив его при перечислении нельзя. Если же требуется опустить параметры, для которых определено значение по умолчанию, достаточно явного указания имен параметров при вызове хранимой процедуры. Более того, таким способом можно перечислять параметры и их значения в произвольном порядке.

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

7. ТРИГГЕРЫ

Триггеры являются одной из разновидностей хранимых процедур. Их исполнение происходит при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML). Триггеры используются для проверки целостности данных, а также для отката транзакций.

Триггер - это откомпилированная SQL-процедура, исполнение которой обусловлено наступлением определенных событий внутри реляционной базы данных. Применение триггеров большей частью весьма удобно для пользователей базы данных. И все же их использование часто связано с дополнительными затратами ресурсов на операции ввода/вывода. В том случае, когда тех же результатов (с гораздо меньшими непроизводительными затратами ресурсов) можно добиться с помощью хранимых процедур или прикладных программ, применение триггеров нецелесообразно.

Триггеры - особый инструмент SQL-сервера, используемый для поддержания целостности данных в базе данных. С помощью ограничений целостности, правил и значений по умолчанию не всегда можно добиться нужного уровня функциональности. Часто требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность. Кроме того, иногда необходимо отслеживать изменения значений таблицы, чтобы нужным образом изменить связанные данные. Триггеры можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответствии с правилами, стандартными значениями и т.д.

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

Создает триггер только владелец базы данных. Это ограничение позволяет избежать случайного изменения структуры таблиц, способов связи с ними других объектов и т.п.

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

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

a) проверка корректности введенных данных и выполнение сложных ограничений целостности данных, которые трудно, если вообще возможно, поддерживать с помощью ограничений целостности, установленных для таблицы;

b) выдача предупреждений, напоминающих о необходимости выполнения некоторых действий при обновлении таблицы, реализованном определенным образом;

c) накопление аудиторской информации посредством фиксации сведений о внесенных изменениях и тех лицах, которые их выполнили;

d) поддержка репликации.

Основной формат команды CREATE TRIGGER показан ниже:

<Определение_триггера>::=

CREATE TRIGGER имя_триггера

BEFORE | AFTER <триггерное_событие>

ON <имя_таблицы>

[REFERENCING

<список_старых_или_новых_псевдонимов>]

[FOR EACH { ROW | STATEMENT}]

[WHEN(условие_триггера)]

<тело_триггера>

триггерные события состоят из вставки, удаления и обновления строк в таблице. В последнем случае для триггерного события можно указать конкретные имена столбцов таблицы. Время запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанных с ним событий) или AFTER (после их выполнения).

Выполняемые триггером действия задаются для каждой строки (FOR EACH ROW), охваченной данным событием, или только один раз для каждого события (FOR EACH STATEMENT).

Обозначение <список_старых_или_новых_псевдонимов> относится к таким компонентам, как старая или новая строка (OLD / NEW) либо старая или новая таблица (OLD TABLE / NEW TABLE). Ясно, что старые значения не применимы для событий вставки, а новые - для событий удаления.

При условии правильного использования триггеры могут стать очень мощным механизмом. Основное их преимущество заключается в том, что стандартные функции сохраняются внутри базы данных и согласованно активизируются при каждом ее обновлении. Это может существенно упростить приложения. Тем не менее следует упомянуть и о присущих триггеру недостатках:

a) сложность: при перемещении некоторых функций в базу данных усложняются задачи ее проектирования, реализации и администрирования;

b) скрытая функциональность: перенос части функций в базу данных и сохранение их в виде одного или нескольких триггеров иногда приводит к сокрытию от пользователя некоторых функциональных возможностей. Хотя это в определенной степени упрощает его работу, но, к сожалению, может стать причиной незапланированных, потенциально нежелательных и вредных побочных эффектов, поскольку в этом случае пользователь не в состоянии контролировать все процессы, происходящие в базе данных;

c) влияние на производительность: перед выполнением каждой команды по изменению состояния базы данных СУБД должна проверить триггерное условие с целью выяснения необходимости запуска триггера для этой команды. Выполнение подобных вычислений сказывается на общей производительности СУБД, а в моменты пиковой нагрузки ее снижение может стать особенно заметным. Очевидно, что при возрастании количества триггеров увеличиваются и накладные расходы, связанные с такими операциями.

Неправильно написанные триггеры могут привести к серьезным проблемам, таким, например, как появление "мертвых" блокировок. Триггеры способны длительное время блокировать множество ресурсов, поэтому следует обратить особое внимание на сведение к минимуму конфликтов доступа.

В реализации СУБД MS SQL Server используется следующий оператор создания или изменения триггера:

<Определение_триггера>::=

{CREATE | ALTER} TRIGGER имя_триггера

ON {имя_таблицы | имя_просмотра }

[WITH ENCRYPTION ]

{

{ { FOR | AFTER | INSTEAD OF }

{ [ DELETE] [,] [ INSERT] [,] [ UPDATE] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

AS

sql_оператор[...n]

} |

{ {FOR | AFTER | INSTEAD OF } { [INSERT] [,]

[UPDATE] }

[ WITH APPEND]

[ NOT FOR REPLICATION]

AS

{ IF UPDATE(имя_столбца)

[ {AND | OR} UPDATE(имя_столбца)] [...n]

|

IF (COLUMNS_UPDATES(){оператор_бит_обработки}

бит_маска_изменения)

{оператор_бит_сравнения }бит_маска [...n]}