Агапов И.Е. Лабораторный практикум. Базы данных

Подождите немного. Документ загружается.

<описание ограничения поля> = {

PRIMARY KEY |

UNIQUE |

REFERENCES Другая_Таблица[(Поле [, Поле ...])] |

CHECK (Проверяемое_Условие)

}

Упрощенный синтаксис задания ограничений на основе нескольких

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

(способ использования описываемого элемента <ограничение> приведен

на странице 22):

<ограничение> = [CONSTRAINT Имя_Ограничения]

<описание ограничения> [<ограничение> ...]

<Описание ограничения> = {

{PRIMARY KEY | UNIQUE} (Поле [, Поле ...]) |

FOREIGN KEY (Поле [, Поле ...])

REFERENCES Другая_Таблица[(Поле [, Поле ...])] |

CHECK (Проверяемое_Условие)

}

Как видно из синтаксиса задания ограничений, каждое ограничение

может иметь имя, записываемое после ключевого слова CONSTRAINT. Все

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

нии ограничения не задать его имени, то оно будет назначено сервером

СУБД автоматически. При выполнении лабораторных работ необходимо ка-

ждому ограничению обязательно

давать имя.

4.4.1. Ограничение первичного ключа (Primary key)

Это ограничение обозначает, какие поля в таблице единственным обра-

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

ключа заставляет сервер базы данных гарантировать, что значения, вставлен-

ные в поля первичного ключа, будут уникальны внутри таблицы. У таблицы

может быть только одно такое ограничение.

Первичные ключи выполняют следующие функции:

1. Они служат средством

различения отдельных записей.

2. Используются в ссылочной целостности.

3. При создании первичного ключа автоматически создается индекс, поэто-

му они ускоряют поиск и сортировку.

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

Все поля первичного ключа должны быть объявлены NOT NULL.

Пример задания ограничения первичного ключа:

ALTER TABLE Customer

ADD CONSTRAINT PK_Customer PRIMARY KEY (Cust_No);

4.4.2. Ограничения уникальности (Unique)

Используются для гарантии уникальности одного или нескольких по-

лей записи. Единственное отличие от ограничения первичного ключа – воз-

можность задания для таблицы нескольких таких ограничений.

Пример задания ограничения уникальности:

ALTER TABLE Employee

ADD CONSTRAINT Unique_Phone UNIQUE (Phone);

4.4.3. Ограничения внешнего ключа (Foreign keys)

Эти ограничения используются для определения связей между табли-

цами. Они гарантируют соответствие значений полей одной таблицы значе-

ниям ключевых полей в другой таблице. Такие ограничения могут предот-

вратить стирание данных из главной таблицы, на которые имеются ссылки в

подчиненной таблице.

При определении внешнего ключа автоматически генерируется индекс.

Пример задания ограничения внешнего

ключа:

ALTER TABLE Book ADD CONSTRAINT FK_Book

FOREIGN KEY (B_PubNo) REFERENCES Publisher (B_NO);

В отличие от полей первичных ключей, в полях, которые являются

внешним ключом, могут допускаться пустые значения. Эта возможность не-

обходима для разрешения взаимных ссылок и возможности хранить в табли-

це иерархические структуры, когда корневые узлы содержат NULL.

4.4.4. Контрольные ограничения (Checks)

Это условия, налагаемые на возможные значения полей. Контрольные

ограничения гарантируют истинность заданного правила для каждой записи

в таблице.

В контрольных ограничениях можно использовать большой набор опе-

раторов для проверки вставляемых и изменяемых значений. Полный синтак-

сис создания контрольного ограничения здесь не приводится из-за своего

большого размера. С ним можно ознакомиться

в [9] (LangRef.pdf).

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

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

операции вставки и обновления записей.

Пример контрольного ограничения, которое гарантирует, что значение

поля "Num" будет иметь значение от 0 до 4:

ALTER TABLE Property

ADD CONSTRAINT Check_Invalid_Number

CHECK (Num >= 0 AND Num <= 4);

Контрольные ограничения реализованы при помощи системных триг-

геров.

4.5. Задание

Лабораторную работу следует выполнять в следующем порядке:

1. Создать на сервере pi_srv рабочую папку для хранения файлов, получае-

