3.2. Нормализация отношений реляционных баз данных
Под нормализацией подразумевается попытка предотвратить аномалии обновления реляционных таблиц (R-таблиц), то есть ситуации, которые могут привести к противоречивости данных. В теории реляционных баз данных обычно выделяется следующая последовательность нормальных форм:
первая нормальная форма (1NF);
вторая нормальная форма (2NF);
третья нормальная форма (3NF);
нормальная форма Бойса-Кодда (BCNF);
четвертая нормальная форма (4NF);
пятая нормальная форма, или нормальная форма проекции-соединения (5NF или PJ/NF).
Процесс нормализации заключается в разложении (декомпозиции) исходных отношений БД на более простые отношения. Каждая ступень этого процесса приводит схему отношений в последовательные нормальные формы. Для каждой ступени нормализации имеются наборы ограничений, которым должны удовлетворять отношения БД. Нормализация позволяет удалить из таблиц базы избыточную неключевую информацию.
3.2.1. Пример декомпозиции исходной «универсальной» таблицы на простые отношения.
Предположим, что проектирование базы данных с условным названием "Питание" начинается с выявления атрибутов и подбора данных, образец которых (часть блюд изготовленных и реализованных 1/9/94 г.) показан на рис. 3.2.1.1.
Блюдо
|
Вид
|
Ре-цепт
|
Пор-ций
|
Дата Р
|
Про-дукт
|
Калорий-ность
|
Вес (г)
|
Поставщик
|
Город
|
Страна
|
Вес (кг)
|
Цена ($)
|
Дата П
|
Лобио
|
Закуска
|
Лом.
|
158
|
1/9/94
|
Фасоль
|
3070
|
200
|
"Хуанхэ"
|
Пекин
|
Китай
|
250
|
0.37
|
24/8/94
|
|
|
|
|
|
Лук
|
450
|
40
|
"Наталка"
|
Киев
|
Украина
|
100
|
0.52
|
27/8/94
|
|
|
|
|
|
Масло
|
7420
|
30
|
"Лайма"
|
Рига
|
Латвия
|
70
|
1.55
|
30/8/94
|
|
|
|
|
|
Зелень
|
180
|
10
|
"Даугава"
|
Рига
|
Латвия
|
15
|
0.99
|
30/8/94
|
Харчо
|
Суп
|
...
|
144
|
1/9/94
|
Мясо
|
1660
|
80
|
"Наталка"
|
Киев
|
Украина
|
100
|
2.18
|
27/8/94
|
|
|
|
|
|
Лук
|
450
|
30
|
"Наталка"
|
Киев
|
Украина
|
100
|
0.52
|
27/8/94
|
|
|
|
|
|
Томаты
|
240
|
40
|
"Полесье"
|
Киев
|
Украина
|
120
|
0.45
|
27/8/94
|
|
|
|
|
|
Рис
|
3340
|
50
|
"Хуанхэ"
|
Пекин
|
Китай
|
75
|
0.44
|
24/8/94
|
|
|
|
|
|
…
|
…
|
…
|
…
|
…
|
…
|
…
|
…
|
…
|
Рис. 3.2.1.1.Данные, необходимые для создания базы данных "Питание"
Этот вариант таблицы "Питание" не является отношением, так как большинство ее строк не атомарны. Атомарными являются лишь значения полей Блюдо, Вид, Рецепт (хотя он и большой), Порций и Дата_Р. Остальные же поля таблицы рис. 3.2.1.1– множественные.
Для придания таким данным формы отношения необходимо реконструировать таблицу. Наиболее просто это сделать с помощью простого процесса вставки, результат которой показан на рис. 3.2.1.2. Однако такое преобразование приводит к возникновению большого объема избыточных данных.
Блюдо
|
Вид
|
Рецепт
|
Порций
|
Дата Р
|
Продукт
|
Калорий-ность
|
Вес (г)
|
Поставщик
|
Город
|
Страна
|
Вес (кг)
|
Цена ($)
|
Дата П
|
Лобио
|
Закуска
|
Лом.
|
158
|
1/9/94
|
Фасоль
|
3070
|
200
|
"Хуанхэ"
|
Пекин
|
Китай
|
250
|
0.37
|
24/8/94
|
Лобио
|
Закуска
|
Лом
|
108
|
1/9/94
|
Лук
|
450
|
40
|
"Наталка"
|
Киев
|
Украина
|
100
|
0.52
|
27/8/94
|
Лобио
|
Закуска
|
Лом
|
108
|
1/9/94
|
Масло
|
7420
|
30
|
"Лайма"
|
Рига
|
Латвия
|
70
|
1.55
|
30/8/94
|
Лобио
|
Закуска
|
Лом
|
108
|
1/9/94
|
Зелень
|
180
|
10
|
"Даугава"
|
Рига
|
Латвия
|
15
|
0.99
|
30/8/94
|
Харчо
|
Суп
|
...
|
144
|
1/9/94
|
Мясо
|
1660
|
80
|
"Наталка"
|
Киев
|
Украина
|
100
|
2.18
|
27/8/94
|
Харчо
|
Суп
|
...
|
144
|
1/9/94
|
Лук
|
450
|
30
|
"Наталка"
|
Киев
|
Украина
|
100
|
0.52
|
27/8/94
|
Харчо
|
Суп
|
...
|
144
|
1/9/94
|
Томаты
|
240
|
40
|
"Полесье"
|
Киев
|
Украина
|
120
|
0.45
|
27/8/94
|
Харчо
|
Суп
|
...
|
144
|
1/9/94
|
Рис
|
3340
|
50
|
"Хуанхэ"
|
Пекин
|
Китай
|
75
|
0.44
|
24/8/94
|
Харчо
|
Суп
|
...
|
144
|
1/9/94
|
Масло
|
7420
|
15
|
"Полесье"
|
Киев
|
Украина
|
50
|
1.62
|
27/8/94
|
…
|
…
|
...
|
…
|
…
|
…
|
...
|
…
|
…
|
…
|
…
|
…
|
…
|
…
|
Рис. 3.2.1.2. Универсальное отношение "Питание"
Таблица на рис. 3.2.1.2. представляет собой экземпляр корректного отношения. Его называют универсальным отношением проектируемой БД. В одно универсальное отношение включаются все представляющие интерес атрибуты, и оно может содержать все данные, которые предполагается размещать в БД в будущем. Для малых БД (включающих не более 15 атрибутов) универсальное отношение может использоваться в качестве отправной точки при проектировании БД.
3.2.2. Проблемы, возникающие при использовании универсального отношения
1. Избыточность. Данные практически всех столбцов многократно повторяются. Повторяются и некоторые наборы данных (Блюдо-Вид-Рецепт, Продукт-Калорийность, Поставщик-Город-Страна). Нежелательно повторение рецептов, некоторые из которых намного больше рецепта "Лобио". И уж совсем плохо, что все данные о блюде (включая рецепт) повторяются каждый раз, когда это блюдо включается в меню.
2. Потенциальная противоречивость (аномалии обновления). Вследствие избыточности можно обновить адрес поставщика в одной строке, оставляя его неизменным в других. Если поставщик кофе сообщил о своем переезде в Харбин и была обновлена строка с продуктом кофе, то у поставщика "Хуанхэ" появляются два адреса, один из которых не актуален. Следовательно, при обновлениях необходимо просматривать всю таблицу для нахождения и изменения всех подходящих строк.
3. Аномалии включения. В БД не может быть записан новый поставщик ("Няринга", Вильнюс, Литва), если поставляемый им продукт (Огурцы) не используется ни в одном блюде. Можно, конечно, поместить неопределенные значения в столбцы Блюдо, Вид, Порций и Вес (г) для этого поставщика. Но если появится блюдо, в котором используется этот продукт, не забудем ли мы удалить строку с неопределенными значениями?
По аналогичным причинам нельзя ввести и новый продукт (например, Баклажаны), который предлагает существующий поставщик (например, "Полесье"). А как ввести новое блюдо, если в нем используется новый продукт (Крабы)?
4. Аномалии удаления. Обратная проблема возникает при необходимости удаления всех продуктов, поставляемых данным поставщиком или всех блюд, использующих эти продукты. При таких удалениях будут утрачены сведения о таком поставщике.
Многие проблемы этого примера исчезнут, если выделить в отдельные таблицы сведения о блюдах, продуктах, рецептах, расходе блюд, поставщиках продуктов и их городах, а также создать связующие таблицы "Состав" и "Поставки" (Рис. 3.2.2.1).
Блюда Продукты
-
-
Код блюда
|
Блюдо
|
Вид
|
1
|
Лобио
|
Закуска
|
2
|
Харчо
|
Суп
|
3
|
Шашлык
|
Горячее
|
…
|
…
|
…
|
|
Код продукта
|
Продукт
|
Калорийность
|
1
|
Фасоль
|
3070
|
2
|
Лук
|
450
|
3
|
Масло
|
7420
|
…
|
…
|
…
|
|
Состав Расход
-
-
-
-
Код блюда
|
Код продукта
|
Веc (г)
|
1
|
1
|
200
|
1
|
2
|
40
|
1
|
3
|
30
|
2
|
5
|
80
|
…
|
…
|
…
|
|
Код блюда
|
Порций
|
Дата_Р
|
1
|
158
|
1/9/94
|
2
|
144
|
1/9/94
|
3
|
207
|
1/9/94
|
4
|
235
|
1/9/94
|
...
|
...
|
...
|
|
Рецепты Поставщики
-
-
Код блюда
|
Рецепт
|
1
|
Ломаную очищ…
|
2
|
…
|
3
|
…
|
...
|
...
|
|
Код поставщика
|
Поставщик
|
Код города
|
1
|
"Полесье"
|
1
|
2
|
"Наталка"
|
1
|
3
|
"Хуанхэ"
|
2
|
4
|
"Лайма"
|
3
|
|
Поставки Города
-
Код поставщика
|
Код продукта
|
Вес (кг)
|
Цена
|
Дата_П
|
1
|
6
|
120
|
0.45
|
27/8/94
|
1
|
3
|
50
|
1.82
|
27/8/94
|
1
|
2
|
50
|
0.61
|
27/8/94
|
2
|
2
|
100
|
0.52
|
27/8/94
|
2
|
5
|
100
|
2.18
|
27/8/94
|
…
|
…
|
…
|
…
|
…
|
|
Код города
|
Город
|
Страна
|
1
|
Киев
|
Украина
|
2
|
Пекин
|
Китай
|
4
|
Москва
|
Россия
|
5
|
Сочи
|
Россия
|
3
|
Рига
|
Латвия
|
…
|
…
|
…
|
|
Рис. 3.2.2.1. Преобразование универсального отношения "Питание"
Каждая таблица состоит из однотипных строк и имеет уникальное имя.
Строки имеют фиксированное число полей (столбцов) и значений (множественные поля и повторяющиеся группы недопустимы). Иначе говоря, в каждой позиции таблицы на пересечении строки и столбца всегда имеется в точности одно значение или ничего.
Строки таблицы обязательно отличаются друг от друга хотя бы единственным значением, что позволяет однозначно идентифицировать любую строку такой таблицы.
Столбцам таблицы однозначно присваиваются имена, и в каждом из них размещаются однородные значения данных (даты, фамилии, целые числа или денежные суммы).
Полное информационное содержание базы данных представляется в виде явных значений данных и такой метод представления является единственным.
При выполнении операций с таблицей ее строки и столбцы можно обрабатывать в любом порядке.
Удаление сведений о некоторых поставках или блюдах не приводит к потере сведений о поставщиках.
Включение осуществляется простым добавлением строк в соответствующую таблицу.
Для исключения ссылок на длинные текстовые значения последние обычно нумеруют: нумеруют блюда в больших кулинарных книгах, товары (продукты) в каталогах и т.д. Воспользуемся этим приемом для исключения избыточного дублирования данных и появления ошибок при копировании длинных текстовых значений (рис. 3.2.2.1). Теперь при изменении названия поставщика "Полесье" на "Днепро" исправляется единственное значение в таблице Поставщики. И даже если оно вводится с ошибкой ("Днипро"), то это не может повлиять на связь между поставщиками и продуктами (в связующей таблице Поставки используются номера поставщиков и продуктов, а не их названия).
3.2.3. Первая нормальная форма (1NF).
Первый этап нормализации БД - назначение первичных ключей для каждой таблицы, т.е. превращение ее в отношение, проверка атрибутов таблиц на атомарность и выделение атрибутов, имеющих более одного значения, в новые сущности.
Атомарным называется атрибут, значения которого логически неделимы.
Примеры:
Название фабрики "Красная синька" содержит два слова, которые служат названием только в этом сочетании, в то время как по частям "красная" характеризует цвет, а "синька" - изделие. Название фабрики разделению не подлежит.
В отличие от первого примера атрибут рабочего "Сидоров Иван Петрович", содержит три части - фамилию, имя и отчество человека, которого он характеризует, т.е. должен быть разбит на три атомарных атрибута, каждый из которых может употребляться самостоятельно.
Атрибут "Дата рождения рабочего", как и "Фамилия Имя Отчество", не является атомарным, поскольку включает в себя три понятия "Год", "Месяц" и "День". Однако в БД существуют типы данных - Date и DateTime, которые позволяют выполнять над датами специальные операции, например, < дата 1 > - < дата 2 > = < количество дней между ними > . В связи с этим значения дат в базах данных считаются атомарными.
Таблица находится в первой нормальной форме тогда и только тогда, когда ни одна из ее строк не содержит в любом своем поле более одного значения и ни одно из ее ключевых полей не пусто.
БД находится в первой нормальной форме, если все ее таблицы являются отношениями, а столбцы таблиц удовлетворяют условию атомарности
В задачах часто встречаются данные, имеющие более одного значения. Примером может служить заказ на изготовление каких - либо изделий, в который вошли названия изделий, их цены и заказанное количество:
№
|
Заказ- чик
|
Изделие 1
|
Изделие 2
|
Изделие 3
|
Дата заказа
|
Назва- ние
|
Цена
|
Кол-во
|
Назва- ание
|
Цена
|
Кол-во
|
Назва- ние
|
Цена
|
Кол-во
|
Сущность "Заказ" с атрибутами, полностью отражающими приведенную таблицу, хотя и находится в первой нормальной форме, но обладает двумя недостатками:
Если заказчику требуется только один или два вида изделий, в таблице окажутся пустые ячейки, что понижает коэффициент использования памяти. Для четвертого вида изделий место в заказе просто отсутствует, т.е. ему придется оформлять второй заказ. Значит, в таблице появится лишняя строка и новые пустые ячейки
Поиск изделий с конкретным названием усложнен, так как его приходится искать в нескольких столбцах таблицы.
Эти недостатки можно устранить одним из двух способов:
Разбить строку таблицы на несколько строк, в каждой из которых будет стоять только одна группа повторяющихся атрибутов (в рассмотренном примере это Название изделия, Цена, Количество);
Перевести повторяющиеся атрибуты в новую сущность, назначить ей первичный ключ (Код изделия) и связать с исходной сущностью ссылкой на первичный ключ последней (Номер заказа).
Итак, была сущность:
При использовании первого способа ее экземпляр будет иметь атрибуты:
В результате этого преобразования увеличилось количество строк, зато повысилась плотность записи и упростился поиск изделий.
После выделения новой сущности (второй способ преобразования), стало:
3.2.4. Вторая нормальная форма (2NF)
Вторая нормальная форма к требованию атомарности атрибутов добавляет еще одно - каждый неключевой атрибут должен функционально полно зависеть от первичного ключа (не должен зависеть от части составного ключа).
Таблица находится во второй нормальной форме, если она удовлетворяет определению 1НФ и все ее поля, не входящие в первичный ключ, связаны полной функциональной зависимостью с первичным ключом.
Состав созданной нами в предыдущем примере сущности "Заказанное изделие" служит характерным примером нарушения второй нормальной формы. Первичный ключ этой сущности - сочетание атрибутов Номер заказа и Код изделия. От этого составного ключа зависит один атрибут - Количество заказанных изделий; остальные атрибуты - Название и Цена изделия зависят только от Кода изделия.
Чтобы привести сущность "Заказанное изделие" ко второй нормальной форме, выделим из нее атрибуты, характеризующие изделие как таковое, создав еще одну сущность - "Изделие" и будем ссылаться на нее из "Заказанного изделия" через Код изделия :
Другой пример сущности, в которой часть неключевых атрибутов не зависит от первичного ключа:
Даже если существует ограничение, что один лектор может читать только одну дисциплину, такой состав атрибутов будет служить источником ошибок:
При исключении дисциплины из планов обучения, вместе с нею пропадает и информация о лекторе.
Если какой-либо лектор временно прекращает читать свою дисциплину, информация о нем также пропадает.
Правильным решением в этом случае будет выделение информации о лекторе в отдельную сущность со ссылкой на код дисциплины:
А если учесть, что на самом деле один лектор может читать более одной дисциплины, также как и одну и ту же дисциплину могут читать несколько лекторов, необходимо отказаться от жесткой привязки лектора к дисциплине в сущности "Лектор", создав дополнительную сущность, которая будет показывать, как связаны между собой лекторы и дисциплины:
3.2.5. Третья нормальная форма (3NF)
Третья нормальная форма подразумевает атомарность и функционально полную зависимость атрибутов каждой сущности от ее первичного ключа. Кроме того, между неключевыми атрибутами сущности должны отсутствовать транзитивные зависимости, т.е. они должны быть взаимно независимы.
Таблица находится в третьей нормальной форме, если она удовлетворяет определению 2НФ и не одно из ее неключевых полей не зависит функционально от любого другого неключевого поля.
В качестве примера рассмотрим сущность «Сотрудник» гипотетической Организации, в которую человека принимают на конкретную должность, а по истечении срока или какой-либо другой причине - увольняют. Переходы с одной должности на другую там запрещены:
Здесь неключевые атрибуты Название должностиi и Окладi находятся в транзитивной зависимости.
В чем опасность такой зависимости? Во-первых, несколько человек могут работать в одной и той же должности. При изменении должностного оклада в этом случае нужно будет менять данные в каждой записи, содержащей эту должность. Во-вторых, в организации могут оказаться уникальные должности, например, начальник, и тогда при увольнении этого сотрудника (удалении записи) потеряется информация об окладе для этой должности.
В рассмотренной ситуации нужно было создать новую сущность "Должность" с находящимися в транзитивной зависимости атрибутами - Название должностиi и Окладi и сделать ссылку от "Сотрудника" на "Должность":
В реальных условиях сотрудник может переходить с одной должности на другую и может одновременно занимать несколько должностей в своей организации. В связи с этим нужно создать еще одну сущность, которая будет фиксировать должностные перемещения сотрудников, перенеся в нее атрибуты Код должностиi , Дата зачисленияi и Дата увольненияi, и ссылаться на "Сотрудника" через Табельный номерi:
Обычно при моделировании БД нормализацию на этом заканчивают, так как дальнейшая декомпозиция таблиц замедляет обработку данных.
В заключение рассмотрим еще один пример.
Пример: Имеется сущность "Наряд на изготовление изделий" с атрибутами: номер наряда, дата и номер смены, на которую выписан наряд, фамилия, имя, отчество и табельный номер исполнителя, наименование операции 1, стоимость ее выполнения, необходимое количество1, наименование операции 2, стоимость ее выполнения, необходимое количество 2, …, общая стоимость работ. Пусть наряды в каждом цехе ежедневно нумеруются с единицы.
Чтобы привести сущность к первой нормальной форме, добавим к приведенному списку атрибут номер цеха. Теперь атрибуты номер цеха , дата выписки наряда и номер наряда совместно однозначно характеризуют каждый ее экземпляр, т.е. являются первичным ключом.
Итак, сущность " Наряд " является отношением, но в ней имеются избыточная информация и повторяющиеся атрибуты.
Избыточная информация - вычисляемый атрибут: «Общая стоимость работ», которая определяется как сумма произведений стоимости выполнения операции на количество операций; удаляем этот атрибут.
Повторяющиеся атрибуты - наименование, стоимость и количество для каждого вида операций в текущем наряде. Как уже отмечалось, наличие повторяющихся атрибутов ведет к снижению коэффициента использования дисковой памяти. Выделяем из сущности " Наряд " дочернюю сущность - "Выполняемая операция". При выделении в дочернюю сущность мигрирует первичный ключ родительской сущности.
Первичным ключом новой сущности может служить сочетание мигрировавших в нее ключей и наименования операции. Однако, наименование операции может быть очень длинным и потому не подходит для ключевого поля. Добавляем сюда код операции.
Проверяем неключевые атрибуты сущности "Выполняемая операция" на зависимость от первичного ключа: наименование и цена зависят лишь от кода операции. Выделяем их в родительскую сущность "Операция" с первичным ключом код операции.
3.2.6. Нормальная форма Бойса-Кодда (BCNF)
Рассмотрим внимательно таблицы, представленные на рис. 3.2.2.1.Среди них таблицы «Блюда» и «Продукты» не удовлетворяют определению 3НФ. Действительно, в этих таблицах имеются функциональные зависимости между неключевыми полями:
Блюдо->Вид и Продукт->Калорийность.
Следовательно, для приведения таблиц «Блюда» и «Продукты» к 3НФ их надо разбить на
Блюда(Код блюда, Блюдо),
Вид_блюда(Код блюда, Вид);
Продукты(Код продукта, Продукт);
Калор_прод(Код продукта,Калорийносить),
хотя интуиция подсказывает, что это лишнее разбиение, совсем не улучшающее проекта базы данных.
Столкнувшись с подобными несуразностями, которые могут возникать не только из-за введения кодированных первичных ключей, теоретики реляционных систем Кодд и Бойс обосновали и предложили более строгое определение для 3НФ, которое учитывает, что в таблице может быть несколько возможных ключей.
Таблица находится в нормальной форме Бойса-Кодда, если и только если любая функциональная зависимость между его полями сводится к полной функциональной зависимости от возможного ключа.
В соответствие с этой формулировкой таблицы «Блюда» и «Продукты» рис. 3.2.2.1, имеющие по паре возможных ключей (Код блюда, Блюдо) и (Код продукта, Продукт) находятся в НФБК или в 3НФ.
3.2.7. Четвертая нормальная форма (4NF). Пятая нормальная форма, или нормальная форма проекции-соединения (5NF или PJ/NF)
В следующих нормальных формах (4НФ и 5НФ) учитываются не только функциональные, но и многозначные зависимости между полями таблицы. Для их описания познакомимся с понятием полной декомпозиции таблицы.
Полной декомпозицией таблицы называют такую совокупность произвольного числа ее проекций, соединение которых полностью совпадает с содержимым таблицы.
Например, естественным соединением таблиц рис. 3.2.2.1 можно образовать исходную таблицу, приведенную на рис. 3.2.1.2. Следовательно, таблицы рис. 3.2.2.1 являются полными декомпозициями таблицы Питание рис. 3.2.1.2. Теперь можно дать определения высших нормальных форм. И сначала будет дано определение для последней из предложенных – 5НФ.
Таблица находится в пятой нормальной форме (5НФ) тогда и только тогда, когда в каждой ее полной декомпозиции все проекции содержат возможный ключ. Таблица, не имеющая ни одной полной декомпозиции, также находится в 5НФ.
Четвертая нормальная форма является частным случаем 5НФ, когда полная декомпозиция должна быть соединением ровно двух проекций. Весьма не просто подобрать реальную таблицу, которая находилась бы в 4НФ, но не была бы в 5НФ.
|
