4. Реляційні бази даних

4. 1. Нормалізація баз даних 2

Приведення до нормальних форм 6

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

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

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

4. 2. Ключі та індекси 12

4. 3. Зв’язок між таблицями 14

4. 4. Методи і способи доступу до даних 16

Контрольні запитання 17

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

• будь-якому стовпцю таблиці присвоюється унікальне для цієї таблиці ім'я;

• стовпці таблиці упорядковуються зліва направо, тобто стовпець 1, стовпець 2, ..., стовпець n. З математичної точки зору це твердження некоректне, тому що в реляційній системі стовпці не упорядковані. Однак з погляду користувача, порядок, у якому визначені імена стовпців, стає порядком, у якому повинні вводитися в них дані, якщо не випереджати при введенні кожне значення ім'ям відповідного стовпця;

• рядки таблиці (записи) не упорядковані (їх послідовність визначається лише послідовністю введення в таблицю);

• у полі, на перетині рядка і стовпця, будь-якої таблиці завжди може бути тільки одне значення даних і ніколи не повинно бути декількох значень (правда, це значення може бути досить об'ємним);

• усім рядкам таблиці відповідає та сама кількість стовпців, хоча у визначених стовпцях будь-який рядок може містити порожні значення (NULL-значення);

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

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

Реляційні системи беруть свій початок у математичній теорії множин. Вони були запропоновані наприкінці 1968 року доктором Э.Ф.Коддом , який першим усвідомив, що можна використовувати математику для надання надійної основи і строгості в області керування базами даних.

Нечіткість багатьох термінів, які використовуються у сфері обробки даних, змусила Кодда відмовитися від них і придумати нові або дати більш точні визначення існуючим. Так, він не міг використовувати широко розповсюджений термін "запис", який у різних ситуаціях може означати екземпляр запису, або тип записів, запис у стилі Кобола (яка допускає групи, які повторюються), логічний запис або фізичний запис, збережений запис або віртуальний запис і т.д. Замість цього він використовував термін "кортеж довжини n" або просто "кортеж", якому дав точне визначення. У літературі можна докладно ознайомитися з термінологією реляційних баз даних, а тут ми будемо використовувати неформальні їх еквіваленти:

таблиця для відношення,

рядок або запис для кортежу,

стовпець або поле для атрибуту.

4. 1. Нормалізація баз даних

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

• швидкий доступ до даних;

• відсутність дублювання (повторення) даних;

• цілісність даних.

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

При проектуванні структур даних можна виділити три основних підходи [5].

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

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

3. Структурування інформації в процесі проведення системного аналізу на основі сукупності правил і рекомендацій.

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

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

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

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

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

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

Таблиця 4.1. Приклад не надлишкового дублювання даних

Співробітник	Телефон
Іванов П. Л.	123
Петров А. Ф.	123
Сидоров О. Е.	456
Кузнєцова В. А.	789
Васин И. Г.	123

Три співробітники мають однаковий номер телефону 123, що може бути наприклад, у випадку, коли вони знаходяться в одній кімнаті. У такий спосіб номер телефону в таблиці дублюється, однак для кожного співробітника цей номер є унікальним. У випадку видалення одного з дубльованих значень номера телефону (видалення відповідної рядка таблиці) буде загублена інформація про прізвище співробітника — Іванова П. Л., Петрова А. Ф. чи Васина И. Г., що є аномалією видалення.

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

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

Тепер приведемо приклад надлишкового дублювання даних. Список телефонів доповнений номерами кімнат, у яких знаходяться співробітники (табл. 4.2). При цьому номер телефону зазначений тільки для одного зі співробітників — в прикладі для Іванова П. Л., що знаходиться в списку першим. Замість номерів телефонів інших співробітників цієї кімнати поставлено прочерк. На практиці кодування прочерку залежить від особливостей конкретної таблиці, наприклад, можна позначати прочерк значенням Null.

Таблиця 4.2. Приклад надлишкового дублювання даних

Співробітник	Кімната	Телефон
Іванов П. Л.	17	123
Петров А. Ф.	17	-
Сидоров О. Е.	22	456
Кузнецова В. А.	8	789
Васин И. Г.	17	-

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

• при зчитуванні номера телефону співробітника виникає необхідність пошуку цього номера в іншому рядку таблиці (по номері кімнати);

• при запам'ятовуванні таблиці для кожного рядка виділяється однакова пам'ять поза залежністю від наявності чи відсутності прочерків;

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

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

Таблиця 4. 3а. Список співробітників і номерів кімнат

Співробітник	Кімната
Іванов П.Л.	17
Петров А.Ф.	17
Сидоров О.Е.	22
Кузнєцова В. А.	8
Васин И. Г.	17

Таблиця 4. 3б. Список номерів кімнат і номерів кімнат і телефонів

Кімната	Телефон
17	123
8	789
22	456

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

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

Приведення до нормальних форм

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

Виділяють наступну послідовність нормальних форм:

• перша нормальна форма;

• друга нормальна форма;

• третя нормальна форма;

• посилена третя нормальна форма, чи нормальна форма Бойса-Кодда;

• четверта нормальна форма;

• п'ята нормальна форма.

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

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

• дата матчу;

• команда господарів;

• команда гостей;

• гравець;

• ознака команди;

• час.

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

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

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

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

Приведемо вихідну таблицю до першої нормальної форми, для якої повинні виконуватися наступні умови:

• поля містять неподільну інформацію;

• у таблиці відсутні групи полів, які повторюються.

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

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

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

Таблиця 4.4. Результати здачі екзаменаційної сесії

Студент	Математика	Інформатика	Фізика	Історія	Англійська мова
Семенов Р. О.	4	4	4	-	4
Костина В. К.	4	3	3	-	5
Папаєв Д. Г.	5	5	-	5	-
Симонов П. С.	-	-	-	3	3

Друга і третя нормальні форми стосуються відношень між ключовими і неключовими полями.

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

До другої нормальної форми висуваються наступні вимоги:

• таблиця повинна задовольняти вимоги першої нормальної форми;

• будь-яке не ключове поле повинно однозначно ідентифікуватися ключовими полями.

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

Тоді структура таблиці набуде такого вигляду:

• код матчу (унікальний ключ);

• дата матчу;

• назва команди господарів;

• місто команди;

• прізвище тренера команди господарів;

• назва команди гостей;

• місто команди гостей;

• прізвище тренера команди гостей;

• гравець;

• ознака команди;

• час.

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

Поля таблиці матчів:

• код матчу (унікальний ключ);

• дата матчу;

• назва команди господарів;

• місто команди господарів;

• прізвище тренера команди господарів ;

• назва команди гостей;

• місто команди гостей;

• прізвище тренера команди гостей.

Поля таблиці голів:

• код голу (унікальний ключ);

• код матчу;

• гравець;

• ознака команди;

• час.

Таблиці, зв'язані по полю Код матчу, яке для таблиці матчу має унікальне значення. Щоб забезпечити унікальність записів таблиці голів, в неї введено ключове поле Код голу.

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

Вимогами третьої нормальної форми є наступні:

• повинна задовольняти вимоги другої нормальної форми;

• жодне із неключових полів не повинно однозначно ідентифікуватися значенням другого неключового поля (полів).

Приведення таблиці до третьої нормальної форми припускає виділення в окрему таблицю (таблиці) тих полів, які не залежать від ключа. У таблиці матчів такими є поля з прізвищами тренерів команд, які однозначно визначаються значеннями назви і міста команди. Передбачається, що протягом сезону тренер в команді не міняється, що на практиці часто не виконується, але не змінює суті питання. Розіб'ємо таблицю матчів на дві таблиці: одну з даними про матчі і другу — з даними про команди. Їх структура приводиться нижче:

Таблиця матчів:

• код матчу (унікальний ключ);

• дата матчу;

• код команди господарів;

• код команди гостей.

Таблиця команд:

• код команди (унікальний ключ);

• назва команди;

• місто;

• прізвище тренера.

Рис.8. Структура бази даних "Чемпіонат з футболу"

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

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

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

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