Особенности проектирования баз данных

- 23 -

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

1. В СУБД InterBase 6.0/Firebird 1.5 реализуйте серверную часть спроектированной ранее базы данных, которая должна содержать:

1) таблицы с определением первичного ключа

2) триггеры, реализующие каскадное обновление и каскадное удаление связанных полей

3) триггеры, присваивающие уникальное значение генератора в поле первичного ключа

4) триггеры, обеспечивающие журнализацию изменений определенной таблицы

5) не менее трех различных хранимых процедур

2. В ИСР Delphi создайте клиентское приложение, используя любую технологию доступа к данным (BDE, IBX, ADO и др.) с возможностью поиска и фильтрации данных, а также вывода отчета.

1) У каждой книги в библиотеке свой идентификационный номер.

2) Каждая книга может быть издана в одном издательстве.

3) В одном и том же издательстве издается несколько книг.

4) Каждая книга имеет определенное количество страниц, тип переплета, величину тиража.

5) Каждая книга в конкретный момент может находиться или в библиотеке или на руках только у одного читателя.

6) Каждый читатель может взять из библиотеки несколько книг.

7) Каждый читатель имеет уникальный номер абонемента.

8) Имя читателя не уникально. Название издательства не уникально.

9) Читатель имеет ФИО, телефон, адрес. Издательство - название, город.

- 23 -

Рис.1. ER-диаграмма

Так как каждая книга в конкретный момент времени может находиться на руках только у одного читателя, то мы можем связать сущности «Книга» и «Читатели» по полю «ID Абонента» связью типа Многие к одному так как в этом поле, являющемся внешним ключом для атрибута «Книги», могут повторяться записи, ибо один и тот же читатель может взять сразу несколько книг. Если это поле содержит значение NULL, то значит, что данная книга ни находится на руках у читателя, а находится в библиотеке. Добавим для сущности «Книга» дополнительное свойство «Дата выдачи». Значения этого поля должны равняться NULL если значение поля «ID Абонента» так же равняется NULL.

Приведём базу данных к третьей нормальной форме. Свойство «Издательство» сущности «Книги» имеет дополнительные свойства не зависящие от ключевых. Поэтому сделаем свойство «Издательство» самостоятельной сущностью и введём для него дополнительное свойство «ID Издательства» и сделаем его ключевым.

- 23 -

Рис.2. ER-диаграмма

Опишем структуру каждой таблицы.

Таблица «Книги» (Books)

Таблица «Издательства» (Publishers)

Таблица «Читатели» (Readers)

Серверную часть базы данных будем выполнять на сервере FireBird 2.1 в визуальной среде разработки IBExpert.

Запустим IBExpert. Выберем команду Database/Create Database (База данных/Создать базу данных). В появившемся окне Create Database (Создание базы данных) укажем:

· Server (Сервер) - Local (локальный)

· Database (Файл БД) - C:\01\LIBRARY.fdb

· UserName (Имя_пользователя) - SYSDBA

· Password (пароль) - masterkey - это имя и пароль администратора по умолчанию.

· Charset (кодировка) - WIN1251

· SQL Dialect (Диалект БД) - Dialect 3 (Диалект 3).

Нажмём OK. база данных будет создана. В появившемся окне Database Register (Регистрация БД) выберем в поле Версия сервера FireBird 2.1. Нажмем кнопку Register, и база данных будет зарегистрирована и отобразится в окне Database Explorer. В этом окне выберем зарегистрированную базу данных и выберем Database/Connect to Database (База данных/Подключиться к Базе данных). В окне Database Explorer отобразятся все элементы базы данных.

Создадим таблицу «Книги» (Books). Выберем Database/New Table (База данных/Новая таблица). В окне Table (Таблица) зададим имя Books и определим называния атрибутов их типы данных и ограничения. Откомпилируем скрипт, нажав <Ctrl+F9>.

Зададим ключевое поле таблицы «BOOKS». Для этого сделаем активной вкладку Constraint (Ограничения). Щелкнем правой кнопкой мыши в свободном пространстве окна Table (Таблица) и в выпадающем меню выберем пункт New primary key (новый первичный ключ). Далее установим первичный ключ для атрибута «ID_BOOKS». Для этого щелкнем левой кнопкой мыши в поле On Field (На поле) и установим нужный атрибут. Установим сортировку по возрастанию значения индекса - выберем Ascending для Index Sorting.

Перейдём на вкладку Fields (поля) щелчком правой кнопкой мыши на поле «ID_BOOKS» вызовем меню, из которого выберем Edit Field ID_BOOKS. В появившемся окне перейдём на вкладку Autoincrement (авто приращение). Ниже появятся три дополнительные вкладки Generator (генератор) Trigger (триггер) и Procedure (процедура). На вкладке Generator выберем Create Generator (создать генератор), присвоим ему имя «GEN_BOOKS_ID» и начальное значение укажем равным 1. Перейдём на вкладку Trigger и выберем Create Trigger (создать триггер). Автоматически будет написан код:

