Реализация ограничений целостности реляционной базы данных

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

Физическая модель БД

Описание физической модели базы данных включает в себя описание объектов БД. Есть несколько способов, с помощью которых структура объектов БД описывается для СУБД. Чаще всего для этого создается текстовый файл, который описывает эти структуры. Язык, используемый для определения структур объектов БД, называется языком определения данных (ЯОД). Альтернативой текстовому описанию является визуальный (графический) способ задания структур объектов БД, используемый, например, в СУБД Access.

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

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

— имя отношения (таблицы);

— имена атрибутов (полей);

— определение первичных ключей;

— определение уникальных (потенциальных) ключей;

— определение физических характеристик атрибута (тип и длину);

— определение обязательности значения атрибута;

— определение логических ограничений на значение атрибута.

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

Техническое описание можно представить в виде таблицы. Рассмотрим на примере. Допустим, есть следующая схема реляционной БД, содержащая следующие отношения: Категория должности (Код, Название, Краткое название); Должность (Код, Название, Краткое название, Код категории); Подразделение (Номер, Название, Аббревиатура, Код подразделения); Запись о работе сотрудника (Номер, Дата начала, Дата окончания, Ставка, Код должности, Код подразделения, Код сотрудника); Сотрудник (Табельный номер, Имя, Дата рождения, Пол).

Техническое описание таблицы «Должность» на ЯОД СУБД Access приведено в таблице 27, на ЯОД СУБД InterBase в таблице 28.

Таблица 27 – Реляционная таблица «Должность»

Таблица 28— Реляционная таблица «Dolgn»

Из таблиц 27 и 28 видно, как отличаются языки определения данных в СУБД Access и InterBase. СУБД InterBase использует ЯОД, являющийся диалектом стандарта языка SQL.

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

Поддержка целостности реляционной БД рассматривается в 3—х аспектах.

1 Целостность таблицы. Обязательно должны поддерживаться:

— уникальность строк таблицы. Должен быть определен первичный ключ таблицы, и значение его должно быть определено;

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

Эти ограничения реализуются в командах создания и модификации таблиц. Например, в языке SQL это команды Create Table, Alter Table. В этих командах для описания полей — первичных ключей используется конструкция Primary Key, для описания полей – уникальных ключей конструкция Unique, обязательность значений полей задается конструкцией Not Null.

2 Ссылочная целостность. Каждая таблица проектируемой БД должна быть связана с другими посредством соответствующих первичных и внешних ключей, т.е. быть либо родительской (главной) по отношению к другим таблицам, либо дочерней (подчиненной), либо той и другой для разного уровня связей. Назначение внешнего ключа — связывать каждую строку дочерней таблицы с соответствующей строкой родительской таблицы. Значение внешнего ключа может иметь и пустое значение (Null), если он реализует необязательную связь, выявленную в предметной области. В качестве значения внешнего ключа может выступать значение и любого уникального (потенциального) ключа. Чтобы в физическом проекте реализовать поддержку ссылочной целостности, необходимо знать ситуации, когда она может быть нарушена:

1) вставка новой строки в дочернюю таблицу. В этом случае значение атрибута внешнего ключа новой строки должно соответствовать конкретному значению, присутствующему в одной из строк родительской таблицы, либо должно быть равно пустому значению (Null). В противном случае целостность будет нарушена;

2) обновление внешнего ключа в строке дочерней таблицы. Ситуация достаточно редкая, должны поддерживаться требования предыдущего пункта;

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

а) No Action – удаление строки из родительской таблицы запрещено, если в дочерней таблице есть хотя бы одна ссылающаяся на неё строка;

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

в) Set Null – при удалении строки из родительской таблицы во всех ссылающихся на неё строках дочерней таблицы в атрибутах внешнего ключа записывается пустое значение (Null);

г) Set Default – при удалении строки родительской таблицы значение атрибутов внешнего ключа ссылающейся на неё строки дочерней таблицы автоматически замещаются значениями по умолчанию, определенными при создании дочерней таблицы;

д) No Check – при удалении строки из родительской таблицы никаких действий по сохранению ссылочной целостности не предпринимается;

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

Связи между таблицами (ссылочная целостность) могут быть заданы либо путем явного описания внешних ключей в структурах таблиц (что является более предпочтительным, как и любое другое явное описание), либо ссылочная целостность может поддерживаться с помощью триггеров. Например, в среде СУБД InterBase связь между двумя таблицами можно определить в команде Create Table при помощи конструкции Foreign Key, задающей явно поле – внешний ключ, ссылающийся на соответствующее поле — первичный ключ (конструкция References). В этом случае СУБД InterBase запрещает изменять значение первичного ключа, если на нее ссылаются какая—либо строка из дочерней таблицы и удалять запись в родительской таблице, если на неё есть ссылающаяся запись из дочерней таблицы. Таким образом связь, описанная в команде Create Table, блокирует каскадные изменения и удаления в родительской и дочерней таблицах. По умолчанию СУБД InterBase использует стратегию No Action.

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

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

3 Декларативные ограничения данных. Так называют ограничения реляционной базы данных, объявленные предметной областью и выявленные в ходе её анализа. Задача проектировщика БД — адекватно отобразить их в БД.

Самые распространенные ограничения предметной области – это ограничения на свойства объекта предметной области, далее атрибута отношения или поля таблицы:

— обязательность значения поля;

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

Такие ограничения рекомендуется задавать на уровне домена в командах Create Domain, Alter Domain. Также они могут быть заданы в командах создания и модификации таблиц — Create Table, Alter Table при описании поля таблицы.

Кроме ограничений предметной области, которые могут быть явно отображены на всех этапах проектирования БД на уровне моделей данных (например, использование семантических возможностей ER—диаграммы), существует ряд ограничений, которые выявлены, но не отражены в моделях данных, а описаны на естественном языке проектировщика. Например, это такие ограничения:

— если статус человека «не состоит в браке», то его следующим статусом может быть «в браке», но не в коем случае «разведен (а)»;

— если статус студента был «в академическом отпуске», то следующий статус может быть только «вышел из академического отпуска», но не в коем случае «отчислен»;

— общее количество действующих записей (текущая дата меньше даты окончания работы меньше) о работе человека не должно превышать больше 2—х (запрет двух совмещений);

— общее количество ставок, занимаемых работающим человеком, не может превышать 1,5 единиц;

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

Такие ограничения называют ограничениями перехода. Ранее рассмотренные ограничения можно отнести к ограничениям состояния.

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