Переход от ER-модели к схеме реляционной базы данных
1. Каждая простая сущность превращается в таблицу (отношение). Имя сущности становится именем таблицы. Каждый простой атрибут становится столбцом таблицы с тем же именем: R1 (И1, А1, А2, А3).
2. Компоненты уникального идентификатора сущности превращаются в первичный ключ. Если имеется несколько возможных уникальных идентификаторов, выбирается наиболее используемый. Учитываются также следующие факторы:
- длина ключа – в качестве первичного ключа выбирается, как правило, самый короткий из вероятных ключей;
- стабильность – желательно выбирать в качестве первичного ключа атрибуты, которые не изменяются;
- мнемоничность – при прочих равных условиях следует отдавать предпочтение тем из вероятных ключей, которые легче запомнить.
Некоторые СУБД (Access, Paradox и др.) позволяют автоматически генерировать в качестве ключа таблицы поле типа «счетчик». Этот искусственный код можно использовать для простых объектов, если в предметной области не предполагается применение другой системы кодирования.
Если в состав уникального идентификатора входят связи, то к числу столбцов первичного ключа добавляется копия уникального идентификатора сущности, находящейся на дальнем конце связи (процесс может продолжаться рекурсивно).
3. Каждому из многозначных атрибутов ставится в соответствие отношение, полями которого будут идентификатор, выбранный в качестве первичного ключа, и многозначный атрибут. Ключ этого отношения будет составным, включающим оба эти атрибута. Для многозначных атрибутов МА4 и МА5 будут созданы отношения: R2(И1, МА4) и R3(И1, МА5).
4. Если сущность имеет необязательный атрибут, возможны два варианта:
- если таким свойством обладают многие экземпляры объекта, его можно хранить как обычный атрибут в той же таблице (столбец может содержать неопределенные значения);
- если свойством обладает малое число экземпляров, то можно выделить отношение, включающее идентификатор и соответствующий атрибут: R4(И1, НА6). Отношение будет содержать столько строк, сколько объектов имеет свойство.
5. Если сущность имеет составной атрибут, то возможны два варианта:
- составному свойству ставится в соответствие отдельное поле;
- каждому из составляющих элементов составного свойства ставится в соответствие отдельное поле.
Выбор варианта зависит от характера обработки данных. При реализации запросов проще объединить поля, чем выделить часть поля. Если предполагается использование компонентов атрибута, лучше вариант 2, иначе – вариант 1.
6. Бинарные связи «один-к-одному» и «один-ко-многим» становятся внешними ключами. Создается копия уникального идентификатора с конца связи «один», и соответствующие столбцы составляют внешний ключ.
Связь «один-к-одному» между сущностями встречается редко. Если класс принадлежности обеих сущностей является обязательным, то для отображения обеих связанных сущностей можно использовать одну таблицу: R3(И1, И2, А1, А2).
Однако таким решением злоупотреблять не следует. Если для каждого объекта потребуются свои связи или в запросах потребуется информация по каждой сущности, то выбранное решение усложнит или замедлит работу с БД.
Если для каждой сущности создаются отдельные отношения, то информацию о связях можно отразить, включив в одно из отношений идентификатор из другого отношения. Причем это можно сделать в любом из отношений: R1(И1, А1, И2), R2(И2, А2) или R1(И1, А1), R2(И2, А2, И1).
Если класс принадлежности одной из сущностей является необязательным, то идентификатор сущности с необязательным классом добавляется в отношение, соответствующее сущности с обязательным классом принадлежности.
Если класс принадлежности обеих сущностей является необязательным, то, чтобы избежать наличия пустых полей, следует использовать три отношения: по одному для каждой сущности и одно – для отображения связи между ними: R1(И1, А1), R2(И2, А2), R3(И1, И2).
7. Преобразование бинарной связи «один-ко-многим» (1:N) зависит только от класса принадлежности N-связной сущности. Если он является обязательным, то можно использовать два отношения (по одному для каждой сущности). В отношение для N-связной сущности добавляется идентификатор 1-связной сущности: R1(И1, А1), R2(И2, А2, И1).
Если класс принадлежности N-связной сущности является необязательным, то для отображения связи создается третье отношение, которое будет содержать ключи каждой из связанных сущностей: R1(И1, А1), R2(И2, А2), R3(И1, И2).
8. Для бинарной связи «многие-ко-многим» (М:N) потребуются три отношения: по одному для каждой сущности и одно дополнительное – для отображения связи между ними. Последнее отношение будет содержать идентификаторы связанных объектов. Ключ этого отношения будет составным: R1(И1, А1), R2(И2, А2), R3(И1, И2).
9. В случае N-арной связи необходимо использовать (n + 1) отношение – по одному для каждой сущности, и одно для связи. Идентификатор каждой сущности станет первичным ключом соответствующего отношения. Отношение, порождаемое связью, будет иметь среди своих атрибутов ключи каждой сущности. Если связь имеет атрибуты, то они становятся атрибутами отношения связи. Например: R1(И1, А1), R2(И2, А2), R3(И3, А3), R4(И1, И2, И3, АС4).
10. Обобщающей сущности соответствует одно отношение, причем ключ сущности становится ключом отношения. Этому отношению приписываются общие для всех ролевых сущностей атрибуты. Ролевые элементы и связи, их соединяющие, порождают такое число отношений, которое определяется ранее описанными правилами, причем каждая роль трактуется как обычная сущность. Связываются отношения с помощью ключевого атрибута. Каждому значению ключевого атрибута ролевой сущности соответствует одна запись в обобщающем отношении с таким же значением ключа.
11. Индексы создаются для первичного ключа (уникальный индекс), а также внешних ключей и тех атрибутов, которые будут часто использоваться в запросах.
Нормализация отношений
Нормализация отношений – это процесс построения оптимальной структуры таблиц и связей в реляционной БД (процесс уменьшения избыточности информации). В процессе нормализации данные группируются в таблицы, представляющие классы объектов и их взаимодействие.
Цели, которые преследуются при построении наиболее эффективной структуры данных:
- обеспечить быстрый доступ к данным;
- исключить ненужное повторение данных, которое может являться причиной ошибок при вводе, а также привести к нерациональному использованию дискового пространства;
- обеспечить целостность данных, т.о. чтобы при изменении одних объектов автоматически происходило соответствующее изменение связанных с ними объектов.
Теория нормализации отношений работает с 6 нормальными формами таблиц: 1-я НФ (обычно обозначается также 1НФ), 2НФ, 3НФ, НФ Бойса-Кодда (НФБК), 4НФ, 5НФ. Каждая последующая форма должна отвечать требованиям предыдущих плюс некоторые дополнительные требования.
На практике, как правило, ограничиваются 3НФ, ее оказывается вполне достаточно для создания надежной схемы БД. НФ более высокого порядка представляют скорее академический интерес из-за чрезмерной сложности. Более того, при реализации абстрактной схемы БД в виде реальной базы иногда разработчики вынуждены сделать шаг назад – провести денормализацию с целью повышения эффективности, ибо идеальная с точки зрения теории структура может оказаться слишком накладной на практике.
Рассмотрим подробнее три первые НФ.
Таблица, находящаяся в первой нормальной форме должна отвечать следующим требованиям:
- таблица не должна иметь повторяющихся записей;
- в таблице должны отсутствовать повторяющиеся группы полей.
Для приведения к 1НФ можно использовать следующий алгоритм:
1. Определить поле, которое можно назначить первичным ключом. Если такого поля нет, то добавить новое уникальное ключевое поле.
2. Определить группы повторяющихся полей.
3. Вынести группы повторяющихся полей в отдельные таблицы, в основной таблице остается одно поле для организации связи между таблицами.
4. Назначить первичные ключи в новых таблицах. (В качестве ключевых полей можно использовать поля таблицы или добавить новое поле. Если ключевое поле имеет большой размер, предпочтительней добавлять новое поле.)
5. Определить тип отношения между таблицами.
Таблица, находящаяся во второй нормальной форме, должна отвечать всем требованиям 1НФ, а также любое неключевое поле должно однозначно идентифицироваться полным набором ключевых полей.
2НФ применяется к таблицам, которые имеют составной ключ или частичнозависимое поле – поле, зависящее только от части ключа.
Для приведения к 2НФ необходимо: вынести все частичнозависимые поля в отдельную таблицу; определить ключевые поля; установить отношения между таблицами.
Таблица, находящаяся в третьей нормальной форме, должна отвечать всем требованиям 2НФ, а также ни одно из неключевых полей не должно идентифицироваться при помощи другого неключевого поля. Другими словами в таблице нет полей, которые не зависят от ключа.
Нормализация – это фактически исправление огрехов, допущенных при проектировании БД. Как известно, частенько оказывается проще не допускать этих огрехов с самого начала, нежели спроектировать БД кое-как, а потом оптимизировать. Неоценимую помощь в проектировании БД оказывают соответствующие методологии (например, IDEF1X), а также инструментальные средства для поддержки этих методологий (например, AlFusion Data Modeler производства Computer Associates).