Проектирование баз данных информационных систем

Размещено на http://www.allbest.ru/

Содержание

Введение

1. Типы данных языка SQL, определенные стандартом ISO

1.1 Идентификаторы языка SQL

1.2 Скалярные типы данных языка SQL

1.3 Точные числовые данные (тип exact numeric).

2. Средства поддержки целостности данных

2.1 Обязательные данные

2.2 Ограничения для доменов.

2.3 Целостность сущностей

2.4 Ссылочная целостность.

2.5 Требования данного предприятия.

3. Определение данных

3.1 Создание баз данных

3.2 Создание таблиц (оператор CREATE TABLE).

3.3 Модификация определения таблицы (оператор ALTER TABLE)

3.4 Удаление таблиц (оператор DROP TABLE)

3.5 Создание индекса (оператор CREATE INDEX).

3.6 Удаление индекса (оператор DROP INDEX).

4. Представления.

5. Использование транзакций

6. Управление доступом к данным

Заключение

Глоссарий

Библиографический список.

Приложение

Введение

Язык SQL является первым и пока единственным стандартным языком работы с базами данных, который получил достаточно широкое распространение. Есть еще один стандартный язык работы с базами данных, NDL (Network Database Language), который построен на использовании сетевой модели CODASYL конференция по языкам систем обработки данных, но он применяется лишь в немногих разработках. Практически все крупнейшие разработчики СУБД в настоящее время создают свои продукты с использованием языка SQL либо интерфейса SQL, и большинство таких компаний участвуют в работе, по меньшей мере, одной организации, которая занимается разработкой стандартов этого языка. В SQL сделаны огромные инвестиции как со стороны разработчиков, так и со стороны пользователей. Он стал частью архитектуры приложений (например, такой как System Application Architecture (SAA) корпорации IBM), а также является стратегическим выбором многих крупных и влиятельных организаций (например, консорциума Х/Open, занятого разработкой стандартов для среды UNIX), Язык SQL также принят в качестве федерального стандарта обработки информации (Federal Information Processing Standard -- FIPS), который должен соблюдаться в СУБД для получения разрешения продавать ее на территории США. Консорциум разработчиков SQL Access Group прилагает усилия по созданию расширений языка SQL, которые позволят обеспечить взаимодействие разнородных систем.

Язык SQL используется в других стандартах и даже оказывает влияние на разработку многих стандартов как инструмент их определения. В качестве примера можно привести стандарты ISO "Information Resource Dictionary System" (IRDS) и "Remote Data Access" (RDA). Разработка языка вызвала определенную заинтересованность научных кругов, выразившуюся как в выработке необходимых теоретических основ, так и в подготовке успешно реализованных технических решений. Это особенно справедливо в отношении оптимизации запросов, методов распределения данных и реализации средств защиты. Начали появляться специализированные реализации языка SQL, предназначенные для новых рынков, такие как OnLine Analytical Processing (OLAP специальная технология выпуска деловых отчетов).

1. Типы данных языка SQL, определенные стандартом ISO

В настоящем разделе описаны типы данных, определенные стандартом ISO SQL. Начнем с определения требований к допустимому идентификатору языка SQL.

1.1 Идентификаторы языка SQL

Идентификаторы языка SQL предназначены для обозначения объектов в базе данных и являются именами таблиц, представлений и столбцов. Символы, которые могут использоваться в создаваемых пользователем идентификаторах языка SQL, должны быть определены как набор символов. Стандарт ISO задает набор символов, который должен использоваться по умолчанию; он включает строчные и прописные буквы латинского алфавита (A-Z, a-z), цифры (0-9) и символ подчеркивания (_). Допускается использование и альтернативного набора символов.

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

*может иметь длину до 128 символов (большинство диалектов предусматривает более жесткие ограничения);

*должен начинаться с буквы;

*не может содержать пробелов.

1.2 Скалярные типы данных языка SQL

В табл. 1(смотрите приложение) перечислены скалярные типы данных языка SQL, которые определены стандартом ISO. В некоторых случаях в целях упрощения манипулирования и преобразования, а также из-за сходства основных свойств данные типов character и bit объединяются под названием "строковые типы данных", а данные типов exact numeric и approximate numeric -- под названием "числовые типы данных". В стандарте SQL3 определены также большие символьные и двоичные объекты.

Логические данные (тип boolean) состоят из различимых истинностных значений TRUE (истинный) и FALSE (ложный). Логические данные поддерживают также истинностное значение UNKNOWN (неопределенный), заданное как значение NULL, если применение неопределенных значений-не запрещено ограничением NOT NULL. Все значения данных логического типа и истинностные значения SQL могут совместно применяться в операторах сравнения и присваивания. Значение TRUE в арифметических операторах сравнения больше значения FALSE, а любое сравнение, в котором участвует значение NULL или истинностное значение UNKNOWN, возвращает результат UNKNOWN.

Символьные данные (тип character) состоят из последовательностей символов, входящих в определенный создателями СУБД набор символов. Поскольку наборы символов являются специфическими для различных диалектов языка SQL, перечень символов, которые могут входить в состав значений данных символьного типа, также зависит от конкретной реализации. В настоящее время чаще всего используются наборы символов ASCII и EBCDIC. Для определения данных символьного типа применяется следующий формат:

CHARACTER [VARYING] [length]

CHARACTER (может. быть сокращено до СНАЕ.) и

