Концептуальные и физические ER-модели

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

Составление модели "Сущность-связь". Нормализация отношений Цель работы: ознакомиться с задачами этапа концептуального проектирования БД. Изучить ER-модель представления данных (модель «сущность-связь»). Для указанного варианта задания разработать ER-модель данных с учетом семантических ограничений предметной области. Представить модель в виде ER-диаграммы.

Теоретическая часть

Основные понятия ER-диаграмм

Сущность – это класс однотипных объектов, информация о которых должна быть учтена в модели.

Каждая сущность должна иметь наименование, выраженное существительным в единственном числе.

Примерами сущностей могут быть такие классы объектов как «Поставщик», «Сотрудник», «Накладная».

Каждая сущность в модели изображается в виде прямоугольника с наименованием:

Рисунок 1 – Изображение сущности в модели

Экземпляр сущности – это конкретный представитель данной сущности.

Например, представитель сущности «Сотрудник» может быть «Сотрудник Иванов». Экземпляры сущностей должны быть различимы, т.е. сущности должны иметь некоторые свойства уникальные для каждого экземпляра этой сущности.

Атрибут сущности – это именованная характеристика, являющаяся некоторым свойством сущности.

Наименование атрибута должно быть выражено существительным в единственном числе (возможно, с характеризующим прилагательными). Примерами атрибутов сущности «Сотрудник» могут быть такие атрибуты как «Табельный номер», «Фамилия», «Имя», «Отчество», «Должность», «Зарплата» и т.п.

Атрибуты изображаются в пределах прямоугольника, определяющего сущность:

Рисунок 2 – Атрибуты

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

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

Рисунок 3 – Изображение ключевых полей

Связь – это некоторая ассоциация между двумя сущностями. Одна сущность может быть связана с другой сущностью или сама с собою. Связи позволяют по одной сущности находить другие сущности, связанные с нею. Например, связи между сущностями могут выражаться следующими фразами – «СОТРУДНИК может иметь несколько ДЕТЕЙ», «каждый СОТРУДНИК обязан числиться ровно в одном ОТДЕЛЕ».

Графически связь изображается линией, соединяющей две сущности:

Рисунок 4 – Графическое изображение связи между двумя сущностями

Каждая связь имеет два конца и одно или два наименования. Наименование обычно выражается в неопределённой глагольной форме: «иметь», «принадлежать» и т.п. Каждое из наименований относиться к своему концу связи. Иногда наименования не пишутся ввиду их очевидности.

Каждая связь может иметь один из следующих типов связи:

Рисунок 5 – Типы связи

Связь типа один-к-одному означает, что один экземпляр первой сущности (левой) связна с одним экземпляром второй сущности (правой). Связь один-к-одному чаще всего свидетельствует о том, что на самом деле есть одна сущность, неправильно разделенную на две.

