Цілісність і зв’язки в реляційній базі

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

Цілі лекції

Після опрацювання матеріалу ви зможете:

• розрізняти сутнісну, доменну та посилальну цілісність;
• пояснювати роль зовнішнього ключа та можливі дії зі зв’язаними рядками;
• переносити зв’язки 1:1, 1:N і M:N до реляційної схеми;
• пояснювати призначення асоціативної таблиці;
• розпізнавати основні операції реляційної алгебри за змістом задачі.

Передумови

Потрібно знати поняття відношення, кортеж, атрибут, домен, схема, первинний і зовнішній ключі, а також розуміти кардинальності 1:1, 1:N і M:N в ER-моделі. SQL-синтаксис для створення обмежень у цій лекції не потрібний.

Наскрізний приклад

Розглядатимемо фрагмент електронного журналу. Запис схеми STUDENT(student_id, full_name, group_id) означає назву відношення та його атрибути, а не SQL-команду.

GROUP(group_id, group_name)
STUDENT(student_id, full_name, email, group_id)
STUDENT_PROFILE(student_id, birth_date, phone)
COURSE(course_id, title)
ENROLLMENT(student_id, course_id, enrolled_on)
ASSIGNMENT(assignment_id, course_id, title)
SUBMISSION(student_id, assignment_id, grade)

Уявімо такі дані:

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

1. Що означає цілісність

Цілісність даних - це відповідність збережених даних установленим правилам моделі та предметної області. Вона не гарантує істинність кожного введеного факту. Наприклад, система може перевірити, що оцінка належить діапазону від 1 до 12, але не знає, чи викладач справедливо її виставив.

У реляційній моделі виділяють три основні складові цілісності.

1.1. Сутнісна цілісність

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

• унікальним у межах відношення;
• непорожнім, адже невідомий ключ не може надійно позначати конкретний рядок.

У STUDENT атрибут student_id є первинним ключем. Два студенти не можуть мати student_id = 101, а студент без значення student_id не повинен з’явитися у відношенні.

Ім’я не є надійною заміною ключа: дві різні людини можуть мати однакове ім’я, а ім’я конкретної людини може змінитися.

1.2. Доменна цілісність

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

Питання про порожнє значення також належить до правил домену: атрибут може бути обов’язковим або необов’язковим. Дозволене порожнє значення не означає помилки, якщо модель свідомо допускає «ще невідомо» або «не застосовується».

1.3. Посилальна цілісність

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

У нашому прикладі:

STUDENT.group_id -> GROUP.group_id

Студента з group_id = 7 можна зберегти, бо група 7 існує. Значення group_id = 99 порушить посилальну цілісність, якщо групи 99 немає. Такий запис називають осиротілим посиланням: він стверджує зв’язок із неіснуючим об’єктом.

Зовнішній ключ не зобов’язаний бути унікальним. У зв’язку 1:N багато студентів можуть мати однаковий group_id. Його завдання тут не розрізняти студентів, а вказувати на групу.

2. Зовнішній ключ і дії зі зв’язаними рядками

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

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

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

3. Перенесення зв’язків до реляційної схеми

ER-діаграма показує змістовий зв’язок. У реляційній схемі його реалізують ключами та окремими відношеннями.

3.1. Зв’язок 1:N

Одна група містить багато студентів, а кожен студент у цій моделі належить не більш ніж одній групі.

GROUP(group_id, group_name)
STUDENT(student_id, full_name, group_id)
                                 ^
                                 зовнішній ключ до GROUP

Для зв’язку 1:N зовнішній ключ розміщують на стороні N. Якби student_id зберігали у GROUP, одна клітинка мала б містити список студентів або довелося б дублювати групу. Обидва підходи спотворюють структуру.

Обов’язковість участі визначає, чи може STUDENT.group_id бути порожнім. Кардинальність і обов’язковість пов’язані, але це різні характеристики.

3.2. Зв’язок 1:1

Нехай кожен студент має не більш ніж один розширений профіль, і кожен профіль належить рівно одному студенту.

STUDENT(student_id, full_name, email, group_id)
STUDENT_PROFILE(student_id, birth_date, phone)

STUDENT_PROFILE.student_id одночасно може бути:

• первинним ключем профілю;
• зовнішнім ключем до STUDENT.student_id.

Посилання забезпечує існування студента, а унікальність не дозволяє створити два профілі для одного студента. Зовнішній ключ без унікальності реалізував би 1:N, а не 1:1.

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

3.3. Зв’язок M:N та асоціативна таблиця

Студент вивчає багато дисциплін, а дисципліну вивчає багато студентів. Один зовнішній ключ у STUDENT або COURSE не може подати обидві множини без списків у клітинках.

