Потенциальными ключами отношения АВТОРЫ являются атрибуты
Паспортные данные и ИНН. Первый хранится как длинная строка, а последний по условиям предметной области не является обязательным. Поэтому для авторов необходимо ввести суррогатный ключ – A_id. Книги можно идентифицировать по атрибуту Контракт: его номер обязателен и уникален. Потенциальные ключи отношения СОТРУДНИКИ – атрибуты ИНН, Паспортные данные, Табельный номер, причём все они обязательные. Табельный номер занимает меньше памяти, чем ИНН, поэтому он и будет первичным ключом. Кортежи отношения ЗАКАЗЫ можно идентифицировать ключом Номер заказа.

Потенциальными ключами вспомогательных отношений являются комбинации первичных ключей соответствующих базовых отношений.

Отношения приведены в табл. 1-7. Для каждого отношения указаны атрибуты с их внутренним названием, типом и длиной. Типы данных обозначаются так: N – числовой, C – символьный, D – дата (последний имеет стандартную длину, зависящую от СУБД, поэтому она не указывается).

Таблица 1. Схема отношения СОТРУДНИКИ (Employees)

Содержание поля	Имя поля	Тип, длина	Примечания
Табельный номер	E_ID	N(4)	первичный ключ
Фамилия, имя, отчество	E_NAME	C(50)	обязательное поле
Дата рождения	E_BORN	D
Пол	E_SEX	C(1)	обязательное поле
Паспортные данные	E_PASSP	C(50)	обязательное поле
ИНН	E_INN	N(12)	обязательное уникальное поле
Должность	E_POST	C(30)	обязательное поле
Оклад	E_SALARY	N(8,2)	обязательное поле
Адрес	E_ADDR	C(50)
Телефоны	E_TEL	C(30)	многозначное поле

Таблица 2. Схема отношения КНИГИ (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)

Таблица 3. Схема отношения АВТОРЫ (Authors)

Содержание поля	Имя поля	Тип, длина	Примечания
Код автора	A_ID	N(4)	суррогатный первичный ключ
Фамилия, имя, отчество	A_NAME	C(50)	обязательное поле
Паспортные данные	A_PASSP	C(50)	обязательное поле
ИНН	A_INN	N(12)	уникальное поле
Адрес	A_ADDR	C(50)	обязательное поле
Телефоны	A_TEL	C(30)	многозначное поле

---

Таблица 4. Схема отношения ЗАКАЗЫ (Orders)

Содержание поля	Имя поля	Тип, длина	Примечания
Номер заказа	O_ID	N(6)	первичный ключ
Заказчик	O_COMPANY	С(40)	обязательное поле
Дата поступления заказа	O_DATE	D	обязательное поле
Адрес заказчика	O_ADDR	C(50)	обязательное поле
Дата выполнения заказа	O_READY	D

Таблица 5. Схема отношения КНИГИ–АВТОРЫ (Titles)

Содержание поля	Имя поля	Тип, длина	Примечания
Код книги (№ контракта)	B_ID	N(6)	внешний ключ (к Books)
Код автора	A_ID	N(4)	внешний ключ (к Authors)
Номер в списке	A_NO	N(1)	обязательное поле
Гонорар	A_FEE	N(3)	процент от общего гонорара

Таблица 6. Схема отношения КНИГИ–РЕДАКТОРЫ (Editors)

Содержание поля	Имя поля	Тип, длина	Примечания
Код книги (№ контракта)	B_ID	N(6)	внешний ключ (к Books)
Код редактора	E_ID	N(4)	внешний ключ (к Employees)

Таблица 7. Схема отношения СТРОКИ ЗАКАЗА (Items)

Содержание поля	Имя поля	Тип, длина	Примечания
Номер заказа	O_ID	N(6)	внешний ключ (к Orders)
Код книги (№ контракта)	B_ID	N(6)	внешний ключ (к Books)
Количество	B_COUNT	N(4)	обязательное поле

3.3. Нормализация полученных отношений (до 4НФ)

1НФ. Для приведения таблиц к 1НФ требуется составить прямоугольные таблицы (один атрибут – один столбец) и разбить сложные атрибуты на простые, а многозначные атрибуты вынести в отдельные отношения.

Примечание. В реальных БД сложные атрибуты разбиваются на простые, если:

а) этого требует внешнее представление данных;

б) в запросах поиск может осуществляться по отдельной части атрибута.

Разделим атрибуты Фамилия, имя, отчество на два атрибута Фамилия и Имя, отчество и Паспортные данные на атрибуты Номер паспорта (уникальный), Дата выдачи иКем выдан.

