3. Лабораторный практикум Лабораторная работа № 1 Проектирование баз данных с использованием er-технологии

Теоретические сведения

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

Разработку логической модели можно осуществлять различными методами. Наиболее формализованным и простым для понимания является метод «сущность-связь», или ER-метод, предусматривающий выполнение следующих шагов:

1. построение ER-диаграммы, включающей все сущности и связи, важные с точки зрения интересов предметной области;

2. анализ связей и определение их характеристик – степени связи и класса принадлежности;

3. построение набора предварительных отношений с указанием предполагаемого первичного ключа для каждого отношения;

4. подготовка списка всех неключевых атрибутов и назначение каждого из этих атрибутов одному из предварительных отношений;

5. проверка нахождения всех полученных отношений в НФБК;

6. построение схемы данных;

Если отношения не находятся в НФБК или если некоторым атрибутам не находится логически обоснованных мест, необходимо пересмотреть ER-диаграммы.

Сущность – это объект, информация о котором должна быть представлена в БД. Экземпляр сущности – это информация о конкретном представителе объекта. Связь – соединение между двумя и более сущностями. Экземпляр связи – это конкретная связь между конкретными представителями объектов. Атрибут – свойство сущности или связи. Атрибут или набор атрибутов, используемый для однозначной идентификации экземпляра сущности, называется ключом сущности.

В ER-диаграмме для отображения сущностей используются прямоугольники, а для отображения связей – овалы или ромбы. Различают ER-диаграммы для экземпляров сущностей и ER-диаграммы для классов сущностей (рис. 2–3). Здесь П1, П2, ПЗ, П4 – различные преподаватели; ПОИС, ПСИИ, Информатика, Математика – названия дисциплин. На первом этапе необходимы только атрибуты Код преподавателя и Код дисциплины, являющиеся ключами сущностей Преподаватель и Дисциплина. Другие атрибуты добавляются в отношения позднее.

Рис. 2. ER-диаграмма для экземпляров сущностей и связей

Рис. 3. ER-диаграмма классов

Степень связи показывает, сколько сущностей участвует в связи. Чаще всего встречаются бинарные связи (связи между двумя сущностями), но могут быть и связи более высокого порядка, соединяющие несколько сущностей. Связь Преподаватель читает дисциплину для специальности является тройственной.

Пары чисел на диаграмме отражают характеристики связи – класс принадлежности (первое число) и мощность связи (второе число). Класс принадлежности сущности к связи может принимать значение 1 (обязательный) или 0 (необязательный). При обязательном классе принадлежности каждый экземпляр сущности обязательно должен быть связан с экземпляром другой сущности, при необязательном классе принадлежности это не требуется.

Мощность связи – число экземпляров сущности, участвующих в данной связи. В зависимости от мощности связь может иметь один из следующих типов:

• один-к-одному (1:1) – каждый экземпляр первой сущности может быть связан только с одним экземпляром второй сущности и наоборот;

• один-ко-многим (1:М) – каждый экземпляр первой сущности может быть связан с несколькими экземплярами второй сущности, а каждый экземпляр второй сущности может быть связан только с одним экземпляром первой;

• многие-ко-многим (M:N) – каждый экземпляр первой сущности может быть связан с несколькими экземплярами второй сущности и наоборот.

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

Вариант 1. Каждый Преподаватель может читать одну Дисциплину, каждая Дисциплина читается только одним Преподавателем (рис. 4).

Рис. 4. ER-диаграммы для варианта 1

Вариант 2. Преподаватель может читать только одну Дисциплину, каждая Дисциплина читается только одним Преподавателем. Есть преподаватели, которые в настоящий момент не заняты в учебном процессе (рис. 5).

Рис. 5. ER-диаграммы для варианта 2

Вариант 3. Преподаватель может читать только одну Дисциплину, Дисциплина читается не более чем одним Преподавателем. Есть преподаватели, которые в настоящий момент не заняты в учебном процессе, и есть дисциплины в учебном плане, которые никем не читаются (рис. 6).

