Нормализация полученных отношений до 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)