Функциональные зависимости и нормализация При проектировании БД возникает две проблемы: Проблема логического проектирования БД: Как отразить объекты предметной области в абстрактные объекты модели данных так, чтобы это отражение не противоречило семантике предметной области и было «лучшим» ? Проблема физического проектирования БД: Как обеспечить эффективность манипулирования данными в рамках конкретной СУБД? Если при логическом проектировании РБД не касаться ограничений целостности, то необходимо решить: • из каких отношений должна состоять БД; • какие атрибуты должны быть у этих отношений. Один из вариантов проектирования баз данных – использование моделей сущность – связь (ER-моделей). Данный подход основан на формализации концептуальных представлений разработчика о структуре данных предметной области. Затем используется «отображение» ER-модели на реляционные структуры. Нормализация - вариант восходящего подхода при проектировании баз данных. Нормализация предусматривает идентификацию требуемых атрибутов с последующим созданием из них нормализованных таблиц, основанных на функциональных зависимостях между этими атрибутами.
Функциональная зависимость (ФЗ) является связью типа многие-к-одному между множествами атрибутов внутри одного отношения. Различают ФЗ которые выполняются для конкретного значения отношения и ФЗ которые выполняются для набора всех возможных значений отношения. Определение Пусть R – отношение (переменная отношения), а X и Y произвольные подмножества атрибутов R. Тогда Y функционально зависит от X (X Y) тогда и только тогда, когда для любого допустимого значения отношения R каждое значение X связано в точности с одним значением Y. Иначе говоря, для любого допустимого значения отношения R, обладающего ФЗ (X Y), истинно утверждение: если кортежи совпадают по значению X, то они совпадают и по значению Y. Определение X Y xi Yi xk yk (X Y) xi=xk yi=yk Тривиальные зависимости - зависимости, которые не могут не выполняться. Например {X, Y} Y. Зависимость тривиальна тогда и только тогда, когда правая (зависимая) часть является подмножеством левой (детерминанта).
Из определения потенциального ключа следует, что если X – потенциальный ключ отношения R, то все неключевые атрибуты Y отношения R должны функционально зависеть от X. Если отношение R удовлетворяет функциональной зависимости X Y и X не является потенциальным ключем, то R будет обладать некоторой избыточностью. X Z Y Xi … Yi … … Xk … Yk Если X – потенциальный ключ, то Xi Xk и существует X Y и X Z. При этом не обязательно Yi Yk и Zi Zk ФЗ обусловлены свойствами предметной области и являются ограничениями целостности, поэтому при каждом обновлении данных в БД все они должны быть проверены. Для обеспечения быстродействия работы БД необходимо минимизировать множество функциональных зависимостей в отношениях. Поэтому для множества функциональных зависимостей S отношения R желательно определить множество меньшего размера Sm такое, что каждая ФЗ из S могла быть получена на основе ФЗ из Sm. Определение Замыкание множества функциональных зависимостей S+ - это множество всех ФЗ, которые могут быть получены на основе заданного множества ФЗ путем применения правил вывода. S.
Правила вывода функциональных зависимостей Используемые обозначения: А, В, С и D – произвольные подмножества атрибутов заданного отношения R, АВ –объединение множеств А и В. (АВ : : =А В) 1. Рефлексивность: если В является подмножеством А, то А В. (тривиальная зависимость) 2. Дополнение: если А В, то АС ВС. 3. Транзитивность: если А В и В С, то А С. Этот набор правил является полным (на их основе могут быть получены все ФЗ, которые подразумевает заданный набор ФЗ S) и исчерпывающим (никакие дополнительные (неподразумеваемые S) ФЗ не могут быть выведены). Т. е. этих правил достаточно для получения замыкания S+. Дополнительные правила (удобны, хотя и выводятся из 1 -3): 4. Самоопределение: A A 5. Декомпозиция: если A BC, то A B и A C 6. Объединение: если A B и A C, то A BC 7. Композиция: если A B и C D, то AC BD 8. Теорема всеобщего объединения: Если A B и C D, то A (C-D) BD
Пример анализа функциональных зависимостей Рассмотрим отношение R , содержащее атрибуты A, B, C, D, E, F A B C D Структура отношения R Номер сотрудника Номер отдела Номер руководителя (начальника) Номер проекта, возглавляемого данным руководителем (уникальный для отдельного руководителя) E Название отдела F Доля времени, уделяемого данным руководителем заданному проекту В отношении R заданы ФЗ: A BC; B E; CD EF. Необходимо доказать наличие ФЗ: AD F Доказательство: Шаг 1: A BС (дано) Шаг 2: A С (из 1 согласно декомпозиции) Шаг 3: AD CD (из 2 согласно дополнению) Шаг 4: CD EF (дано) Шаг 5: AD EF (из 3 и 4 согласно транзитивности) Шаг 6: AD F (из 5 согласно декомпозиции) Наличие зависимости доказано!
Особенности анализа функциональных зависимостей На практике обычно не строится замыкание S+ заданного множества ФЗ S , а анализируется принадлежность конкретной ФЗ данному замыканию S+. Суперключ отношения R – это множество атрибутов К отношения R, которое содержит в виде подмножества (необязательно собственного) по крайней мере один потенциальный ключ. Следовательно, для каждого атрибута А отношения R истина ФЗ К А Пусть дано отношение R, множество ФЗ S и предполагаемый суперключ К. Необходимо определить, будут ли все атрибуты отношения R функционально зависеть от К 1. 2. 3. 4. 5. 6. Алгоритм построения замыкания К+ множества К для заданного S: К+=К, K 0=K. Выбрать первую ФЗ X Y из S. Если X K+, то К+= К+ Y. Если рассмотрены все ФЗ из S, то перейти на 5, иначе выбрать нерассмотренную ФЗ X Y из S и перейти на 3. Если К+=К 0, то 6, иначе К 0= К+ и перейти на 2. Конец (получено замыкание К+ множества К для заданного S)
Определения Если имеются два множества ФЗ S 1 и S 2 и любая ФЗ Si S 1 так же принадлежит множеству S 2 (Si S 2), то есть S 1+ S 2+, то S 2 называется покрытием S 1. Множества ФЗ S 1 и S 2 некоторого отношения R эквивалентны, если S+=S 1+ Множества зависимостей называются неприводимыми, если они удовлетворяют следующим условиям: 1. Правая (зависимая) часть каждой ФЗ множества S содержит только один атрибут 2. Левая часть (детерминант) каждой ФЗ множества S является неприводимой, т. е. ни один атрибут не может быть опущен из детерминанта без изменения замыкания S+. Такая ФЗ называется неприводимой слева или полной ФЗ. 3. Ни одна ФЗ множества S не может быть опущена из S без изменения замыкания S+.
Анализ функциональных зависимостей Получение неприводимого эквивалентного множества для заданного произвольного множества ФЗ S. 1. Используя декомпозицию получить из S множество S 1 из ФЗ с одноэлементной правой частью. 2. Для каждой ФЗ множества S 1 проверить каждый атрибут детерминанта: если S 1 и множество, полученное в результате устранения некоторого атрибута А в левой части, эквивалентны, то этот атрибут следует удалить. 3. Для каждой ФЗ f множества S 1 проверить эквивалентность S 1 и S 1 -f, если они эквивалентны, то f следует удалить из S 1. Для наглядного представления ФЗ используют диаграммы Пример: Пусть дано отношение R , содержащее атрибуты A, B, C, D, E, F И заданы ФЗ: A BC, B E, CD EF Диаграммы ФЗ для отношения R : B E C E D F A C
Нормализация Одна из основных целей проектирования реляционной базы данных заключается в группировании атрибутов в отношения таким образом, чтобы была достигнута минимальная избыточность данных и, следовательно, минимизирован объем памяти, необходимый для физического хранения отношений. «Неправильная» структура отношений не только приводит к нерациональному использованию памяти, но и может стать причиной ряда аномалий, приводящих к нарушению целостности данных: Ø аномалии вставки; Ø аномалии удаления; Ø аномалии обновления. Нормализация – это формальный метод анализа отношений на основе их ключей и существующих функциональных зависимостей В результате нормализации должен быть создан набор отношений с заданными свойствами на основе требований к данным, определяемых ограничениями предметной области.
Нормализация. Базовые понятия и определения: Основным методом, применяемым при нормализации отношений является декомпозиция Декомпозиция отношения – разбиение отношения на два или более отношений без потери информации. Проведение декомпозиции может осуществляться различными способами. Но процесс декомпозиции имеет два важных свойства, которые необходимо учитывать 1. Декомпозиция должна быть обратимой. Декомпозиция осуществляется на основе проецирования. При соединении отношений, полученных в результате декомпозиции, должно быть получено исходное отношение. R Проецирование R 1 R 2 Соединение
Нормализация. Базовые понятия и определения: 2. Декомпозиция должна выполняться с сохранением зависимостей Пусть в результате декомпозиции отношения R, имеющего множество ФЗ S, получено: множество проекций R 1, R 2… Rn и множества соответствующих ФЗ S 1, S 2, …, Sn; каждая ФЗ множества Si будет относиться только к атрибутам Ri. Если замыкание множества S’, являющегося объединением множеств Si (S’=S 1 S 2 … Sn), равно замыканию множества S (S’+=S+), то декомпозицию можно считать проведенной с сохранением зависимостей. При выборе варианта декомпозиции целесообразно руководствоваться концепцией независимых проекций. Пусть R 1 и R 2 – проекции отношения R, полученные в результате декомпозиции. R 1 и R 2 независимы тогда и только тогда, когда каждая ФЗ в отношении R является логическим следствием ФЗ в R 1 и R 2; общие атрибуты проекций R 1 и R 2 образуют потенциальный ключ, по крайней мере, для одной из них.
Нормальные формы Определение: Ненормализованная форма (ННФ) отношения предполагает наличие в отношении одной или нескольких повторяющихся групп данных. Пример: Фирма Товар Цена ТД «Тимур» ARDO BOSH SAMSUNG 12350 15500 12750 ТД «Пингвин» ARDO BOSH LG 12600 16300 14000 Вятка 9800 ARDO SAMSUNG LG 12450 12500 13750 «Быт» ТД «Эксперт» Ключом в представленной таблице является поле «Фирма» При работе с таблицей возможны все указанные ранее аномалии (обновления, удаления, вставки)
Нормальные формы Отношение находится в определенной нормальной форме (НФ), если оно удовлетворяет заданным условиям этой формы. Определение: Отношение находится в первой нормальной форме (1 НФ), если отношение содержит уникальные кортежи со скалярными данными. (Т. е. нет одинаковых строк в таблице и находится только одно значение) Пример: Приведение ненормализованной таблицы к отношению в 1 НФ путем дублирования данных (возникающая избыточность будет «убрана» в ходе дальнейшей нормализации) в каждой ячейке таблицы Фирма Товар Цена ТД «Тимур» ARDO BOSH SAMSUNG 12350 15500 12750 ТД «Пингвин» ARDO BOSH LG 12600 16300 1400 Отношение в 1 НФ иногда называют просто нормализованным отношением.
Нормальные формы Каждая последующая НФ удовлетворяет всем условиям предыдущей НФ. Определение: Отношение находится во второй нормальной форме (2 НФ), если оно находится в 1 НФ и, кроме этого, каждый неключевой атрибут (атрибут не входящий ни в один потенциальный ключ) функционально полно (неприводимо) зависит от любого возможного ключа в этом отношении. Пример : Дано отношение R , содержащее атрибуты A, B, C, D с первичным ключом (А, В) и ФЗ А В. Т. к. АВ – ключ отношения, то истины ФЗ АВ С и АВ D. Следовательно, это отношение может быть заменено двумя отношениями R 1 и R 2: R 1 (A, D) с первичным ключом (A), R 2 (A, B, C) с первичным ключом (A, В) и внешним ключом (A), связанным с R 1. При получения отношений во 2 НФ необходимо в отношении 1 НФ устранить частичные зависимости. (Имеет смысл, если R 1 детерминанты составные) R A A D B C R 2 Т. к. А В и АВ С, то получаем В С и А С, т. е. С транзитивно (через В) зависит от А, т. е имеем избыточное ограничение целостности.
Нормальные формы Определение: Отношение находится в третьей нормальной форме (3 НФ), если оно находится во 2 НФ и, кроме этого, каждый неключевой атрибут отношения нетранзитивно зависит от каждого потенциального ключа этого отношения. . R R 1 A A D B C D A R 3 A B B C B R 2 с ФЗ A B C C R 4 Если в отношении имеет место транзитивная зависимость между атрибутами, то для перехода к 3 НФ транзитивно-зависимые атрибуты «выносятся» в новое отношение вместе с копией детерминанта.
Нормальные формы Определение: Нормальная форма Бойса-Кодда (НФБК) – каждая нетривиальна и неприводимая слева ФЗ обладает потенциальным ключом к качестве детерминанта. Другая формулировка – Отношение находится в НФБК тогда и только тогда, когда детерминанты являются потенциальными ключами. Нормальная форма Бойса-Кодда учитывает ФЗ, в которых участвуют все потенциальные ключи отношения, а не только его первичный ключ. Для отношения с единственным потенциальным ключом 3 НФ и НФБК эквивалентны. Пример: Дано отношение R{S, D, T}, где: S-студент, D-дисциплина, T-преподаватель. На R заданы следующие ограничения: 1. Каждый студент, изучающий данную дисциплину, обучается только одним преподавателем. 2. Каждый преподаватель ведет только одну дисциплину (но дисциплина может преподаваться несколькими преподавателями). Из 1 следует ФЗ {S, D} T , а из 2 следует ФЗ T D S D T
Нормальные формы В отношении R есть два перекрывающихся потенциальных ключа {S, D} и {S, T}. Отношение находится в 3 НФ (но не в НФБК) и характеризуется определенными аномалиями обновления (Например, при удалении данных о том что некоторый студент изучает определенную дисциплину, может быть утрачены данные о том, что соответствующий преподаватель преподает эту дисциплину). Это связано с тем, что Т является детерминантом ФЗ, но не является потенциальным ключом. Для решения проблемы необходимо провести декомпозицию данного отношения на два отношения в НФБК: R{S, D, T} > R 1{S, T} и R 2{T, D} с единственной ФЗ T D R 1{S, T} – ключ {S, T}, без ФЗ S (Студент) Иванов Сидоров T (Преподаватель) Петров Васильев Петров Николаев S (Студент) Иванов Сидоров D (Дисциплина) Математика Физика T (Препод-ль) Петров Васильев Петров Николаев R 2{T, D} – ключ Т, ФЗ T D T (Преподаватель) Петров Васильев Николаев D (дисциплина) Математика Физика Полученные проекции не являются независимыми, так как ФЗ {S, D} T для R не может быть выведена из ФЗ T D, имеющейся в R 1 и R 2.
Нормальные формы Замечания! Ø При декомпозиции не всегда можно получить независимые проекции в НФБК, поэтому данное условие является желательным, но не обязательным. Ø Декомпозиция с образованием НФБК всегда возможноа но не всегда целесообразна (т. к. при такой декомпозиции могут быть «потеряны» ФЗ). Нормализация от 1 НФ до НФБК позволяет устранить любые аномалии, вызванные функциональными зависимостями. Но! Кроме функциональных зависимостей, существуют и другие виды зависимостей, которые могут вызвать проблемы, связанные с нарушением целостности данных.
Нормальные формы Определение: Многозначная зависимость имеет место в отношении R, если для каждого значения A имеется набор значений атрибута B и набор значений атрибута C, но входящие в эти наборы значения атрибутов B и C не зависят друг от друга, а определяются только значением атрибута A. Обозначение: A B и A C Пример: Таблица с многозначными зависимостями Фирма Цена ТД «Тимур» Пример не удачный! Почему? Товар ARDO BOSH SAMSUNG 12350 15500 12750 ТД «Пингвин» ARDO 12600 BOSH 16300 LG 14000 В данной таблице имеют место две многозначные зависимости: «Быт» Вятка 9800 Торговая фирм Магазины и Торговая фирм Товары. ТД «Эксперт» 12450 При этом перечень товаров, в общем случае, может не зависеть от адресов магазинов
Нормальные формы Определение: Многозначная зависимость A B отношения R определяется как тривиальная , если атрибут B является подмножеством атрибута A, или A B =R. Если условия B A или A B =R не выполняются, то A B определяется как нетривиальная. Определение: Отношение находится в четвертой нормальной форме (4 НФ), если оно находится в НФБК и не содержит нетривиальных многозначных зависимостей. Пример: Дано ненормализованное отношение R (атрибуты не являются атомарными) без функциональных зависимостей D (дисциплина) Математика Физика T (Преподаватели) Петров Васильев Николаев B (Учебник) Матанализ Аналит. Геометрия Механика Электричество Оптика
Нормальные формы Приведем отношение R к 1 НФ, получим отношение R’: В данном отношении кортеж {D: d, T: t; B: b} будет присутствовать, если дисциплину d ведет преподаватель t с использованием учебника b. D (дисциплина) Математика Физика … Физика T (Преподаватели) Петров Васильев Васильев … Николаев B (Учебник) Матанализ Аналит. Геометрия Механика Электричество Оптика … Оптика Отношение R’ находится в НФБК, т. к. является полностью ключевым. НО! Полученное отношение обладает избыточностью и характеризуется аномалиями обновления (например, на каждого нового преподавателя по предмету требуется ввести количество кортежей, равное числу учебников по данному предмету). В R’ нет ФЗ, но существуют две многозначные зависимости: D >T и D >B. Отношение R’ можно заменить двумя более удобными в использовании проекциями R 1 {D, T} и R 2 {D, B} каждая из которых находится в НФБК (является полностью ключевой).
Нормальные формы Замечание! Существуют отношения, для которых нельзя выполнить декомпозицию без потерь на две проекции, но можно провести такую декомпозицию на n (n>2) проекций. Такие отношения называют «n-декомпозируемыми» Пример: Имеем отношение R{S, P, J} в 4 НФ (полностью ключевое, не содержит нетривиальных ФЗ и МЗ) и три его бинарные проекции R 1{S, P}, R 2{P, J} и R 3{J, S} S s 1 s 2 s 1 P p 1 p 2 p 1 J j 2 j 1 j 1 S s 1 s 2 s 1 P P 1 P 2 P 1 P p 1 p 2 p 1 J j 2 j 1 j 1 j 1 S s 1 s 2 s 1 Соединение проекций R 1{S, P} и R 2{P, J} по P дает отношение R’{S, P, J}, в котором имеется лишний кортеж <s 2, p 1, j 2> (Т. е. R’ R). Для того, чтобы исключить «лишний» кортеж необходимо выполнить операцию соединения полученного отношения R’ с проекцией R 3{J, S} (по атрибутам J, S) Итоговый результат не зависит от порядка выполнения соединений!
Нормальные формы Отношение будет n-декомпозируемым если оно удовлетворяет некоторому циклическому ограничению. Ограничение, связанное с таким условием называется зависимостью соединения (ЗС). Определение: Зависимость соединения (ЗС) – свойство декомпозиции, которое вызывает генерацию ложных кортежей при обратном соединении декомпозированных отношений с помощью операции естественного соединения Определение: Отношение R находится в пятой нормальной форме (5 НФ) (проекционно-соединительной НФ, если оно не содержит зависимостей соединения. Отношение в 5 НФ не содержит аномалий, которые могут быть исключены разбиением на проекции.
Общая схема процесса нормализации 1. 1 НФ 2 НФ (декомпозиция на проекции, исключающие неприводимые ФЗ) 2. 2 НФ 3 НФ (декомпозиция на проекции исключающие транзитивные ФЗ) 3. 3 НФ НФБК (декомпозиция на проекции, исключающие ФЗ в которых детерминанты не являются потенциальными ключами) Обобщение п. п. 1 -3 : Исходное отношение следует разбить на проекции для исключения всех ФЗ, в которых детерминанты не являются потенциальными ключами. 4. НФБК 4 НФ (декомпозиция на проекции, исключающие многозначные зависимости, которые не являются ФЗ). (На практике обычно многозначные зависимости исключаются перед этапами 1 -3 путем отделения независимых повторяющихся групп) 5. 4 НФ 5 НФ (декомпозиция на проекции, исключающие любые зависимости соединения, которые не подразумеваются потенциальными ключами (если их можно выявить))
Скачать презентацию Функциональные зависимости и нормализация При проектировании БД возникает
Л9_ФЗ_Нормализ.ppt