Рис. 6. ER-диаграммы для варианта 3

Вариант 4. Каждый Преподаватель может читать одновременно несколько Дисциплин, но каждая Дисциплина читается одним Преподавателем. Есть преподаватели, которые в настоящий момент не заняты в учебном процессе, и есть дисциплины в учебном плане, которые никем не читаются (рис. 7).

Рис. 7. ER-диаграммы для варианта 4

Вариант 5. Каждый Преподаватель читает не более одной Дисциплины, но каждая Дисциплина может читаться несколькими Преподавателями (рис. 8).

Вариант 6. Преподаватель может читать несколько Дисциплин, и Дисциплина может читаться несколькими Преподавателями. Есть преподаватели, которые в настоящий момент не заняты в учебном процессе, и есть дисциплины в учебном плане, которые никем не читаются. Степень связи M:N, класс принадлежности обеих сущностей необязательный (рис. 9).

Рис. 8. ER-диаграммы для варианта 5

Рис. 9. ER-диаграммы для варианта 6

Правила генерации предварительных отношений из ER-диаграмм можно получить, опираясь на класс принадлежности и степень связи.

Правило 1. Если степень бинарной связи 1:1 и класс принадлежности обеих сущностей является обязательным, то требуется только одно отношение. Первичным ключом отношения может быть ключ любой из двух сущностей.

Например, Преподаватель читает Дисциплину (рис. 10).

Рис. 10. ER-диаграмма к правилу 1

Получаем отношение Преподаватель (Код преподавателя, Фамилия, Телефон, Код дисциплины, Дисциплина, Число часов). Отношение не будет содержать пустых данных и повторяющихся групп избыточных данных.

Правило 2. Если степень бинарной связи 1:1 и класс принадлежности одной сущности является обязательным, а другой – необязательным, то необходимы два отношения. Под каждую сущность выделяется одно отношение. Ключи сущностей будут первичными ключами соответствую­щих отношений. Ключ сущности с необязательным классом принадлежности добавляется как атрибут в отношение для сущности с обязательным классом принадлежности.

Например, класс принадлежности сущности Преподаватель обязательный, а сущности Дисциплина – необязательный (рис. 11).

Рис. 11. ER-диаграмма к правилу 2

Получаем отношения:

Преподаватель (Код преподавателя, Фамилия, Телефон, Код дисциплины)

Дисциплина (Код дисциплины, Наименование дисциплины, Число часов).

Если класс принадлежности сущности Преподаватель необязательный, а сущности Дисциплина – обязательный (рис. 12), то получаем отношения:

Преподаватель (Код преподавателя, Фамилия, Телефон)

Дисциплина (Код дисциплины, Наименование дисциплины, Число часов, Код преподавателя).

Рис. 12. ER-диаграмма к правилу 2

Правило 3. Если степень бинарной связи 1:1 и класс принадлежности ни одной из сущностей не является обязательным, то необходимы три отношения – по одному для каждой сущности и одно отношение для связи. Ключ каждой сущности используется в качестве первичного ключа соответствующего отношения. Отношение связи должно иметь в числе атрибутов ключи каждой сущности.

Например, класс принадлежности обеих сущностей – Преподаватель и Дисциплина – необязательный (рис. 13).

Рис. 13. ER-диаграмма к правилу 3

Получаем отношения:

Преподаватель (Код преподавателя, Фамилия, Телефон)

Дисциплина (Код дисциплины, Наименование дисциплины, Число часов)

Читает (Код преподавателя, Код дисциплины).

Для бинарных связей 1:N используются два правила. Каждое из них определяется классом принадлежности N-связной сущности. Класс принадлежности 1-связной сущности на результат не влияет.

