Преобразование концептуальной модели в реляционную

Преобразование концептуальной модели в реляционную состоит в следующем:

· Построить набор предварительных таблиц и указать первичные ключи.

· Провести процесс нормализации.

Первый пункт мы уже рассматривали, со вторым мы ознакомимся на практике.

Итак, нам надо построить набор таблиц. Сделать это несложно, т.к. таблицы - это наши объекты, а поля таблиц - атрибуты объектов. Набор предварительных таблиц, исходя из нашей концептуальной модели, выглядит так:

Таким образом, у нас определены таблицы, поля, первичные ключи (РК) и связи (FK). Обратите внимание, в таблицах Журнал поставок и Журнал покупок первичные ключи - составные, т.е. состоят из двух полей. Теоретически бывают таблицы, в которых все поля являются одним составным ключом.

Переходим ко второму пункту, а именно к нормализации отношений (таблиц).

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

Для чего это нужно? Во-первых, для устранения избыточности данных. Например, в нашем примере для форума, мы оставили бы вот такую таблицу:

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

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

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

· 1НФ - первая нормальная форма

· 2НФ - вторая нормальная форма

· 3НФ - третья нормальная форма

· НФБК - нормальная форма Бойса-Кодда

· 4НФ - четвертая нормальная форма

· 5НФ - пятая нормальная форма

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

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

Надо сказать, что НФБК, 4НФ и 5НФ используются крайне редко. Поэтому и мы рассмотрим только первые три.

Первая нормальная форма

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

В нашей таблице Поставщики есть поле Адрес. Если наш магазин работает только с поставщиками из одного города, то значения поля Адрес можно считать атомарными, а саму таблицу - приведенной к 1НФ.

Но что если наши поставщики находятся в разных городах? Тогда, посылая машину за товарами в определенный город, мы должны быть уверенны, что она заберет товары у всех поставщиков, находящихся в этом городе. Т.е. нам могут понадобиться сведения о поставщикам, находящихся в определенном городе. В этом случае, значения в поле Адрес уже не являются атомарными (т.к. мы используем часть адреса), и для приведения таблицы к 1НФ нам надо выделить еще одно поле - Город:

Таким образом надо проанализировать все таблицы нашей базы данных. Так, в таблице Покупатель есть поле ФИО. Если мы собираемся, например, поздравлять наших покупателей с именинами (которые, как известно, завися от имени), то это поле пришлось бы разбить на три: Фамилию, Имя и Отчество. Наш магазин этого делать не собирается, поэтому поле ФИО можно считать атомарным, а таблицу - приведенной к 1НФ.

Для запросов нашего магазина все остальные таблицы приведены к 1НФ.

Вторая нормальная форма

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

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

В нашей базе данных две таблицы имеют составной ключ - Журнал покупок и Журнал поставок. Значение поля Количество зависит, как от Поставки (Покупки), так и от Товара. Значит, наши таблицы находятся во 2НФ.

Но предположим, что на этапе концептуального моделирования нашей базы данных, мы не выделили объекты Поставка и Покупка. Тогда наши таблицы могли бы выглядеть так:

Посмотрим теперь на таблицу Журнал поставок: поле Количество зависит от Наименования товара и от Даты поставки, но не зависит от того, кто поставил товар (поле Поставщика). Т.е. таблица не находится во 2НФ. Если бы на этапе концептуального моделирования нашей базы данных, мы не выделили объекты Поставка и Покупка, нам бы пришлось это делать сейчас. Но мы их выделили, поэтому все наши таблицы находятся во 2НФ.

Третья нормальная форма

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

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

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

Тогда наша таблица будет выглядеть так:

Даже не прибегая к 3НФ видно, что такая таблица будет содержать избыточную информацию. Но посмотрим на ее поля: поля Наименование и Дата зависят от id товара, а поле Цена зависит также и от Даты. Т.е. таблица не находится в 3НФ. Для устранения транзитивной зависимости необходимо провести "расщепление" объекта на два:

Все остальные таблицы нашей базы данных находятся в 3НФ. Кстати, в таблице Товар можно было и не вводить поле id товара, а сделать первичным ключом поле Наименование, но, как уже говорилось, суррогатные ключи все-таки предпочтительнее.

Подведем итог. Схема нашей базы данных после нормализации несколько изменилась и выглядит теперь так:

Таким образом, мы преобразовали нашу концептуальную модель в реляционную. Дальше необходимо эту модель реализовать в конкретной СУБД.

Подведем итог.

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

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

В математических дисциплинах понятию «таблица» соответствует понятие «отношение» (relation). Таблица отражает объект реального мира – сущность, а каждая ее строка отражает конкретный экземпляр сущности. Каждый столбец имеет уникальное для таблицы имя. Строки не имеют имен, порядок их следования не определен, а количество логически не ограничено. Одним из основных преимуществ РМД является однородность (каждая строка таблицы имеет один формат). Пользователь сам решает вопрос, обладают ли соответствующие сущности однородностью. Этим решается проблема пригодности модели. Основные элементы РМД показаны на рис. 13.

Отношение представляет собой двумерную таблицу, содержащую некоторые данные. Сущность – объект любой природы, данные о котором хранятся в БД. Атрибуты – свойства, характеризующие сущность (столбцы). Степень отношения – количество столбцов. Схема отношения – список имен атрибутов, например, СОТРУДНИК (№, ФИО, Год рождения, Должность, Кафедра). Домен – совокупность значений атрибутов отношения (тип данных). Кортеж – строка таблицы. Кардинальность (мощность) – количество строк в таблице.

Рис. Элементы реляционной модели

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

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

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

Таблица 13

