Первая страница
Наша команда
Контакты
О нас

    Головна сторінка



Конспект лекцій зміст модуль №1 «Основні поняття систем баз даних. Реляційна модель даних» 2

Конспект лекцій зміст модуль №1 «Основні поняття систем баз даних. Реляційна модель даних» 2




Сторінка4/7
Дата конвертації18.05.2017
Розмір0.69 Mb.
1   2   3   4   5   6   7

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

Однією з складних проблем, з якою стикається більшість розробників БД, є проблема оцінки, які елементи даних належить збирати в один кортеж. Непродумане групування елементів даних може призвести як мінімум до дублювання даних, а як максимум до неможливості виконати запит. Головна мета раціонального групування даних в кортежі – зменшення надмірності даних. Для рішення цієї проблеми застосовують підхід, який зветься нормалізація відношень.

Відношення реляційної БД містять як структурну, так і семантичну інформацію. Структурна інформація задається схемою відношення, а семантична виражена функціональними зв’язками між атрибутами. Склад атрибутів БД повинен задовольняти дві основні вимоги:


  • між атрибутами не може бути небажаної функціональної залежності;

  • групування атрибутів повинне забезпечувати мінімальне дублювання даних.

Задоволення цих вимог досягається шляхом нормалізації відношень БД. Нормалізація відношень – це покроковий зворотній процес розкладання початкових відношень на більш дрібні і прості. При цьому встановлюються всі можливі функціональні залежності. Апарат нормалізації був розроблений Коддом. В ньому визначаються різні нормальні форми. Кожна з нормальних форм обмежує типи функціональних залежностей відношень. Кодд виділив три нормальні форми: 1НФ, 2НФ, 3НФ. Вважається достатнім приведення до 3НФ.

Перша нормальна форма

Кожен атрибут повинен бути простим, тобто мати атомарні або неподільні значення. Наприклад, відношення СТУДЕНТ_СПОРТ (рис. 2.16) ненормалізоване, оскільки містить складний атрибут “спорт”.

СТУДЕНТ_СПОРТ



прізвище_імя_по-батькові

курс

спеціальність

спорт

вид

розряд

Петренко А.І.

2

прикл. мат

плавання

мс

Іванов І.І.

3

механіка

футбол

кмс

Сидоров А.А.

1

економіка

шахи


Рис. 2.16. Об’єктне відношення СТУДЕНТ_СПОРТ.


Первинним ключем цього відношення є атрибут “прізвище_ім’я_по-батькові”. Відношення, у якого всі атрибути прості, називається приведеним до першої нормальної форми (1НФ). Приведемо це відношення до 1НФ, тобто звільнимося від складного атрибута “спорт”. В отриманому відношенні ключ складається з атрибутів “прізвище_ім’я_по-батькові” та “вид_спорт” (рис. 2.17).

СТУДЕНТ_ВИД_СПОРТ



прізвище_імя_по-батькові

вид_спорт

курс

спеціальність

розряд

Петренко А.І.

плавання

2

прикл. мат

мс

Іванов І.І.

футбол

3

механіка

кмс

Сидоров А.А.

шахи

1

економіка


Рис. 2.17. Об’єктне відношення СТУДЕНТ_ ВИД_СПОРТ в першій нормальній формі.


Типи функціональних залежностей

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

Момент часу - точка на часовій осі. Про події, що відповідають одному моменту часу, говорять як про одночасні.
Функціональна залежність відображається так: A→B. Розглянемо відношення ВИКЛАДАЧ_ПРЕДМЕТ (рис. 2.18) з складеним первинним ключем “табельний_номер” і “назва_предмету”. Вважаємо, що викладач закріплений за однією кафедрою.


ВИКЛАДАЧ_ПРЕДМЕТ

табельний_номер

назва_предмету

кількість_годин

прізвище

посада

оклад

кафедра

телефон

201

ІС

36

Федін

доцент

1000

ЕОМ

5–89

201

Бух. облік

72

Федін

доцент

1000

ЕОМ

5–89

202

СКБД

48

Васильєв

доцент

1000

ЕОМ

5–89

301

СКБД

48

Костенко

професор

2000

АСУ

5–89

401

Оптика

24

Петренко

асистент

500

Фізика

3–10

401

Механіка

30

Петренко

асистент

500

Фізика

3–10

Рис. 2.18. Відношення ВИКЛАДАЧ_ПРЕДМЕТ.


В цьому відношенні атрибут “посада” функціонально залежить від атрибута “оклад”, “табельний_номер” від атрибута “прізвище ”, “прізвище” від атрибута “ табельний_номер” і т. ін. Атрибут може функціонально залежати від цілої групи атрибутів Якщо відношення знаходиться в 1НФ, то всі неключові атрибути функціонально залежать від ключа, але ступінь залежності може бути різним. Якщо неключовий атрибут залежить тільки від частини ключа, то кажуть про часткову залежність, наприклад, неключовий атрибут “кількість_годин” функціонально залежить тільки від частини ключа, тобто тільки від атрибута “назва_предмету”.

Якщо неключовий атрибут залежить від всього складеного ключа і не знаходиться в частковій залежності від його частин, то говорять про його повну функціональну залежність від складеного ключа. В відношенні ВИКЛАДАЧ_ПРЕДМЕТ не має атрибутів, які знаходяться в повній функціональній залежності від складеного ключа.

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

Якщо для атрибутів A, B, C виконуються умови A→B, B→C, а зворотна залежність відсутня, то говорять, що C залежить від A транзитивно. Наприклад, “прізвище”→”кафедра”→”телефон”.

У відношеннях між атрибутами може існувати ще один тип залежності – багатозначна залежність.

В теорії баз даних, багатозначна залежність - повне обмеження між двома множинами атрибутів у відношенні.
У відношенні R атрибут B багатозначно залежить від A (A→→B), якщо кожному значенню A відповідає множина значень B, ніяк не пов’язаних з іншими атрибутами з R. Багатозначна залежність можлива при наявності у відношенні хоча б трьох атрибутів: ключа і не менш двох незалежних один від одного атрибутів. Наприклад, розглянемо відношення ВИКЛАДАЧ_ РОЗКЛАД (рис. 2.19).

ВИКЛАДАЧ_РОЗКЛАД



прізвище

група

предмет

Іванов

410

СКБД

Іванов

410

ІС

Іванов

309

СКБД

Іванов

309

ІС

Сидоров

510

СКБД

Рис. 2.19. Відношення ВИКЛАДАЧ_ РОЗКЛАД.

Між викладачами і групами студентів є зв’язок типу “один-до-багатьох” (1:М), оскільки викладач може вести курсові проекти в одній і більше групах, однак кожній групі відповідає один викладач. Між викладачем і предметами є зв’язок типу “багато-до-багатьох” (M:N), оскільки викладач може читати один і більше предметів, і навпаки, один предмет можуть читати декілька викладачів. В відношенні існує незалежні багатозначні залежності “прізвище”→→”група” і “прізвище”→→”предмет”, так як значення багатозначних атрибутів ”група” і ”предмет” ніяк не пов’язані між собою і можлива зміна їх значень в будь-якому рядку відношення.

Друга нормальна форма



  1. В відношенні ВИКЛАДАЧ_ПРЕДМЕТ можна відмітити часткову функціональну залежність атрибутів “прізвище”, “посада”, “оклад”, “кафедра”, “телефон” від частини “табельний_номер” складеного ключа. Така часткова залежність приводить до наступних аномалій:

  2. Має місце дублювання даних про викладача, оскільки викладач може читати декілька предметів.

  3. Існує проблема контролю надлишковості даних, так як зміна, наприклад, окладу спричиняє необхідність пошуку та зміни значень окладів у всіх кортежах з даним викладачем.

  4. Виникає проблема з викладачами, які вданий час не ведуть предмети. Викладача без предмета неможливо включити у відношення і, навпаки, якщо викладач звільняється і вилучається із відношення, то буде віддалений і предмет, хоча предмет продовжує читатися. Таким чином, відношення у 2НФ потребує подальших перетворень.

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

  6. Побудувати проекцію на частину складеного ключа і атрибути, залежні від цієї частини;

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

  8. Отримаємо два відношення ВИКЛАДАЧ і ПРЕДМЕТ, які знаходяться у 2НФ (рис. 2.20).

