Глушаков С.В., Ломотько Д.В. Базы данных

Подождите немного. Документ загружается.

200

Часть II. Структурированный язык запросов SQL

щим данным - например, попытка уменьшить размер поля может

привести к потере данных.

ALTER TABLE не действует, когда таблица должна быть пе-

реопределена, однако при разработке БД не стоит исключать не-

обходимость этого действия. Кроме того, изменение структуры

таблицы в тот момент, когда она находится в использовании,

также чревато потерей информации - например, запрос может

потерпеть неудачу по той причине, что некоторого поля в табли-

це просто уже не существует По этим причинам лучше разраба-

тывать БД так, чтобы использовать ALTER TABLE только в

крайнем случае.

Для того, чтобы иметь возможность удалить таблицу, пользо-

ватель должен быть ее собственником, т.е. создателем. Кроме

того, перед удалением, SQL потребует очистки таблицы от дан-

ных, что позволяет избежать случайной и невосполнимой потери

информации. Таким образом, таблица, с находящимися в ней

строками, не может быть удалена.

Синтаксис команды для удаления таблицы (при условии, что

она является пустой) следующий:

DROP TABLE <TABLE NAME>;

После выполнения этой команды, имя таблицы больше не

распознается, и нет таких действий, которые могли быть выпол-

нены с этим объектом. Перед удалением стоит убедиться в том,

что эта таблица не ссылается на другую таблицу и что она не ис-

пользуется в каком-либо представлении.

Например, для удаления таблицы STUDENTS, в которой все

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

DROP TABLE STUDENTS;

Таким образом, использование рассмотренных DDL команд

позволяет создавать новые, изменять структуру существующих и

удалять пустые таблицы БД.

Индексы

Индексом принято называть упорядоченный список полей или

групп полей в таблице. Таблицы могут иметь огромное количест-

во записей, при этом, как было замечено выше, записи не нахо-

дятся в каком-либо определенном порядке, поэтому на их поиск

Глава 2.4. Способы создания баз данных

201

по указанному критерию может потребоваться достаточно

продолжительное время.

Индексный адрес - это специальный метод обеспечения

объединения всех значений в группы из одной или больше за-

писей, которые отличаются одна от другой, т.к. уникальность

записей часто необходима.

Индексы - это полезный инструмент, который широко приме-

няется во всех современных СУБД. Когда создается индекс в по-

ле, БД запоминает соответствующий порядок всех значений этого

поля в области памяти. О преимуществах индексов может гово-

рить следующее: предположим, что таблица STUDENTS имеет

несколько тысяч записей, и необходимо найти студента с кон-

кретным номером студенческого билета. Т.к. записи в таблице не

упорядочены, то СУБД будет вынуждена просматривать всю таб-

лицу, строку за строкой, проверяя каждый раз значение поля

SNUM на равенство искомому значению. При наличии индекса в

поле SNUM, система могла бы найти искомый номер прямо в

этом упорядоченном индексе, и дать информацию о том, как най-

ти правильную строку таблицы.

У индексов есть и недостатки. В то время как индекс значи-

тельно улучшает эффективность запросов, использование индек-

са несколько замедляет операции модификации, особенно такие,

как INSERT и DELETE. Кроме того, сам индекс занимает место

на устройстве хранения информации.

Отсюда вытекает, что при создании таблицы необходимо

принять взвешенное решение, индексировать ее или нет. Индек-

сы могут состоять сразу из нескольких полей, при этом первое

поле является как бы главным, второе упорядочивается внутри

первого, третье внутри второго, и т. д.

Синтаксис команды для создания индекса следующий:

CREATE INDEX <INDEX NAME> ON <TABLE NAME>

(<COLUMN NAME> [,<COLUMN NAME>]...);

Разумеется, что таблица, для которой создается индекс, долж-

на уже существовать и содержать имена индексируемых полей.

При этом имя индекса не может быть использовано для чего-либо

другого в БД и SQL сам решает, когда он необходим для работы

и использует его автоматически.

Приведем следующий пример. Очевидно, что в таблице STU-

DENTS одним из наиболее часто употребляемых может быть ин-

202

Часть II. Структурированный язык запросов SQL

деке по полю, содержащему фамилию студента. Тогда команда

для создания такого индекса будет следующей:

CREATE INDEX SFAMIDX ON STUDENTS (SFAM) ;

После этого при поиске информации о студентах, СУБД будет

находить ее очень быстро. Однако, при создании этого индекса,

ему не предписана уникальность, несмотря на то, что это являет-

ся одним из его назначений.

Для создания уникальных (не содержащих повторяющихся

значений) индексов используют ключевое слово UNIQUE в ко-

манде CREATE INDEX. Фактически такой индекс будет являться