Многозначный атрибут Телефоны для сотрудников компании следует сначала разделить на два – Домашние телефоны и Рабочие телефоны. (Для авторов мы не будем различать домашние и рабочие телефоны). Затем нужно создать отдельные отношения с (нерабочими) телефонами для сотрудников (ТЕЛЕФОНЫ СОТРУДНИКОВ) и для авторов (ТЕЛЕФОНЫ АВТОРОВ).

Атрибут Рабочие телефоны отношения СОТРУДНИКИ имеет неоднородные значения. Один из номеров телефонов – основной – определяется рабочим местом сотрудника (рассматриваются только стационарные телефоны). Наличие других номеров зависит от того, есть ли в том же помещении (комнате) другие сотрудники, имеющие стационарные телефоны. Можно добавить в отношение СОТРУДНИКИ атрибут Номер комнаты, а в атрибуте
Рабочие телефоны хранить номер того телефона, который стоит на рабочем месте сотрудника. Дополнительные номера телефонов можно будет вычислить из других кортежей с таким же номером комнаты. Но в случае увольнения сотрудника мы потеряем сведения о номере рабочего телефона.

---

Поэтому создадим новое отношение КОМНАТЫ и включим в него атрибуты Номер комнаты и Телефон. Так как в комнате может не быть телефона, первичный ключ нового отношения не определен (ПК не может содержать null–значения), но на этих атрибутах можно определить составной уникальный ключ. Связь между отношениямиСОТРУДНИКИ и КОМНАТЫ реализуем через составной внешний ключ (Номер комнаты, Телефон). Значение внешнего ключа для каждого сотрудника будем брать из того кортежа, в котором хранится основной рабочий телефон этого сотрудника.

2НФ. В нашем случае составные первичные ключи имеют отношения СТРОКИ ЗАКАЗА, КНИГИ–АВТОРЫ и КНИГИ–РЕДАКТОРЫ. Неключевые атрибуты этих отношений функционально полно зависят от первичных ключей.

3НФ. В отношении ЗАКАЗЫ атрибут Адрес заказчика зависит от атрибута Заказчик, а не от первичного ключа, поэтому адрес следует вынести в отдельное отношениеЗАКАЗЧИКИ. Но при этом первичным ключом нового отношения станет атрибут Заказчик, т.е. длинная символьная строка. Целесообразнее перенести в новое отношение атрибуты Заказчик и Адрес заказчика и ввести для него суррогатный ПК. Так как каждый заказчик может сделать несколько заказов, связь между отношениями ЗАКАЗЧИКИ иЗАКАЗЫ будет 1:n и суррогатный ПК станет внешним ключом для отношения ЗАКАЗЫ .

В отношении СОТРУДНИКИ атрибут Оклад зависит от атрибута Должность. Поступим с этой транзитивной зависимостью так же, как в предыдущем случае: создадим новое отношение ДОЛЖНОСТИ, перенесём в него атрибуты Должность и Оклад и введём суррогатный первичный ключ.

В отношениях СОТРУДНИКИ и АВТОРЫ атрибуты Дата выдачи и Кем выдан зависят от атрибута Номер паспорта, а не от первичного ключа. Но если мы выделим их в отдельное отношение, то получившиеся связи будут иметь тип 1:1. Следовательно, декомпозиция нецелесообразна.

4НФ. Отношения данного примера не нарушают 4НФ, т.к. не содержат нетривиальных многозначных зависимостей.

В реальных базах данных после нормализации может проводиться денормализация. Она проводится с одной целью – повышение производительности БД. Рассмотрим некоторые запросы к нашей базе данных.

Например, запрос на получение списка телефонов авторов или домашних телефонов сотрудников потребует в нормализованной БД соединения отношений. Пользователю безразлична форма представления этого списка: номера телефонов через запятую или в столбец. Поэтому мы откажемся от создания отдельных отношений с номерами телефонов, и вернёмся к варианту с многозначными полями. (Это не касается рабочих телефонов сотрудников).

Другой запрос: как определяется, можно ли выполнить очередной заказ? Для каждой позиции заказа нужно просуммировать количество книг по выполненным заказам, получить остаток (тираж минус полученная сумма) и сравнить остаток с объёмом заказа. Такой расчёт может потребовать много времени, поэтому предлагается добавить в отношение КНИГИ производный атрибут Остаток тиража. Значение этого атрибута должно автоматически пересчитываться при установлении даты выполнения заказа.

---

После проведённых преобразований схема БД выглядит так (рис. 9):

Рис.9. Окончательная схема РБД издательской компании

Окончательные схемы отношений базы данных с указанием ключей и других ограничений целостности (или лингвистическое описание) приведены в табл. 8–17.

