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

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

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

СТУДЕНТ (Код студента, Фамилия, Название факультета, Название специальности).

Эта схема отношений обусловливает следующие недостатки соответствующей базы данных:

• Дублирование информации (избыточность). У студентов, обучающихся на одном факультете, будет повторяться название факультета. Для разных факультетов будут повторяться специальности.

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

• Потенциальная возможность потери сведений (аномалии удаления). При удалении информации о всех студентах, поступающих на определенную специальность, мы теряем все сведения об этой специальности.

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

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

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

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

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

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

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

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

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

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

Пример

Пример приведения таблицы к первой нормальной форме

Исходная, ненормализованная, таблица:

Сотрудник	Номер телефона
Иванов И. И.	283-56-82 390-57-34
Петров П. Ю.	708-62-34

Таблица, приведённая к 1NF:

Сотрудник	Номер телефона
Иванов И. И.	283-56-82
Иванов И. И.	390-57-34
Петров П. Ю.	708-62-34

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

Одно и то же значение может быть атомарным или неатомарным в зависимости от смысла этого значения. Например, значение «4286» является

• атомарным, если его смысл — «пин-код кредитной карты» (при разбиении на части или переупорядочивании смысл теряется)

• неатомарным, если его смысл — «четные цифры» (при разбиении на части или переупорядочивании смысл не теряется)

Хорошим способом принятия решения о необходимости разбиения атрибута на части является вопрос: «будут ли части атрибута использоваться по отдельности?». Если да, то атрибут следует разделить (но так, чтобы сохранились осмысленные части атрибута). Далее необходимо снова задаться тем же вопросом для новой структуры и так до тех пор, пока не останется атрибутов, допускающих разбиение.

Примеры неатомарного атрибута, часто встречающиеся на практике: составные поля в виде строки идентификаторов, разделённых, скажем, запятыми: 100,32,168,1045

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

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

Пример

Пусть Сотрудник и Должность вместе образуют первичный ключ в такой таблице:

Сотрудник	Должность	Зарплата	Наличие компьютера
Гришин	Кладовщик	20000	Нет
Васильев	Программист	40000	Есть
Васильев	Кладовщик	25000	Нет

Зарплату сотруднику каждый начальник устанавливает сам, но её границы зависят от должности. Наличие же компьютера у сотрудника зависит только от должности, то есть зависимость от первичного ключа неполная.

В результате приведения к 2NF получаются две таблицы:

Сотрудник	Должность	Зарплата
Гришин	Кладовщик	20000
Васильев	Программист	40000
Васильев	Кладовщик	25000

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

Должность	Наличие компьютера
Кладовщик	Нет
Программист	Есть

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

Согласно определению Кодда, таблица находится в 3НФ тогда и только тогда, когда выполняются следующие условия:

• Отношение R (таблица) находится во второй нормальной форме;

• Каждый непервичный атрибут R находится в нетранзитивной (то есть прямой) зависимости от каждого ключа R.

Таким образом, отношение находится в 3NF тогда и только тогда, когда оно находится во 2NF и отсутствуют транзитивные зависимости неключевых атрибутов от ключевых. Транзитивной зависимостью неключевых атрибутов от ключевых называется следующая: A → B и B → C, где A — набор ключевых атрибутов (ключ), B и С — различные множества неключевых атрибутов.

При решении практических задач в большинстве случаев третья нормальная форма является достаточной. Процесс проектирования реляционной базы данных, как правило, заканчивается приведением к 3NF.

Пример.

Исходная таблица:

Фамилия	Отдел	Телефон
Гришин	1	11-22-33
Васильев	1	11-22-33
Петров	2	44-55-66

В результате приведения к 3НФ получаются две таблицы:

Фамилия	Отдел
Гришин	1
Васильев	1
Петров	2

Отдел	Телефон
1	11-22-33
2	44-55-66

Нормальная форма Бойса-Кодда (англ. Boyce-Codd normal form; сокращённо BCNF) — одна из возможных нормальных форм отношения в реляционной модели данных.

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

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

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

Для определения BCNF следует понимать понятие функциональной зависимости атрибутов отношения.

Пусть R является переменной отношения, а X и Y — произвольными подмножествами множества атрибутов переменной отношения R. Y функционально зависимо от X тогда и только тогда, когда для любого допустимого значения переменной отношения R, если два кортежа переменной отношения R совпадают по значению X, они также совпадают и по значению Y. Подмножество X называют детерминантом, а Y — зависимой частью.

Функциональная зависимость тривиальна тогда и только тогда, когда ее правая (зависимая) часть является подмножеством ее левой части (детерминанта).

Ситуация, когда отношение будет находиться в 3NF, но не в BCNF, возникает, например, при условии, что отношение имеет два (или более) потенциальных ключа, которые являются составными и имеют общий атрибут. На практике такая ситуация встречается достаточно редко, для всех прочих отношений 3NF и BCNF эквивалентны.

Пример приведения таблицы к нормальной форме Бойса — Кодда

Исходная таблица:

Номер клиента	Дата собеседования	Время собеседования	Номер комнаты	Номер сотрудника
С345	13.10.03	13.00	103	А138
С355	13.10.03	13.05	103	А136
С368	13.09.03	13.00	102	А154
С366	13.09.03	13.30	105	А207

В результате приведения к форме Бойса—Кодда получаются две таблицы:

Номер клиента	Дата собеседования	Время собеседования	Номер Сотрудника
С345	13.10.03	13.00	А138
С355	13.10.03	13.05	А136
С368	13.09.03	13.00	А154
С366	13.09.03	13.30	А207

