Перехід до нормалізаційних форм

Міністерство освіти та науки,молоді та спорту України

Херсонський політехнічний  коледж

Одеського національного  політехнічного університету

 

 

 

 

 

 

 

Реферат

З дисципліни «Системи керування  базами даних»

на тему:

«Перехід до нормалізаційних  форм»

 

 

 

 

 

 

 

Розробив:

Студент 391 групи

Бурлаков Денис

 

 

 

 

 

 

Нормалізація  схем баз даних

Нормалізація - це розбивка таблиці на дві або більш, які характеризуються кращими властивостями при доповненні , зміні і вилученні даних. Кінцева мета нормалізації зводиться до отримання такого проекту бази даних, у котрому кожний факт з'являється лише в однім місці, тобто виключена надлишковість інформації. Це робиться не стільки з метою економії пам'яті, скільки для виключення можливої суперечливості збережених даних.

Кожна таблиця в реляційній БД задовольняє умові, у відповідності  з якою у позиції на перетині кожного  рядка і стовпця таблиці завжди знаходиться єдине атомарне значення і ніколи не може бути множини таких  значень. Будь-яка таблиця, що задовольняє  цій умові, називається нормалізованою.

Кожній нормальній формі  відповідає деякий визначений набір  обмежень. Відношення знаходиться в  деякій нормальній формі, якщо задовольняється  властивий їй набір обмежень.

Кожна нормальна форма  є більш обмеженою і більш  бажаною, ніж попередня. Це зв'язано  з тим, що в (N+1)-ій нормальній формі  вилучаються деякі небажані властивості, які характерні N-ій нормальній формі. Теорія нормалізації грунтується на наявності тієї або іншої залежності між полями таблиці.

Основні властивості нормальних форм:

· кожна наступна нормальна  форма в деякому змісті краще  попередньої;

· при переході до наступної  нормальної форми властивості попередніх нормальних властивостей зберігаються.

Найбільше важливі нормальні  форми відношень грунтуються  на фундаментальному в теорії реляційних баз даних понятті функціональної залежності.

Визначення 1. Функціональна  залежність .

У відношенні R атрибут Y функціонально  залежить від атрибута X (X і Y можуть бути складовими) у тому і тільки в тому випадку, якщо кожному значенню X відповідає в точності одне значення Y: X Y.

Визначення 2. Повна функціональна  залежність.

Функціональна залежність X Y називається повною, якщо атрибут Y не залежить функціонально від  будь-якої підмножини X.

Визначення 3. Транзитивна  функціональна залежність .

Функціональна залежність називається  транзитивною, якщо з функціональних залежностей X Y та Y Z випливає, що X Z.

Наприклад, Вінниця входить  до Поділля, а Поділля -до України . Для  даного прикладу має місце транзитивна  залежність ВІННИЦЯ УКРАЇНА.

Визначення 4. Неключовий атрибут .

Неключовим атрибутом  називається будь-який атрибут відношення, що не входить до складу первинного ключа .

Визначення 5. Взаємно незалежні  атрибути .

Два або більш атрибути взаємно незалежні, якщо жодний із цих  атрибутів не є функціонально  залежним від інших.

Відношення R задано в першій нормальній формі, якщо воно задано у  виді множини своїх кортежів , які  не повторюються.

Для того, щоб представити  відношення в першій нормальній формі  необхідно над його кортежами  виконати операцію проекції для видалення  рядків, які повторюються.

Відношення R задано вдругій  нормальній формі, якщо воно, по-перше, є відношенням у першій нормальній формі і, по-друге, кожний його атрибут, який не є основним атрибутом, функціонально  повно залежить від будь-якого  можливого ключа цього відношення.

Нехай задано відношення ФАКУЛЬТЕТ ( НАЙМЕНУВАННЯ, ПІБ ДЕКАНА, ТЕЛЕФОН).

Відношення ФАКУЛЬТЕТ  задано в другій нормальній формі, тому що у відношенні ФАКУЛЬТЕТ атрибут  ТЕЛЕФОН, який не є основним, повністю залежить від будь-якого можливого  ключа: НАЙМЕНУВАННЯ, ПІБ ДЕКАНА.

У загальному випадку, якщо всі можливі ключі відношення містять по одному атрибуту, то це відношення задане в другій нормальній формі, тому що в цьому випадку всі атрибути, які не є основними, функціонально  повно залежать від можливих ключів. Однак це твердження не завжди справедливе, якщо ключ відношення R є складовим.

Розглянемо наступне відношення:

СТУДЕНТ-КУРС ПРОЕКТ (НОМЕР  ЗАЛІКОВОЇ КНИЖКИ, КОД ПРЕДМЕТУ, ПРІЗВИЩЕ СТУДЕНТА, НОМЕР ГРУПИ, ВИКЛАДАЧ, ПРОЦЕНТ ВИКОНАННЯ).

Припустимо, що в одній  групі можуть навчаються однофамільці. Тоді для цього відношення можливий тільки один ключ: НОМЕР ЗАЛІКОВОЇ  КНИЖКИ, КОД ПРЕДМЕТУ. Виходячи з  прийнятого припущення, атрибут ПРІЗВИЩЕ СТУДЕНТА не входить у ключ. Тоді атрибут НОМЕР ЗАЛІКОВОЇ КНИЖКИ не визначається значенням атрибута ПРІЗВИЩЕ СТУДЕНТА, тобто атрибути ПРІЗВИЩЕ СТУДЕНТА і НОМЕР ГРУПИ  не є основними, але функціонально  залежать від основного атрибута НОМЕР ЗАЛІКОВОЇ КНИЖКИ, що входить  у складовий ключ. Функціональні  залежності між атрибутами цього  відношення показані на рис. 4.7.

Рисунок 4.7 - Функціональна  залежність

Розщепивши вихідне відношення на два нових у другій нормальній формі, можна усунути надлишковість (рис. 4.8 ). При виконанні цієї операції розбивки на два відношення враховано  те, що атрибути, які функціонально  залежать від одного основного атрибута разом із ним утворять одне відношення з єдиним ключем НОМЕР ЗАЛІКОВОЇ  КНИЖКИ, а інші атрибути, які функціонально  повно залежать від складового ключа ,залишено у вихідній схемі.

Рисунок 4.8 - Приклад усунення функціональної залежності

Розглянемо наступний  приклад схеми відношення:

СПІВРОБІТНИКИ - ВІДДІЛИ - ПРОЕКТИ

(СПІВРОБ НОМЕР,СПІВРОБ  ЗАРП, ВІДДІЛ НОМЕР, ПРО НОМЕР,  СПІВРОБ ЗАВДАННЯ).

У відношенні використані  скорочення : СПІВРОБ- співробітник, ЗАРП- зарплата, ПРО- проект.

Первинний ключ:

СПІВРОБ НОМЕР, ПРО НОМЕР.

Функціональні залежності:

СПІВРОБ НОМЕР -> СПІВРОБ  ЗАРП

СПІВРОБ НОМЕР -> ВІДДІЛ НОМЕР

ВІДДІЛ НОМЕР -> СПІВРОБ  ЗАРП

СПІВРОБ НОМЕР, ПРО НОМЕР -> СПІВРОБ ЗАВДАННЯ.

Хоча первинним ключем є складовий атрибут СПІВРОБ  НОМЕР, ПРО НОМЕР, атрибути СПІВРОБ  ЗАРП і ВІДДІЛ НОМЕР функціонально  залежать від частини первинного ключа, тобто атрибута СПІВРОБ НОМЕР. В результаті, неможливо вставити у відношення СПІВРОБІТНИКИ - ВІДДІЛИ - ПРОЕКТИ кортеж, що описує співробітника, який ще не виконує ніякого проекту (первинний ключ не може містити  невизначене значення). При видаленні  кортежу не тільки руйнується зв'язок даного співробітника з даним  проектом, але втрачається інформацію про те, що він працює в деякому  відділі. При переводі співробітника  в інший відділ необхідно модифікувати всі кортежі, які описують цього  співробітника, або одержимо неузгоджений результат. Такі неприємні явища  називаються аномаліями схеми відношення. Вони усуваються шляхом нормалізації.

Виконаємо декомпозицію відношення СПІВРОБІТНИКИ - ВІДДІЛИ в два  відношення СПІВРОБІТНИКИ - ВІДДІЛИ  і СПІВРОБІТНИКИ - ПРОЕКТИ :

СПІВРОБІТНИКИ - ВІДДІЛИ

(СПІВРОБ НОМЕР, СПІВРОБ  ЗАРП, ВІДДІЛ НОМЕР)

Первинний ключ:

СПІВРОБ НОМЕР

Функціональні залежності:

СПІВРОБ НОМЕР -> СПІВРОБ  ЗАРП

СПІВРОБ НОМЕР -> ВІДДІЛ НОМЕР

ВІДДІЛ НОМЕР -> СПІВРОБ  ЗАРП

СПІВРОБІТНИКИ - ПРОЕКТИ

(СПІВРОБ_НОМЕР, ПРО НОМЕР,  СПІВРОБ ЗАВДАННЯ)

