Реляційні бази даних

Додаток Б

Б.1. Про фізичну модель

Б.2. База даних

Б.3. Домени

Б.4. Таблиця PhoneTypes («ТИПИ ТЕЛЕФОНІВ»)

Б.5. Таблиця PasportData («ПАСПОРТНІ ДАНІ»)

Б.6. Таблиця Librarians («БІБЛІОТЕКАРИ»)

Б.7. Таблиця Readers («ЧИТАЧІ»)

Б.8. Таблиця Phones («ТЕЛЕФОНИ»)

Б.9. Таблиця Books («КНИЖКИ»)

Б.10. Таблиця BookAuthors («АВТОРИ КНИЖОК»)

Б.11. Таблиця CoAuthorship («СПІВАВТОРСТВО»)

Б.12. Таблиця BookFunds («ТИПИ ФОНДІВ КНИЖОК»)

Б.13. Таблиця BookInventoryNumbers («ІНВЕНТАРНІ НОМЕРИ КНИЖОК»)

Б.14. Таблиця BookGiveOutRecord («ОБЛІК ВИДАЧІ КНИЖОК»)

Висновки

Мета

Мета: показати тісний зв'язок між логічним і фізичним моделюванням реляційної бази даних.

Б.1. Про фізичну модель

Фізична модель бази даних створюється після вибору системи управління базою даних (СКБД). Шановні відвідувачі сайту не важливо яку систему керування базами даних Ви будете використовувати у своїй професійній кар'єрі. Запропонований Вашій увазі курс був від початку спрямований на вивчення організації реляційних баз даних. Тому головним критерієм вибору СКБД Interbase був мінімум часу на її освоєння.

Що робити, якщо для вивчення курсу ви вибрали іншу реляційну СКБД? Це не змінить суті процесу фізичного моделювання. Фізична модель бази даних «БІБЛІОТЕКА» буде відрізнятися тільки типами даних вашої СКБД. Тут наведено типи даних СКБД Interbase. Знайдіть їх аналоги в своїй СКБД і внесить відповідні зміни до фізичної моделі. Зверніть увагу, що ваша СКБД може не підтримувати домени. Відповідні пояснення щодо цього наведені в параграфі B.3.

У лекції Ви можете прочитати загальні рекомендації про порядок створення таблиць в реляційних БД. Там же наведений приклад послідовності створення таблиць у БД «БІБЛІОТЕКА». Вона відрізняється від тієї, що наведена у фізичній моделі (табл. Б.1). Пам'ятайте, що для досягнення мети є багато шляхів. Вам їх вибирати.

Таблиця Б.1

Порядок опису таблиць у фізичній моделі БД «БІБЛІОТЕКА»
№	Ім'я відношення у логічній моделі	Ім'я таблиці у фізичній моделі
1	ТИПИ ТЕЛЕФОНІВ	PhoneTypes
2	ПАСПОРТНІ ДАНІ	PasportData
3	БІБЛІОТЕКАРИ	Librarians
4	ЧИТАЧІ	Readers
5	ТЕЛЕФОНИ	Phones
6	КНИЖКИ	Books
7	АВТОРИ КНИЖОК	BookAuthors
8	СПІВАВТОРСТВО	CoAuthorship
9	ТИПИ ФОНДІВ КНИЖОК	BookFunds
10	ІНВЕНТАРНІ НОМЕРИ КНИЖОК	BookInventoryNumbers
11	ОБЛІК ВИДАЧІ КНИЖОК	BookGiveOutRecord

Існує подібність в описі послідовності таблиць в логічної і фізичної моделі БД «БІБЛІОТЕКА». Обидві моделі можна розділити на дві частини. Спочатку описуються таблиці з інформацією про читачів і бібліотекарів. Потім описуються таблиці з інформацією про книги. Таблиця з інформацією про процес прийому і видачі книг описується останньої. Однак порядок опису таблиць в першій і другій частинах є різним для логічної і фізичної моделей.

Б.2. База даних

Фізичне моделювання реляційної бази даних починається з її створення. Для цього необхідно скористатися оператором CREATE DATABASE. Для створення БД «БІБЛІОТЕКА» він буде мати наступний вигляд:

CREATE DATABASE "C:/DB/LIBRARY.GDB"

USER "SYSDBA"

PASSWORD "masterkey"

Оператор розміщує базу даних у каталозі C:/DB/. Вам необхідно створити його перед виконанням оператора CREATE DATABASE. База даних створена для користувача з необмеженими правами. Такий користувач за замовчуванням є в будь-який СКБД. Ми зробили це для полегшення знайомства з організацією реляційних баз даних. Наш проект освітній. НІКОЛИ НЕ РОБІТЬ ЦЕ В СПРАВЖНЬМУ ПРОЕКТІ.

Оператор CREATE DATABASE створить файл LIBRARY.GDB в каталозі C:/DB/. Щоб створити об'єкти фізичної моделі Вам необхідно підключитися до бази даних LIBRARY.GDB. Для цього використовуйте оператор CONNECT:

CONNECT "C:/DB/LIBRARY.GDB"

USER "SYSDBA"

PASSWORD "masterkey"

