Нормализация с помощью метода ER-диаграмм

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

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

На использовании разновидностей ER-модели основано большинство современных подходов к проектированию баз данных (главным образом, реляционных). Модель была предложена Ченом (Chen) в 1976 г. Моделирование предметной области базируется на использовании графических диаграмм, включающих небольшое число разнородных компонентов. В связи с наглядностью представления концептуальных схем баз данных ER-модели получили широкое распространение в системах CASE, поддерживающих автоматизированное проектирование реляционных баз данных. Среди множества разновидностей ER-моделей, одна из наиболее развитых применяется в системе CASE фирмы ORACLE.

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

Ключевыми для данного метода являются понятия сущность и связь.

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

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

Для применения метода ER-диаграмм необходимо определить сущности, которые являются необходимыми (стержневыми) для проектируемой базы данных, а также их ключевые атрибуты. После этого строятся ER-диаграммы. Затем согласно степени связи и класса принадлежности объекта строятся отношения, которые необходимо проверить на соответствие 3НФ. Если хотя бы одно полученное отношение не удовлетворяет требованиям 3НФ, то необходимо пересмотреть ER-диаграмму, соответствующую этому отношению.

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

Исходя из описания предметной области, можно выделить, 3 стержневые сущности: Клиент (физическое лицо), Клиент (юридическое лицо), Недвижимость (Объект продажи-покупки).

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

Для объекта Клиент (физическое лицо):

Код клиента -> ФИО, Адрес, Телефон, № паспорта, № идентиф. кода, Тип клиента.

№ паспорта -> ФИО, Адрес, Телефон, Код клиента, № идентиф. кода, Тип клиента.

№ идентиф. кода -> ФИО, Адрес, Телефон, Код клиента, № паспорта, Тип клиента.

Для объекта Клиент (юридическое лицо):

Код клиента -> Наименование организации, Адрес, Телефон, № регистр. свидетельства, № банковского счёта, Тип клиента.

№ регистр. свидетельства -> ФИО, Адрес, Телефон, Код клиента, № банковского счёта, Тип клиента.

№ банковского счёта -> ФИО, Адрес, Телефон, Код клиента, № регистр. свидетельства, Тип клиента.

Для объекта Недвижимость (Объект продажи-покупки):

Код заявки -> Дата, Наименование объекта, Площадь, Этаж, Кол-во комнат, Страна, Область, Населенный пункт, Район, Улица, Цена.

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

Основываясь на вышеизложенном определении первичного ключа, выделим ключевые атрибуты для каждого объекта:

Для объекта Клиент (физическое лицо) ключевым является поле Код клиента.

Для объекта Клиент (юридическое лицо) ключевым является поле Код клиента.

Для объекта Недвижимость (Объект продажи-покупки) ключевым является поле Код заявки.

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

Построим схему сущностей объекта Клиент (физическое лицо) и Недвижимость (Объект продажи-покупки).

Клиент1 Недвижимость1

Клиент2 Недвижимость2

Клиент3 Недвижимость3

Клиент4 Недвижимость4

Клиент5 Недвижимость5

Клиент6 Недвижимость6

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

Строим ER-диаграмму:

Полученная диаграмма имеет связь 1: n. Правило для преобразования такой диаграммы в отношение гласит:

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

Ключевым атрибутом для объекта Клиент (физическое лицо) как и для объекта, Клиент (юридическое лицо) является Код клиента. Этот ключ добавляем, в качестве атрибута, в объект Недвижимость (Объект продажи-покупки).

В результате нормализации получили отношения, приведённые в таблицах 4.1 – 4.3.

Таблица 4.1 – Отношение "Клиент (Физическое лицо)"

Таблица 4.2 – Отношение "Клиент (Юридическое лицо)"

Таблица 4.3 – Отношение "Недвижимость (Объект покупки-продажи)"

Продолжение Таблицы 4.3 – Отношение "Недвижимость (Объект покупки-продажи)"

Эти отношения будут использованы при создании базы данных.

Проверим полученные отношения на соответствие нормальной форме Бойса-Кодда.

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

Все полученные отношения находятся в нормальной форме Бойса-Кодда, так как, все первичные ключи совпадают с детерминантами.