Особенности проектирования баз данных (стр. 1 из 3)

Задание

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

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) Читатель имеет ФИО, телефон, адрес. Издательство – название, город.

Проектирование базы данных

Приведём ER-диаграмму в соответствии с описанием предметной области:

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

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

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

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

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

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

Наименование поля	Тип данных	Ограничения
ID Книги (ID_Books)	Целое число	NOTNULLПервичный ключЗначение уникально
Наименование (Name)	Строка (30)	NOT NULL
ID Издательства (ID_Publishers)	Целое число	NOT NULLВнешний ключ
Количество страниц (K_pages)	Целое число	NOT NULL
Тип переплёта (Cover)	Строка (15)	NOT NULL
Тираж (Tiraz)	Целое число	NOT NULL
ID Абонента (ID_abonent)	Целое число	Внешний ключ
Дата выдачи (Date_issue)	Дата

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

Наименование поля	Тип данных	Ограничения
ID Издательства (ID_Publishers)	Целое число	NOTNULLПервичный ключЗначение уникально
Название (Name)	Строка (20)	NOT NULL
Город (City)	Строка (20)

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

Наименование поля	Тип данных	Ограничения
ID Абонента (ID_ abonent)	Целое число	NOTNULLПервичный ключЗначение уникально
ФИО (FIO)	Строка (30)	NOTNULL
Телефон (Telephone)	Строка (10)
Адрес (Address)	Строка (20)	NOTNULL

Реализация серверной части базы данных

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

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

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

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

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

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

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

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

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

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

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

Зададим ключевое поле таблицы «BOOKS». Для этого сделаем активной вкладку Constraint (Ограничения). Щелкнем правой кнопкой мыши в свободном пространстве окна Table (Таблица) и в выпадающем меню выберем пункт Newprimarykey (новый первичный ключ). Далее установим первичный ключ для атрибута «ID_BOOKS». Для этого щелкнем левой кнопкой мыши в поле OnField (На поле) и установим нужный атрибут. Установим сортировку по возрастанию значения индекса – выберем Ascending для IndexSorting.

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

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/NewTrigger...(Триггер/Новый триггер). Появится окно создания триггера в котором укажем:

· Имя – «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 заносится в столбцы под новые значения.

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

Создадим триггер каскадного обновления и удаления записей таблицы «BOOKS» при обновлении и удалении соответствующих им записей в таблице «PUBLISHERS».