Ще раз зверніть увагу на те, що база даних LIBRARY.GDB навчальна. Ми будемо працювати з нею під ім'ям адміністратора СКБД Interbase. Інформацію про організацію доступу інших користувачів до реляційних баз даних Ви знайдете в розділі «Захист бази даних від несанкціонованого доступу».

Б.3. Домени

Ця частина фізичного проектування реляційної бази даних для тих, хто вибрав СКБД, в якій підтримуються домени. Якщо Ваша СКБД їх не підтримує, то Вам варто дізнатися від чого Ви відмовилися.

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

Оператор CREATE DOMAIN в СКБД Interbase надає широкий спектр можливостей для визначення властивостей атрибутів відношень. Він дозволяє привласнити унікальне ім'я стандартному типу даних. Саме це робить семантичне значення атрибута відношення унікальним. Ви можете визначити значення за замовчуванням для домену та вказати умови для його перевірки. Цей список завжди можна уточнити в документації СКБД. Він може бути ширшим або звуженим.

Є ще одна незаперечна перевага визначення типів даних за допомогою доменів. Його важко переоцінити. Наприклад, всі атрибути, що зберігають прізвище, ім'я або по батькові читача, бібліотекара або автора книги в базі даних «БІБЛІОТЕКА», визначені на домені FIO (табл. Б.2). Тут наведено відповідний оператор CREATE DOMAIN. На цьому домені визначено дев'ять атрибутів в трьох відношеннях. Для запису кожного з цих значень виділено тридцять символів. Ви можете змінити їх кількість або інші властивості цих атрибутів за допомогою одного оператора ALTER DOMAIN. Такі випадки виникають досить часто в ході експлуатації реляційних баз даних.

Таблиця Б.2

Домени атрибутів відношень в БД «БІБЛІОТЕКА»
№	Атрибути	Відношення	Опис домену	Загальні вимоги	Шмена доменів	Тип даних
1	Первинні та зовнішні ключі усіх відношень	УСІ	Унікальні номери рядків для всіх відношень (первинні ключі), значення зовнішніх ключів усіх відношень	Цілочислове значення	AllCode	INTEGER
2	Прізвища, імена та по батькові людей	ЧИТАЧІ, БІБЛІОТЕКАРИ, АВТОРИ КНИЖОК	Множина усіх прізвищ, імен і по батькові людей	Символьний, 30 символів	FIO	CHAR(30)
3	Найменування	ТИПИ ТЕЛЕФОНІВ	Множина усіх найменувань типів телефонів	Символьний, 20 символів	AllNameType	CHAR(20)
4	Серія паспорта	ПАСПОРТНІ ДАНІ	Множина усіх серій паспортів читачів та бібліотекарів	Символьний, 2 символів	AllSeries	CHAR(2)
5	№ паспорта	ПАСПОРТНІ ДАНІ	Множина усіх номерів паспортів читачів та бібліотекарів	Цілочислове значення	AllNumber	INTEGER
6	Місце народження	ПАСПОРТНІ ДАНІ	Множина усіх описів місць народження читачів та бібліотекарів	Символьний, 100 символів	AllPlace	CHAR(100)
7	Пол	ПАСПОРТНІ ДАНІ	Множина описів статі читачів і бібліотекарів	Символьний, 1 символ	AllSex	CHAR(1)
8	Місце видачі паспорта	ПАСПОРТНІ ДАНІ	Множина описів місць видачі паспортів читачам і бібліотекарям	Символьний, 100 символів	AllIssuePlace	CHAR(100)
9	Примітки	ЧИТАЧІ, БІБЛІОТЕКАРИ, ПАСПОРТНІ ДАНІ, КНИЖКИ, АВТОРИ КНИЖОК	Множина усіх не структурованих заміток	Двійковий великий об'єкт	AllNote	BLOB
10	коротка біографія	АВТОРИ КНИЖОК	Множина усіх коротких біаграфій авторів книг	Двійковий великий об'єкт	AllNote	BLOB
11	Табельний номер	БІБЛІОТЕКАРИ	Множина усіх табельних номерів бібліотекарів	Цілочислове значення	AllClockNumber	INTEGER
12	Посада	БІБЛІОТЕКАРИ	Множина усіх посад бібліотекарів	Символьний, 30 символів	AllPost	CHAR(30)
13	Домашній телефон, № телефону	БІБЛІОТЕКАРИ, ТЕЛЕФОНИ	Множина усіх телефонів бібліотекарів та читачів	Символьний, 20 символів	AllHomePhone	CHAR(20)
14	№ квитка читача	ЧИТАЧІ	Множина усіх номерів читацьких квитків	Цілочислове значення	AllReaderCardNumber	INTEGER
15	Місце основної роботи	ЧИТАЧІ	Множина описів усіх місць основної роботи читачів	Символьний, 60 символів	AllJob	CHAR(60)
16	Назва	КНИЖКИ	Множина усіх назв книг	Символьний, 200 символів	BookName	CHAR(200)
17	Тираж	КНИЖКИ	Множина усіх тиражів книг	Цілочислове значення	AllDrawing	INTEGER
18	Універсальний десятинний класифікатор	КНИЖКИ	Множина усіх універсальних десяткових класифікаторів книг	Символьний, 20 символів	AllUDK	CHAR(20)
19	Шифр	КНИЖКИ	Множина усіх внутрішніх шифрів книг в бібліотеці	Символьний, 30 символів	AllCipher	CHAR(30)
20	Найменування	ТИПИ ФОНДІВ КНИЖОК	Множина усіх найменувань типів фондів книжок	Символьний, 20 символів	AllName	CHAR(20)
21	Інвентарний номер	ІНВЕНТАРНІ НОМЕРИ КНИЖОК	Множина усіх інвентарних номерів книжок	Цілочислове значення	AllInventoryNumber	INTEGER
22	Вартість	ІНВЕНТАРНІ НОМЕРИ КНИЖОК	Множина усіх номіналів вартості книжок	Значення з плаваючою точкою	AllCost	FLOAT

