Презентація на тему:
Нормалізація Бази даних та інформаційні системи

Бази даних та інформаційні системи Нормалізація

План лекции Введение. Цель и варианты применения нормализации в проектировании РБД Проблем, связанные с избыточностью данных Способы оценки применимости или качества спроектированных отношений Концепция функциональной зависимости Использование функциональной зависимости для группирования атрибутов в отношения, Способы проведения процесса нормализации Нормальные формы: 1НФ, 2НФ, 3НФ, НФБК, 4НФ, 5НФ Примеры приведения данных к 3НФ Заключение

Цель лекции:

Введение Классическая нормализация Нормальная форма свойство отношения в реляционной модели данных, характеризующее его с точки зрения избыточности, потенциально приводящей к логически ошибочным результатам выборки или изменения данных. Нормальная форма определяется как совокупность требований, которым должно удовлетворять отношение. Нормализация – процесс преобразования отношений базы данных (БД) к виду, отвечающему нормальным формам. Тип подхода к проектированию БД: ВОСХОДЯЩИЙ Базовая методология: ПОСТРОЕНИЕ ОТНОШЕНИЙ НА ОСНОВЕ АНАЛИЗА ФУКНЦИОНАЛЬНЫХ ЗАВИСИМОСТЕЙ Подход предложен Э.Ф. Коддом в 1972г. 1НФ, 2НФ, 3НФ - 1972г.; НФБК - 1974г.; 4НФ – 1977г.; 5НФ – 1979г.

Цель и варианты применения нормализации Варианты применения нормализации к проектированию РМД: Применение как восходящего подхода к проектированию, который начинается с установления связей между атрибутами. Использование для проектирования БД методологии нисходящего проектирования, где проектирование начинается с выявления основных сущностей и связей между ними, а нормализация используется лишь в качестве метода проверки корректности полученного решения. Цель нормализации – найти оптимальную группировку атрибутов для каждого отношения в схеме, что позволяет предотвратить возможное возникновение аномалий обновлений и минимизировать избыточность данных. Замечание! Нормализация предназначена для приведения структуры БД к виду, обеспечивающему минимальную логическую избыточность, и не имеет целью уменьшение или увеличение производительности работы или же уменьшение или увеличение физического объёма базы данных.  Конечной целью нормализации является уменьшение потенциальной противоречивости хранимой в базе данных информации, что достигается путем декомпозиции отношений таким образом, чтобы в каждом отношении хранились только первичные факты (то есть факты, не выводимые из других хранимых фактов).

Логическое проектирование Данный этап включает следующие шаги Создание и проверка локальной логической модели данных для отдельных пользователей Исключение особенностей, несовместимых с РМ: Преобразование двухсторонних связей типа M:N Преобразование связей с атрибутами Преобразование сложных связей Преобразование многозначных атрибутов Преобразование рекурсивных связей M:N Дополнительный анализ: Перепроверка связей типа 1:1 Анализ рекурсивных связей 1:1 Удаление избыточных связей Анализ связей супер класс/подкласс Формирование отношений на основе логической модели (раскрытие схемы) Проверка отношений с использованием средств нормализации Проверка применимости отношений для выполнения пользовательский транзакций Определение ограничений целостности Согласование локальной логической модели данных с пользователем Создание и проверка глобальной логической модели данных

Избыточность данных и аномалии обновлений Пример1 БП: Сотрудник занимает только одну должность и работает только в одном отделении. Зарплата сотрудника не зависит от должности, которую он занимает. По одному адресу расположено только одно отделение, отделение расположено только по одному адресу Таблица 1.1 - Отношение Сотрудник Таблица 1.2 – Отношение Отделение Таблица 2 – Отношение Сотрудник_Отделение