первичным ключом таблицы. Например, для таблицы STU-

DENTS поле SNUM подходит в качестве первичного ключа, и он

станет первым кандидатом для уникального индекса. Создать его

можно командой:

CREATE UNIQUE INDEX

SNUMIDX ON STUDENTS (SNUM);

Имейте в виду, что эта команда не будет выполнена, если в

поле SNUM имеются неуникальные значения. Поэтому рекомен-

дуется создавать индексы сразу после того, как создана таблица и

до ввода в нее каких-либо значений. Интересна и такая особен-

ность уникального индекса: если в нем используется более одно-

го поля (т.е. он является комбинацией значений), то, вообще го-

воря, каждое из этих полей может и не быть уникальным.

Поскольку основным признаком индекса является его имя, то

по его имени он может быть идентифицирован и удален. Обычно

пользователи не знают о существовании индекса, a SQL автома-

тически определяет - позволено ли пользователю использовать

индекс, и. если да, то разрешает его употребление.

Однако, для удаления индекса, необходимо знать его имя. С

учетом этого команда для удаления имеет следующий синтаксис:

DROP INDEX <INDEX NAME>;

Например, для удаления созданного индекса по фамилии сту-

дента, можно воспользоваться следующей командой:

DROP INDEX SFAMIDX;

Учтите, что удаление индекса ни в коем случае не воздейству-

ет на данные, которые содержатся в полях.

Глава 2.4. Способы создания баз данных

203

Определение условий проверки

Для расширения возможностей работы с данными, вводимы-

ми в таблицу, интересен механизм условий проверки значений,

или, как его еще часто называют - ограничения данных.

Ограничения данных - это часть определений таблицы, кото-

рая ограничивает значения, допустимые к вводу в поля таблицы.

До этого в качестве ограничения на данные, рассмотренного на-

ми, были только тип и размер вводимых значений, которые

должны быть совместимы с теми полями, в которые эти значения

помещаются.

Ограничения дают большие возможности и, например, огово-

рив значение по умолчанию (это значение, которое автоматически

вставляется в любое поле таблицы, когда явное значение отсутст-

вует в команде), команда INSERT для поля уже не будет встав-

лять NULL значение.

Когда создается таблица или изменяется ее структура, можно

устанавливать ограничения на значения, которые могут быть вве-

дены в поля. Если это сделано, SQL будет отвергать любые зна-

чения, которые нарушают оговоренные критерии. Как уже было

сказано ранее, имеется два основных типа ограничений - ограни-

чение поля (атрибута) и ограничение таблицы (отношения). На-

помним, что различие между ними в том. что ограничение поля

применяется только к определенному полю, а ограничение таб-

лицы - к группам из одного и более полей.

Объявление ограничений осуществляется следующим обра-

зом: ограничение поля помещается в конец фрагмента команды,

объявляющего его имя после типа данных; ограничение таблицы

помещается в конец объявления имени таблицы после последнего

имени поля. Таким образом, полный синтаксис для команды

CREATE TABLE такой:

CREATE TABLE <TABLE NAME>

(<COLUMN NAME> <DATA TYPE> [(<SIZE>)]

<COLUMN CONSTRAINT^

(<COLUMN NAME> <DATA TYPE> [(<SIZE>)]

<COLUMN CONSTRAINTS ...

[,<COLUMN NAME>])...);

204

Часть II. Структурированный язык запросов SQL

Здесь ограничение поля (COLUMN CONSTRAINT) применя-

ется к тому полю, после чьего имени это ограничение следует.

Названия полей в круглых скобках после ограничения таблицы

(TABLE CONSTRAINT) - это поля, к которым применено данное

ограничение.

Часто команду CREATE TABLE используют для того, что-

бы предохранить поле от попадания в него NULL значений. С

этой целью используют ограничение NOT NULL, которое мо-

жет применяться только как ограничение поля. Очевидно, что

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

поскольку это будет нарушать их функциональные возможно-

сти. Более того, такие поля, как правило, требуют определен-

ных уникальных значений.

Например, если при создании таблицы STUDENTS, поместить

ключевые слова NOT NULL сразу после типа данных для полей,

в которых неопределенное значение нежелательно, любая попыт-

ка их записи в эти поля будет отклонена. Если же при создании

таблицы это не оговорено, то SQL считает, что NULL значения

разрешены. Итак, рассмотрим команду:

CREATE TABLE STUDENTS

(SNUM INTEGER NOT NULL,

SPAM CHAR (20) NOT NULL,

SIMA CHAR (10),

SOTCH CHAR (15)

STIP DECIMAL) ;

В такой таблице неопределенное значение не может быть ус-

