Практическое занятие № 2. Нормализация реляционной БД, освоение принципов проектирования БД Цель: спроектировать БД для выбранной предметной области согласно примеру, представленном в методическом указании. Провести нормализацию (до 3 нормальной формы). 1.1. Задание Спроектировать БД для выбранной предметной области согласно примеру, представленному в пункте 2. Провести нормализацию (до 3 нормальной формы). Проектирование осуществляется при помощи векторного графического редактора, редактора диаграмм и блок схем – Microsoft Visio (можно другой графический редактор или даже текстовый редактор). После нормализации количество таблиц должно не превышать 7, желательно 5 таблиц. 2. Провести инфологическое проектирование проанализировав предметную область согласно варианту задания. Разработать диаграмму «Сущность-связь» . В результате в отчёт идут две диаграммы (как в примере на рис. 1.2 и на рис. 1.4). 3. Осуществить процесс логического проектирования, подробно расписав процесс преобразования диаграммы «Сущность-связь» в схему отношений. Учитывая знания, полученные по нормализации отношений. В результате у Вас должны получиться схемы отношений, представленные в табличном виде (как в примере таблицы с 1.8 по 1.17). 4. Подготовить отчет о проделанной работе. Структура отчета: - титульный лист; - задание; - описание процесса проектирования (инфологическое проектирование и логическое проектирование, аналогично примеру, представленному в данном методическому указании.); - заключение. Варианты заданий 13 прокат 1.3. Теоретический базис Проектирование БД – одна из наиболее сложных и ответственных задач, связанных с созданием информационной системы (ИС). В результате её решения должны быть определены содержание БД, эффективный для всех её будущих пользователей способ организации данных и инструментальные средства управления данными. Основная цель процесса проектирования БД состоит в получении такого проекта, который удовлетворяет следующим требованиям: - корректность схемы БД, т.е. база должна быть гомоморфным образом моделируемой предметной области, где каждому объекту предметной области соответствуют данные в памяти ЭВМ, а каждому процессу – адекватные процедуры обработки данных. - обеспечение ограничений (на объёмы внешней и оперативной памяти и другие ресурсы вычислительной системы). - эффективность функционирования (соблюдение ограничений на время реакции системы на запрос и обновление данных). - защита данных (от аппаратных и программных сбоев и несанкционированного доступа). - простота и удобство эксплуатации. - гибкость, т.е. возможность развития и адаптации к изменениям предметной области и/или требований пользователей. Этапы проектирования базы данных Процесс проектирования включает в себя следующие этапы: - инфологическое проектирование; - определение требований к операционной обстановке, в которой будет функционировать информационная система; - выбор системы управления базой данных (СУБД) и других инструментальных программных средств; - логическое проектирование БД; - физическое проектирование БД. 1.4. Пример проектирования реляционной базы данных В качестве примера возьмем базу данных компании, которая занимается издательской деятельностью. 1.4.1. Инфологическое проектирование 1.4.1.1. Анализ предметной области База данных создаётся для информационного обслуживания редакторов, менеджеров и других сотрудников компании. БД должна содержать данные о сотрудниках компании, книгах, авторах, финансовом состоянии компании и предоставлять возможность получать разнообразные отчёты. В соответствии с предметной областью система строится с учётом следующих особенностей: - каждая книга издаётся в рамках контракта; - книга может быть написана несколькими авторами; - контракт подписывается одним менеджером и всеми авторами книги; - каждый автор может написать несколько книг (по разным контрактам); - порядок, в котором авторы указаны на обложке, влияет на размер гонорара; - если сотрудник является редактором, то он может работать одновременно над несколькими книгами; - у каждой книги может быть несколько редакторов, один из них – ответственный редактор; - каждый заказ оформляется на одного заказчика; - в заказе на покупку может быть перечислено несколько книг. Для инфологического проектирования воспользуемся методом «сущность-связь». Для того, чтобы представить, как устроена предметная область нужно задать множество объектов реального мира (главная проблема что считать объектом). Объект – семантическое понятие, которое может быть полезно при обсуждении устройств реального мира. Сущность реального мира – объекты – не обязательно материальны – важно понятие существенно и различимо для других. Между объектами могут возникать связи трех видов: - один к одному 1:1 (пациент: место в палате); - один к многим 1:n и многие к одному n:1; - многие ко многим n:n (пациент : хирург). При построении моделей используются следующие геометрические фигуры: В настоящее время существует большое множество прикладных программ для создания графического представления структуры БД. При этом могут быть использованы как специализированные средства (например, Visio), так и средства построения графических образов (Umbrello, OO Draw). Либо использовать облачный сервис https://www.draw.io/. Выделим базовые сущности этой предметной области: - Сотрудники компании. Атрибуты сотрудников – ФИО, табельный номер, пол, дата рождения, паспортные данные, ИНН, должность, оклад, домашний адрес и телефоны. Для редакторов необходимо хранить сведения о редактируемых книгах; для менеджеров – сведения о подписанных контрактах. - Авторы. Атрибуты авторов – ФИО, ИНН (индивидуальный номер налогоплательщика), паспортные данные, домашний адрес, телефоны. Для авторов необходимо хранить сведения о написанных книгах. - Книги. Атрибуты книги – авторы, название, тираж, дата выхода, цена одного экземпляра, общие затраты на издание, авторский гонорар. - Контракты будем рассматривать как связь между авторами, книгами и менеджерами. Атрибуты контракта – номер, дата подписания и участники. Для отражения финансового положения компании в системе нужно учитывать заказы на книги. Для заказа необходимо хранить номер заказа, заказчика, адрес заказчика, дату поступления заказа, дату его выполнения, список заказанных книг с указанием количества экземпляров. ER–диаграмма издательской компании приведена на рисунке ниже (базовые сущности на рисунках выделены полужирным шрифтом). 1.4.2. Логическое проектирование реляционной БД 1.4.2.1. Преобразование ER–диаграммы в схему базы данных База данных создаётся на основании схемы базы данных. Для преобразования ER–диаграммы в схему БД приведём уточнённую ER– диаграмму, содержащая атрибуты сущностей. Преобразование ER–диаграммы в схему БД выполняется путем сопоставления каждой сущности и каждой связи, имеющей атрибуты, отношения (таблицы БД). Будем использовать обозначения, представленные на рисунке 1.3. Рис.1.3 – Обозначения, используемые на схеме базы данных Полученная схема реляционной БД приведена на рисунок 1.4. Рис. 1.4 – Схема реляционной БД, полученная из ER–диаграммы издательской компании На схеме (рисунок 1.4) есть связь типа 1:1 – обязательная связь между КНИГАМИ и КОНТРАКТАМИ. Такие отношения следует объединять в одно. Дополнительный эффект от объединения этих отношений – слияние связей авторы–контракты и авторы–книги: ведь в нашем случае контракт заключается именно для написания книги. Связь типа 1:n (один-ко-многим) между отношениями реализуется через внешний ключ. Ключ вводится для того отношения, к которому осуществляется множественная связь (КНИГИ). Связь редактировать между отношениями КНИГИ и СОТРУДНИКИ принадлежит к типу n:m (многие-ко-многим). Этот тип связи реализуется через вспомогательное отношение, которое является соединением первичных ключей соответствующих отношений. Бинарная связь между отношениями не может быть обязательной для обоих отношений. После объединения сущностей КНИГИ и КОНТРАКТЫ остаётся три связи, обязательные для всех участников: между авторами и книгами и между заказами и строками заказов. Такой тип связи означает, что, например, прежде чем добавить новый заказ в отношение ЗАКАЗЫ, нужно добавить новую строку в отношение СТРОКИ ЗАКАЗА, и наоборот. Поэтому для такой связи необходимо снять с одной стороны условие обязательности. Так как все эти связи будут реализованы с помощью внешнего ключа, снимем условие обязательности связей для отношений, содержащих первичные ключи. Уточнённая схема реляционной БД издательской компании приведена на рисунке 1.5. Схема на рисунке 1.5 содержит цикл «сотрудники–книги– сотрудники». Цикл допустим только в том случае, если связи, входящие в него, независимы друг от друга. Примем для нашей предметной области, что ответственный редактор книги может являться также просто редактором этой же книги или не входить в число редакторов. При этом цикл не приводит к нарушению логической целостности данных. 1.4.2.2. Составление реляционных отношений Каждое реляционное отношение соответствует одной сущности (объекту предметной области) и в него вносятся все атрибуты сущности. Для каждого отношения необходимо определить первичный ключ и внешние ключи (если они есть). В том случае, если базовое отношение не имеет потенциальных ключей, вводится суррогатный первичный ключ, который не несёт смысловой нагрузки и служит только для идентификации записей. Примечание: суррогатный первичный ключ также может вводиться в тех случаях, когда потенциальный ключ имеет большой размер (например, длинная символьная строка) или является составным (не менее трёх атрибутов). Потенциальными ключами отношения АВТОРЫ являются атрибуты Паспортные данные и ИНН. Первый хранится как длинная строка, а последний по условиям предметной области не является обязательным. Поэтому для авторов необходимо ввести суррогатный ключ – A_id. Книги можно идентифицировать по атрибуту Контракт: его номер обязателен и уникален. Потенциальные ключи отношения СОТРУДНИКИ – атрибуты ИНН, Паспортные данные, Табельный номер, причём все они обязательные. Табельный номер занимает меньше памяти, чем ИНН, поэтому он и будет первичным ключом. Кортежи отношения ЗАКАЗЫ можно идентифицировать ключом Номер заказа. Потенциальными ключами вспомогательных отношений являются комбинации первичных ключей соответствующих базовых отношений. Отношения приведены в таблице 1.1-1.7. Для каждого отношения указаны атрибуты с их внутренним названием, типом и длиной. Типы данных обозначаются так: N – числовой, C – символьный, D – дата (последний имеет стандартную длину, зависящую от СУБД, поэтому она не указывается). 1.4.3. Нормализация полученных отношений (до 4НФ) 1НФ Для приведения таблиц к 1НФ требуется составить прямоугольные таблицы (один атрибут – один столбец) и разбить сложные атрибуты на простые, а многозначные атрибуты вынести в отдельные отношения. Примечание. В реальных БД сложные атрибуты разбиваются на простые, если: - этого требует внешнее представление данных; - в запросах поиск может осуществляться по отдельной части атрибута. Разделим атрибуты «Фамилия, имя, отчество» на три атрибута «Фамилия» «Имя», «Отчество» и «Паспортные данные» на атрибуты «Номер паспорта (уникальный)», «Дата выдачи» и «Кем выдан». Многозначный атрибут «Телефоны» для сотрудников компании следует сначала разделить на два – «Домашние телефоны» и «Рабочие телефоны». (Для авторов мы не будем различать домашние и рабочие телефоны). Затем нужно создать отдельные отношения с (нерабочими) телефонами для сотрудников (ТЕЛЕФОНЫ СОТРУДНИКОВ) и для авторов (ТЕЛЕФОНЫ АВТОРОВ). Атрибут «Рабочие телефоны» отношения СОТРУДНИКИ имеет неоднородные значения. Один из номеров телефонов – основной – определяется рабочим местом сотрудника (рассматриваются только стационарные телефоны). Наличие других номеров зависит от того, есть ли в том же помещении (комнате) другие сотрудники, имеющие стационарные телефоны. Можно добавить в отношение СОТРУДНИКИ атрибут Номер комнаты, а в атрибуте Рабочие телефоны хранить номер того телефона, который стоит на рабочем месте сотрудника. Дополнительные номера телефонов можно будет вычислить из других кортежей с таким же номером комнаты. Но в случае увольнения сотрудника мы потеряем сведения о номере рабочего телефона. Поэтому создадим новое отношение КОМНАТЫ и включим в него атрибуты Номер комнаты и Телефон. Так как в комнате может не быть телефона, первичный ключ нового отношения не определен (ПК не может содержать null–значения), но на этих атрибутах можно определить составной уникальный ключ. Связь между отношениями СОТРУДНИКИ и КОМНАТЫ реализуем через составной внешний ключ (Номер комнаты, Телефон). Значение внешнего ключа для каждого сотрудника будем брать из того кортежа, в котором хранится основной рабочий телефон этого сотрудника. 2НФ В нашем случае составные первичные ключи имеют отношения СТРОКИ ЗАКАЗА, КНИГИ–АВТОРЫ и КНИГИ–РЕДАКТОРЫ. Не ключевые атрибуты этих отношений функционально полно зависят от первичных ключей. 3НФ В отношении ЗАКАЗЫ атрибут Адрес заказчика зависит от атрибута Заказчик, а не от первичного ключа, поэтому адрес следует вынести в отдельное отношение ЗАКАЗЧИКИ. Но при этом первичным ключом нового отношения станет атрибут Заказчик, т.е. длинная символьная строка. Целесообразнее перенести в новое отношение атрибуты Заказчик и Адрес заказчика и ввести для него суррогатный ПК. Так как каждый заказчик может сделать несколько заказов, связь между отношениями ЗАКАЗЧИКИ и ЗАКАЗЫ будет 1:n и суррогатный ПК станет внешним ключом для отношения ЗАКАЗЫ. В отношении СОТРУДНИКИ атрибут Оклад зависит от атрибута Должность. Поступим с этой транзитивной зависимостью так же, как в предыдущем случае: создадим новое отношение ДОЛЖНОСТИ, перенесём в него атрибуты Должность и Оклад и введём суррогатный первичный ключ. В отношениях СОТРУДНИКИ и АВТОРЫ атрибуты Дата выдачи и Кем выдан зависят от атрибута Номер паспорта, а не от первичного ключа. Но если мы выделим их в отдельное отношение, то получившиеся связи будут иметь тип 1:1. Следовательно, декомпозиция нецелесообразна. В реальных базах данных после нормализации может проводиться денормализация. Она проводится с одной целью – повышение производительности БД. Рассмотрим некоторые запросы к нашей базе данных. Например, запрос на получение списка телефонов авторов или домашних телефонов сотрудников потребует в нормализованной БД соединения отношений. Пользователю безразлична форма представления этого списка: номера телефонов через запятую или в столбец. Поэтому мы откажемся от создания отдельных отношений с номерами телефонов, и вернёмся к варианту с многозначными полями. (Это не касается рабочих телефонов сотрудников). Другой запрос: как определяется, можно ли выполнить очередной заказ? Для каждой позиции заказа нужно просуммировать количество книг по выполненным заказам, получить остаток (тираж минус полученная сумма) и сравнить остаток с объёмом заказа. Такой расчёт может потребовать много времени, поэтому предлагается добавить в отношение КНИГИ производный атрибут Остаток тиража. Значение этого атрибута должно автоматически пересчитываться при установлении даты выполнения заказа. После проведённых преобразований схема БД выглядит так (рисунок 1.6): Окончательные схемы отношений базы данных с указанием ключей и других ограничений целостности приведены в табл. 1.8–1.17. 1.4.4. Определение дополнительных ограничений целостности Перечислим ограничения целостности, которые не указаны в таблицах 8–17. - Значения всех числовых атрибутов – больше 0 (или null, если атрибут необязателен). - Область значений атрибута Sex отношения EMPLOYEES – символы «м» и «ж». - Отношение ROOMS не имеет первичного ключа, но комбинация значений (R_no, Tel) уникальна. - В отношении TITLES порядковые номера авторов на обложке одной книги должны идти подряд, начиная с 1. - В отношении TITLES сумма процентов гонорара по одной книге равна 100. Ограничения (4,5) нельзя реализовать в схеме отношения. В реальных БД подобные ограничения целостности реализуются программно (через внешнее приложение или специальную процедуру контроля данных).