Лабораторная работа создание баз данных. Лабораторная работа проектирование базы данных
 Скачать 1.01 Mb.
|
|
Содержание поля Имя поля Тип, длина Примечания Код книги (№ контракта) B_ID N(6) внешний ключ (к Books) Код автора A_ID N(4) внешний ключ (к Authors) Номер в списке A_NO N(1) обязательное поле Гонорар A_FEE N(3) процент от общего гонорара Таблица 1.6 – Схема отношения КНИГИ–РЕДАКТОРЫ (Editors) Содержание поля Имя поля Тип, длина Примечания Код книги (№ контракта) B_ID N(6) внешний ключ (к Books) Код редактора E_ID N(4) внешний ключ (к Employees) Таблица 1.7 – Схема отношения СТРОКИ ЗАКАЗА (Items) Содержание поля Имя поля Тип, длина Примечания Номер заказа O_ID N(6) внешний ключ (к Orders) Код книги (№ контракта) B_ID N(6) внешний ключ (к Books) Количество B_COUNT N(4) обязательное поле 1.5. Нормализация полученных отношений (до 3НФ) Нормализация – это процесс организации данных в базе данных, включающий создание таблиц и установление отношений между ними в соответствии с правилами, которые обеспечивают защиту данных и делают базу данных более гибкой, устраняя избыточность и несогласованные зависимости. Первая нормальная форма Отношение находится в 1НФ, если все его атрибуты являются простыми, все используемые домены должны содержать только скалярные значения. Не должно быть повторений строк в таблице. Для примера возьмем следующую таблицу 1.8. Таблица 1.8. Машины - модели Фирма Модели BMW M5, X5M, M1 Nissan GT-R Нарушение нормализации 1НФ происходит в моделях BMW, т.к. в одной ячейке содержится список из 3 элементов: M5, X5M, M1, т.е. он не является атомарным. Преобразуем таблицу к 1НФ как показано в таблице 1.9. Таблица 1.9. – Преобразование отношения Фирма Модели BMW M5 BMW X5M BMW M1 Nissan GT-R Вторая нормальная форма Отношение находится во 2НФ, если оно находится в 1НФ и каждый не ключевой атрибут неприводимо зависит от Первичного Ключа(ПК). Неприводимость означает, что в составе потенциального ключа отсутствует меньшее подмножество атрибутов, от которого можно также вывести данную функциональную зависимость. (простыми словами: все не ключевые атрибуты должны полностью зависеть от первичного ключа и не должно быть атрибутов, которые зависят от части ПК). Приведем пример в таблице 1.10. Таблица 1.10 –Пример работы с атрибутами для приведения отношения ко 2НФ Модель Фирма Цена Скидка M5 BMW 5000000 5% X5M BMW 9000000 5% M1 BMW 2500000 5% GT-R Nissan 5000000 10% Таблица находится в первой нормальной форме, но не во второй. Цена машины зависит от модели и фирмы (здесь составной первичный ключ). Скидка зависят от фирмы, то есть зависимость от первичного ключа неполная. Исправляется это путем декомпозиции на два отношения, в которых не ключевые атрибуты зависят от ПК. Нормализованные таблицы представлены под номером 1.11 и 1.12. Таблица 1.11 – Нормализованное отношение «Модель-Фирма» Модель Фирма Цена M5 BMW 5000000 X5M BMW 9000000 M1 BMW 2500000 GT-R Nissan 5000000 Таблица 1.12 – Нормализованное отношение «Фирма» Фирма Скидка BMW 5% Nissan 10% Третья нормальная форма Отношение находится в 3НФ, когда находится во 2НФ и каждый не ключевой атрибут нетранзитивно зависит от первичного ключа. Проще говоря, второе правило требует выносить все не ключевые поля, содержимое которых может относиться к нескольким записям таблицы в отдельные таблицы. Например, возьмем таблицу 1.13. Первичный ключ Таблица 1.13 – Отношение во 2НФ Модель Магазин Телефон BMW Риал-авто 87-33-98 Audi Риал-авто 87-33-98 Nissan Некст-авто 94-54-12 Таблица 1.13 находится во 2НФ, но не в 3НФ. В отношении атрибут «Модель» является первичным ключом. Личных телефонов у автомобилей нет, и телефон зависит исключительно от магазина. Таким образом, в отношении существуют следующие функциональные зависимости: Модель → Магазин, Магазин → Телефон, Модель → Телефон. Зависимость «Модель → Телефон» является транзитивной, следовательно, отношение не находится в 3НФ. В результате разделения исходного отношения получаются два отношения, находящиеся в 3НФ. Полученные отношения представлены в таблицах 1.14 и 1.15 Таблица 1.14 – Отношение «Модель» в 3НФ Модель Магазин BMW Риал-авто Audi Риал-авто Nissan Некст-авто Таблица 1.15 – Отношение «Магазин» в 3НФ Магазин Номер Риал-авто 87-33-98 Некст-авто 94-54-12 1.6. Нормализация отношений для БД «Издательская компания». 1.1.1. 1 НФ (нормальная форма) Для приведения таблиц к 1НФ требуется составить прямоугольные таблицы (один атрибут – один столбец) и разбить сложные атрибуты на простые, а многозначные атрибуты вынести в отдельные отношения. Примечание. В реальных БД сложные атрибуты разбиваются на простые, если: этого требует внешнее представление данных; в запросах поиск может осуществляться по отдельной части атрибута. Разделим атрибуты «Фамилия, имя, отчество» на три атрибута «Фамилия» «Имя», «Отчество» и «Паспортные данные» на атрибуты «Номер паспорта (уникальный)», «Дата выдачи» и «Кем выдан». Многозначный атрибут «Телефоны» для сотрудников компании следует сначала разделить на два – «Домашние телефоны» и «Рабочие телефоны». (Для авторов мы не будем различать домашние и рабочие телефоны). Затем нужно создать отдельные отношения с (нерабочими) телефонами для сотрудников (ТЕЛЕФОНЫ СОТРУДНИКОВ) и для авторов (ТЕЛЕФОНЫ АВТОРОВ). Атрибут «Рабочие телефоны» отношения СОТРУДНИКИ имеет неоднородные значения. Один из номеров телефонов – основной – определяется рабочим местом сотрудника (рассматриваются только стационарные телефоны). Наличие других номеров зависит от того, есть ли в том же помещении (комнате) другие сотрудники, имеющие стационарные телефоны. Можно добавить в отношение СОТРУДНИКИ атрибут Номер комнаты, а в атрибуте Рабочие телефоны хранить номер того телефона, который стоит на рабочем месте сотрудника. Дополнительные номера телефонов можно будет вычислить из других кортежей с таким же номером комнаты. Но в случае увольнения сотрудника мы потеряем сведения о номере рабочего телефона. Поэтому создадим новое отношение КОМНАТЫ и включим в него атрибуты Номер комнаты и Телефон. Так как в комнате может не быть телефона, первичный ключ нового отношения не определен (ПК не может содержать null–значения), но на этих атрибутах можно определить составной уникальный ключ. Связь между отношениями СОТРУДНИКИ и КОМНАТЫ реализуем через составной внешний ключ (Номер комнаты, Телефон). Значение внешнего ключа для каждого сотрудника будем брать из того кортежа, в котором хранится основной рабочий телефон этого сотрудника. 2 НФ (нормальная форма) В нашем случае составные первичные ключи имеют отношения СТРОКИ ЗАКАЗА, КНИГИ–АВТОРЫ и КНИГИ–РЕДАКТОРЫ. Не ключевые атрибуты этих отношений функционально полно зависят от первичных ключей. 3 НФ (нормальная форма) В отношении ЗАКАЗЫ атрибут Адрес заказчика зависит от атрибута Заказчик, а не от первичного ключа, поэтому адрес следует вынести в отдельное отношение ЗАКАЗЧИКИ. Но при этом первичным ключом нового отношения станет атрибут Заказчик, т.е. длинная символьная строка. Целесообразнее перенести в новое отношение атрибуты Заказчик и Адрес заказчика и ввести для него суррогатный ПК. Так как каждый заказчик может сделать несколько заказов, связь между отношениями ЗАКАЗЧИКИ и ЗАКАЗЫ будет 1:n и суррогатный ПК станет внешним ключом для отношения ЗАКАЗЫ. В отношении СОТРУДНИКИ атрибут Оклад зависит от атрибута Должность. Поступим с этой транзитивной зависимостью так же, как в предыдущем случае: создадим новое отношение ДОЛЖНОСТИ, перенесём в него атрибуты Должность и Оклад и введём суррогатный первичный ключ. В отношениях СОТРУДНИКИ и АВТОРЫ атрибуты Дата выдачи и Кем выдан зависят от атрибута Номер паспорта, а не от первичного ключа. Но если мы выделим их в отдельное отношение, то получившиеся связи будут иметь тип 1:1. Следовательно, декомпозиция нецелесообразна. В реальных базах данных после нормализации может проводиться денормализация. Она проводится с одной целью – повышение производительности БД. Рассмотрим некоторые запросы к нашей базе данных. Например, запрос на получение списка телефонов авторов или домашних телефонов сотрудников потребует в нормализованной БД соединения отношений. Пользователю безразлична форма представления этого списка: номера телефонов через запятую или в столбец. Поэтому мы откажемся от создания отдельных отношений с номерами телефонов, и вернёмся к варианту с многозначными полями. (Это не касается рабочих телефонов сотрудников). Другой запрос: как определяется, можно ли выполнить очередной заказ? Для каждой позиции заказа нужно просуммировать количество книг по выполненным заказам, получить остаток (тираж минус полученная сумма) и сравнить остаток с объёмом заказа. Такой расчёт может потребовать много времени, поэтому предлагается добавить в отношение КНИГИ производный атрибут Остаток тиража. Значение этого атрибута должно автоматически пересчитываться при установлении даты выполнения заказа. После проведённых преобразований схема БД выглядит так (рисунок 1.3): Рис. 1.3 – Окончательная схема РБД издательской компании Окончательные схемы отношений Окончательные схемы отношений базы данных с указанием ключей и других ограничений целостности приведены в табл. 1.8–1.17. Таблица 1.8 – Схема отношения ДОЛЖНОСТИ (Posts) Содержание поля Имя поля Тип, длина Примечания Код должности P_ID N(3) суррогатный первичный ключ Название должности P_POST C(30) обязательное поле Оклад P_SAL N(8,2) обязательное поле Таблица 1.9 – Схема отношения КОМНАТЫ (Rooms) Содержание поля Имя поля Тип, длина Примечания Номер комнаты R_NO N(3) составной первичный ключ Номер телефона R_TEL C(10) Таблица 1.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) Таблица 1.11 – Схема отношения ЗАКАЗЧИКИ (Customers) Содержание поля Имя поля Тип, длина Примечания Код заказчика C_ID N(4) суррогатный первичный ключ Заказчик C_NAME C(30) обязательное поле Адрес заказчика C_ADDR C(50) обязательное поле Таблица 1.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) многозначное поле Таблица 1.13 – Схема отношения КНИГИ (Books) Содержание поля Имя поля Тип, длина Примечания Номер контракта B_CONTRA CT N(6) первичный ключ Дата подписания контракта B_DATE D обязательное поле Менеджер B_MAN N(4) внешний ключ (к Employees) Название книги B_TITLE N(40) обязательное поле Цена B_PRICE N(6,2) цена экземпляра книги Затраты B_ADVANC E N(10,2) общая сумма затрат на книгу Авторский гонорар B_FEE N(8,2) общая сумма гонорара Дата выхода B_PUBL D Тираж B_CIRCUL N(5) Ответственный редактор B_EDIT N(4) внешний ключ (к Employees) Остаток тиража B_REST N(5) производное поле Таблица 1.14 – Схема отношения ЗАКАЗЫ (Orders) Содержание поля Имя поля Тип, длина Примечания Номер заказа O_ID N(6) первичный ключ Код заказчика O_COMPANY N(4) внешний ключ (к Customers) Дата поступления заказа O_DATE D обязательное поле Дата выполнения заказа O_READY D Таблица 1.15 – Схема отношения КНИГИ–АВТОРЫ (Titles) Содержание поля Имя поля Тип, длина Примечания Код книги (№ контракта) B_ID N(6) внешний ключ (к Books) Код автора A_ID N(4) внешний ключ (к Authors) Номер в списке A_NO N(1) обязательное поле Гонорар A_FEE N(3) процент от общего гонорара Таблица 1.16 – Схема отношения СТРОКИ ЗАКАЗА (Items) Содержание поля Имя поля Тип, длина Примечания Номер заказа O_ID N(6) внешний ключ (к Orders) Код книги (№ контракта) B_ID N(6) внешний ключ (к Books) Количество B_COUNT N(4) обязательное поле Таблица 1.17 – Схема отношения КНИГИ–РЕДАКТОРЫ (Editors) Содержание поля Имя поля Тип, длина Примечания Код книги (№ контракта) B_ID N(6) внешний ключ (к Books) Код редактора E_ID N(4) внешний ключ (к Employees) 1.7. Пример нормализации отношение №3 1 НФ (нормальная форма) В ходе логического моделирования на первом шаге предложено хранить данные в одном отношении, имеющем следующие атрибуты: СОТРУДНИКИ_ОТДЕЛЫ_ПРОЕКТЫ (Н_СОТР, ФАМ, Н_ОТД, ТЕЛ, Н_ПРО, ПРОЕКТ, Н_ЗАДАН) Где: Н_СОТР - табельный номер сотрудника ФАМ - фамилия сотрудника Н_ОТД - номер отдела, в котором числится сотрудник ТЕЛ - телефон сотрудника Н_ПРО - номер проекта, над которым работает сотрудник ПРОЕКТ - наименование проекта, над которым работает сотрудник Н_ЗАДАН - номер задания, над которым работает сотрудник Т.к. каждый сотрудник в каждом проекте выполняет ровно одно задание, то в качестве потенциального ключа отношения необходимо взять пару атрибутов {Н_СОТР, Н_ПРО}. В текущий момент состояние предметной области отражается следующими фактами: Сотрудник Иванов, работающий в 1 отделе, выполняет в первом проекте "Космос" задание 1 и во втором проекте "Климат" задание 1. Сотрудник Петров, работающий в 1 отделе, выполняет в первом проекте "Космос" задание 2. Сотрудник Сидоров, работающий во 2 отделе, выполняет в первом проекте "Космос" задание 3 и во втором проекте "Климат" задание 2. Это состояние отражается в таблице (курсивом выделены ключевые атрибуты): Таблица 1.18 – Отношение СОТРУДНИКИ_ОТДЕЛЫ_ПРОЕКТЫ Н_СОТР ФАМ Н_ОТД ТЕЛ Н_ПРО ПРОЕКТ Н_ЗАДАН 1 Иванов 1 11-22-33 1 Космос 1 1 Иванов 1 11-22-33 2 Климат 1 2 Петров 1 11-22-33 1 Космос 2 3 Сидоров 2 33-22-11 1 Космос 3 3 Сидоров 2 33-22-11 2 Климат 2 Определение функциональной зависимости Для правильного проектирования модели данных применяется метод нормализации отношений. Нормализация основана на понятии функциональной зависимости атрибутов отношения. Пусть - отношение. Множество атрибутов функционально зависимо от множества атрибутов ( функционально определяет ) тогда и только тогда, когда для любого состояния отношения для любых кортежей из того, что следует что (т.е. во всех кортежах, имеющих одинаковые значения атрибутов , значения атрибутов также совпадают в любом состоянии отношения ). Символически функциональная зависимость записывается Множество атрибутов называется детерминантом функциональной зависимости, а множество атрибутов называется зависимой частью. Замечание. Если атрибуты составляют потенциальный ключ отношения , то любой атрибут отношения функционально зависит от . В отношении СОТРУДНИКИ_ОТДЕЛЫ_ПРОЕКТЫ можно привести следующие примеры функциональных зависимостей: Зависимость атрибутов от ключа отношения: {Н_СОТР, Н_ПРО} ФАМ {Н_СОТР, Н_ПРО} Н_ОТД {Н_СОТР, Н_ПРО} ТЕЛ {Н_СОТР, Н_ПРО} ПРОЕКТ {Н_СОТР, Н_ПРО} Н_ЗАДАН Зависимость атрибутов, характеризующих сотрудника от табельного номера сотрудника: Н_СОТР ФАМ Н_СОТР Н_ОТД Н_СОТР ТЕЛ Зависимость наименования проекта от номера проекта: Н_ПРО ПРОЕКТ Зависимость номера телефона от номера отдела: Н_ОТД ТЕЛ 2 НФ (нормальная форма) Отношение СОТРУДНИКИ_ОТДЕЛЫ_ПРОЕКТЫ не находится в 2НФ, т.к. есть атрибуты, зависящие от части сложного ключа: Зависимость атрибутов, характеризующих сотрудника от табельного номера сотрудника является зависимостью от части сложного ключа: Н_СОТР ФАМ Н_СОТР Н_ОТД Н_СОТР ТЕЛ Зависимость наименования проекта от номера проекта является зависимостью от части сложного ключа: Н_ПРО ПРОЕКТ Для того, чтобы устранить зависимость атрибутов от части сложного ключа, нужно произвести декомпозицию отношения на несколько отношений. При этом те атрибуты, которые зависят от части сложного ключа, выносятся в отдельное отношение. Отношение СОТРУДНИКИ_ОТДЕЛЫ_ПРОЕКТЫ декомпозируем на три отношения - СОТРУДНИКИ_ОТДЕЛЫ, ПРОЕКТЫ, ЗАДАНИЯ. Отношение СОТРУДНИКИ_ОТДЕЛЫ (Н_СОТР, ФАМ, Н_ОТД, ТЕЛ): Функциональные зависимости: Зависимость атрибутов, характеризующих сотрудника от табельного номера сотрудника: Н_СОТР ФАМ Н_СОТР Н_ОТД Н_СОТР ТЕЛ Зависимость номера телефона от номера отдела: Н_ОТД ТЕЛ Таблица 1.19 – Отношение СОТРУДНИКИ_ОТДЕЛЫ Н_СОТР ФАМ Н_ОТД ТЕЛ 1 Иванов 1 11-22-33 2 Петров 1 11-22-33 3 Сидоров 2 33-22-11 Отношение ПРОЕКТЫ (Н_ПРО, ПРОЕКТ): Функциональные зависимости: Н_ПРО ПРОЕКТ Таблица 1.20 – Отношение ПРОЕКТЫ Н_ПРО ПРОЕКТ 1 Космос 2 Климат Отношение ЗАДАНИЯ (Н_СОТР, Н_ПРО, Н_ЗАДАН): Функциональные зависимости: {Н_СОТР, Н_ПРО} Н_ЗАДАН Таблица 1.21 – Отношение ЗАДАНИЯ Н_СОТР Н_ПРО Н_ЗАДАН 1 1 1 1 2 1 2 1 2 3 1 3 3 2 2 3НФ (нормальная форма) Отношение СОТРУДНИКИ_ОТДЕЛЫ не находится в 3НФ, т.к. имеется функциональная зависимость неключевых атрибутов (зависимость номера телефона от номера отдела): Н_ОТД ТЕЛ Для того, чтобы устранить зависимость неключевых атрибутов, нужно произвести декомпозицию отношения на несколько отношений. При этом те неключевые атрибуты, которые являются зависимыми, выносятся в отдельное отношение. Отношение СОТРУДНИКИ_ОТДЕЛЫ декомпозируем на два отношения - СОТРУДНИКИ, ОТДЕЛЫ. Отношение СОТРУДНИКИ (Н_СОТР, ФАМ, Н_ОТД): Функциональные зависимости: Зависимость атрибутов, характеризующих сотрудника от табельного номера сотрудника: Н_СОТР ФАМ Н_СОТР Н_ОТД Н_СОТР ТЕЛ Таблица 1.22 – Отношение СОТРУДНИКИ Н_СОТР ФАМ Н_ОТД 1 Иванов 1 2 Петров 1 3 Сидоров 2 Отношение ОТДЕЛЫ (Н_ОТД, ТЕЛ): Функциональные зависимости: Зависимость номера телефона от номера отдела: Н_ОТД ТЕЛ Таблица 1.23 – Отношение ОТДЕЛЫ Н_ОТД ТЕЛ 1 11-22-33 2 33-22-11 Обратим внимание на то, что атрибут Н_ОТД, не являвшийся ключевым в отношении СОТРУДНИКИ_ОТДЕЛЫ, становится потенциальным ключом в отношении ОТДЕЛЫ. Именно за счет этого устраняется избыточность, связанная с многократным хранением одних и тех же номеров телефонов. Вывод Реляционная модель, состоящая из четырех отношений СОТРУДНИКИ, ОТДЕЛЫ, ПРОЕКТЫ, ЗАДАНИЯ, находящихся в третьей нормальной форме, является адекватной описанной модели предметной области, и требует наличия только тех триггеров, которые поддерживают ссылочную целостность. Такие триггеры являются стандартными и не требуют больших усилий в разработке. Определение дополнительных ограничений целостности Перечислим ограничения целостности, которые не указаны в таблицах 8–17. Значения всех числовых атрибутов – больше 0 (или null, если атрибут необязателен). Область значений атрибута Sex отношения EMPLOYEES – символы «м» и «ж». Отношение ROOMS не имеет первичного ключа, но комбинация значений (R_no, Tel) уникальна. В отношении TITLES порядковые номера авторов на обложке одной книги должны идти подряд, начиная с 1. В отношении TITLES сумма процентов гонорара по одной книге равна 100. Ограничения (4,5) нельзя реализовать в схеме отношения. В реальных БД подобные ограничения целостности реализуются программно (через внешнее приложение или специальную процедуру контроля данных). 2. ЗАКЛЮЧЕНИЕ В данной лабораторной работе была дана краткая теоретическая информация проектирования баз данных. Были представлены этапы моделирования и описание реляционных отношений. В результате выполнения данной лабораторной работы студент получит практические навыки по проектированию БД и нормализации реляционных отношений. |