13.Нормализация отношений.

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

Теория нормализации основана на концепции нормальных форм. Говорят, что таблица находится в данной нормальной форме, если она удовлетворяет определенному набору требований. Выделяют следующую последовательность нормальных форм: 1-я, 2-я, 3-я нормальная форма, нормальная форма Бойса-Кодда, 4-я и 5-я нормальная форма. Теоретически существует пять нормальных форм, но на практике обычно используются только первые три. Более того, первые две нормальные формы являются по существу промежуточными шагами для приведения базы данных к третьей нормальной форме.

В качестве примера рассмотрим базу данных по проектам, которые выполняют сотрудники организации. Первичный ключ таблицы формируется из двух полей ProjectId и ManId.

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

Опр:Функциональная зависимость. Пусть X и Y - два атрибута некоторого отношения. Говорят, что Y функционально зависит от Х, если в любой момент времени каждому значению Х соответствует только одно значение атрибута Y Функциональная зависимость обозначается Х  Y. В нормализованном отношении все не ключевые атрибуты функционально зависят от первичного ключа отношения.

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

Для устранения проблем модификации приведем отношение Projects ко второй нормальной форме.

Опр: Реляционная таблица находится во второй нормальной форме, если она находится в первой нормальной форме и ее неключевые поля полностью зависят от всего первичного ключа.

Таблица Projects находится в первой, но не во второй нормальной форме, так как поля Job, Salary, Comm, Phone зависят только от поля ManId, являющегося частью составного первичного ключа (ProjectId, ManId).

Чтобы перейти от первой нормальной формы ко второй, нужно выполнить следующие шаги:

1. Создать таблицу, исключив атрибуты, которые не находятся в полной функциональной зависимости от составного ключа.

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

В результате преобразований получим две новые таблицы, которые находятся во второй нормальной форме.

Транзитивная зависимость. Пусть X,Y,Z - атрибуты некоторого отношения. При этом Y функционально зависит от X (ХУ), Z функционально зависит от Y (YZ), но обратная зависимость отсутствует. Тогда говорят, что Z транзитивно зависит от X.

Для отношения Employee пример транзитивной зависимости: Job  Salary  Comm. Хранение в отношении атрибутов, находящихся в транзитивной зависимости, порождает ряд неудобств: например, дублирование оклада; при изменении размера надбавки придется корректировать его у всех сотрудников, работающих в определенной должности.

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

Для преобразования отношения к 3НФ надо построить несколько проекций. Например, сформируем новое отношение Stuff (должности) с атрибутами Job, Salary, Comm, первичный ключ -атрибут Job. В отношении Employee удалим атрибуты, которые не зависят от первичного ключа ManId. После нормализации исходное отношение Projects «поделено» на три отношения:

Projects, Employee, Stuff, которые являются оптимальной схемой для данного примера.

Нормализация устраняет избыточность данных, что позволяет снизить объем хранимых данных и избавиться от описанных выше аномалий их изменения. В процессе нормализации отношения ко второй и третьей нормальной форме число отношений увеличивается, однако всегда есть возможность получить исходное отношение путем выполнения операции соединения.Алгоритм приведения в 3НФ:1) а) Разделить на схемы, которые содержат только простые атрибуты

б) Определить первичный ключ 2) а) Для схемы, в которой первичный ключ состоит из нескольких атрибутов, проверить, что каждый не ключевой атрибут функционально полно зависит от первичного ключа. б) Если это не так, разделить схему (выполнить проекцию)3)а) Проверить, что все не ключевые атрибуты являются взаимно независимыми.

б) Если это не так, выполнить проекцию, исключив избыточные атрибуты