Реляционные базы данных

Приложение Б

Б.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 поддерживают ссылочную целостность реляционной базы данных с помощью каскадного удаления и обновления значений внешних ключей. Его работа описана ранее для таблицы СoАuthorship.

Остаётся только добавить, что таблица 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 («УЧЕТ ВЫДАЧИ КНИГ»)