Б.4. Таблиця PhoneTypes («ТИПИ ТЕЛЕФОНІВ»)

Перша частина фізичної моделі, як і перша частина логічної моделі БД «БІБЛІОТЕКА», містить таблиці з інформацією про читачів та бібліотекарів. Ми починаємо роботу над фізичною моделлю бази даних «БІБЛІОТЕКА» зі створення доменів для таблиці PhoneTypes (табл. Б.1).

Атрибути відношення «ТИПИ ТЕЛЕФОНІВ» логічної моделі БД "БІБЛІОТЕКА" визначено в її фізичної моделі на доменах AllCode і AllNameType. В СКБД Interbase 2020 для їх створення використовуються наступні оператори:

CREATE DOMAIN AllCode AS INTEGER

NOT NULL;

CREATE DOMAIN AllNameType AS CHAR(20)

NOT NULL;

Пропозиція NOT NULL усуває відсутність даних в будь-яких атрибутах відношень, що визначені на доменах AllCode і AllNameType у фізичній моделі. Тут Ви можете побачити інші можливості оператора CREATE DOMAIN в СКБД Interbase.

Специфікація будь-яких відношень логічної моделі реалізується у фізичній моделі за допомогою оператора CREATE TABLE. Для відносини «ТИПИ ТЕЛЕФОНІВ» в СКБД Interbase 2020 він має наступний вигляд:

CREATE TABLE PhoneTypes

(Code AllCode,

Name AllNameType,

PRIMARY KEY (Code));

Якщо СКБД не підтримує домени, то оператор CREATE TABLE матиме такий формат:

CREATE TABLE PhoneTypes

(Code INTEGER,

Name CHAR(20),

PRIMARY KEY (Code));

Тут Ви можете побачити інші можливості оператора CREATE TABLE в СКБД Interbase. Знайдіть цей оператор в документації СКБД, яку ви вибрали для своєї професійної діяльності. Він буде мати багато спільного з оператором CREATE TABLE СКБД Interbase.

Зверніть увагу, що таблиця PhoneTypes повністю відповідає специфікації відношення «ТИПИ ТЕЛЕФОНІВ» (табл. Б.3).

Таблиця Б.3

Специфікація таблиці *PhoneTypes* («ТИПИ ТЕЛЕФОНІВ»)
№	Заголовок	Имя столбца	Тип	Длина	Ключ
1	Код	Code	INTEGER		P
2	Найменування	Name	CHAR	20

Б.5. Таблиця PassportData («ПАСПОРТНІ ДАНІ»)

Атрибути відношення «ПАСПОРТНІ ДАНІ» визначено у фізичній моделі бази даних «БІБЛІОТЕКА» на наступних доменах: AllCode, AllSeries, AllNumber, AllPlace, AllSex, AllIssuePlace і AllNote. Домен AllCode був визначений за допомогою оператора CREATE DOMAIN перед таблицею PhoneTypes. Решту доменів необхідно створити.

CREATE DOMAIN AllSeries AS CHAR(2)

NOT NULL;

CREATE DOMAIN AllNumber AS INTEGER

CHECK(VALUE >= 10)

NOT NULL;

CREATE DOMAIN AllPlace AS CHAR(100)

NOT NULL;

CREATE DOMAIN AllSex AS CHAR(1)

CHECK(VALUE IN ('М','Ж'));

CREATE DOMAIN AllIssuePlace AS CHAR(100)

NOT NULL;

CREATE DOMAIN AllNote AS BLOB;

В усіх доменах крім AllSex і AllNote має місто пропозиція NOT NULL. Вона забороняє відсутність даних в усіх стовбцях таблиці PassportData, що визначені на доменах AllCode, AllSeries, AllNumber, AllPlace і AllIssuePlace. Про це ми вже писали для доменів таблиці PhoneTypes.

Фактично, домен AllSex також не допускає порожнього значення. Його аргумент CHECK(VALUE IN ('M', 'F')) точно вказує набір допустимих значень. Таким чином, тільки домен AllNote дозволяє не вводити ніяких значень. Нагадаємо, що відсутність значення в чарунці таблиці реляційної СКБД позначається спеціальним визначником NULL.

