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

1. Лекция 6. Нормализация БД

2.

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

3.

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

4.

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

5. Первая нормальная форма (1НФ)

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

6.

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

7.

• Ключевым свойством первой нормальной
формы является атомарность: значение в ячейки
таблицы может быть только одно, например, если
столбец называется ФИО, то значение будет Иванов
Иван Иваны, а если столбец должность, то значение
будет инженер, но никак не: механик, электрик,
сантехник.
На рисунке 1 таблица не находится в первой нормальной форме, поэтому не
является отношением. Всё дело в том, что у сотрудника Пупкина Сергея в
столбце должность указано два значения: механик и сантехник. Давайте
приведем нашу таблицу к первой нормальной форме.

8.

• Таблица на Рисунке 2 действительно находится в
первой нормальной форме, поскольку в каждой
ячейки содержится только одно логическое
значение, то есть было соблюдено свойство
атомарности. Добились мы этого путем
дублирования данных о сотруднике с кодом 1,
следовательно, мы внесли избыточность в базу
данных, но привели ее к первой нормальной
форме. Избавиться от избыточности в базе данных
нам поможет вторая нормальная форма.

9. Вторая нормальная форма (2НФ)

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

10.

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

11.

• Обратите внимание на зеленые столбцы: в
этих столбцах содержится информация о
издательствах: наименование и номер.
Номер издательства и его название связаны
между собой функционально, то есть
между ними есть функциональная
зависимость, детерминантом в которой
является наименование издательства.
Голубые столбцы: автор и день рождения
автора, так же связаны между собой.

12.

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

13.

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

14. Пример ER-диаграммы 2nf

Пример ERдиаграммы
2nf
На рисунке мы видим две базы данных:
одна из них находится в первой
нормальной форме, вторая – во второй.
Условимся, что у книги может быть
только один автор и одно издательство,
чтобы не усложнять нашу структуру.
Поэтому таблица Books (2nf) связана с
таблицей Author (2nf) связью один ко
многим (один автор может написать
несколько книг). Аналогично таблица
Books (2nf) связана с таблицей Publish
(2nf) связью один ко многим: одна
запись из таблицы издательства может
соответствовать нескольким записям из
таблицы книг (соответствующая
стрелочка на диаграмме это отображает).
на рисунке выделены атрибуты ZIP и City,
данные атрибуты имеют транзитивную
зависимость (зависят не только от ключа,
но и друг от друга), такая зависимость
ведет к аномалии (логическим и
смысловым ошибкам). Например,
оператор, наполняющий базу данных в
поле City записал Новосибирск, а в поле
индекс написал 644000 (индекс Омска),
какому значению верить? Чтобы нам
избавиться от транзитивной
зависимости, необходимо привести нашу
базу данных к третьей нормальной
форме.

15. Третья нормальная форма (3НФ)

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

16.

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

17.

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

18.

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