Цілісність посилань (referencial integrity)

Розглянемо найпопулярніший у базах даних зв’язок "один-до-багатьох" (рис. 1.8).

Таблиця “Товари”

Таблиця “Відпуск товарів”

Рис. 1.8 Зв’язані таблиці БД

Зауважимо, що дочірня та батьківська таблиці пов’язані між собою за спільним полем "Товар". Назвемо це поле полем зв’язку.

Можливими є два види змін, які призведуть до втрати зв’язків між записами у батьківській і дочірній таблицях:

• зміна значення поля зв’язку в запису батьківської таблиці без зміни значень полів зв’язку у відповідних записах дочірньої таблиці;

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

Розглянемо перший випадок. На рис. 1.9 показано зміну значення поля "Товар" з "Цукор" на "Рафінад" в таблиці "Товари". В таблиці "Відпуск товарів" значення поля зв’язку "Цукор" не змінилось. В результаті:

- в дочірній таблиці "Відпуск товарів" для товара "Рафінад" (таблиця "Товари") немає відомостей про його відпуск зі складу;

- деякі записи таблиці "Відпуск товаров" містять відомості про відпуск товару ("Цукор"), про який немає інформації в таблиці "Товари".

Таблиця “Товари”

Таблиця “Відпуск товарів”

Рис. 1.9 Порушення цілісності БД: записи з товаром “Цукор” (таблиця “Відпуск товарів”) не мають батьківського запису

Розглянемо другий випадок. Нехай в одному записі таблиці "Відпуск товарів" значення поля зв’язку "Цукор" змінилось на "Рафінад" (рис. 1.10). В результаті:

- в дочірній таблиці "Відпуск товарів" недостовірні відомості про відпуск зі складу товару "Цукор" (таблиця "Товари");

- один із записів таблиці "Відпуск товаров" містить дані про відпуск товару ("Рафінад"), відомості про який відсутні у таблиці "Товари".

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

Таблиця “Товари”

Таблиця “Відпуск товарів”

Рис. 1.10 Порушення цілісності БД: запис з товаром “Рафінад” (таблиця “Відпуск товарів”) не мають батьківського запису

Щоб попередити втрату цілісності посилань, використовують механізм каскадних змін. Він передбачає такі дії:

• при зміні поля зв’язку в запису батьківської таблиці, слід синхронно змінити значення полів зв’язку у відповідних записах дочірньої таблиці;

• при вилученні запису у батьківській таблиці, слід вилучити відповідні записи в дочірній таблиці.

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

Існує інший різновид каскадного вилучення: при вилученні батьківського запису в записах дочірніх таблиць значення полів зв’язку онулюються. Цей різновид застосовується рідко.

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

Індекси.

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

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

Наприклад, нехай є таблиця (рис. 1.11.)

Рис. 1.11. Фізична структура таблиці

З логічної точки зору індекси мають такий вигляд (рис. 1.12):

Рис. 1 12 Логічна структура індексів

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

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

- пошук йде по індексу, а не по самій таблиці;

- доступ починається безпосередньо з першого рядка, що задовільняє умові запиту або його частині;

- рядки в індексі, починаючи з першого знайденого запису, переглядаються послідовно.

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

Поняття транзакцій

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

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

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

Товари (Товар, Од. Виміру, Ціна за од.))

Покупці(Покупець, Місто, Адреса))

Накладні(Номер накладної, Дата, Покупець)

Продаж товарів(Товар, Номер накладної, Кількість)

Статистика по товару (Товар, Всього продано) – відомості про сумарний продаж кожного товару зі складу з початку року

Статистика по покупцю (Покупець, Всього продано) – відомості про сумарний продаж товарів кожному покупцеві з початку року

Тоді транзакція по додаванню в БД відомостей про продаж товару зі складу буде складатись з наступних операцій:

• додавання запису в таблицю "Продаж товарів";

• пошук запису по даному товару в таблиці "Статистика по товару" і збільшення значения поля "Всього продано" на значення "Кількість"; якщо запис по такому товару в таблиці "Статистика по товару" відсутній, його потрібно додати;

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

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

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