CHARACTER VARYING (может быть- сокращено до VARCHAR)

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

Символьная строка может быть определена как имеющая фиксированную или переменную (VARYING) длину. Если строка определена с фиксированной длиной, то при вводе в нее меньшего количества символов строковое значение дополняется до указанной длины пробелами, добавляемыми справа. Если строка определена с переменной длиной, то при вводе в нее меньшего количества символов в базе данных будут сохранены только введенные символы, что позволяет достичь определенной экономии внешней памяти. Например, столбец branchNo таблицы Branch с обозначением номера отделения имеет фиксированную длину четыре символа и может быть объявлен следующим образом:

branchNo CHAR(4)

Столбец address таблицы PrivateOwner имеет переменную длину значения {максимум до 30 символов), поэтому он может быть объявлен следующим образом:

address VARCHAR(30)

Битовые данные (тип bit) используется для определения битовых строк, т.е. последовательности двоичных цифр (битов), каждая из которых может иметь значение либо 0, либо 1. Для опредеяения данных битового типа используется формат, сходный с определением символьных данных:

OBIT IVARYING] {length].

Например, для сохранения битовой строки с фиксированной длиной и значением ' ООН ' может быть объявлен столбец bitstring:

bitString BIT(4)

1.3 Точные числовые данные (тип exact numeric)

Тип точных числовых данных используется для определения чисел, которые имеют точное представление в компьютере. Числа состоят из цифр и необязательных символов (десятичной точки, знака "плюс" или "минус"). Данные точного числового типа определяются значностъю (precision) и длиной, дробной части (scale). Значность задает общее количество значащих десятичных цифр числа, в которое входят длина целой и дробной частей, но без учета самой десятичной точки. Дробная часть указывает количество дробных десятичных разрядов числа. Например, точное число -12.345 имеет значность, равную 5 цифрам, и дробную часть длиной 3. Особой разновидностью точных чисел являются целые числа. Существует несколько способов определения данных точного числового типа:

NUMERIC [ precision - [, scale] ]

DECIMAL [ precision [, scale] ]

INTEGER

SMALLXNT

INTEGER. (может быть сокращено до INT) и DECIMAL (до DEC)

Типы NUMERIC и DECIMAL предназначены для хранения чисел в десятичном формате. По умолчанию длина дробной части равна нулю, а принимаемая по умолчанию значность зависит от реализации. Тип INTEGER используется для хранения больших положительных или отрицательных целых чисел. Тип SMALLINT используется для хранения небольших положительных или отрицательных целых чисел. При использовании этого типа данных расход внешней памяти существенно сокращается. Например, максимальное абсолютное значение числа, которое может сохраняться в столбцах с типом данных SMALLINT, чаще всего составляет 32 767. Для столбца rooms таблицы PropertyForRent, в котором сохраняются сведения о количестве комнат сдаваемого в аренду объекта, можно выбрать тип SMALLINT и объявить его следующим образом:

rooms SMALLINT Столбец salary таблицы Staff может быть объявлен следующим образом:

salary DECIMAL(7,2)

В этом случае максимальное значение заработной платы составит 99 999.99 фунтов стерлингов,

Округленные числовые данные (тип approximate numeric). Тип округленных числовых данных используется для описания данных, которые нельзя точно представить в компьютере, например действительных чисел. Для представления округленных чисел или чисел с плавающей точкой используется экспоненциальная система обозначений, в которой число записывается с помощью мантиссы, умноженной на определенную степень десяти (порядок), на пример: 10ЕЗ, +5.2Е6, -0.2Е-4. Существует несколько способов определения данных с типом округленных числовых данных:

FLOAT [precision]

REAL

DOUBLE PRECISION

Параметр precision задает значность мантиссы. Значность определений типа REAL и DOUBLE PRECISION зависит от конкретной реализации.

Дата и время (тип datetime). Тип данных "дата/время" используется для определения моментов времени с некоторой установленной точностью. Примерами являются даты, отметки времени и время суток. Стандарт ISO разделяет тип данных "дата/время" на подтипы YEAR (Год), MONTH (Месяц), DAY (День), HOUR (Час), MINUTE (Минута), SECOND (Секунда), TIMEZONE_HOUR (Зональный час) и TIMEZONE_MINUTE (Зональная минута). Два последних типа определяют час и минуты сдвига зонального времени по отношению к всеобщему скоординированному времени (прежнее название -- гринвичское время). Поддерживаются три типа полей даты/времени.

DATE

TIME [timePrecision] [WITH TIME 2ONS]

TIMESTAMP [timePrecision] [WITH TIME ZONE]

Тип данных DATE используется для хранения календарных дат, включающих поля YEAR, MONTH и DAY. Тип данных TIME используется для хранения отметок времени, включающих поля HOUR, MINUTE и SECOND. Тип данных TIMESTAMP служит для совместного хранения даты и времени. Параметр timePrecision задает количество дробных десятичных знаков, определяющих точность представления значений в поле SECOND. Если этот параметр опущен, по умолчанию его значение для столбцов типа TIME принимается равным нулю (т.е. сохраняется целое количество секунд), тогда как для полей типа TIMESTAMP он принимается равным 6 (т.е. отметки времени сохраняются с точностью до микросекунд). Наличие ключевого слова WITH TIME ZONE определяет использование полей TIMEZONE_HOUR и TIMEZONE_MINUTE. Например, столбец date таблицы Viewing, представляющий дату (день, месяц и год) осмотра клиентом сдаваемого в аренду объекта, может быть определен следующим образом:

