5 Целостная составляющая реляционной модели данных
5.1 Понятия ограничения целостности. Аспекты классификации ограничений целостности
Для иллюстрации возможного нарушения целостности базы данных рассмотрим следующие примеры:
Пример 1. Пусть имеется БД, в которой хранятся данные о подразделениях и работающих в них сотрудниках.
Список подразделений хранится в таблице:
DEPART(Dep_Id, Dep_Name, Dept_Kol),
где Dept_Id - идентификатор подразделения;
Dept_Name - наименование подразделения;
Dept_Kol - количество сотрудников в подразделении.
Список сотрудников хранится в таблице:
PERSON(Pers_Id, Pers_Name, Dept_Id),
где Pers_Id - идентификатор сотрудника;
Pers_Name - имя сотрудника;
Dept_Id - идентификатор подразделения, в котором работает сотрудник.
|
Таблица 1 DEPART |
||
|
Dept_Id |
Dept_Name |
Dept_Kol |
|
1 |
Кафедра алгебры |
3 |
|
2 |
Кафедра программирования |
2 |
|
Таблица 2 PERSON |
||
|
Pers_Id |
Pers_Name |
Dept_Id |
|
1 |
Иванов |
1 |
|
2 |
Петров |
2 |
|
3 |
Сидоров |
1 |
|
4 |
Пушников |
2 |
|
5 |
Шарипов |
1 |
Ограничение целостности этой базы данных состоит в том, что поле Dept_Kol не может заполняться произвольными значениями - это поле должно содержать количество сотрудников, реально числящихся в подразделении.
С учетом этого ограничения можно заключить, что вставка нового сотрудника в таблицу не может быть выполнена одной операцией. При вставке нового сотрудника необходимо одновременно увеличить значение поля Dept_Kol.
Шаг 1. Вставить сотрудника в таблицу PERSON: INSERT INTO PERSON (6, Мифтахов, 1).
Шаг 2. Увеличить значение поля Dept_Kol: UPDATE DEPART SET Dept=Dept+1 WHERE Dept_Id=1.
Если после выполнения первой операции и до выполнения второй произойдет сбой системы, то реально будет выполнена только первая операция и база данных остается в нецелостном состоянии.
Ограничение целостности - это некоторое утверждение, которое может быть истинным или ложным в зависимости от состояния базы данных.
Примерами ограничений целостности могут служить также и следующие утверждения.
Пример 2. Возраст сотрудника не может быть меньше 18 и больше 65 лет.
Пример 3. Каждый сотрудник имеет уникальный табельный номер.
Пример 4. Сотрудник обязан числиться в одном отделе.
Пример 5. Сумма накладной обязана равняться сумме произведений цен товаров на количество товаров для всех товаров, входящих в накладную.
База данных находится в согласованном (целостном) состоянии, если выполнены (удовлетворены) все ограничения целостности, определенные для базы данных.
В данном определении важно подчеркнуть, что должны быть выполнены не все вообще ограничения предметной области, а только те, которые определены в базе данных. Для этого необходимо, чтобы СУБД обладала развитыми средствами поддержки ограничений целостности.
Вместе с понятием целостности базы данных возникает понятие реакции системы на попытку нарушения целостности. Система должна не только проверять, не нарушаются ли ограничения в ходе выполнения различных операций, но и должным образом реагировать, если операция приводит к нарушению целостности. Имеется два типа реакции на попытку нарушения целостности:
1. отказ выполнить "незаконную" операцию;
2. выполнение компенсирующих действий.
Например, если система знает, что в поле "Возраст_Сотрудника" должны быть целые числа в диапазоне от 18 до 65, то система отвергает попытку ввести значение возраста 66. При этом может генерироваться какое-нибудь сообщение для пользователя.
В противоположность этому, в примере 1 система допускает вставку записи о новом сотруднике (что приводит к нарушению целостности базы данных), но автоматически производит компенсирующие действия, изменяя значение поля Dept_Kol в таблице DEPART.
В некоторых случаях система может не выполнять проверку на нарушение ограничений, а сразу выполнять компенсирующие операции. Действительно, в примере 1 при вставке или удалении сотрудника проверку производить не нужно, т.к. результаты ее известны заранее - ограничение обязательно будет нарушено. В этом случае необходимо сразу приступать к компенсированию возникшего нарушения.
Ограничения целостности можно классифицировать несколькими способами:
- по области действия;
- по времени проверки;
- по способам реализации.
5.2 Ограничения целостности по области действия
По области действия ограничения делятся на:
- ограничения домена;
- ограничения атрибута;
- ограничения кортежа;
- ограничения отношения (целостность сущностей);
- ограничения базы данных (целостность по ссылкам).
Ограничения целостности домена представляют собой ограничения, накладываемые только на допустимые значения домена (если СУБД поддерживает понятие домена). Фактически, ограничения домена обязаны являться частью определения домена. Например, ограничением домена "Возраст сотрудника" может быть условие "Возраст сотрудника не менее 18 и не более 65".
Ограничения домена сами по себе не проверяются. Если на каком-либо домене основан атрибут, то ограничение соответствующего домена становится ограничением этого атрибута.
Ограничение целостности атрибута представляют собой ограничения, накладываемые на допустимые значения атрибута. Ограничение атрибута в точности совпадают с ограничениями соответствующего домена вследствие того, что атрибут основан на каком-либо домене. Отличие ограничений атрибута от ограничений домена в том, что ограничения атрибута проверяются. Например, ограничение домена из предыдущего примера, можно оформить ограничением атрибута: «18 лет < Возраст сотрудника < 65 лет» Еще одним примером ограничения на атрибут "Табельный номер сотрудника" может быть условие "таб_номер > 0".
Ограничения целостности кортежа представляют собой ограничения, накладываемые на допустимые значения отдельного кортежа (строки таблицы). Требование, что ограничение относится к отдельному кортежу отношения, означает, что для его проверки не требуется никакой информации о других кортежах отношения (строчках таблицы).
Пример 6. Атрибут "Возраст сотрудника" в таблице "Спецподразделение", может иметь дополнительное ограничение "Возраст сотрудника не менее 25 и не более 45", помимо того, что этот атрибут уже имеет ограничение, определяемое доменом - "Возраст сотрудника не менее 18 и не более 65".
Ограничения целостности отношения представляют ограничения, накладываемые только на допустимые значения отдельного отношения. Требование, что ограничение относится к отдельному отношению, означает, что для его проверки необходимо иметь информацию обо всех кортежах данного отношения, но не требуется информации о других отношениях.
Примером ограничения отношения является требование целостности сущностей. Оно состоит в том, что любой кортеж любого отношения отличим от любого другого кортежа этого отношения, т.е. другими словами, любое отношение должно обладать первичным ключом.
Для соблюдения целостности сущности достаточно гарантировать отсутствие в любом отношении кортежей с одним и тем же значением первичного ключа.
Пример 7. Предположим, что в отношении PERSON задано следующее ограничение - в каждом отделе должно быть не менее двух сотрудников. Это ограничение можно сформулировать так - количество строк с одинаковым значением Dept_Id должно быть не меньше 2.
Для того чтобы ввести в действие (объявить) это ограничение, необходимо, чтобы в отношение уже были вставлены некоторые кортежи.
Ограничения целостности базы данных представляют ограничения, накладываемые на значения двух или более связанных между собой отношений (в том числе отношение может быть связано само с собой).
Ограничение целостности ссылок, задаваемое внешним ключом отношения, является ограничением базы данных.
Вернемся к примеру 1.
Атрибут Dept_Id появляется в отношении PERSON (Сотрудники) не потому, что номер отдела является собственным свойством сотрудника, а лишь для того, чтобы иметь возможность восстановить при необходимости полную сущность DEPART (Отделы). Значение атрибута Dept_Id в любом кортеже отношения PERSON (Сотрудники) должно соответствовать значению атрибута Dept_Id в одном из кортежей отношения DEPART (Отделы).
Атрибут такого рода называется внешним ключом, поскольку его значения однозначно характеризуют сущности, представленные кортежами некоторого другого отношения (т.е. задают значения их первичного ключа). Говорят, что отношение, в котором определен внешний ключ, ссылается на соответствующее отношение, в котором такой же атрибут является первичным ключом.
Требование целостности по ссылкам, или требование внешнего ключа состоит в том, что для каждого значения внешнего ключа, появляющегося в дочернем (ссылающемся) отношении, в родительском отношении, на которое ведет ссылка, должен найтись кортеж с таким же значением первичного ключа, либо значение внешнего ключа должно быть неопределенным (т.е. ни на что не указывать). Для нашего примера это означает, что если для сотрудника указан номер отдела, то этот отдел должен существовать.
Ограничения целостности по ссылкам должны поддерживаться СУБД.
Понятно, что при обновлении дочернего (ссылающегося) отношения (вставке новых кортежей или модификации значения внешнего ключа в существующих кортежах) достаточно следить за тем, чтобы не появлялись некорректные (не существующие в родительской таблице) значения внешнего ключа.
При удалении кортежа из родительского отношения - на которое ведет ссылка - существуют три подхода, каждый из которых поддерживает целостность по ссылкам.
Первый подход заключается в том, что запрещается производить удаление кортежа, на который существуют ссылки (т.е. сначала нужно либо удалить ссылающиеся кортежи, либо соответствующим образом изменить значения их внешнего ключа).
При втором подходе при удалении кортежа, на который имеются ссылки, во всех ссылающихся кортежах значение внешнего ключа автоматически становится неопределенным.
Наконец, третий подход (каскадное удаление) состоит в том, что при удалении кортежа из отношения, на которое ведет ссылка, из ссылающегося отношения автоматически удаляются все ссылающиеся кортежи.
К моменту проверки ограничения базы данных должны быть проверены ограничения целостности отношений.
5.3 Ограничения целостности по времени проверки
По времени проверки ограничения делятся на:
- немедленно проверяемые ограничения;
- ограничения с отложенной проверкой.
Немедленно проверяемые ограничения проверяются непосредственно в момент выполнения операции, которая может нарушить ограничение. Если ограничение нарушается, то такая операция отвергается. Транзакция, внутри которой произошло нарушение немедленно проверяемого утверждения целостности, обычно откатывается.
Ограничение домена, атрибута и кортежа являются немедленно проверяемыми ограничениеми, так как не зависит ни от каких других объектов базы данных, кроме домена, на котором основан атрибут. Поэтому никакие изменения в других объектах не могут повлиять на истинность ограничения.
Ограничение отношения может быть как немедленно проверяемым ограничением, так и ограничением с отложенной проверкой.
Требование целостности сущностей является немедленно проверяемым ограничением.
Ограничения с отложенной проверкой проверяется в момент фиксации всей транзакции оператором COMMIT WORK. Внутри транзакции ограничение может не выполняться. Если в момент фиксации транзакции обнаруживается нарушение, то транзакция откатывается.
Примером ограничения, которое не может быть проверено немедленно является ограничение из примера 1. Это происходит оттого, что транзакция, заключающаяся во вставке нового сотрудника в таблицу PERSON, состоит не менее чем из двух операций - вставки строки в таблицу PERSON и обновления строки в таблице DEPART. Ограничение, безусловно, неверно после первой операции и становится верным после второй операции.
Ограничение базы данных (внешнего ключа) может быть ограничением с отложенной проверкой.
- декларативную поддержку ограничений целостности;
- процедурную поддержку ограничений целостности.
Декларативная поддержка ограничений целостности заключается в определении ограничений средствами языка определения данных (DDL - Data Definition Language). Обычно средства декларативной поддержки целостности (если они имеются в СУБД) определяют ограничения на значения доменов и атрибутов, целостность сущностей и ссылочную целостность). Декларативные ограничения целостности можно использовать при создании и модификации таблиц средствами языка DDL или в виде отдельных утверждений.
Например, следующий оператор создает таблицу PERSON и определяет для нее некоторые ограничения целостности:
CREATE TABLE PERSON (Pers_Id INTEGER PRIMARY KEY, Pers_Name CHAR(30) NOT NULL, Dept_Id REFERENCES DEPART(Dept_Id) ON UPDATE CASCADE ON DELETE CASCADE);
После выполнения оператора для таблицы PERSON будут объявлены следующие ограничения целостности:
- поле Pers_Id образует потенциальный ключ отношения;
- поле Pers_Name не может содержать null-значений;
- поле Dept_Id является внешней ссылкой на родительскую таблицу DEPART, причем, при изменении или удалении строки в родительской таблице каскадно должны быть внесены соответствующие изменения в дочернюю таблицу.
Процедурная поддержка ограничений целостности заключается в использовании триггеров и хранимых процедур.
Не все ограничения целостности можно реализовать декларативно. Примером такого ограничения может служить требование из примера 1, утверждающее, что поле Dept_Kol таблицы DEPART должно содержать количество сотрудников, реально числящихся в подразделении. Для реализации этого ограничения необходимо создать триггер, запускающийся при вставке, модификации и удалении записей в таблице PERSON, который корректно изменяет значение поля Dept_Kol. Например, при вставке в таблицу PERSON новой строки, триггер увеличивает на единицу значение поля Dept_Kol, а при удалении строки - уменьшает. Заметим, что при модификации записей в таблице PERSON могут потребоваться даже более сложные действия. Действительно, модификация записи в таблице PERSON может заключаться в том, что мы переводим сотрудника из одного отдела в другой, меняя значение в поле Dept_Id. При этом необходимо в старом подразделении уменьшить количество сотрудников, а в новом - увеличить.
Кроме того, необходимо защититься от неправильной модификации строк таблицы DEPART. Действительно, пользователь может попытаться модифицировать запись об отделе, введя неверное значение поля Dept_Kol. Для предотвращения подобных действий необходимо создать также триггеры, запускающиеся при вставке и модификации записей в таблице DEPART.
По сути, наличие ограничения целостности (как декларативного, так и процедурного характера) всегда приводит к созданию или использованию некоторого программного кода, реализующего это ограничение. Разница заключается в том, где такой код хранится и как он создается.