Создание базы данных в InterBase

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

Реляционная база данных – это система таблиц, связанных между собой. Вот такое простое определение :)

Сотрудник компании IBM Эдгар Кодд в 1970 году предложил реляционную модель баз данных. За базовую структуру данных в реляционной модели принято отношение (relation).

Но Питер Чен был крайне не согласен с Эдгаром и поэтому в 1976 году предложил интерпретировать реляционные структуры данных (отношения), как набор сущностей и связей между ними.

А еще во времена СССР и даже раньше писали учебники для школьников и решили, что отношения и сущности проще будет называть таблицами, а на связи между ними забили :)

Вот что получилось в результате длинного пути развития человечества, помни: все эти слова синонимы!

В теории баз данных есть несколько непонятных слов, которые я сейчас тебе объясню.

Первичный ключ таблицы – это такое уникальное поле (или уникальная группа полей), используемое для идентификации каждой строки. Я рекомендую для первичного ключа выделять специальное поле (числовое!) и не использовать группу полей.

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

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

Если что-то не понятно, сильно не беспокойся – на практике все достаточно просто!

Представь, что к тебе пришел умный дядька и просит тебя написать ему программу, которая организует прозрачность работы автомойки (на самом деле компания не имеет принципиального значение, ну у нас пусть будет автомойка :). А что такое прозрачность? Прозрачность в данном контексте означает то, что есть система, которая хранит все операции автомойки и позволяет следить за бизнесом.

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

По поводу двух последних таблиц: в SERVICE мы заносим все услуги предоставляемые автомойкой, а TSERVICE необходима для связи справочника УСЛУГИ (SERVICE) с таблицей ОПЕРАЦИИ (OPERATION). Дело в том, что клиент может заказать несколько услуг, и чтобы они все принадлежали одной операции нужна таблица TSERVICE.

Если что-то не понятно, перечитай выше изложенный текст. Если ты уже это сделал и не раз, тогда читай дальше – скоро все встанет на свои места!

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

Приведенная диаграмма выполнена в нотации IDEF1X (Integration Definition for Function Modeling), разработанной Т.Рэмей.

В этой нотации:

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

- связи таблиц типа «один-ко-многим» изображают линиями, которые на одном конце заканчиваются кружком. Связь «один-ко-многим» на примере таблиц «CLIENT» и «CAR» означает, что у одного клиента может быть несколько автомобилей, т.е. запись о данном клиенте в «CLIENT» одна, а в «CAR» может быть несколько записей, относящихся к данному клиенту!

- связи таблиц типа «один-к-одному» изображают линиями. Это жесткие связи таблиц, то есть любой одной записи из первой таблице соответствует одна определенная запись из второй. Как ты уже заметил, в нашей диаграмме таких связей нет, они могли быть, если б мы разбили таблицу «PERSONAL» на две, выделив в отдельную таблицу список дат рождения, но это крайне дурацкая затея :) а потому она отметается!
Наша диаграмма еще с 1976 года стала называться ER-диаграммой (Entity-Relationship, сущность-связь) – стандартный способ детализации хранилищ данных.

С постановкой задачи разобрались! Теперь за практику!

Я решил использовать InterBase 7.5. Именно 7.5! Потому что люблю использовать последние версии программных продуктов, так как в новых версиях появляется много интересных фишек и исправлена добрая куча ошибок, замеченных в предыдущих версиях ПО. Но о том, что в свежей версии всегда есть куча новых багов лучше не думать :)

Кликай на кнопку ‘Next’, выбирай тип установки ‘Server and Client’ и еще несколько раз жми на ‘Next’.
Вот и вся установка!

Все, теперь можно приступать к запуску InterBase, для этого запусти IBConsole:

Пуск – Программы – InterBase 7.5 Developer Edition – IBConsole.

После запуска появится окно, изображенное на рис.3.

Нам нужно зарегистрировать сервер InterBase, для этого лезь в меню “Server” и выбирай “Register …”, появится окошко, заполни его так чтобы получилось что-то вроде этого (рис.4):

Мы будем создавать базу на своем компе, поэтому выбирай Local Server. В поле Description введи описание нашего сервера. В поле Login Information – вводим логин и пароль админа базы данных, по умолчанию он будет (не знаю почему, но введи именно эти данные, потом можно будет добавлять и удалять пользователей):

Обязательно создай папку, где ты хочешь сохранить свою базу данных (вся твоя база будет состоять из одного GDB-файла!).
Теперь создадим базу данных – выбираем пункт меню “Database” – “Create Database …”
Появится следующее окно (рис.5.):