viewDate DATE

Интервальный тип данных interval. Данные с интервальным типом используются для представления периодов времени. Любой интервальный тип данных состоит из набора полей: YEAR, MONTH, DAY, HOUR, MINUTE и SECOND. Существуют два класса данных с интервальным типом: интервалы год-месяц и интерналы сутки-время суток. В первом случае данные включают только два поля -- YEAR и/или MONTH. Данные второго типа могут состоять из произвольной последовательности полей DAY, HOUR, MINUTE, SECOND.

Данные интервального типа определяются следующим образом:

INTERVAL -{{startField TQ.endField} singleDatetimeField}

StartField = YEAR MONTH | DAY j HOUR | MINUTE

[ (intervaiLeadingFieldPrecisicm) ]

endField = YEAR | MONTH | DAY j.HOUR-.. | MINUTE | SECOND

[(fractionalSecondsPrecision)]

singleDatetimeField = startPield |;SECONB

[ (intervejlbeadingFie.IdPrecis.icm [,fractionalSecondsRrecision])]

Для параметра startField должна быть всегда указана размерность первого поля (intervalLeadingFieldPrecision), которая по умолчанию принимается равной двум. Например:

INTERVAL YEAR(2) ТО MONTH

Это объявление описывает интервал времени, значение которого может находиться между 0 годом, 0 месяцем и 99 годом, 11 месяцем. Еще один пример:

INTERVAL HOUR TO SECOND(4)

Это объявление описывает интервал времени, значение которого может изменяться от 0 часов, 0 минут, о секунд до 99 часов, 59 минут 59.9999 секунды. (Число дробных десятичных знаков для секунд установлено равным 4.)

Скалярные операторы. Язык SQL включает некоторое количество встроенных скалярных операторов и функций, которые могут использоваться для построения скалярных выражений, т.е. выражений, вычисление которых дает скалярный результат. Помимо обычных арифметических операторов (+, -, * и /) в языке определены и другие операторы, представленные в табл. 2.

2. Средства поддержки целостности данных

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

* обязательные данные;

* ограничения для доменов;

* целостность сущностей;

* ссылочная целостность;

* требования конкретного предприятия.

Эти ограничения могут быть определены в операторах CREATE TABLE и ALTER TABLE.

2.1 Обязательные данные

Для некоторых столбцов требуется наличие в каждой строке таблицы конкретного и допустимого значение, отличного от неопределенного значения (или значения NULL). Значение NULL не следует путать с пустыми строковыми значениями или нулевыми числовыми значениями; оно служит для представления данных, которые в данный момент недоступны, отсутствуют или не определены. Например, каждый работник обязательно занимает ту или иную должность: менеджер, заместитель и т.п. Для задания ограничений подобного типа стандарт ISO предусматривает использование спецификатора NOT NULL, указываемого в операторах CREATE TABLE и ALTER TABLE. Если для столбца задан спецификатор NOT NULL, система отвергает любые попытки вставить в такой столбец пустое значение. А если при определении характеристик столбца задан спецификатор NULL, то система допускает размещение в этом столбце значений NULL. В соответствии со стандартом ISO по умолчанию применяется спецификатор NULL. Например, для указания того, что столбец position (Должность) в таблице Staff (Персонал) не может содержать пустых значений, следует определить его, как показано ниже. position VARCHAR(IO) NOT NULL

2.2 Ограничения для доменов

Каждый столбец имеет собственный домен, т.е. некоторый набор допустимых значений. Например, для определения пола работника достаточно всего двух значений, поэтому домен для столбца sex (Пол) таблицы Staff можно определить как набор из двух строк длиной в один символ со значением либо 'М', либо ' F '. Стандарт ISO предусматривает два различных механизма определения доменов в операторах CREATE TABLE и ALTER TABLE. Первый состоит в использовании конструкции CHECK, позволяющей задать требуемые ограничения для столбца или таблицы в целом. Конструкция CHECK имеет следующий формат:

CHECK {searchCandition}

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