Зв’язок M:N перетворюють на два зв’язки 1:N через асоціативну таблицю:

STUDENT 1 --- N ENROLLMENT N --- 1 COURSE

ENROLLMENT(student_id, course_id, enrolled_on)

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

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

4. Реляційна алгебра: операції над відношеннями

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

4.1. Об’єднання, перетин і різниця

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

Нехай CLUB_A(student_id) і CLUB_B(student_id) містять учасників двох гуртків.

• Об’єднання CLUB_A ∪ CLUB_B повертає студентів, які є хоча б в одному гуртку.
• Перетин CLUB_A ∩ CLUB_B повертає студентів, які є в обох гуртках.
• Різниця CLUB_A − CLUB_B повертає студентів першого гуртка, яких немає в другому.

Різниця несиметрична: A − B зазвичай не дорівнює B − A.

4.2. Декартів добуток

Декартів добуток A × B утворює всі можливі пари кортежів з A і B. Якщо є 3 студенти й 2 дисципліни, добуток міститиме 6 комбінацій.

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

4.3. Селекція і проекція

Селекція σ вибирає кортежі, які задовольняють умову. Вона відповідає питанню «які рядки?».

σ group_id = 7 (STUDENT)

Результат містить студентів групи 7 та всі атрибути STUDENT.

Проекція π залишає вказані атрибути. Вона відповідає питанню «які стовпці?».

π full_name (STUDENT)

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

4.4. З’єднання

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

STUDENT ⋈ STUDENT.group_id = GROUP.group_id GROUP

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

4.5. Ділення

Ділення відповідає на запитання з квантором «усі». Нехай:

COMPLETION(student_id, assignment_id)
REQUIRED(assignment_id)

Тоді COMPLETION ÷ REQUIRED повертає студентів, які виконали кожну роботу з REQUIRED. Студент, який виконав багато робіт, але пропустив одну обов’язкову, до результату не потрапить.

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

5. Як поняття працюють разом

Розгляньмо запитання: «Які студенти групи ІПЗ-31 виконали всі обов’язкові роботи з баз даних?»

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

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

Практичні завдання

Завдання 1. Знайдіть порушення цілісності

Задано GROUP: групи 7 і 8. Для кожного запису визначте, чи порушено сутнісну, доменну або посилальну цілісність.

1. У STUDENT уже є student_id = 101, але додають іншого студента з таким самим ключем.
2. У SUBMISSION.grade додають значення 15, хоча домен становить цілі числа від 1 до 12.
3. Студенту задають group_id = 9, хоча такої групи немає.
4. Студенту залишають phone порожнім, а модель дозволяє не вказувати телефон.

Для кожного рішення назвіть правило, а не тільки слово «помилка».

Завдання 2. Перенесіть зв’язки до схеми

Доповніть схему ключами та поясніть їх розміщення:

1. одна група має багато студентів, студент належить рівно одній групі;
2. студент може мати не більш ніж одну картку доступу, картка належить рівно одному студенту;
3. студент відвідує багато гуртків, гурток має багато студентів; для участі потрібно зберігати дату вступу.

Запишіть схеми у формі TABLE(attribute, ...), позначте первинні та зовнішні ключі. Для 1:1 не забудьте правило унікальності, для M:N створіть асоціативну таблицю.

Завдання 3. Побудуйте план отримання результату

Є відношення:

STUDENT(student_id, full_name, group_id)
GROUP(group_id, group_name)
COMPLETION(student_id, assignment_id)
REQUIRED(assignment_id)

Поясніть словами послідовність операцій для запитання: «Які студенти групи ІПЗ-31 виконали всі обов’язкові роботи?»

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

Підсумок

• Сутнісна цілісність вимагає унікального й непорожнього первинного ключа кожного кортежу.
• Доменна цілісність обмежує значення атрибута його допустимим типом, діапазоном, форматом та правилами порожніх значень.
• Посилальна цілісність не допускає зовнішніх ключів, що посилаються на неіснуючі рядки.
• Дії за зовнішнім ключем визначають, чи заборонити зміну, каскадувати її або змінити посилання; вибір залежить від предметної області.
• Зв’язок 1:N реалізують зовнішнім ключем на стороні N; для 1:1 потрібна додаткова унікальність; M:N потребує асоціативної таблиці.
• Об’єднання, перетин і різниця працюють із сумісними відношеннями; декартів добуток утворює всі пари.
• Селекція вибирає рядки, проекція - стовпці, з’єднання поєднує пов’язані рядки, а ділення виражає вимогу «для всіх».