Язык определения данных

Язык определения  данных

Для создания и изменения  структуры БД предназначены SQL-запросы, называемые языком определения данных, или DDL (Data Definition Language). С помощью DDL-запросов можно выполнить следующее:

•  определить структуру нового домена и создать его;

•  определить структуру новой таблицы и создать ее;

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

•  определить виртуальную таблицу (представление, курсор);

• создать индексы для  ускорения доступа к таблицам.

Ядро языка определения  данных в Firebird образуют следующие четыре SQL-запроса:

•  CREATE (создать), позволяющий определить и создать объект БД;

•  DROP (удалить), служащий для удаления существующего объекта БД;

•  ALTER (изменить), с помощью которого можно изменить определение объекта БД;

•  RECREATE (заново создать), с помощью которого можно заново создать объект со старым именем.

Пустая БД в Firebird представляет собой файл, который не содержит таблиц пользователя, но содержит пустые системные таблицы. Физическая структура  пустой БД (файл) создается сервером Firebird при выполнении скрипта, содержащего  команду CREATE DATABASE, или в интерактивном  режиме утилиты IBExpert. Для подключения к существующей БД можно воспользоваться соответствующим пунктом меню утилиты IBExpert или использовать скрипт, содержащий команду CONNECT.

Таким образом, чтобы создать  БД в Firebird необходимо:

•  Создать пустой файл БД и выполнить команду соединения с базой.

•  Создать необходимые домены.

•  Создать пользовательские таблицы.

•  Заполнить данными созданные таблицы пользователя.

Команды CREATE DATABASE и CONNECT будут  рассмотрены позднее при изучении SQL-скриптов.

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

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

4.1. Домены

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

Доменом называется именованное множество скалярных значений одного типа. Например, домен TMonth (месяц) в учебной базе данных - это множество всех возможных номеров месяцев (от 1 до 12). Цель доменов в SQL - обеспечить возможность один раз определить элементарную спецификацию типа данных, а затем использовать ее одновременно для нескольких столбцов в нескольких базовых таблицах.

То есть домен - это тип данных (как это понимается в современных языках программирования). Например, в языке программирования Pascal допустимы следующие выражения:

type TDay = (Sun, Mon, Tue, Wed, Thu, Fri, Sat);

var Today : TDay;.

Здесь имеется определенный пользователем тип TDay (имеющий в  точности семь допустимых значений) и  переменная Today, принадлежащая этому  типу данных (а значит, и ограниченная этими семью значениями). Эта ситуация аналогична ситуации в реляционной  БД, когда имеются домен, названный TDay , и атрибут, названный Today.

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

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

CREATE DOMAIN имя_домена [AS] <тип_данных>

[DEFAULT { литерал | NULL | USER}]

[NOT NULL] [CHECK ( <ограничение_домена>)];,

где

имя_домена - имя создаваемого домена;

<тип_данных> - тип данных, представленный в табл. 2.5;

DEFAULT - ключевое слово,  определяющее значение по умолчанию,  применяемое к каждому столбцу  как: 

•  литерал - константа строкового, числового типа или типа дата/время (соответствует типу данных домена);

•  NULL - значение NULL;

•  USER - контекстная переменная, возвращающая имя пользователя, подключенного к БД.

Следующие предложения задают набор ограничений целостности  к каждому столбцу, определенному  на этом домене:

- NOT NULL - чтобы столбец  не имел NULL-значения. Этот атрибут  используется при определении  домена, если требуется, чтобы  все столбцы, создаваемые на  основе этого домена, имели непустое  значение. Следует помнить, что  переопределить атрибут NOT NULL, заданный  для домена, нельзя. Часто неизвестно, действительно ли потребуется,  чтобы все столбцы, определяемые  на домене, имели NOT NULL значения. Также  часто требуется определять внешний  ключ на домене без условия  NOT NULL. Поэтому предпочтительнее  добавлять атрибут NOT NULL при определении  столбцов (будет рассмотрено далее);

- CHECK - определяет список  ограничений на значение (VALUE) в  соответствующем столбце. Ограничение  домена фактически повторяет  синтаксис условия поиска в  предложении WHERE для однотабличных  запросов (используются простое  сравнение, проверка на принадлежность  диапазону, на членство во множестве  и т.д.) с той лишь разницей, что вместо точного указания  проверяемого значения используется  слово VALUE.

Ограничение домена может  быть одним из следующих:

<ограничение_цомена>::=[NOT] <ограничение_домена1> [[AND|OR][NOT] <ограничение_цомена2>]...

где

<ограничение_цомена>::=

{VALUE <операция_сравнения> <значение>

| VALUE [NOT] BETWEEN <значение1> AND <значение2>

| VALUE [NOT] LIKE 'шаблон' [ ESCAPE 'символ_пропуска' ]

| VALUE [NOT] CONTAINING <значение>

| VALUE [NOT] STARTING [ WITH ] <значение>

| VALUE [NOT] IN (<значение1> [ , <значение2> ...])

| VALUE IS [NOT] NULL

| VALUE IS [NOT] DISTINCT FROM <значение>};

<значение> ::= {столбец | константа | <выражение> | функция}.

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

Например, для определения  домена с именем Telephone, описывающего номер телефона абонента (по умолчанию '999999' и не может быть значение '100000') и имеющего тип VARCHAR (10), следует применить  следующий запрос:

CREATE DOMAIN Telephone AS VARCHAR(10)