тановлено для полей SNUM и SFAM. При этом важно не забы-

вать, что теперь для этих столбцов обязательно должно быть ус-

тановлено явное значение в команде INSERT.

При использовании команды ALTER TABLE для добавления

новых столбцов к уже существующей таблице, можно помещать

ограничение столбцов типа NOT NULL. Однако если новому

столбцу устанавливается ограничение NOT NULL, модифици-

руемая таблица должна быть пустой.

Часто в поле требуется реализовать ограничение, связанное с

уникальностью значений - это даже скорее свойство данных в

таблице, и поэтому его более логично назвать ограничением дан-

ных, а не просто ограничением поля. Несомненно, уникальные

индексы - один из самых простых и наиболее эффективных мето-

Глава 2.4. Способы создания баз данных

205

дов предписания уникальности. Однако имеется возможность

установить уникальность в качестве ограничения столбца.

Если необходимо быть уверенным в том, что все значения,

введенные в поле, отличаются друг от друга, то в ограничение

поля при создании таблицы помещают ключевое слово UNIQUE.

При этом СУБД отклонит любую попытку ввода в это поле зна-

чения, которое уже имеет место в другой строке. Это ограниче-

ние может применяться только к полям, для которых уже было

объявлено ограничение NOT NULL, что вполне логично - не име-

ет смысла позволить одной записи иметь значение NULL, а затем

исключать другие строки с NULL значениями как дубликаты.

Для иллюстрации этих возможностей, усовершенствуем пре-

дыдущий пример и запретим повторяющиеся значения в поле

SNUM, т.к. в этой таблице логично использование его в качестве

первичного ключа:

CREATE TABLE STUDENTS

(SNUM INTEGER NOT NULL UNIQUE,

SFAM CHAR (20) NOT NULL,

SIMA CHAR (10),

SOTCH CHAR (15)

STIP DECIMAL);

Напомним, что поля, значения в которых требуют уникально-

сти, однако не являющиеся первичными ключами, называются

ключами - кандидатами или уникальными ключами. Например,

можно было бы предусмотреть ограничение уникальности для

поля SFAM, тогда его можно было бы считать ключом - кандида-

том, однако это не позволило бы хранить в таблице STUDENTS

однофамильцев.

В SQL можно определить группу из нескольких полей как

уникальную с помощью команды ограничения таблицы

UNIQUE. Объявление группы полей уникальной, принципи-

ально отличается от объявления уникальным одного поля: в

первом случае это - комбинация значений, а во втором - инди-

видуальное значение каждой записи. При этом совсем не обя-

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

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

полей, входящих в комбинацию условия уникальности, само

по себе является уникальным, то большого смысла в таком

ограничении нет.

Например, в таблице успеваемости USP такая ситуация долж-

на быть с парами значений номер студенческого билета - код

206

Часть II. Структурированный язык запросов SQL

предмета - отдельная запись для каждой сдачи студентом того

или иного учебного предмета не должна существовать. Таким

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

командой:

CREATE TABLE USP

(UNUM INTEGER NOT NULL UNIQUE,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL,

PNUM INTEGER NOT NULL,

UNIQUE (SNUM, PNUM));

Обратите внимание на то, что оба поля в ограничении табли-

цы UNIQUE еще к тому же используют ограничение столбца

NOT NULL.

До этого использовалось ограничение UNIQUE или уникаль-

ные индексы в первичных ключах для предписания этим полям

свойства уникальности. Однако SQL поддерживает первичные

ключи непосредственно с ограничением PRIMARE KEY.

С помощью PRIMARY KEY можно ограничивать таблицу или

отдельные его столбцы. Это ограничение работает аналогично

UNIQUE, однако для данной таблицы должен быть только один

первичный ключ. Синтаксис и определение его уникальности те

же, что и для ограничения UNIQUE - первичные ключи не допус-

кают NULL значений, следовательно, любое поле, используемое

в ограничении PRIMARY KEY, должно уже быть объявлено NOT

NULL. Рассмотрим следующий пример:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL,

PNUM INTEGER NOT NULL,

UNIQUE (SNUM, PNUM) ) ;

Как видно, лучше всего помещать ограничение PRIMARY

KEY в поле, которое образовывает уникальный идентификатор

строки, и сохранить ограничение UNIQUE для поля или группы

полей, которые должны быть уникальными логически, а не ис-

пользоваться для идентификации строк.

Глава 2.4. Способы создания баз данных

207

Ограничение PRIMARY KEY может также быть применено для

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

Попробуем предусмотреть для последнего примера другой

первичный ключ:

CREATE TABLE USP

(UNUM INTEGER NOT NULL UNIQUE,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL,

PNUM INTEGER NOT NULL,

PRIMARY KEY (SNUM, PNUM));