Указываем название файла базы данных и его месторасположение, в одной из строк списка Files.

Список Default Character Set не трогай! Если на твоем компе используется русский язык (то есть в настройках винды указано что ты в России), то русские буквы будут поддерживаться твоей базой.

В поле Alias вводим произвольный псевдоним.

Жмем ОК, база создана, окно IBConsole стала такой (рис.6.):

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

InterBase версии 7.5 позволяет манипулировать данными с помощью гуя, но мы все будем делать через утилиту Interactive SQL, так как с интерфейсом я думаю ты и так разберешься, а вот основные команды SQL тебе пригодятся.

Запускай утилиту Interactive SQL, выбрав в меню Tools – Interactive SQL, появится окошко (рис.7):

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

Введи в окне Interactive SQL следующий SQL-скрипт:

Эти строки означают, что мы создаем домен с именем "dnDB", тип данных: дата и накладываем ограничения: значение должно быть меньше либо равно сегодняшней дате.

Команда commit завершает транзакцию, которую неявно стартует утилита Interactive SQL. Противоположная ей команда rollback, которая позволяет делать откат, то есть если ты удалишь запись из таблицы, то можешь ее вернуть, написав в окне Interactive SQL:

Теперь нажми кнопку либо юзай сочетание клавиш: CTRL+E. Если ошибок нет, тогда у тебя в базе появится свеженький домен :)

Продолжим создавать наши домены.
Теперь пиши следующий код:

CREATE DOMAIN dnData AS DATE
NOT NULL;
commit;

CREATE DOMAIN dnFIO AS CHAR(40);
commit;

CREATE DOMAIN dnTEL AS CHAR(20);
commit;

CREATE DOMAIN dnNum AS INT
CHECK (VALUE > 0)
NOT NULL;
commit;

CREATE DOMAIN dnNOTE AS CHAR(150);
commit;

CREATE DOMAIN dnNAME AS CHAR(30);
commit;

CREATE DOMAIN dnNumCar AS CHAR(10);
commit;

CREATE DOMAIN dnCost AS NUMERIC(4, 2)
CHECK (VALUE > 0)
NOT NULL;
commit;

Жми CTRL+E, у тебя должны создаться 9 доменов (рис.9)

CREATE TABLE Personal (
PersonalID dnNum,
FIO dnFIO,
DATA_BD dnDB,
TEL dnTEL,

PRIMARY KEY (PersonalID)
);
commit;

Create table – команда создания таблицы, после команды указывается название таблицы, дальше в скобках указываются параметры таблицы, потом скобка закрывается и обязательно ставится точка с запятой.

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

Командой PRIMARY KEY определяем первичный ключ.
Не знаешь что такое первичный ключ? Бегом в начало статьи я об этом подробно писал!

Жми кнопку «Высокого напряжения»

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

Не останавливаемся на достигнутом, создаем еще 6 таблиц:

CREATE TABLE Client (
ClientID dnNum,
FIO dnFIO,
DATA_BD dnDB,
TEL dnTEL,

PRIMARY KEY (ClientID)
);
commit;

CREATE TABLE GrafWork (
WorkID dnNum,
PersonalKod dnNum,
Note dnNOTE,
Data dnDATA,

PRIMARY KEY (WorkID),
FOREIGN KEY (PersonalKod) REFERENCES Personal (PersonalID)
);
commit;

CREATE TABLE Car (
CarID dnNum,
ClientKod dnNum,
Mark dnNAME,
NumCar dnNUMCAR,
Note dnNOTE,

PRIMARY KEY (CarID),
FOREIGN KEY (ClientKod) REFERENCES Client (ClientID)
);
commit;

CREATE TABLE Service (
ServiceID dnNum,
Name dnNAME,
Cost dnCOST,

PRIMARY KEY (ServiceID)
);
commit;

CREATE TABLE Operation (
OperID dnNum,
PersonalKod dnNum,
ClientKod dnNum,
Data dnDATA,
Note dnNOTE,

PRIMARY KEY (OperID),
FOREIGN KEY (PersonalKod) REFERENCES Personal (PersonalID),
FOREIGN KEY (ClientKod) REFERENCES Client (ClientID)
);
commit;

CREATE TABLE TService (
TServiceID dnNum,
OperKod dnNum,
ServiceKod dnNum,

PRIMARY KEY (TServiceID),
FOREIGN KEY (OperKod) REFERENCES Operation (OperID),
FOREIGN KEY (ServiceKod) REFERENCES Service (ServiceID)
);
commit;

