Компьютеры
и программирование

СОЗДАНИЕ БАЗЫ ДАННЫХ SQL И ТАБЛИЦ БАЗЫ ДАННЫХ

Создание базы данных обычно начинается с построения модели "сущность-связь". Например, если наша база данных моделирует сеть аптек, то в ней есть такие сущности, как "Аптека" (таблица Pharmacy в нашем примере создания базы данных), "Препарат" (таблица Preparation в нашем примере создания базы данных), "Доступность (препаратов в аптеке)" (таблица Availability в нашем примере создания базы данных), "Клиент" (таблица Client в нашем примере создания базы данных) и другие, которые здесь подробно и разберём.

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

Для увеличения рисунка можно нажать на него левой кнопкой мыши.

При создании базы данных, в которой таблицы связаны между собой, важно позаботиться о целостности данных. Это означает, например, что если если удалить препарат из таблицы Preparation, то должны удалиться все записи этого препарата в таблице Availability. Ещё пример ограничения целостности: нужно установить запрет на удаление названия группы препарата из таблицы Group, если существует хотя бы один препарат этой группы. Особый случай составляет изменение данных в одной таблице, когда производятся действия с данными в другой таблице. Об этом поговорим в конце статьи.

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

Теперь приступим к созданию команд. Первая наша команда SQL создаёт базу данных PHARMNETWORK:

Код SQL

CREATE DATABASE PHARMNETWORK

Описание таблицы PHARMACY (Аптека):

Имя поляТип данныхОписание
PH_IDsmallintИдентификационный номер аптеки
Addressvarchar(40)Адрес аптеки

Пишем команду, которая создаёт таблицу PHARMACY (Аптека), значения первичного ключа PH_ID генерируются автоматически от 1 с шагом 1, вносится проверка на то, чтобы значения атрибута Address в этой таблице были уникальными:

Код SQL

CREATE TABLE PHARMACY(PH_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Address varchar(40) NOT NULL, PRIMARY KEY(PH_ID), CONSTRAINT PH_UNIQ UNIQUE(Address))

Описание таблицы GROUP (Группа препаратов):

Имя поляТип данныхОписание
GR_IDsmallintИдентификационный номер группы препаратов
Namevarchar(40)Название группы препаратов

Пишем команду, которая создаёт таблицу Group (Группа препаратов), значения первичного ключа GR_ID генерируются автоматически от 1 с шагом 1, проводится проверка уникальности наименования группы (для этого используется ключевое слово CONSTRAINT):

Код SQL

CREATE TABLE GROUP(GR_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Name varchar(40) NOT NULL, PRIMARY KEY(GR_ID), CONSTRAINT GR_UNIQ UNIQUE(Name))

Описание таблицы PREPARATION (Препарат):

Имя поляТип данныхОписание
PR_IDsmallintИдентификационный номер препарата
GR_IDsmallintИдентификационный номер группы препарата
Namevarchar(40)Название препарата

Команда, которая создаёт таблицу PREPARATION, значения первичного ключа PR_ID генерируются автоматически от 1 с шагом 1, определяется, что значения внешнего ключа GR_ID (Группа препаратов) не могут принимать значение NULL, определена проверка уникальности значений атрибута Name:

Код SQL

CREATE TABLE PREPARATION(PR_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Name varchar(40) NOT NULL, GR_ID int NOT NULL, PRIMARY KEY(PR_ID), constraint PR_UNIQ UNIQUE(Name))

Дальше небольшая хитрость. Чтобы не писать громоздкий запрос для выполнения ограничений целостности, можно изменить уже созданную таблицу и добавить требуемое условие. Делается это с помощью выражения alter table. Написанная таким образом команда устанавливает запрет на удаление группы препаратов из таблицы Group, если существует хотя бы один препарат этой группы, что определяется записью в таблице Preparation:

Код SQL

alter table Preparation add constraint GR_ID foreign key(GR_ID) references Group on delete restrict