В этом случае первичным ключом является комбинация по-

лей SNUM и PNUM. которая должна быть уникальной по при-

чинам, изложенным выше. Очевидно, что ни первое, ни второе

поле в этой таблице нельзя заставить не содержать повторяю-

щиеся значения по отдельности, однако их комбинация есть

уникальная структура. И еще один важный момент - в качестве

первичного ключа лучше применять поля числового (предпоч-

тительно - целого) типа, т.к. использование текстовых полей

может привести к неоднозначным результатам. Например, не

исключена вероятность того, что при использовании в качест-

ве первичного ключа комбинации полей, содержащих фами-

лию, имя и отчество студента, в таблице смогут появиться бо-

лее одного человека, у которых эти данные совпадают. Понят-

но, что первичный ключ, который в одних случаях работает, а

в других - нет, не является приемлемым.

Разумеется, может существовать любое число ограничений,

которые допускается устанавливать для данных, вводимых в таб-

лицы БД. Для этого в SQL предусмотрено ограничение CHECK,

позволяющее поставить условие, в соответствии с которым про-

веряется вводимое в таблицу значение до того, как оно будет со-

хранено. Это ограничение состоит из ключевого слова CHECK и

предиката, который использует указанное поле. Любая попытка

модифицировать или вставить значение поля, которое делает этот

предикат неверным, будет отклонена.

Рассмотрим для примера таблицу USP. Очевидно, что оценка,

полученная студентом, не должна превышать 5 баллов, поэтому

можно при создании этой таблицы предусмотреть соответствую-

щую проверку:

208

Часть II. Структурированный язык запросов SQL

CREATE TABLE USP

(TJNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER CHECK (OCENKA =< 5),

TJDATE DATE,

SNUM INTEGER NOT NULL,

PNUM INTEGER NOT NULL,

UNIQUE (SNUM, PNUM) ) ;

Вообще говоря, можно использовать CHECK для того, чтобы

предопределять допустимое вводимое значение. Например, для

улучшения предыдущего примера, стоит предусмотреть то, что в

поле OCENKA могут быть введены только оценки 1,2, 3,4 или 5.

Тогда следующая команда позволит исключить ввод других зна-

чений и предотвратить возможные опечатки:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER CHECK IN(1, 2, 3, 4, 5),

UDATE DATE,

SNUM INTEGER NOT NULL,

PNUM INTEGER NOT NULL,

UNIQUE (SNUM, PNUM)) ;

Обратите внимание на то, что команда ALTER TABLE позво-

ляет изменять определение таблицы, даже когда она находится в

использовании. Однако изменение или удаление ограничений не

всегда возможно. Более надежным способом порой является соз-

дание новой таблицы с измененным ограничением и заполнение

ее информацией из старой таблицы.

В SQL можно также использовать CHECK в качестве таблич-

ного ограничения. Это полезно в тех случаях, когда возникает

необходимость включить более одного поля в условие. Предпо-

ложим, что отличные оценки могут быть выставлены только до

определенной даты, скажем до 15/06/1999. Тогда воспользуемся

следующей командой:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER,

UDATE DATE,

SNUM INTEGER NOT NULL,

PNUM INTEGER NOT NULL,

Глава 2.4. Способы создания баз данных

209

CHECK (OCENKA < 5

AND UDATE > 15/06/1999));

В такую таблицу теперь можно ввести только оценки ниже 5

баллов для дат, позже 15/06/1999. При этом, как можно видеть,

два различных поля одной и той же строки должны быть прове-

рены для того, чтобы определить - верен предикат или нет. Кроме

того, нельзя использовать ограничение CHECK для того, чтобы

проверить условие связанное сразу с несколькими строками таб-

лицы, например, что оценки по дисциплине не ниже, чем средняя

оценка по всем учебным предметам.

Когда происходит вставка записи в таблицу без указания зна-

чений для каждого поля, SQL должен иметь значение по умолча-

нию для включения его в каждую запись, иначе команда не будет

выполнена. Наиболее распространенным значением по умолча-

нию является NULL. Это значение по умолчанию для любого

столбца, которому не было дано ограничение NOT NULL.

В SQL имеется средство DEFAULT (по умолчанию), указы-

ваемое в команде CREATE TABLE аналогично, как и ограниче-

ние поля. Имейте в виду, что значение DEFAULT не ограничи-

тельного свойства - оно не ограничивает данные, которые можно

вводить, а просто определяет, что должно случиться, если не вве-

дено любое из явных допустимых значений.

Например, если большинство оценок, которое попадает в таб-

лицу USP, это пятерки, то логично указать это в качестве значе-

ния поля OCENKA по умолчанию, т.е.:

CREATE TABLE USP

(UNUM INTEGER NOT NULL PRIMARY KEY,

OCENKA INTEGER CHECK IN(1, 2, 3, 4, 5)

DEFAULT = 5,

UDATE DATE,

SNUM INTEGER NOT NULL,

PNUM INTEGER NOT NULL,

UNIQUE (SNUM, PNUM) ) ;

Конечно, использовать значение по умолчанию - это альтер-

натива для NULL, т.к. неизвестное значение неверно при любом

сравнении (кроме IS NULL), и такие записи исключаются с по-

мощью большинства предикатов. Иногда, когда нужно видеть

пустые значения полей, не обрабатывая их каким-то определен-

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

числовых или пробел для текстовых полей. Различие в данном

210

Часть II. Структурированный язык запросов SQL

случае будет заключаться в том. что теперь такие записи будут

обрабатываться так же. как и любые другие значения. Это озна-

чает, что при выборке данных эти записи будут включены в вы-

вод запроса, что часто является очень полезным.

В этом же поле допускается использовать ограничения

UNIQUE или PRIMARY KEY, но тогда любую вставляемую

строку придется модифицировать прежде, чем будет добавлена

какая-либо другая строка с этой установкой по умолчанию, что

не совсем удобно.

На практике для содержимого отдельных полей существуют и

другие ограничения. Например, поле TFAM таблицы TEACHERS

не должно на самом деле содержать любую произвольную фами-

лию. Например, фамилия Смирнова является "неправильной", т.к.

не соответствует ни одному преподавателю рассматриваемого

ВУЗа. Формально на языке реляционной БД это означает, что

доменом поля TFAM являются не просто какие-либо строковые

значения, и даже не просто фамилии людей, а ряд фамилий лю-

дей, являющихся преподавателями этого ВУЗа.

В стандарте SQL2 определение домена реализовано как часть

определения БД. Согласно этому стандарту, домен является име-

нованной совокупностью значений данных и широко использует-

ся в определении БД. как дополнительный тип данных. Домен

создается с помощью команды CREATE DOMAIN, и, после объ-

явления, на него можно ссылаться, как на тип данных.

Таким образом, нами рассмотрены несколько способов управ-

ления значениями, которые могут быть введены в поля таблицы.

Эти средства позволят более полно использовать возможности

SQL и избегать ошибок при вводе данных.

Создание синонимов

При каждом обращении в команде к базовой таблице или

представлению, для которого пользователь не является владель-

цем, необходимо указывать префикс имени владельца, так чтобы

SQL знал, где ее осуществлять поиск. При выполнении большого

количества запросов и других команд такое указание становится

неудобным, поэтому в данном случае лучше воспользоваться

возможностью создания синонимов для таблиц.

Синоним - это альтернативное имя для таблицы. Пользова-

тель, создавший синоним, становится его собственником, а зна-

Глава 2.4. Способы создания баз данных

211

чит, отпадает необходимость того, чтобы при обращении вводил-

ся пользовательский идентификатор доступа.

Если для пользователя оговорены правила доступа (привиле-

гии) по крайней мере, для одного поля таблицы, то он может соз-

дать синоним. Например, пользователь с идентификатором SHER

может создать синоним для таблицы STUDENTS, принадлежа-

щей пользователю SA следующим образом:

CREATE SYNONYM FIRST FOR SA. STUDENTS;

После этого пользователь может работать с таблицей FIRST

так же, как это делалось при обращении к ней по имени

SA.STUDENTS, т.к. FIRST - это исключительная собственность,

используемая для SHER.

Обратите внимание на тот факт, что префикс (идентификатор)

пользователя - это фактически часть имени любой таблицы. Вся-

кий раз, когда не указывается собственное имя пользователя. SQL

подразумевает, что таблица принадлежит ему и сам заполняет

соответствующую информацию. Отсюда следует, что два одина-

ковых имени таблицы, связанные с различными владельцами,

являются не идентичными и. значит, не приводят к какому-либо

беспорядку в SQL. Другими словами, два пользователя могут

создать две полностью несвязанные таблицы с одинаковыми

именами, но это также будет означать, что один пользователь

может создать представление, основанное на имени другого

пользователя, стоящем после имени таблицы. Поэтому можно

создавать собственные синонимы пользователя, имена которых

будут такими же. что и первоначатьные имена таблиц. Например,

пользователь SHER может создать синоним для таблицы STU-

DENTS, с таким же именем:

CREATE SYNONYM STUDENTS FOR SA. STUDENTS;

После этого с точки зрения SQL теперь имеются два разных

имени одной таблицы: SHER.STUDENTS и SA.STUDENTS. Од-

нако каждый из этих пользователей может ссылаться к этой таб-