мых при выполнении лабораторной работы №4. Эта папка должна быть

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

"ЛР4".

2. Скопировать в эту папку файл сценария, созданный при выполнении ла-

бораторной работы

№3.

3. Открыть окно "Script Executive" в приложении "IB Expert" и прочитать в

него файл сценария, скопированный ранее в папку "ЛР4".

4. Изменить соответствующим образом в сценарии текст комментариев и

сделать, чтобы база данных теперь создавалась в папке "ЛР4".

5. Добавить в сценарий в операторы создания таблиц создание ограничений

всех четырех видов. Каждая таблица должна

иметь ограничение первич-

ного ключа. С помощью ограничений внешнего ключа должны быть зада-

ны все имеющиеся связи между таблицами.

6. Выполнить сценарий и сохранить его в папке "ЛР4".

7. Зарегистрировать созданную базу данных в программе "IB Expert" и под-

ключиться к ней.

8. Создать в своей базе данных таблицу в диалоговом режиме.

Она должна

называться "TestConstraints" и содержать десять полей разных типов с

произвольными именами.

9. Добавить в диалоговом режиме к этой таблице по одному ограничению

каждого типа. Скопировать в отчет сценарий создания этой таблицы, ко-

торый сгенерирует "IB Expert".

10. Создать в папке "ЛР4" резервную копию базы данных.

11. Создать и сохранить в папке "ЛР

4" файл с отчетом о выполнении лабора-

торной работы №4, который должен называться "Отчет.doc".

4.6. Ход работы

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

сценария, как и операции создания таблиц в диалоговом режиме, были изу-

чены при выполнении предыдущих лабораторных работ.

Для создания ограничений с помощью программы "IB Expert" в диало-

говом режиме необходимо выполнить следующие действия:

1. Подключиться к базе данных.

2. Открыть окно с таблицей, в которую

будут добавляться ограничения (см.

рис. 14 на стр. 27).

3. Перейти на вкладку "Constraints". На этой вкладке имеется четыре допол-

нительных вкладки для просмотра и редактирования ограничений всех че-

тырех типов (рис. 17).

Рис. 17. Окно просмотра и редактирования ограничений

4. Чтобы создать новое ограничение надо перейти на нужную вкладку и на-

жать клавишу [Insert]. В результате в сетку с перечнем имеющихся огра-

ничений будет добавлена строка с новым ограничением.

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

кнопку

[Compile] (Ctrl+F9), которая приведет к автоматическому соз-

данию оператора создания ограничения и его выполнению.

На рис. 18 показан результат создания ограничения уникальности для

таблицы "Человек".

Рис. 18. Окно с примером создания ограничения уникальности

4.7. Отчет о выполнении работы

Отчет о выполнении лабораторной работы №4 оформляется так же, как

и для предыдущих лабораторных работ.

Он должен содержать описание и результаты работы, представляемые в

следующей последовательности:

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

2. Распечатка сценария создания базы данных с комментариями.

3. Перечень файлов, полученных при выполнении лабораторной работы с

указанием их имен, места расположения, даты изменения и размеров

(сценарий, база данных, резервная копия базы данных, файл с отчетом).

4.8. Контрольные вопросы

1. Дать определение термина "Ограничение".

2. Какие существуют варианты операторов создания ограничений?

3. Какие особенности имеют поля, входящие в первичный ключ и ограниче-

ние уникальности?

4. Какие функции имеет ограничение первичного ключа?

5. Что такое внешний ключ?

6. Зачем в полях внешнего ключа могут разрешаться пустые значения?

7. Что такое контрольное

ограничение?

8. При помощи чего реализованы контрольные ограничения?

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

в явном виде? Какие имена при этом будут иметь автоматически созда-

ваемые индексы?

10. Какие имена получают ограничения уникальности, если их не задать в яв-

ном виде? Какие имена при этом

будут иметь автоматически создаваемые

индексы?

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

явном виде? Какие имена имеют индексы, автоматически создаваемые для

ограничений внешнего ключа?

12. Какие имена назначаются системным триггерам для реализации кон-

трольных ограничений?

5. Лабораторная работа №5.

Триггеры, генераторы, исключения,

5.1. Цель работы

Изучить используемые в Firebird триггеры, генераторы и исключения.

