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

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

Если рассмотреть  кортежи полученного для трансформаторов  отношения, то сразу виден ряд  аномалий.  

       Аномалии.

1) Избыточность  данных (повторение одной и той  же информации).

2) Обновления (например, изменение адреса завода  ведет к модификации множества  записей).

       3) Удаления (при удалении последнего  трансформатора, выпускаемого на  каком-то заводе, теряется информация  о самом заводе).

       4) Включения (например, при включении  нового типа нужно сразу описывать  все атрибуты).

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

       Состав  атрибутов должен обеспечивать:

       - отсутствие нежелательных функциональных  зависимостей.

       - минимальное дублирование данных.   

       Функциональная  зависимость атрибутов. 

       Если  в любой момент времени каждому  значению атрибута А соответствует не более, чем одно значение атрибута В, говорят, что В функционально зависит от А   

         А→В

       Понятно, что если отношение находится  в 1НФ, то все атрибуты зависят от ключа.

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

       Для рассматриваемого примера в качестве набора ключевых атрибутов выступают  «Тип, Uв, Uн», но остальные атрибуты зависят только от «Типа», то есть речь идет о частичной зависимости.

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

       Для перехода ко 2НФ нужно:

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

б) построить  проекцию на часть ключа и атрибута от нее зависящие.

       Таким образом исходное отношение «Трансформатор» может быть разложено на два отношения:

       ТР1( Тип, Uв, Uн)

       ТР2( Тип, Sном, Длина, Ширина, Высота, Завод, Адрес).  

       Соединение  этих отношений по типу даст исходное отношение.  

       Транзитивная  зависимость атрибутов.

       Если  атрибут В зависит от атрибута А, а атрибут С от атрибута В, то атрибут С транзитивно зависит от атрибута А.

       А→В,  В→С – транзитивная зависимость.

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

В рассматриваемом  отношении атрибут Адрес завода транзитивно зависит от атрибута Тип. Поэтому для перехода к 3НФ отношение

       ТР2( Тип, Sном, Длина, Ширина, Высота, Завод, Адрес)

       должно  быть разложено на два

       ТР2_1(Тип, Sном, Длина, Ширина, Высота, Завод)

       ТР2_2(Завод, Адрес).

Обратный переход  осуществляется их соединением по атрибуту «Завод».  

       Многозначная  зависимость.

       Если  каждому значению атрибута А соответствует множество значений атрибута В, никак не связанных с другими атрибутами.

       А→→В.

       4НФ  – отношения находятся в 3-ей  НФ но в нем отсутствуют многозначные зависимости.

       В рассматриваемом примере Uв и Uн многозначно зависят от типа.  

       Общее правило нормализации.

       Если  отношение в отношении R(x,y,z) имеют место зависимости x→y или x→→y, то R можно разложить в R1(x,y) и R2(x,z), а R естественное соединение R1 и R2.

       Например, отношение 

Обучение(Таб. №,   ФИО, Предмет, Часы, Группа, Семестр, Должность, Зарплата, Кафедра, Телефон_Кафедры),

       содержащее разные зависимости, может быть разложено на отношения:  

       Преподаватель (Таб. №,   ФИО, Должность, Кафедра)

Кафедра (Кафедра, Телефон_Кафедры)

Оклад (Должность, Зарплата)

       Расписание (Таб. №, Предмет, Группа, Семестр),

       Программа (Предмет, Группа, Семестр, Часы). 
 

4.2.2. 1NF - первая нормальная форма.

Для обсуждения первой нормальной формы необходимо дать два определения:

Простой атрибут - атрибут, значения которого атомарны (неделимы).

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

Теперь можно  дать

Определение первой нормальной формы:

отношение находится в 1NF если значения всех его атрибутов атомарны.

Рассмотрим пример, заимствованный из уже упоминавшейся статьи Е.Ф.Кодда:

В базе данных отдела кадров предприятия необходимо хранить  сведения о служащих, которые можно  попытаться представить в отношении

СЛУЖАЩИЙ(НОМЕР_СЛУЖАЩЕГО, ИМЯ, ДАТА_РОЖДЕНИЯ, ИСТОРИЯ_РАБОТЫ, ДЕТИ).

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

·  ИСТОРИЯ_РАБОТЫ (ДАТА_ПРИЕМА, НАЗВАНИЕ, ИСТОРИЯ_ЗАРПЛАТЫ),

·  ИСТОРИЯ_ЗАРПЛАТЫ (ДАТА_НАЗНАЧЕНИЯ, ЗАРПЛАТА),

