Що ви опануєте
Навчитеся виділяти сутності, пояснювати одиницю рядка та перетворювати правила предметної області на схему PostgreSQL. Розглянемо бібліотеку навчальних ресурсів: студент знаходить видання, бібліотекар реєструє примірник, система фіксує видачу. Чітка модель допомагає відповісти, які факти зберігаються, як вони пов’язані та хто відповідає за їхню коректність.
Основні поняття
Реляційна модель представляє дані у відношеннях. У SQL їх реалізують таблиці: стовпці описують атрибути, рядки — конкретні записи. Схема задає структуру та правила; екземпляр бази — фактичні дані в певний момент. SQL-таблиця може містити повторювані рядки, тому унікальність потрібно забезпечувати явними ключами.
Сутність — предметний об’єкт, який потрібно розрізняти: студент, видання, фізичний примірник. Атрибут — характеристика: назва, рік, інвентарний номер. Зв’язок показує взаємодію сутностей. Один примірник належить одному виданню; видання може мати багато примірників. Це зв’язок один-до-багатьох.
Одиниця рядка та ідентичність
Для таблиці видань один рядок означає конкретне видання з певною редакцією. Для примірників — фізичний об’єкт. Дві книги одного видання мають однакову назву, але різні інвентарні номери. Якщо записувати кількість у виданні й одночасно вести примірники, треба визначити, чи кількість є обчисленою характеристикою. Два незалежні джерела одного факту створюють ризик розбіжностей.
Первинний ключ однозначно визначає рядок. Природний ключ має предметне значення, наприклад інвентарний номер. Сурогатний ключ створюється системою, наприклад числовий id. Навіть за сурогатного ключа предметну унікальність зберігає окреме обмеження UNIQUE. Назва видання для цього не підходить: різні видання можуть мати однакові назви.
Три рівні моделювання
Концептуальна модель описує сутності й правила мовою предметної області. Логічна модель визначає таблиці, ключі та кратності. Фізична модель обирає типи PostgreSQL, індекси та спосіб зберігання. На концептуальному рівні питання «чи можливий примірник без видання?» важливіше за вибір integer або bigint. Відповідь визначить обов’язковість зв’язку.
Припустімо, кожен примірник має рівно одне видання. Тоді edition_id буде NOT NULL і зовнішнім ключем. Видання без примірників можливе: його могли лише замовити. Отже, нуль примірників є коректним станом. Ці правила потрібно погодити до введення даних, щоб схема підтримувала очікуваний робочий процес.
Виконуваний приклад PostgreSQL
У навчальній базі виконайте один раз:
CREATE SCHEMA lecture_model;
SET search_path TO lecture_model;
CREATE TABLE edition (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL CHECK (length(trim(title)) > 0),
published_year integer CHECK (published_year BETWEEN 1450 AND 2200)
);
CREATE TABLE copy (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
inventory_no text NOT NULL UNIQUE,
edition_id bigint NOT NULL REFERENCES edition(id),
CHECK (length(trim(inventory_no)) > 0)
);
INSERT INTO edition (title, published_year)
VALUES ('Практичний SQL', 2024);
INSERT INTO copy (inventory_no, edition_id)
SELECT 'LIB-001', id FROM edition WHERE title = 'Практичний SQL';
SELECT inventory_no, title
FROM copy JOIN edition ON edition.id = copy.edition_id;
Схема — простір імен для таблиць. Search_path дозволяє скорочувати назви в поточній сесії; в застосунку бажано явно визначати дозволені схеми. Identity генерує числовий ключ. REFERENCES перевіряє існування видання. JOIN поєднує пов’язані рядки для читання. Очікується LIB-001 та назва видання. Приклад припускає одне щойно створене видання; у реальному інтерфейсі використовуйте id, повернений INSERT … RETURNING.
NULL та відсутня інформація
NULL позначає відсутність значення. Рік невідомого видання можна залишити NULL; порожній текст назви не є невідомою назвою й відхиляється CHECK. SQL використовує тризначну логіку: порівняння NULL = 2024 дає unknown. Для перевірки відсутності пишуть IS NULL. WHERE залишає рядки, для яких умова true.
CHECK не відхиляє результат unknown. Тому перевірка діапазону року дозволяє NULL, а обов’язковість назви забезпечується окремим NOT NULL. Це пояснює, чому обмеження потрібно читати разом. Для кожного атрибута визначте, чи відсутність значення має зміст та як її покаже інтерфейс.
Практичний сценарій і перевірка моделі
Побудуйте словник даних: назва атрибута, визначення, одиниці, допустимі значення, джерело, обов’язковість. Для published_year зазначте календарний рік, для inventory_no — внутрішній код бібліотеки. Потім пройдіть сценарії: нове видання, другий примірник, повторний інвентарний номер, видалення видання з примірниками. Остання операція за стандартним зовнішнім ключем буде обмежена: спершу потрібно вирішити долю пов’язаних записів.
Видачу варто моделювати окремою сутністю loan з посиланнями на студента й примірник та часовими атрибутами. Історія видач допомагає відповідати на питання про минулі події. Поле поточний_студент у copy саме по собі цю історію не зберігає. Потрібний рівень історичності залежить від вимог.
Типові помилки та професійні практики
Список інвентарних номерів через кому в одній клітинці ускладнює перевірку унікальності та пошук. Для кожного примірника створюйте рядок. Поле «всі дані студента» змішує атрибути з різними правилами. Зберігайте характеристики окремо й обмежуйте збір персональних даних метою системи.
Перед зміною схеми визначайте вплив на наявні записи. Версійовані міграції мають послідовність, опис і перевірку у тестовому середовищі. Малий набір прикладів повинен включати звичайні й граничні стани, а не лише заповнені таблиці. Відсутність примірників або невідомий рік часто виявляють нечітке правило раніше за велику кількість даних.
Перевірка історії та зміни предметних фактів
Модель поточного стану й модель подій відповідають різним питанням. Поле available у примірника допомагає зрозуміти доступність зараз, а записи loan описують, кому й коли його видавали. Для історичного звіту потрібні часові факти. Якщо зберігати лише останнього користувача, попередні видачі неможливо відновити за самим поточним рядком.
Визначте, які атрибути можуть змінюватися. Назва видання може бути виправлена, студент може змінити контакт. Історичний документ іноді повинен зберігати текст саме на момент події. Це окрема вимога: посилання на поточний student і зафіксований контакт у документі мають різний зміст. Не дублюйте факти без визначення, чи це навмисний знімок.
Для видалення розділіть технічну можливість і предметний процес. Видача, яка вже завершилася, може залишатися частиною історії, хоча примірник списано. Тоді статус списання або архівний запис краще виражає вимогу, ніж фізичне видалення з каскадом. Конкретне рішення залежить від строків збереження й доступу.
Побудуйте три питання до моделі: що доступне зараз, хто користувався минулого місяця, які видання ще не мають примірників. Для кожного назвіть потрібні сутності й атрибути. Якщо відповідь потребує факту, який не записується, модель слід уточнити до реалізації. Така перевірка допомагає погодити достатню деталізацію без збору зайвих відомостей. У словнику відзначте поточні, історичні та обчислені значення окремо.
Схема процесу
Визначаємо об’єкти та правила.
Самоперевірка
Як обґрунтувати відповідь своїми словами?
Підсумок
Реляційна схема спирається на чітку одиницю рядка, ключі й правила зв’язків. Типи та обмеження реалізують предметні домовленості. Словник даних і сценарії перевірки допомагають виявляти неоднозначність моделі.