Правило 4. Если степень бинарной связи 1:N и класс принадлежности N-связной сущности является обязательным, то достаточно двух отношений, по одному на каждую сущность. Ключ каждой сущности служит первичным ключом для соответствующего отношения. Дополнительно ключ 1-связной сущности должен быть добавлен как атрибут в отношение, отводимое N-связной сущности.

Для ER-диаграммы, показанной на рис. 14 получаем отношения:

Преподаватель (Код преподавателя, Фамилия, Телефон)

Дисциплина (Код дисциплины, Наименование дисциплины, Число часов, Код преподавателя).

Рис. 14. ER-диаграмма к правилу 4

Правило 5. Если степень бинарной связи 1:N и класс принадлежности N-связной сущности является необязательным, то необходимо формирование трех отношений – по одному для каждой сущности и одно отношение для связи. Ключ каждой сущности будет первичным ключом соответствующего отношения. Отношение связи должно иметь в числе своих атрибутов ключи каждой сущности.

Получаем отношения:

Преподаватель (Код преподавателя, Фамилия, Телефон)

Дисциплина (Код дисциплины, Наименование дисциплины, Число часов)

Читает (Код преподавателя, Код дисциплины).

Правило 6. Если степень бинарной связи M:N, то для хранения данных необходимо три отношения – по одному для каждой сущности и одно отношение для связи. Ключ каждой сущности используется в качестве первичного ключа соответствующего отношения. Отношение связи должно иметь в числе своих атрибутов ключи каждой сущности (см. правило 5).

Рассмотрим задачу о рыболовных проводниках, обслуживающих группы рыбаков на озерах. Одно озеро может обслуживаться несколькими проводниками, но каждый проводник обслуживает только одно озеро. Рыбаки интересуются видами рыб в озерах, самыми крупными экземплярами каждого вида, выловленными в текущем сезоне, типом лучшей наживки для каждого вида рыбы. Функциональные зависимости атрибутов показаны на рис. 15. Атрибутами проводника являются: код, фамилия, телефон, плата за день, размер группы. Атрибутами озера являются: код, наименование, рейтинг. Атрибутами рыбы являются: код, название вида, вес самого большого экземпляра каждого вида, лучшая наживка для каждого вида.

Рис. 15. Функциональные зависимости к примеру

ER-диаграмма для рассматриваемой задачи показана на рис. 16. Проводник обслуживает озеро, в котором водится рыба.

Рис. 16. ER-диаграмма к примеру

Получаем отношения:

Проводник (КП, Фамилия, Телефон, Плата, Размер)

Озеро (КО, Название, Рейтинг)

Рыба (КР, Название, V_max , Наживка)

Водится (КО, КР).

Анализ функциональных зависимостей показывает, что все детерминанты являются потенциальными ключами; следовательно, отношения находятся в НФБК.

Для рассмотрения связей более высокого порядка усложним задачу. Предположим, что проводник предпочитает ловить определенный вид рыбы. Связь между сущностями Проводник и Рыба должна быть представлена как Предпочитает (рис. 17). Если мощность связи N:1, можно обойтись бинарными связями, добавив в отношение Проводник ключевой атрибут вида рыбы КР.

Для корректного моделирования некоторых ситуаций бинарных связей недостаточно. Предположим, что проводники обслуживают более чем по одному озеру, и каждый проводник может предпочитать ловить один вид рыбы в одном озере, а другой – в другом озере. Для отображения этой информации, воспользуемся трехсторонней связью, объединяющей три сущности – проводник, обслуживаемое им озеро и предпочитаемая проводником в этом озере рыба (рис. 18).

Рис. 17. ER-диаграмма к задаче об озерах

Рис. 18. ER-диаграмма для трехсторонней связи

Правило 7. В случае трехсторонней связи необходимо использовать 4 предварительных отношения, по одному для каждой сущности, и одно для связи. Ключ каждой сущности будет первичным ключом соответствующего отношения. Отношение связи будет иметь среди своих атрибутов ключи каждой сущности. Для N-сторонней связи требуется n+1 предварительное отношение.

