ГЛАВА 8.
Принципы
поддержки целостности в
реляционной модели данных
Одним из
основополагающих понятий в технологии баз данных является понятие целостности.
В общем случае это понятие прежде всего связано с тем, что база данных отражает
в информационном виде некоторый объект реального мира или совокупность взаимосвязанных
объектов реального мира. В реляционной модели объекты реального мира представлены
в виде совокупности взаимосвязанных отношений. Под целостностью будем понимать
соответствие информационной модели предметной области, хранимой в базе данных,
объектам реального мира и их взаимосвязям в каждый момент времени. Любое изменение
в предметной области, значимое для построенной модели, должно отражаться в базе
данных, и при этом должна сохраняться однозначная интерпретация информационной
модели в терминах предметной области.
Мы отметили,
что только существенные или значимые изменения предметной области должны отслеживаться
в информационной модели. Действительно, модель всегда представляет собой некоторое
упрощение реального объекта, в модели мы отражаем только то, что нам важно для
решения конкретного набора задач. Именно поэтому в информационной системе «Библиотека»
мы, например, не отразили место хранения конкретных экземпляров книг, потому
что мы не ставили задачу автоматической адресации библиотечных стеллажей. И
в этом случае любое перемещение книг с одного места на другое не будет отражено
в модели, это перемещение несущественно для наших задач. С другой стороны, процесс
взятия книги читателем или возврат любой книги в библиотеку для нас важен, и
мы должны его отслеживать в соответствии с изменениями в реальной предметной
области. И с этой точки зрения наличие у экземпляра книги указателя на его отсутствие
в библиотеке и одновременное отсутствие записи о конкретном номере читательского
билета, за которым числится этот экземпляр книга, является противоречием, такого
быть не должно. И в модели данных должны быть
предусмотрены средства и методы, которые позволят нам обеспечивать динамическое
отслеживание в базе данных согласованных действий, связанных с согласованным
изменением информации. Именно этим вопросам и посвящена данная глава.
Общие понятия и определения целостности
Поддержка
целостности в реляционной модели данных в ее классическом понимании включает
в себя 3 аспекта.
Во-первых,
это поддержка структурной целостности, которая трактуется как то, что
реляционная СУБД должна допускать работу только с однородными структурами данных
типа «реляционное отношение». При этом понятие «реляционного
отношения» должно удовлетворять всем ограничениям, накладываемым на него
в классической теории реляционной БД (отсутствие дубликатов кортежей, соответственно
обязательное наличие первичного ключа, отсутствие понятия упорядоченности кортежей).
В дополнение
к структурной целостности необходимо рассмотреть проблему неопределенных Null
значений. Как уже указывалось раньше, неопределенное значение интерпретируется
в реляционной модели как значение, неизвестное на данный момент времени. Это
значение при появлении дополнительной информации в любой момент времени может
быть заменено на некоторое конкретное значение. При сравнении неопределенных
значений не действуют стандартные правила сравнения: одно неопределенное значение
никогда не считается равным другому неопределенному значению. Для выявления
равенства значения некоторого атрибута неопределенному применяют специальные
стандартные предикаты:
<имя атрибута>IS
NULL и <имя атрибута> IS NOT NULL.
Если в данном
кортеже (в данной строке) указанный атрибут имеет неопределенное значение, то
предикат IS NULL принимает значение TRUE (Истина), а предикат IS NOT NULL —
FALSE (Ложь), в противном случае предикат IS NULL принимает значение FALSE,
а предикат IS NOT NULL принимает значение TRUE.
Ведение Null
значений вызвало необходимость модификации классической двузначной логики и
превращения ее в трехзначную. Все логические операции, производимые с неопределенными
значениями, подчиняются этой логике в соответствии с заданной таблицей истинности.
Таблица
8.1. Таблица истинности для логических операций с неопределенными
значениями ,
А |
В |
Not A |
A&B |
A V В |
||
TRUE |
TRUE |
FA |
TRUE |
TRUE |
||
TRUE |
FALSE |
FALSE |
FALSE |
TRUE |
||
А |
B |
Not A |
A&B |
A V В |
||
TRUE |
Null |
FALSE |
Null |
TRUE |
||
FALSE |
TRUE |
TRUE |
FALSE |
TRUE |
||
FALSE |
FALSE |
TRUE |
FALSE |
FALSE |
||
FALSE |
Null |
TRUE |
FALSE |
Null |
||
Null |
TRUE |
Null |
Null |
TRUE |
||
Null |
FALSE |
Null |
FALSE |
Null |
||
Null |
Null |
Null |
Null |
Null |
||
В стандарте
SQL2 появилась возможность сравнивать не только конкретные значения атрибутов
с неопределенным значением, но и результаты логических выражений сравнивать
с неопределенным значением, для этого введена специальная логическая константа
UNKNOWN. В этом случае операция сравнения выглядит как:
Логическое выражение>
IS {TRUE | FALSE | UNKNOWN}
Во-вторых,
это поддержка языковой целостности, которая состоит в том, что реляционная
СУБД должна обеспечивать языки описания и манипулирования данными не ниже стандарта
SQL. He должны быть доступны иные низкоуровневые средства манипулирования данными,
не соответствующие стандарту.
Именно поэтому
доступ к информации, хранимой в базе данных, и любые изменения этой информации
могут быть выполнены только с использованием операторов языка SQL.
В-третьих,
это поддержка ссылочной целостности (Declarative Referential Integrity,
DRI), означает обеспечение одного из заданных принципов взаимосвязи между экземплярами
кортежей взаимосвязанных отношений:
Ссылочная
целостность обеспечивает поддержку непротиворечивого состояния БД в процессе
модификации данных при выполнении операций добавления или удаления.
Кроме указанных
ограничений целостности, которые в общем виде не определяют семантику БД, вводится
понятие семантической поддержки целостности.
Структурная,
языковая и ссылочная целостность определяют правила работы СУБД с реляционными
структурами данных. Требования поддержки этих трех видов целостности говорят
о том, что каждая СУБД должна уметь это делать, а разработчики должны это учитывать
при построении баз данных с использованием реляционной модели. И эти требования
поддержки целостности достаточно абстрактны, они определяют допустимую форму
представления и обработки информации в реляционных базах данных. Но с другой
стороны, эти аспекты никак
не касаются содержания базы данных. Для определения некоторых ограничений, которые
связаны с содержанием базы данных, требуются другие методы. Именно эти методы
и сведены в поддержку семантической целостности. Давайте рассмотрим конкретный
пример. То, что мы можем построить схему базы данных или ее концептуальную модель
только из совокупности нормализованных таблиц, определяет структурную целостность.
И мы построили нашу схему библиотеки из пяти взаимосвязанных отношений. Но мы
не можем с помощью перечисленных трех методов поддержки целостности обеспечить
ряд правил, которые определены в нашей предметной области и должны в ней соблюдаться.
К таким правилам могут быть отнесены следующие:
Принципы
семантической поддержки целостности как раз и позволяют обеспечить автоматическое
выполнение тех условий, которые перечислены ранее.
Семантическая
поддержка может быть обеспечена двумя путями: декларативным и процедурным путем.
Декларативный путь связан с наличием механизмов в рамках СУБД, обеспечивающих
проверку и выполнение ряда декларативно заданных правил-ограничений, называемых
чаще всего «бизнес-правилами» (Business Rules) или декларативными
ограничениями целостности.
Выделяются
следующие виды декларативных ограничений целостности:
Задание значения по
умолчанию означает, что каждый раз при вводе новой строки в отношение, при
отсутствии данных в указанном столбце этому атрибуту присваивается именно
значение по умолчанию. Например, при вводе новых книг разумно в качестве
значения по умолчанию для года издания задать значение текущего года. Например,
для MS Access 97 это выражение будет иметь вид:
YEAR(NOW())
Здесь NOW() — функция,
возвращающая значение текущей даты, YEAR(data) — функция, возвращающая значение
года указанной в качестве параметра даты.
В качестве условия
на значение для года издания надо задать выражение, которое будет истинным
только тогда, когда год издания будет лежать в пределах от 1960 года до
текущего года. В конкретных СУБД это значение будет формироваться с использованием
специальных встроенных функций СУБД.
Для MS Access 97 это
выражение будет выглядеть следующим образом:
Between 1960 AND YEAR(NOW())
В СУБД MS SQL Server7.0
значение по умолчанию записывается в качестве «бизнес-правила».
В этом случае будет использоваться выражение, в котором явным образом должно
быть указано имя соответствующего столбца, например:
YEAR_PUBL >= 1960
AND YEAR_PUBL <- YEAR(GETDATE())
Здесь GETDATE() —
функция MS SQL Server7.0, возвращающая значение текущей даты, YEAR_PUBL
— имя столбца, соответствующего году издания.
Да, это действительно
легче, тем более что в процессе работы схема базы данных разрастается и
начинает содержать более сотни отношений, и задача нетривиальная — найти
все отношения, в которых ранее установлено это ограничение и исправить его.
Одним из основных
правил при разработке проекта базы данных, как мы уже упоминали раньше,
является минимизация избыточности, а это означает, что если возможно информацию
о чем-то, в том числе и об ограничениях, хранить в одном месте, то это надо
делать обязательно.
HOME_PHON IS NOT NULL
OR WORK_PHON IS NOT NULL
Декларативные
ограничения целостности относятся к ограничениям, которые являются немедленно
проверяемыми. Есть ограничения целостности, которые являются откладываемыми.
Эти ограничения целостности поддерживаются механизмом транзакций и триггеров.
Мы их рассмотрим в следующих главах.
Операторы DDL в языке SQL с
заданием ограничений целостности
Декларативные
ограничения целостности задаются на уровне операторов создания таблиц. В стандарте
SQL оператор создания таблиц имеет следующий синтаксис:
определение
таблицы>: :=CREATE TABLE <имя таблицы>
(<описание
элемента таблицы> [{.<описание элемента таблицы>}...])
<описание
элемента таблицы>::=<определение столбца>|
<определение
ограничений таблицы>
определение
столбца>::=<имя столбца> <тип данных>
[<значение
по умолчанию>][<дополнительные ограничения столбца>...]
<значение по умолчанию>::=DEFAULT { <litera1> | USER | NULL }
дополнительные
ограничения столбца>: :=NOT NULL
[ограничение
уникальности столбца>]|
<ограничение
по ссылкам столбца>|
CHECK (<условия
проверки на допустимость>) ограничение уникальности столбца>::= UNIQUE
<ограничение по ссылкам столбца>: :=FOREIGN KEY спецификация ссылки> спецификация ссылки>::= REFERENCES <имя основной таблицы>
(<имя первичного
ключа основной таблицы>)
Давайте кратко
прокомментируем оператор определения таблицы, синтаксис которого мы задали с
помощью традиционной формы Бэкуса—Наура.
При описании
таблицы задается имя таблицы, которое является идентификатором в базовом языке
СУБД и должно соответствовать требованиям именования объектов в данном языке.
Кроме имени
таблицы в операторе указывается список элементов таблицы, каждый из которых
служит либо для определения столбца, либо для определения ограничения целостности
определяемой таблицы. Требуется наличие хотя бы одного определения столбца.
То есть таблицу, которая не имеет ни одного столбца, определить нельзя. Количество
столбцов в одной таблице не ограничено, но в конкретных СУБД обычно бывают ограничения
на количество атрибутов. Так, например, в MS SQL Server 6.5 максимальное количество
столбцов в таблице было 250, но уже в MS SQL Server 7.0 оно увеличено до 1024.
Оператор
CREATE TABLE определяет так называемую базовую таблицу, то есть реальное хранилище
данных.
Как видно,
кроме обязательной части, в которой задается имя столбца и его тип данных, определение
столбца может содержать два необязательных раздела: значение столбца по умолчанию
и раздел дополнительных ограничений целостности столбца.
В разделе
значения по умолчанию указывается значение, которое должно быть помещено в строку,
заносимую в данную таблицу, если значение данного столбца явно не указано. В
соответствии со стандартом языка SQL значение по умолчанию может быть указано
в виде литеральной константы с типом, соответствующим типу столбца; путем задания
ключевого слова USER, которому при выполнении оператора занесения строки соответствует
символьная строка, содержащая имя текущего пользователя (в этом случае столбец
должен иметь тип символьных строк); или путем задания ключевого слова NULL,
означающего, что значением по умолчанию является неопределенное значение. Если
значение столбца по умолчанию не специфицировано и в разделе ограничений целостности
столбца указано NOT NULL (то есть наличие неопределенных значений запрещено),
то попытка занести в таблицу строку с незаданным значением данного столбца приведет
к ошибке.
Задание в
разделе ограничений целостности столбца выражения NOT NULL приводит к неявному
порождению проверочного ограничения целостности для всей таблицы "CHECK
(С IS NOT NULL)" (где С — имя данного столбца). Если ограничение NOT NULL
не указано и раздел умолчаний отсутствует, то неявно порождается раздел умолчаний
DEFAULT NULL. Если указана спецификация уникальности, то порождается соответствующая
спецификация уникальности для таблицы.
При задании
ограничений уникальности данный столбец определяется как возможный ключ, что
предполагает уникальность каждого вводимого значения в данный столбец. И если
это ограничение задано, то СУБД будет автоматически
осуществлять проверку на отсутствие дубликатов значений данного столбца во всей
таблице.
Если в. разделе
ограничений целостности указано ограничение по ссылкам данного столбца, то порождается
соответствующее определение ограничения по ссылкам для таблицы: FOREIGN КЕУ(<имя
столбца>) <спецификация ссылки>, что означает, что значения данного
столбца должны быть взяты из соответствующего столбца родительской таблицы.
Родительской таблицей в данном случае называется таблица, которая связана с
данной таблицей связью «один-ко-многим» (1:М). При этом каждая строка
родительской таблицы может быть связана с несколькими строками определяемой
таблицы. Трансляция операторов SQL проводится в режиме интерпретации, поэтому
важно, чтобы сначала была бы описана родительская таблица, а потом уже все подчиненные
(дочерние) таблицы, связанные с ней. Иначе транслятор определит ссылку на неопределенный
объект.
Наконец,
если указано проверочное ограничение столбца, то условие поиска этого ограничения
должно ссылаться только на данный столбец, и неявно порождается соответствующее
проверочное ограничение для всей таблицы. В проверочных ограничениях, накладываемых
на столбец, нельзя задавать сравнение со значениями других столбцов данной таблицы.
В главе 5
определены типы данных, которые допустимы по стандартам SQL. Попробуем написать
простейший оператор создания таблицы BOOKS из базы данных «Библиотека».
При этом
будем предполагать наличие следующих ограничений целостности:
CREATE TABLE
BOOKS
(
ISBN varchar(14)
NOT NULL PRIMARY KEY,
TITLE varchar(120)
NOT NULL.
AUTOR varchar
(30) NULL.
COAUTOR varchar(30)
NULL,
YEAR_PUBLsmallint DEFAULT Year(GetDate())
CHECK(YEAR_PUBL
>= 1960 AND YEAR PUBL <= YEAR(GetDate())),
PUBLICH varchar(20)
NULL.
PAGES smalllnt CHECK(PAGES > = 5 AND PAGES <= 1000)
);
Почему мы
не задали обязательность значения для количества страниц в книге? Потому что
это является следствием проверочного ограничения, заданного на количество страниц,
количество страниц всегда должно лежать в пределах от 5 до 1000, значит, оно
не может быть незаданным и система это контролирует автоматически.
Теперь зададим
описание таблицы «Читатели», которой соответствует отношение READERS:
CREATE TABLE
READERS
(
READER_ID Smallint(4)
PRIMARY KEY.
FIRST_NAME char(30)
NOT NULL.
LAST_NAME char(30)
NOT NULL.
ADRES char(50).
HOME_PHON char(12).
WORK_PHON chart
12),
BIRTH_DAYdate
CHECK(DateDiff(year. GetDate() ,BIRTH_DAY) >=17)
);
Здесь DateDiff
(часть даты, начальная дата, конечная дата) — функция MS SQL Server 7.0, которая
определяет разность между начальной и конечной датами, заданную в единицах,
определенных первым параметром — часть даты. Мы задали в качестве параметра
Year, что значит, что мы разность определяем в годах.
Теперь зададим
операцию создания таблицы EXEMPLAR (экземпляры книги). В этой таблице первичным
ключом является атрибут, задающий инвентарный номер экземпляра книги. В такой
постановке мы полагаем, что при поступлении книг в библиотеку им просто присваиваются
соответствующие порядковые номера. Для того чтобы не утруждать библиотекаря
все время помнить, какой номер был последним, мы можем воспользоваться тем,
что некоторые СУБД допускают специальный инкрементный тип данных, то есть такой,
значения которого автоматически увеличиваются или уменьшаются на заданную величину
при каждом новом вводе данных. В СУБД MS Access такой тип данных называется
«счетчик»
(counter) и он всегда имеет начальное значение 1 и шаг, равный тоже 1, то есть
при вводе каждого нового значения счетчик увеличивается на 1, значит, практически
считает вновь введенные значения. В СУБД MS SQL Server 7.0 это свойство -IDENTITY,
которое может быть присвоено ряду целочисленных типов данных. В отличие от «счетчика»
свойство IDENTITY позволяет считать с любым шагом, положительным или отрицательным,
но обязательно целым. Если мы не задаем дополнительных параметров этому свойству,
то оно начинает работать как счетчик в MS Access, начиная с единицы и добавляя
при каждом вводе тоже единицу.
Кроме того,
таблица EXEMPLAR является подчиненной двум другим ранее определенным таблицам:
BOOKS и READERS. При этом с таблицей BOOKS таблица EXEMPLAR связана обязательной
связью, потому что не может быть ни одного экземпляра книги, который бы не был
приписан конкретной книге. С таблицей READERS таблица EXEMPLAR связана необязательной
связью, потому что не каждый экземпляр в данный момент находится на руках у
читателя. Для моделирования этих связей при создании таблицы EXEMPLAR должны
быть определены два внешних ключа (FOREIGN KEY). При этом атрибут, соответствующий
шифру книги (мы его назовем так же, как и в родительской таблице — ISBN), является
обязательным, то есть не может принимать неопределенных значений, а атрибут,
который является внешним ключом для связи с таблице READERS, является необязательным
и может принимать неопределенные значения.
Необязательными
там являются два остальных атрибута: дата взятия и дата возврата книги, оба
они имеют тип данных, соответствующей календарной дате. Атрибут, который содержит
информацию о присутствии или отсутствии книги, имеет логический тип. Напишем
оператор создания таблицы EXEMPLAR в синтаксисе MS SQL Server 7.0:
CREATE TABLE EXEMPLAR
(
EXEMPLAR_ID
| NT IDENTITY PRIMARY KEY,
ISBN varchar(14)
NOT NULL FOREIGN KEY references BOOKS(ISBN),
READERJD Smallint(4)
NULL FOREIGN KEY references READERS (READERJD).
DATA_IN date.
DATA_OUT date,
EXIST Logical,
);
Как мы уже
знаем, не все декларативные ограничения могут быть заданы на уровне столбцов
таблицы, часть ограничений может быть задана только на уровне всей таблицы.
Например, если мы имеем в качестве первичного ключа не один атрибут, а последовательность
атрибутов, то мы не можем определить ограничение типа PRIMARY KEY (первичный
ключ) только на уровне всей таблицы.
Допустим,
что мы считаем экземпляры книги не подряд, а отдельно для каждого издания, тогда
таблица EXEMPLAR в качестве первичного ключа будет иметь набор из двух атрибутов:
это шифр книги (ISBN) и порядковый номер экземпляра
данной книги
(ID_EXEMPL), в этом случае оператор создания таблицы EXEMPLAR будет выглядеть
следующим образом:
CREATE TABLE EXEMPLAR
(
ID_EXEMPLAR
int NOT NULL.
ISBN varchar(14)
NOT NULL FOREIGN KEY references BOOKS(ISBN),
READERJD Smallint(4)
NULL FOREIGN KEY references READERS (READERJD),
DATA_IN date.
DATA_OUT date,
EXIST Logical.
PRIMARY KEY (ID_EXEMPLAR. ISBN)
);
Мы видим,
что один и тот же атрибут ISBN, с одной стороны, является внешним ключом (FORIGN
KEY), а с другой стороны, является частью первичного ключа (PRIMARY KEY). И
ограничение типа первичный ключ (PRIMARY KEY) задается не на уровне одного атрибута,
а на уровне всей таблицы, потому что оно содержит набор атрибутов.
То же самое
можно сказать и о проверочных (CHECK) ограничениях, если условия проверки предполагают
сравнения значений нескольких столбцов таблицы. Введем дополнительное ограничение
для таблицы BOOKS, которое может быть сформулировано следующим образом: соавтор
не может быть задан, если не задан автор. При описании книги допустимо не задавать
ни автора, ни соавтора, или задать и автора и соавтора, или задать только автора.
Однако задание соавтора в отсутствие задания автора считается ошибочным. В этом
случае оператор создания таблицы BOOKS будет выглядеть следующим образом:
CREATE TABLE BOOKS
(
ISBN varchar(14)
NOT NULL PRIMARY KEY.
TITLE varchar(120)
NOT NULL,
AUTOR varchar
(30) NULL.
COAUTOR varchar(30)
NULL.
YEARJHJBL small int DEFAULT Year(GetDate())
CHECK(YEARJ>UBL
> 1960 AND YEAR_PUBL <= YEAR(GetDate())).
PUBLICH varchar(20)
NULL.
PAGES smallint
CHECK( PAGES >= 5 AND PAGES <= 1000).
CHECK (NOT (AUTOR IS NULL AND COAUTOR IS NOT NULL))
);
Для анализа
ошибок целесообразно именовать все ограничения, особенно если таблица содержит
несколько ограничений одного типа. Для именования ограничений используется ключевое
слово CONSTRAINT, после которого следует уникаль-
ное имя ограничения,
затем тип ограничения и его выражения. Для идентификации ограничений рекомендуют
использовать систему именования, которая легко позволит определить при получении
сообщения об ошибке, которое вырабатывает СУБД, какое ограничение нарушено.
Обычно имя ограничения состоит из краткого названия типа ограничения, далее
через символ подчеркивания идет имя атрибута или таблицы, в зависимости от того,
к какому уровню относится ограничение, и, наконец, порядковый номер ограничения
данного типа, если к одному объекту задается несколько ограничений одного типа.
Сокращенные
обозначения ограничений состоят из одной или двух букв и могут быть следующими:
Приведем
пример оператора создания таблицы BOOKS с именованными ограничениями:
CREATE TABLE
BOOKS
(
ISBN varchar(14)
NOT NULL.
TITLE varchar(120)
NOT NULL;
AUTOR varchar
(30) NULL.
COAUTOR varchar(30)
NULL.
YEAR_PUBL smallint
NOT NULL.
PUBLICH varchar(20)
NULL,
PAGES smallint
NOT NULL.
CONSTRAINT PK_BOOKS
PRIMARY KEY (ISBN).
CONSTRAINT OF_
YEAR_PUBL DEFAULT (Year(GetDate()).
CONSTRAINT CK_
YEAR_PUBL CHECK (YEAR_PUBL >- 1960 AND
YEAR_PUBL <=
YEAR(GetDate())).
CONSTRANT CK_PAGES CHECK (PAGES > = 5 AND PAGES <= 1000). CONSTRAINT CK_BOOKS CHECK (NOT (AUTOR IS NULL AND COAUTOR IS NOT NULL))
);
CREATE TABLE READERS
(
READER_ID |
Small int |
PRIMARY KEY |
||
FIRST_NAME |
char(30) |
NOT NULL. |
||
LAST_NAME |
char(30) |
NOT NULL. |
||
ADRES |
char(50). |
|
||
HOME_PHON char(12).
WORK_PHON char(12).
BIRTH_DAY date CHECK( DateDiff(year, GetDate().BIRTH_DAY) >=17 ),
CONSTRAINT CK_READERS
CHECK (HOME_PHON IS NOT NULL OR WORK_PHON IS NOT NULL) );
CREATE TABLE CATALOG
(
ID_CATALOG Smallint
PRIMARY KEY,
KNOWELEDGE_AREA varchar(150)
);
CREATE TABLE EXEMPLAR
(
ID_EXEMPLAR
Int NOT NULL,
ISBN varchar(14)
NOT NULL FOREIGN KEY references BOOKS(ISBN),
READER_ID Smallint(4)
NULL FOREIGN KEY references REABERS (READER_ID).
DATA_IN date.
DATA_OUT date.
EXIST Logical.
PRIMARY KEY (ID_EXEMPLAR, ISBN)
);
CREATE TABLE RELATION_1
(
ISBN varchar(14) NOT NULL
FOREIGN KEY references BOOKS(ISBN).
ID_CATALOG smallint NOT NULL
FOREIGN KEY references CATALOG(ID_CATALOG).
CONSTRAINT PK_RELATION_1
PRIMARY KEY (ISBN.ID_CATALOG)
).
Операторы
языка SQL, как указывалось ранее, транслируются в режиме интерпретации, в отличие
от большинства алгоритмических языков, трансляторы для которых выполнены по
принципу компиляции. В режиме интерпретации каждый оператор отдельно транслируется,
то есть переводится в машинные коды, и тут же выполняется. В режиме компиляции
вся программа, то есть совокупность операторов, сначала переводится в машинные
коды, а затем может быть выполнена как единое целое. Такая особенность SQL накладывает
ограничение на порядок описания создаваемых таблиц. Действительно, если при
трансляции оператора описания подчиненной таблицы с указанным внешним ключом
и соответствующей ссылкой на родительскую таблицу эта родительская таблица не
будет обнаружена, то мы получим сообщение об ошибке с указанием ссылки на несуществующий
объект. Сначала должны быть описаны все основные таблицы, а потом подчиненные
таблицы.
В нашем примере
с библиотекой порядок описания таблиц следующий:
Набор операторов
языка SQL принято называть не программой, а скриптом. Тогда скрипт, который
добавит набор из 5 взаимосвязанных таблиц базы данных «Библиотека»
в существующую базу данных, будет выглядеть следующим образом:
CREATE TABLE BOOKS
(
ISBN varchar(14)
NOT NULL .
TITLE varchar(120)
NOT NULL.
AUTOR varchar
(30) NULL.
COAUTOR varchar(30)
NULL.
YEAR_PUBL smallint
NOT NULL.
PUBLICH varchar(20)
NULL.
PAGES smallInt NOT NULL.
CONSTRAINT PK_BOOKS PRIMARY KEY (ISBN).
CONSTRAINT DF_ YEAR_PUBL DEFAULT (Year(GetDate()).
CONSTRAINT CK_
YEAR_PUBL CHECK (YEAR_PUBL >= 1960 AND
YEAR_PUBL <=
YEAR(GetDate())).
CONSTRANT CK_PAGES CHECK (PAGES > = 5 AND PAGES <= 1000).
CONSTRAINT CK_BOOKS CHECK (NOT (AUTOR IS NULL AND COAUTOR IS NOT NULL)) CREATE TABLE READERS
(
READER_ID Small
int PRIMARY KEY.
FIRST_NAME char(30)
NOT NULL.
LAST_NAME char(30)
NOT NULL.
ADRES char(50),
HOME_PHON char(12).
WORK_PHON char(12).
BIRTH_DAYdate СНЕCK DateDiff(year. GetDate().BIRTH_DAY) >=17 )
CONSTRAINT CK_READERS CHECK (HOME_PHON IS NOT NULL OR WORK_PHON IS NOT NULL)
);
CREATE TABLE CATALOG
(
ID_CATALOG Smallint
PRIMARY KEY,
KNOWELEDGE_AREA varchar(l50)
);
CREATE TABLE EXEMPLAR
(
ID_EXEMPLAR
int NOT NULL,
ISBN varchar(14) NOT NULL
FOREIGN KEY references
BOOKS(ISBN).
READER_ID Smallint(4) NULL
FOREIGN KEY
references READERS (READER_ID).
DATA_IN date,
DATA_OUT date.
EXIST Logical,
PRIMARY KEY (ID_EXEMPLAR. ISBN)
);
CREATE TABLE RELATION_1
(
ISBN varchar(14) NOT NULL
FOREIGN KEY references BOOKS(ISBN).
ID_CATALOG small int NOT NULL
FOREIGN KEY references CATALOG (ID_CATALOG),
CONSTRAINT PK_RELATION_1 PRIMARY KEY (ISBN.ID_CATALOG)
);
При написании
скрипта мы добавили в оператор создания таблицы «Читатели» ограничение
на уровне таблицы, которое связано с обязательным наличием хотя бы одного из
двух телефонов.
Средства определения схемы базы данных
В стандарте
SQL1 задается спецификация оператора описания схемы базы данных, но не указывается
способ создания собственно базы данных, поэтому в различных СУБД используются
неодинаковые подходы к этому вопросу.
Например,
в СУБД ORACLE база данных создается в ходе установки программного обеспечения
собственно СУБД. Все таблицы пользователей помещаются в единую базу данных.
Однако они могут быть разделены на группы, объединенные в подсхемы. Понятие
подсхемы не стандартизировано в SQL и не используется в других СУБД.
В состав
СУБД INGRES входит специальная системная утилита, имеющая имя CREATEDB, которая
позволяет создавать новые базы данных. Права на использование этой утилиты имеет
администратор сервера. Для удаления базы данных существует соответствующая утилита
DESTROYDB.
В СУБД MS
SQL Server существует специальный оператор CREATE DATABASE, который является
частью языка определения данных, для удаления базы данных в языке определен
оператор DROP DATABASE. Правами на создание баз данных наделяются администраторы
баз данных, которых в общем случае может быть несколько. .Правами более высокого
уровня обладает администратор сервера баз данных (SQL Server), который и может
предоставить права администратора базы данных другим пользователям сервера.
Администраторы баз данных могут удалить только свою базу данных. Приведем пример
оператора создания схемы базы данных в MS SQL Server 7.0:
CREATE DATABASE
databasename
[ON [PRIMARY][<спецификация
файла>[... .n]][.«группа файлов> [,...n]]]
[ LOG ON { спецификация
файла> [,...n]} ][ FOR LOAD | FOR ATTACH ] спецификация файла> : : = (
[ NAME = логическое имя файла,]FILENAME = 'физическое имя файла'
[. SIZE = размер][,
MAXSIZE - { максимальный размер | UNLIMITED } ]
[. FILEGROWTH
= инкремент увеличения файла] ) [,...п] <группа файлов>::= FILEGROUP имя
группы файлов спецификация файла> [,...п]
Здесь
Почти все
параметры, кроме имени базы данных, являются необязательными, поэтому оператор
создания простой базы данных «Библиотека» может выглядеть следующим
образом:
CREATE DATABASE
Library
Для изменения
схемы базы данных в MS SQL Server 7.0 может быть использована команда:
ALTER DATABASE
database
{ ADD FILE спецификация
файлов> [,...n] [TO FILEGROUP filegroup_name]
| ADD LOG FILE
спецификация файлов> [,...n]
| REMOVE FILE
имя_файла
| ADD FILEGROUP
имя_группы файлов
|REMOVE FILEGROUP
имя группы_файлов
|MODIFY FILE
<спецификация файлов>
(MODIFY FILEGROUP
имя_группы_файлов имя_свойства_группы файлов}
Здесь свойства
группы файлов определяет одно из допустимых ключевых слов:
Как видно,
при изменении схемы базы данных в нее могут быть добавлены (ADD) дополнительные
файлы и файловые группы или удалены (REMOVE ) ранее определенные файлы или файловые
группы. Назначение этих файлов нам будет более понятно после того, как мы познакомимся
с физическими моделями и файловыми структурами, используемыми для хранения данных
в базах данных.
Сейчас мы
познакомимся с последней командой, которая предназначена для удаления базы данных.
В MS SQL Server 7.0 это команда имеет следующий синтаксис:
DROP DATABASE
databasename
После выполнения
этой команды уничтожается вся база данных вместе с содержащимися в ней данными.
Средства изменения описания таблиц и средства удаления таблиц
В язык SQL
добавлены средства изменения схемы таблиц. Что можно и что нельзя изменять в
описании таблицы? В стандарте SQL2 добавлены достаточно широкие возможности
по модификации уже существующих схем таблиц. Для модификации таблиц используется
оператор ALTER TABLE, который позволяет выполнить следующие операции изменения
для схемы таблицы:
Синтаксис
оператора ALTER TABLE:
<Изменить
описание таблицы>::= ALTER TABLE <имя таблицы> { ADD определение столбца>
|
ALTER <имя
столбца> (SET DEFAULT <значение> DROP DEFAULT } |
DROP <имя столбца>{CASCADE | RESTRICT} |
ADD { <определение первичного ключа>| определение внешнего ключа> |
<условие уникальности данных> |
<условие проверки> } |
DROP CONSTRAINT
имя условия { CASCADE | RESTRICT} }
Одним оператором
ALTER TABLE можно провести только одно из перечисленных изменений, например,
за один раз можно добавить один столбец. Если вам требуется добавить два столбца,
то необходимо применить два оператора.
Давайте рассмотрим
несколько примеров. Чаще всего применяется операция добавления столбца. Предложение
определения нового столбца в операторе ALTER TABLE имеет точно такой же синтаксис,
как и в операторе создания таблицы. Добавим столбец EDUCATION (образовние),
содержащий символьный тип данных, с заданным перечнем значений («начальное»,
«среднее», «неоконченное высшее», «высшее»).
ALTER TABLE
READERS
ADD EDUCATION
varchar (30) DEFAULT NULL
CHECK (EDUCATION
IS NULL OR
EDUCATION= "начальное"
OR
EDUCATION= "среднее
" OR EDUCATION= "неоконченное высшее" OR
EDUCATION= "высшее"
)
В таблицу
READERS будет добавлен столбец EDUCATION, в который по умолчанию будут добавлены
все кортежи неопределенного значения. В дальнейшем эти значения могут быть заменены
на одно из допустимых символьных значений.
Добавим ограничение
на соответствие между датами взятия и возврата книги в таблице EXEMPLAR. Действительно,
если даты введены, то требуется, чтобы дата возврата книги была бы больше на
срок выдачи книги. Считаем, что стандартным сроком являются 2 недели. Теперь
сформулируем оператор изменения таблицы EXEMPLARE:
ALTER TABLE
EXEMPLARE
ADD CONSTRAINT CK_ EXEMPLARE
CHECK ((DATA_IN
IS NULL AND DATA_OUT IS NULL) OR
(DATA_OUT >=
DATAJN +14) )
Здесь мы
применили операцию сложения к календарной дате, которая предполагает, что добавляют
заданное число дней.
Операция
удаления столбца связана с проверкой ссылочной целостности, и поэтому не разрешается
удалять столбцы, связанные с поддержкой ссылочной целостности
таблицы, то есть нельзя удалить столбцы родительской таблицы, входящие в первичный
ключ таблицы, если на них есть ссылки в подчиненных таблицах.
При изменении
первичного ключа таблицы следует быть внимательными. Во-первых, у исходной таблицы
могут быть подчиненные, при этом первичный ключ исходной таблицы является внешним
ключом для подчиненных таблиц, и просто его удалить невозможно, СУБД контролирует
ссылочную целостность и не позволит выполнить операцию удаления первичного ключа
таблицы, если на него имеются ссылки. Следовательно, в этом случае порядок изменения
первичного ключа должен быть таким, как на рис. 8.1:
Рис.
8.1. Алгоритм изменения первичного ключа таблицы
Чаще всего
операция ALTER TABLE применяется в CASE-системах при автоматической генерации
скриптов создания таблиц в базе данных. В этих системах универсальный алгоритм
предполагает сначала создание всех таблиц, которые заданы в даталогической модели,
и только после этого добавляются соответствующие связи. И это понятно — в отличие
от человеческого разума искусственный интеллект CASE-системы будет испытывать
затруднения в определении иерархических взаимосвязей таблиц базы данных, поэтому
он предпочитает использовать универсальный алгоритм, в котором сначала все объекты
определяются, а затем добавляются соответствующие свойства для атрибутов, которые
являются внешними ключами с указанием требуемых ссылок. В этом случае все операции
назначения внешних ключей будут считаться корректными, потому что все объекты
были описаны заранее, и для такого алгоритма порядок создания таблиц безразличен.
Далее приведен скрипт, который был получен при разработке схемы базы данных
«Библиотека» в PowerDesignerG.l. По умолчанию для каждой таблицы
создается индекс по первичному ключу, так что кроме знакомых операций создания
и изменения таблиц мы увидим еще и операцию создания индексов (CREATE INDEX),
после изучения физических моделей в базах данных мы еще вернемся к этой операции,
а пока примем ее на веру. При создании даталогичекой модели в качестве СУБД
был выбран сервер MS SQL Server 6.X, и для этого сервера скрипт был сгенерирован
на встроенном языке этой СУБД, называмом TransactSQL. В нем операция USE <имя
базы дан-ных> соответствует операции открытия базы данных, а команда до означает
переход к выполнению следующей команды.
/* ================================
*/
/* Database name: LIBRARY ' */ /* DBMS name: Microsoft SQL Server 6.x */
/* Created on:
06.10.00 18:56 */
/* ================================
*/
/* Database
name: LIBRARY */
/* ================================
*/
use LIBRARY
go
/* ================================
*/
/* Table: BOOKS
*/
/* ================================
*/
create table BOOKS
(
ISBN |
varchar(14) |
not null . |
||
TITLE |
varchar(255) |
not null . |
||
AUTOR |
varchar(30) |
null. |
||
COAUTOR |
varchar(30) |
null. |
||
PUBLICHER |
varchar(30) |
null. |
||
WHERE_PUBLICH
varchar(30) null,
YEAR_IZD smallint
not null
constraint CKC_YEAR_IZD_BOOKS check
(
YEAR_PUBL >-
1969 AND YEAR_PUBL <= YEAR(GetDate())).
PAGES smallint
not null
constraint CKC_PAGES_BOOKS check
(
PAGES between
5 and 1000).
constraint PK_BOOKS
primary key (ISBN).
constraint CKT_BOOKS check
(
(AUTOR IS NOT
NULL OR (AUTOR IS NULL AND COAUTOR IS NULL))) ) go
/* Table: READERS
*/
create table READERS
(
NUM_READER intnot
null.
NAME varchar(30)
not null.
BIRTH_DAY datetime
not null
constraint CKC_BIRTH_DAY_READERS check
(
(DateDiffCyear. GetDate().BIRTH_DAY) >=17 )
),
SEX chard) not
null
constraint CKC_SEX_READERS check
(
SEX in СМ'.'1'.'м'.'ж')).
HOME_PHON char(9)
null.
WORK_PHON char(9)
null,
constraint PK_READERS
primary key (NUM_READER).
constraint CKT_READERS check
(
(HOME_PHON IS NOT NULL OR WORK_PHON IS NOT NULL))
)
go
/* Table: CATALOG
*/
/* ================================
*/
create table CATALOG
( KW KOD smallint
not null.
NAME_KW varchar(255) null, constraint PK_CATALOG primary key (KW_KOD)
)
go
/* ================================
*/
/* Table:
EXEMPLAR */
create table
EXEMPLAR
(
INVJUMER |
int not |
null. |
||
ISBN |
varchar(14) |
not null . |
||
NUM_READER |
int |
null. |
||
PRESENT |
bit not |
null. |
||
DATE_IN |
datetime |
null. |
||
DATE OUT |
datetime |
null. |
||
constraint PK_EXEMPLAR
primary key (INVJUMER) )
go
/* ================================ */
/* Index: RELATION_43_FK ' . */ /*
/* ================================
*/
create index
RELATION_43_FK on EXEMPLAR (ISBN) .
go
/* Index: RELATION_44_FK
*/
/* ================================
*/
create index
RELATION_44_FK on EXEMPLAR (NUM_READER) go
/* ================================
*/
/* Table: RELATION_67 */
/* ================================
*/
create table
RELATION_67
(
ISBN varchar(14) not null, KW_KOD smallint not null, constraint
PK_RELATION_67
primary key (ISBN, KW_KOD)
)
go
/* ================================
*/
/* Index: IOIINEONY_E_IAEANOE_CIAIEE_FK
*/
/* ================================
*/
create index
IOIINEONY_E_IAEANOE_CIAIEE_FK on RELATION_67 (ISBN) go
/* ================================
*/
/* Index: I_AANOAAEAIA_A_EIEAAO_FK
*/
create index
I_AANOAAEAIA_A_EIEAAO_FK on RELATION_67 (KW_KOD)
go
alter table
EXEMPLAR
add constraint
FK_EXEMPLAR_RELATION_BOOKS foreign key (ISBN)
references BOOKS
(ISBN)
go
alter table
EXEMPLAR
add constraint
FK_EXEMPLAR_RELATION_READERS foreign key (NUM_READER)
references READERS
(NUM_READER) go alter table RELATION_67
add constraint
FK_RELATION_IOIINEONY_BOOKS foreign key (ISBN)
references BOOKS
(ISBN) go alter table RELATION_67
add constraint
FK_RELATION_I_AANOAAE_CATALOG foreign key (KW_KOD)
references CATALOG
(KW_KOD) go
В языке SQL
присутствует и операция удаления таблиц. Синтаксис этой операции предельно прост:
<Удалить
таблицу>::= DROP TABLE <имя таблицы> [CASCADE | RESTRICT]
Параметр
CASCADE означает, что при удалении таблицы одновременно удаляются и все объекты,
связанные с ней. С таблицей, кроме рассмотренных ранее ограничений, могут быть
связаны также объекты типа триггеров и представления. Понятие представления
будет рассмотрено в следующем подразделе, а триггеров мы коснемся в разделах,
связанных с архитектурой клиент-сервер. Однако операция удаления объектов определяется
еще правами пользователей, что связано с концепцией безопасности в базах данных.
Это значит, что если вы не являетесь владельцем объекта, то вы можете не иметь
прав на его удаление. И в этом случае синтаксически правильный оператор DROP
TABLE не может быть выполнен системой в силу отсутствия прав на удаление связанных
с удаляемой таблицей
объектов. Кроме того, операция удаления таблицы не должна нарушать целостность
базы данных, поэтому удалять таблицу, на которую имеются ссылки других таблиц,
невозможно.
Например,
в нашей схеме, связанной с библиотекой, мы не можем удалить ни таблицу BOOKS,
ни таблицу READERS, ни таблицу CATALOG. У этих таблиц есть связь с подчиненными
таблицами EXEMPLAR и RELATION_67. Поэтому если вы хотите удалить некоторый набор
таблиц, то сначала необходимо грамотно построить последовательность их удаления,
которая не нарушит базовых принципов поддержки целостности вашей схемы БД. В
нашем примере последовательность операторов удаления таблиц может быть следующей:
DROP TABLE EXEMPLAR
DROP TABLE RELATION_67
DROP TABLE CATALOG
DROP TABLE READERS
DROP TABLE BOOKS
Понятие представления операции создания представлений
Для описания
внешних моделей в реляционной модели могут использоваться представления. Представление
(View) — это SQL-запрос на выборку, который пользователь воспринимает как
некоторое виртуальное отношение. Задание представлений входит в описание схемы
БД в реляционных СУБД. Представления позволяют скрыть ненужные несущественные
детали для разных пользователей, модифицировать реальные структуры данных в
удобном для приложений виде и, наконец, разграничить права доступа к данным
и тем самым повысить защиту данных от несанкционированного доступа.
В отличие
от реальной таблицы представление в том виде, как оно сконструировано, не существует
в базе данных, это действительно только виртуальное отношение, хотя все данные,
которые представлены в нем, действительно существуют в базе данных, но в разных
отношениях. Они скомпонованы для пользователя в удобном виде из реальных таблиц
с помощью некоторого запроса. Однако пользователь может этого не знать, он может
обращаться с этим представлением как со стандартной таблицей. Представление
при создании получает некоторое уникальное имя, его описание хранится в описании
схемы базы данных, и СУБД в любой момент времени при обращении к этому представлению
выполняет запрос, соответствующий его описанию, поэтому пользователь, работая
с представлением, в каждый момент времени видит действительно реальные, актуальные
на настоящий момент данные. Оно формируется как бы на лету, в момент обращения.
Оператор
определения представления имеет следующий вид:
<создание
представлениям := CREATE VIEW <имя представления> [ (<список столбцов>)]
AS <SQL-3anpoc>
При необходимости
в представлении можно задать новое имя для каждого столбца виртуальной таблицы.
При этом надо помнить, что если указывается список столбцов, то он должен содержать
ровно столько столбцов, сколько содержит их SQL-запрос.
Если список
имен столбцов в представлении не задан, то каждый столбец представления получает
имя соответствующего столбца запроса.
Рассмотрим
типичные виды представлений и их назначение.
Этот вид
представления широко применяется для уменьшения объема реальных таблиц в обработке
и ограничения доступа пользователей к закрытой для них информации. Так, например,
правилом хорошего тона считается, что руководитель подразделения в некоторой
фирме может видеть оклады и результаты работы только своих сотрудников, в этом
случае для него создается горизонтальное представление, в которое загружены
строки общей таблицы сотрудников, работающих в его подразделении.
Например,
у нас есть таблица «Сотрудник» (EMPLOYEE) с полями «Табельный
номер» (T_NUM), «ФИО» (NAME), «должность»(POSITION),
«оклад»(SALARY), «надбав-Ka»(PREMIUM), «отдел»
(DEPARTMENT).
Для приложения,
с которым работает начальник отдела продаж, будет создано представление
CREATE VIEW
SAL_DEPT AS
SELECT * FROM
EMPLOYEE WHERE DEPARTMENT= "Отдел продаж"
Этот вид
представления практически соответствует выполнению операции проектирования некоторого
отношения на ряд столбцов. Он используется в основном для скрытия информации,
которая не должна быть доступна в конкретной внешней модели.
Например,
для работника табельной службы, который учитывает присутствие сотрудников на
работе, информация об окладе и надбавке должна быть закрыта. Для него можно
создать следующее вертикальное представление:
CREATE VIEW
TABEL AS
SELECT T_NUM.NAME.
POSITION. DEPARTMENT FROM EMPLOYEE
Эти представления
содержат запросы, которые имеют группировку. Сгруппированные представления всегда
должны содержать список столбцов. Они могут использовать агрегированные функции
в качестве результирующих столбцов, а в дальнейшем это представление может использоваться
как виртуальная таблица, например, в других запросах.
Создадим
представление, которое определяет суммарный фон заработной платы и надбавок
по каждому подразделению с указанием количества сотрудников, минимальной, максимальной
и средней зарплаты и надбавки по подразделению. Такой запрос позволяет сравнить
заработную плату и надбавки прямо по всем подразделениям, и он может быть очень
эффективно использован администрацией при проведении сравнительного анализа
подразделений фирмы.
CREATE VIEW
RATE
DEPARTMENT.
COUNT(*). SUM(SALARY). SUM(PREMIUM). MAX(SALARY). MIN(SALARY).
AVERAGE (SALARY).
MAX(PREMIUM). MIN(PREMIUM), AVERAGE (PREMIUM) AS SELECT DEPARTMENT, COUNT(*).
SUM(SALARY). SUM(PREMIUM). MAX(SALARY).
MIN(SALARY).
AVERAGE (SALARY). MAX(PREMIUM). MIN(PREMIUM).
AVERAGE (PREMIUM)
FROM EMPLOYEE GROUP BY DEPARTMENT
Часто представления
базируются на многотабличных запросах. Такое использование позволяет упростить
разработку пользовательского интерфейса, сохранив при этом корректность схемы
базы данных. Для примера снова обратимся к базе данных «Библиотека»
и создадим представление, которое содержит список читателей-должников с указанием
книг/ которые у них на руках, и указанных в базе сроков сдачи этих книг. Такое
представление может понадобиться для административного приложения, которое разрабатывается
для директора библиотеки или его заместителя, они должны принимать административные
меры для наказания нарушителей и возврата книг в библиотеку.
CREATE VIEW
DEBTORS
ISBN,TITLE.
NUM_READER.NAME.ADRES.HOME_PHON. WORK_PHON.DATA_OUT
AS
SELECT ISBN.TITLE.NUM_READER,NAME,ADRES.HOME_PHON.
WORK_PHON,DATA_OUT
FROM BOOKS.EXEMPLAR.READERS
WHERE BOOKS.ISBN
= EXEMPLAR.ISBN AND
EXEMPLAR.NUM_READER
= READERS.NUM_READER AND
EXEMPLAR.PRESENT
= FALSE AND
EXEMPLAR.DATA
OUT < GetDate()
Ограничение стандарта SQL1 на обновление представлений
Несмотря
на то, что для пользователей представления выглядят как реальные отношения,
существует ряд ограничений на операции модификации данных, связанные с представлениями.
СУБД может
обновлять данные через представления только в том случае, если она может однозначно
сопоставить каждой строке представления строку из реальной таблицы базы данных,
а для каждого обновляемого столбца представления однозначно определить исходный
столбец исходной таблицы базы данных. Далеко не для всех запросов это возможно
сделать. Действительно, запросы с группировкой, сложные запросы с подзапросами
возвращают результат, который СУБД не сможет однозначно интерпретировать в терминах
реальных таблиц БД.
Согласно
стандарту, представление можно обновлять только в том случае, когда его запрос
соответствует следующим требованиям: