Модель сущность-связь (ER-модель)

Модель сущность-связь (ER-модель) (англ. entity-relationship model, ERM) — модель данных, позволяющая описывать концептуальные схемы предметной области. Концептуа́льная схе́ма — система взаимосвязанных по определенным правилам понятий (а не единичное понятие) и концепций.

ER-модель используется  при высокоуровневом (концептуальном) проектировании баз данных. С её помощью можно выделить ключевые сущности и обозначить связи, которые могут устанавливаться между этими сущностями.

Во время проектирования баз данных происходит преобразование ER-модели в конкретную схему базы данных на основе выбранной модели данных (реляционной, объектной, сетевой или др.).

Информационная  модель- это совокупность диаграмм типа "сущность - связь". Модели "сущность - связь" определяют структуру и взаимные связи используемой в системе информации, в полной мере отображающей её работу.

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

Cущность – это совокупность похожих объектов/экземпляров (людей, мест, предметов, событий), которая именуется общим существительным, обладает ключом (однозначно идентифицирующим каждый экземпляр) и имеет один или несколько атрибутов (описывающих каждый экземпляр). 
 
Каждая сущность может обладать любым количеством отношений с другими сущностями. Сущность является независимой, если каждый экземпляр сущности может быть однозначно идентифицирован без определения его отношений с другими сущностями. Независимая сущность на диаграмме изображается блоком с прямыми углами. Сущность называется зависимой, если однозначная идентификация экземпляра сущности зависит от его отношения к другой сущности. Если сущность зависима от идентификатора, то углы блока закругляются.  
 
Например, сущности Клиент, Издание являются независимыми, а сущность Заказ клиента - зависимой.

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

Идентифицирующим  отношением называется отношение, при котором экземпляр сущности-потомка однозначно определяется своей связью с сущностью-родителем. В противном случае отношение называется неидентифицирующим. Например, специфическое отношение связи будет существовать между сущностью Клиент и Заказ клиента, если Клиент имеет ноль, один или более Заказов, а каждый Заказ принадлежит только одному Клиенту. Отношение связи изображается линией, проводимой между сущностью-родителем и сущностью-потомком с точкой на конце линии у сущности-потомка. 
 
Идентифицирующее отношение изображается сплошной линией, пунктирная линия изображает неидентифицирующее отношение. 
 
Мощность определяет какое количество экземпляров сущности-потомка может существовать для каждого экземпляра сущности-родителя. 
 
В IDEF1X могут быть выражены следующие мощности отношений:

• Каждый экземпляр сущности-родителя может иметь ноль, один или более связанных с ним экземпляров сущности-потомка (эта мощность устанавливается по умолчанию);
• Каждый экземпляр сущности-родителя должен иметь не менее одного связанного с ним экземпляра сущности-потомка (буква Р (positive), помещенная рядом с точкой);
• Каждый экземпляр сущности-родителя может иметь не более одного связанного с ним экземпляра сущности-потомка (буква Z (zero), помещенная рядом с точкой);
• Каждый экземпляр сущности-родителя связан с некоторым фиксированным числом экземпляров сущности-потомка. Если мощность в точности равна некоторому числу N, это число (целое, положительное) помещается около точки.

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

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

Таблица находится  в первой нормальной форме (1НФ) тогда и только тогда, когда ни одна из ее строк не содержит в любом своем поле более одного значения и ни одно из ее ключевых полей не пусто.

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

В реляционной  модели отношение всегда находится  в первой нормальной форме по определению  понятия отношение.

Что же касается различных таблиц, то они могут не быть правильными представлениями отношений и, соответственно, могут не находиться в 1NF. В соответствии с определением К. Дж. Дейта для такого случая, таблица нормализована (эквивалентно — находится в первой нормальной форме) тогда и только тогда, когда она является прямым и верным представлением некоторого отношения. Конкретнее, рассматриваемая таблица должна удовлетворять следующим пяти условиям:

Нет упорядочивания строк сверху-вниз (другими словами, порядок строк не несет в себе никакой информации).

Нет упорядочивания столбцов слева-направо (другими словами, порядок столбцов не несет в себе никакой информации).

Нет повторяющихся  строк.

Каждое пересечение строки и столбца содержит ровно одно значение из соответствующего домена (и больше ничего).

Все столбцы являются обычными[1].

«Обычность» всех столбцов таблицы означает, что в таблице нет «скрытых» компонентов, которые могут быть доступны только в вызове некоторого специального оператора взамен ссылок на имена регулярных столбцов, или которые приводят к побочным эффектам для строк или таблиц при вызове стандартных операторов. Таким образом, например, строки не имеют идентификаторов кроме обычных значений потенциальных ключей (без скрытых «идентификаторов строк» или «идентификаторов объектов»). Они также не имеют скрытых временных меток[1].

