Реляційна модель і структура таблиць

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

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

Цілі лекції

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

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

Передумови

Потрібно розуміти поняття бази даних, СКБД, предметної області, сутності та атрибута ER-моделі. SQL у цій лекції не потрібен.

1. Від ER-моделі до реляційного подання

На етапі концептуального проєктування ми описуємо сутності предметної області. Наприклад, у системі коледжу сутність Студент має ім'я, номер студентського квитка, електронну адресу та належить до групи.

У реляційній моделі цей опис можна подати схемою:

STUDENT(student_id, student_card_no, full_name, email, group_code)

Один поточний стан даних має такий табличний вигляд:

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

2. Відношення, кортеж, атрибут і домен

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

Відношення - це множина кортежів, побудованих за однією схемою. Відношення можна позначити як STUDENT.

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

Кортеж і рядок

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

Кортеж про Анну можна записати так:

(student_id: 101,
 student_card_no: КВ-2026-001,
 full_name: Бондар Анна,
 email: a.bondar@college.edu,
 group_code: ІПЗ-41)

Позиція цього рядка на екрані не є частиною факту. Якщо СКБД покаже його останнім замість першого, сам кортеж не зміниться.

Атрибут і стовпець

Атрибут - названа характеристика, що входить до схеми відношення. У таблиці атрибут подано стовпцем. Наприклад, email є атрибутом відношення STUDENT, а конкретне значення a.bondar@college.edu належить одному кортежу.

Назва атрибута описує роль значення. Стовпець із назвою code без контексту неоднозначний, а group_code чіткіше повідомляє зміст.

Домен

Домен - множина допустимих атомарних значень атрибута з визначеним змістом. Домен - це не лише технічний тип.

Для прикладу можна задати:

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

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

3. Схема і стан відношення

Схема відношення описує його назву, атрибути та відповідні домени. Спрощений запис:

STUDENT(
  student_id: StudentId,
  student_card_no: StudentCardNumber,
  full_name: PersonName,
  email: CollegeEmail,
  group_code: GroupCode
)

Схема змінюється відносно рідко. Наприклад, додавання нового атрибута admission_year змінює схему.

Стан відношення - конкретна множина кортежів у певний момент. Реєстрація нового студента змінює стан, але не обов'язково схему.

Ця різниця важлива:

• «у STUDENT є атрибут email» - твердження про схему;
• «у STUDENT зараз 480 студентів» - твердження про стан;
• «додано кортеж нового студента» - зміна стану;
• «додано атрибут phone» - зміна схеми.

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

4. Ступінь і потужність відношення

Дві кількісні характеристики відповідають на різні запитання.

Ступінь відношення - кількість його атрибутів. Інша назва - арність.

У схемі:

STUDENT(student_id, student_card_no, full_name, email, group_code)

п'ять атрибутів, тому ступінь STUDENT дорівнює 5.

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

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

5. Властивості реляційного відношення

Формальне відношення має такі властивості:

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

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

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

6. Навіщо потрібні ключі

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

Суперключ - набір одного або кількох атрибутів, значення якого однозначно ідентифікує кортеж.

Якщо student_id унікальний, то {student_id} є суперключем. Набір {student_id, full_name} також унікальний, але full_name у ньому зайвий: достатньо student_id.

Потенційний ключ (candidate key) - мінімальний суперключ. Він має дві обов'язкові властивості:

• унікальність: два різні кортежі не можуть мати однакове значення ключа;
• мінімальність: якщо вилучити будь-який атрибут ключа, однозначна ідентифікація зникне.

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

7. Первинний та альтернативні ключі

Якщо відношення має кілька потенційних ключів, проєктувальник вибирає один із них як первинний ключ (primary key). Інші потенційні ключі стають альтернативними ключами (alternate keys).

Для STUDENT можна прийняти:

Первинний ключ: student_id
Альтернативний ключ: student_card_no

Первинний ключ не стає «більш унікальним» за альтернативний. Обидва потенційні ключі однозначно ідентифікують кортеж. Первинний ключ лише виконує роль головного обраного ідентифікатора.

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

8. Простий і складений ключ

Простий ключ складається з одного атрибута. student_id є простим ключем.

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

Розглянемо реєстрацію студентів на дисципліни:

ENROLLMENT(student_id, course_code, semester, enrolled_at)

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