Первинний ключ:

СПІВРОБ НОМЕР, ПРО НОМЕР

Функціональна залежність:

СПІВРОБ НОМЕР, ПРО НОМЕР -> СПІВРОБ_ЗАВДАННЯ

Кожне з цих двох відношень  знаходиться в 2НФ і в них усунуті  відзначені вище аномалії .

Відношення R знаходиться  в третій нормальній формі (3НФ) у  тому і тільки в тому випадку, якщо знаходиться в 2НФ і кожний неключовий атрибут нетранзитивно залежить від первинного ключа.

Наприклад, відношення:

ГУРТОЖИТОК (ПІБ СТУДЕНТА, НОМЕР ГРУПИ, НОМЕР КІМНАТИ, СТАРОСТА КІМНАТИ) знаходиться в другій нормальній формі, але не в третій, тому що атрибут  СТАРОСТА КІМНАТИ залежить від атрибута НОМЕР КІМНАТИ, який у свою чергу  залежить від атрибута ПІБ СТУДЕНТА і, отже, СТАРОСТА КІМНАТИ транзитивно  залежить від ПІБ СТУДЕНТА. Це відношення можна привести до необхідної форми  шляхом його розщеплення на два:

СТУДЕНТ-ГУРТОЖИТОК (ПІБ  СТУДЕНТА, НОМЕР ГРУПИ, НОМЕР КІМНАТИ),

КІМНАТА -ГУРТОЖИТОК (НОМЕР  КІМНАТИ. СТАРОСТА КІМНАТИ).

Залежності між атрибутами вихідного й отриманих відношень  подані на рис. 4.9 , звідки видно, що отримані відношення більш доцільніші від  вихідного. Так, інформація про старосту кімнати може знадобитися незалежно  від інформації про студентів, що проживають у цій кімнаті.

Рисунок 4.9 - Приклад усунення транзитивної залежності

Нехай маємо відношення (ФІРМА, СКЛАД, ОБ'ЄМ ).

Для даного відношення характерні наступні аномалії.

1. Якщо в даний момент  відсутня фірма, яка отримує  товар зі складу, то в базу  даних неможливо ввести інформацію  про об'єм складу.

2. Якщо фірма перестає  отримувати товар зі складу, то  данні про склад та його  об'єм не можна зберігати в  базі даних.

3. Якщо об'єм складу  змінився, то необхідно переглянути  всі рядки відношення і змінити  кортежі для форм, пов'язаних зі  складом.

Причиною аномалій для  даного відношення є наявність транзитивного  зв'язку між атрибутами.

Для усунення аномалій розіб'ємо  вихідне відношення на два :

ЗБЕРІГАННЯ ( ФІРМА, СКЛАД),

ОБ'ЄМ (СКЛАД, ОБ'ЄМ).

На практиці в більшості  випадків три нормальні форми  схем відношень є достатніми і  приведенням до третьої нормальної форми процес проектування реляційної бази даних , як правило, закінчується. Однак іноді корисно продовжити процес нормалізації.

Розглянемо наступний  приклад схеми відношення:

СПІВРОБІТНИКИ-ПРОЕКТИ (СПІВРОБІТНИКА  НОМЕР, СПІВРОБІТНИКА ПРИЗВІЩЕ, ПРОЕКТУ  НОМЕР, СПІВРОБІТНИКА ЗАВДАННЯ) .

Можливі ключі (звернете увагу, що на цій стадії нормалізації в  увагу приймаються існування  можливих ключів):

СПІВРОБІТНИКА НОМЕР, ПРОЕКТУ  НОМЕР

СПІВРОБІТНИКА ПРИЗВІЩЕ, ПРОЕКТУ  НОМЕР .

Функціональні залежності:

СПІВРОБІТНИКА НОМЕР -> СПІВРОБІТНИКА  ПРИЗВІЩЕ

СПІВРОБІТНИКА НОМЕР -> ПРОЕКТУ  НОМЕР

СПІВРОБІТНИКА ПРИЗВІЩЕ -> СПІВРОБІТНИКА НОМЕР

СПІВРОБІТНИКА ПРИЗВІЩЕ -> ПРОЕКТУ НОМЕР

СПІВРОБІТНИКА НОМЕР, ПРОЕКТУ  НОМЕР -> СПІВРОБІТНИКА

ЗАВДАННЯ

СПІВРОБІТНИКА ПРИЗВІЩЕ, ПРОЕКТУ  НОМЕР ->

СПІВРОБІТНИКА ЗАВДАННЯ