ВИКЛАДАЧ


табельний_номер

прізвище

посада

оклад

кафедра

телефон

201

Федін

доцент

1000

ЕОМ

5–89

202

Васильєв

доцент

1000

ЕОМ

5–89

301

Костенко

професор

2000

АСУ

5–89

401

Петренко

асистент

500

Фізика

3–10

ПРЕДМЕТ


табельний_номер

назва_предмету

кількість_годин

201

ІС

36

201

Бух. облік

72

202

СКБД

48

301

СКБД

48

401

Оптика

24

401

Механіка

30

Рис. 2.20. Відношення ВИКЛАДАЧ і ПРЕДМЕТ у другій нормальній формі.


Таким чином, приведення до 2НФ можна описати наступним чином. Нехай дана змінна-відношення R(A,B,C,D) PRIMARY KEY {A,B} і є функціональна залежність A→D. Процедура нормалізації в 2НФ передбачає заміну цієї змінної-відношення наступними двома проекціями R1 і R2:

R1(A,D) PRIMARY KEY {A}

R2(A,B,C) PRIMARY KEY {A,B}

FOREIGN KEY {A} REFERENCES R1

В ряді випадків і 2НФ породжує незручності, пов’язані з надлишковістю даних. Для їх усунення використовується наступний крок нормалізації, який перетворює 2НФ у 3НФ.

Третя нормальна форма та подальша нормалізація

В отриманому відношенні ВИКЛАДАЧ є наступні транзитивні функціональні залежності:

“табельний_номер”→”кафедра”→”телефон”

“табельний_номер”→”посада”→”оклад”

Наявність транзитивних функціональних залежностей викликає аномалії наступного характеру (на прикладі атрибуту ”телефон”):



  1. Має місце дублювання інформації про телефон для викладача однієї кафедри.

  2. Існує проблема контролю надлишковості даних, оскільки зміна номеру телефону кафедри викликає необхідність пошуку і зміни всіх номерів телефонів всіх викладачів кафедри.

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

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

Отримаємо три відношення ВИКЛАДАЧ, ПОСАДА і КАФЕДРА, які знаходяться у 3НФ (рис. 2.21).


ВИКЛАДАЧ

табельний_номер

прізвище

посада

кафедра

201

Федін

доцент

ЕОМ

202

Васильєв

доцент

ЕОМ

301

Костенко

професор

АСУ

401

Петренко

асистент

Фізика

ПОСАДА

посада

оклад

доцент

1000

професор

2000

асистент

500

КАФЕДРА


кафедра

телефон

ЕОМ

5–89

АСУ

5–89

Фізика

3–10

Рис. 2.21. ВИКЛАДАЧ, ПОСАДА і КАФЕДРА у третій нормальній формі.


Приведення до 3НФ можна описати наступним чином. Нехай дана змінна-відношення R(A,B,C) PRIMARY KEY {A} і є функціональна залежність B→C. Процедура нормалізації в 3НФ передбачає заміну цієї змінної-відношення наступними двома проекціями R1 і R2:

R1(B,C) PRIMARY KEY {B}

R2(A,B) PRIMARY KEY {A}

FOREIGN KEY { B } REFERENCES R1

Таким чином, концептуальна модель БД у третій нормальній формі складається з чотирьох взаємопов’язаних відношень: ВИКЛАДАЧ, ПРЕДМЕТ, ПОСАДА і КАФЕДРА (рис. 2.22):


Рис. 2.22. Концептуальна модель БД.
Третя нормальна форма визволяє від надлишковості і аномалій виконання операцій включення, віддалення і поновлення, якщо відношення має один ключ, і інші залежності, в тому числі і багатозначні, в ньому відсутні. Але, якщо при цьому існують інші залежності, окрім залежності від ключа, то 3НФ не забезпечує відсутність аномалій операцій. В цьому випадку застосовують посилену 3НФ, так звану нормальну форму Бойса-Кодда (НФБК). Існують також 4НФ і 5НФ. Рівень нормалізації відношення залежить від його семантики, яка задається функціональними зв’язками. Вважається достатнім приведення до 3НФ.

1   2   3   4   5   6   7