Теория нормализации
Цели теории нормализации: устранение дублирования информации; решение проблемы “присоединенных записей”. Почему дублирование информации – это плохо? Лишний расход памяти. Проблемы с корректировкой данных.
Дублирование информации Сотрудники Табельный. Ном ФИО Ном. Отдела Наз. Отдела 100 Иванов И. И 5 Отдел маркетинга 101 Петров П. П. 2 Отдел кадров 105 Сидоров С. С. 5 Отдел маркетинга 113 Новиков Н. Н. 2 Отдел кадров 128 Воробьев В. В. 2 Отдел кадров
«Присоединенные» записи Сотрудники Табельный. Ном ФИО Ном. Отдела Наз. Отдела 100 Иванов И. И 5 Отдел маркетинга 101 Петров П. П. 2 Отдел кадров 105 Сидоров С. С. 5 Отдел маркетинга Пусть требуется добавить новый отдел с номером 10 и названием «Отдел логистики» . Как это сделать в текущей таблице?
Полная декомпозиция Таблицу «Сотрудники» следует разбить на 2 проекции: Сотрудники 2(Табельный. Ном, ФИО, Ном. Отдела) Отдел (Ном. Отдела, Наз. Отдела) Если к этим проекциям применить операцию join, то получим исходную таблицу. Полная декомпозиция таблицы – это набор произвольного числа ее проекций, соединение которых идентично исходной таблице. Процесс нормализации заключается в переходе от исходной таблицы к ее полной декомпозиции вплоть до получения таблиц в пятой нормальной форме.
Первая нормальная форма Таблица находится в 1 НФ тогда и только тогда, когда в каждом ее поле (на пересечении строки и столбца) находится ровно одно значение (не более одного и не ноль значений). Специально для выполнения этого требования придумано значение NULL. Пример нарушения 1 НФ: в поле Номер. Телефона указано несколько номеров через запятую.
Первая нормальная форма Ном. Отдела Наз. Отдела Номер. Телефона 2 Отдел маркетинга 111 -111, 123 -123 5 Отдел кадров 234 -567, 222 -333 Что делать? Создать новую таблицу для многозначного атрибута, в которую нужно добавить исходный первичный ключ Ном. Отдела Наз. Отдела 2 Отдел маркетинга 5 Отдел кадров Ном. Отдела Номер. Телефона 2 111 -111 2 123 -123 5 234 -567 5 222 -333
Функциональная зависимость Пусть X и Y – списки полей таблицы. Говорят, что Y функционально зависит от X, если каждому значению X соответствует единственное значение Y. Обозначают X->Y. Любое неключевое поле функционально зависит от первичного ключа!
Полная функциональная зависимость Пусть X и Y – списки полей таблицы. Говорят, что Y находится в полной функциональной зависимости от X, если: Y функционально зависит от X; Y функционально не зависит ни от какого подмножества X, не совпадающего с X. Заказы (Номер. Заказа, Номер. Товара, Описание. Товара, Количество) Поле «Описание. Товара» зависит от части ключа «Номер. Товара» , т. е. , не находится в полной функциональной зависимости от первичного ключа таблицы.
Вторая нормальная форма Таблица находится в 2 НФ тогда и только тогда, когда каждое неключевое поле таблицы связано полной функциональной зависимостью с первичным ключом. Пример нарушения 2 НФ: рассмотрим таблицу: Заказы (Номер. Заказа, Номер. Товара, Описание. Товара, Количество) Поле «Описание. Товара» зависит от части ключа «Номер. Товара» , т. е. , не находится в полной функциональной зависимости от первичного ключа таблицы, требования 2 НФ нарушаются. Что делать?
Теорема Хита (устанавливает связь между функциональной зависимостью и полной декомпозицией таблицы). Пусть список столбцов таблицы разбит на 3 непересекающиеся части: H, J, K. Если K функционально зависит от J, то выполняется утверждение: Таблица = proj H, J (Таблица) join proj J, K (Таблица)
Вторая нормальная форма Заказы (Номер. Заказа, Номер. Товара, Описание. Товара, Количество) Поле «Описание. Товара» зависит от части ключа «Номер. Товара» . Положим J= «Номер. Товара» , K= «Описание. Товара» , H=остальные столбцы, применим теорему Хита. Получим 2 проекции: Заказы2 (Номер. Заказа, Номер. Товара, Количество) Товары(Номер. Товара, Описание. Товара, )
Третья нормальная форма Таблица находится в 3 НФ тогда и только тогда, когда она находится во 2 НФ, и не существует функциональных зависимостей между неключевыми полями. Пример нарушения 3 НФ: рассмотрим таблицу: Сотрудники (Табельный. Номер, ФИО, Номер. Отдела, Название. Отдела) Название. Отдела функционально зависит от Номер. Отдела т. е, от неключевого поля. Такие зависимости называются транзитивными. Применяя теорему Хита, разобьем эту таблицу на 2 проекции: Сотрудники 2 (Табельный. Номер, ФИО, Номер. Отдела) Отделы (Номер. Отдела, Название. Отдела)
Процесс нормализации – когда остановиться? Примечание: Обычно на практике достаточно ограничиться таблицами в 3 НФ, остальные нормальные формы нарушаются редко и представляют только теоретический интерес.
Нормальная форма Бойса-Кодда Таблица находится в НФБК тогда и только тогда, когда любая функциональная зависимость сводится к полной функциональной зависимости от первичного ключа (т. е. , нет функциональных зависимостей ключевых полей от неключевых). Пример нарушения НФБК: рассмотрим таблицу (предполагается, что нет одинаковых городов): Адреса (Индекс, Город, Улица) Город функционально зависит от Индекс. Разбивать такую таблицу на 2 проекции не стоит. Лучше смириться с таким нарушением.
Четвертая нормальная форма Таблица находится в 4 НФ тогда и только тогда, когда в каждой ее полной декомпозиции, состоящей из двух проекций, каждая проекция содержит первичный ключ исходной таблицы. Пример нарушения 4 НФ: рассмотрим таблицу Сотрудник ФИО Иностр. Язык Имя. Ребенка Смит Немецкий Джон Смит Немецкий Мэри Смит Русский Джон Смит Русский Мэри
Четвертая нормальная форма Это нарушение происходит из-за того, что два множественных атрибута объекта (т. е. , такие атрибуты, которых у каждого объекта может быть несколько) поместили в одну и ту же таблицу, в то время как для каждого множественного свойства нужно создавать отдельную таблицу Исходную таблицу нужно разбить на две проекции: ФИО Иностр. Язык ФИО Имя. Ребенка Смит Русский Смит Джон Смит Немецкий Смит Мэри
Пятая нормальная форма Таблица находится в 5 НФ тогда и только тогда, когда в каждой ее полной декомпозиции каждая проекция содержит первичный ключ исходной таблицы.
Пример процесса нормализации Грузовая судоходная компания занимается перевозкой грузов по реке. В распоряжении компании есть несколько судов, каждое судно приписано к какому-то городу. Необходимо хранить информацию о произведенных рейсах, маршрутах рейсов, составе судовых экипажей, перевезенных грузах и дополнительных затратах на перевозку (погрузка, разгрузка и т. п. ) Для каждого груза нужно хранить номер груза, тип груза, вес, объем, из какого города и в какой город он перевозится, срок доставки. Дополнительные затраты характеризуются названием, единицей измерения (например, человеко-часы для грузчиков или квадратные метры упаковочного материала), ценой и количеством.
Пример процесса нормализации Один из сотрудников (боцман) предложил следующую модель для хранения информации о работе компании: Рейс(Ном. Рейса, Наз. Судна, Порт. Судна, Маршрут) Команда(Ном. Рейса, ФИО, Таб. Номер, Должность) Затраты. На Груз(Ном. Груза, Тип. Груза, Вес, Объем, Откуда, Куда, Срок. Доставки, Ном. Рейса, Тип. Затрат, Ед. Изм, Цена, Колво) При этом в ячейке «Маршрут» содержатся примерно такие данные: «Казань, 30. 14; Ульяновск, 31. 10. 14 -01. 14; Саратов, 03. 11. 14 -03. 11. 14, . . . »
Лингвистические отношения Посмотрим, нет ли в нашей модели каких-либо объектов- или атрибутов-синонимов. Столбцы «Порт. Судна» , «Откуда» , «Куда» и названия городов в маршруте – это одинаковые данные, из них можно сделать таблицу: Города(Ном. Города, Наз. Города) Ном. Города Наз. Города 1 Казань 5 Ульяновск 10 Саратов
Первая нормальная форма В ячейке «Маршрут» нарушается первая нормальная форма: «Казань, 30. 14; Ульяновск, 31. 10. 14 -01. 14; Саратов, 03. 11. 14 -03. 11. 14, . . . » Создадим таблицу «Маршрут» : Ном. Города Дата. Прибытия 1 5 31. 10. 14 10 03. 11. 14 … … Дата. Отбытия Ном. Рейса Ном. Пункта. В Рейсе 30. 14 1 1 1 2 03. 11. 14 1 3 … … … 01. 14
Вторая нормальная форма Проверим таблицы на соответствие второй нормальной форме. Нет ли зависимостей от части первичного ключа? Команда(Ном. Рейса, ФИО, Таб. Номер, Должность) Таб. Номер (J) ->ФИО, Должность (K) Применим теорему Хита, получим 2 проекции: Сотрудники(Таб. Номер, ФИО, Должность) Команда 2(Ном. Рейса, Таб. Номер)
Вторая нормальная форма Проверим таблицы на соответствие второй нормальной форме. Нет ли зависимостей от части первичного ключа? Затраты. На Груз(Ном. Груза, Тип. Груза, Вес, Объем, Откуда, Куда, Срок. Доставки, Ном. Рейса, Тип. Затрат, Ед. Изм, Цена, Колво) Ном. Груза (J) -> Тип. Груза, Вес, Объем, Откуда, Куда, Срок. Доставки, Ном. Рейса (K) Ном. Города Применим теорему Хита, получим 2 проекции: Грузы(Ном. Груза, Тип. Груза, Вес, Объем, Откуда, Куда, Срок. Доставки, Ном. Рейса) Затраты. На Груз 2(Ном. Груза, Тип. Затрат, Ед. Изм, Цена, Колво)
Третья нормальная форма Проверим таблицы на соответствие третьей нормальной форме. Нет ли зависимостей неключевых полей друг от друга? Рейс(Ном. Рейса, Наз. Судна, Порт. Судна, Маршрут) Наз. Судна (J) -> Порт. Судна(K) Применим теорему Хита, получим 2 проекции: Ном. Города Судно(Наз. Судна, Порт. Судна) Рейс2(Ном. Рейса, Наз. Судна)
ER-модель «Грузоперевозки» Как выглядит модель при проектировании «с нуля»
Скачать презентацию Теория нормализации Цели теории нормализации устранение дублирования
e022652176bc1d2bfd15390c943403d2.ppt