Код сотрудника	ФИО	Должность	Номер отдела	Наименование отдела	Квалификация
	Иванов И.И.	Программист		Отдел проектирования	C, Java
	Сергеева С.С.	Администратор БД		Финансовый отдел	DB2
	Петров П.П.	Программист		Отдел проектирования	VB, Java
	Николаев Н.Н.	Системный администратор		Отдел проектирования	Windows, Linux

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

· избыточность данных;

· потенциальная противоречивость (аномалии).

Под избыточностью понимают повторение данных в разных строках одной таблицы или в разных таблицах БД. Так, для каждого сотрудника отдела 128 повторяются данные «128, Отдел проектирования».

Аномалии – это проблемы, возникающие в данных из-за дефектов проектирования БД. Существуют три вида аномалий: вставки, удаления и модификации.

Аномалии вставки проявляются при вводе данных в дефектную таблицу. Добавляя информацию о новом сотруднике, мы должны добавить номер и название отдела. Если ввести данные, не соответствующие имеющимся в таблице (например, 42, отдел проектирования), будет не ясно, какая из строк БД содержит правильную информацию.

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

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

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

Решение перечисленных проблем состоит в разделении данных и связей, что обеспечивается процедурой нормализации. Концепции и методы нормализации были разработаны Э. Ф. Коддом.

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

Теория нормализации основана на наличии зависимостей между атрибутами отношения. Основными видами зависимостей являются:

· функциональные;

· многозначные;

· транзитивные.

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

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

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

· один-ко-многим (1:М);

· многие-к-одному (М:1);

· многие-ко-многим (М:М).

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

· первая нормальная форма (1НФ);

· вторая нормальная форма (2НФ);

· третья нормальная форма (3НФ);

· усиленная 3НФ или нормальная форма Бойса-Кодда (БКНФ);

· четвертая нормальная форма (4НФ);

· пятая нормальная форма (5НФ).

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

В столбце Квалификация ненормализованной табл. 13 содержатся списки значений (С, Java и т. д.). Чтобы привести схему к 1НФ, необходимо разместить в этом столбце атомарные значения. Самый простой способ заключается в выделении по одной строке на каждый элемент квалификации (табл. 14).

Таблица 14

Код сотрудника	ФИО	Должность	Номер отдела	Наименование отдела	Квалификация
	Иванов И.И.	Программист		Отдел проектирования	C
	Иванов И.И.	Программист		Отдел проектирования	Java
	Сергеева С.С.	Администратор БД		Финансовый отдел	DB2
	Петров П.П.	Программист		Отдел проектирования	VB
	Петров П.П.	Программист		Отдел проектирования	Java
	Николаев Н.Н.	Системный администратор		Отдел проектирования	Windows
	Николаев Н.Н.	Системный администратор		Отдел проектирования	Linux

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

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

Таблица Квалификации_сотрудников (табл. 14) находится в 1НФ, но не удовлетворяет 2НФ. Первичный ключ должен уникальным образом идентифицировать каждую строку. Единственным вариантом является использование в качестве первичного ключа комбинации Код сотрудника и Квалификация. Это порождает схему: Квалификации_сотрудников (Код сотрудника, ФИО, Должность, Номер отдела, Наименование отдела, Квалификация).

Одной из имеющихся здесь функциональных зависимостей будет: Код сотрудника, Квалификация ’ ФИО, Должность, Номер отдела, Наименование отдела. Но, кроме того, мы также имеем: Код сотрудника ’ ФИО, Должность, Номер отдела, Наименование отдела. Другими словами, можно определить имя, должность и отдел, используя только код сотрудника. Это значит, что указанные атрибуты функционально зависимы только от части первичного ключа, а не от всего первичного ключа. Следовательно, указанная схема не находится в 2НФ.

Для приведения этой схемы в 2НФ необходимо декомпозировать исходное отношение на два, в которых все неключевые атрибуты будут полностью функционально зависеть от ключа: сотрудники (Код сотрудника, ФИО, Должность, Номер отдела, Наименование отдела) и Квалификации_сотрудников (Код сотрудника, Квалификация) (табл. 15–16).

Таблица 15

Код сотрудника	ФИО	Должность	Номер отдела	Наименование отдела
	Иванов И.И.	Программист		Отдел проектирования
	Сергеева С.С.	Администратор БД		Финансовый отдел
	Петров П.П.	Программист		Отдел проектирования
	Николаев Н.Н.	Системный администратор		Отдел проектирования

Таблица 16

Код сотрудника	Квалификация
	C
	Java
	DB2
	VB
	Java
	Windows
	Linux

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

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

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

Функциональная зависимость Код сотрудника ’ Наименование отдела является транзитивной, поскольку содержит промежуточный шаг (зависимость Номер отдела ’ Наименование отдела). Для приведения в 3НФ необходимо исключить эту транзитивную зависимость, декомпозируя отношение на два: сотрудники (Код сотрудника, ФИО, Должность, Номер отдела) и отделы (Номер отдела, Наименование отдела) (табл. 17–18).

Таблица 17

Код сотрудника	ФИО	Должность	Номер отдела
	Иванов И.И.	Программист
	Сергеева С.С.	Администратор БД
	Петров П.П.	Программист
	Николаев Н.Н.	Системный администратор

Таблица 18

Номер отдела	Наименование отдела
	Финансовый отдел
	Отдел проектирования

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

Алгоритм приведения ненормализованных схем в 3НФ показан на рис. 15. На практике построение 3НФ в большинстве случаев является достаточным и приведением к ней процесс построения реляционной БД заканчивается.

Рис. 15. Алгоритм приведения ненормализованных схем в 3НФ

Запомнить правила нормализации помогает изречение: «Нормализация – это ключ, целый ключ и ничего, кроме ключа».

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

Дата добавления: 2014-01-03