Нормализация О нормализации функциональных и многозначных зависимостях

Нормализация

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

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

О нормализации, функциональных и многозначных зависимостях • Функциональная зависимость. Поле Б таблицы функционально зависит от поля А той же таблицы в том и только в том случае, когда в любой заданный момент времени для каждого из различных значений поля А обязательно существует только одно из различных значений поля Б. Здесь допускается, что поля А и Б могут быть составными. • Полная функциональная зависимость. Поле Б находится в полной функциональной зависимости от составного поля А, если оно функционально зависит от А и не зависит функционально от любого подмножества поля А. • Многозначная зависимость. Поле А многозначно определяет поле Б той же таблицы, если для каждого значения поля А существует хорошо определенное множество соответствующих значений Б.

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

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

Процедура нормализации Нормализация — это процесс последовательной замены таблицы ее полными декомпозициями до тех пор, пока все они не будут находиться в 5 НФ. Остановимся на процедуре приведения таблиц к НФБК. Процедура приведения таблиц к НФБК основывается на том, что единственными функциональными зависимостями в любой таблице должны быть зависимости вида K - F, где K - первичный ключ, а F - некоторое другое поле. Цель нормализации состоит именно в том, чтобы избавиться от всех этих "других" функциональных зависимостей, т. е. таких, которые имеют иной вид, чем K - F.

Процедура нормализации Если воспользоваться рекомендацией и подменить на время нормализации коды первичных (внешних) ключей на исходные ключи, то следует рассмотреть: 1. Таблица имеет составной первичный ключ вида (К 1, К 2), и включает также поле F, которое функционально зависит от части этого ключа, например, от К 2, но не от полного ключа. В этом случае рекомендуется сформировать другую таблицу, содержащую К 2 и F (первичный ключ - К 2), и удалить F из первоначальной таблицы: 2. Таблица имеет первичный (возможный) ключ К, не являющееся возможным ключом поле F 1, которое функционально зависит от К, и другое неключевое поле F 2, которое функционально зависит от F 1. Решение здесь то же самое, что и прежде — формируется другая таблица, содержащая F 1 и F 2, с первичным ключом F 1, и F 2 удаляется из первоначальной таблицы 3. Для выполнения этих операций необходимо первоначально иметь в качестве входных данных какие-либо "большие" таблицы.

Универсальное отношение COOK БЛЮДО Бастурма Бастурма Салат мясной Салат мясной Суп харчо Суп харчо ВИД Горячее Горячее Закуска Закуска Суп Суп Суп ОСНОВА Мясо Мясо Мясо Мясо ВЫХОД 300, 0 300, 0 200, 0 200, 0 500, 0 500, 0 ПРОДУКТ Говядина Зелень Лук Масло Помидоры Говядина Зелень Майонез Помидоры Яйца Говядина Зелень Лук Масло Помидоры ПОСТАВЩИК ПОРТОС СЫТНЫЙ УРОЖАЙ ОГУРЕЧИК СЫТНЫЙ ПОРТОС ШУШАРЫ ТУЛЬСКИЙ СЫТНЫЙ КОРЮШКА ОГУРЕЧИК ШУШАРЫ ЛЕТО ОГУРЕЧИК СЫТНЫЙ

1. Определение первичного ключа таблицы: Предположим • каждое блюдо имеет уникальное название, относится к единственному виду и приготавливается по единственному рецепту • название организации поставщика уникально для того города, в котором он расположен, и названия городов уникальны для каждой из стран • поставщик может осуществлять в один и тот же день только одну поставку каждого продукта,

2. Выявление полей, функционально зависящих от части составного ключа. • Поля Вид и Основа функционально зависят только от поля Блюдо, т. е. Блюдо K Вид Блюдо K Основа • Аналогичным образом можно получить зависимости: (Блюдо, Продукт) K Вес Город K Страна (Поставщик, Город) K Цена 3. Формирование новых таблиц. Блюда (Блюдо, Вид) Состав (Блюдо, Продукт, Вес (г)) Города (Город, Страна) Поставки (Поставщик, Город, К во, Цена)

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

