Спроєктуйте просту схему бази даних для бібліотеки.

Повне пояснення

Що це і навіщо

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

Ключові принципи/терміни

• Primary key (PK) – унікальний ідентифікатор запису.
• Foreign key (FK) – посилання на PK іншої таблиці, що гарантує зв’язок.
• Referential integrity – обмеження, що запобігають «загубленим» посиланням.

Як це працює

• При вставці запису в таблицю Loans СУБД перевіряє, що значення book_id існує в Books, а patron_id – в Patrons. Якщо ні, транзакція відхиляється.

Практика й реалізація (PostgreSQL)

CREATE TABLE Authors (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE Books (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INT REFERENCES Authors(id)
);

CREATE TABLE Patrons (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

CREATE TABLE Loans (
  id SERIAL PRIMARY KEY,
  book_id INT REFERENCES Books(id),
  patron_id INT REFERENCES Patrons(id),
  loan_date DATE NOT NULL,
  return_date DATE
);

Тестування

• Unit: перевірка функцій вставки/видалення через pg-mem або jest-pg.
• Integration: використати Prisma для генерації моделей і тестувати через supertest.

Оптимізація запитів

• Індекси на Books.author_id, Loans.book_id і Loans.patron_id.
• Використовувати EXPLAIN ANALYZE для перевірки плану виконання.

Безпека

• Підтримка ролей: librarian, patron.
• Валідація вхідних даних на рівні API, щоб уникнути SQL‑ін’єкцій.

Особливості в контексті ORM

• У Prisma це буде model Book { id Int @id @default(autoincrement()) title String author Author @relation(fields: [authorId], references: [id]) }.

Часті помилки

1. Вставка позики без існуючого book_id.
2. Дублікати унікальних полів (наприклад, email).
3. Відсутність індексу на колонку пошуку.
4. Перевищення ліміту одночасних транзакцій.
5. Неправильне використання ON DELETE CASCADE – видалення автора видаляє всі книги.
6. Застарілі типи даних (наприклад, VARCHAR без довжини).
7. Необроблені помилки при підключенні до БД.
8. Погане управління транзакціями – часткові оновлення.

Cheatsheet

• CREATE TABLE … – визначає структуру.
• PRIMARY KEY, FOREIGN KEY – забезпечують цілісність.
• INSERT … ON CONFLICT DO NOTHING – уникнення дублікати.
• EXPLAIN ANALYZE – аналіз плану.

Follow‑up питання

• Як реалізувати історію позик?
• Які переваги ON DELETE SET NULL над CASCADE?
• Як масштабувати схему при великій кількості користувачів?