Команда "FOREIGN KEY (PersonalKod) REFERENCES Personal (PersonalID)" связывает таблицу GRAFWORK и PERSONAL.

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

Жми CTRL+E, у тебя должны создаться 7 таблиц (рис.10):

В Interbase нет такого типа как счетчик или autoincrement, выход из этой ситуации обеспечивается созданием, так называемого генератора!

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

Вызываем Interactive SQL, пятая кнопка справа. Пишем запрос:

Нажимаем выполнить.
Мы создали генератор GEN_CAR. Теперь нам надо установить начальное значение генератора, делается это следующим образом:

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

Остальные генераторы создаются аналогично:

CREATE GENERATOR GEN_CLIENT;
commit;

SET GENERATOR GEN_CLIENT TO 0;
commit;

CREATE GENERATOR GEN_GRAFWORK;
commit;

SET GENERATOR GEN_GRAFWORK TO 0;
commit;

CREATE GENERATOR GEN_OPERATION;
commit;

SET GENERATOR GEN_OPERATION TO 0;
commit;

CREATE GENERATOR GEN_PERSONAL;
commit;

SET GENERATOR GEN_PERSONAL TO 0;
commit;

CREATE GENERATOR GEN_SERVICE;
commit;

SET GENERATOR GEN_SERVICE TO 0;
commit;

CREATE GENERATOR GEN_TSERVICE;
commit;

SET GENERATOR GEN_TSERVICE TO 0;
commit;

Теперь жми CTRL+E, у тебя должно получится вот так (рис. 11):

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

Пишем запрос:

SET TERM !!;
CREATE TRIGGER "BEF_INS_CAR" FOR "CAR"
ACTIVE BEFORE INSERT
AS
BEGIN
NEW.CARID = GEN_ID(GEN_CAR,1);
END!!
SET TERM ;!!
commit;

Выполняем запрос! Опаньки, наш генератор теперь введен в работу :)

Конструкция SET TERM задает новый разделитель операторов. Если ее не использовать, то получится, что у тебя и после NEW.CARID = GEN_ID(GEN_CAR,1); стоит точка с запятой и после END тоже стоит точка запятой, а InterBase это не нравится, почему я так и не понял, но она без конструкции SET TERM отказывается создавать триггер.

Также хочу обратить твое внимание на то, что в строках:

после TERM необходимо поставить пробел, а между «!!» и «;» ставить пробел необязательно! С этой проблемой я столкнулся при переходе на версию 7.5, в IB6 скрипты работали без разделения пробелом «TERM» и «!!;».

Рассмотрим поподробней, что же мы накодили:

Create Trigger – говорит, что мы хотим создать триггер (правило), далее указываем название триггера и для какой таблицы он будет предназначен (FOR "CAR").
Предложение ACTIVE BEFORE INSERT – указывает, когда триггер должен выполняться, в данном случае, каждый раз перед созданием новой записи. Слово AS – зарезервированное, открывает тело триггера. Тело триггера всегда (даже если триггер содержит единственный оператор – как в нашем случае) должно ограничиваться парой ключевых слов BEGIN – END. В шестой строке расположен оператор, в котором новому значению (слово NEW) поля CARID присваивается значение, полученное от встроенной функции GEN_ID. Двумя параметрами обращения к этой функции указывается имя генератора и то значение, на которое должно увеличиться текущее значение генератора («шаг» генератора).

SET TERM !!;
CREATE TRIGGER "BEF_INS_CLIENT" FOR "CLIENT"
ACTIVE BEFORE INSERT
AS
BEGIN
NEW.CLIENTID = GEN_ID(GEN_CLIENT,1);
END!!
SET TERM ;!!
commit;

SET TERM !!;
CREATE TRIGGER "BEF_INS_GRAFWORK" FOR "GRAFWORK"
ACTIVE BEFORE INSERT
AS
BEGIN
NEW.WORKID = GEN_ID(GEN_GRAFWORK,1);
NEW.DATA = 'TODAY';
END!!
SET TERM ;!!
commit;

SET TERM !!;
CREATE TRIGGER "BEF_INS_OPERATION" FOR "OPERATION"
ACTIVE BEFORE INSERT
AS
BEGIN
NEW.OPERID = GEN_ID(GEN_OPERATION,1);
NEW.DATA = 'TODAY';
END!!
SET TERM ;!!
commit;