Построение даталогической (табличной) модели 1. Представить каждый стержень (независимую сущность) таблицей базы данных (базовой таблицей) и специфицировать первичный ключ этой базовой таблицы. 2. Представить каждую ассоциацию (связь вида "многие-ко-многим" или "многие-ко-многим" и т. д. между сущностями) как базовую таблицу. Использовать в этой таблице внешние ключи для идентификации участников ассоциации и специфицировать ограничения, связанные с каждым из этих внешних ключей. 3. Представить каждую характеристику как базовую таблицу с внешним ключом, идентифицирующим сущность, описываемую этой характеристикой. Специфицировать ограничения на внешний ключ этой таблицы и ее первичный ключ - по всей вероятности, комбинации этого внешнего ключа и свойства, которое гарантирует "уникальность в рамках описываемой сущности".

Построение даталогической (табличной) модели 4. Представить каждое свойство (атрибут) как поле в базовой таблице, представляющей сущность, которая непосредственно описывается этим свойством. 5. Для того чтобы исключить в проекте непреднамеренные нарушения каких-либо принципов нормализации, необходимо выполнить процедуру нормализации 6. Если в процессе нормализации было произведено разделение каких -либо таблиц, то следует модифицировать инфологическую модель базы данных и повторить перечисленные шаги. 7. Указать ограничения целостности проектируемой базы данных и дать (если это необходимо) краткое описание полученных таблиц и их полей.

Проектирование реляционных баз данных на основе принципов нормализации

Введение При проектировании базы данных решаются основные проблемы. • Каким образом отобразить объекты предметной области в абстрактные объекты модели данных, чтобы это отображение не противоречило семантике предметной области и было, по возможности, лучшим (эффективным, удобным и т. д. )? Часто эту проблему называют проблемой логического проектирования баз данных. • Как обеспечить эффективность выполнения запросов к базе данных, т. е. каким образом, имея в виду особенности конкретной СУБД, расположить данные во внешней памяти, создания каких дополнительных структур (например, индексов) потребовать и т. д. ? Эту проблему обычно называют проблемой физического проектирования баз данных.

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

Минимальные функциональные зависимости и вторая нормальная форма • Имеется переменная отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ {СЛУ_НОМ, СЛУ_УРОВ, СЛУ_ЗАРП, ПРО_НОМ, СЛУ_ЗАДАН}. • Атрибуты СЛУ_УРОВ и СЛУ_ЗАДАН содержат, соответственно, данные о разряде служащего и о задании, которое выполняет служащий в данном проекте (разряд служащего определяет размер его заработной платы; каждый служащий может участвовать в нескольких проектах, но в каждом проекте он выполняет только одно задание). • Следовательно, единственно возможным ключом отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ является составной атрибут {СЛУ_НОМ, ПРО_НОМ}.

Диаграмма минимального множества FD Тело значения отношения

Аномалии обновления - трудности, с которыми приходится сталкиваться при выполнении операций добавления (INSERT) кортежей в отношение, удаления кортежей (DELETE) и модификации кортежей (UPDATE) • Добавление кортежей. Мы не можем дополнить отношение СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ данными о служащем, который в данное время еще не участвует ни в одном проекте (ПРО_НОМ является частью первичного ключа и не может содержать неопределенных значений). Между тем часто бывает, что сначала служащего принимают на работу, устанавливают его разряд и размер зарплаты, а лишь потом назначают для него проект.

Аномалии обновления • Удаление кортежей. Мы не можем сохранить в отношении СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ данные о служащем, завершившем участие в своем последнем проекте (по той причине, что значение атрибута ПРО_НОМ для этого служащего становится неопределенным). Между тем характерна ситуация, когда между проектами возникают перерывы, не приводящие к увольнению служащих. • Модификация кортежей. Чтобы изменить разряд служащего, мы будем вынуждены модифицировать все кортежи с соответствующим значением атрибута СЛУ_НОМ. В противном случае будет нарушена естественная FD СЛУ_НОМ->СЛУ_УРОВ (у одного служащего имеется только один разряд).