Дата собеседования	Номер сотрудника	Номер комнаты
13.10.03	А138	103
13.10.03	А136	103
13.09.03	А154	102
13.09.03	А207	105

Второй пример

Предположим, создаётся таблица бронирования для теннисных кортов на день: {Номер корта, Время начала, Время окончания, Тариф, Член клуба}. Тариф зависит от выбранного корта и членства в клубе.

Таким образом, возможны следующие составные первичные ключи: {Номер корта, Время начала}, {Номер корта, Время окончания}, {Тариф, Время начала}, {Тариф, Время окончания}.

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

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

Можно улучшить структуру, разбив таблицу на две: {Номер корта, Время начала, Время окончания, Член клуба} и {Тариф, Номер корта, Член клуба}. Данное отношение будет соответствовать BCNF.

Четвёртая нормальная форма

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

Пример

Предположим, что рестораны производят разные виды пиццы, а службы доставки ресторанов работают только в определенных районах города. Составной ключ таблицы такого отношения включает три поля: {Ресторан, Вид пиццы, Район доставки}.

Такая таблица не соответствует 4NF, так как существует многозначная зависимость:

• {Ресторан} →→ {Вид пиццы}

• {Ресторан} →→ {Район доставки}

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

Для предотвращения аномалии нужно разбить многозначную зависимость — разместить независимые факты в разных таблицах. В данном примере - {Ресторан, Вид пиццы} и {Ресторан, Район доставки}.

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

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

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

Пример

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

Отношение {Продавец, Фирма, Вид товара} соответствует 4NF, однако не отражает ограничения, связанного с ассортиментом продукции фирм. Может возникнуть кортеж, в котором фирме будет соответствовать вид товара, который она не выпускает.

В данном случае (для приведения к 5NF) отношение должно быть разбито на три: {Продавец, Фирма}, {Фирма, Вид товара}, {Продавец, Вид товара}.

Пример проектирования и составления БД

Рассмотрим следующее задание:

Экзамены:

• ФИО преподавателя (работает на кафедре);

• группа;

• дата экзамена;

• наименование экзамена;

• кафедра.

Отчет – выбрать все экзамены группы по датам.

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

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

• атрибуты должны зависеть от ключа, от всего ключа целиком и ни от чего другого, кроме ключа. Если это не так, следует разбить атрибуты на отдельные отношения;

• если можно разбить отношение на два или более отношений с меньшим количеством атрибутов таким образом, что соединение последних в точности воспроизводит исходное состояние, то это нужно сделать.

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

Отношение R находится в первой нормальной форме тогда и только тогда, когда все входящие в него домены содержат только атомарные (неделимые) значения. Основные действия:

• удалить повторяющиеся группы в отдельных таблицах;

• создать отдельные таблицы для каждого множества отношений;

• определить первичный ключ в каждом отношении.

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

Мы имеем следующие атрибуты: ФИО преподавателя, Группа, Дата экзамена, Наименование экзамена, Кафедра. Добавим к ним Код преподавателя, Код кафедры, Код экзамена, Код группы.

В качестве ключевого атрибута выберем Код преподавателя и определим его связь с другими атрибутами.

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

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

Оба полученных отношения находятся в первой нормальной форме.

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

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

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

Рассмотрим подробнее отношение Преподаватель–Экзамен–Группа. Можно заметить, что атрибуты, связанные группой, зависят только от части первичного ключа. Также Наименование экзамена зависит только от части первичного ключа.

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

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

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

Основное действие: удаление транзитивной зависимости.

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

В результате получены пять отношений: Преподаватель, Кафедра, Экзамен, Группа, Дата экзамена, каждое из которых находится в третей нормальной форме. Нормализация завершена.

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

Какими должны быть таблицы сущностей

Основное правило при создании таблиц сущностей - это «каждой сущнос­ти - отдельную таблицу» (как в популярном лозунге: «каждой семье - от­дельную квартиру»),

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

Обычно достаточно определения простого ключа, реже - вводят составной ключ. Таблицей с составным ключом может быть, например, таблица хранения списка сотрудников (фамилия, имя и отчество), в котором встречаются однофа­мильцы. В некоторых СУБД пользователям предлагается определить автомати­чески создаваемое ключевое поле нумерации (в Access - это поле типа «счет­чик»), которое упрощает решение проблемы уникальности записей таблицы.

Иногда в таблицах сущностей имеются поля описания свойств или характерис­тик объектов. Если в таблице есть значительное число повторений по этим полям и эта информация имеет существенный объем, то лучше их выделить в отдельную таблицу (придерживаясь правила: «каждой сущности - отдельную таблицу»). Тем более, следует образовать дополнительную таблицу, если свойства взаимосвязаны.

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

В процессе обработки таблиц сущностей надо иметь в виду следующее. Новую сущность легко добавить и изменить, но при удалении следует уничтожить все ссылки на нее из таблиц связей, иначе таблицы связей будут некорректными. Мно­гие современные СУБД блокируют некорректные действия в подобных операциях.

Организация связи сущностей

Записи таблицы связей предназначены для отображения связей между сущностями, информация о которых находится в соответствующих табли­цах сущностей.

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

Более сложные связи (не бинарные) следует сводить к бинарным. Для описания взаимосвязей N объектов требуется N-1 таблиц связей. Транзитивных связей не должно быть. Избыток связей приводит к противоречиям.

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

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

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