Избыточность данных и аномалии обновлений Схема 1 Сотрудник_ Отделение (СотрИН, СотрФИО, СотрДолжность, СотрЗарплата, ОтдИН, ОтдАдрес) Схема 2 Сотрудник (СотрИН, СотрФИО, СотрДолжность, СотрЗарплата, ОтдИН) Отделение (ОтдИН, ОтдАдрес) Сравнение схем: Сотрудник_ Отделение – содержит избыточные данные: сведения о отделениях повторяются для каждого сотрудника Избыточность данных приводит к аномалиям обновления: аномалии вставки, аномалии удаления, аномалии модификации

Избыточность данных и аномалии обновлений

Свойства соединения без потерь и сохранения зависимости

Функциональные зависимости В основу нормализации положена концепция функциональной зависимости (functional dependency). Функциональной зависимостью (ФЗ) атрибута в от атрибута а в отношении R – называется такая связь между атрибутами, когда каждое значение атрибута а однозначно определяет значение атрибута в (определяет только одно значение атрибута в), причем под атрибутом а может также пониматься и группа атрибутов Обозначение: Атрибут в функционально зависит от атрибута а: а в ФЗ определяется на основе ограничений ПрО (бизнес-правилами) Детерминант ФЗ - атрибут или группа атрибутов, от которого рассматривается зависимость

Функциональные зависимости Примеры: БП: Сотрудник занимает только одну должность Каждую должность может занимать много сотрудников СотрИН СотрДолжность (1:1) ФЗ 2 Ассистент СотрИН – детерминант ФЗ --------------------------------------------------------------------- СотрДолжность СотрИН (1:М) 2 Ассистент 4 6

Функциональные зависимости Замечание! 1. Пусть а в, тогда если две строки имеют одно и то же значение атрибута а, то они обязательно имеют одно и тоже значения атрибута в. Однако для заданного значения атрибута в может существовать несколько значений атрибута а. 2. Функциональная зависимость является свойством реляционной схемы, а не свойством конкретного экземпляра, т.е. должна выполняться для всех возможных значений, а не для тех, которые хранятся в атрибуте в определенный момент времени. Пример. В любой момент времени: СотрИН СотрФИО (1:1) ФЗ В определенный момент времени: СотрФИО СотрИН (1:1), но может измениться на (1:М), следовательно, ФЗ не является. 3. Тривиальные ФЗ, т.е. зависимости, когда в правой части определено подмножество множества, которое указано в левой части (детерминанте), справедливы всегда, но не представляют никакой дополнительной информации о возможных ограничениях. Примеры тривиальных зависимостей: СотрИН, СотрФИО СотрФИО СотрИН, СотрФИО СотрИН

Функциональные зависимости Пример 1 Функциональные зависимости отношения СотрудникОтделение БП: Сотрудник занимает только одну должность работает только в одном отделении. Зарплата сотрудника зависит от должности, которую он занимает, и от отделения, в котором он работает. По одному адресу расположено только одно отделение, отделение расположено только по одному адресу Функциональные зависимости:

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

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

Нормализация Нормализация – это формальный метод анализа отношений на основе их первичного ключа (или потенциальных ключей, как в случае НФБК) и существующих функциональных зависимостей. Описание. Процесс включает ряд правил, которые используются для поверки отдельных отношений таким образом, чтобы вся база данных могла быть нормализована до желаемой степени нормализации Если некоторое требование не удовлетворяется, то нарушающее данное требование отношение должно быть декомпозировано на отношения, каждое из которых удовлетворяет всем правилам нормализации. Для РМД обязательным является удовлетворение требованиям 1НФ. Все остальные НФ используются по желанию проектировщиков. Рекомендуется выполнять нормализацию до 3НФ. Ненормализованная форма (ННФ) – таблица, содержащая одну или несколько повторяющихся групп данных.

Нормализация Ненормализованная форма (ННФ) – таблица, содержащая одну или несколько повторяющихся групп данных. Повторяющейся группой называется группа, состоящая из одного и более атрибутов таблицы, в которой возможно наличие нескольких значений для единственного значения ключевого атрибута ненормализованной таблицы. Пример 2 Сведения об объектах недвижимости, арендованных клиентом Иванов И.И. Рисунок 1.1 – Исходные данные Таблица 0 - Ненормализованная таблица Клиент_Аренда_Объект_Владел