Пропозиція CHECK(VALUE >= 10) домену AllNumber визначає ліву межу діапазону його цілочислових значень. Права межа діапазону визначена максимальним значенням цілочислового типу даних INTEGER СКБД Interbase. Зазвичай, такі речі визначає Замовник, виходячи з особливостей бізнес-процесів.

Після визначення всіх необхідних доменів ми можемо створити таблицю PasportData:

CREATE TABLE PasportData

(Code AllCode,

Series AllSeries,

Number AllNumber,

Birthday DATE NOT NULL,

BirthPlace AllPlace,

Sex AllSex,

IssuePlace AllIssuePlace,

IssueDate DATE,

Note AllNote);

У логічної моделі БД «БІБЛІОТЕКА» атрибут Code – первинний ключ відношення «ПАСПОРТНІ ДАНІ». Його немає у фізичній моделі таблиці PasportData (табл. Б.4). Замість нього в БД «БІБЛІОТЕКА» визначено тригери BfrInsInPasportData і BfrUpdInPasportData. Вони забезпечують цілісність сутності "Паспорт" щодо унеможливлення додавання даних про два різних документи з однаковими значеннями первинного ключа Code та складеного потенційного ключа Series, Number. Подробиці дізнаєтеся після публікації SCRIPT-файлу, який реалізує фізичну модель БД «БІБЛІОТЕКА» в СКБД Interbase.

Таблиця Б.4

Специфікація таблиці *PasportData* («ПАСПОРТНІ ДАНІ»)
№	Заголовок	Имя столбца	Тип	Длина
1	Код	Code	INTEGER
2	Серія паспорта	Series	CHAR	2
3	№ паспорта	Number	INTEGER
4	Дата народження	Birthday	DATE
5	Місце народження	BirthPlace	CHAR	30
6	Пол	Sex	CHAR	1
7	Місце видачі паспорта	IssuePlace	CHAR	100
8	Дата видачі паспорта	IssueDate	DATE
9	Примітки	Note	BLOB

Б.6. Таблиця Librarians («БІБЛІОТЕКАРИ»)

Атрибути відношення «БІБЛІОТЕКАРИ» визначено у фізичній моделі БД «БІБЛІОТЕКА» на наступних доменах: AllCode, AllClockNumber, FIO, AllPost, AllHomePhone і AllNote. Домени AllCode і AllNote визначені раніше. Решту доменів ми повинні визначити перед створенням таблиці Librarians:

CREATE DOMAIN AllClockNumber AS INTEGER

CHECK(VALUE >= 10)

NOT NULL;

CREATE DOMAIN FIO AS CHAR(30)

NOT NULL;

CREATE DOMAIN AllPost AS CHAR(30)

CHECK(VALUE IN ('М','Ж'));

CREATE DOMAIN AllHomePhone AS CHAR(20)

NOT NULL;

Пропозиції NOT NULL і CHECK(VALUE >= 10) оператора CREATE DOMAIN були описані раніше (див. таблиці PhoneTypes і PasportData). Тепер усі домени атрибутів відношення «БІБЛІОТЕКАРИ» визначені, і ми можемо створити таблицю Librarians.

CREATE TABLE Librarians

(Code AllCode,

ClockNumber AllClockNumber,

FamilyName FIO,

Name FIO,

Patronymic FIO,

PasportCode AllCode,

Post AllPost,

HomePhone AllHomePhone,

Note AllNote,

PRIMARY KEY (Code));

У специфікації відношення «БІБЛІОТЕКАРИ» визначено зовнішній ключ «Код паспорта» (див. логічну модель). Його немає у таблиці Librarians (див. оператор CREATE TABLE Librarians і табл. Б.5). Це пов'язано з тим, що посилальна цілісність БД «БІБЛІОТЕКА» між таблицями Librarians і PasportData забезпечується за допомогою тригерів. Цей механізм більш гнучкий, ніж механізм первинних та зовнішніх ключів. Одночасна робота цих двох механізмів неможлива. Більш докладно про це буде розказано після публікації практичних занять.

Таблиця Б.5

Специфікація таблиці *Librarians* («БІБЛІОТЕКАРИ»)
№	Заголовок	Имя столбца	Тип	Длина	Ключ
1	Код	Code	INTEGER		P
2	Табельний номер	ClockNumber	INTEGER
3	Прізвище	FamilyName	CHAR	30
4	Ім'я	Name	CHAR	30
5	По батькові	Patronymic	CHAR	30
6	Код паспорта	PasportCode	INTEGER
7	Посада	Post	CHAR	30
8	Домашній телефон	HomePhone	CHAR	20
9	Примітки	Note	BLOB

Б.7. Таблиця Readers («ЧИТАЧІ»)

Атрибути відношення «ЧИТАЧІ» визначено у фізичній моделі БД «БІБЛІОТЕКА» на наступних доменах: AllCode, FIO, AllReaderCardNumber, AllJob, AllPost і AllNote. Домени AllCode, AllNote, AllPost і FIO визначені раніше перед таблицями PhoneTypes, PasportData і Librarians. Домени AllReaderCardNumber і AllJob ми повинні визначити перед створенням таблиці Readers.

