Программирование на стороне сервера SQL

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

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

Цель работы

1. Изучить СУБД Firebird.

2. Изучить утилиту IBExpert.

3. Научиться созданию базы данных под управлением СУБД Firebird.

4. Научиться созданию доменов.

5. Научиться созданию таблиц базы данных под управлением СУБД Firebird.

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

7. Научиться созданию индексов.

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

9. Научиться заполнению информацией таблиц базы данных с использованием утилиты IBExpert.

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

1. Установить СУБД Firebird (при выполнении данной лабораторной работы дома).

2. Создать базу данных с использованием утилиты IBExpert под управлением СУБД Firebird (Например, G140501_Petrov.fdb, где G140501 – номер группы, Petrov – фамилия студента).

3. Используя физическую модель базы данных (полученную при выполнении лабораторной работы №1) создать домены. Результаты выполнения данного пункта должны быть представлены в таблицах №1 и №2. На основании данных приведенных в таблице №2 создать домены с использованием SQL в среде утилиты IBExpert.

Таблица №1

Таблица №2

4. С использованием утилиты IBExpert и SQL-скрипта (полученного при выполнении лабораторной работы №1) создать таблицы базы данных.

5. Создать ограничения в виде первичных и внешних ключей таблиц баз данных.

6. Создать необходимые индексы в таблицах.

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

8. Заполнить информацией таблицы базы данных.

9. Составить электронный отчет о проделанной работе.

Содержание отчета

1. Титульный лист.

2. Теоретическая часть (написать самостоятельно, что изучено, понято и сделано от 1 страницы).

3. Логическая и физическая модели данных полученные с помощью ERwin.

4. Примеры выполненных заданий.

5. SQL-скрипты, выполненных заданий.

6. Копии экрана, иллюстрирующие работу.

7. Пояснения к каждой копии экрана.

Теоретическая часть

Общая постановка задачи

Создать базу данных из 5 таблиц и заполнить их информацией с использованием утилиты IBExpert.

Программирование на стороне сервера SQL

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

Архитектура клиент-серверных БД предполагает разделение всей логики работы СУБД на две части: обслуживание данных и обслуживание клиентов. Первая часть реализуется SQL-сервером, вторая – клиентским программным обеспечением.

Сервер БД представляет собой достаточно сложный программный комплекс. В данной работе предлагается использовать сервер СУБД Firebird, разработанный в рамках Open Source-проекта. Для создания баз данных и разработки бизнес-правил на стороне сервера SQL предлагается использовать утилиту IBExpert.

В клиентском приложении предлагается использовать технологию доступа к данным InterBase Express. В качестве инструментального средства для разработки клиентских приложений предлагается использовать Builder C++ фирмы Borland.

1.1. СУБД Firebird

Промышленный сервер баз данных Firebird предназначен для решения широкого круга задач. Он сочетает в себе высокую надежность и простоту установки. Первая версия СУБД Firebird была выпущена 12 апреля 2002 года.

СУБД Firebird - это мощная, компактная реляционная система управления базами данных (РСУБД) с архитектурой клиент-сервер. Она может выполняться на разнообразных серверных и клиентских платформах, включая Windows, Linux и на некоторых других платформах UNIX, включая FreeBSD и Mac OS X. Это РСУБД промышленного применения, чьи возможности имеют высокий уровень соответствия стандартам SQL, при этом она реализует мощные расширения языка процедурного программирования конкретного производителя.

Созданный, как проект с открытыми исходными кодами, Firebird является первым в новом поколении потомков InterBase 6.0 Open Edition фирмы Borland, который был сформирован для разработки открытых исходных кодов в июле 2000 г. в рамках InterBase Public License (IPL).

