Нормалізація відношень

Лекція № 13 (2 години)

Тема «Нормалізація відношень»

Мета: познайомитися з призначенням процесу нормалізації та формами нормалізації.

Література

«Бази даних. Проектування, реалізація та супровід. Теорія та практика» -Т. Конолі, К. Бегг, А. Строчан Москва, СПб., Київ. 2000 р.

«Бази даних: основи, проектування, використання» - Малихіна М. П. СПб. 2004р.

«Організація баз даних та знань» - Пасічник В. В., Резніченко В. А., Київ, 2006 р.

«Системы баз данных. Полный курс» - Г. Гарсия Молина, Москва: Вильямс, 2003р.

«Бази даних. Моделі, розробка, реалізація» - Карпов Т., СПб. 2001 р.

Хід заняття

І. Організаційний момент

а) готовність групи до заняття;

б) перевірка присутніх.

ІІ. Актуалізація опорних знань студентів

а) повідомлення теми та мети заняття;

б) повідомлення девізу, під яким будете працювати;

в) відповіді на запитання.

ІІІ. Виклад нового матеріалу

План

1. Процес нормалізації.
2. Перша нормальна форма.
3. Друга нормальна форма.
4. Третя нормальна форма.
5. Переваги нормалізації.
6. Недоліки нормалізації.

ІV. Узагальнення та систематизація знань.

V. Підведення підсумків заняття.

VІ. Домашнє завдання: вивчити матеріал лекції, знати відповіді на такі питання лекції:

1. Що розуміється під поняттям нормалізації?
2. Які нормальні форми існують в реляційній теорії?
3. В чому зміст 1 нормальної форми. Навести приклад бази даних, доведеної до 1 нормальної форми.
4. В чому зміст 2 нормальної форми. Навести приклад бази даних, доведеної до 2 нормальної форми.
5. В чому зміст 3 нормальної форми. Навести приклад бази даних, доведеної до 3 нормальної форми.
6. Якими перевагами володіє нормалізація?
7. Завдяки якій перевазі нормалізації спрощується структура бази даних та економиться дисковий простір?
8. Якими недоліками володіє нормалізація?

Під нормалізацією розуміється процес приведення відношення до однієї з нормальних форм (НФ). Але перед тим як розглядати нормальні форми, треба розповісти, для чого потрібна нормалізація.

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

Для підтримки бази даних в стійкому стані використовується ряд механізмів, які отримали узагальнену назву засобів підтримки цілістності. Ці механізми використовуються статично (на етапі проектування бази даних), так й динамічно (в процесі роботи з базою даних).

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

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

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

Нормалізація - це послідовне перетворення початкової бази даних до НФ, при цьому кожна наступна НФ обов’язково містить у собі попередню.

В реляційній теорії нараховують 6 НФ:  

• 1 НФ;
• 2 НФ;
• 3 НФ;
• НФ Бойса-Кодда (НФБК);
• 4 НФ;
• 5 НФ.

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

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

• кожна наступна нормальна форма покращує властивості попередньої нормальної форми;

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

Перша нормальна форма (1НФ)

Відношення знаходиться в 1 НФ, якщо на перетині кожного стовпця з кожним рядком міститься лише атомарне (нерозподілене) значення. Іншими словами, кожен атрибут відношення повинен зберігати одне єдине значення та не бути ні списком, ні множиною значень. Атрибут, який є атомарним в одному додатку, може виявитися складовим в іншому.

Приклад. В базі даних відділу кадрів фірми в таблиці, в якій зберігаються особисті відомості про співробітників, є атрибут «Домашня адреса», в якому адреса зберігається в форматі: місто, вулиця, будинок[/корпус], квартира (в даному випадку адреса зберігається у вигляді єдиного текстового рядка, так як малоймовірно, щоб необхідно було б обрати співробітників, наприклад, за номером квартири). Таким чином, в контексті бази даних відділу кадрів адреса є атомарним поняттям, та його ділення на складові частини немає сенсу, так як лише привнесе до бази даних надмірну громіздкість. Але та сама адреса для додатку, призначеного для сортування пошти у поштовому відділенні компанії, не є атомарним, тому що бажано було б згрупувати конверти в окремі купи по вулицям, так як кожну вулицю обслуговує свій листоноша. Крім цього, з метою оптимізації переміщення листоноші в межах вулиці, кожну купу бажано було б відсортувати за номерами будинків, для того щоб зробити можливим рознесення пошти за один прохід по вулиці без повернення.