Для нашей задачи получаем отношения:

Проводник (КП, Фамилия, Телефон, Плата, Размер)

Озеро (КО, Название, Рейтинг)

Рыба (КР, Название, V_max , Наживка)

П_О_Р (КП, КО, КР).

Первичный ключ для отношения П_О_Р будет составным <КП, КО>, если каждый проводник предпочитает ловить в озере только один вид рыбы. Если число видов рыбы два или более для какого-либо озера, то ключ будут составлять все три атрибута отношения П_О_Р.

В некоторых ситуациях одних сущностей и связей для полноценного моделирования может оказаться недостаточно. Одна из таких ситуаций возникает тогда, когда экземпляры некоторой сущности должны играть разные роли.

Рассмотрим задачу о производственном персонале. На предприятии есть две категории работников – мастера и сборщики. Мастера получают фиксированный оклад, у сборщиков – почасовая оплата (рис. 19).

Рис. 19. ER-диаграмма классов сущностей

Мастер# и Сборщик# – номера страховых полисов, ключи сущностей. По правилу 4 получаем два предварительных отношения: Мастер (Мастер#, ...), Сборщик (Сборщик# , ... , Мастер#). В отношения необходимо добавить атрибуты: РабФам – фамилия работника; РабТел – служебный телефон мастера; ДомТел – домашний телефон работника; РабАдрес – домашний адрес работника; ТарСтавка – почасовая тарифная ставка сборщика; Оклад – оклад мастера.

В результате предварительные отношения будут преобразованы к виду:

Мастер (Мастер#, Оклад, РабТел, …)

Сборщик (Сборщик#, ТарСтавка, ... , Мастер#).

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

Рис. 20. ER-диаграмма для случая иерархических связей

Правило 8. Обобщающей сущности соответствует одно отношение, причем ключ сущности служит ключом отношения. Этому отношению приписываются общие для всех ролевых сущностей атрибуты. Ролевые элементы и их связи порождают отношения, число которых определяется ранее описанными правилами, причем каждая роль трактуется как сущность. Связываются отношения с помощью ключевого атрибута. Каждому значению ключевого атрибута ролевой сущности соответствует запись в обобщающем отношении с таким же значением ключа:

Работник (Работник#, РабФам, ДомТел, РабАдрес),

Мастер (Мастер#, Оклад, РабТел),

Сборщик (Сборщик#, ТарСтавка, Мастер#).

Эти отношения связывает номер страхового полиса. Можно в отношение Работник включить атрибут Роль – мастер или сборщик.

Таким образом, результатом проектирования по ER-методу является совокупность взаимосвязанных таблиц. Связь между таблицами осуществляется через одинаковые по смыслу, типу и значению атрибуты связи. В одних таблицах эти атрибуты играют роль ключевых, в других они вводятся дополнительно по правилам 1–8. Таблица, в которой атрибут связи является ключом, называется главной, связанная с ней по этому атрибуту таблица называется подчиненной. Атрибут связи в подчиненной таблице называется внешним ключом. Данные в связанных таблицах должны удовлетворять ограничениям целостности, определяющим порядок операций ввода, обновления и удаления записей:

• каждой записи главной таблицы может соответствовать нуль или более записей подчиненной таблицы;

• в подчиненной таблице не должно быть записей, которые не имеют родительской записи в главной таблице;

• каждая запись подчиненной таблицы может иметь только одну родительскую запись в главной таблице.

Практическая работа

При выполнении лабораторной работы необходимо:

• для своего варианта, соответствующего определенной предметной области, построить ER-диаграмму и определить характеристики связей;

• перейти к предварительным отношениям и указать их первичные ключи;

• приписать все оставшиеся атрибуты к отношениям и проверить, находятся ли эти отношения в НФБК;

• составить отчет по лабораторной работе.