IF (NEW."ID_ BOOKS" IS NULL) THEN

NEW."ID_ BOOKS" = GEN_ID(GEN_BOOKS_ID,1);

Здесь содержится оператор условия. Если при обращении к новому значению столбца «ID_ BOOKS» (оператор NEW), оно является пустым (оператор IS NULL) то ему присваивается значение, генерируемое созданным ранее генератором GEN_BOOKS_ID (GEN_ID(имя_генератора, шаг) - оператор обращения к генератору). Нажмём «ОК» и откомпилируем скрипт.

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

Создадим две оставшиеся таблицы: «Издательства» (Publishers) и «Читатели» (Readers).

Создадим триггеры журнализации изменений таблицы «BOOKS». Для этого создадим таблицу «BOOKS_JOURNAL», в которую будут автоматически записываться любые изменения, добавления, удаления в таблице «BOOKS». При этом будет фиксироваться дата (поле «DATE»), операция (INS, UPD, DEL) над таблицей «BOOKS» (поле «OPERATION»), а также старое и новое значение столбцов этой таблицы. Для операции удаления новое значение столбцов будет пустым. Для операции добавления пустым будет старое значение столбцов.

Для журнализации была создана таблица:

CREATE TABLE BOOKS_JOURNAL (

OPERATION CHAR(6) NOT NULL,

ID_BOOKS_OLD INTEGER,

ID_BOOKS_NEW INTEGER,

ID_PUBLISHERS_OLD INTEGER,

ID_PUBLISHERS_NEW INTEGER,

K_PAGES_OLD INTEGER,

K_PAGES_NEW INTEGER,

COVER_OLD VARCHAR(15),

COVER_NEW VARCHAR(15),

TIRAZ_OLD INTEGER,

TIRAZ_NEW INTEGER,

ID_ABONENT_OLD INTEGER,

ID_ABONENT_NEW INTEGER,

DATE_ISSUE_OLD DATE,

DATE_ISSUE_NEW DATE,

NAME_OLD VARCHAR(20),

NAME_NEW VARCHAR(20),

DATE_ DATE NOT NULL,

TIME_ TIME NOT NULL,

ID_JOURNAL INTEGER NOT NULL);

ALTER TABLE BOOKS_JOURNAL ADD PRIMARY KEY (ID_JOURNAL);

Теперь создадим триггер, закреплённый за таблицей «BOOKS» для вставки, изменения и удаления записей. Для создания триггера выберем Triggers/New Trigger...(Триггер/Новый триггер). Появится окно создания триггера в котором укажем:

· Имя - «JOURNAL»;

· Для таблицы - «BOOKS»;

· Тип - «BEFORE», т. е. выполняется прежде операции;

· Укажем INSERT, UPDATE, DELETE.

Полный текст триггера будет следующий:

SET TERM ^ ;

CREATE OR ALTER TRIGGER JOURNAL FOR BOOKS

ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0

AS

begin

IF (INSERTING) THEN

begin

insert into BOOKS_JOURNAL values ('INSERT', null, new.id_books, null, new.id_publishers, null, new.k_pages, null, new.cover, null, new.tiraz, null, new.id_abonent, null, new.date_issue, null, new.name, 'now', 'now', gen_id(generator_journal,1));

end

IF (updating) THEN

begin

insert into BOOKS_JOURNAL values ('UPDATE', old.id_books, new.id_books, old.id_publishers, new.id_publishers, old.k_pages, new.k_pages, old.cover, new.cover, old.tiraz, new.tiraz, old.id_abonent, new.id_abonent, old.date_issue, new.date_issue, old.name, new.name, 'now', 'now', gen_id(generator_journal,1));

end

IF (deleting) THEN

begin

insert into BOOKS_JOURNAL values ('DELETE', old.id_books, null, old.id_publishers, null, old.k_pages, null, old.cover, null, old.tiraz, null, old.id_abonent, null, old.date_issue, null, old.name, null, 'now', 'now', gen_id(generator_journal,1));

end

end

^ SET TERM ; ^

Триггер состоит из трёх условных операторов, проверяющих выполняется ли над записями таблицы соответственно операция вставки, изменения и удаления. Для операции вставки будет выполнен первый оператор, где в таблице журнала в поле «OPERATION» будет вставлено значение «INSERT», в поле «DATE_» текущая дата (значение «now»), в поле «TIME_» текущее время (значение «now»), также будут переписаны все новые значения полей, а в полях таблицы журнала, определённых для хранения старых значений полей таблицы «BOOKS», занесутся значения NULL. Второй оператор « IF (updating) THEN» проверяет, выполняется ли изменение данных, и если да, то выполнится оператор вставки записи в таблицу журнала «BOOKS_JOURNAL». Этот оператор задан аналогично предыдущему, только в поля под старые значения вместо NULL заносятся значения соответствующих столбцов таблицы «BOOKS» до их изменения. Аналогично задан последний оператор на удаления записи из таблицы «BOOKS», где NULL заносится в столбцы под новые значения.

Страницы: 1, 2, 3