Получить навыки работы с триггерами, генераторами и исключениями с по-

мощью команд SQL и с помощью программы "IB Expert".

Время выполнения: 4 часа.

5.2. Исходные данные

Исходными данными является индивидуальное задание и результат

предыдущих лабораторных работ.

5.3. Используемые программы

Программы "IB Expert" и "Microsoft Word".

5.4. Теоретические сведения

5.4.1. Генераторы (Generators)

Генераторы используются для генерации уникальных номеров, исполь-

зуемых в ключевых полях. То есть они реализуют те же функциональные

возможности в таблицах InterBase, что поля с автоматическим приращением

в других СУБД. Можно указать, чтобы числа генерировались, начиная с за-

данного начального значения.

Пример создания генератора:

CREATE GENERATOR MyGenerator;

SET GENERATOR MyGenerator TO 1000;

Генераторы непосредственно не привязываются к какому-либо полю.

Они просто позволяют генерировать уникальные числа. Для этого использу-

ется функция Gen_ID(), встроенная в InterBase, которая генерирует цело-

численные значения. Она берет генератор в качестве первого параметра и

значение шага в качестве второго. Обычно приращение равно 1.

Обращаться к генератору можно только через

функцию Gen_ID().

5.4.2. Бизнес-правила

В реальной жизни вопрос целостности базы данных связан с правила-

ми, установленными у пользователя информационной системы. Например, в

компании могут быть установлены такие правила:

• клиентам не разрешается размещать заказы на сумму, превышающую их

лимит кредита;

• сведения о выполненных заказах хранятся в течение шести месяцев, а за-

тем удаляются;

• нельзя выдавать читателю новых книг, пока он не вернет взятых ранее;

• каждый раз, когда продается какой-нибудь товар, для служащего, офор-

мившего продажу, и для отдела, в котором этот служащий работает, на

стоимость проданного товара

увеличивается значение определенного по-

ля, используемого для вычисления премии; в случае возврата всего или

части проданного товара, значение этого поля соответствующим образом

должно уменьшиться;

• каждый раз, когда приходит новая поставка, количество поставленного

товара на складе увеличивается на количество товара в поставке.

Такие правила называются бизнес-правилами. В первом стандарте SQL

считалось, что эти правила выходят за рамки ответственности СУБД и за их

реализацию отвечает прикладная программа, осуществляющая доступ к базе

данных. Впервые в 1986 году в СУБД Sybase было введено понятие триггер,

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

5.4.3. Триггеры (Triggers)

С любым событием, вызывающим изменение содержимого таблицы,

можно связать сопутствующее действие (триггер), которое СУБД должна

выполнять при каждом возникновении события. Триггер – это группа опе-

раторов языка SQL, которые автоматически выполняется при вставке, моди-

фикации или удалении записи.

В СУБД InterBase можно создавать триггеры, работающие при сле-

дующих шести условиях:

• до вставки записи

(BEFORE INSERT);

• после вставки записи (AFTER INSERT);

• до удаления записи (BEFORE DELETE);

• после удаления записи (AFTER DELETE);

• до модификации записи (BEFORE UPDATE);

• после модификации записи (AFTER UPDATE).

В Firebird 1.5 триггер может срабатывать при возникновении одного из

нескольких событий. Триггеры могут вызывать выполнение хранимых про-

цедур

PT, выполнять различные проверки и генерировать исключения. Обычно

триггеры используются для задания сложных правил контроля целостности

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

Недостатком триггеров является их влияние на производительность

операций с базой данных.

P Хранимые процедуры изучаются в другой лабораторной работе.P

Синтаксис оператора создания триггера:

CREATE TRIGGER Имя FOR Таблица

[ACTIVE | INACTIVE]

{BEFORE | AFTER}

<операция> [OR <операция> [OR <операция>]]

[POSITION Номер]

AS [<список переменных>] <блок>;

<операция> = {INSERT | UPDATE | DELETE}

<список переменных> =

DECLARE [VARIABLE] Переменная <тип данных>;

[DECLARE [VARIABLE] Переменная <тип данных>; ...]

<блок> =

BEGIN

<составной оператор>

[<составной оператор>...]

END

<составной оператор> = <блок> | Оператор;