Ненормализованная форма (ННФ) Пример 2 (продолжение) БП: Клиент может арендовать некоторый объект только 1 раз Клиент не может арендовать одновременно несколько объектов Объект недвижимости принадлежит только одному владельцу Дата окончания аренды может быть изменена Оплата зависит от объекта недвижимости Таблица 0 - Ненормализованная таблица Клиент_Аренда_Объект_Владел

Пример 2 (продолжение) Проектирование на основе построение ER - модели Сам.раб + доска

Первая нормальная форма (1НФ) Определение: Первая нормальная форма (1НФ) – отношение, в котором на пересечении каждой строки и каждого столбца содержится только одно атомарное значение. Требование 1НФ: 1. Отсутствие повторяющихся групп. 2. Наличие первичного ключа. Действия для приведения к 1НФ: Выбор ключевого атрибута ненормализованной таблицы. Поиск повторяющихся групп. Устранение повторяющихся групп. Для устранения повторяющихся групп существует 2 подхода: а) повторяющиеся группы устраняются путем ввода в пустые строки повторяющихся данных; Анализ: вносится некоторая избыточность данных, которая в результате дальнейшей нормализации будет устранена. б) повторяющиеся группы изымаются и помещаются в отдельное отношение вместе с копиями ключа исходной таблицы. Далее в новых отношениях устанавливаются первичные ключи. При наличии повторяющихся групп нескольких уровней данный прием применяется несколько до тех пор, пока повторяющихся групп не остается. Анализ: - данные обладают меньшей избыточностью - сложности связанные с выделением повторяющихся групп Замечание! При выборе подхода а полученное 1НФ - отношение декомпозируется в ходе дальнейшей нормализации на те же отношения, которые получаются при применения подхода б.

Первая нормальная форма (1НФ) Пример 2 (продолжение) Таблица 0 - Ненормализованная таблица Клиент_Аренда_Объект_Владел Действия для приведения к 1НФ (пример): 1.Выбор ключевого атрибута ненормализованной таблицы. ПК: НомерК 2. Поиск повторяющихся групп. Структура повторяющейся группы: (НомерО, АдресО, ДатаН, ДатаО, Плата, НомерВ, НазвВ)

Первая нормальная форма (1НФ) Пример 2 (продолжение) 3. Устранение повторяющихся групп. Подход а: - повторяющиеся группа устраняется с помощью ввода в каждую строку с описанием Объекта недвижимости соответствующих сведений о Клиенте Получаем отношение: Клиент_Аренда_Объект_Владелец(НомерК, НомерО, ФИОК, АдресО, ДатаН, ДатаО, Плата, НомерВ, НазвВ) Таблица 1 - Отношение Клиент_Аренда_Объект_Владелец (1НФ) Потенциальные ключи: Первичный ключ: Вывод: Отношение Клиент_Аренда_Объект_Владелец находится в 1НФ. Анализ: Отношение подвержено аномалиям обновления

Первая нормальная форма (1НФ) Пример 2 (продолжение) 3. Устранение повторяющихся групп. Подход б: - повторяющиеся группа устраняется из ненормализованного отношения и помещается в другое отношение вместе с копией исходного ключевого атрибута (НомерК), затем для нового отношения выбирается собственный первичный ключ Получаем отношения: Клиент (НомерК, ФИОК) Аренда_Объект_Владелец (НомерК, НомерО, АдресО, ДатаН, ДатаО, Плата, НомерВ, НазвВ ) Вывод: Отношения Клиент, Аренда_Объект_Владелец находятся в 1НФ. Анализ: Отношение Объект_Аренда_Владелец также обладает избыточностью, следовательно, подвержено аномалиям обновлениям Внимание!!! Далее используем результат подхода а