Доведення відношення до 1 НФ – проста операція. Треба проглянути відношення та розподілити складові атрибути на різні рядки/стовпці. Можливо, цю ситуацію доведеться повторити декілька разів до тих пір, доки кожен із атрибутів не стане атомарним.

Приклад. В базі даних міститься таблиця підприємств, в якій зберігаються такі відомості:

• найменування підприємства;
• місто;
• адреса;
• електронна адреса;
• веб-сторінка;
• вид агенту (постачальник або клієнт);
• контактні особи (може бути декілька), посада контактної особи, телефон контактної особи.

Табл. 1

Найм-ня	Місто	Адреса	Ел. пошта	Веб-стор.	Вид	Конт. особа
Поршневий з-д	Київ	Вул. 2-а Кольцева, 17	info@ plunger.ru	www. plunger.ru	Постачаль- ник	Іванов І. І., заступник директора, тел (044)76-15-95 Петров П. П., начальник від. збуту, тел (044)76-15-35
ПП«Вимпел»	Одеса	Вул. Гоголя, 25	pennon@ mail.ru		Клієнт	Сидоров С. С., директор, тел. (048)66-65-38
ПП «Альфа»	Київ	Вул. Пушкінська, 37, оф. 565	alpha@ list.ru		Клиієт	Васильєв В.В., директор, тел (044)74-57-45

В даному випадку атрибут «Контактна особа» не є атомарним, тому що в ньому зустрічаються списки з декількох осіб. Розподілимо ці кортежі таким чином, щоб кожен кортеж містив дані тільки про одну особу:

Табл. 2

Найм-ня	Місто	Адреса	Ел. пошта	Веб-стор.	Вид	Конт. особа
Поршневий з-д	Київ	Вул. 2-я Кольцева, 17	info@ plunger.ru	www. plunger.ru	Постачаль-ник	Іванов І. І., заступник директора, тел (044)76-15-95
Поршневий з-д	Київ	Вул. 2-я Кольцева, 17	info@ plunger.ru	www. plunger.ru	Постачаль-ник	Петров П. П., начальник від. збуту, тел (044)76-15-35
ПП «Вимпел»	Одеса	Вул. Гоголя, 25	рennon @mail.ru		Клієнт	Сидоров С.С., директор, тел. (048)66-65-38
ПП «Альфа»	Київ	Вул. Пушкінськая, 37, оф. 565	alpha @list.ru		Клієнт	Васильєв В.В., директор, тел (044)74-57-45

Трохи краще, хоча якщо придивитися виявляється, що атрибут «Контактна особа» може бути атомарним знов з натягуванням, так як містить дані різного роду, хоча й про одну особу. Розподілемо його на декілька атрибутів:

Табл. 3

Найм-ня	Місто	адреса	Ел. пошта	Веб-сторінка	Вид	Посада	ПІБ	Код міста	Тел.
Поршне- вий з-д	Київ	Вул. 2-я Кольцева, 17	info@ plunger.ru	www. plunger.ru	Поста- чаль- ник	Заст. дир.	Іванов І.І.	044	76-15-95
Поршне- вий з-д	Київ	Вул. 2-я Кольцева, 17	info@ plunger.ru	www. plunger.ru	Поста- чаль- ник	Нач. від. збуту	Петров П.П.	044	76-15-35
ПП «Вимпел»	Одеса	Вул. Гоголя, 25	pennon@ mail.ru		Клієнт	Дирек- тор	Сидоров С.С.	048	66-65-38
ПП «Альфа»	Київ	Вул. Пуш-кінська, 37, оф. 565	alpha323@ list.ru		Клієнт	Дирек- тор	Васильєв В.В.	044	74-57-45

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

Друга нормальна форма (2НФ)

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

По-перше, при заповненні таких записів легко помилитися. Наприклад, достатньо змінити лише один символ в атрибуті «Адреса», та це стане зовсім другою адресою, яка немає нічого спільного з першою адресою. Знайти такі помилки в величезній таблиці – дуже складна задача.

По-друге, назва вулиці може змінитися. Може змінитися й адреса веб-сайту фірми. Тоді треба буде проглядати всю таблицю та змінювати відповідні значення.

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

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