CREATE DOMAIN AllReaderCardNumber AS INTEGER

CHECK(VALUE >= 10)

NOT NULL;

CREATE DOMAIN AllJob AS CHAR(60)

NOT NULL;

Призначення пропозицій NOT NULL і CHECK(VALUE >= 10) описано раніше (див. таблиці PhoneTypes і PasportData). Нагадаємо, що джерелом таких обмежень є результат аналізу бізнес-процесів.

Тепер ми можемо створити таблицю Readers:

CREATE TABLE Readers

(Code AllCode,

FamilyName FIO,

Name FIO,

Patronymic FIO,

ReaderCardNumber AllReaderCardNumber,

PasportCode AllCode,

Job AllJob,

Post AllPost,

Note AllNote);

У логічної моделі БД «БІБЛІОТЕКА» була визначена специфікація відношення «ЧИТАЧІ» з первинним та зовнішнім ключем. У фізичної моделі для таблиці Readers ці ключі не визначені (табл. Б.6). Це пов'язано з тим, що на етапі фізичного моделювання прийнято рішення підтримувати цілісність сутностей в таблиці Readers за допомогою тригерів. Тригера також використовуються для підтримки посилальної цілісності між таблицею Readers та іншими таблицями БД «БІБЛІОТЕКА». Більш детально питання застосування тригерів будуть розглянуті після публікації практичних занять.

Таблиця Б.6

Специфікація таблиці *Readers* («ЧИТАЧІ»)
№	Заголовок	Имя столбца	Тип	Длина
1	Код	Code	INTEGER
2	Прізвище	FamilyName	CHAR	30
3	Ім'я	Name	CHAR	30
4	По батькові	Patronymic	CHAR	30
5	№ квитка читача	ReaderCardNumber	INTEGER
6	Код паспорта	PasportCode	INTEGER
7	Місце основної роботи	Job	CHAR	60
8	Посада	Post	CHAR	30
9	Примітки	Note	BLOB

Б.8. Таблиця Phones («ТЕЛЕФОНИ»)

Таблиця Phones завершує першу частину фізичної моделі БД «БІБЛІОТЕКА». До неї увійшли таблиці з інформацією про читачів та бібліотекарів. Нагадаємо, що першу частину фізичної моделі ми почали зі створення доменів для стовпців таблиці PhoneTypes.

Атрибути відношення «ТЕЛЕФОНИ» визначено у фізичній моделі БД «БІБЛІОТЕКА» на доменах AllCode і AllHomePhone. Домен AllCode був створений перед таблицею PhoneTypes, а домен AllHomePhone ми визначили перед таблицею Librarians. Тепер ми можемо створити таблицю Phones:

CREATE TABLE Phones

(ReaderCode AllCode,

PhoneTypesCode AllCode,

PhoneNumber AllHomePhone);

У логічної моделі БД «БІБЛІОТЕКА» була визначена специфікація відношення «ТЕЛЕФОНИ» з двома зовнішніми ключами. Ці ключі не визначені для таблиці Phones в фізичної моделі (табл. Б.7). Замість них тригери підтримують посилальну цілісність в базі даних «БІБЛІОТЕКА» між таблицями Readers та Phones, PhoneTypes та Phones. Більш докладно про це Ви дізнаєтеся після публікації практичних занять.

Таблиця Б.7

Специфікація таблиці *Phones* («ТЕЛЕФОНИ»)
№	Заголовок	Имя столбца	Тип	Длина
1	Код читача	ReaderCode	INTEGER
2	Код типу телефону	PhoneTypeCode	INTEGER
3	№ телефону	PhoneNumber	CHAR	20

Б.9. Таблиця Books («КНИЖКИ»)

Друга частина фізичної моделі БД «БІБЛІОТЕКА» починається зі створення доменів для атрибутів відношення «КНИЖКИ». Вона включає таблиці з інформацією про книжки, що зберігаються в бібліотеці.

Атрибути відношення «КНИЖКИ» визначено на доменах AllCode, BookName, AllDrawing, AllUDK, AllCipher і AllNote. Домен AllCode був створений раніше перед таблицею PhoneTypes. Домен AllNote був визначений до створення таблиці PasportData. Решту доменів необхідно створити.

CREATE DOMAIN BookName AS CHAR(200)

NOT NULL;

CREATE DOMAIN AllDrawing AS INTEGER

CHECK(VALUE >= 10)

NOT NULL;

CREATE DOMAIN AllUDK AS CHAR(20)

NOT NULL;

CREATE DOMAIN AllCipher AS CHAR(30)

NOT NULL;

Пропозиція NOT NULL забороняє відсутність значень. Суть пропозиції CHECK(VALUE >= 10) описана при створенні домену AllNumber перед таблицею PasportData. Вас не повинно дивувати те, що у двох доменів з різними іменами однакові властивості. У цих доменів різні смислові значення.

Всі об'єкти, що необхідні для створення таблиці Books визначені. Створимо її за допомогою оператора CREATE TABLE:

CREATE TABLE Books

(Code AllCode,

Name BookName,

IssueYear DATE NOT NULL,

Drawing AllDrawing,

BookPublishers CHAR(60),

UDK AllUDK,

Cipher AllCipher,

Note AllNote,

PRIMARY KEY (Code));

