Нормализация полученных отношений до 3НФ
1НФ.Для приведения таблиц к 1НФ требуется составить прямоугольные таблицы (один атрибут – один столбец) и разбить сложные атрибуты на простые, а многозначные атрибуты вынести в отдельные отношения.
Примечание. В реальных БД сложные атрибуты разбиваются на простые, если:
1) этого требует внешнее представление данных;
2) в запросах поиск может осуществляться по отдельной части атрибута.
Разделим атрибуты Фамилия, имя, отчество на два атрибута Фамилия и Имя, отчество и Паспортные данные на атрибуты Номер паспорта (уникальный), Дата выдачи и Кем выдан.
Многозначный атрибут Телефоны для сотрудников компании следует сначала разделить на два – Домашние телефоны и Рабочие телефоны. (Для авторов мы не будем различать домашние и рабочие телефоны). Затем нужно создать отдельные отношения с (нерабочими) телефонами для сотрудников (ТЕЛЕФОНЫ СОТРУДНИКОВ) и для авторов (ТЕЛЕФОНЫ АВТОРОВ).
Атрибут Рабочие телефоны отношения СОТРУДНИКИ имеет неоднородные значения. Один из номеров телефонов – основной – определяется рабочим местом сотрудника (рассматриваются только стационарные телефоны). Наличие других номеров зависит от того, есть ли в том же помещении (комнате) другие сотрудники, имеющие стационарные телефоны. Можно добавить в отношение СОТРУДНИКИ атрибут Номер комнаты, а в атрибуте
Рабочие телефоны хранить номер того телефона, который стоит на рабочем месте сотрудника. Дополнительные номера телефонов можно будет вычислить из других кортежей с таким же номером комнаты. Но в случае увольнения сотрудника мы потеряем сведения о номере рабочего телефона.
Поэтому создадим новое отношение КОМНАТЫ и включим в него атрибуты Номер комнаты и Телефон. Так как в комнате может не быть телефона, первичный ключ нового отношения не определен (ПК не может содержать null–значения), но на этих атрибутах можно определить составной уникальный ключ. Связь между отношениями СОТРУДНИКИ и КОМНАТЫ реализуем через составной внешний ключ (Номер комнаты, Телефон). Значение внешнего ключа для каждого сотрудника будем брать из того кортежа, в котором хранится основной рабочий телефон этого сотрудника.
2НФ.В нашем случае составные первичные ключи имеют отношения СТРОКИ ЗАКАЗА, КНИГИ–АВТОРЫ и КНИГИ–РЕДАКТОРЫ. Неключевые атрибуты этих отношений функционально полно зависят от первичных ключей.
3НФ. В отношении ЗАКАЗЫ атрибут Адрес заказчика зависит от атрибута Заказчик, а не от первичного ключа, поэтому адрес следует вынести в отдельное отношение ЗАКАЗЧИКИ. Но при этом первичным ключом нового отношения станет атрибут Заказчик, т.е. длинная символьная строка. Целесообразнее перенести в новое отношение атрибуты Заказчик и Адрес заказчика и ввести для него суррогатный ПК. Так как каждый заказчик может сделать несколько заказов, связь между отношениями ЗАКАЗЧИКИ и ЗАКАЗЫ будет 1:n и суррогатный ПК станет внешним ключом для отношения ЗАКАЗЫ.
В отношении СОТРУДНИКИ атрибут Оклад зависит от атрибута Должность. Поступим с этой транзитивной зависимостью так же, как в предыдущем случае: создадим новое отношение ДОЛЖНОСТИ, перенесём в него атрибуты Должность и Оклад и введём суррогатный первичный ключ.
В отношениях СОТРУДНИКИ и АВТОРЫ атрибуты Дата выдачи и Кем выдан зависят от атрибута Номер паспорта, а не от первичного ключа. Но если мы выделим их в отдельное отношение, то получившиеся связи будут иметь тип 1:1. Следовательно, декомпозиция нецелесообразна. Рассмотрим некоторые запросы к нашей базе данных.
Например, запрос на получение списка телефонов авторов или домашних телефонов сотрудников потребует в нормализованной БД соединения отношений. Пользователю безразлична форма представления этого списка: номера телефонов через запятую или в столбец. Поэтому мы откажемся от создания отдельных отношений с номерами телефонов, и вернёмся к варианту с многозначными полями. (Это не касается рабочих телефонов сотрудников).
Другой запрос: как определяется, можно ли выполнить очередной заказ? Для каждой позиции заказа нужно просуммировать количество книг по выполненным заказам, получить остаток (тираж минус полученная сумма) и сравнить остаток с объёмом заказа. Такой расчёт может потребовать много времени, поэтому предлагается добавить в отношение КНИГИ производный атрибут Остаток тиража. Значение этого атрибута должно автоматически пересчитываться при установлении даты выполнения заказа.
После проведённых преобразований схема БД выглядит так (рисунок 13):
Рисунок 13 Окончательная схема РБД издательской компании
Окончательные схемы отношений базы данных с указанием ключей и других ограничений целостности приведены в табл. 14–23.
Таблица 14. Схема отношения ДОЛЖНОСТИ (Posts)
| Содержание поля | Имя поля | Тип, длина | Примечания |
| Код должности | P_ID | N(3) | суррогатный первичный ключ |
| Название должности | P_POST | C(30) | обязательное поле |
| Оклад | P_SAL | N(8,2) | обязательное поле |
Таблица 15. Схема отношения КОМНАТЫ (Rooms)
| Содержание поля | Имя поля | Тип, длина | Примечания |
| Номер комнаты | R_NO | N(3) | обязательное поле |
| Номер телефона | R_TEL | C(10) |
Таблица 16. Схема отношения СОТРУДНИКИ (Employees)
| Содержание поля | Имя поля | Тип, длина | Примечания |
| Табельный номер | E_ID | N(4) | первичный ключ |
| Фамилия | E_FNAME | C(20) | обязательное поле |
| Имя, отчество | E_LNAME | С(30) | обязательное поле |
| Дата рождения | E_BORN | D | |
| Пол | E_SEX | C(1) | обязательное поле |
| Код должности | E_POST | N(3) | внешний ключ (к Posts) |
| Номер комнаты | E_ROOM | N(3) | составной внешний ключ (к Rooms) |
| Номер телефона | E_TEL | C(10) | |
| ИНН | E_INN | С(12) | обязательное поле |
| Номер паспорта | E_PASSP | C(12) | обязательное поле |
| Кем выдан паспорт | E_ORG | С(30) | обязательное поле |
| Дата выдачи паспорта | E_PDATE | D | обязательное поле |
| Адрес | E_ADDR | C(50) |
Таблица 17 Схема отношения ЗАКАЗЧИКИ (Customers)
| Содержание поля | Имя поля | Тип, длина | Примечания |
| Код заказчика | C_ID | N(4) | суррогатный первичный ключ |
| Заказчик | C_NAME | C(30) | обязательное поле |
| Адресзаказчика | C_ADDR | C(50) | обязательное поле |
Таблица 18. Схема отношения АВТОРЫ (Authors)
| Содержание поля | Имя поля | Тип, длина | Примечания |
| Код автора | A_ID | N(4) | суррогатный ключ |
| Фамилия | A_FNAME | C(20) | обязательное поле |
| Имя, отчество | A_LNAME | С(30) | обязательное поле |
| ИНН | A_INN | С(12) | |
| Номер паспорта | A_PASSP | C(12) | обязательное поле |
| Кем выдан паспорт | A_ORG | С(30) | обязательное поле |
| Дата выдачи паспорта | A_PDATE | D | обязательное поле |
| Адрес | A_ADDR | C(50) | обязательное поле |
| Телефоны | A_TEL | C(30) | многозначное поле |
Таблица 19. Схема отношения КНИГИ (Books)
| Содержание поля | Имя поля | Тип, длина | Примечания |
| Номер контракта | B_CONTRACT | N(6) | первичный ключ |
| Дата подписания контракта | B_DATE | D | обязательное поле |
| Менеджер | B_MAN | N(4) | внешний ключ (к Employees) |
| Название книги | B_TITLE | N(40) | обязательное поле |
| Цена | B_PRICE | N(6,2) | цена экземпляра книги |
| Затраты | B_ADVANCE | N(10,2) | общая сумма затрат на книгу |
| Авторский гонорар | B_FEE | N(8,2) | общая сумма гонорара |
| Дата выхода | B_PUBL | D | |
| Тираж | B_CIRCUL | N(5) | |
| Ответственный редактор | B_EDIT | N(4) | внешний ключ (к Employees) |
| Остаток тиража | B_REST | N(5) | производное поле |
Таблица 20. Схема отношения ЗАКАЗЫ (Orders)
| Содержание поля | Имя поля | Тип, длина | Примечания |
| Номер заказа | O_ID | N(6) | первичный ключ |
| Код заказчика | O_COMPANY | N(4) | внешний ключ (к Customers) |
| Дата поступления заказа | O_DATE | D | обязательное поле |
| Дата выполнения заказа | O_READY | D |
Таблица 21. Схема отношения КНИГИ–АВТОРЫ (Titles)
| Содержание поля | Имя поля | Тип, длина | Примечания |
| Код книги (№ контракта) | B_ID | N(6) | внешний ключ (к Books) |
| Код автора | A_ID | N(4) | внешний ключ (к Authors) |
| Номер в списке | A_NO | N(1) | обязательное поле |
| Гонорар | A_FEE | N(3) | процент от общего гонорара |
Таблица 22. Схема отношения СТРОКИ ЗАКАЗА (Items)
| Содержание поля | Имя поля | Тип, длина | Примечания |
| Номер заказа | O_ID | N(6) | внешний ключ (к Orders) |
| Код книги (№ контракта) | B_ID | N(6) | внешний ключ (к Books) |
| Количество | B_COUNT | N(4) | обязательное поле |
Таблица 23. Схема отношения КНИГИ–РЕДАКТОРЫ (Editors)