Связь типа один-ко-многим означает, что один экземпляр первой сущности (ледовой) связан с несколькими экземплярами второй сущности (правой). Это наиболее часто используемый тип связи. Левая сущность (со стороны «один» называется родительской, правая (со стороны «много») – дочерней. (пример, рисунок 4).

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

Каждая связь может иметь одну из двух модальностей связи:

Рисунок 6 – модальность связей

Модальность «может» означает, что экземпляр одной сущности может быть связан с одним или несколькими экземплярами другой сущности, а может быть и не связан ни с одним экземпляром.

Модальность «должен» означает, что экземпляр одной сущности обязан быть связан не менее чем с одним экземпляром другой сущности.

Связь может иметь разную модальность с разных концов. (пример рисунок 4).

    Описанный графический синтаксис позволяет однозначно читать диаграммы, пользуясь следующей схемой построения фраз:

<Каждый экземпляр СУЩНОСТИ 1> <МОДАЛЬНОСТЬ СВЯЗИ> <НАИМЕНОВАНИЕ СВЯЗИ> <ТИП СВЯЗИ> <экземпляр СУЩНОСТИ 2>.

Каждая связь может быть прочитана как слева направо, так и справа налево. Связь на рис. 4 читается как: Слева направо: «каждый сотрудник может иметь несколько детей».

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

Пример разработки простой ER-модели.

При разработке ER-модели необходимо получить следующую информацию о предметной области:

1. Список сущностей предметной области.

2. Список атрибутов сущностей.

3. Описание взаимосвязей между сущностями.

4. ER-диаграммы удобны тем, что процесс выделения сущностей, атрибутов и связей является итерационным. Разработав первый приближенный вариант диаграмм, необходимо уточнить их, опрашивая экспертов предметной области. При этом документацией, в которой фиксируются результаты бесед, является сами ER-Диаграммы.

Предположим, что стоит задача разработать ИС по заказу некоторой оптовой торговой фирмы. В первую очередь необходимо изучить предметную область, и процессы, происходящие в ней. Для этого опрашиваем сотрудников фирмы, читаем документацию, изучаем формы заказов, накладных и т.д.

Например, в ходе беседы с менеджером по продажам, выяснилось, что он считает, что проектируемая система должна выполнять следующие действия:

· Хранить информацию о покупателях;

· Печатать накладные на отпущенные товары;

· Следить за наличием товаров на складе.

Выделим все существительные в этих предложениях – это будут потенциальные кандидаты на сущности и атрибуты, и проанализируем их ( непонятные термины будем выделять знаком вопроса):

· Покупатель –явный кандидат на сущность.

· Накладная – явный кандидат на сущность.

· Товар – явный кандидат на сущность.

· (?)Склад – сколь складов имеет фирма? Если несколько, то это будет кандидатов на новую сущность.

· (?) Наличие товара – это, скорее всего, атрибут, но атрибут какой сущности?

Сразу возникает очевидная связь между сущностями – «покупатели могут покупать много товаров» и «товары могут продаваться многим покупателям». Первый вариант диаграммы выглядит так:

 

Рисунок 7 – Связь между сущностями «покупатели могут покупать много товаров»

Задав дополнительные вопросы менеджеру, выяснялось, что фирма имеет несколько складов. Причем, каждый товар может храниться на нескольких складах и быть проданным с любого склада. Куда поместить сущности «Накладная» и «Склад» и с чем их связать? Спросим себя, как связаны эти сущности между собой и с сущностями «Покупатель» и «Товар»? Покупатели покупают товары, получая при этом накладные, в которые внесены данные о количестве и цене купленного товара. Каждый покупатель может получить несколько накладных. Каждая накладная обязана выписываться на одного покупателя. Каждая накладная обязан содержать несколько товаров (не бывает пустых накладных). Каждый товар, в свою очередь, может быть продан нескольким покупателям через несколько накладных. Кроме того, каждая накладная должна быть выписана с определенного склада, и с любого склада может быть выписано много накладных. Таким образом, после уточнения, диаграмма будет выглядеть следующим образом:

Рисунок 8 – Уточнённая диаграмма

Пора подумать об атрибутах сущностей. Беседуя с сотрудниками фирмы, выяснили следующее:

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

· Каждый товар имеет наименование, цену ,а также характеризуется единицами измерения.

· Каждая накладная имеет уникальный номер, дату выписки, список товаров с количествами и ценами, а также общую сумму накладной. Накладная выписывается с определенного склада и на определенного покупателя.

· Каждый склад имеет свое наименование.

Снова выпишем все существительные, которые будут потенциальными атрибутами, и проанализируем их:

· Юридическое лицо – термин риторический, не работает с физическими лицами. Не обращаем внимания.

· Наименование покупателя – явная характеристика покупателя.

· Адрес - явная характеристика покупателя.

· Банковские реквизиты - явная характеристика покупателя.

· Наименование товара - явная характеристика товара.

· (?) Цена товара – похоже, что это характеристика товара. Отличается ли эта характеристика от цены в накладной?

· Единица измерения - явная характеристика товара.

· Номер накладной - явная характеристика накладной.

· Дата накладной – явная характеристика накладной.

· (?)Список товара в накладной – список не может быть атрибутом. Вероятно, нужно выделить этот список в отдельную сущность.

· (?) Количество товара в накладной – это явная характеристика, но характеристика чего? Это характеристика не просто «товара», а «товара в накладной».

· Сумма накладной – явная характеристика накладной. Это характеристика не является независимой. Сумма накладной равна сумме стоимостей всех товаров, входящих в накладную.

· Наименование склада – явная характеристика склада.

В ходе дополнительной беседы с менеджером удалось прояснить различные понятие цен. Оказалось, что каждый товар имеет некоторую текущую цену. Это цена, по которой товар продается в данный момент времени. Естественно, что это цена может меняться со временем. Цена одного и того же товара в разных накладных, выписанных в разное время может быть различной. Таким образом, имеется две цены- цена товар в накладной и текущая цена товара. С возникающим понятием «Список товаров в накладной» все довольно ясно. Сущность «Накладная» и «Товар» связаны друг с другом отношением типа много-ко-многим. Такая связь, должна быть расщеплена на две связи типа один-ко-многим. Для этого требуется дополнительная сущность. Этой сущностью и будет сущность «Список товаров в накладной». Связь ее с сущностями «Накладная» и «Товар» характеризуется следующими фразами – «каждая накладная обязана иметь несколько записей из списка товаров в накладной», «каждая запись из списка товаров в накладной обязана включаться ровно в одну накладную», «каждый товар может включаться в несколько записей из списка товаров в накладной», «каждая запись из списка товаров в накладной обязана быть связана ровно с одним товаром». Атрибуты «Количество товара в накладной» и «Цена товара в накладной» являются атрибутами сущности «Список товаров в накладной». Точно также поступим со связью, соединяющей сущности «Склад» и «Товар». Введем дополнительную сущность «Товар на складе». Атрибутом этой сущности, будет «Количество товара на складе». Таким образом, товар будет числиться на любом складе и количество его на каждом складе будет свое. Теперь можно внести эти данные в диаграмму:

Рисунок 9 – Уточнения по ER-диаграмме

Концептуальные и физические ER-модели

Разработанный выше пример ER-диаграммы является примером концептуальной диаграммы. Это означает, что диаграмма не учитывает особенности конкретной СУБД. По данной концептуальной диаграмме можно построить физическую диаграмму, которая будет учитывать такие особенности СУБД, как допустимые типы и наименования полей и таблиц, ограничения целостности и т.п. Физический вариант диаграммы, приведенной на рисунке 9 может выглядеть, например, следующим образом:

Рисунок 10 - Физический вариант диаграммы

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

“CUST_DETAIL” и “PROD_IN_SKLAD”, соответствующих сущностям «Запись списка накладной» и «Товар на складе», появились новые атрибуты, которых не было в концептуальной модели – это ключевые атрибуты родительских таблиц, мигрировавших в дочерние таблицы для того, чтобы обеспечить связь между таблицами посредством внешних ключей.

Полученные таблицы сразу находятся в 3НФ.

Выводы

Реальным средством моделирования данных является не формальный метод нормализации отношений, а так называемое семантическое моделирование. В качестве инструмента семантического моделирования используются различные варианты диаграмм сущность- связь (ER-диаграммы).

Диаграммы сущность-связь позволяют использовать наглядные графические обозначения для моделирования сущностей и их взаимосвязей.

Различают концептуальны и физические ER-диаграммы. Концептуальные диаграммы не учитывают особенностей конкретных СУБД. Физические диаграммы строятся по концептуальным и представляют собой прообраз конкретной базы данных. Сущности, определенные в концептуальной диаграмме становятся таблицами, атрибуты становятся колонками таблиц (при этом учитываются допустимые для данной СУБД типы данных и наименования столбцов), связи реализуются путем миграции ключевых атрибутов родительских сущностей и создания внешних ключей.

При правильном определении сущностей, полученные таблицы будут сразу находиться в 3НФ. Основное достоинство метода состоит в том, модель строится методом последовательных уточнений первоначальных диаграмм.

Задание 1. Сделать конспект основных понятий теоретической части.

Задание 2. Провести инфологическое проектирование проанализировав предметную область согласно варианту задания. Разработать диаграмму «Сущность-связь».

Контрольные вопросы:

1. Перечислите основные этапы проектирования БД

2. Определите соотношение понятия «сущность», «связь».

3. В чем заключается концептуальное проектирование для конкретной предметной области?

4. Понятие отношения в реляционной модели.

5. Понятие целостности реляционных данных.

6. Понятие потенциального и первичного ключа.

7. Понятие ссылки (внешнего ключа) и ссылочной целостности. Представление объектов ER-модели в реляционной модели.