Таблиця Books повністю відповідає специфікації відношення «КНИЖКИ» в логічної моделі БД «БІБЛІОТЕКА» (табл. Б.8.).

Таблиця Б.8

Специфікація таблиці *Books* («КНИЖКИ»)
№	Заголовок	Имя столбца	Тип	Длина	Ключ
1	Код	Code	INTEGER		P
2	Назва	Name	CHAR	200
3	Рік видання	IssueYear	DATE
4	Тираж	Drawing	INTEGER
5	Видавництво	BookPublishers	CHAR	60
6	Універсальний десятинний класифікатор	UDC	CHAR	20
7	Шифр	Cipher	CHAR	30
8	Примітки	Note	BLOB

Б.10. Таблиця BookAuthors («АВТОРИ КНИЖОК»)

Відношення «АВТОРИ КНИЖОК» продовжує реалізацію другої частини фізичної моделі БД «БІБЛІОТЕКА». Воно визначено всього на трьох доменах: AllCode, FIO і AllNote. Усі вони визначені вище. Тому ми можемо відразу створити таблицю BookAuthors за допомогою оператора CREATE TABLE:

CREATE TABLE BookAuthors

(Code AllCode,

FamilyName FIO,

Name FIO,

Patronymic FIO,

Birthday DATE NOT NULL,

Deatheday DATE,

ShortBiography AllNote,

Note AllNote,

PRIMARY KEY (Code));

Таблиця BookAuthors повністю відповідає специфікації відношенню «АВТОРИ КНИЖОК» в логічної моделі БД «БІБЛІОТЕКА» (табл. Б.9).

Таблиця Б.9

Специфікація таблиці *BookAuthors* («АВТОРИ КНИЖОК»)
№	Заголовок	Имя столбца	Тип	Длина	Ключ
1	Код	Code	INTEGER		P
2	Прізвище	FamilyName	CHAR	30
3	Ім'я	Name	CHAR	30
4	По батькові	Patronymic	CHAR	30
5	Дата народження	Birthday	DATE
6	Дата смерті	DeatheDay	DATE
7	Коротка біографія	ShortBiography	BLOB
8	Примітки	Note	BLOB

Б.11. Таблиця CoAuthorship («СПІВАВТОРСТВО»)

Атрибути відношення «СПІВАВТОРСТВО» визначені всього на одному домені: AllCode. Він визначений раніше перед створенням таблиці PhoneTypes. Тому, ми можемо відразу додати в нашу фізичну модель БД «БІБЛІОТКА» таблицю CoAuthorship за допомогою оператора CREATE TABLE:

CREATE TABLE CoAuthorship

(Code AllCode,

AuthorCode AllCode,

PRIMARY KEY (BookCode, AuthorCode),

FOREIGN KEY (AuthorCode) REFERENCES BookAuthors

ON DELETE CASCADE

ON UPDATE CASCADE,

FOREIGN KEY (BookCode) REFERENCES Books

ON DELETE CASCADE

ON UPDATE CASCADE);

Зупинимося докладніше на пропозиції FOREIGN KEY оператора CREATE TABLE CoAuthorship. Синтаксис пропозиції побудований так, що можна наочно бачити первинний ключ і таблицю, з якою пов'язаний кожен зовнішній ключ таблиці CoAuthorship. Зовнішній ключ AuthorCode посилається на первинний ключ таблиці BookAuthors. Зовнішній ключ BookCode посилається на первинний ключ таблиці Books. Пропозиції ON DELETE CASCADE і ON UPDATE CASCADE визначають дії механізму підтримки посилальної цілісності БД «БІБЛІОТЕКА». Наведемо його роботу на прикладі зовнішнього ключа AuthorCode.

Для зовнішнього ключа AuthorCode пропозиція ON DELETE CASCADE означає, що видалення рядка в таблиці BookAuthors спричинить за собою видалення набору рядків в таблиці CoAuthorship. Значення в стовпці AuthorCode для цих рядків дорівнюють значенню первинного ключа AllCode в рядку, який видаляється з таблиці BookAuthors. Пропозиція ON UPDATE CASCADE змінює значення зовнішнього ключа в стовпці AuthorCode якщо значення первинного ключа в стовпці AllCode таблиці BookAuthors буде змінено. Наприклад, зміна значення 1 на 100 в стовпці AllCode таблиці BookAuthors призведе до зміни значення 1 на 100 відразу в двох рядках таблиці CoAuthorship (див. таблицю В.8).

Залишилося додати, що таблиця CoAuthorship повністю відповідає специфікації відношення «СПІВАВТОРСТВО» в логічній моделі БД «БІБЛІОТЕКА» (табл. Б.10).

Таблиця Б.10

Специфікація таблиці *СoАuthorship* («СПІВАВТОРСТВО»)
№	Заголовок	Имя столбца	Тип	Длина	Ключ
1	Код книжки	BookCode	INTEGER		F
2	Код автора	AuthorCode	INTEGER		F

Б.12. Таблиця BookFunds («ТИПИ ФОНДІВ КНИЖОК»)

