ПП.02 Тема: «Нормализация баз данных» Нормализация баз данных 1 Примеры Элементы теории нормализации отношений
2 21.11.2022 Нормализации баз данных Нормализации баз данных Данные могут группироваться в таблицы (отношения) разными способами. При проектировании БД в качестве отправной точки может использоваться одно универсальное отношение, в которое включаются все необходимые атрибуты. Оно может содержать все данные, которые предполагается размещать в БД. В качестве примера рассмотрим универсальное отношение сотрудники, содержащее информацию о сотрудниках предприятия (таблица 1).
3 21.11.2022 Нормализации баз данных Нормализации баз данных Таблица 1 – Исходное отношение Код сотруд - ника ФИО Долж - ность Номер отдела Наимено - вание отдела Квалифи - кация 7513 Иванов И.И. Програм-мист 128 Отдел проекти-рования C, Java 9842 Сергеева С.С. Админист-ратор БД 42 Финансовый отдел DB2 6651 Петров П.П. Програм-мист 128 Отдел проекти-рования VB, Java 9006 Николаев Н.Н. Системный админист-ратор 128 Отдел проекти-рования Windows, Linux
4 21.11.2022 Нормализации баз данных Нормализации баз данных При использовании универсального отношения возникают две проблемы: избыточность данных; потенциальная противоречивость (аномалии). Под избыточностью понимают повторение данных в разных строках одной таблицы или в разных таблицах БД. Так, для каждого сотрудника отдела 128 повторяются данные «128, Отдел проектирования». Аномалии – это проблемы, возникающие в данных из-за дефектов проектирования БД. Существуют три вида аномалий: вставки, удаления и модификации.
5 21.11.2022 Нормализации баз данных Нормализации баз данных Аномалии вставки проявляются при вводе данных в дефектную таблицу. Добавляя информацию о новом сотруднике, мы должны добавить номер и название отдела. Если ввести данные, не соответствующие имеющимся в таблице (например, 42, отдел проектирования), будет не ясно, какая из строк БД содержит правильную информацию. Аномалии удаления возникают при удалении данных из дефектной схемы. Предположим, что все сотрудники отдела 128 уволились в один и тот же день. После удаления записей этих сотрудников в БД больше не будет ни одной записи, содержащей информацию об отделе 128.
6 21.11.2022 Нормализации баз данных Нормализации баз данных Аномалии модификации возникают при изменении данных дефектной схемы. Предположим, что отдел 128 решили переименовать в отдел передовых технологий. Необходимо изменить соответствующие данные о каждом сотруднике отдела. Если мы пропустим хотя бы одну запись, возникнет аномалия модификации. Правилом разработки хорошей структуры БД является необходимость избегать схем с большим числом пустых атрибутов. Если мы хотим указать, что один из ста служащих имеет особую квалификацию, для хранения этой информации не следует добавлять в таблицу еще один столбец, поскольку для остальных 99 работников значением столбца будет NULL. Вместо этого следует добавить новую таблицу, в которой будут храниться только кодовые номера и информация о квалификации тех работников, которых это касается.
7 21.11.2022 Нормализации баз данных Нормализации баз данных Решение перечисленных проблем состоит в разделении данных и связей, что обеспечивается процедурой нормализации. Концепции и методы нормализации были разработаны Э. Ф. Коддом. Нормализация отношений – это формальный аппарат ограничений на формирование отношений, который позволяет устранить дублирование и потенциальную противоречивость хранимых данных, уменьшает трудозатраты на ведение БД. Процесс нормализации заключается в декомпозиции исходных отношений на более простые отношения. Цель нормализации – получение такого проекта БД, в котором «каждый факт появляется лишь в одном месте».
8 21.11.2022 Элементы теории нормализации отношений Элементы теории нормализации отношений При переходе от ЕR-диаграммы к схеме реляционной БД возникают некоторые проблемы.Главная из них - ненормализованность полученных отношений, а значит, избыточное дублирование данных. Наиболее распространенная причина данного явления - недостаточная детализация ER- диаграммы. Это приводит к попаданию в одно отношение наряду с зависимостями от ключа других зависимостей между атрибутами.
9 21.11.2022 Элементы теории нормализации отношений Элементы теории нормализации отношений Теория нормализации основана на наличии зависимостей между атрибутами отношения. Существуют различные классы зависимостей между атрибутами. Наиболее простые и важные из них - функциональные зависимости. Функциональные зависимости, так же как и ключи, - ограничения целостности, диктуемые предметной областью. Знать этот вид ограничений необходимо для того, чтобы уметь нормализовать отношения.
10 21.11.2022 Нормализации баз данных Нормализации баз данных Теория нормализации основана на наличии зависимостей между атрибутами отношения. Основными видами зависимостей являются: функциональные; многозначные; транзитивные
11 21.11.2022 Элементы теории нормализации отношений Элементы теории нормализации отношений Ф ормальное определение и его толкование Пусть R - схема отношения r и А, В - некоторые атрибуты из R. Говорят, что отношение r (R) удовлетворяет функциональной зависимости А В, если каждому значению А соответствует ровно одно связанное с ним значение В ( в любой момент времени). Запись А В читается так: " А функционально определяет В " Запись, А В можно прочитать так: " В функционально зависит от А "
12 21.11.2022 Элементы теории нормализации отношений О пределение функциональной зависимости Элементы теории нормализации отношений А В t 0 a 0 b 0 t 1 a 1 b 1 t 2 a 1 b 1 Если t 1 и t 2 совпадают здесь Если t 1 и t 2 совпадают здесь С практической точки зрения смысл этого определения состоит в следующем: если в отношении r существует в данный момент только один кортеж t 0 с заданным значением а 0 атрибута А, то а 0 очевидным образом соответствует только одно значение b 0 атрибута В ; если в отношении r есть два или более кортежей с одним и тем же значением а 1 атрибута А, то каждый из этих кортежей должен иметь одно и то же значение b 1 атрибута В.
13 21.11.2022 Элементы теории нормализации отношений О пределение функциональной зависимости Элементы теории нормализации отношений Зависимость, при которой каждый неключевой атрибут зависит от всего составного ключа и не зависит от его частей, называется полной функциональной зависимостью. Если атрибут А зависит от атрибута В, а атрибут В зависит от атрибута С ( С В А ), но обратная зависимость отсутствует, то зависимость А от С называется транзитивной.
14 21.11.2022 Элементы теории нормализации отношений О пределение функциональной зависимости Элементы теории нормализации отношений Многозначная зависимость. Говорят, что один атрибут отношения многозначно определяет другой атрибут того же отношения, если для каждого значения первого атрибута существует множество соответствующих значений второго атрибута. Многозначные зависимости могут быть: один-ко-многим (1:М); многие-к-одному (М:1); многие-ко-многим (М:М).
15 21.11.2022 Элементы теории нормализации отношений О пределение функциональной зависимости Элементы теории нормализации отношений Каждая ступень процесса нормализации приводит схему отношений в последовательные нормальные формы. Для каждой ступени имеются наборы ограничений. Выделяют следующую последовательность нормальных форм: первая нормальная форма (1НФ); вторая нормальная форма (2НФ); третья нормальная форма (3НФ); усиленная 3НФ или нормальная форма Бойса-Кодда (БКНФ); четвертая нормальная форма (4НФ); пятая нормальная форма (5НФ).
16 21.11.2022 Нормализации баз данных Нормализация БД Нормализации баз данных Таблица 13 – Исходное отношение Код сотруд - ника ФИО Долж - ность Номер отдела Наимено - вание отдела Квалифи - кация 7513 Иванов И.И. Програм-мист 128 Отдел проекти-рования C, Java 9842 Сергеева С.С. Админист-ратор БД 42 Финансовый отдел DB2 6651 Петров П.П. Програм-мист 128 Отдел проекти-рования VB, Java 9006 Николаев Н.Н. Системный админист-ратор 128 Отдел проекти-рования Windows, Linux
17 21.11.2022 Элементы теории нормализации отношений О пределение функциональной зависимости Нормализация баз данных - 1НФ Отношение находится в первой нормальной форме (1НФ), когда каждая строка содержит только одно значение для каждого атрибута (столбца), то есть все атрибуты отношения имеют единственное значение (являются атомарными ). В столбце Квалификация ненормализованной таблицы 13 содержатся списки значений (С, Java и т. д.). Чтобы привести схему к 1НФ, необходимо разместить в этом столбце атомарные значения. Самый простой способ заключается в выделении по одной строке на каждый элемент квалификации (таблица 14).
18 21.11.2022 Нормализации баз данных Нормализация БД Нормализация баз данных - 1НФ Таблица 14 Код сотрудника ФИО Должность Номер отдела Наименование отдела Квалифи-кация 7513 Иванов И.И. Программист 128 Отдел проектирования C 7513 Иванов И.И. Программист 128 Отдел проектирования Java 9842 Сергеева С.С. Администратор БД 42 Финансовый отдел DB2 6651 Петров П.П. Программист 128 Отдел проектирования VB 6651 Петров П.П. Программист 128 Отдел проектирования Java 9006 Николаев Н.Н. Системный администратор 128 Отдел проектирования Windows 9006 Николаев Н.Н. Системный администратор 128 Отдел проектирования Linux
19 21.11.2022 Элементы теории нормализации отношений 2НФ Нормализация баз данных - 2НФ Отношение находится во второй нормальной форме (2НФ), если оно находится в 1НФ, и каждый не ключевой атрибут полностью функционально зависит от всех составляющих первичного ключа. Если атрибут не зависит полностью от первичного ключа, то он внесен ошибочно и должен быть удален. Нормализация производится путем нахождения существующего отношения, к которому относится данный атрибут, или созданием нового отношения, в который атрибут должен быть помещен.
20 21.11.2022 Элементы теории нормализации отношений 2НФ Нормализация баз данных - 2НФ Таблица Квалификации_сотрудников ( таблица 14) находится в 1НФ, но не удовлетворяет 2НФ. Первичный ключ должен уникальным образом идентифицировать каждую строку. Единственным вариантом является использование в качестве первичного ключа комбинации Код сотрудника и Квалификация. Это порождает схему: Квалификации_сотрудников (Код сотрудника, ФИО, Должность, Номер отдела, Наименование отдела, Квалификация).
21 21.11.2022 Элементы теории нормализации отношений 2НФ Нормализация баз данных - 2НФ Одной из имеющихся здесь функциональных зависимостей будет: Код сотрудника, Квалификация ФИО, Должность, Номер отдела, Наименование отдела. Но, кроме того, мы также имеем: Код сотрудника ФИО, Должность, Номер отдела, Наименование отдела. Другими словами, можно определить имя, должность и отдел, используя только код сотрудника. Это значит, что указанные атрибуты функционально зависимы только от части первичного ключа, а не от всего первичного ключа. Следовательно, указанная схема не находится в 2НФ.
22 21.11.2022 Элементы теории нормализации отношений 2НФ Нормализация баз данных - 2НФ Для приведения этой схемы в 2НФ необходимо декомпозировать исходное отношение на два, в которых все не ключевые атрибуты будут полностью функционально зависеть от ключа: сотрудники (Код сотрудника, ФИО, Должность, Номер отдела, Наименование отдела) и Квалификации_сотрудников (Код сотрудника, Квалификация) (таблицы 15–16).
23 21.11.2022 Элементы теории нормализации отношений 2НФ Нормализация баз данных - 2НФ т аблица 15 Код сотруд - ника ФИО Должность Номер отдела Наименование отдела 7513 Иванов И.И. Программист 128 Отдел проектирования 9842 Сергеева С.С. Администратор БД 42 Финансовый отдел 6651 Петров П.П. Программист 128 Отдел проектирования 9006 Николаев Н.Н. Системный администратор 128 Отдел проектирования
24 21.11.2022 Элементы теории нормализации отношений 2НФ Нормализация баз данных - 2НФ Т аблица 16 Код сотрудника Квалификация 7513 C 7513 Java 9842 DB2 6651 VB 6651 Java 9006 Windows 9006 Linux
25 21.11.2022 Элементы теории нормализации отношений 3НФ Нормализация баз данных - 3НФ Отношение находится в третьей нормальной форме (ЗНФ), если оно находится во 2НФ и ни один из его не ключевых атрибутов не связан функциональной зависимостью с любым другим не ключевым атрибутом. Атрибуты, зависящие от других не ключевых атрибутов, нормализуются путем перемещения зависимого атрибута и атрибута, от которого он зависит, в новое отношение. Формально, для приведения схемы в 3НФ необходимо исключить все транзитивные зависимости. Схема отношения сотрудники (таблица 15) содержит следующие функциональные зависимости: Код сотрудника ФИО, Должность, Номер отдела, Наименование отдела и Номер отдела Наименование отдела.
26 21.11.2022 Элементы теории нормализации отношений 3НФ Нормализация баз данных - 3НФ Первичным ключом является Код сотрудника, и все атрибуты полностью функционально зависимы от него (первичный ключ определяется единственным атрибутом). При этом Номер отдела ключом не является. Функциональная зависимость Код сотрудника Наименование отдела является транзитивной, поскольку содержит промежуточный шаг (зависимость Номер отдела Наименование отдела ). Для приведения в 3НФ необходимо исключить эту транзитивную зависимость, декомпозируя отношение на два: сотрудники (Код сотрудника, ФИО, Должность, Номер отдела) и отделы (Номер отдела, Наименование отдела) (таблицы 17–18).
27 21.11.2022 Элементы теории нормализации отношений 3НФ Нормализация баз данных - 3НФ Т аблица 17 Код сотрудника ФИО Должность Номер отдела 7513 Иванов И.И. Программист 128 9842 Сергеева С.С. Администратор БД 42 6651 Петров П.П. Программист 128 9006 Николаев Н.Н. Системный администратор 128 Номер отдела Наименование отдела 42 Финансовый отдел 128 Отдел проектирования Т аблица 18
28 21.11.2022 Элементы теории нормализации отношений 3НФ Нормализация баз данных - БКНФ Нормальная форма Бойса-Кодда (БКНФ) является развитием 3НФ и требует, чтобы в отношении были только такие функциональные зависимости, левая часть которых является потенциальным ключом отношения. Потенциальный ключ представляет собой атрибут (или множество атрибутов), который может быть использован для данного отношения в качестве первичного ключа. Фактически первичный ключ – это один из потенциальных ключей, назначенный в качестве первичного. Детерминантом называется левая часть функциональной зависимости. Отношение находится в БКНФ тогда и только тогда, когда каждый детерминант отношения является потенциальным ключом.
Элементы теории нормализации отношений 29 Алгоритм приведения ненормализованных схем в 3НФ 21.11.2022
30 21.11.2022 Элементы теории нормализации отношений 3НФ Нормализация баз данных - БКНФ Запомнить правила нормализации помогает изречение: « Нормализация – это ключ, целый ключ и ничего, кроме ключа ». Нормальные формы высших порядков (4НФ и 5НФ) представляют больший интерес для теоретических исследований, чем для практики проектирования БД. В них учитываются многозначные зависимости между атрибутами. Полной декомпозицией отношения называют такую совокупность произвольного числа его проекций, соединение которых позволяет получить исходное отношение.
