Приклад створення логічної моделі бази даних

 

У прикладі (п.5.4) розроблено концептуальний проект бази даних для предметної області ВИЩИЙ НАВЧАЛЬНИЙ ЗАКЛАД (ВНЗ). ER-діаграма відображає всі бізнес правила, які в свою чергу визначають сутності, атрибути, зв'язки і т.д.

Наступним етапом проектування бази даних є створення логічної моделі бази даних на основі створеної ER-моделі (рис. 5.21). Створення логічної моделі бази даних виконується шляхом застосування правил перетворення (гл. 6.) ER-діаграми в логічну модель (рис. 6.26).

 

 

Рис. 6.26. Логічна модель бази даних для предметної області ВИЩИЙ НАВЧАЛЬНИЙ ЗАКЛАД

 

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

 

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

 

(див. рис. 6.1).

 

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

 

Правила перетворення ER-діаграми в логічну модель наведені в дод. 2.

 

Тема 7. Нормалізація

1. Постановка задачі

2. Нормальні форми

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

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

2.3. Третя нормальна форма

2.4. Нормальна форма Бойса-Кодда

2.5. Четверта нормальна форма

2.6. П'ята нормальна форма

3. Денормалізація

Постановка задачі

 

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

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

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

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

Приклад. Розглянемо відношення Студент (табл. 7.1).

 

		Студент		Таблиця 7.1
Номер
залікової	Прізвище		Група	Факультет		Декан
книжки
	Бойко		ІТП-31	АІТ		Барков
	Лемешко		ІУСТ-22	АІТ		Барков
	Шевченко		ІТП-31	АІТ		Барков
	Петренко		БМО-32	АІТ		Барков
	Грицюк		ТБ-21	БТ		Тимчук

 

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

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

Аномалія вилучення виникає при спробі вилучити дані простудента, який в групі поки ще один, наприклад Лемешко. В цьому випадку зникне інформація про групу ІУСТ-22.

Виконання декомпозиції наведеного відношення дозволяє позбутися вищеозначених аномалій (табл. 7.2...7.4).

Таблиця 7.2

Студент

Номер
залікової	Прізвище	Група
книжки
	Бойко	ІТП-31
	Лемешко	ІУСТ-22
	Шевченко	ІТП-31
	Петренко	БМО-32
	Грицюк	ТБ-21

 

Таблиця 7.3

 

Група

		Група		Факультет
		ІТП-31		АІТ
		ІУСТ-22		АІТ
		БМО-32		АІТ
		ТБ-21		БТ
					Таблиця 7.4
		Факультет
		Факультет		Декан
		АІТ	Барков
		БТ	Тимчук

 

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

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

Атрибут B функціонально залежить від A, якщо кожному значенню A відповідає в точності одне значення B. Математичний запис функціональної залежності (ФЗ): A → B

Приклад. Функціональні залежності:

Студент → Група; Група → Факультет; Викладач, Студент, Дисципліна → Оцінка.

Якщо існує ФЗ A → B, то це означає, що у всіх кортежах з однаковим значенням атрибуту A атрибут B буде мати також одне й те ж значення. A і B можуть складатися з декількох атрибутів.

 

Нормальні форми

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

Відношення знаходиться в1НФ тоді і тільки тоді, коли всі його атрибути є атомарними.

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

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

Таблиця 7.5

Дата народження

 

Прізвище	Дата народження
Бойко		15 лютого 1991
Прізвище	Дата і місяць		Рік
Бойко	15 лютого
Прізвище	День		Місяць		Рік
Бойко			лютий

 

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

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

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

Приклад. Розглянемо відношення Студент (табл. 7.6).

 

Таблиця 7.6

Студент

 

Номер залікової	Прізвище	Група	Дисципліна	Оцінка
книжки
	Бойко	ІТП-31	Бази даних

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

№ залік.кн., Дисципліна → Прізвище, Група, Оцінка

№ залік.кн. → Прізвище, Група

Для приведення даного відношення до 2НФ необхідно розбити його на проекції, при цьому повинна бути виконана умова відновлення вихідного відношення без втрат. Проекції мають такий вигляд (табл. 7.7...7.8).

 

Таблиця 7.7

Студент

 

Номер залікової		Дисципліна	Оцінка
книжки
		Бази даних
								Таблиця 7.8
	Група
Номер залікової		Прізвище		Група
книжки
		Бойко		ІТП-31

 

Відсутність втрат при декомпозиції відношення R (x, y, z) на відношення R₁ (x, y) і R₂ (x, z) виконується, якщо від спільного атрибуту двох отриманих відношень (x) залежить хоча б один атрибут з двох, що залишилися (y або z), тобто якщо виконується (x → y) або (x → z).