Вторая нормальная форма (2НФ) 2НФ – применяется к отношениям с составным ПК Основана на требовании полной функциональной зависимости от ПК Полная функциональная зависимость Полная ФЗ а b – ФЗ, при которой b функционально зависит от полного значения атрибута а и не зависит ни от какого подмножества полного значения атрибута а. Пример полной зависимости ФЗ: НомерК, НомерО ДатаН Пример частичной зависимости ФЗ: НомерК, НомерО Плата, т.к. по БП 4:Оплата зависит от объекта недвижимости При частичной зависимости возникают аномалии обновления. Например, при изменении значения Плата для объекта Кв4 необходимо обновить 2 строки.

Вторая нормальная форма (2НФ) Определение: 2НФ – отношение находится в 1НФ и каждый атрибут, не входящий в состав первичного ключа характеризуется полной функциональной зависимостью от этого первичного ключа Требование 2НФ: 1. Отношение находится в 1НФ. 2. Отсутствие ЧФЗ от составного ключа. Действия для приведения к 2НФ: Выявление ФЗ (полной, частичной). Устранение ЧФЗ. Для устранения ЧФЗ: Частично зависимые атрибуты удаляются из исходного отношения и помещаются в новое отношение вместе с копией их детерминанта, оставшийся детерминант в исходном отношении будет выступать ВК

Вторая нормальная форма (2НФ) Пример 2 (продолжение) Действия для приведения к 2НФ (пример): 1. Выписываем ФЗ отношения Клиент_Аренда_Объект_Владелец (полную ФЗ от ПК, частичные, транзитивные?). ФЗ0: НомерК, НомерО ФИОК, АдресО, ДатаН, ДатаО, Плата, НомерВ, НазвВ - ФЗ от ПК - Полная ФЗ от ПК - Частичная ФЗ от ПК - Частичная ФЗ от ПК - Транзитивная зависимость 2. Устранение ЧФЗ.

Вторая нормальная форма (2НФ) Пример 2 (продолжение) Таблица 2.1 – Отношение Клиент (2НФ) Таблица 2.2 - Отношение Объект_Владелец (2НФ) Таблица 2.3 - Отношение Аренда (2НФ)

Третья нормальная форма (3НФ) Транзитивная зависимость: Если для атрибутов а, b, c некоторого отношения существуют зависимости а b, b c, то говорят, что атрибут c транзитивно зависит от атрибута а через атрибут b (при условии, что атрибут а функционально не зависит ни от атрибута b, ни от атрибута с) Наличие транзитивных ФЗ приводит к аномалиям обновления Определение 3НФ: 3НФ – отношение находится в 2НФ и не содержит неключевых атрибутов, которые находились бы в транзитивной ФЗ от этого первичного ключа. Требование 3НФ: 1. Отношение находится в 2НФ. 2. Отсутствие ТФЗ от ПК. Действия для приведения к 3НФ: Выявление ТФЗ. Устранение ТФЗ. Для устранения ЧФЗ: Транзитивно зависимые атрибуты удаляются из исходного отношения и помещаются в новое отношение вместе с копией их детерминанта, оставшийся детерминант в исходном отношении будет выступать ВК

Третья нормальная форма (3НФ) Пример 2 (продолжение) Действия для приведения к 3НФ (пример): Выписываем ФЗ отношений Аренда (НомерК(ВК), НомерО(ВК),ДатаН, ДатаО) ФЗ1: НомерК, НомерО ДатаН, ДатаО; - Полная ФЗ от ПК Клиент (НомерК, ФИОК) ФЗ2: НомерК ФИОК; - Полная ФЗ от ПК Объект_Владелец (НомерО, АдресО, Плата, НомерВ, НазвВ) ФЗ3: НомерО АдресО, Плата, НомерВ, НазвВ; - Полная ФЗ от ПК ФЗ4: НомерВ НазвВ; - Транзитивная зависимость 2. Устранение ТФЗ.

Третья нормальная форма (3НФ) Пример 2 (продолжение) Таблица 2.1 – Отношение Клиент (3НФ) Таблица 2.2.1 - Отношение Объект (3НФ) Таблица 2.2.2 - Отношение Владелец (3НФ) Таблица 2.3 - Отношение Аренда (3НФ)