Исходные коды Firebird поддерживаются и развиваются на основании международного открытого кода па сайте SourceForge.net (https://sourceforge.net), большой группой профессиональных разработчиков, в которую входят добровольцы и наемные специалисты, получающие частичное финансирование из сообщества и коммерческих источников.

Последняя версия СУБД Firebird 2.0 сервера обеспечивает поддержку параллельной работы на многопроцессорном оборудовании. С Borland Builder C++ поставляется ряд компонентов InterBase eXpress (IBX), позволяющих без особого труда работать с этим сервером. С самим сервером, помимо мощных консольных утилит, поставляется некоторое количество вспомогательных инструментов. Одной из таких утилит является Firebird Guardian.

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

На основе СУБД Firebird можно разрабатывать двух и трехзвенные приложения баз данных. При разработке трехзвенного приложения необходимо в качестве промежуточного звена использовать сервер приложения. Взаимодействие с сервером может осуществляться напрямую с использованием API-сервера, при помощи компонентов InterBase eXpress, либо через BDE или dbExpress.

1.1.1. Установка Firebird

Сервер Firebird в реализации для платформы Windows устанавливается очень просто. Достаточно запустить файл Firebird-Win32.exe. После этого можно нажать кнопку Next, показанную на рисунке 1.1.1. и указать путь расположения сервера.

Рисунок 1.1.1. Установка СУБД Firebird.

Стоит выбрать все доступные для установки элементы. Для продолжения работы нужно нажать кнопку Next, а затем Install.

1.1.2. Связь с сервером и соединение с базой данных

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

Научиться работать с утилитой IBExpert. После ее запуска появится окно, показанное на рисунке 1.1.2. Затем необходимо выбрать тип БД, как показано на рисунке 1.1.3.

Рисунок. 1.1.2. Окно запуска утилиты IBExpert

Рисунок. 1.1.3. Окно выбора типа БД

Затем следует устанавливать соединение с сервером, для этого необходимо выбрать тип сервера: Удаленный или Локальный. Если сервер удаленный то надо указать имя компьютера, на котором находится СУБД Firebird и выбрать протокол связи с этим сервером (например, TCP/IP). Далее необходимо выбрать версию сервера (например, Firebird 1.5).

Рисунок. 2.2.1.4. Окно открыть базу данных

В поле Файл базы данных нужно указать путь к базе данных, как показано на рисунке 1.1.4. После нажатия кнопки Открыть надо указать в поле Пользователь - SYSDBA, а в поле пароль — пароль masterkey. Имя пользователя SYSDBA является системным, и пароль для него устанавливается по умолчанию. Соответственно, в реальных разработках его необходимо будет сменить. В поле Кодировка выбрать WIN1251. Окно регистрации базы данных приведено на рисунке 1.1.5.

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

Таким образом, создается подключение к локальному серверу Firebird и устанавливается соединение с конкретной базой данных, находящейся на этом же компьютере (например, D: \moe\БДиСУБД\BD\REALT.FDB).

Рисунок. 1.1.5. Окно регистрации базы данных

Для того чтобы подключиться к удаленному серверу базы данных, используя IBExpert, необходимо в окне соединения с сервером (см рисунок 1.1.5.) выбрать пункт Remote (Удаленный), а в поле Server Name (Имя сервера) указать имя сервера. Из списка Network Protocol следует выбрать протокол соединения с базой данных и путь до базы данных. Например, для соединения с сервером через протокол TCP/IP следует выбрать данный протокол и в поле Server Name ввести IP-адрес сервера. Подключившись к серверу, можно подключить базу данных.

Форматы строк соединения для разных протоколов различаются. Для соединения по протоколу TCP/IP используется формат < server_name>: < filename>. Для соединения по протоколу NetBEUI строка принимает вид \\< server_name> \< filename>. А для соединения по протоколу SPX требуется строка вида < server_name> @< filename>.

Рисунок. 1.1.6. Результат соединения с сервером

1.1.3. Создание базы данных

Создание базы данных является несложным процессом. Нужно запустить утилиту IBExpert и выбрать в меню Базы данных пункт Создать базу, как показано на рисунке 1.1.7.

Затем следует устанавливать соединение с сервером, как показано на рисунке 1.1.8. В поле Файл БД указываются имя файла базы данных и путь до него, как указано на рисунке 1.1.9.

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

Рисунок. 1.1.7. Окно создать базу данных

В этом случае в поле FileName указывают имя первого файла и его размер в поле Size, во второй строчке — имя и размер вторичного файла. Размер страницы базы данных указывается в поле Page Size, он может принимать значения 1024, 2048, 4096, 8192 и 16384 байт. В поле Кодировка (Default Character Set) можно выбрать кодировку, которая будет использоваться по умолчанию. Кодировка определяет набор символов национального алфавита, который будет использоваться в базе данных по умолчанию.

Рисунок. 1.1.8. Окно создание базы данных

Рисунок. 1.1.9. Окно для задания имени файла базы данных

Если предстоит работать только с русским и английским языками, то имеет смысл выставить значение WIN 1251. В поле SQL Dialect следует выбрать используемый диалект языка SQL. Также потребуется установить флажок «Зарегистрировать после создания» (Register database) для того, чтобы при создании база данных была зарегистрирована.

После нажатия кнопки «ОК» базу данных надо будет зарегистрировать, как показано на рисунке 1.1.10. После этого база данных Proba.fdb будет создана, список ее объектов приведен на рисунке 1.1.11.

Рисунок. 1.1.10. Окно регистрации созданной базы данных

Рисунок. 1.1.11. Список объектов базы данных Proba.fdb

1.1.4. Страницы базы данных

База данных Firebird состоит из последовательно пронумерованных страниц. Нулевая страница является системной и содержит служебную информацию.

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

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

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

1.1.5. Размер страницы базы данных

Файл базы данных разбивается на страницы фиксированного размера. Сервер Firebird постранично считывает и записывает изменения в базу данных. Таким образом, чтобы произвести какую либо операцию с записью, он считывает всю страницу. Но при этом рекомендуется устанавливать размер страницы не менее 4096 байт.

Предположим, что запись имеет несколько десятков полей, каждое из которых занимает несколько десятков байт. Такая запись при малом объеме страницы будет занимать несколько страниц. Следовательно, для того чтобы осуществить с ней какую-либо операцию, сервер будет вынужден обратиться к диску несколько раз, что само по себе отрицательно скажется на производительности. Как было отмечено ранее, размер страницы в Firebird 2.0 может принимать значения 1024, 2048, 4096 и 8192 байт. Если база данных располагается на диске с файловой системой NTFS, размер страницы следует устанавливать 4096 байт (равным размеру кластера), если файловой системой является FAT32, то лучше под страницу отводить 8192 байтов.

1.1.6. Диалект базы данных

В ходе эволюции Firebird в разных версиях изменялись типы данных и используемые операторы языка SQL. Этот процесс породил необходимость создания диалектов — форматов типов данных. На данный момент существует три диалекта — 1, 2 и 3. Первый диалект поддерживают серверы InterBase версии 4 и 5, а третий диалект поддерживается серверами InterBase, начиная с шестой версии и сервер Firebird. В третьем диалекте выделены поля даты и времени. Также введены типы данных для работы с большими целыми числами.

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

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

1.1.7. Технические характеристики СУБД Firebird

Большинство фактических ограничений Firebird практически шире того, что нужно в программах. Например, вы можете определить до 32 7671 столбцов в таблице, однако зачем вы будете это делать? В таблице 1.1. представлены теоретические и практические ограничения, применимые к Firebird 1.5. Некоторые из этих ограничений будут изменены в сторону улучшения в следующих версиях.

Таблица 1.1. Ограничения СУБД Firebird 1.5

1.1.8. Типы данных в СУБД Firebird

Firebird поддерживает большинство типов данных SQL 92, поля типа BLOB и массивы. Любой тип данных имеет набор операций, которые можно выполнять со значениями этого типа, поэтому необходимо правильно выбрать тип на этапе разработки базы данных. В Firebird версии 2.0 определено 13 типов данных:

BLOB — тип данных с динамически изменяемым размером, предназначенный для хранения данных большого размера. В этих полях хранятся графические данные, большие массивы текстов, музыка и многое другое. Данные хранятся в сегментах размером по 64 Кбайт.

Boolean — логическое поле. Может принимать значения True, False и Unknown. Имеет размер два байта.

CHAR(n) — символьное поле фиксированной длины. Размер поля определяется на этапе проектирования и указывается в качестве параметра п. Поле может занимать до 32 767 байт.

DATE — поле даты. Может принимать значение от 1 января 100 года до 29 февраля 32768 года. Поле занимает четыре байта.

DECIMAL — числовой тип данных с фиксированной точкой. Тип данных принимает в качестве аргументов разрядность и точность хранимых чисел. Разрядность определяет общее число цифр, а точность — число цифр после запятой. Разрядность может быть определена в пределах от 1 до 18 знаков, а точность — достигать определенной перед этим разрядности. Поле может занимать размер 16, 32 или 64 бита.

DOUBLE PRECISION — вещественный тип данных повышенной точности. Число может находиться в диапазоне от 2, 25х10^-308 до 1, 797х10³⁰⁸ и иметь размер до 15 знаков. Поле занимает восемь байтов.

FLOAT — вещественный тип данных. Число может находиться в диапазоне от 1, 175x10^-38 доЗ, 4002х10³⁸ и иметь размер до 15 знаков. Поле занимает четыре байта.

INTEGER — знаковый целочисленный тип данных. Может принимать значение в диапазоне от -2 147 483 648 до 2 147 483 647. Поле занимает четыре байта.

NUMERIC — эквивалентен типу DECIMAL.

SMALLINT — знаковый целочисленный тип данных. Может принимать значение в диапазоне от -32 768 до 32 767. Поле занимает два байта.

TIME —хранит данные о времени с точностью до десятитысячной доли секунды. Может принимать значение в диапазоне от 00: 00 до 23: 59.9999.

TIМЕSТАМР — тип данных, хранящий информацию о дате и времени. Фактически, представляет собой комбинацию типов DATE и TIME.

VARCHAR (п) — символьное поле переменной длины. Размер поля определяется на этапе проектирования и указывается в качестве параметра п. Поле может занимать до 32 767 байт.

Массивы. СУБД Firebird позволяет создавать однородные массивы для большинства типов данных. Использование массива позволяет хранить множество элементов данных в виде дискретных, многомерных элементов в одном столбце. СУБД Firebird может выполнять опера­ции над целым массивом, трактуя его как один элемент, или он может оперировать с частью массива — подмножеством элементов массива. Часть массива может состоять из одного элемента или из набора многих смежных элементов.

Использование массивов желательно, когда:

1. элементы данных естественно принимают вид множества данных одного типа;

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

3. к каждому элементу также должен быть индивидуальный доступ;

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

Массивы могут содержать элементы любого поддерживаемого Firebird типа за исключением BLOB Массивы массивов не поддерживаются. Все элементы конкретного массива имеют один и тот же тип данных.

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

Например, A char(10) [8] – это 8 элементов в 1 строке, а

В INTEGER[4, 5] – 4 строки по 5 элементов.

Firebird поддерживает многомерные массивы размерностью от 1 до 16.

Firebird хранит многомерные массивы в порядке развертывания по строкам.

1.2. Создание Доменов

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

CREATE DOMAIN имя домена [AS] < datatype>

[DEFAULT { literal | NULL | USER}]

[NOT NULL]

[CHECK (< dom_search_condition>)]

[CHARSET {набор символов | NONE}]

[COLLATE collation];

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

CREATE DOMAIN TEST AS VARCHAR(20);

В определении домена можно указать значение, которое будет присваиваться полю по умолчанию. Это значение указывается после оператора DEFAULT. Значение по умолчанию может иметь строковый тип, числовой, дату, NULL либо USER.

Константа USER содержит в себе имя пользователя, работающего с базой в данный момент, в данной сессии. Это иллюстрирует следующий пример:

CREATE DOMAIN TESTDOMAIN AS VARCHAR(20) DEFAULT USER;

CREATE TABLE TESTTABLE (OrderJDate DATE,. Entered_By TESTDOMAIN);

INSERT INTO TESTTABLE (ORDER_DATE) VALUES ('01.01.2005');

Значением по умолчанию является константа USER. Данный код создает таблицу TESTTABLE с двумя полями. При вводе одного из значении тюле даты Order_Date во второе поле Entered_By, созданное на базе домена TEST DO MAIN, автоматически будет вводиться новое значение.

Также можно ввести требование обязательного ввода значения в объявляемое поле. Реализуется это при помощи оператора NOT NULL, как показано в примере:

CREATE DOMAIN TEST1 INTEGER NOT NULL;

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

< операторы соответствия> = {

VALUE < operator> < val>

| VALUE [NOT] BETWEEN < val> AND < val>

| VALUE [NOT] LIKE < val> [ESCAPE < val> ]

| VALUE [NOT] IN (< val> [, < val>...])

| VALUE IS [NOT] NULL

| VALUE [NOT] CONTAINING < val>

| VALUE [NOT] STARTING [WITH] < val>

(< dom_search_condition>)

NOT < dom_search_condition> | < dom_search_condition> OR < dom_search_condition> | < dom_search_condition> AND < dom_search_condition> }

< операторы отношения> = {= | < | > | < = | > = |! < | i> | о |! =}

Можно привести небольшой пример использования этого ограничения:

CREATE DOMAIN CHECKDOMAIN AS INTEGER DEFAULT 162 CHECK (VALUE < 10);

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

Оператор COLLATE позволяет определить порядок сравнения символьных данных:

CREATE DOMAIN COLDOM AS CHAR(30) CHARACTER SET WIN1251 COLLATE PXW_CYRL;

Для изменения определения домена следует воспользоваться командой ALTER DOMAIN, синтаксис которой приведен ниже:

ALTER DOMAIN name {

[SET DEFAULT { literal | NULL | USER}]

| [DROP DEFAULT]

| [ADD [CONSTRAINT] CHECK (< dom_search_condition>)]

| [DROP CONSTRAINT]

| new_col_name

| TYPE data_type

};

Например, для изменения имени домена TEST1 на NEWTEST должен использоваться следующий SQL-запрос:

ALTER DOMAIN TEST1 ТО NEWTEST;

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

ALTER DOMAIN CHECKDOMAIN DROP CONSTRAINT;

Рассмотрим пример создания доменов с использованием утилиты IBExpert при разработке информационной системы риелторовской фирмы «Аренда недвижимости». Описание типов полей базы данных приведено в таблице 1.2.1.

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

Скрипты на языке SQL для создания одного домена приведен ниже

CREATE DOMAIN D_INDEXTYPE AS

SMALLINT NOT NULL CHECK (value> 0)

Рисунок. 1.2.1. Структурная схема базы данных Realt.fdb

Таблица 1.2.1. Типы данных полей таблиц базы данных Realt.fdb

Таблица 1.2.1. Типы доменов базы данных Realt.fdb

Создание домена D_ADRES с использованием утилиты IBExpert показано на рисунке 1.2.1.

Результат создания доменов базы данных Realt.fdb с использованием утилиты IBExpert показан на рисунке 1.2.2.

Рисунок. 1.2.1. Создание домена D_ADRES с использованием утилиты IBExpert

Рисунок. 1.2.1. Домены базы данных Realt.fdb

1.3. Создание таблиц

Для создания таблицы применяется команда CREATE TABLE, синтаксис которой определен ниже:

CREATE TABLE Table [EXTERNAL [FILE] 'filespec']

(< col_def> [, < col_def> | < tconstraint> … ]);

В параметре Table указывается имя создаваемой таблицы. В параметре EXTERNAL [FILE] указывается файл, содержащий внешнюю таблицу с данными.

Параметр < col_def> предназначен для описания поля таблицы. Синтаксис описания поля приведен ниже:

< col_def> = col {datatype | COMPUTED [BY] (< ехрr>) | domain}

[DEFAULT { literal | NULL | USER}]

[NOT NULL] [ < col_constraint> ]

[COLLATE collation]

< col constraint = [CONSTRAINT constraint] < constraint def>

[ < col_constraint>...]

< constraint_def> = {UNIQUE | PRIMARY KEY |

CHECK (< search _condition>) |

REFERENCES other _table [(other_col [, other_col...])]

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

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

CREATE TABLE MYTABLE (

MyFieldl CHAR(6),

MyField2 CHAR(50) CHARACTER SET WIN1251,

MyField3 INTEGER);

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

< co1_name> computed [by] (< expr>)

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

CREATE TABLE EMPLOYEE

(FIRST_NAME VARCHAR(10) NOT NULL,

LAST_NAME VARCHAR(50) NOT NULL,

FULL_NAME COMPUTED BY (LAST_NAME || '. ' || FIRST_NAME));

Поле FULL_NAME является вычисляемым. В нем будет храниться результат объединения двух строк из предыдущих полей. Если тип возвращаемого выражением значения не указан, Firebird самостоятельно определяет его тип в процессе вычислений.

1.3.1. Ограничения в таблицах

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

СУБД Firebird предоставляет два вида ограничений:

Ограничение целостности на уровне столбца за счет первичного ключа PRIMARY KEY;

Ссылочное ограничение за счет использования вторичного ключа FOREIGN KEY.

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

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

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

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

1.3.1.1. Синтаксис объявления первичного ключа

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

Ограничение primary key может применяться в любой из следующих фаз создания

в определении столбца в операторах CREATE TABLE или alter table как часть определения столбца;

в определении таблицы в операторах CREATE TABLE или alter table как отдельно определенное ограничение таблицы.

Ниже приведен пример объявления первичного ключа в определения столбца таблицы для примера, приведенного на рисунке 1.2.1.

CREATE TABLE OWNER (

ID D_INDEXTYPE PRIMARY KEY,

FIO D_NAME,

ADRES D_ADRES)

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

CREATE TABLE TENANT (

ID D_INDEXTYPE,

FIO D_NAME,

ADRES D_ADRES,

CONSTRAINT PK_TENANT PRIMARY KEY(ID))

В приведенном примере PK_TENANT – это имя ограничения.

1.3.1.2. Ссылочная целостность данных

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

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

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

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

Стандартная модель объект-отношение описывает простое отношение многим, между двумя сущностями, как показано на рисунке 1.3.1.

Рисунок 1.3.1. Модель объект-отношение описывает между двумя сущностями

Если мы реализуем такую модель между двумя таблицами parent и child, to строки в таблице child зависят от существования связанной строки из parent. Ограничение FOREIGN key в СУБД Firebird осуществляет это отношение следующими способами:

1. требуется, чтобы значение столбца внешнего ключ в таблице child (child.pareht_id) могло быть связано с соответствующим значением уникального ключа (в нашем случае, первичного ключа) в таблице parent (parent.id);

2. по умолчанию запрещено удаление строки parent или изменение значения уникального ключа, если существуют зависимые строки в child;

3. должно быть реализовано отношение, которое предполагалось во время создания ссылки или когда оно в последний раз изменялось;

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

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

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

CREATE TABLE PARENT (

ID BIGINT NOT NULL,

DATA VARCHAR(20),

CONSTRAINT PK__PARENT PRIMARY KEY(ID));

COMMIT;

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

CREATE TABLE CHILD (

ID BIGINT NOT NULL,

CHILD_DATA. VARCHAR(20),

PARENT_ID BIGINT,

CONSTRAINT PK_CHILD PRIMARY KEY(ID));

COMMIT;

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

1.3.1.3. Синтаксис объявления вторичного ключа

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

FOREIGN KEY (столбец [, столбец...))

REFERENCES (родительская-таблица (столбец [, столбец...]))

[USING [ASC | DESC ] INDEX имя-индекса]

[ ОN DELETE { NО ACTION | CASCADE | SET NULL | SET DEFAULT }]

[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }]

Определим внешний ключ:

ALTER TABLE CHILD

ADD CONSTRAINT FK_CHILD_PARENT

FOREIGN KEY(PARENT_ID)

REFERENCES PARENT(ID);

/* также допустимо REFERENCES PARENT,

поскольку ID является первичным ключом таблицы PARENT */

Firebird сохраняет ограничение FK_CHILD_PARENT и создает обычный индекс для столбца, (столбцов), перечисленных в качестве аргументов foreign key. В Firebird этот индекс будет также назван fk_child_parent, если вы не использовали необязательное предложение -jsisg для задания другого имени индекса.

Наши две таблицы теперь связаны ограничением формальной ссылочной целостности. Мы можем добавлять новые строки в таблицу parent без каких-либо ограничений:

INSERT INTO PARENT (ID, DATS)

VALUES (1, 'Parent No. 1');

При этом существует ограничение для child. Мы можем выполнить следующее:

INSERT INTO CHILD (ID, CHILD_DATA)

VALUES (1, ' CHILD No. 1');

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

INSERT INTO CHILD (ID, CHILD_DATA, PARENT_ID)

VALUES (2, 'Child Mo. 2', 2};

1.3.2. Действия триггеров по изменению правил целостности

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

Язык SQL в Firebird позволяет сделать это с помощью необязательных автоматических действий триггеров:

[ON DELETE {NО ACTION | CASCADE | SET NULL | SET DEFAULT}]

[ON UPDATE {NO ACTION | CASCADE | SET NULL | SET DEFAULT}]

1.3.2.1. Автоматические действия триггеров

Firebird предоставляет необязательные стандартные события DML — ON DELETE и on UPDATE, — используемые для изменения правил ссылочной целостности. События DML и автоматическое поведение совместно определяют действия для триггера, — какие действия должны быть выполнены для зависимой таблицы при изменении или удалении соответствующего ключа в родительской таблице. Определение действий включают каскадные изменения в связанной через внешний ключ таблице (таблицах).

Рассмотрим семантику действий триггера:

NO ACTION - это действие триггера по умолчанию. Операция DML над родительским первичным ключом не изменяет внешний ключ и потенциально может привести к ошибке операции над родительской таблицей;

ON UPDATE CASCADE – в этом случае в зависимой таблице внешний ключ, соответствующий старому значению первичного ключа, изменяется на новое значение первичного ключа;

ON DELETE CASCADE в этом случае в зависимой таблице удаляются строки с соответствующим значением ключа;

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

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

Используется значение по умолчанию, которое существовало в момент создания ограничения foreign key. Если значение по умолчанию у столбца будет изменено позже, то значение по умолчанию для действия set default в определении внешнего ключа не будет изменено на новое значение - оно сохранит прежнее значение.

Если никакое значение по умолчанию не было явно установлено для столбца, то неявным значением по умолчанию будет null. В этом случае поведение при set default будет тем же самым, что и при set null

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

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

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

Рассмотрим пример создания таблиц базы данных информационной системы «Аренда недвижимости». Ниже приведен SQL-скрипт для создания таблицы LEASE предназначенной для хранения информации о заключенных договорах на аренду недвижимости.

/****************************************************/

/*** Tables ***/

/****************************************************/

CREATE TABLE LEASE (

ID D_INDEXTYPE,

ID_OWNER D_INDEXTYPE,

ID_TENANT D_INDEXTYPE,

ID_REALTY D_INDEXTYPE,

PRICE D_PRICE NOT NULL,

" DATE" D_DATETYPE

);

/****************************************************/

/*** Primary Keys ***/

/****************************************************/

ALTER TABLE LEASE ADD CONSTRAINT PK_LEASE PRIMARY KEY (ID);

/**************************** ***********************/

/*** Foreign Keys ***/

/******************************************** *******/

ALTER TABLE LEASE ADD CONSTRAINT FK_LEASE_1 FOREIGN KEY (ID_REALTY) REFERENCES REALTY (ID);

ALTER TABLE LEASE ADD CONSTRAINT FK_LEASE_2 FOREIGN KEY (ID_OWNER) REFERENCES OWNER (ID);

ALTER TABLE LEASE ADD CONSTRAINT FK_LEASE_3 FOREIGN KEY (ID_TENANT) REFERENCES TENANT (ID);

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

На рисунке 1.3.2. приведен список таблиц базы данных Realt.fdb созданных с использованием утилиты IBExpert.

На рисунке 1.3.3. приведен пример создания первичного ключа в таблице LEASE с использованием утилиты IBExpert.

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

На рисунке 1.3.5. Приведены данные введенные в таблицу LEASE.

На рисунке 1.3.6. Приведена форма с данными таблицы LEASE.

На рисунке 1.3.7. Приведен пример печати данных таблицы LEASE.

На рисунках 1.3.8- 1.3.12. приведена процедура ввода информации в таблицу LEASE

Рисунок 1.3.2. Список полей таблицы LEASE базы данных Realt.fdb

Рисунок 1.3.3. Пример создания первичного ключа в таблице LEASE

Рисунок 1.3.4. Пример создания вторичных ключей в таблице LEASE

Рисунок 1.3.5. Данные в таблице LEASE

Рисунок 1.3.6. Форма с данными таблицы LEASE

Рисунок 1.3.7. Пример печати данных таблицы LEASE

Рисунок 1.3.8. Ввод информации в поле ID_OWNER таблицы LEASE

Рисунок 1.3.9. Ввод информации в поле ID_TENANT таблицы LEASE

Рисунок 1.3.10. Ввод информации в поле ID_REALTY таблицы LEASE

Рисунок 1.3.11. Ввод информации в поле PRICE таблицы LEASE

Рисунок 1.3.12. Ввод информации в поле DATE таблицы LEASE

1.4. Создание индексов

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

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

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

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX IndexName ON table (col [, col...]);

Сервер Firebird автоматически создает уникальный индекс при создании ограничений PRIMARY KEY и UNIQUE для столбца или группы столбцов. Значения ключевых полей, входящих в уникальный индекс, не могут дублироваться. Используя операторы ASC[ENDING] и DESC[ENDING], можно определить порядок сортировки значений в индексе. Константа IndexName определяет имя индекса.

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

SELECT MARKAKONFET, VES FROM MYTINT

GROUP BY MARKAKONFET,

VES HAVING COUNT(*) > 1;

Для создания уникального индекса по полям MarkaKonfet и Ves для таблицы MYTINT с порядком сортировки по возрастанию используется другой запрос:

CREATE UNIQUE ASC INDEX MYINDEX ON MYTINT (MARKAKONFET. VES);

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

перестроить индекс, используя команду ALTER INDEX;

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

пересоздать индекс, используя команды DROP INDEX и CREATE INDEX;

произвести резервирование и восстановление базы данных.

Команда ALTER INDEX имеет следующий синтаксис:

ALTER INDEX name {ACTIVE | INACTIVE};

Параметр INACTIVE переводит индекс в неактивное состояние. При его активации параметром ACTIVE производится перестройка индекса. Для перестройки индекса MYINDEX применяется следующий запрос:

ALTER INDEX MYINDEX INACTIVE:

ALTER INDEX MYINDEX ACTIVE:

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

SET STATISTICS IndexName;

Для удаления индекса используется команда DROP INDEX. В качестве иллюстрации можно привести SQL-запрос, удаляющий индекс MYINDEX:

DROP INDEX MYINDEX

На рисунке 1.4.1. приведен индекс _IDX1 в таблице OWNER по полю FIO для базы данных Realt.fdb созданный с использованием утилиты IBExpert.

Рисунок 1.4.1. Индекс _IDX1 в таблице OWNER по полю FIO

1.5. Генераторы

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

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

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

CREATE GENERATOR name;

Для того чтобы инициализировать генератор начальным значением, следует использовать команду SET GENERATOR, имеющую следующий синтаксис:

SET GENERATOR NAME TO int;

Следующая команда задает для генератора MYGEN стартовое значение 10:

SET GENERATOR MYGEN TO 10;

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

После того как генератор создан и инициализирован, его можно использовать с помощью функции SQL GEN_ID().

Получения следующее значение генератора можно следующим образом:

GEN_ID(ИмяГенератора, N),

где ИмяГенератора – имя генератора заданное командой SET GENERATOR, а N – целое число определяющее значение шага.

Следующий запрос:

SELECT GEN_ID(AGen, 2) FROM RDB$DATABASE;

возвращает число, которое на 2 больше последнего сгенерированного числа, и устанавливает значение генератора в сгенерированное значение.

Ниже приведен SQL-скрипт для создания генератора в базе данных Realt.fdb

CREATE GENERATOR GEN_LEASE_ID;

SET GENERATOR GEN_LEASE_ID TO 15;

На рисунке 2.2.5.1. приведены генераторыбазы данных Realt.fdb

Рисунок 2.2.5.1. Генераторыбазы данных Realt.fdb

1.6. Триггеры

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

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

2. возможностью ведения истории действий;

3. автоматическим оповещением об изменениях, вносимых в базу данных.

Триггер создается командой CREATE TRIGGER, имеющей следующий синтаксис:

CREATE TRIGGER name FOR { table | view}

[ACTIVE | INACTIVE]

{BEFORE | AFTER}

{DELETE | INSERT | UPDATE}

[POSITION number]

AS < trigger_body>

trigger_body> = [< variable_declaration_list> ] < block>

variable_declaration_list> = DECLARE VARIABLE variable datatype;

[DECLARE VARIABLE variable datatype;...]

< block>

BEGIN

< compound_statement> [< compound_statement>...]

END

< compound_statement> = {< block> | statement; }

В параметре name указывается имя триггера. В параметре table указывается имя таблицы или представления, с которым будет связан триггер.

Параметр ACTIVE | INACTIVE определяет активность триггера. Параметры BEFORE и AFTER определяют, до или после совершения события будет вызван триггер, а параметры DELETE | INSERT | UPDATE определяют событие, при наступлении которого триггер сработает.

Параметр POSITION number определяет порядок, в котором будут выполняться триггеры. Этот параметр используется для триггеров, которые имеют о