Нормальні форми відносин

Етапи розробки бази даних

Метою розробки будь-якої бази даних є зберігання та використання інформації про будь-якої предметної області. Для реалізації цієї мети є наступні інструменти:


  1. Реляційна модель даних – зручний спосіб представлення даних предметної області.
  2. Мова SQL – універсальний спосіб маніпулювання такими даними.

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

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

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

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

Модель предметної області. Модель предметної області – це наші знання про предметну область. Знання можуть бути як у вигляді неформальних знань в мозку експерта, так і виражені формально за допомогою яких-небудь засобів. В якості таких коштів можуть виступати текстові описи предметної області, набори посадових інструкцій, правила ведення справ у компанії і т.п. Досвід показує, що текстовий спосіб подання моделі предметної області вкрай неефективний. Набагато більш інформативними і корисними при розробці баз даних є описи предметної області, виконані за допомогою спеціалізованих графічних нотацій. Є велика кількість методик опису предметної області. З найбільш відомих можна назвати методику структурного аналізу SADT і засновану на ньому IDEF0, діаграми потоків даних Гейна-Сарсона, методику об'єктно-орієнтованого аналізу UML, і ін Модель предметної області описує швидше процеси, що відбуваються в предметній області і дані, використовувані цими процесами. Від того, наскільки правильно змодельована предметна область, залежить успіх подальшої розробки додатків.

Логічна модель даних. На наступному, більш низькому рівні знаходиться логічна модель даних предметної області. Логічна модель описує поняття предметної області, їх взаємозв'язок, а також обмеження на дані, які накладаються предметною областю. Приклади понять – "співробітник", "відділ", "проект", "зарплата". Приклади взаємозв'язків між поняттями – "співробітник числиться рівно в одному відділі", "співробітник може виконувати кілька проектів "," над одним проектом може працювати декілька співробітників ". Приклади обмежень -" вік співробітника не менше 16 і не більше 60 років ".