Теперь самое время создать таблицу AVAILABILITY (Доступность или Наличие препарата в аптеке). Её описание:

Имя поляТип данныхОписание
A_IDsmallintИдентификационный номер записи о доступности
PH_IDsmallintИдентификационный номер аптеки
PR_IDsmallintИдентификационный номер препарата
QuantityintКоличество доступного препарата
DateStartvarchar(20)Дата начала работы аптеки с данным препаратом
DateEndvarchar(20)Дата окончания работы аптеки с данным препаратом
Martvarchar(3)Выставлен ли препарат на витрину

Пишем команду, которая создаёт таблицу AVAILABILITY. Определяются даты начала (не может быть NULL) и окончания (по умолчанию NULL).

Код SQL

CREATE TABLE AVAILABILITY(A_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), PH_ID INT NOT NULL, PR_ID INT NOT NULL, DateStart varchar(20) NOT NULL, DateEnd varchar(20) DEFAULT NULL, QUANTITY INT NOT NULL, MART varchar(3) DEFAULT NULL, PRIMARY KEY(A_ID), CONSTRAINT AVA_UNIQ UNIQUE(PH_ID, PR_ID))

И вновь нам пригодилось выражение ALTER TABLE. Нужно модифицировать таблицу AVAILABILITY и определить, что в одной и той же записи значение атрибута DateStart таблицы не должено быть меньше значения DateEnd.

Код SQL

ALTER TABLE AVAILABILITY ADD CONSTRAINT CHECK_DATA CHECK(DateStart < DateEnd)

Вновь изменяем таблицу AVAILABILITY и определяем, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице AVAILABILITY.

Код SQL

alter table AVAILABILITY add constraint PR_ID foreign key(PR_ID) references PREPARATION on delete cascade

Теперь определим, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице AVAILABILITY:

Код SQL

alter table AVAILABILITY add constraint PH_ID foreign key(PH_ID) references PHARMACY on delete cascade

Создаём таблицу DEFICIT (Дефицит препарата в аптеке, то есть, неудовлетворённый запрос). Её описание:

Имя поляТип данныхОписание
D_IDsmallintИдентификационный номер записи о дефиците
PH_IDsmallintИдентификационный номер аптеки
PR_IDsmallintИдентификационный номер препарата
Solutionvarchar(40)Решение проблемы дефицита
DateStartvarchar(20)Дата появления проблемы
DateEndvarchar(20)Дата решения проблемы

Пишем команду, которая создаёт таблицу DEFICIT:

Код SQL

CREATE TABLE DEFICIT(D_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), PH_ID INT NOT NULL, PR_ID INT NOT NULL, Solution varchar(40) NOT NULL, DateStart varchar(20) NOT NULL, DateEnd varchar(20) DEFAULT NULL)

С помощью уже знакомого ключевого слова ALTER TABLE изменяем таблицу DEFICIT и определяем, что в одной и той же записи значение атрибута DateStart таблицы не должено быть меньше значения DateEnd:

Код SQL

ALTER TABLE DEFICIT ADD CONSTRAINT CHECK_DATA CHECK(DateStart < DateEnd)

Следующая команда модифицирует таблицу DEFICIT и определяет, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице DEFICIT:

Код SQL

alter table DEFICIT add constraint PR_ID foreign key(PR_ID) references PREPARATION on delete cascade

Вновь модифицируем таблицу DEFICIT и определяем, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице DEFICIT:

Код SQL

alter table DEFICIT add constraint PH_ID foreign key(PH_ID) references PHARMACY on delete cascade

Осталось немного. Мы уже дошли до команды, которая создаёт таблицу Employee (Сотрудник). Её описание:

Имя поляТип данныхОписание
E_IDsmallintИдентификационный номер сотрудника
PH_IDsmallintИдентификационный номер аптеки
FNamevarchar(40)Имя сотрудника
LNamevarchar(40)Фамилия сотрудника
Postvarchar(40)Должность