Третя нормальна форма

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

Атрибут C транзитивно залежить від атрибуту A, якщо для атрибутів A, B, C виконуються такі умови A → B і B → C, але зворотня залежність відсутня.

Приклад. Розглянемо відношення Студент (табл. 7.9).

 

	Студент		Таблиця 7.9
Номер залікової	Прізвище	Група	Факультет
книжки
	Бойко	ІТП-31	АІТ

 

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

 

№ залік.кн. → Прізвище, Група, Факультет Група → Факультет

 

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

	Студент	Таблиця 7.10
Номер залікової		Прізвище	Група
книжки
		Бойко	ІТП-31
	Група	Таблиця 7.11
	Група	Факультет
	ІТП-31		АІТ

 

Нормальна форма Бойса-Кодда

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

Приклад. Розглянемо відношення Спеціальність (табл. 7.12).

 

Спеціальність	Таблиця 7.12
Спеціальність	Дисципліна	Викладач
ІТП	Бази даних	Барко
ІУСТ	Бази даних	Шевченко

 

Припустимо, що на кожній спеціальності певну дисцип-ліну може викладати тільки один викладач і кожен викладач викладає тільки одну дисципліну. У цьому випадку мають місце такі залежності:

Спеціальність, Дисципліна → Викладач Викладач → Дисципліна

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

 

Для того щоби позбутися аномалій необхідно виконати декомпозицію відношення (табл. 7.13, 7.14).

Таблиця 7.13

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

	Спеціальність	Дисципліна
	ІТП	Бази даних
	ІУСТ	Бази даних

Таблиця 7.14

Дисципліна

Викладач	Дисципліна
Барко	Бази даних
Шевченко	Бази даних

 

Четверта нормальна форма

Відношення знаходиться в

4НФ тоді і тільки тоді, коли у випадку існування багатозначної залежності A →→ B всі інші атрибути відношення функціонально залежать від A.

У відношенні R (A, B, C) існує багатозначна залежністьA →→ B в тому і тільки в тому випадку,коли множина значень B,що відповідає парі значень A і C залежить тільки від A і не залежить від C.

Відношення R (A, B, C) можна розбити без втрат інформації на відношення R₁ (A, B) і R₂ (A, C) в тому і тільки в тому випадку, якщо існують багатозначні залежності A →→ B і A →→ C.

Приклад. Розглянемо відношення Кафедра (табл.7.15).

 

Таблиця 7.15

Кафедра

Кафедра	Викладач	Група
ІТ	Барко	ІТП-31
ІТ	Барко	ІТП-32
ІТ	Шевченко	ІТП-31
ІТ	Шевченко	ІТП-32

 

У даному відношенні існують дві багатозначні залежності:

Кафедра →→ Викладач Кафедра →→ Група

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

Для того, щоби звести відношення до 4НФ, необхідно виконати його декомпозицію (табл. 7.16, 7.17).

Таблиця 7.16

Кафедра

	Кафедра	Викладач
	ІТ	Барко
	ІТ	Шевченко

 

Таблиця 7.17

Група

	Кафедра	Група
	ІТ	ІТП-31
	ІТ	ІТП-32

 

П'ята нормальна форма

Відношення знаходиться в5НФтоді і тільки тоді, коли будь-яка залежність з'єднання у відношенні виходить з існування деякого можливого ключа у відношенні.

Відношення R (X, Y,…, Z) задовольняє залежності з'єднання (X, Y,…, Z)тоді і тільки тоді,коли R відновлюється безвтрат інформації шляхом з'єднання своїх проекцій на X, Y,…, Z. Залежність з'єднання є узагальненням функціональної і багатозначної залежностей.

Приклад. Розглянемо відношення Заняття:

Заняття (Студент, Викладач, Дисципліна)

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

Утворимо такі складені атрибути відношення:

СВ (Студент, Викладач) СД (Студент, Дисципліна) ВД (Викладач, Дисципліна).

Якщо відношення R спроектувати на складені атрибути СВ, СД, ВД, то з'єднання цих проекцій дасть вихідне відношення. Це означає, що у відношенні Заняття існувала залежність з'єднання. Результатом декомпозиції відношення Заняття буде отримання таких відношень: R₁ (Студент,

Викладач), R₂ (Студент, Дисципліна), R₃ (Викладач, Дисципліна).

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

Результати зведення до нормальних форм наведені в табл. 7.18.

Таблиця 7.18