DEFAULT '999999'

CHECK (VALUE <> '100000');.

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

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

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

CREATE DOMAIN Boolean AS SMALLINT

CHECK (VALUE IN (0, 1));

CREATE DOMAIN Money AS NUMERIC(15,2);

CREATE DOMAIN PKField AS INTEGER;

CREATE DOMAIN TMonth AS SMALLINT

CHECK (VALUE BETWEEN 1 AND 12);

CREATE DOMAIN TYear AS SMALLINT

CHECK (VALUE BETWEEN 1990 AND 2100);.

Просмотреть список доменов, определенных в БД, и структуру  каждого из них можно в IBExpert с  помощью инспектора объектов.

Определение существующего  домена можно изменить с помощью запроса ALTER DOMAIN. Этот запрос позволяет:

- удалить существующее  и определить новое значение  по умолчанию (заменяя при этом  старое значение, если оно было  указано);

- удалить существующее  и ввести новое ограничение  целостности. Синтаксис запроса  ALTER DOMAIN имеет следующий формат:

ALTER DOMAIN имя_домена {[SET DEFAULT {литерал  | NULL | USER}]| [DROP DEFAULT]| [ADD [CONSTRAINT] CHECK (<ограничение_домена>)] | [DROP CONSTRAINT]};,

где

SET DEFAULT - указывает для  существующего домена значение  по умолчанию; 

DROP DEFAULT - для существующего  домена удаляет значение по  умолчанию; 

ADD [CONSTRAINT] CHECK - добавляет  ограничение для существующего  домена;

DROP CONSTRAINT - удаляет CHECK ограничение  из определения домена. Например, созданный домен Telephone можно изменить, удалив ограничение, следующим  запросом:

ALTER DOMAIN Telephone DROP CONSTRAINT;

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

ALTER DOMAIN Telephone SET DEFAULT '111111';.

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

DROP DOMAIN имя_домена;.

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

Например, если попытаться на учебной БД удалить домен BOOLEAN:

DROP DOMAIN BOOLEAN;,

то будет выдано сообщение  об ошибке следующего вида: "column BOOLEAN is used in table REQUEST (local name EXECUTED) and cannot be dropped" [столбец BOOLEAN используется в таблице REQUEST (локальное имя EXECUTED) и не может  быть удален].

4.2. Создание, изменение  и удаление базовых таблиц  БД

4.2.1. Создание таблицы

В реляционной БД наиболее важным элементом ее структуры является таблица. Перед тем как перейти  к созданию таблиц, необходимо выполнить  проектирование базы данных и нормализацию таблиц.

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

Как будет называться таблица?

Как будут называться столбцы (поля) таблицы?

Какие типы данных будут  закреплены за каждым столбцом?

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

Из каких столбцов будет  состоять первичный ключ?

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

CREATE TABLE базовая_таблица (<определение_столбца1>

[, <определение_столбца2> ..... ]

[,<тип_ограничения> ..... ]);.

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

Конструкция <определение_столбца> имеет следующий вид:

<определение_столбца> ::= столбец {<тип_данных> | COMPUTED [BY] (<выражение>) | имя_домена}[NOT NULL] [DEFAULT {литерал | NULL | USER}] [<ограничение_столбца>].

Все столбцы таблицы должны иметь уникальные имена. Тип данных столбца может задаваться непосредственно  указанием имени типа или указанием  имени домена. С точки зрения теории БД использование доменов при  определении типа столбца является необходимым. При этом домены должны создаваться до создания таблиц.

Конструкция

COMPUTED [BY] (<выражение>)

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

Например, если требуется  поместить в столбец summa значение суммы столбцов first и second, описание типа данных столбца summa должно выглядеть  так:

summa COMPUTED BY (first + second).

В результате тип данных столбца summa будет автоматически  приведен к типу данных столбцов first и second по правилу преобразования типов.

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

full_name COMPUTED BY (name || ',' || second_name).

Столбец full_name будет иметь  строковый тип данных с длиной, равной сумме длин строк second_name и name.

Очевидно, что определенные таким образом столбцы summa и full_name будут доступны только для чтения (read - only column), т.е. в эти столбцы  нельзя добавлять значения, а следовательно, они не должны упоминаться в списке столбцов запросов INSERT и UPDATE.

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

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

Например, необходимо создать  таблицу Days, состоящую из пяти столбцов: number типа INTEGER, dat типа DATE, event типа VARCHAR (20), usr типа CHAR (10) и tel, определенного на созданном домене Telephone. При этом столбец number не должен содержать NULL-значений, столбец dat по умолчанию должен содержать 1 сентября 1996 года, столбец event по умолчанию  должен содержать NULL-значения, столбец usr по умолчанию должен содержать  имя пользователя, подключенного  к БД, а столбец tel должен содержать  значение по умолчанию из домена Telephone. Запрос для создания таблицы Days примет следующий вид:

CREATE TABLE Days (number INTEGER NOT NULL, dat DATE DEFAULT '09/01/1996', event VARCHAR(20) DEFAULT NULL, usr CHAR(10) DEFAULT USER, tel Telephone);.

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

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

•  на определенный столбец;

•  на всю таблицу.

4.2.2. Определение  ограничений столбца

При ограничении столбца соответствующее ограничение объявляется индивидуально для каждого столбца непосредственно после определения имени и типа столбца. При этом используется конструкция <ограничение_столбца>.