лице просто как к STUDENTS, a SQL сам разберется, какая таб-

лица имеется в виду.

Конечно, если планируется иметь таблицу студентов STU-

DENTS, используемую большим числом пользователей, то лучше

так организовать работу с ней, чтобы они ссылались к ней с по-

212

Часть II. Структурированный язык запросов SQL

мощью одного и того же имени. Для того, чтобы создать единое

имя для всех пользователей, создается общий синоним. Напри-

мер, если все пользователи будут вызывать таблицу студентов по

имени STUDENTS, необходимо выполнить следующую команду:

CREATE PUBLIC SYNONYM STUDENTS FOR

STUDENTS;

В основном, общие синонимы создаются владельцами объек-

тов или привилегированными пользователями, например, адми-

нистратором БД. Пользователям, кроме того, должны еще быть

предоставлены привилегии в таблице STUDENTS для того, что-

бы они могли иметь к ней доступ. Это связано с тем, что даже

если имя является общим, сама таблица общей не является.

Общие и другие синонимы могут удаляться командой DROP

SYNONYM. При этом они удаляются их владельцами, за исклю-

чением общих синонимов - для их удаления, как правило, требу-

ются привилегии администратора БД.

Например, для удаления синонима FIRST можно воспользо-

ваться командой:

DROP SYNONYM FIRST;

Разумеется, сама таблица STUDENTS останется без каких-

либо изменений.

Архитектура баз данных

Как уже было сказано, в SQL команды CREATE, DROP и

ALTER образуют основу языка определения данных (DDL). Опе-

раторы с этими командами используются в реляционных СУБД

для управления таблицами, индексами и представлениями. Кроме

того, эти команды могут быть использованы для манипуляции

другими дополнительными объектами БД (см. табл. 2.6).

Таблица 2.6

Операторы DDL и объекты, управляемые ими

Операторы DDL

CREATE/DROP/ALTER

TABLE

Управляемый объект

таблица

Глава 2.4. Способы создания баз данных

213

Операторы DDL

CREATE/DROP/ALTER VIEW

CREATE/DROP/ALTER

INDEX

CREATE/DROP SYNONYM

CREATE/DROP/ALTER

DATABASE

CREATE/DROP DEFAULT

CREATE/DROP PROCEDURE

CREATE/DROP RULE

CREATE/DROP TRIGGER

Управляемый объект

представление

индекс

синоним

база данных

значение поля по умолчанию

хранимая процедура

правило соблюдения целост-

ности поля

хранимый триггер

В соответствии со стандартом SQL1, структура БД должна

быть такой, чтобы у каждого пользователя была принадлежащая

ему совокупность таблиц. Несмотря на то, что в различных СУБД

используется одинаковая логическая структура отдельной БД,

наблюдается большое разнообразие в том, как организована и

структурирована вся совокупность данных в конкретной опера-

ционной системе.

В одних СУБД все данные хранятся в одной общей БД, в дру-

гих - в нескольких БД, каждой из которых присваивается имя. А в

некоторых СУБД информация хранится в нескольких БД, органи-

зованных в виде системы каталогов. Такое разнообразие не ока-

зывает какого-либо влияния на структуру языка SQL, используе-

мого для доступа к информации, хранимой в БД, однако оно

влияет на способ организации данных, а также на способ перво-

начального обращения к БД. Т.е. при возникновении необходи-

мости обращения к той или иной таблице, необходимо сообщить

СУБД, с какой именно из них нужно работать.

Рассмотрим однобазовую архитектуру БД, при которой СУБД

содержит одно общее хранилище данных (см. рис. 2.1).

При таком подходе все данные о студентах, преподавателях,

учебных предметах и успеваемости хранятся в таблицах внутри

одной БД. Главные таблицы собраны вместе и принадлежат од-

ному пользователю (в нашем случае - SA). Преимуществом такой

214

Часть II. Структурированный язык запросов SQL

архитектуры является то, что таблицы из разных приложений

могут иметь ссылки друг на друга. Например, таблица OTL, хра-

нящая данные об отличниках, содержит внешний ключ к таблице

STUDENTS, и СУБД может использовать эту связь для манипу-

ляции данными. Имея соответствующие привилегии, пользова-

тель может выполнять запросы, объединяющие данные из разных

таблиц.

1 Г"

STUDENTS

PREDMET

"1 Г"

TEACHERS

USP

SHER

COURSE

OTL

вооко

Рис. 2.1. Схема однобазовой архитектуры БД

Такую структуру хранения используют, например, в системах

DB2 и Oracle. Недостатком такой архитектуры является то, что по

мере добавления новых элементов БД приобретает огромные

размеры. Проблемы, связанные с управлением БД больших раз-