Третья нормальная форма (3НФ) Пример 2 (продолжение) Схема декомпозиции отношения Клиент_Аренда_Объект_Владелец (в 1НФ) на 4 отношения Клиент, Аренда, Объект, Владелец в 3НФ Клиент_Аренда_Объект_Владелец (1НФ) Клиент Аренда Объект_Владелец (2НФ) Клиент Аренда Объект Владелец (3НФ) Замечания!!! 1) процесс нормализации заключается в декомпозиции исходного отношения посредством последовательного выполнения нескольких операций ПРОЕКЦИИ реляционной алгебры; 2) полученные в результате декомпозиции отношения обеспечивают формирование исходного отношения без потерь, путем использования операции ЕСТЕСТВЕННОГО СОЕДИНЕНИЯ

Пример1.Закрепление материала. «Чемпионат по автогонкам Формула-1» Рисунок - Дан фрагмент документа «Чемпионат по автогонкам Формула-1» БП: 1. В команде м.б. только несколько Гонщиков, Гонщик может быть только в одной команде 2. Кол_во кругов зависит от гонки 3. В одну дату на трассе проходит только 1 соревнование Шаг1. Преобразование к 1НФ: Чемпионат (ИН_Ком, НазвКом, ИН_Трасса, Назв_Трасса, Протяж, Кол_во_кругов, Место_Гонщ, Дата_гонки, ИН_Гонщ, ФИО_Гонщ, Страна_Гонщ) Шаг 2!!!. Первичный ключ 1НФ:

Пример2. В-02 ПО «Учебный план на 2011/2012» БП: Учебный план охватывает все группы Преподаватель может ничего не преподавать в 2011/2012 году, каждый преподаватель закреплен за кафедрой Дисциплина может не читаться в 2011/2012 году Каждая группа закрепляется за факультетом, на факультете много групп. На изучение одной и тот же дисциплины разным группам может выделяться разное количество часов. Каждый преподаватель закреплен за кафедрой Нагрузка преподавателя зависит от группы и дисциплины Доп. ПО «План дипломного проектирования на 2011/2012» БП: 8. Студенты 4,5 курсов кроме изучения плановых дисциплин занимаются написанием дипломной работы одного из типов: бакалаврская работа, дипломная работа специалиста, работа магистра 9. За каждым студентом 4,5 курса (бакалавром, специалистом, магистром) закрепляется руководитель (преподаватель), а также тема дипломной работы и назначается предприятие для прохождения преддипломной практики

Пример3.Закрепление материала «Сведения о проектах 1» Рисунок –Дан фрагмент документа «Сведения о проектах» БП: 1. На проекте д. работать хотя бы 1 исполнитель 2. Исполнитель участвует в нескольких проектах, но временно может не участвовать в проектах 3. Заказчик может заказывать более 1 проекта, у проекта только 1 заказчик 4. У исполнителя только 1 должность, которая не зависит от проекта 5.Оклад зависит от должности 6. Бюджет проекта назначается заказчиком и не зависит от количества и квалификации исполнителей

Пример4.Закрепление материала «Лечение в санаториях Украины» Рисунок –Дан фрагмент документа «Лечение в санаториях Украины» БП:1. Продолжительность процедуры зависит от только самой процедуры 2. Количество сеансов процедуры зависит от процедуры и клиента, в его конкретный заезд 3. Клиент в одну дату может заезжать только в один санаторий Шаг1. Преобразование к 1НФ: Чемпионат (ИН_Ком, НазвКом, ИН_Трасса, Назв_Трасса, Протяж, Кол_во_кругов, Место_Гонщ, Дата_гонки, ИН_Гонщ, ФИО_Гонщ, Страна_Гонщ) Шаг 2!!!. Первичный ключ 1НФ: ИН_Трасса, Дата_гонки, ИН_Гонщ