Аномалии (кратко):
- Вставка: нельзя добавить автора/издателя без книги (повторение их полей в Books).
- Обновление: изменение фамилии/страны автора или адреса издателя требует правки во всех строках Books → возможна несогласованность.
- Удаление: удаление последней книги автора/издателя теряет информацию об авторе/издателе (потеря данных).
Нормализация до $\text{3NF}$ (предложение):
- Authors(author_id PK, name, country)
- Publishers(publisher_id PK, name, address)
- Books(book_id PK, title, author_id FK→Authors(author_id), publisher_id FK→Publishers(publisher_id), year)
- Borrowers(borrower_id PK, name, contact)
- Loans(loan_id PK, book_id FK→Books(book_id), borrower_id FK→Borrowers(borrower_id), loan_date, return_date)
Пояснения:
- Убраны повторяющиеся атрибуты автора/издателя из Books — устранены транзитивные зависимости, каждая таблица хранит одну сущность → $\text{3NF}$.
- При необходимости поддержки нескольких физических экземпляров добавить Copies(copy_id PK, book_id FK→Books(book_id), barcode/condition) и ссылаться в Loans на copy_id.
Индекcная стратегия (частые запросы):
- Поиск книг по названию/автору:
- полнотекстовый/GIN-индекс на Books.title (если длинные тексты).
- B-tree индекс на Authors.name и/или комбинированный индекс (Authors.name, Authors.country) для фильтрации по стране.
- Поиск текущих выдач и история по пользователю/книге:
- индекс Loans(book_id), Loans(borrower_id).
- частичный индекс на Loans(return_date) WHERE return_date IS NULL (если СУБД поддерживает) для быстрых текущих займов.
- Поддержка FK-проверок и соединений: индексы на Books.author_id и Books.publisher_id (обычно создаются автоматически при FK) и на Copies.book_id, если есть Copies.
- Уникальные / ограничения: уникальный индекс на Publishers(name, address) при необходимости; уникальность на Copies.barcode.
Пример ACID‑транзакции для выдачи книги (концептуально, SQL‑псевдо; использовать параметры):
- Цель: выдать книгу, если она доступна (нет незакрытого займа).
- Предполагаем, что Loans.return_date IS NULL означает «в аренде».
SQL (пример для PostgreSQL; параметры :book_id, :borrower_id, :now):
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- строгая изоляция, предотвращает фантомы
-- блокируем строку книги (или копии) чтобы избежать гонок
SELECT book_id FROM Books WHERE book_id = :book_id FOR UPDATE;
-- проверяем, нет ли открытой выдачи
SELECT loan_id FROM Loans WHERE book_id = :book_id AND return_date IS NULL;
-- если результат пустой, создаём запись о выдаче
INSERT INTO Loans(book_id, borrower_id, loan_date, return_date) VALUES (:book_id, :borrower_id, :now, NULL);
COMMIT;
Комментарий к транзакции:
- Изоляция $\text{SERIALIZABLE}$ или блокировка через SELECT ... FOR UPDATE предотвращает одновременную двойную выдачу.
- Альтернатива: использовать уникальное частичное ограничение (например уникальный индекс на book_id WHERE return_date IS NULL) — тогда простая вставка в транзакции с обработкой ошибки конкуренции достаточна.
- Обрабатывать ошибки (повтор транзакции при серийных конфликтов) и откат в случае нарушения ограничений.
Дополнительно: настроить каскадные/ограничивающие FK (ON DELETE RESTRICT для Authors/Publishers, либо ON DELETE SET NULL по политике) и логирование для аудита.