Логічна модель даних є початковим прототипом майбутньої бази даних. Логічна модель будується в термінах інформаційних одиниць, але без прив'язки до конкретної СУБД. Більш того, логічна модель даних необов'язково повинна бути виражена засобами саме реляційної моделі даних. Основним засобом розробки логічної моделі даних у даний момент є різні варіанти ER-діаграм (Entity-Relationship, діаграми сутність-зв'язок). Одну й ту ж ER-модель можна перетворити як в реляційну модель даних, так і в модель даних для ієрархічних і мережевих СУБД, або в постреляціонную модель даних. Однак, оскільки ми розглядаємо саме реляційні СУБД, то можна вважати, що логічна модель даних для нас формулюється в термінах реляційної моделі даних.

Рішення, прийняті на попередньому рівні, при розробці моделі предметної області, визначають деякі межі, в межах яких можна розвивати логічну модель даних, в межах же цих кордонів можна приймати різні рішення. Наприклад, модель предметної області складського обліку містить поняття "склад", "накладна", "товар". При розробці відповідної реляційної моделі ці терміни обов'язково повинні бути використані, але різних способів реалізації тут багато – можна створити одне відношення, в якому будуть присутні як атрибутів "склад", "накладна", "товар", а можна створити три окремих відносини, по одному на кожне поняття.

При розробці логічної моделі даних виникають питання: чи добре спроектовані відносини? Чи правильно вони відображають модель предметної області, а отже і саму предметну область?

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

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

При розробці фізичної моделі даних виникають питання: чи добре спроектовані таблиці? Чи правильно вибрані індекси? Наскільки багато програмного коду у вигляді тригерів і збережених процедур необхідно розробити для підтримки цілісності даних?

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

Але знову рішення, прийняті на попередньому рівні – рівні фізичного проектування, визначають межі, в межах яких можна приймати рішення щодо вибору програмно-апаратної платформи і налаштування СУБД.

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


Критерії оцінки якості логічної моделі даних

Мета даного розділу – описати деякі принципи побудови хороших логічних моделей даних. Хороших в тому сенсі, що рішення, прийняті в процесі логічного проектування приводили б до хорошим фізичним моделям і в кінцевому підсумку до хорошої роботи бази даних.

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

Звичайно, таких критеріїв може бути дуже багато і вибір їх у достатній мірі довільний. Ми розглянемо деякі з таких критеріїв, які є безумовно важливими з точки зору отримання якісної бази даних:


Адекватність бази даних предметної області

База даних повинна адекватно відображати предметну область. Це означає, що повинні виконуватися наступні умови:


  1. Стан бази даних в кожний момент часу має відповідати стану предметної області.
  2. Зміна стану предметної області повинно приводити до відповідної зміни стану бази даних
  3. Обмеження предметної області, відображені в моделі предметної області, повинні певним чином відбиватися і враховуватися базі даних.

Легкість розробки і супроводу бази даних

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

Збережені процедури – Це процедури і функції, що зберігаються безпосередньо у базі даних в відкомпілювався вигляді і які можуть запускатися користувачами або додатками, що працюють з базою даних. Збережені процедури зазвичай пишуться або на спеціальному процедурному розширенні мови SQL (наприклад, PL / SQL для ORACLE або Transact-SQL для MS SQL Server), або на деякій універсальній мові програмування, наприклад, C + +, з включенням в код операторів SQL у відповідності зі спеціальними правилами такого включення. Основне призначення процедур, що зберігаються – реалізація бізнес-процесів предметної області.

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

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


Швидкість операцій оновлення даних (вставка, оновлення, видалення)

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

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

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

Розглянемо операції оновлення та вилучення даних з таблиці. Перш, ніж оновити або видалити запис, її потрібно знайти. Якщо таблиця не індексована, то єдиним способом пошуку є послідовне сканування таблиці в пошуку потрібного запису. У цьому випадку, швидкість операцій оновлення і видалення істотно збільшується зі збільшенням кількості записів у таблиці і не залежить від кількості атрибутів. Але насправді неіндексовані таблиці практично ніколи не використовуються. Для кожної таблиці зазвичай оголошується один або кілька індексів, відповідний потенційним ключам. За допомогою цих індексів пошук запису відбувається дуже швидко і практично не залежить від кількості рядків і атрибутів в таблиці (хоча, звичайно, деяка залежність є). Якщо для таблиці оголошено кілька індексів, то при виконанні операцій оновлення і видалення ці індекси повинні бути перебудовані, на що витрачається додатковий час. Таким чином, швидкість виконання операцій оновлення і видалення також зменшується при збільшенні кількості індексів у таблиці і мало залежить від числа рядків в таблиці.

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

Додаткові міркування на користь наведеного тези про уповільнення виконання операцій оновлення даних (вплив журналізації, довжини рядків таблиць) наведені в роботі А. Прохорова [27].


Швидкість операцій вибірки даних

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


Основний приклад

Розглянемо як предметної області деяку організацію, яка виконує деякі проекти. Модель предметної області опишемо наступним неформальним текстом:


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

У ході додаткового уточнення того, які дані необхідно враховувати, з'ясувалося наступне:


  1. Про кожного співробітника необхідно зберігати табельний номер і прізвище. Табельний номер є унікальним для кожного співробітника.
  2. Кожен відділ має унікальний номер.
  3. Кожен проект має номер і найменування. Номер проекту є унікальним.
  4. Кожна робота з проекту має номер, унікальний в межах проекту. Роботи в різних проектах можуть мати однакові номери.

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

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

У ході логічного моделювання на першому кроці запропоновано зберігати дані в одному відношенні, що має такі атрибути:

СОТРУДНІКІ_ОТДЕЛИ_ПРОЕКТИ ( Н_СОТР , ФАМ, Н_ОТД, ТЕЛ, Н_ПРО , ПРОЕКТ, Н_ЗАДАН)

де

Н_СОТР – Табельний номер співробітника

ФАМ – Прізвище співробітника

Н_ОТД – Номер відділу, в якому значиться співробітник

ТЕЛ – Телефон співробітника

Н_ПРО – Номер проекту, над яким працює співробітник

ПРОЕКТ – Найменування проекту, над яким працює співробітник

Н_ЗАДАН – Номер завдання, над яким працює співробітник

Оскільки кожен співробітник у кожному проекті виконує рівно одне завдання, то в якості потенційного ключа відносини необхідно взяти пару атрибутів { Н_СОТР , Н_ПРО }.

У поточний момент стан предметної області відображається наступними фактами:

Цей стан відображається в таблиці (курсивом виділено ключові атрибути):



















































Н_СОТР


ФАМ


Н_ОТД


ТЕЛ


Н_ПРО


ПРОЕКТ


Н_ЗАДАН

1   Іванов 1 11-22-33 1   Космос 1
1   Іванов 1 11-22-33 2   Клімат 1
2   Петров 1 11-22-33 1   Космос 2
3   Сидоров 2 33-22-11 1   Космос 3
3   Сидоров 2 33-22-11 2   Клімат 2

Таблиця 1. Ставлення СОТРУДНІКІ_ОТДЕЛИ_ПРОЕКТИ


Аномалії оновлення

Навіть одного погляду на таблицю відносини СОТРУДНІКІ_ОТДЕЛИ_ПРОЕКТИ достатньо, щоб побачити, що дані зберігаються в ній з великою надмірністю. У багатьох рядках повторюються прізвища співробітників, номери телефонів, найменування проектів. Крім того, в даному відношенні зберігаються разом незалежні один від одного дані – і дані про співробітників, і про відділи, і про проекти, і про роботи за проектами. Поки ніяких дій з ставленням не виробляється, це не страшно. Але як тільки стан предметної області змінюється, то, при спробах відповідним чином змінити стан бази даних, виникає велика кількість проблем.

Історично ці проблеми отримали назву аномалії поновлення. Спроби дати строге поняття аномалії в базі даних не є цілком задовільними [51, 7]. У даних роботах аномалії визначені як протиріччя між моделлю предметної області і фізичної моделлю даних, підтримуваних засобами конкретної СУБД. "Аномалії виникають в тому випадку, коли наші знання про предметну область виявляються, з якихось причин, невимовним в схемі БД або входять в протиріччя з нею "[7]. Ми дотримуємося іншої точки зору, що полягає в тому, що аномалій у сенсі визначень згаданих авторів немає, а є або неадекватність моделі даних предметної області, або деякі додаткові труднощі в реалізації обмежень предметної області засобами СУБД. Більш глибоке обговорення проблеми строгого визначення поняття аномалій виходить за межі даної роботи.

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

Оскільки аномалії виявляють себе при виконанні операцій, що змінюють стан бази даних, то розрізняють такі види аномалій:

У відношенні СОТРУДНІКІ_ОТДЕЛИ_ПРОЕКТИ можна навести приклади наступних аномалій:


Аномалії вставки (INSERT)

У відношення СОТРУДНІКІ_ОТДЕЛИ_ПРОЕКТИ не можна вставити дані про співробітника, який поки не бере участь ні в одному проекті. Дійсно, якщо, наприклад, у другому відділі з'являється новий співробітник, скажімо, Пушніков, і він поки не бере участь ні в одному проекті, то ми повинні вставити у відношення кортеж (4, Пушніков, 2, 33-22-11, null, null, null). Це зробити неможливо, тому що атрибут Н_ПРО (Номер проекту) входить до складу потенційного ключа, і, отже, не може містити null-значень.

Так само не можна вставити дані про проект, над яким поки що не працює жоден працівник.

Причина аномалії – зберігання в одному відношенні різнорідної інформації (і про співробітників, і про проекти, і про роботи за проектом).

Висновок – логічна модель даних неадекватна моделі предметної області. База даних, заснована на такій моделі, працюватиме неправильно.


Аномалії оновлення (UPDATE)

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

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

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


Аномалії видалення (DELETE)

При видаленні деяких даних може відбутися втрата іншої інформації. Наприклад, якщо закрити проект "Космос" і видалити всі рядки, в яких він зустрічається, то будуть втрачені всі дані про працівника Петрові. Якщо видалити співробітника Сидорова, то буде втрачена інформація про те, що у відділі номер 2 знаходиться телефон 33-22-11. Якщо за проектом тимчасово припинені роботи, то при видаленні даних про роботи з цього проекту будуть видалені і дані про сам проект (найменування проекту). При цьому якщо був співробітник, який працював тільки над цим проектом, то будуть втрачені і дані про це співробітника.

Причина аномалії – зберігання в одному відношенні різнорідної інформації (і про співробітників, і про проекти, і про роботи за проектом).

Висновок – логічна модель даних неадекватна моделі предметної області. База даних, заснована на такій моделі, працюватиме неправильно.


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

Ставлення СОТРУДНІКІ_ОТДЕЛИ_ПРОЕКТИ знаходиться в 1НФ, при цьому, як було показано вище, логічна модель даних не адекватна моделі предметної області. Таким чином, першої нормальної форми недостатньо для правильного моделювання даних.


Визначення функціональної залежності

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

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

.

Безліч атрибутів називається детермінантом функціональної залежності, А безліч атрибутів називається залежною частиною.

Зауваження. Якщо атрибути складають потенційний ключ відносини , То будь-який атрибут відносини функціонально залежить від .

Приклад 1. У відношенні СОТРУДНІКІ_ОТДЕЛИ_ПРОЕКТИ можна навести такі приклади функціональних залежностей:

Залежність атрибутів від ключа відносини:

{Н_СОТР, Н_ПРО} ФАМ

{Н_СОТР, Н_ПРО} Н_ОТД

{Н_СОТР, Н_ПРО} ТЕЛ

{Н_СОТР, Н_ПРО} ПРОЕКТ

{Н_СОТР, Н_ПРО} Н_ЗАДАН

Залежність атрибутів, що характеризують співробітника від табельного номера співробітника:

Н_СОТР ФАМ

Н_СОТР Н_ОТД

Н_СОТР ТЕЛ

Залежність найменування проекту від номера проекту:

Н_ПРО ПРОЕКТ

Залежність номера телефону від номера відділу:

Н_ОТД ТЕЛ

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


Функціональні залежності відносин і математичне поняття функціональної залежності

Функціональна залежність атрибутів відносини нагадує поняття функціональної залежності в математиці. Але це не одне і те ж. Для порівняння нагадаємо математичне поняття функціональної залежності:

Визначення 2. Функціональна залежність (функція) – Це трійка об'єктів , Де

– Безліч (область визначення),

– Безліч (безліч значень),

– Правило, згідно з яким кожному елементу ставиться у відповідність один і тільки один елемент (правило функціональної залежності).

Функціональна залежність звичайно позначається як або .

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

Функціональна залежність атрибутів відносини теж нагадує це визначення. Дійсно:

визначено коректно.

Відмінність від математичного поняття відношення полягає в тому, що, якщо розглядати математичне поняття функції, то для фіксованого значення відповідне значення функції завжди одне і те ж. Наприклад, якщо задана функція , То для значення відповідне значення завжди буде дорівнює 4. На противагу цьому у відносинах значення залежного атрибуту може приймати різні значення в різних станах бази даних. Наприклад, атрибут ФАМ функціонально залежить від атрибуту Н_СОТР. Припустимо, що зараз співробітник з табельною номером 1 має прізвище Іванов, тобто при значенні детермінанта рівного 1, значення залежного аргументу одно "Іванов". Але працівник може змінити прізвище, наприклад на "Сидоров". Тепер при тому ж значенні детермінанта, рівного 1, значення залежного аргументу одно "Сидоров".

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

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


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

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

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

Ставлення СОТРУДНІКІ_ОТДЕЛИ_ПРОЕКТИ не знаходиться в 2НФ, тому що є атрибути, які залежать від частини складного ключа:

Залежність атрибутів, що характеризують співробітника від табельного номера співробітника є залежністю від частини складного ключа:

Н_СОТР ФАМ

Н_СОТР Н_ОТД

Н_СОТР ТЕЛ

Залежність найменування проекту від номера проекту є залежністю від частини складного ключа:

Н_ПРО ПРОЕКТ

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

Ставлення СОТРУДНІКІ_ОТДЕЛИ_ПРОЕКТИ декомпозіруем на три відносини – СОТРУДНІКІ_ОТДЕЛИ, ПРОЕКТИ, ЗАВДАННЯ.

Ставлення СОТРУДНІКІ_ОТДЕЛИ ( Н_СОТР , ФАМ, Н_ОТД, ТЕЛ):

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

Залежність атрибутів, що характеризують співробітника від табельного номера співробітника:

Н_СОТР ФАМ

Н_СОТР Н_ОТД

Н_СОТР ТЕЛ

Залежність номера телефону від номера відділу:

Н_ОТД ТЕЛ























Н_СОТР


ФАМ


Н_ОТД


ТЕЛ

1   Іванов 1 11-22-33
2   Петров 1 11-22-33
3   Сидоров 2 33-22-11

Таблиця 2. Ставлення СОТРУДНІКІ_ОТДЕЛИ

Ставлення ПРОЕКТИ ( Н_ПРО , ПРОЕКТ):

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

Н_ПРО ПРОЕКТ












Н_ПРО


ПРОЕКТ

1   Космос
2   Клімат

Таблиця 3. Ставлення ПРОЕКТИ

Ставлення ЗАВДАННЯ ( Н_СОТР , Н_ПРО , Н_ЗАДАН):

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

{Н_СОТР, Н_ПРО} Н_ЗАДАН



























Н_СОТР


Н_ПРО


Н_ЗАДАН

1   1   1
1   2   1
2   1   2
3   1   3
3   2   2

Таблиця 4. Відносини ЗАВДАННЯ


Аналіз декомпонований відносин

Відносини, отримані в результаті декомпозиції, знаходяться в 2НФ. Справді, відносини СОТРУДНІКІ_ОТДЕЛИ і ПРОЕКТИ мають прості ключі, отже автоматично знаходяться в 2НФ, ставлення ЗАВДАННЯ має складний ключ, але єдиний неключових атрибут Н_ЗАДАН функціонально залежить від всього ключа {Н_СОТР, Н_ПРО}.

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

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

Якщо за проектом тимчасово припинені роботи, але потрібно, щоб сам проект зберігся, то для цього проекту видаляються відповідні кортежі відносно ЗАВДАННЯ, А дані про сам проект і дані про співробітників, які брали участь у проекті, залишаються у відносинах ПРОЕКТИ і СОТРУДНІКІ_ОТДЕЛИ.

Тим не менше, частина аномалій дозволити не вдалося.


Решта аномалії вставки (INSERT)

У відношення СОТРУДНІКІ_ОТДЕЛИ не можна вставити кортеж (4, Пушніков, 1, 33-22-11), тому що при цьому вийде, що два співробітники з 1-го відділу (Іванов і Пушніков) мають різні номери телефонів, а це суперечить моделі предметної області. У цій ситуації можна запропонувати два рішення, в залежності від того, що реально відбулося в предметної області. Інший номер телефону може бути введений з двох причин – помилково людини, що вводить дані про новому співробітнику, або тому що номер у відділі дійсно змінився. Тоді можна написати тригер, який при вставці запису про співробітника перевіряє, чи збігається телефон з вже наявним телефоном в іншого співробітника цього ж відділу. Якщо номери відрізняються, то система повинна задати питання, чи залишити старий номер у відділі або замінити його новим. Якщо потрібно залишити старий номер (новий номер введений помилково), то кортеж з даними про новому співробітнику буде вставлений, але номер телефону буде у нього буде той, який вже є в відділі (у даному випадку, 11-22-33). Якщо ж номер у відділі дійсно змінився, то кортеж буде вставлений з новим номером, і одночасно будуть змінені номери телефонів у всіх співробітників цього ж відділу. І в тому і в іншому випадку не обійтися без розробки громіздкого тригера.

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

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


Решта аномалії оновлення (UPDATE)

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

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

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


Решта аномалії видалення (DELETE)

При видаленні деяких даних як і раніше може відбутися втрата іншої інформації. Наприклад, якщо видалити співробітника Сидорова, то буде втрачена інформація про те, що у відділі номер 2 знаходиться телефон 33-22-11.

Причина аномалії – зберігання в одному відношенні різнорідної інформації (і про співробітників, і про відділи).

Висновок – логічна модель даних неадекватна моделі предметної області. База даних, заснована на такій моделі, працюватиме неправильно.

Зауважимо, що при переході до другої нормальної форми відносини стали майже адекватними предметної області. Залишилися також труднощі в розробці бази даних, пов'язані з необхідністю написання тригерів, підтримують цілісність бази даних. Ці труднощі тепер пов'язані тільки з одним відношенням СОТРУДНІКІ_ОТДЕЛИ.


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

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

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

Ставлення СОТРУДНІКІ_ОТДЕЛИ не знаходиться в 3НФ, тому що є функціональна залежність неключових атрибутів (залежність номера телефону від номера відділу):

Н_ОТД ТЕЛ

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

Ставлення СОТРУДНІКІ_ОТДЕЛИ декомпозіруем на два відношення – СПІВРОБІТНИКИ, ВІДДІЛИ.

Ставлення СПІВРОБІТНИКИ ( Н_СОТР , ФАМ, Н_ОТД):

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

Залежність атрибутів, що характеризують співробітника від табельного номера співробітника:

Н_СОТР ФАМ

Н_СОТР Н_ОТД

Н_СОТР ТЕЛ



















Н_СОТР


ФАМ


Н_ОТД

1   Іванов 1
2   Петров 1
3   Сидоров 2

Таблиця 5. Ставлення СПІВРОБІТНИКИ

Ставлення ВІДДІЛИ ( Н_ОТД , ТЕЛ):

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

Залежність номера телефону від номера відділу:

Н_ОТД ТЕЛ












Н_ОТД


ТЕЛ

1   11-22-33
2   33-22-11

Таблиця 6. Ставлення ВІДДІЛИ

Звернемо увагу на те, що атрибут Н_ОТД, Не був ключовим у відношенні СОТРУДНІКІ_ОТДЕЛИ, Стає потенційним ключем у відношенні ВІДДІЛИ. Саме за рахунок цього усувається надмірність, пов'язана з багаторазовим зберіганням одних і тих самих номерів телефонів.

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


Алгоритм нормалізації (приведення до 3НФ)

Отже, алгоритм нормалізації (тобто алгоритм приведення відносин до 3НФ) описується наступним чином.

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

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

Початкове відношення: .

Ключ: – Складний.

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

– Залежність всіх атрибутів від ключа відношення.

– Залежність деяких атрибутів від частини складного ключа.

Декомпонований відносини:

– Залишок від початкового відношення. Ключ .

– Атрибути, винесені з вихідного відносини разом з частиною складного ключа. Ключ .

Крок 3 (Приведення до 3НФ). Якщо в деяких відносинах виявлена залежність деяких неключових атрибутів інших неключових атрибутів, то проводимо декомпозицію цих відносин наступним чином: ті неключові атрибути, які залежать інших неключових атрибутів виносяться в окреме відношення. У новому відношенні ключем стає детермінант функціональної залежності:

Початкове відношення: .

Ключ: .

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

– Залежність всіх атрибутів від ключа відношення.

– Залежність деяких неключових атрибутів інших неключових атрибутів.

Декомпонований відносини:

– Залишок від початкового відношення. Ключ .

– Атрибути, винесені з вихідного відносини разом з детермінантом функціональної залежності. Ключ .

Зауваження. На практиці, при створенні логічної моделі даних, як правило, не йдуть прямо до наведеного алгоритму нормалізації. Досвідчені розробники зазвичай відразу будують відносини в 3НФ. Крім того, основним засобом розробки логічних моделей даних є різні варіанти ER-діаграм. Особливість цих діаграм в тому, що вони відразу дозволяють створювати відносини в 3НФ. Тим не менш, наведений алгоритм важливий з двох причин. По-перше, цей алгоритм показує, які проблеми виникають при розробці слабо нормалізованих відносин. По-друге, як правило, модель предметної області ніколи не буває правильно розроблена з першого кроку. Експерти предметної області можуть забути про що-небудь згадати, розробник може неправильно зрозуміти експерта, під час розробки можуть змінитися правила, прийняті в предметній області, і т.д. Все це може призвести до появи нових залежностей, які були відсутні в первинній моделі предметної області. Тут як раз і необхідно використовувати алгоритм нормалізації хоча б для того, щоб переконатися, що відносини залишилися в 3НФ і логічна модель не погіршилася.


Аналіз критеріїв для нормалізованих і ненормалізованих моделей даних

Порівняння нормалізованих і ненормалізованих моделей

Зберемо воєдино результати аналізу критеріїв, за якими ми хотіли оцінити вплив логічного моделювання даних на якість фізичних моделей даних і продуктивність бази даних:









Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*



Критерій


Відносини слабо нормалізовані
(1НФ, 2НФ)


Відносини сильно нормалізовані
(3НФ)

Адекватність бази даних предметної області