Главное меню

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 поддерживают ссылочную целостность реляционной базы данных с помощью каскадного удаления и обновления значений внешних ключей. Его работа описана ранее для таблицы С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. Процесс создания физической модели БД «БИБЛИОТЕКА» наглядно доказал тесную связь между логическим и физическим моделированием реляционных баз данных.

© Куваев Я.Г., 2005—2023.

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

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