Ограничения реляционной БД "Живопись на марках"

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

К декларативным ограничениям относят

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

ALTER TABLE Museums ADD CONSTRAINT unique_museum_name UNIQUE(name);

Однако несмотря на то, что имя художника является уникальным, нам не удастся создать аналогичное ограничение для таблицы Artists. Проблему создают неизвестные художники, для которых столбец name содержит NULL-значение. Ограничение UNIQUE допускает наличие NULL-значений, но такое значение должно быть только одно. У нас же неизвестных художников оказалось 10. Решить проблему уникальности нам поможет уникальный индекс, синтаксис которого позволяет задать предикат, которому должны удовлетворять уникальные значения. В нашем случае таким предикатом является name IS NOT NULL. Итак,

CREATE UNIQUE INDEX uix_artists_name ON Artists(name) WHERE name IS NOT NULL;  

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

В отличие от имени художника название картины уникальным не является. Картины, посвященные одним и тем же событиям или лицам, вполне могут называться одинаково, не говоря уже об автопортретах. И даже составной ключ {название картины, ИД художника} не является уникальным в нашей базе. Так, например, Перов, по крайней мере, дважды рисовал автопортрет. Для таблицы Paints естественным ключом может служить столбец stamp, содержащий путь к файлу изображения марки. Он не очень надежен, т.к. копии изображений (которые не контролируются СУБД) могут привести к дубликатам строк в таблице. Но, с другой стороны, никакая комбинация из имеющихся столбцов помимо stamp в принципе не может быть уникальной, поскольку на одну и ту же картину может быть выпущено несколько марок.

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

Проверочные ограничения определяют допустимые значения, которые могут находиться в столбце (столбцах) таблицы. Фактически ограничением домена является проверочное ограничение уровня атрибута. Например, мы могли бы ограничить художественную направленность музеев только следующими типами: Зарубежная живопись
Зарубежное искусство
Русская живопись
Русская живопись и скульптура
Русское и Зарубежное искусство
Русское искусство

написав следующее ограничение, накладываемое на столбец orientation
ALTER TABLE Museums ADD CONSTRAINT m_types CHECK(orientation IN(
'Зарубежное искусство', 'Русская живопись', 'Русская живопись и скульптура',
'Русское и Зарубежное искусство', 'Русское искусство', 'Зарубежная живопись')
);

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

ALTER TABLE Artists ADD CONSTRAINT birth_death CHECK(birth < death);

Это ограничение будет проверяться при изменении каждого из столбцов - birth и death.

Более сложным является ограничение, которое будет проверять, чтобы год рождения художника предшествовал году создания им картины. В этом ограничении задействованы две таблицы - Paints и Artists. Кроме того, в ограничении CHECK нельзя использовать подзапросы (стандарт этого не запрещает, но я не знаю СУБД, которая бы их поддерживала). Известный приём заключается в использовании скалярных функций пользователя - UDF. Давайте напишем функцию birth_creation(), которая будет возвращать 0, если не существует художника, написавшего картину до своего рождения, и 1 - в противном случае.

CREATE FUNCTION birth_creation()
RETURNS INT
AS
BEGIN
DECLARE @res INT
SELECT @res=CASE WHEN EXISTS(
SELECT artist_id, paint_id, creation, birth FROM
(SELECT id paint_id, artist_id, CAST(creation AS INT) creation FROM Paints WHERE ISNUMERIC(creation)=1) p JOIN 
(SELECT id, birth from Artists WHERE birth>0) a ON p.artist_id=id
WHERE creation <= birth) THEN 1 ELSE 0 END
RETURN @res
END;

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

Теперь создадим ограничение для таблицы Paints:

ALTER TABLE Paints ADD CONSTRAINT birth_before_paint CHECK(dbo.birth_creation() = 0);

Не получилось! Имеющиеся данные нарушают создаваемое ограничение. Проблему создают БРЕЙГЕЛЬ П. мл. и его картина "Поклонение волхвов". Пока специалист в предметной области будет разбираться с данными, создадим ограничение без проверки имеющихся данных:

ALTER TABLE Paints WITH NOCHECK ADD CONSTRAINT birth_before_paint CHECK(dbo.birth_creation() = 0);

Теперь это ограничение будет работать только при изменении данных в таблице Paints. Но если мы нарушим ограничение, изменив данные в таблице Artists, это изменение останется "невидимым" для нашего контроля! Для подобных случаев стандарт предоставляет глобальное ограничение уровня схемы ASSERTION, которое проверяется при изменении любых объектов, на которые в ограничении имеются ссылки. Но, как и в случае с подзапросами в CHECK, я не знаю реализации, которая бы поддерживала ASSERTION.

В нашем случае таблицы всего две, поэтому мы можем выйти из ситуации, создав аналогичное ограничение также и на таблице Artists:

ALTER TABLE Artists WITH NOCHECK ADD CONSTRAINT paint_after_birth CHECK(dbo.birth_creation() = 0);

Реляционная схема БД "Живопись на марках"

Примеры заданий на ограничения

Показать

Создать ограничения для базы данных "Аэропорт":

  1. Запретить продавать более одного билета на каждое место в самолете (сложность 1).
  2. Запретить пассажиру иметь более одного билета на вылет в одно и то же время (сложность 2).
  3. Запретить пассажиру находиться в воздухе одновременно в двух самолетах (сложность 3).

Графовая схема БД "Живопись на марках" (SQL Server 2017)