Возможная декомпозиция Для преодоления возникших трудностей можно произвести декомпозицию переменной отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ на две переменных отношений – СЛУЖ {СЛУ_НОМ, СЛУ_УРОВ, СЛУ_ЗАРП} и СЛУЖ_ПРО_ЗАДАН {СЛУ_НОМ, ПРО_НОМ, СЛУ_ЗАДАН}.

Вторая нормальная форма Переменная отношения находится во второй нормальной форме (2 NF) тогда и только тогда, когда она находится в первой нормальной форме, и каждый неключевой атрибут минимально функционально зависит от первичного ключа. • Переменные отношений СЛУЖ и СЛУЖ_ПРО_ЗАДАН находятся в 2 NF (все неключевые атрибуты отношений минимально зависят от первичных ключей). Переменная отношения СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ не находится в 2 NF. • Любая переменная отношения, находящаяся в 1 NF, но не находящаяся в 2 NF, может быть приведена к набору переменных отношений, находящихся в 2 NF. В результате декомпозиции получаем набор проекций исходной переменной отношения, естественное соединение значений которых воспроизводит значение исходной переменной отношения (т. е. это декомпозиция без потерь). Для переменных отношений СЛУЖ и СЛУЖ_ПРО_ЗАДАН исходное отношение СЛУЖАЩИЕ_ПРОЕКТЫ_ЗАДАНИЯ воспроизводится их естественным соединением по общему атрибуту СЛУ_НОМ.

Нетранзитивные функциональные зависимости и 3 НФ Функциональные зависимости переменной отношения СЛУЖ по-прежнему порождают некоторые аномалии обновления. Они вызываются наличием транзитивной FD СЛУ_НОМ->СЛУ_ЗАРП. Эти аномалии связаны с избыточностью хранения значения атрибута СЛУ_ЗАРП в каждом кортеже, характеризующем служащих с одним и тем же разрядом. • Добавление кортежей. Невозможно сохранить данные о новом разряде (и соответствующем ему размере зарплаты), пока не появится служащий с новым разрядом. (Первичный ключ не может содержать неопределенные значения. ) • Удаление кортежей. При увольнении последнего служащего с данным разрядом мы утратим информацию о наличии такого разряда и соответствующем размере зарплаты. • Модификация кортежей. При изменении размера зарплаты, соответствующей некоторому разряду, мы будем вынуждены изменить значение атрибута СЛУ_ЗАРП в кортежах всех служащих, которым назначен этот разряд (иначе не будет выполняться FD СЛУ_УРОВ->СЛУ_ЗАРП).

Возможная декомпозиция • произведем декомпозицию переменной отношения СЛУЖ на две переменных отношений –СЛУЖ 1 {СЛУ_НОМ, СЛУ_УРОВ} и УРОВ {СЛУ_УРОВ, СЛУ_ЗАРП}.

Возможная декомпозиция Выполненное преобразование обратимо, т. е. любое допустимое значение исходной переменной отношения СЛУЖ является естественным соединением значений отношений СЛУЖ 1 и УРОВ. • Добавление кортежей. Чтобы сохранить данные о новом разряде, достаточно добавить соответствующий кортеж к отношению УРОВ. • Удаление кортежей. При увольнении последнего служащего, обладающего данным разрядом, удаляется соответствующий кортеж из отношения СЛУЖ 1, и данные о разряде сохраняются в отношении УРОВ. • Модификация кортежей. При изменении размера зарплаты, соответствующей некоторому разряду, изменяется значение атрибута СЛУ_ЗАРП ровно в одном кортеже отношения УРОВ.

Третья нормальная форма Переменная отношения находится в третьей нормальной форме (3 NF) в том и только в том случае, когда она находится во второй нормальной форме, и каждый неключевой атрибут нетранзитивно функционально зависит от первичного ключа. • Отношения СЛУЖ 1 и УРОВ оба находятся в 3 NF (все неключевые атрибуты нетранзитивно зависят от первичных ключей СЛУ_НОМ и СЛУ_УРОВ). • Отношение СЛУЖ не находится в 3 NF (FD СЛУ_НОМ->СЛУ_ЗАРП является транзитивной). • Любое отношение, находящееся в 2 NF, но не находящееся в 3 NF, может быть приведено к набору отношений, находящихся в 3 NF.