У цьому прикладі припускаємо, що особистість співробітника цілком визначається як його номером, так і  прізвищем.

Незалежно від того, який із можливих ключів обраний у якості первинного ключа, ця схема знаходиться  в 3НФ. Однак той факт, що є функціональні  залежності атрибутів відношення від  атрибута, що є частиною первинного ключа, приводить до аномалій. Наприклад, для того, щоб змінити ПРИЗВІЩЕ співробітника з даним номером  погодженим способом, буде потрібно модифікувати всі кортежі, які включають його номер. Введемо визначення.

Детермінантом називається  будь-який атрибут, від котрого функціонально  повно залежить деякий інший атрибут.

Нормальна форма Бойса-Кодда. Відношення R знаходиться в нормальній формі Бойса-Кодда (БКНФ) у тому і  тільки в тому випадку, якщо кожний детермінант є можливим ключем.

Зауважимо, що якщо у відношенні є тільки один можливий ключ (який є  первинним ключем), те це визначення стає еквівалентним визначенню третьої  нормальної форми.

Очевидно, що ця вимога не виконана для відношення СПІВРОБІТНИКИ-ПРОЕКТИ. Можна зробити його декомпозицію до відношень СПІВРОБІТНИКИ і  СПІВРОБІТНИКИ-ПРОЕКТИ:

СПІВРОБІТНИКИ (СПІВРОБІТНИКА  НОМЕР, СПІВРОБІТНИКА ПРИЗВІЩЕ) .

Можливі ключі:

СПІВРОБІТНИКА НОМЕР

СПІВРОБІТНИКА ПРИЗВІЩЕ

Функціональні залежності:

СПІВРОБІТНИКА НОМЕР -> СПІВРОБІТНИКА  ПРИЗВІЩЕ

СПІВРОБІТНИКА ПРИЗВІЩЕ -> СПІВРОБІТНИКА НОМЕР

СПІВРОБІТНИКИ-ПРОЕКТЫ (СПІВРОБІТНИКА  НОМЕР,

ПРОЕКТУ НОМЕР, СПІВРОБІТНИКА  ЗАВДАННЯ)

Можливий ключ:

СПІВРОБІТНИКА НОМЕР, ПРОЕКТУ  НОМЕР

Функціональні залежності:

СПІВРОБІТНИКА НОМЕР, ПРОЕКТУ  НОМЕР -> СПІВРОБІТНИКА ЗАВДАННЯ.

Можлива альтернативна декомпозиція, якщо вибрати за основу СПІВРОБІТНИКА  ПРИЗВІЩЕ. У обох випадках отримані відношення СПІВРОБІТНИКИ і СПІВРОБІТНИКИ-ПРОЕКТИ  знаходяться в БКНФ, і їм не властиві відзначені аномалії.

Розглянемо відношення

R (МІСТО, АДРЕСА, ІНДЕКС).

Атрибут ІНДЕКС визначає індекс відділення зв'язку, яке обслуговує адресатів деякої вулиці міста, АДРЕСА - назву вулиці і номеру будинку. При цьому будемо припускати, що кортеж (С, S, Z) належить деякому відношенню зі схемою відношення R, якщо тільки в  місті С є будинок за адресою S і Z є відповідним поштовим індексом. У цьому випадку мають місце  наступні функціональні залежності:

МІСТО, АДРЕСА ІНДЕКС,

ІНДЕКС МІСТО.

Іншими словами, повна  адреса (назва міста і адреса в  місті) визначає поштовий індекс, а  поштовий індекс, у свою чергу, визначає назву міста, але не визначає адресу, тому що одне відділення зв'язку обслуговує багато будинків на різних вулицях. Таким  чином, в якості основного ключа  можна вибрати одне з двох множин атрибутів:

МІСТО, АДРЕСА і

АДРЕСА, ІНДЕКС .

Схема відношення R (МІСТО, АДРЕСА, ІНДЕКС) не знаходиться в нормальній формі Бойса-Кода, так як має місце  залежність ІНДЕКС МІСТО. Декомпозицією  відношення його можна привести до нормальної форми Бойса-Кода.

Розглянемо приклад наступної  схеми відношення:

ПРОЕКТИ( ПРОЕКТУ НОМЕР, ПРОЕКТУ  СПІВРОБ, ПРОЕКТУ ЗАВДАННЯ) .

Відношення ПРОЕКТИ містить  номера проектів, кожний проекту - список співробітників, які можуть виконувати проект, і список завдань, які передбачаються проектом. Співробітники можуть брати  участь у декількох проектах, і  різні проекти можуть включати однакові завдання.