Построение реляционной структуры из ER-модели

К статьям Разработка → Отношения таблиц базы данных и Разработка → Проектирование баз данных хабравчанина ueasley хотелось бы сделать небольшое дополнение.

Хочу описать правила, по которым можно построить реляционную схему базы данных. Правила эти, наверное, мало кому нужны, поскольку они используются разработчиками на интуитивном уровне, но интересны даже тем, что формализуют процесс построения схемы БД.

Правила эти применяются к ER-модели, то есть модели «сущность-связь».

ER-модель

ER-модель представляет собой схему, составными элементами которой являются:

• Сущность — это реальный, либо воображаемый объект, информацию о котором необходимо хранить в базе данных. На диаграмме ER-модели сущность изображается в виде прямоугольника, содержащего имя сущности.
• Связь — отображаемая графически на диаграмме ассоциация между двумя (чаще всего) сущностями, или между одной и той же сущностью (рекурсивная связь). Связь изображается ромбом, на котором выделяются два конца, по одному на каждую сущность. Для каждой стороны этой связи устанавливаются:
  1. Степень связи — сколько экземпляров данной сущности связывается
  2. Обязательность связи — обязательно ли данная сущность должна участвовать в связи.

Приведу пример:
Пусть необходимо хранить информацию о клиентах и их заказах. Построим диаграмму


Рис.1

Заметим, что со стороны сущности «ЗАКАЗ» связь обозначена дополнительным прямоугольником — это обозначение того, что каждому экземпляру сущности «ЗАКАЗ» соответствует экземпляр сущности «КЛИЕНТ» (для клиента же наличие заказа не обязательно). Степень «M» означает, что для каждого экземпляра сущности «КЛИЕНТ» могут существовать несколько экземпляров сущности «ЗАКАЗ» (но не наоборот, поскольку для каждого заказа всегда только один заказчик — ставим степень «1»)

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

Этапы проектирования

1. Концептуальное проектирование

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

2. Логическое проектирование

   
   Строится набор предварительных отношений с указанием первичного ключа для каждого отношения. Составляется список атрибутов, затем эти атрибуты распределяются по отношениям. Необходимо, чтобы все отношения оставались в НФБК.
   
   Переход к реляционной структуре (построение набора отношений) производится по следующим правилам:
   
   
   1. Если степень бинарной связи равна 1:1 и класс принадлежности обеих сущностей обязательный, то требуется только одно отношение. Первичным ключом этого отношения может быть ключ любой из этих двух сущностей. В этом случае гарантируется однократное появление каждого значения ключа в любом экземпляре отношения.
   2. Если степень бинарной связи равна 1:1 и класс одной из сущностей необязательный, то необходимо построение двух отношений, под каждую сущность необходимо выделение одного отношения. Ключ сущности, для которого класс принадлежности является необязательным, добавляется в качестве атрибута в отношение, выделенное для сущности с обязательным классом принадлежности.
   3. Если степень бинарной связи равна 1:1 и класс принадлежности ни одной из сущностей не является необязательным, то используется три отношения — по одному для каждой сущности — ключи которых служат в качестве первичных в соответствующих отношениях и одного для связи. Отношение, выделенное для связи, будет иметь по одному ключу сущности от каждой сущности.
   4. Если степень бинарной связи равна 1: М и класс принадлежности М-связной сущности обязательный, то достаточно использовать два отношения: по одному на каждую сущность, при условии, что ключ сущности служит в качестве первичного ключа для соответствующего отношения. Ключ же односвязной сущности должен быть добавлен как атрибут в отношение, отводимое М-связной сущности.
   5. Если степень бинарной связи равна 1: М и класс принадлежности М-связной сущности необязателен, то необходимо использовать три отношения: по одному на сущность и одно для связи. Связь должна иметь среди своих атрибутов ключ сущности от каждой сущности.
   6. Если степень бинарной связи равна М: М, то для хранения данных необходимо три отношения: по одному на сущность и одно для связи. Ключи сущности входят в связь. Если одна из сущностей вырождена, то — два отношения (т.е. достаточно будет двух таблиц).
   7. В случае трехсторонней связи необходимо использовать четыре отношения: по одному на сущность и одно для связи. Отношение, порождаемое связью, имеет в себе среди атрибутов ключи сущности от каждой сущности.
   
   
   Воспользуемся правилами, сведем данные в таблицу.
   
   

   Сущности	Номер правила	Отношения
   Клиент Заказ	4	Клиент(#Клиента Заказ(#Заказа, #Клиента
   Сотрудник Заказ	4	Сотрудник(#Сотрудника Заказ(#Заказа, #Сотрудника
   Заказ Элемент заказа	4	Заказ(#Заказа Элемент заказа(#Элемента заказа, #Заказа
   Бригада Элемент заказа	4	Бригада(#Бригады Элемент заказа(#Элемента, #Бригады
   Изделие Элемент заказа	4	Изделие(#Изделия Элемент заказа(#Элемента, #Изделия
   Клиент Заказ	6	Клиент(#Клиента Заказ(#Заказа Платеж(#Платежа, #Клиента, #Заказа
   Бригада Сотрудник	5	Бригада(#Бригады Сотрудник(#Сотрудника Сотрудник бригады(#Сотрудника бригады, #Сотрудника, #Бригады
   Элемент заказа Операция	5	Элемент заказа(#Элемента Операция(#Операции Запись операции(#Записи, #Элемента, #Операции
   Элемент заказа Материал	5	Элемент заказа(#Элемента Материал(#Материала Расход(#Записи, #Элемента, #Материала

   Табл. 1
   
   Распределив атрибуты по полученным отношениям, получим (в списке полей на первом месте — первичный ключ, остальные, помеченные «#», являются внешними ключами):
   
   

   БРИГАДА	(#Бригады, #Бригадира, Расположение)
   ДОЛЖНОСТЬ	(#Должности, Должность, Оклад)
   ЗАКАЗ	(#Заказа, #Клиента, #Сотрудника, ДатаРазмещения, ТребуемаяДата, ДатаИсполнения, Описание)
   КЛИЕНТ	(#Клиента, Название, Имя, Фамилия, ОрганизацияИлиОтдел, Адрес, НомерТелефона, АдресЭлектроннойПочты)
   ЗАПИСЬОПЕРАЦИИ	(#Записи, #Элемента,#Операции, #Сотрудника, Количество)
   ОПЛАТА	(#Оплаты, #Клиента, #Заказа, СуммаОплаты, ДатаОплаты, Заметки)
   РАСХОД	(#Записи, #РасхМат, #Елемента, Количество)
   СОСТАВ	(#Элемента, #Заказа, #Товара, #Бригады, Количество)
   СОТРБРИГАДЫ	(#СотрБригады, #Бригады,#Сотрудника)
   СОТРУДНИК	(#Сотрудника, НомерПаспорта, Фамилия, Имя, Отчество, #Должности, Адрес, ДомашнийТелефон, РабочийТелефон, ДатаРождения, ДатаНайма, ДатаОкончДоговора, Фотография, Заметки)
   ОПЕРАЦИЯ	(#Операции, Описание, Стоимость, Время, Оборудование, Выполнение)
   МАТЕРИАЛ	(#РасхМат, НаимРасхМат, Цена, Плотность, Тип, Состав)
   ТОВАР	(#Товара, Марка, Название, ОписаниеТовара, Тип, СерийныйНомер, НаСкладе, Цена)

   Табл. 2
   

Вот так нас учили делать в университете. Может будет кому-нибудь интересно. Насчет «нужно ли это», слушаю ваши мнения!