[править]Пример

Исходная ненормализованная (то есть не являющаяся правильным представлением некоторого отношения) таблица:

Сотрудник	Номер телефона
Иванов И. И.	283-56-82  390-57-34
Петров П. П.	708-62-34

Таблица, приведённая  к 1NF (являющаяся правильным представлением некоторого отношения):

Сотрудник	Номер телефона
Иванов И. И.	283-56-82
Иванов И. И.	390-57-34
Петров П. П.	708-62-34

Таблица находится во второй нормальной форме (2НФ), если она удовлетворяет определению 1НФ и все ее поля, не входящие в первичный ключ, связаны полной функциональной зависимостью с первичным ключом.

Переменная отношения  находится во второй нормальной форме  тогда и только тогда, когда она находится в первой нормальной форме и каждый неключевой атрибут неприводимо зависит от ее потенциального ключа.[1]

Неприводимость  означает, что в составе потенциального ключа отсутствует меньшее подмножество атрибутов, от которого можно также  вывести данную функциональную зависимость.[1] Для неприводимой функциональной зависимости часто используется эквивалентое понятие «полная функциональная зависимость».[1]

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

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

[править]Пример

Пример приведения отношения ко второй нормальной форме

Пусть в следующем  отношении первичный ключ образует пара атрибутов {Сотрудник, Должность}:

Сотрудник	Должность	Зарплата	Наличие компьютера
Гришин	Кладовщик	20000	Нет
Васильев	Программист	40000	Есть
Иванов	Кладовщик	25000	Нет

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

В результате приведения к 2NF получаются два отношения:

Сотрудник	Должность	Зарплата
Гришин	Кладовщик	20000
Васильев	Программист	40000
Иванов	Кладовщик	25000

 

Должность	Наличие компьютера
Кладовщик	Нет
Программист	Есть

Таблица находится  в третьей нормальной форме (3НФ), если она удовлетворяет определению 2НФ и не одно из ее неключевых полей не зависит функционально от любого другого неключевого поля.

Пример

Рассмотрим  в качестве примера отношение  которое находится во 2NF, но не соответствует 3NF:

R1
Сотрудник	Отдел	Телефон
Гришин	Бухгалтерия	11-22-33
Васильев	Бухгалтерия	11-22-33
Петров	Снабжение	44-55-66

В отношении  атрибут «Сотрудник» является первичным  ключом. Личных телефонов у сотрудников  нет, и телефон сотрудника зависит  исключительно от отдела.

Таким образом, в отношении существуют следующие  функциональные зависимости: Сотрудник → Отдел, Отдел → Телефон, Сотрудник → Телефон.

Зависимость Сотрудник  → Телефон является транзитивной, следовательно, отношение не находится  в 3NF.

В результате декомпозиции отношения R1 получаются два отношения, находящиеся в 3NF:

R2
Отдел	Телефон
Бухгалтерия	11-22-33
Снабжение	44-55-66

 

R3
Сотрудник	Отдел
Гришин	Бухгалтерия
Васильев	Бухгалтерия
Петров	Снабжение

 
Исходное отношение R1 при необходимости  легко получается в результате операции соединения отношений R2 и R3

Таблица находится  в нормальной форме Бойса-Кодда (НФБК), если и только если любая функциональная зависимость между его полями сводится к полной функциональной зависимости отвозможного ключа.

Пример

Предположим, создаётся таблица  бронирования для теннисных кортов на день: {Номер корта, Время начала, Время окончания, Тариф, Член клуба}. Тариф зависит от выбранного корта и членства в клубе.

Таким образом, возможны следующие  составные первичные ключи: {Номер корта, Время начала}, {Номер корта, Время окончания}, {Тариф, Время начала}, {Тариф, Время окончания}.

Таблица соответствует  второй и третьей нормальной форме, так как атрибуты, не входящие в  состав первичного ключа, зависят от составного первичного ключа целиком (2NF) и нет транзитивных зависимостей (3NF).

Тем не менее, существует функциональная зависимость тарифа от номера корта. То есть, по ошибке можно  нарушить логическую целостность и, например, приписать тариф Premium для первого корта, хотя тариф Premium может относиться только ко второму корту.

Можно улучшить структуру, разбив таблицу на две: {Номер корта, Время начала, Время окончания, Член клуба} и{Тариф, Номер корта, Член клуба}. Данное отношение будет соответствовать BCNF.

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

Таблица находится  в пятой нормальной форме (5НФ) тогда и только тогда, когда в каждой ее полной декомпозиции все проекции содержат возможный ключ. Таблица, не имеющая ни одной полной декомпозиции, также находится в 5НФ.

Пример

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