·  ДЕТИ (ИМЯ_РЕБЕНКА, ГОД_РОЖДЕНИЯ).

Их связь представлена на рис. 4.3.

Рис.4.3. Исходное отношение. 

  
Для приведения исходного отношения СЛУЖАЩИЙ к первой нормальной форме необходимо декомпозировать его на четыре отношения, так как это показано на следующем рисунке:

Рис.4.4. Нормализованное  множество отношений. 

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

Алгоритм нормализации описан Е.Ф.Коддом следующим образом:

• Начиная с отношения, находящегося на верху дерева (рис. 4.3.), берется его первичный ключ, и каждое непосредственно подчиненное отношение расширяется путем вставки домена или комбинации доменов этого первичного ключа.
• Первичный Ключ каждого расширенного таким образом отношения состоит из Первичного Ключа, который был у этого отношения до расширения и добавленного Первичного Ключа родительского отношения.
• После этого из родительского отношения вычеркиваются все непростые домены, удаляется верхний узел дерева, и эта же процедура повторяется для каждого из оставшихся поддеревьев.

4.2.3. 2NF - вторая нормальная форма.

Очень часто  первичный ключ отношения включает несколько атрибутов (в таком  случае его называют составным) - см., например, отношение ДЕТИ, показанное на рис. 4.4. При этом вводится понятие полной функциональной зависимости.

Определение:

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

Пусть имеется  отношение ПОСТАВКИ (N_ПОСТАВЩИКА, ТОВАР,  ЦЕНА).  
Поставщик может поставлять различные товары, а один и тот же товар может поставляться разными поставщиками. Тогда ключ отношения - "N_поставщика + товар". Пусть все поставщики поставляют товар по одной и той же цене. Тогда имеем следующие функциональные зависимости:

• N_поставщика, товар -> цена
• товар -> цена

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

• ПОСТАВКИ (N_ПОСТАВЩИКА, ТОВАР)
• ЦЕНА_ТОВАРА (ТОВАР, ЦЕНА)

Таким образом, можно дать

Определение второй нормальной формы:

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

4.2.4. 3NF - третья нормальная форма.

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

Определение:

Пусть X, Y, Z - три атрибута некоторого отношения. При этом X --> Y и Y --> Z, но обратное  
соответствие отсутствует, т.е. Z -/-> Y и Y -/-> X. Тогда  Z транзитивно зависит от X.  
   
Пусть имеется отношение ХРАНЕНИЕ (ФИРМА, СКЛАД, ОБЪЕМ), которое содержит информацию о фирмах, получающих товары со складов, и объемах этих складов. Ключевой атрибут - "фирма". Если каждая фирма может получать товар только с одного склада, то в данном отношении имеются следующие функциональные зависимости:

• фирма -> склад
• склад -> объем

При этом возникают  аномалии:

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

Для устранения этих аномалий необходимо декомпозировать  исходное отношение на два:

• ХРАНЕНИЕ (ФИРМА, СКЛАД)
• ОБЪЕМ_СКЛАДА (СКЛАД, ОБЪЕМ)

Определение третьей нормальной формы:

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

4.2.5. BCNF - нормальная форма Бойса-Кодда.

Эта нормальная форма вводит дополнительное ограничение  по сравнению с 3НФ.  
Определение нормальной формы Бойса-Кодда:

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

Ситуация, когда  отношение будет находится в 3NF, но не в BCNF, возникает при условии, что отношение имеет два (или более) возможных ключа, которые являются составными и имеют общий атрибут. Заметим, что на практике такая ситуация встречается достаточно редко, для всех прочих отношений 3NF и BCNF эквивалентны.  

4.2.6. Многозначные зависимости и четвертая нормальная форма (4NF).

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

Многозначная  зависимость является обобщением функциональной зависимости и рассматривает  соответствия между множествами значений атрибутов.

В качестве примера  рассмотрим отношение ПРЕПОДАВАТЕЛЬ (ИМЯ, КУРС, УЧЕБНОЕ_ПОСОБИЕ), хранящее сведения о курсах, читаемых преодавателем, и написанных им учебниках. Пусть профессор N читает курсы "Теория упругости" и "Теория колебаний" и имеет соответствующие учебные пособия, а профессор K читает курс "Теория удара" и является автором учебников "Теория удара" и "Теоретическая механика". Тогда наше отношение будет иметь вид:

      ----------------------------------------------------

      | ИМЯ  |  КУРС            | УЧЕБНОЕ_ПОСОБИЕ        |

      ----------------------------------------------------

      |  N   | Теория упругости | Теория упругости       |

      |  N   | Теория колебаний | Теория упругости       |

      |  N   | Теория упругости | Теория колебаний       |

      |  N   | Теория колебаний | Теория колебаний       |

      |  K   | Теория удара     | Теория удара           |

      |  K   | Теория удара     | Теоретическая механика |

      ----------------------------------------------------