(student_id, course_code, semester)

Жодна частина окремо не ідентифікує кортеж:

• student_id повторюється для різних дисциплін;
• course_code повторюється для різних студентів;
• semester повторюється для багатьох реєстрацій;
• навіть пара (student_id, course_code) може повторитися в іншому семестрі.

Отже, усі три атрибути потрібні, а ключ є мінімальним і складеним.

9. Природний і сурогатний ключ

Ця класифікація стосується походження ключа.

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

Сурогатний ключ - штучний ідентифікатор, створений спеціально для бази або інформаційної системи. Наприклад, student_id = 101 не описує властивість студента, а стабільно позначає його запис.

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

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

10. Первинний і зовнішній ключ - різні ролі

У STUDENT атрибут student_id ідентифікує кортеж самого відношення. Це роль первинного ключа.

Розглянемо інше відношення:

ACADEMIC_GROUP(group_code, curator_name)

Якщо ACADEMIC_GROUP.group_code є його первинним ключем, то STUDENT.group_code може бути зовнішнім ключем (foreign key), який посилається на групу.

STUDENT.group_code -> ACADEMIC_GROUP.group_code

Отже:

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

Зовнішній ключ не зобов'язаний бути унікальним: багато студентів можуть мати group_code = ІПЗ-41. Детальні правила посилальної цілісності, обов'язковість посилань і реалізацію зв'язків розглянемо в лекції 6.

11. Мінімальний вступ до реляційної алгебри

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

Для першого знайомства достатньо двох операцій:

Вибірка (селекція) залишає кортежі, що відповідають умові. Наприклад, із STUDENT можна отримати студентів групи ІПЗ-41:

σ group_code = 'ІПЗ-41' (STUDENT)

Результат має ті самі атрибути, але лише вибрані кортежі.

Проєкція залишає зазначені атрибути:

π full_name, email (STUDENT)

Результат містить лише ПІБ та електронні адреси. У формальній реляційній алгебрі результат знову є множиною, тому дублікати кортежів не зберігаються.

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

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

Завдання 1. Назвіть елементи відношення

Для відношення:

COURSE(course_code, title, credits)

і кортежу:

(DB101, Бази даних, 5)

1. Назвіть відношення, його атрибути та наведений кортеж.
2. Запропонуйте домен для кожного атрибута.
3. Поясніть, що зміниться після додавання нової дисципліни: схема чи стан.

Завдання 2. Перевірте структуру і властивості

Наведено таблицю:

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

Завдання 3. Обґрунтуйте ключі

Коледж зберігає:

STUDENT(student_id, student_card_no, full_name, email, group_code)
ENROLLMENT(student_id, course_code, semester, enrolled_at)

Відомі правила:

• student_id генерує система й він унікальний;
• student_card_no є офіційним унікальним номером;
• ПІБ можуть збігатися;
• електронна адреса може змінюватися;
• студент реєструється на дисципліну не більше одного разу в одному семестрі.

1. Назвіть потенційні ключі STUDENT.
2. Виберіть первинний ключ і назвіть альтернативний. Поясніть вибір.
3. Класифікуйте ці ключі як природні або сурогатні.
4. Визначте складений потенційний ключ ENROLLMENT.
5. Назвіть атрибут STUDENT, який може бути зовнішнім ключем, і поясніть його роль одним реченням.

Підсумок

• Відношення є множиною кортежів спільної схеми; таблиця є практичним способом його подання.
• Кортеж відповідає рядку, атрибут - стовпцю, а домен визначає допустимі значення та їхній зміст.
• Схема описує будову відношення, а стан - його кортежі в конкретний момент.
• Ступінь дорівнює кількості атрибутів, потужність - кількості кортежів поточного стану.
• У формальному відношенні немає дублікатів і значущого порядку кортежів; значення відповідають доменам.
• Потенційний ключ є мінімальним унікальним набором атрибутів. Один потенційний ключ обирають первинним, інші стають альтернативними.
• Ключ може бути простим або складеним, природним або сурогатним; ці класифікації описують різні властивості.
• Зовнішній ключ посилається на інше відношення і не виконує автоматично роль первинного ключа.
• Реляційна алгебра формує нові відношення; вибірка обирає кортежі, а проєкція - атрибути.