Відношення «ТИПИ ФОНДІВ КНИЖОК» і «ТИПИ ТЕЛЕФОНІВ» в логічній моделі БД «БІБЛІОТЕКА» мають однакову специфікацію. У фізичній моделі ми вже створили таблицю PhoneTypes («ТИПИ ТЕЛЕФОНІВ»). Його ключовий атрибут Code визначено на домені AllCode. Теж саме зробимо в таблиці BookFunds («ТИПИ ФОНДІВ КНИЖОК»). Різниця полягає в тому, що атрибут Name таблиці PhoneTypes визначений на домені AllNameType, а в таблиці BookFunds він буде визначений на домені AllName:

CREATE DOMAIN AllName AS CHAR(20)

NOT NULL;

Для домену був обраний символьний тип даних. Максимальна кількість символів в чарунці таблиці вказано у круглих дужках. Пропозиція NOT NULL робить обов'язковим введення значення в усі чарунки стовбця AllName.

У фізичній моделі БД «БІБЛІОТЕКА» відношенню «ТИПИ ФОНДІВ КНИЖОК» відповідає таблиця BookFunds. Всі домени для її створення визначені. Додамо таблицю BookFunds до фізичної моделі:

CREATE TABLE BookFunds

(Code AllCode,

Name AllName,

PRIMARY KEY (Code));

Ви можете бачити, що таблиця BookFunds повністю відповідає специфікації відносини «ТИПИ ФОНДІВ КНИЖОК» в логічній моделі БД «БІБЛІОТЕКА» (табл. Б.11).

Таблиця Б.11

Специфікація таблиці *BookFunds* («ТИПИ ФОНДІВ КНИЖОК»)
№	Заголовок	Имя столбца	Тип	Длина	Ключ
1	Код	Code	INTEGER		P
2	Найменування	Name	CHAR	20

Б.13. Таблиця BookInventoryNumbers («ІНВЕНТАРНІ НОМЕРИ КНИЖОК»)

Відношення «ІНВЕНТАРНІ НОМЕРИ КНИЖОК» завершує другу частину фізичної моделі БД «БІБЛІОТЕКА». Його атрибути визначені на доменах: AllCode, AllInventoryNumber і AllCost. Домен AllCode був визначений раніше перед створенням таблиці PhoneTypes. Створимо домени AllInventoryNumber і AllCost:

CREATE DOMAIN AllInventoryNumber AS INTEGER

CHECK(VALUE >= 10)

NOT NULL;

CREATE DOMAIN AllCost AS FLOAT

NOT NULL;

Обидва домена визначені на стандартних типах даних. INTEGER – це цілочислові значення. FLOAT – значення з плаваючою точкою. Для обох доменів використовується пропозиція NOT NULL. Воно забороняє залишати без значення чарунки стовпців таблиці. Суть пропозиції CHECK(VALUE >= 10) для домену AllInventoryNumber була описана при створенні домену AllNumber перед таблицею PasportData. Воно забороняє введення значень, які менші або дорівнюють 10. Джерелом таких обмежень зазвичай є результат аналізу бізнес-процесів. Це повинно бути узгоджено з замовником.

Тепер ми можемо додати у фізичну модель БД «БІБЛІОТКА» таблицю BookInventoryNumbers:

CREATE TABLE BookInventoryNumbers

(Code AllCode,

BookCode AllCode,

FundCode AllCode,

InventoryNumber AllInventoryNumber,

Cost AllCost,

PRIMARY KEY (Code),

FOREIGN KEY (BookCode) REFERENCES Books

ON DELETE CASCADE

ON UPDATE CASCADE,

FOREIGN KEY (FundCode) REFERENCES BookFunds

ON DELETE CASCADE

ON UPDATE CASCADE);

Пропозиції ON DELETE CASCADE та ON UPDATE CASCADE підтримують посилальну цілісність реляційної бази даних за допомогою каскадного видалення та оновлення значень зовнішніх ключів. Його робота описана раніше для таблиці CoAuthorship.

Залишається тільки додати, що таблиця BookInventoryNumbers повністю відповідає специфікації відношення «ІНВЕНТАРНІ НОМЕРИ КНИЖОК» в логічній моделі БД «БІБЛІОТЕКА» (табл. Б.12).

Таблиця Б.12

Специфікація таблиці *BookInventoryNumbers* («ІНВЕНТАРНІ НОМЕРИ КНИЖОК»)
№	Заголовок	Имя столбца	Тип	Ключ
1	Код	Code	INTEGER	P
2	Код книжки	BookCode	INTEGER	F
3	Код фонду	FundCode	INTEGER	F
4	Інвентарний номер	InventoryNumber	INTEGER
5	Вартість	Cost	FLOAT

Б.14. Таблиця BookGiveOutRecord («ОБЛІК ВИДАЧІ КНИЖОК»)

Нагадаємо, що логічну та фізичну моделі БД «БІБЛІОТЕКА» ми умовно розділили на дві частини. У першій частині описані відношення і таблиці з інформацією про людей. Друга частина містить інформацію про книги. Відношення «ОБЛІК ВИДАЧІ КНИЖОК» (логічна модель) або таблиця BookGiveOutRecord (фізична модель) пов'язує їх в одне ціле. Саме тут бібліотекари ведуть облік видачі книжок читачам.

