8. Определение схемы базы данных и ограничений целостности

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

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

Различают следующие ограничения целостности:

1. Ключи.

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

3. Ограничения атрибута: значение по умолчанию, обязательность или необязательность значения, условия на значение.

4. Ограничения на уровне строки отношения.

5. Ограничение целостности на уровне отношения.

6. Так называемые утверждения (assertion) – любое булево выражение

Бывают и недекларативные (откладываемые, динамические) ограничения целостности. Они поддерживаются аппаратом триггеров.

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

Для объявления таблицы используется оператор:

CREATE TABLE <имя> (<список элементов>)

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

DROP TABLE <имя>

Элемент обычно представляет собой пару, состоящую из имени атрибута (поля) таблицы и его типа. Кроме того, атрибут или группа атрибутов могут быть объявлены уникальными или образующими первичный ключ. Для этого используются слова UNIQUE и PRIMARY KEY. Разница между ограничением уникальности и ограничением первичного ключа в следующем:

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

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

Другие ограничения, которые могут быть наложены на атрибут:

1. NOT NULL – атрибут не может иметь неопределенное значение

2. DEFAULT <значение> - если атрибуту явно не установлено значение, то он принимает <значение> по умолчанию

Можно добавить атрибуты в уже существующее отношение:

ALTER TABLE <имя> ADD <элемент>

Можно удалить атрибут из существующего отношения:

ALTER TABLE <имя> DROP <имя атрибута>

Общий вид оператора создания (объявления) отношения с ограничениями целостности:

<опр. таблицы> ::= CREATE TABLE <имя> (<описание элемента таблицы> [{,<описание элемента таблицы>}…])

<описание элемента таблицы> ::= <определение столбца> | <определение ограничений таблицы>

<определение столбца> ::= <имя> <тип> [<знач. по умолчанию>][<доп. Ограничения столбца>…]

<знач. по умолчанию> ::= DEFAULT {<literal>|USER|NULL}

<доп. Ограничения столбца> ::= NOT NULL | <ограничение уникальности столбца> | <ограничение по ссылкам столбца> |

CHECK <условие проверки на допустимость>

<ограничение уникальности столбца> ::= UNIQUE

<ограничение по ссылкам столбца> ::= FOREIGN KEY <ссылка>

<ссылка> ::= REFERENCES <имя основной таблицы> <(поля первичного ключа основной таблицы)>

PK – автоматическое создание индексов

Для вторичных индексов:

CREATE [UNIQUE] INDEX <имя индекса> ON <имя таблицы>

(<имя столбца>[<признак упорядочивания>] | [,<имя столбца > [<признак упорядочивания >]…)

<имя индекса> - уникальный идентификатор для БД

<признак упорядочивания> - {ASC | DESC}

ASC – по возрастанию, DESC – по убыванию

DROP INDEX <имя индекса>

Пример: выразим на SQL объявления таблиц из базы данных о поставках и продажах товаров (см. пример по нормализации):

Таблица Товары:

CREATE TABLE GOODS (

CODE INTEGER NOT NULL PRIMARY KEY,

NAME CHAR(50) NOT NULL,

NDS INTEGER DEFAULT 20 NOT NULL CHECK (NDS >=5 AND NDS <=30) )

Таблица Города:

CREATE TABLE CITIES (

CODE INTEGER NOT NULL PRIMARY KEY,

NAME CHAR(50) NOT NULL,

COUNTRY CHAR(50) NOT NULL)

CREATE TABLE PROVIDERS ( CODE INTEGER NOT NULL PRIMARY KEY, NAME CHAR(50) NOT NULL, CITY INTEGER NOT NULL, FOREIGN KEY (CITY) REFERENCES CITIES (CODE) )

CREATE TABLE PROVIDERS ( CODE INTEGER NOT NULL PRIMARY KEY, NAME CHAR(50) NOT NULL, CITY INTEGER NOT NULL FOREIGN KEY REFERENCES CITIES (CODE) )

Два варианта

определения

PROVIDERS

(таблица Поставщики)

Таблица Поставки:

CREATE TABLE SUPPLIES (

CODEC INTEGER NOT NULL,

CODEG INTEGER NOT NULL,

DAT DATE NOT NULL,

WEIGHT DOUBLE PRECISION NOT NULL,

PRICE DOUBLE PRECISION NOT NULL,

На следующей строке именованное ограничение:

CONSTRAINT PK_PURCH PRIMARY KEY (CODEC, CODEG, DAT),

FOREIGN KEY (CODEC) REFERENCES PROVIDERS (CODE),

FOREIGN KEY (CODEG) REFERENCES GOODS(CODE)

)

Таблица Продажи:

CREATE TABLE SELLS (

CODEG INTEGER NOT NULL,

DAT DATE NOT NULL,

WEIGHT DOUBLE PRECISION NOT NULL,

PRIMARY KEY (CODEG, DAT),

FOREIGN KEY (CODEG) REFERENCES GOODS (CODE)

)

Рассмотрим на примере таблиц SELLS и GOODS возможные нарушения ограничений ссылочной целостности.

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

2. С другой стороны, возможно нарушение при попытке удалить запись в GOODS (или изменить значения первичного ключа) с кодом товара, который уже присутствует в SELLS.

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

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

2. При изменении полей первичного ключа изменяются поля внешнего ключа (название стратегии – Cascade).

3. При изменении первичного ключа в основной таблице, поля внешнего ключа становятся неопределенны (название стратегии – Relation или SET NULL)

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

ON [UPDATE|DELETE] [SET NULL|CASCADE|RESTRICT]

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

Общая форма изменения существующих отношений:

<Изменить описание таблицы> ::= ALTER TABLE <имя>

{ADD <определение столбца>| ALTER <имя столбца> {SET DEFAULT <значение >| DROP DEFAULT} |

DROP <имя столбца> { CASCADE | RESTRICT} |

ADD {<определение PK >|< определение FK > | < условие уникальности данных >|< условие проверки >} |

ALTER TABLE GOODS

ADD EXPIRED DATE DEFAULT NULL

CHECK (EXPIRED > NOW() + 10 )

ALTER TABLE GOODS

ADD TIP CHAR(10) DEFAULT NULL

CHECK ( TIP IS NULL OR TIP = “Овощ” OR TIP = “Фрукт” )