Параметры, входящие в этот оператор, пояснены в табл. 2.

Таблица 2

Описание параметров оператора создания триггера

Параметр Описание

Имя

Уникальное название триггера.

Таблица

Название таблицы или просмотра, для ко-

торых создается триггер

ACTIVE | INACTIVE

Указывает будет ли работать триггер

BEFORE | AFTER

Обязательный параметр, показывающий

когда будет срабатывать триггер

INSERT | UPDATE |

DELETE

Одной из событий, на которые будет сра-

батывать триггер

POSITION Номер

Определяет порядок срабатывания тригге-

ра, когда имеется несколько триггеров,

реагирующих на одно и то же событие.

Номер – целое число между 0 и 32767

DECLARE [VARIABLE]

Переменная <тип данных>

Описание локальной переменной, которую

можно будет использовать только в этом

триггере

Оператор

Любой одиночный оператор языка храни-

мых процедур и триггеров InterBase

Внутри тела триггера доступны две контекстные переменные NEW и

OLD, которые позволяют получить доступ к новым и старым значениям по-

лей записи, при изменении которой был вызван триггер.

Пример генератора и триггера:

CREATE GENERATOR EMP_NO_GEN;

CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE

ACTIVE BEFORE INSERT POSITION 0

BEGIN

IF (NEW.EMP_NO IS NULL) THEN

NEW.EMP_NO = GEN_ID(EMP_NO_GEN, 1);

END;

5.4.4. Исключения (Exceptions) и обработка ошибок

Исключение – это определенная пользователем ошибка, которая имеет

имя и связанный с ним текст сообщения.

Для создания исключения служит оператор:

CREATE EXCEPTION Имя 'Текст сообщения';

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

служит оператор:

EXCEPTION Имя;

При возникновении исключения нормальный ход выполнения преры-

вается и InterBase ищет ближайший обработчик этого исключения в текущем

блоке BEGIN … END. Если обработчика исключения в этом блоке нет, то In-

terBase поднимается на уровень выше (к внешнему блоку BEGIN … END) и

ищет обработчик там, и т.д. Если обработчик исключения найден, то выпол-

няется его код и

далее управление передается на первый оператор за обра-

ботчиком исключения. В случае если было сгенерировано исключение, кото-

рое никто не обрабатывает, это исключение передается в клиентское прило-

жение. Чаще всего это приводит к выдаче пользователю сообщения о про-

изошедшей ошибке.

Синтаксис оператора для обработки исключений внутри тела триггера

или хранимой

процедуры имеет следующий вид:

WHEN {<ошибка> [, <ошибка> ...] | ANY} DO

<составной оператор>

<ошибка> =

{EXCEPTION Имя | SQLCODE Номер | GDSCODE Код_Ошибки}

Параметры, входящие в этот оператор, описаны в табл. 3.

Таблица 3

Описание параметров оператора обработки исключений

Параметр Описание

ANY

Позволяет обработать все типы ошибок

EXCEPTION Имя

Обработка исключения, сгенерированного

пользователем (оператором EXCEPTION)

SQLCODE Номер

Обработка ошибки SQL

GDSCODE Код_Ошибки

Обработка ошибки InterBase

5.4.5. Операторы языка хранимых процедур и триггеров

В этом разделе приведены некоторые из используемых при написании

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

ше. Для изучения полного перечня поддерживаемых операторов следует об-

ратиться к [9].

5.4.5.1. IF … THEN … ELSE

Для реализации сложных алгоритмов сервер InterBase поддерживает

операторы ветвления.

IF (условие)

THEN <составной оператор>

[ELSE <составной оператор>]

5.4.5.2. FOR SELECT … DO

Представляет собой оператор выборки данных и обработки их в цикле

по записям.

FOR <выражение select>

DO <составной оператор>

5.4.5.3. WHILE … DO

Оператор цикла.

WHILE (условие) DO

<составной оператор>

5.5. Задание

Лабораторную работу следует выполнять в следующем порядке:

1. Создать аналогично предыдущим лабораторным работам рабочую папку и

назвать ее "ЛР5".

2. Скопировать в эту папку файл сценария, созданный при выполнении ла-

бораторной работы №4.

3. Открыть в приложении "IB Expert" этот сценарий.