Головне меню

EN | RU | UK

На головну

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

Наверх сторінки
Додаток Б
Мета

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

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

© Куваєв Я.Г., 2005—2020.

Всі права захищені.

Вся інформація, яка розміщена на цьому веб-сайті, призначена тільки для персонального використання і не підлягає подальшому відтворенню і/або поширенню в будь-якій формі, інакше як за письмовим дозволом Автора.