Sех CHAR NOT NULL CHECK {sex IN CM1, ' F 1 } )

Однако стандарт ISO позволяет определять и более сложные домены, для чего предназначен второй механизм -- использование оператора CREATE DOMAIN, имеющего следующий формат:

CREATE DOMAIN domainWame [AS] datatype

[DEFAULT defaultOption]

[CHECK (searcftCoriditicn)]

Каждому создаваемому домену присваивается имя, задаваемое параметром domainName, тип данных, определяемый параметром dataType (см. раздел 6.1.2), необязательное значение по умолчанию, устанавливаемое параметром defaulCOption, и необязательный набор допустимых значений, определяемый в конструкции CHECK. Следует отметить, что приведенный формат оператора CREATE DOMAIN является неполным, однако его достаточно для демонстрации основных возможностей. Таким образом, в условиях предыдущего примера мы могли бы определить домен для столбца sex с помощью следующего оператора:

CREATE DOMAIN SexType AS CHAR

DEFAULT 'M'

CHECK (VALUE IN { ' M ', ' F ' ) ) ;

В результате обработки этого оператора в базе данных будет создан домен под именем SexType, состоящий из двух отдельных символов, имеющих значения "М1 и 'F'. Теперь столбец sex в таблице Staff можно будет описать, используя домен SexType вместо определителя типа данных CHAR:

sex SexType NOT NULL

Значение параметра searchCondicion может предусматривать обращение к справочной таблице. Например, можно создать домен BranchNumber (Номер отделения), который позволит вводить в соответствующие столбцы различных таблиц только те значения, которые уже существуют в столбце branchNo таблицы Branch. Для этой цели необходимо использовать следующий оператор:

CREATE DOMAIN BranchNumber AS VARCHAR(4)

CHECK (VALUE IN (SELECT branchNo PROM Branch));

Удаление доменов из базы данных выполняется с помощью оператора DROP DOMAIN, имеющего следующий формат:

DROP DOMAIN domainName [RESTRICT | CASCADE]

Спецификатор способа удаления домена (RESTRICT или CASCADE) определяет, какие действия выполняются в базе данных, если домен в настоящее время используется. Если задан спецификатор RESTRICT, а домен применяется в существующей таблице, представлении или определении проверки (см. раздел 6.5.2), то операция удаления оканчивается неудачей. А если задан спецификатор CASCADE, то в любой столбец таблицы, который основан на определении домена, автоматически вносятся изменения таким образом, чтобы в нем применялся базовый тип данных домена, а любые ограничения или применяемые по умолчанию конструкции операторов для этого домена заменяются в случае необходимости ограничениями столбца или применяемой по умолчанию конструкцией оператора для соответствующего столбца.

2.3 Целостность сущностей

Первичный ключ таблицы должен иметь уникальное непустое значение в каждой ее строке. Например, каждая строка таблицы PropertyForRent должна содержать уникальное значение номера объекта недвижимости, помещенное в столбец propertyNo; именно оно будет уникальным образом определять объект недвижимости, представленный этой строкой таблицы. Стандарт ISO позволяет задавать подобные требования поддержки целостности данных с помощью конструкции PRIMARY KEY в операторах CREATE TABLE и ALTER TABLE. Например, для определения первичного ключа таблицы PropertyForRent можно использовать следующую конструкцию:

PRIMARY KEY(staffNo)

В случае составного первичного ключа, например, первичного ключа таблицы Viewing, состоящего из двух столбцов под именами clientNo и propertyNo, конструкция определения первичного ключа PRIMARY KEY будет иметь вид

PRIMARY KEY(clientNo, propertyNo)

Конструкция PRIMARY KEY может указываться в определении таблицы только один раз. Однако существует возможность гарантировать уникальность значений и для любых альтернативных ключей таблицы, для чего предназначено ключевое слово UNIQUE. Кроме того, при определении столбцов альтернативных ключей рекомендуется использовать и спецификаторы NOT NULL. В каждой таблице может быть определено произвольное количество конструкций UNIQUE. База данных отвергает любые попытки выполнения операций INSERT или UPDATE, которые влекут за собой создание повторяющегося значения в любом потенциальном ключе (под этим подразумевается первичный или альтернативный ключ). Например, определение таблицы Viewing можно переписать следующим образом:

clientNo VARCHAR{5) NOT NULL,

propertyNo VARCHAR(S) NOT NULL,

UNIQUE (clientNo, propertyNo)

2.4 Ссылочная целостность

Внешние ключи представляют собой столбцы или наборы столбцов, предназначенные для связывания каждой из строк дочерней таблицы, содержащей этот внешний ключ, со строкой родительской таблицы, содержащей соответствующее значение потенциального ключа. Понятие ссылочной целостности означает, что если поле внешнего ключа содержит некоторое значение, то оно обязательно должно ссылаться на существующую допустимую строку в родительской таблице. Например, значение в столбце номера отделения branchNo таблицы PropertyForRent всегда должно связывать данные об объекте недвижимости с конкретной строкой таблицы Branch, соответствующей тому отделению компании, за которым закреплен этот объект недвижимости. Если столбец с номером отделения не пуст, он обязательно должен являться допустимым значением столбца branchNo таблицы Branch. В противном случае объект недвижимости будет закреплен за несуществующим отделением компании.

Стандарт ISO предусматривает механизм определения внешних ключей с помощью конструкции FOREIGN KEY операторов CREATE TABLE и ALTER TABLE. Например, для определения внешнего ключа branchNo в таблице PropertyForRent можно использовать следующуюконструкцию:

FOREIGN KEY(branchNo) REFERENCES Branch

Теперь система отклонит выполнение любых операторов INSERT или UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице значение внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы. Действия системы, выполняемые при поступлении операторов UPDATE или DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или несколько строк дочерней таблицы, зависят от правил поддержки ссылочной целостности, указанных в конструкциях ON UPDATE и ON DELETE конструкции FOREIGN KEY. На тот случай, если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или несколько строк дочерней таблицы, в языке SQL предусмотрены следующие четыре допустимых варианта действий.

*CASCADE. Удаление строки из родительской таблицы сопровождается автоматическим удалением всех ссылающихся на нее строк дочерней таблицы. Поскольку удаляемые строки дочерней таблицы также могут содержать некоторые потенциальные ключи, используемые в качестве внешних ключей в других таблицах, анализируются и применяются правила обработки внешних ключей этих таблиц, активизируется проверка правил обработки внешних ключей и т.д. Такой способ выполнения операции называется каскадным, поскольку он предусматривает переход с одного уровня иерархии на другой.

* SET NULL. Выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносятся значения NULL. Этот вариант применим только в том случае, если в определении столбца внешнего ключа отсутствует ключевое слово NOT NULL.

*SET DEFAULT. Выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию. Этот вариант применим только в том случае если в определении столбца внешнего ключа присутствует ключевое слово DEFAULT и задано значение, используемое по умолчанию.

*NO ACTION. Операция удаления строки из родительской таблицы отвергается. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа конструкция ON DELETE опущена. Те же правила применяются в языке SQL и тогда, когда значение потенциального ключа родительской таблицы обновляется. В случае использования правила CASCADE в столбцы внешнего ключа дочерней таблицы помещается новое, измененное значение потенциального ключа родительской таблицы. Аналогичным образом, обновления каскадно распространяются на другие таблицы, если их внешние ключи ссылаются на обновленные столбцы дочерней таблицы. Например, в таблице PropertyForRent столбец табельного номера работника staff No является внешним ключом, ссылающимся на таблицу staff. Для этого внешнего ключа можно установить правило удаления, указывающее, что в случае удаления записи о работнике из таблицы staff соответствующее значение в столбце staffNo таблицы PropertyForRent должно быть заменено значением NULL:

FOREIGN KEY (staffNo} REFERENCES Staff ON DELETE SET NULL

Аналогичным образом, столбец с номером владельца объекта недвижимости ownerNo таблицы PropertyForRent является внешним ключом, связывающим ее с таблицей PrivateOwner. Можно установить правило обновления, указывающее, что в случае изменения номера владельца в таблице PrivateOwner соответствующие значения в столбце ownerNo таблицы PropertyForRent также должны быть заменены новым значением:

FOREIGN KEY {ownerNo) REFERENCES PrivateOwner ON UPDATE CASCADE

2.5 Требования данного предприятия

Обновления данных в таблицах могут быть ограничены существующими в данной организации требованиями (которые принято также называть деловым регламентом), установленными в отношении выполнения вручную операций, связанных с внесением изменений в информацию. Например, в компании DreamHome существует правило, ограничивающее количество сдаваемых в аренду объектов, за которые может отвечать один работник, причем верхний предел установлен равным ста объектам. Стандарт ISO позволяет реализовать деловой регламент предприятий либо с помощью конструкций CHECK и ключевого слова UNIQUE в операторах CREATE TABLE и ALTER TABLE, либо с помощью оператора CREATE ASSERTION. Использование конструкции CHECK и ключевого слова UNIQUE уже обсуждалось выше в этом разделе. Оператор CREATE ASSERTION предназначен для введения ограничений целостности данных, которые непосредственно не связаны с определениями таблиц. Этот оператор имеет следующий формат:

CREATE ASSERTION ArsercicnNane

CHECK (searchCondion);

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

CHEATS ASSERTION Staff NotHandlingTooMuch

CHECK (NOT EXISTS (SELECT staff No

FROM PropertyForRent

GROUP BY staffNo

HAVING COUNT(*} > 100))

В следующем разделе показано, как используются эти средства обеспечения целостности в операторах CREATE TABLE и ALTER TABLE.

3. Определение данных

Язык определения данных SQL DDL язык управления данными(Data Definition Language) позволяет создавать и уничтожать такие объекты базы данных, как схемы, домены, таблицы, представления и индексы. В настоящем разделе кратко рассматриваются способы создания и удаления схем, таблиц и индексов, а в следующем разделе показано, как создавать и удалять представления. Стандарт ISO предусматривает также возможность создания наборов символов, схем сортировки и преобразования. Но в настоящей книге эти объекты базы данных не рассматриваются. Ниже перечислены основные операторы языка определения данных SQL.

CREATE SCHEMA

CREATE DOMAIN

CREATE TABLE

CREATE VIEW

ALTER DOMAIN

ALTER TABLE

DROP SCHEMA

DROP DOMAIN

DROP TABLE

DROP VIEW

Эти операторы используются для создания, модификации и уничтожения структур, входящих в состав концептуальной схемы. Во многих СУБД предусмотрены также следующие два оператора, хотя они не рассматриваются в стандарте SQL:

CREATE INDEX DROP INDEX

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

3.1 Создание баз данных

В различных СУБД процедура создания баз данных существенно отличается. В многопользовательских системах право создания баз данных обычно закрепляется только за администратором базы данных (АБД). В однопользовательских системах предусмотренная по умолчанию база данных может быть создана непосредственно в процессе установки и настройки параметров самой СУБД, а другие базы данных создаются самим пользователем по мере необходимости. Стандарт ISO не определяет, как должны создаваться базы данных, поэтому в каждом из диалектов языка SQL обычно используется собственный подход. В соответствии со стандартом ISO, таблицы и другие объекты базы данных существуют в некоторой среде (environment). Помимо всего прочего, каждая среда состоит из одного или нескольких каталогов (catalog), а каждый каталог -- из набора схем (schema). Схема представляет собой именованную коллекцию объектов базы данных, которые определенным образом связаны друг с другом (все объекты в базе данных должны быть описаны в той или иной схеме). Объектами схемы могут быть таблиц, представления, домены, утверждения, сопоставления, толкования и наборы символов. Все объекты схемы имеют одного и того же владельца и множество общих значений, применяемых по умолчанию.

Этот стандарт оставляет право выбора конкретного механизма создания и уничтожения каталогов за разработчиком СУБД, однако регламентирует механизм создания и удаления схем. Оператор определения схемы имеет следующий формат (упрощенно):

CREATE. SCHEMA [nаше | AUTHORIZATION Creator-Identifier]

Таким образом, если создателем схемы под именем SqlTests является пользователь Smith, то данный оператор будет выглядеть следующим образом:

CREATE SCHEMA SqlTests AUTHORIZATION Smith;

В стандарте ISO также указано, что должна существовать возможность определить в рамках данного оператора диапазон средств, доступных пользователям создаваемой схемы. Однако конкретные способы определения подобных привилегий в разных СУБД различаются. Схема удаляется с помощью оператора DROP SCHEMA, который имеет следующий формат:

DROP SCHEMA Name [ RESTRICT | CASCADE]

Если указано ключевое слово RESTRICT (именно оно принимается по умолчанию), схема должна быть пустой, иначе выполнение операции будет отменено. Если указано ключевое слово CASCADE, при выполнении оператора будут автоматически удалены все связанные с удаляемой схемой объекты, причем в порядке, указанном выше. Если одна из этих операций удаления будет завершена неудачно, выполнение всего оператора DROP SCHEMA будет отменено. Общий эффект от выполнения оператора DROP SCHEMA с параметром CASCADE может затронуть значительную часть базы данных, поэтому подобные операторы должны вводиться с исключительной осторожностью.

В настоящее время операторы CREATE SCHEMA и DROP SCHEMA реализованы в очень немногих СУБД.

3.2 Создание таблиц (оператор CREATE TABLE)

После создания общей структуры базы данных можно приступить к созданию таблиц, представляющих отношения, входящие в состав проекта базы данных. Для этой цели используется оператор CREATE TABLE, имеющий следующий общий формат:

CREATE TABLED TableName

{ (columName data Type [NOT NULL] [UNIQUE]

[DEFAULT defaultoption] [CHSCK (searchCondition}:] [, …] }

[PRIMARY KEY (ListOfColumns) ]

{ [UNIQUE {listOfCoIumns) ] [,...] }

{ [FOREIGN KEY (listOfFdreignKeyColumns)

REFERENCES ParentTabl eName [(listOfCandidateKeyColuims) ],

[MATCH {PARTIAL | FULL}

[ON UPDATE. referentialAction]

[ON DELETE referential Action] } [,,.-.] }

{[CHECK (searchCondtitioa)] [,.,, ] } )

Эта версия оператора CREATE TABLE включает средства определения ограничений ссылочной целостности и других ограничений. Структура самого оператора и степень поддержки тех или иных ограничений в значительной степени зависят от применяемого диалекта языка SQL. Но, как правило, в базе данных следует использовать все поддерживаемые ограничения, поскольку это позволяет повысить качество хранимых данных.

В результате выполнения этого оператора будет создана таблица, имя которой определяется параметром TableName, состоящая из одного или нескольких столбцов типа dataType. Набор доступных типов данных описан в разделе 1.2. Для задания значения, применяемого по умолчанию при вставке данных в конкретный столбец, предусмотрена необязательная конструкция DEFAULT. В базе данных это значение применяется по умолчанию в тех случаях, если в операторе INSERT не задано значение для такого столбца. Кроме прочих значений, опция определения применяемого по умолчанию значения defaultoption может включать литералы. Конструкции КОТ NULL, UNIGUE и CHECK рассматривались в предыдущем разделе. Остальные конструкции известны под названием ограничений таблицы и могут быть дополнительно обозначены с помощью следующей конструкции:

CONSTRAINT ConstraintName

Эта конструкция позволяет в дальнейшем удалить ограничение, указав его имя в операторе ALTER TABLE, как описано ниже. Конструкция PRIMARY KEY определяет один или несколько столбцов, которые образуют первичный ключ таблицы. Если эта конструкция предусмотрена в диалекте SQL, реализованном в конкретной базе данных, то она должна применяться при создании каждой таблицы. По умолчанию для всех столбцов, представляющих первичный ключ, предусмотрено применение ограничения NOT NULL. При создании таблицы разрешено использование только одной конструкции PRIMARY KEY. База данных отвергает все попытки выполнения операций INSERT или UPDATE, которые влекут за собой создание строки с повторяющимся значением в столбце (столбцах) PRIMARY KEY. Таким образом, в базе данных гарантируется уникальность значений первичного ключа. В конструкции FOREIGN KEY определяется внешний ключ (дочерней) таблицы и ее связь с другой (родительской) таблицей. Эта конструкция позволяет реализовать ограничения ссылочной целостности и состоит из следующих частей.

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

*Вспомогательная конструкция REFERENCES, указывающая на родительскую таблицу (т.е. таблицу, в которой определен соответствующий потенциальный ключ). Если список UstOfCandidateKsyCalumns опущен, предполагается, что определение внешнего ключа совпадает с определением первичного ключа родительской таблицы. В таком случае родительская таблица должна иметь в своем операторе CREATE TABLE конструкцию PRIMARY KEY.

*Необязательное правило обновления (ON UPDATE) для определения взаимосвязи между таблицами, которое указывает, какое действие (referentialAction) должно выполняться при обновлении в родительской таблице потенциального ключа, соответствующего внешнему ключу дочерней таблицы. В качестве парамйтра referentiaJAcCion можно указать CASCADE, SET NULL, SET DEFAULT ИЛИ NO ACTION. Если КОНСТРУКЦИЯ ON UPDATE опущена, то по умолчанию подразумевается, что никакие действия не выполняются, в соответствии со значением NO ACTION.

*Необязательное правило удаления (ON DELETE) для определения взаимосвязи между таблицами, которое указывает, какое действие (referentialAction) должно выполняться при удалении строки из родительской таблицы, которая содержит потенциальный ключ, соответствующий внешнему ключу дочерней таблицы. Определение параметра referentzalAction совпадает с определением такого же параметра для правила ON UPDATE.

*По умолчанию ограничение ссылочной целостности удовлетворяется, если любой компонент внешнего ключа имеет значение NULL или в родительской таблице есть соответствующая строка. Опция MATCH позволяет ввести дополнительные ограничения, касающиеся применения значений NULL во внешнем ключе. Если задана опция MATCH FULL, то либо все компоненты внешнего ключа должны быть пусты (NULL), либо все должны иметь непустые значения. А если задана опция MATCH PARTIAL, то либо все компоненты внешнего ключа должны быть пусты (NULL), либо в родительской таблице должна существовать хотя бы одна строка, способная удовлетворить это ограничение, если все остальные значения NULL были подставлены правильно. Некоторые авторы утверждают, что в ограничениях ссылочной целостности следует применять только опцию MATCH FULL.

В операторе создания таблицы может быть задано любое количество конструкций FOREIGN KEY. Конструкции CHECK и CONSTRAINT позволяют определять дополнительные ограничения. Если конструкция CHECK используется в качестве ограничения столбца, то она может ссылаться только на определяемый столбец. Ограничения фактически контролируются после применения каждого оператора SQL к таблице, на которой они заданы, но такая проверка может быть отложена до окончания той транзакции, в состав которой входит текущийоператор SQL.

3.3 Модификация определения таблицы (оператор ALTER TABLE)

В стандарте ISO предусмотрено применение оператора ALTER TABLE для изменения структуры таблицы после ее создания. Определение оператора ALTER TABLE состоит из шести опций, позволяющих выполнить следующие действия:

*ввести новый столбец в таблицу;

*удалить столбец из таблицы;

*ввести новое ограничение та(5лицы;

*удалить ограничение таблицы;

*задать для столбца значение, применяемое по умолчанию;

*удалить опцию, предусматривающую применение для столбца значения, заданного по умолчанию.

Ниже приведен основной формат этого оператора.

ALTER TABLE TableName

[ADD [COLUMN] columneName [RESTRICT | CASCADE]]

[DROP [COLUMN] columnName [RESTRICT | CASCADE]]

[ADD [CONSTRAINT [ConstreintName]] tableConstraintDefinition]

[DROP CONSTRAINT ConstraintName [RESTRICT | CASCADE]]

[ALTER [COLUMN] SET DEFULT defaultOption]

[ALTER [COLUMN] DROP DEFAULT]

Почти все параметры данного оператора совпадают с параметрами оператора CREATE TABLE, описанного в предыдущем разделе, В качестве параметра с определением ограничения таблицы CaJbleConstraintDefinition может применяться одна из конструкций PRIMARY KKY, UNIQUE, FOREIGN KEY или CHECK. Конструкция ADD COLUMN аналогична конструкции определения столбца в операторе CREATE TABLE. В конструкции DROP COLUMN задается имя столбца, удаляемого из определения таблицы, и имеется необязательная опция, позволяющая указать, является ли действие операции DROP каскадным или нет, как показано ниже.

*RESTRICT, Операция DROP отвергается, если на данный столбец имеется ссылка в другом объекте базы данных (например, в определении представления). Это значение опции предусмотрено по умолчанию.

*CASCADE. Выполнение операции DROP продолжается в любом случав и ссылки на столбец автоматически удаляются из любых объектов базы данных, где они имеются. Эта операция выполняется каскадно, поэтому если столбец удаляется из объекта, содержащего ссылку, то в базе данных выполняется проверка того, имеются ли ссылки на этот столбец я каком-либо ином объекте, такие ссылки уничтожаются и в этом объекте, и т.д.

3.4 Удаление таблиц (оператор DROP TABLE)

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

DROP TABLE.TableWame [RESTRICT I CASCADE]

Например, для удаления таблицы PropertyForRent можно использовать следующий оператор:

DROP TABLE PropertyForRent;

Однако следует отметить, что эта команда удалит не только указанную таблицу, но и все входящие в нее строки данных. Если требуется удалить из таблицы лишь строки данных, сохранив в базе описание самой таблицы, то следует использовать оператор DELETE. Оператор DROP TABLE дополнительно позволяет указывать, следует ли операцию удаления выполнять каскадно.

*RESTRICT. Операция DROP отвергается, если в базе данных имеются другие объекты, существование которых зависит от того, существует ли в базе данных удаляемая таблица.

*CASCADE. Операция DROP продолжается, и из базы данных автоматически удаляются все зависимые объекты (и объекты, зависящие от этих объектов).

Общий эффект от выполнения оператора DROP TABLE с ключевым словом CASCADE может распространяться на значительную часть базы данных, поэтому подобные операторы следует использовать с максимальной осторожностью. Чаще всего оператор DROP TABLE используется для исправления ошибок, допущенных при создании таблицы. Если таблица была создана с неправильной структурой, можно воспользоваться оператором DROP TABLE для ее удаления, после чего создать таблицу заново.

3.5 Создание индекса (оператор CREATE INDEX)

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

CREATE [UNIQUE] INDEX IndexName

ON TableName (columnName [ASC| DESC] [, ….])

Указанные в операторе столбцы составляют ключ индекса и должны быть перечислены в порядке уменьшения значимости. Индексы могут создаваться только для таблиц базы данных, но не для представлений. Если в операторе указано ключевое слово UNIQUE, уникальность значений ключа индекса будет автоматически поддерживаться СУБД. Требование уникальности значений обязательно для первичных ключей, а также, возможно, и для других столбцов таблицы (например, для альтернативных ключей). Хотя создание индексов осуществимо в любой момент, при построении индекса для уже заполненной данными таблицы могут возникнуть проблемы, связанные с дублированием данных в различных строках. Следовательно, имеет смысл создавать уникальные индексы (по крайней мере для первичного ключа) непосредственно при создании таблицы. В результате система сразу же возьмет на себя контроль над уникальностью значений данных в соответствующих столбцах.

Для таблиц Staff и PropertyForRent должны быть созданы, по крайней мере, следующие индексы:

CREATE UNIQUE INDEX ScaffNoInd ON Staff (staffNo);

CREATE UNIQUE INDEX PropertyNoInd ON PropertyForRent (propertyNo);

Для каждого из ключевых столбцов может быть указан порядок следования значений -- по возрастанию (ASC) или по убыванию (DESC), причем значение АЗС используется по умолчанию. Например, для таблицы PropertyForRent можно создать следующий индекс:

CREATE INDEX Rentlnd ON PropertyForRent (city, rent),-

При обработке этого оператора будет создан файл под именем Rentlnd, содержащий данные вновь созданного индекса таблицы PropertyForRent. Строки в этом файле будут расположены в порядке возрастания значений столбца city, а внутри них -- в порядке возрастания значений столбца rent.

3.6 Удаление индекса (оператор DROP INDEX)

Если для таблицы базы данных был создан индекс, который впоследствии оказался ненужным, то его можно удалить с помощью оператора DROP INDEX. Этот оператор имеет следующий формат:

DROP INDEX JndexWame

С помощью приведенного ниже оператора будет удален индекс, созданный в предыдущем примере.

DROP INDEX Rentlnd;

4. Представления

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

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

5. Использование транзакций

Стандарт ISO включает определение модели транзакций, построенной на использовании двух специальных операторов -- COMMIT и ROLLBACK. Большинство коммерческих реализаций языка SQL (однако не все) поддерживает эту модель, которая впервые была реализована в СУБД DB2 компании IBM. Транзакцией называется логическая единица работы, состоящая из одного или нескольких операторов SQL, которая с точки зрения восстановления данных будет рассматриваться и обрабатываться системой как единое неделимое действие. В стандарте указывается, что в языке SQL транзакция автоматически запускается любым инициализирующим транзакцию оператором SQL, выполняемым пользователем или программой (например, SELECT, INSERT или UPDATE). Изменения, внесенные в базу данных в ходе выполнения транзакции, не будут восприниматься любыми другими выполняющимися параллельно транзакциями до тех пор, пока эта транзакция не будет явным образом завершена. Завершение транзакции может быть выполнено одним из следующих четырех способов.

*Ввод оператора COMMIT означает успешное завершение транзакции. После его выполнения внесенные в базу данных изменения приобретают постоянный характер. После обработки оператора COMMIT ввод любого инициирующего транзакцию оператора автоматически вызовет запуск новой транзакции.

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

*При внедрении операторов SQL в текст программы успешное окончание ее работы автоматически вызовет завершение последней запущенной программой транзакции, даже если оператор COMMIT для нее не был введен явно.

*При внедрении операторов SQL в текст программы аварийное окончание ее работы автоматически вызовет откат последней транзакции, запущенной этой программой.

В языке SQL запрещено использование вложенных транзакций. С помощью оператора SET TRANSACTION пользователи могут настраивать определенные характеристики процесса обработки транзакций. Основной формат этого оператора имеет следующий вид:

SET' TRANSACTION

[READ ONLY | READ WRITS} |

[ISOLATION LEVEL READ UNCOMMITTED READ COMMITTED

REPEATABLE READ | SERIALIZABLE3]

Квалификаторы READ ONLY и READ WRITE указывают, что в транзакциях допускается выполнение только операций чтения или чтения и записи. По умолчанию предполагается использование квалификатора READ WRITE (если только не выбран уровень изоляции READ UNCOMMITTED). Вероятно, многих смутит тот факт, что в режиме READ ONLY в транзакциях допускается выдача операторов INSERT, UPDATE и DELETE для временных таблиц (но только для временных). Показатель уровня изоляции определяет ту степень взаимодействия с другими транзакциями, которая допускается при выполнении транзакции. Сведения об ограничениях в отношении сериализации (определения порядка следования) результатов выполнения транзакций для каждого из существующих уровней изоляции приведены в табл. 3. Полная безопасность гарантируется только уровнем изоляции SERIALIZABLE, который предусматривает генерацию временных графиков сериализации. Все остальные уровни изоляции требуют, чтобы СУБД предоставляла некоторый механизм, который программисты могли бы использовать для обеспечения сериализации данных. Там же будут даны дополнительные разъяснения по поводу механизмов выполнения транзакций и сериализации.

6. Управление доступом к данным

Язык SQL включает операторы GRANT и REVOKE, предназначенные для организации защиты таблиц в базе данных. Применяемый механизм защиты построен на использовании идентификаторов пользователей, предоставляемых им прав владения и привилегий.

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