меров, очевидны: резервное копирование, восстановление дан-

ных, анализ эффективности работы требуют, как правило, посто-

янного внимания администратора БД.

При однобазовой архитектуре доступ к данным осуществляет-

ся достаточно просто, т.к. БД одна и не требуется делать какой-

либо выбор. При этом доступ к данным реализуется в общем, а не

к какой-нибудь конкретной БД.

Глава 2.4. Способы создания баз данных

215

При многобазовой архитектуре (см. рис. 2.2) каждой БД при-

сваивается уникальное имя. Такая архитектура применяется, на-

пример, в SQL Server и Ingres. Как видно из схемы, каждая БД в

основном предназначена для отдельной прикладной задачи. При

добавлении новой задачи, создается новая БД. Основное пре-

имущество многобазовой архитектуры по сравнению с однобазо-

вой заключается в том, что проблема управления разделяется на

более мелкие части, а значит, легче решаемые. Пользователь,

ответственный за конкретную прикладную задачу, может одно-

временно быть администратором своей собственной БД, меньше

беспокоясь об общей координации всей системы. Если появляет-

ся необходимость добавить новую задачу, это можно сделать в

своей БД, не нарушая работу других частей системы.

STUDENTS

PREDMET

USP

TEACHERS

БД EDUCATION

OTL

SHER

COURSE

БД PREMIA

Рис. 2.2. Схема многобазовой архитектуры БД

Главным недостатком многобазовой архитектуры является то,

что не исключена вероятность разобщенности друг от друга ин-

дивидуальных БД. Часто таблица одной БД не может содержать

внешний ключ для таблицы другой, а значит - СУБД не сможет

выполнять межбазовые запросы.

216

Часть II. Структурированный.язык запросов SQL

Кроме того, если в СУБД используется многобазовая архитек-

тура и поддерживаются запросы между БД. то в ней должны быть

расширены правила именования нолей и таблиц, как это было

оговорено выше. Наконец, при многобазовой архитектуре не-

сколько усложняется доступ к данным, т.к. в СУБД необходимо

хранить и передавать информацию о том, с какой именно БД идет

работа. Поэтому для получения доступа к данным СУБД просит

пользователя ввести имя БД, имя пользователя и пароль, и только

после этого доступ к данным разрешается.

При хранении данных с помощью каталоговой архитектуры,

БД организуется в виде древовидной структуры (см. рис. 2.3).

Здесь, так же, как и в многобазовой архитектуре, для каждой

прикладной задачи создается собственная БД, имеющая собст-

венное имя, причем в разных каталогах имена могут совпадать.

Каталог верхнего уровня

Каталог

EDUCATION

STUDENTS

TEACHERS

PREDMET

USP

Каталог PREMIA

OTL

Каталог DISCiPL

COURSE

Рис. 2.З. Схема каталоговой архитектуры БД

Главное достоинство каталоговой архитектуры - это ее гиб-

кость. Такая архитектура особенно полезна для инженерных и

конструкторских программ, т.к. традиционно с ними работает

много опытных пользователей, которым может потребоваться

несколько БД для структурированного хранения своей информа-

Глава 2.4. Способы создания баз данных

217

ции. Недостатки каталоговой архитектуры аналогичны многоба-

зовой, а, кроме того, СУБД, как правило, не имеет информации

обо всех созданных БД по всей структуре каталогов К тому же

здесь нет главной БД, отслеживающей все остальные, что делает

очень трудным централизованное администрирование этой сис-

темы.

Каталоговая архитектура еще больше усложняет доступ к

данным, поскольку для этого необходимо указать не только имя

БД, но и ее местоположение в иерархической структуре катало-

гов. Однако СУБД с каталоговой архитектурой, обеспечивают

доступ к нескольким БД одновременно.

Из вышесказанного следует, что существует огромное разно-

образие в том, как в различных СУБД организованы БД и каким

образом к данным осуществляется доступ. Эта область языка

SQL является одной из самых нестандартных и. порой, такая не-

согласованность между разными СУБД делает невозможным или

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

Таким образом, чаще всего таблицы и другие объекты данных

сохраняются в БД и находятся там связанными с определенными

пользователями, которые ими владеют. В некотором смысле,

можно сказать, что они сохраняются в именной области пользо-

вателя, хотя это ви коим вбразом не отражает их физического

расположения, но зато обеспечивает строгую логическую конст-

рукцию хранения.

Однако не надо забывать и тот факт, что объекты данных на

устройствах хранения занимают некоторый объем физической

памяти и ее количество, которое может использоваться опреде-

ленным объектом или пользователем в данное время, имеет свой

предел. Действительно, никакой компьютер не может иметь пря-