добавляем:

      ----------------------------------------------------

      |  K   | Теория упругости | Теория удара           |

      |  K   | Теория упругости | Теоретическая механика |

      ----------------------------------------------------

Это отношение  имеет значительную избыточность и  его использование приводит к  возникновению аномалии обновления. Например, добавление информации о том, что профессор K будет также читать лекции по курсу "Теория упругости" приводит к необходимости добавить два кортежа (по одному для каждого написанного им учебника) вместо одного. Тем не менее, отношение ПРЕПОДАВАТЕЛЬ находится в NFBC (ключевой атрибут - ИМЯ).

Заметим, что  указанные аномалии исчезают при  замене отношения ПРЕПОДАВАТЕЛЬ  его проекциями:

      ---------------------------      -------------------------------

      | ИМЯ  |  КУРС            |      | ИМЯ |   УЧЕБНОЕ_ПОСОБИЕ     |

      ---------------------------      -------------------------------

      |  N   | Теория упругости |      |  N  |Теория упругости       |

      |  N   | Теория колебаний |      |  N  |Теория колебаний       |

      |  K   | Теория удара     |      |  K  |Теоретическая  механика |

      |  K   | Теория упругости |      |  K  |Теория удара           |

      ---------------------------      -------------------------------

Аномалия обновления возникает в данном случае потому, что в отношении ПРЕПОДАВАТЕЛЬ  имеются:

1. зависимость множества значений атрибута КУРС от множества значений атрибута ИМЯ
2. зависимость множества значений атрибута УЧЕБНОЕ_ПОСОБИЕ от множества значений атрибута ИМЯ.

Такие зависимости и  называются многозначными  и обозначаются как 

           ИМЯ ->> КУРС           ИМЯ ->> УЧЕБНОЕ_ПОСОБИЕ

Нетрудно  показать, что многозначные зависимости всегда образуют связанные  пары, поэтому их часто обозначают

             ИМЯ ->> КУРС | УЧЕБНОЕ_ПОСОБИЕ 

Очевидно, что каждая функциональная зависимость является многозначной, но не каждая многозначная зависимость является функциональной.

Определение четвертой нормальной формы:

Отношение находится в 4NF если оно находится в BCNF и в нем отстутсвуют многозначные зависимости, не являющиеся функциональными зависимостями.  

4.2.7. Зависимости по соединению и пятая нормальная форма (5NF).

До  сих пор мы предполагали, что единственной операцией, необходимой  для устранения избыточности в отношении, была декомпозиция его  на две проекции. Однако, существуют отношения, для которых нельзя выполнить декомпозицию без потерь на две проекции, но которые можно подвергнуть декомпозиции без потерь на три (или более) проекций. Этот факт получил название зависимости по соединению, а такие отношения называют 3-декомпозируемые отношения (ясно, что любое отношение можно назвать "n-декомпозируемым", где n >= 2).

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

Определение пятой нормальной формы:

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

Другими словами, каждая проекция такого отношения  содержит не менее  одного возможного ключа  и не менее одного неключевого атрибута.  
 

К объектам СУБД относятся: таблицы, формы, запросы, отчёты, макросы и модули.

Таблицы √ служат источником данных для запросов фирм, объектов. В реляционных БД имеют двухмерную структуру. Состоят из полей и записей.

Форма √ это окно диалога или электронный документ, позволяющий осуществлять ввод данных и при задании определённых условий √ контролировать этот ввод.

Запросы √ это средства отбора данных из одной или нескольких таблиц при помощи определённых пользователем условий. Запросы позволяют отбирать, добавлять, удалять данные. Запросы √ это тоже таблица, созданная только в памяти компьютера. В запросах можно создавать вычисляемые поля, по которым можно сортировать и отбирать данные.

Отчёт √ это средство создания сложных многостраничных документов и вывода их на печать.

Макросы √ содержат описания действий, которые должны быть выполнены, при совершение некоторых событий. Макросы создаются пользователем, (это программирование) на языке программирования (Visual Bai-sic).

Модули √ это отдельные программы на языке Visual Bai-sic.