Припустимо, що при постановці задачі замовник повідомив нам, що в межах кожного міста найменування підприємств є унікальним, але в різних містах найменування можуть співпадати. Таким чином, підприємство характеризується складовим ключем «Найменування + Місто». Подивимося на наше відношення з точки зору того, як ми тільки що дізналися про 2 НФ. Явно, що телефонний код міста залежить виключно від самого міста та жодним чином не пов’язаний з найменуванням підприємства. Звідси й одне з джерел надмірності даних – скільки разів в таблиці зустрічається рядок, який містить відомості про контактну особу будь-якого підприємтва, яке знаходиться в даному місті, стільки й повторюється інформація про код міста. Для усунення цієї надмірності, треба розподілити наше відношення на декілька відношень:

      

    Табл. 4а

Найм-ня	Місто	Адреса	Ел. пошта	Веб- сторінка	Вид	Посада	ПІБ	Тел.
Поршне вий з-д	Київ	Вул. 2-я Кольцева, 17	info@ plunger.ru	www. plunger.ru	Поста-чальник	Заст. дир.	Іванов І.І.	76-15-95
Поршне вий з-д	Київ	Вул. 2-я Кольцева, 17	info@ plunger.ru	www. plunger.ru	Поста- чальник	Нач. від. збуту	Петров П.П.	76-15-35
ПП «Вимпел»	Одеса	Вул. Гоголя, 25	pennon@ mail.ru		Клієнт	Директор	Сидоров С.С.	66-65-38
ПП «Альфа»	Київ	Вул. Пуш- кінська, 37, оф. 565	alpha323@ list.ru		Клиієт	Директор	Васильєв В.В.	74-57-45

  Табл. 4б

Місто	Код міста
Київ	044
Одеса	048

Отже, ми усунули часткову залежність атрибута «Код міста» від складового ключа, перемістивши коди міст в окреме відношення з ключем «Місто». Таким чином, ми отримали два відношення, кожне з яких знаходиться в 2 НФ.

Недивлячись на здійснені зусилля, таблиця 4а все ж таки містить надмірність – достатньо поглянути на значення в стовбцях «Адреса», «Ел. Пошта» та «Веб. сторінка», які півторюються. Це значить, що процес нормалізації не є завершуваним, та треба приступати до його наступного етапу.

Третя нормальна форма (3НФ)

Існує функціональна залежність між атрибутами «ПІБ», «Посада» та «Телефон». Є очевидним, що на підприємстві деяка людина займає визначену посаду та розпоряджається визначеним робочим телефоном. Зворотнє в спільному випадку не є вірним – на підприємстві може існувати декілька аналогічних штатних одиниць, наприклад, менеджери за збутом, та декілька людин мають можливість користуватися одним робочим телефоном.

Для того, щоб уникнути цю функціональну залежність, треба зробити декомпозицію таблиці 4а на дві таблиці. Перша з них буде зберігати факти, які відносяться безпосередньо до самого підприємства:

Табл. 5а

Найм-ня	Місто	Адреса	Ел. пошта	Веб-сторінка	Вид
Поршневий з-д	Київ	Вул. 2-я Кольцева, 17	info@plunger.ru	www.plunger.ru	Постачаль- ник
ПП «Вимпел»	Одеса	Вул. Гоголя, 25	pennon@mail.ru		Клієнт
ПП «Альфа»	Київ	Вул. Пушкинська, 37, оф. 565	alpha323@list.ru		Клієнт

Друга таблиця буде зберігати факти, які відносяться до конкретної особи, яка виконує обов’язки на цьому підприємстві:

  Табл. 5б

Найм-ня	Місто	ПІБ	Посада	Тел.
Поршневий з-д	Київ	Іванов І.І.	Заступник директора	76-15-95
Поршневий з-д	Київ	Петров П.П.	Начальник від. збуту	76-15-35
ПП «Вимпел»	Одеса	Сидоров С.С.	Директор	66-65-38
ПП «Альфа»	Київ	Васильєв В.В.	Директор	74-57-45

Разом з таблицею 4б цей набір таблиць уявляє собою нашу першопочаткову базу даних, наведену до 3 НФ.  

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

В більшості випадків досягнення третьої нормальної форми вважається достатнім для реальних проектів баз даних, проте в теорії нормалізації існують нормальні форми вищих порядків (НФБК, 4НФ, 5НФ), деякі з яких пов'язані вже не з функціональними залежностями між атрибутами стосунків, а відображають тонші питання смислового вмісту наочної області.

Переваги нормалізації:

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

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

Недоліки нормалізації

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

Нормалізація відношень