SET TERM !!;
CREATE TRIGGER "BEF_INS_PERSONAL" FOR "PERSONAL"
ACTIVE BEFORE INSERT
AS
BEGIN
NEW.PERSONALID = GEN_ID(GEN_PERSONAL,1);
END!!
SET TERM ;!!
commit;

SET TERM !!;
CREATE TRIGGER "BEF_INS_SERVICE" FOR "SERVICE"
ACTIVE BEFORE INSERT
AS
BEGIN
NEW.SERVICEID = GEN_ID(GEN_SERVICE,1);
END!!
SET TERM ;!!
commit;

SET TERM !!;
CREATE TRIGGER "BEF_INS_TSERVICE" FOR "TSERVICE"
ACTIVE BEFORE INSERT
AS
BEGIN
NEW.TSERVICEID = GEN_ID(GEN_TSERVICE,1);
END!!
SET TERM ;!!
commit;

Выполняй запрос. Теперь все генераторы связаны со своими таблицами.

В теории баз данных есть такая фишка: каскадное удаление данных, то есть при удалении строки из родительской таблицы происходит автоматическое удаление данных из дочерних таблиц. В нашем случае, например, тебе захочется удалить какого-нибудь сотрудника из таблицы «PERSONAL», но в «GRAFWORK» есть информация о графике работы этого сотрудника и в «OPERATION» находятся все операции совершенные им. То есть если сотрудник исчезнет, в связанных таблицах данные «повиснут», и твоя база будет засорена.

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

SET TERM !!;
CREATE TRIGGER "BEF_DEL_PERSONAL" FOR "PERSONAL"
ACTIVE BEFORE DELETE
AS
BEGIN
DELETE FROM "GRAFWORK" WHERE GRAFWORK.PERSONALKOD=PERSONAL.PERSONALID;
DELETE FROM "OPERATION" WHERE OPERATION.PERSONALKOD=PERSONAL.PERSONALID;
END!!
SET TERM ;!!
commit;

Мы создали триггер "BEF_DEL_PERSONAL" для таблицы "PERSONAL", который будет срабатывать перед удаление строки (ACTIVE BEFORE DELETE), выполняя удаление из таблицы «GRAFWORK» и «OPERATION» (DELETE FROM ...).

Теперь сделай еще два триггера с помощью скрипта:

SET TERM !!;
CREATE TRIGGER "BEF_DEL_OPERATION" FOR "OPERATION"
ACTIVE BEFORE DELETE
AS
BEGIN
DELETE FROM "TSERVICE" WHERE TSERVICE.OPERKOD=OPERATION.OPERID;
END!!
SET TERM ;!!
commit;

SET TERM !!;
CREATE TRIGGER "BEF_DEL_CLIENT" FOR "CLIENT"
ACTIVE BEFORE DELETE
AS
BEGIN
DELETE FROM "CAR" WHERE CAR.CLIENTKOD=CLIENT.CLIENTID;
DELETE FROM "OPERATION" WHERE OPERATION.CLIENTKOD=CLIENT.CLIENTID;
END!!
SET TERM ;!!
commit;

Выполни скрип, у тебя получатся вот такие триггеры (рис.12):

Всё готово!!!
Можешь кричать: «Ура!» и радостно бегать по комнате :)

Чтобы от нашей базы была хоть какая-то польза необходимо внести в нее информацию.

Можно, конечно, это сделать с помощью интерфейса IBConsole, но я опять-таки рекомендую юзать Interactive SQL. Запускай его, выбирай меню Querty – Load Script, находи в моих исходниках папку ‘sql’ и выбирай файл ‘insert.sql’ жми Ok.

Скрипт загрузится из файла в твое окошко, выполняй запрос и начинай изучать свою базу!

Можешь вносить свои данные и изменять старые. Удалять строки из таблиц, проверяя, как действует каскадное удаление.

Если ты заметил, в папке ‘sql’ есть еще несколько файлов, так вот в них содержатся все скрипты по созданию БД описанные выше! Теперь ты понял фишку использования SQL-скриптов? Сейчас ты в любой момент можешь заново создать свою базу буквально за 30 секунд :) Тебе достаточно в нужном порядке выполнять скрипты, а именно:

1. domain.sql
2. table.sql
3. generator.sql
4. trigger.sql
5. insert.sql

Ну, вот и все! Что знал – написал!

Помни: в этой статье изложены лишь основы БД (на примере InterBase), так сказать, чтоб ты был в курсе!!! Настоятельно рекомендую продолжить изучать СУБД, так как дело прибыльное и интересное ;)