Таблица 8. Схема отношения ДОЛЖНОСТИ (Posts)

Содержание поля	Имя поля	Тип, длина	Примечания
Код должности	P_ID	N(3)	суррогатный первичный ключ
Название должности	P_POST	C(30)	обязательное поле
Оклад	P_SAL	N(8,2)	обязательное поле

Таблица 9. Схема отношения КОМНАТЫ (Rooms)

Содержание поля	Имя поля	Тип, длина	Примечания
Номер комнаты	R_NO	N(3)	обязательное поле
Номер телефона	R_TEL	C(10)

Таблица 10. Схема отношения СОТРУДНИКИ (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)

Таблица 11. Схема отношения ЗАКАЗЧИКИ (Customers)

Содержание поля	Имя поля	Тип, длина	Примечания
Код заказчика	C_ID	N(4)	суррогатный первичный ключ
Заказчик	C_NAME	C(30)	обязательное поле
Адрес заказчика	C_ADDR	C(50)	обязательное поле

Таблица 12. Схема отношения АВТОРЫ (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)	многозначное поле

Таблица 13. Схема отношения КНИГИ (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)	производное поле

---

Таблица 14. Схема отношения ЗАКАЗЫ (Orders)

Содержание поля	Имя поля	Тип, длина	Примечания
Номер заказа	O_ID	N(6)	первичный ключ
Код заказчика	O_COMPANY	N(4)	внешний ключ (к Customers)
Дата поступления заказа	O_DATE	D	обязательное поле
Дата выполнения заказа	O_READY	D

Таблица 15. Схема отношения КНИГИ–АВТОРЫ (Titles)

Содержание поля	Имя поля	Тип, длина	Примечания
Код книги (№ контракта)	B_ID	N(6)	внешний ключ (к Books)
Код автора	A_ID	N(4)	внешний ключ (к Authors)
Номер в списке	A_NO	N(1)	обязательное поле
Гонорар	A_FEE	N(3)	процент от общего гонорара

Таблица 16. Схема отношения СТРОКИ ЗАКАЗА (Items)

Содержание поля	Имя поля	Тип, длина	Примечания
Номер заказа	O_ID	N(6)	внешний ключ (к Orders)
Код книги (№ контракта)	B_ID	N(6)	внешний ключ (к Books)
Количество	B_COUNT	N(4)	обязательное поле

Таблица 17. Схема отношения КНИГИ–РЕДАКТОРЫ (Editors)

Содержание поля	Имя поля	Тип, длина	Примечания
Код книги (№ контракта)	B_ID	N(6)	внешний ключ (к Books)
Код редактора	E_ID	N(4)	внешний ключ (к Employees)

3.3. Определение дополнительных ограничений целостности

Перечислим ограничения целостности, которые не указаны в табл. 8–17.

1. Значения всех числовых атрибутов – больше 0 (или null, если атрибут необязателен).

2. Область значений атрибута Sex отношения EMPLOYEES – символы 'м' и 'ж'.

3. Отношение ROOMS не имеет первичного ключа, но комбинация значений (R_no, Tel) уникальна.

4. В отношении TITLES порядковые номера авторов на обложке одной книги должны идти подряд, начиная с 1.

5. В отношении TITLES сумма процентов гонорара по одной книге равна 100.

Ограничения (4,5) нельзя реализовать в схеме отношения. В реальных БД подобные ограничения целостности реализуются программно (через внешнее приложение или специальную процедуру контроля данных).

3.4. Описание групп пользователей и прав доступа

Опишем для каждой группы пользователей права доступа к каждой таблице и к каждому полю (атрибуту).

1. Администратор БД: имеет доступ ко всем данным (по записи), может изменять структуру базы данных и связи между отношениями. Устанавливает права доступа для всех остальных групп.

2. Представители администрации компании: имеют доступ по чтению ко всем данным и доступ по записи к отношениям POSTS, ROOMS и EMPLOYEES.

3. Менеджеры: имеет доступ по чтению ко всем данным, кроме отношения POSTS. Имеют доступ по записи к отношениям AUTHORS, CUSTOMERS, BOOKS, EDITORS, TITLES,ORDERS, ITEMS.

4. Редакторы: имеют доступ по чтению к следующим отношениям:

• AUTHORS, кроме полей A_passp, A_org, A_pdate, A_INN(паспортные данные и ИНН).

• BOOKS, кроме полей B_advance, B_fee (затраты и гонорар).

• EDITORS.

• TITLES.

---

Смотрите также файлы

• УМК_Практические работы.pdf

• Требования к СУБД.docx

• Теория информации КР 2017.pdf

• Темы.docx

• Темы ВКР по ПИ.doc