мого доступа к бесконечному числу аппаратных средств для хра-

нения данных. Поэтому в больших SQL системах БД разделяется

на области, которые принято называть областями БД (DBS) или

разделами.

Это области хранимой информации, которые размещены осо-

бым образом так, чтобы данные внутри них находились близко

друг к другу для выполнения команд, т.е. СУБД не должна рас-

полагать информацию где-то далеко. Системы, которые исполь-

зуют области БД, позволяют пользователю обрабатывать их с

помощью команд SQL как объекты.

Областями БД манипулируют с помощью команд CREATE

DBSPACE (создать), ACQUIRE DBSPACE (получить) или

CREATE TABLESPACE (создать табличную область), в зависи-

218

Часть II, Структурированный язык запросов SQL

мости от используемой СУБД. Одна область может вмещать в

себя любое число пользователей, а конкретный пользователь мо-

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

таблицы для пользователя может быть передана по всей БД, но

чаще распространяется только на конкретную область.

Например, для создания области хранения БД EDUCATION с

нашими примерами, можно воспользоваться следующей командой:

CREATE DBSPACE EDUCATION

(РСТINDEX 15,

PCTFREE 20) ;

Здесь параметр PCTINDEX устанавливает, какой процент об-

ласти БД должен быть оставлен, чтобы сохранять в нем индексы

таблиц, а ключевое слово PCTFREE задает процент области, ко-

торый резервируется для того, чтобы позволить таблицам расши-

рять величины своих записей, например, при увеличении коман-

дой ALTER TABLE размера полей.

Кстати говоря, большинство СУБД, исходя из имеющегося

аппаратного обеспечения, удачно подбирают параметры области

БД автоматически, поэтому достаточно часто при создании ис-

пользуют параметры ло умолчанию. Полезным свойством облас-

ти является возможность установить определенное ограничение

размера БД, или, наоборот, разрешить ей расти в размерах неог-

раниченно вместе со всеми таблицами.

При разделении используемой БД на области необходимо

иметь в виду типы операций, которые будут часто выполняться.

При этом желательно, чтобы таблицы, которые будут часто объе-

диняться или которые имеют внешние ключи, находились вместе

в одной области.

Глава 2.5

Специальные аспекты работы с базами

данных

Контроль целостности данных с

использованием триггеров

Выше уже не раз поднимались вопросы о связях, которые су-

ществуют между некоторыми полями таблиц в БД. Например,

Глава 2.5. Специальные аспекты работы с базами данных 219

поле SNUM таблицы STUDENTS соответствует полю SNUM в

таблице USP, а поле PNUM таблицы PREDMET - соответственно

PNUM в той же таблице USP. Такую связь можно назвать спра-

вочной целостностью - она необходима для того, чтобы избежать

избыточности информации в таблицах. Рассмотрим использова-

ние этой возможности более подробно.

Напомним: когда все значения в одном поле таблицы пред-

ставлены в поле другой, обычно говорят: первое поле ссылается

на второе, и это указывает на прямую связь между значениями

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

в таблице STUDENTS имеет поле SNUM, которое указывает на

оценку, хранимую в таблице успеваемости USP.

Когда одно поле в таблице ссылается на другое, оно назы-

вается внешним ключом, а поле, на которое оно ссылается ро-

дительским ключом. Таким образом, поле SNUM таблицы

STUDENTS является родительским ключом, а поле SNUM в

таблице USP - внешним ключом. Имена внешнего ключа и ро-

дительского ключа не обязательно должны быть одинаковыми,

однако соблюдение условия идентичности имен делает соеди-

нение более понятным.

Кроме того, внешний ключ не обязательно должен состоять

только из одного поля, хотя ключи, состоящие из одного поля,

встречаются чаще всего. Подобно первичному ключу, внешний

ключ может иметь любое число полей, которые все вместе обра-

батываются как единое целое. Внешний ключ и родительский

ключ, на который он ссылается, обязательно должны иметь оди-

наковый тип поля, и, при использовании нескольких полей, нахо-

диться в одинаковом порядке.

Когда поле является внешним ключом, очевидно, что оно

определенным образом связано с таблицей, на которую ссыла-

ется. Фактически происходит следующее: каждому значению в

поле, которое является внешним ключом, непосредственно

соответствует значение в другом поле - родительском ключе.

При этом каждое значение внешнего ключа должно однознач-

но ссылаться к одному значению родительского ключа, тогда

можно говорить о том, что система находится в состоянии

справочной целостности.

Если это не так, то возникает неоднозначная ситуация. На-

пример, если в таблице STUDENTS в поле SNUM появятся два

одинаковых значения (скажем, 3412), то при получении результа-

тов запроса о полученных оценках