Для визначення атрибутів відношення «ОБЛІК ВИДАЧІ КНИЖОК» використовується всього один домен: AllCode. Його ми створили перед таблицею PhoneTypes. Ще ряд атрибутів відношення визначені на стандартному типі даних: DATE. Тепер ми можемо закінчити фізичне моделювання БД «БІБЛІОТЕКА» створенням таблиці BookGiveOutRecord за допомогою оператора CREATE TABLE:

CREATE TABLE BookGiveOutRecord

(Code AllCode,

ReaderCode AllCode,

OutLibrarianCode AllCode,

InventoryCode AllCode,

IssueDate DATE NOT NULL,

ReturnDate DATE NOT NULL,

FactReturnDate DATE,

InLibrarianCode AllCode,

PRIMARY KEY (Code),

FOREIGN KEY (OutLibrarianCode) REFERENCES Librarians

ON DELETE CASCADE

ON UPDATE CASCADE,

FOREIGN KEY (InventoryCode) REFERENCES BookInventoryNumbers

ON DELETE CASCADE

ON UPDATE CASCADE,

FOREIGN KEY (InLibrarianCode) REFERENCES Librarians

ON DELETE CASCADE

ON UPDATE CASCADE);

Є одна невідповідність таблиці BookGiveOutRecord і відношення «ОБЛІК ВИДАЧІ КНИЖОК» в логічній моделі БД «БІБЛІОТЕКА». У таблиці не визначений зовнішній ключ ReaderCode. Це пов'язано з тим, що було прийнято рішення підтримувати посилальну цілісність між таблицями Readers і BookGiveOutRecord за допомогою тригерів. Ми хотіли показати, як в одній таблиці можна реалізувати різні механізми підтримки посилальної цілісності.

Визначення зовнішніх ключів для підтримки посилальної цілісності описані раніше для таблиць BookInventoryNumbers і CoAuthorship. Більш докладно про пропозиції ON DELETE CASCADE та ON UPDATE CASCADE дивіться в описі таблиці CoAuthorship. В іншому таблиця BookGiveOutRecord відповідає специфікації відношення «ОБЛІК ВИДАЧІ КНИЖОК» в логічній моделі БД «БІБЛІОТЕКА» (табл. Б.13).

Таблиця Б.13

Специфікація таблиці *BookGiveOutRecord* («ОБЛІК ВИДАЧІ КНИЖОК»)
№	Заголовок	Имя столбца	Тип	Ключ
1	Код	Code	INTEGER	P
2	Код читача	ReaderCode	INTEGER
3	Код бібліотекара, який видала книжку	OutLibrarianCode	INTEGER	F
4	Код інвентарного номера книжки	InventoryCode	INTEGER	F
5	Дата видачі книжки	IssueDate	DATE
6	Дата повернення книжки	ReturnDate	DATE
7	Дата фактичного повернення книжки	FactReturnDate	DATE
8	Код бібліотекара, який прийняв книжку	InLibrarianCode	INTEGER	F

Висновки

1. Порядок створення таблиць у фізичній моделі може не збігатися з порядком моделювання відносин в логічної моделі реляційної бази даних.

2. Назви об'єктів у фізичній моделі повинні збігатися з назвами об'єктів в логічній моделі або бути схожими на них.

3. Необхідно вибирати найбільш прості механізми підтримки цілісності сутностей і посилальної цілісності реляційної бази даних.

4. Використання більш складних механізмів підтримки цілісності сутностей і посилальної цілісності реляційної бази даних може бути виправдане тільки відсутністю можливості реалізувати вимоги замовника за допомогою більш простих механізмів.

5. Процес створення фізичної моделі БД «БІБЛІОТЕКА» наочно довів тісний зв'язок між логічним і фізичним моделюванням реляційних баз даних.

ДОДАТОК Б. Фізична модель БД «БІБЛІОТЕКА»

Б.1. Про фізичну модель

Б.2. База даних

Б.3. Домени

Б.4. Таблиця PhoneTypes («ТИПИ ТЕЛЕФОНІВ»)

Б.5. Таблиця PassportData («ПАСПОРТНІ ДАНІ»)

Б.6. Таблиця Librarians («БІБЛІОТЕКАРИ»)

Б.7. Таблиця Readers («ЧИТАЧІ»)

Б.8. Таблиця Phones («ТЕЛЕФОНИ»)

Б.9. Таблиця Books («КНИЖКИ»)

Б.10. Таблиця BookAuthors («АВТОРИ КНИЖОК»)

Б.11. Таблиця CoAuthorship («СПІВАВТОРСТВО»)

Б.12. Таблиця BookFunds («ТИПИ ФОНДІВ КНИЖОК»)

Б.13. Таблиця BookInventoryNumbers («ІНВЕНТАРНІ НОМЕРИ КНИЖОК»)

Б.14. Таблиця BookGiveOutRecord («ОБЛІК ВИДАЧІ КНИЖОК»)