Проектирование баз данных с использованием теории нормализации: возникающие проблемы, понятие нормальной формы, 1нф, 2нф, 3нф, нфбк, 4нф.

Процесс проектирования реляционной БД предполагает выполнение операций нормализации отношений, использующих концепцию нормальных форм.

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

 

Нормализация – метод создания набора отношений с заданными свойствами на основе требований к данным, установленным в некоторой организации.

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

Вслед за НФБК Fagin привел определения четвертой (4НФ) и пятой (5НФ) нормальных форм. Однако на практике используются крайне редко.

Все эти нормальные формы можно представить следующим образом:

1НФ

Отношение находится в 1НФ тогда и только тогда, когда все используемые домены содержат только скалярные (атомарные, простые) значения.

Ненормализованное отношение легко преобразовать в 1НФ. Для этого достаточно вместо составного домена ввести составляющие его простые домены.

 

Даны отношения со схемами S(S#, SNAME, CITY) – поставщик, P(P#, PNAME, PRICE) – деталь, поставка характеризуется количеством товаров поставщика.

ПОСТАВКА ТОВАРОВ: S(S#, SName, City, Status, PS (P#, PName, Price, Qty)) – отношение ненормализовано (PS – составной домен)

S(S#, SName, City, Status, P#, PName, Price, Qty) – нормализованное отношение (S#, P#) – первичный ключ

 

Неудобства 1НФ: для нее существуют определенные аномалии. Главное, что свойственно 1НФ, – это избыточность информации, которая может привести к нарушению согласованности данных и даже к невозможности хранения некоторой информации. Проблемы, которые могут возникнуть при манипулировании данными в таком нормализованном отношении.

Проблема обновления. Необходимо изменять информацию во всех кортежах, связанных с измененным отношением. Если хотя бы в одном кортеже информация не будет обновлена, это приведет к нарушению достоверности данных.

Проблема вставки. При составном PK, и когда атрибуты первичного ключа не могут иметь пустые значения, нельзя создать новый экземпляр сущности с пустыми атрибутами первичного ключа. В ненормализованном отношении может появиться запись – поставщик, не поставляющий товар, а в нормализованном не может, так как ключ P# будет пустым

Проблема удаления. Удаляя весь кортеж, мы теряем информацию о поставщике. Если поставщик поставляет только одну деталь, то при удалении этой детали удалятся и данные о поставщике.

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

 

2НФ

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

 

(S#, P#)→Qty (функционально полно), но (S#, P#)→SName не является функционально полной, так как есть зависимость S#→SName, делаем декомпозицию следующим образом:

S (S#, SName, Status), P (P#, PName, Price), SP (S#, P#, Qty)

(разрешая связи n к n мы гарантированно получаем отношение во 2НФ)

 

Для достижения 2НФ Мб необходимо разбиение исходного отношения на несколько др.

Проблема обновления – меняется статус города ® надо изменить данные во всех кортежах

Проблема вставки – можно вставить данные о городе, только если в нем есть поставщик

Проблема удаления – удаляем поставщика, единственного в городе – удаляем город

Причиной этих аномалий является то, что в отношении, находящемся в 2НФ, есть транзитивная зависимость City→Status (из S#→City и City→Status S#→Status)

 

3НФ

Отношение находится в 3НФ тогда и только тогда, когда оно находится в 2НФ, и каждый его неключевой атрибут нетранзитивно зависит от ключа.

 

Проведем декомпозицию отношения S таким образом: S(S#, SName, City), C(City, Status)

Примечание: если при проектировании базы данных используется IDEF1x, тогда на этапе построения полноатрибутной концептуальной схемы для каждой сущности определяются не ключевые атрибуты. При этом:

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

- применяется правило отсутствия повторений.

Если значение некоторого «неудобного» атрибута встречается часто, вместо этого атрибута лучше создать отдельную сущность, экземпляры которой представляют собой некоторые справочные данные. Для решения большинства задач достаточно 3НФ.

 

Нормальная форма Бойса-Кодда (НФБК)

В Конкретной предметной области может иметь место следующая ситуация:

- отношение имеет несколько потенциальных ключей,

- потенциальные ключи являются составными,

- потенциальные ключи имеют общие атрибуты.

Такие ситуации на практике встречаются не очень часто, поэтому НФБК была определена позднее первых трех.

Нормализованное отношение находится в НФБК тогда и только тогда, когда каждый детерминант является потенциальным ключом.

(в определении функциональной зависимости: f: X à Y левая часть (X) называется детерминантом. Кроме того, рассматриваются только нетривиальные функциональные зависимости.)

 

S – студент, P – предмет, T – преподаватель. Функциональные зависимости: (S,P)→T, T→P

(S, P)→T, (S,P)→(S, P) (S, P)→(S, P, T)

T→P, (S, P)→(S, P, T) (S, T)→(S, P, T)

Вследствие того, что есть два составных ключа с одинаковым подмножеством атрибутов, могут возникнуть аномалии при удалении. Отношение (S, P, T) не находится в НФБК, так как детерминант T не является потенциальным ключом. Решение – декомпозиция на E1(T, S) и E2(T, P), не сохраняющая функциональные зависимости. Из концептуальной схемы видно, что связь между сущностями ПРЕДМЕТ и ПРЕДМЕТ СТУДЕНТА лишняя: так как преподаватель ведет занятия только по 1 предмету, можно выяснить, какой предмет изучает студент, зная, какой преподаватель проводит занятия ним. Эту связь удаляем => ПРЕДМЕТ СТУДЕНТА будет удовлетворять НФБК.

 

Многозначные зависимости идеи были сформулированы Фейгином (Fagin) в 1971 г.

Пусть A, B, C – некоторое произвольное подмножество атрибутов схемы отношения R(A, B, C). Тогда B многозначно зависит от A (A ®® B) тогда и только тогда, когда множество значений B, соответствующее заданной паре <A:a, C:c> отношения R зависит только от A, но не зависит от C.

Фейгин показал, что для данного отношения R(A, B, C) многозначная зависимость A ®® B выполняется тогда и только тогда, когда также выполняется многозначная зависимость A ®® C. Таким образом, многозначные зависимости всегда образуют пары: A ®® B | C.

В нашем примере имеет место многозначная зависимость:

Учебный курс ®® День занятий | Студент

Всякая функциональная зависимость является многозначной, но не всякая многозначная зависимость является функциональной. Фейгин сформулировал следующую теорему.

Теорема

Пусть A, B, C – некоторые множества атрибутов схемы отношения R(A, B, C). Отношение R будет равно соединению его проекций R₁(A, B) и R₂(A, C) тогда и только тогда, когда для отношения R выполняется многозначная зависимость A ®® C.

 

4НФ

Отношение R находится в 4НФ тогда и только тогда, когда в случае существования многозначной зависимости A ®® B все атрибуты отношения R функционально зависят от A.