Пишем команду, которая создаёт таблицу Employee (Сотрудник), с первичным ключом, генерируемым по тем же правилам, что и первичные ключи предыдущих таблиц, в которых они существуют. Внешним ключом PH_ID Сотрудник связан с PHARMACY (Аптекой).:

Код SQL

CREATE TABLE EMPLOYEE(E_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), F_Name varchar(40) NOT NULL, L_Name varchar(40) NOT NULL, POST varchar(40) NOT NULL, PH_ID INT NOT NULL, PRIMARY KEY(E_ID))

Устанавливаем запрет на удаление аптеки из таблицы Pharmacy, если существует хотя бы один сотрудник этой аптеки, что определяется записью в таблице Employee:

Код SQL

alter table Employee add constraint PH_ID foreign key(PH_ID) references Pharmacy on delete restrict

Очередь дошла до создании таблицы CLIENT (Клиент). Её описание:

Имя поляТип данныхОписание
C_IDsmallintИдентификационный номер клиента
FNamevarchar(40)Имя клиента
LNamevarchar(40)Фамилия клиента
DateRegvarchar(20)Дата регистрации

Пишем команду, создающую таблицу CLIENT (Клиент), в отношении первичного ключа которого справедливо предыдущее описание. Особенность этой таблицы в том, что её атрибуты F_Name и L_Name имеют по умолчанию значение NULL. Это связано с тем, что клиенты могут быть как зарегистрированными, так и незарегистрированными. У последних значения имени и фамилии как раз и будут неопределёнными (то есть NULL):

Код SQL

CREATE TABLE CLIENT(C_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), FName varchar(40) DEFAULT NULL, LName varchar(40) DEFAULT NULL, DateReg varchar(20), PRIMARY KEY(C_ID))

Предпоследняя таблица в нашей базе данных - таблица BASKET (Корзина покупок). Её описание:

Имя поляТип данныхОписание
BS_IDsmallintИдентификационный номер корзины покупок
E_IDsmallintИдентификационный номер сотрудника, оформившего корзину
C_IDsmallintИдентификационный номер клиента

Пишем команду, создающую таблицу BASKET (Корзина покупок), так же с уникальным и инкрементируемым первичным ключом и связанную внешним ключами C_ID и E_ID с Клиентом и Сотрудником соответственно:

Код SQL

CREATE TABLE BASKET(BS_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), C_ID INT NOT NULL, E_ID INT NOT NULL, PRIMARY KEY(BS_ID))

И, наконец, последняя таблица в нашей базе данных - таблица BUYING (покупка). Её описание:

Имя поляТип данныхОписание
B_IDsmallintИдентификационный номер покупки
PH_IDsmallintИдентификационный номер аптеки
PR_IDsmallintИдентификационный номер препарата
BS_IDvarchar(40)Идентификационный номер корзины покупок
Pricevarchar(20)Цена
Datevarchar(20)Дата

Пишем команду, создающую таблицу BUYING (покупка), так же с уникальным и инкрементируемым первичным ключом и связанную внешними ключами BS_ID, PH_ID, PR_ID с Корзиной покупок, Аптекой и Препаратом соответственно:

Код SQL

CREATE TABLE BUYING(B_ID smallint NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), BS_ID INT NOT NULL, PH_ID INT NOT NULL, PR_ID INT NOT NULL, DateB varchar(20) NOT NULL, Price Double NOT NULL, PRIMARY KEY(B_ID))

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

И действительно, есть декларативное, в частности, программное средство решение обозначенной выше задачи уменьшения значения количества препарата. А именно: в условии добавления соответствующего препарата в таблицу BUYING (Покупка) пишется функция с запросом с ключевым словом UPDATE на замену значения количества этого препарата на единицу меньше в той же аптеке. И таблица BUYING, и таблица AVAILABILITY имеют внешний ключ PH_ID - идентификатор определённой аптеки.

На этом многогранная тема создания баз данных прерывается...

Поделиться с друзьями

Ещё в разделе Реляционные базы данных: реляционная алгебра