Схема (реляционные таблицы, ключи, кратко поля)
- Book(BookID PK, ISBN UNIQUE, Title, Year, PublisherID FK, Pages, Description)
- Author(AuthorID PK, FirstName, LastName, BirthDate)
- BookAuthor(BookID FK, AuthorID FK, AuthorOrder, PRIMARY KEY(BookID, AuthorID)) — связь многие-ко-многим и порядок авторов
- Copy(CopyID PK, BookID FK, Barcode UNIQUE, AcquisitionDate, Condition, Status) — физические экземпляры книги
- Reader(ReaderID PK, FullName, Email UNIQUE, Phone, Address, RegDate)
- Loan(LoanID PK, CopyID FK, ReaderID FK, LoanDate, DueDate, ReturnDate, Fine) — ReturnDate NULL = не возвращена
- Publisher(PublisherID PK, Name, Address)
- Genre(GenreID PK, Name) и BookGenre(BookID FK, GenreID FK, PRIMARY KEY(BookID,GenreID))
Нормализация до $3NF$
1) Исходная "плоская" таблица (пример):
$$R(BookID,ISBN,Title,PublisherName,AuthorID,AuthorName,CopyID,Barcode,ReaderID,ReaderName,LoanID,LoanDate,DueDate,ReturnDate)$$
2) Функциональные зависимости (основные):
$$BookID\to ISBN,Title,PublisherID$$
$$ISBN\to BookID$$ (если ISBN уникален)
$$PublisherID\to PublisherName$$
$$AuthorID\to AuthorName$$
$$CopyID\to BookID,Barcode$$
$$LoanID\to CopyID,ReaderID,LoanDate,DueDate,ReturnDate$$
$$ReaderID\to ReaderName,Email$$
3) Приведение к $1NF$: все атрибуты атомарны (авторы/жанры разнесены в отдельные таблицы/связки).
4) $2NF$: устранение частичных зависимостей от части составного ключа. В исходной плоской схеме атрибуты автора, книги, читателя и займа зависят от своих идентификаторов, а не от составных ключей — поэтому декомпозиция в таблицы Book, Author, Copy, Reader, Loan, BookAuthor устраняет частичные зависимости.
5) $3NF$: устранение транзитивных зависимостей. Пример:
$$BookID\to PublisherID\text{и}PublisherID\to PublisherName$$
значит PublisherName транзитивно зависит от BookID — поэтому выделяем таблицу Publisher. После разложения каждая неключевая колонка зависит только от ключа своей таблицы — это $3NF$.
Итог: таблицы, перечисленные выше, находятся в $3NF$: нет частичных или транзитивных зависимостей неключевых атрибутов.
Индексы и их влияние на типичные запросы
Общие рекомендации:
- PK — кластерные/btree автоматические индексы на первичных ключах.
- FK — индексировать внешние ключи (например, BookID в Copy, CopyID и ReaderID в Loan, AuthorID в BookAuthor) для ускорения JOIN и удаления/обновления.
- Уникальные индексы: ISBN (Book), Barcode (Copy), Email (Reader) — для быстрого поиска и поддержания уникальности.
Индексы для типичных сценариев:
- Поиск книг по названию/описанию: полнотекстный индекс (например, GIN/tsvector) на Book(Title, Description) — ускоряет поиски по ключевым словам.
- Поиск по автору: индекс на Author(LastName) и на BookAuthor(AuthorID) — ускоряет получение всех книг автора.
- Получение всех экземпляров книги: индекс на Copy(BookID).
- Текущие выдачи читателя: составной индекс на Loan(ReaderID, ReturnDate) или частичный индекс на Loan(ReaderID) WHERE ReturnDate IS NULL — быстрый список незавершённых займов.
- Поиск просроченных: индекс на Loan(DueDate) и/или частичный индекс WHERE ReturnDate IS NULL для эффективного запроса просрочек.
- Частые сортировки: если часто сортируете по DueDate при фильтре ReturnDate IS NULL, создайте составной индекс (ReturnDate, DueDate) либо частичный индекс для ReturnDate IS NULL с включённым DueDate.
Влияние на производительность:
- Плюсы: индексы значительно ускоряют SELECT и JOIN по проиндексированным колонкам, уменьшает время ответа для поиска книг, списка книг автора, проверок доступности и выдач.
- Минусы: индексы расходуют дополнительное место и замедляют операции записи (INSERT/UPDATE/DELETE) — особенно множество и сложные составные/текстовые индексы. Частичные/покрывающие индексы и выбор только необходимых индексов помогают балансировать скорость чтения и записи.
- Рекомендация: мониторить реальные запросы (EXPLAIN), добавлять индексы под нагрузки (например, частичный индекс на активные займы), избегать индексации слишком многих колонок без профилирования.
Короткая практика индексов (пример для PostgreSQL):
- PRIMARY KEY и UNIQUE создаются автоматически.
- INDEX для быстрых выборок:
- CREATE INDEX idx_copy_bookid ON Copy(BookID);
- CREATE INDEX idx_loans_reader_active ON Loan(ReaderID) WHERE ReturnDate IS NULL;
- CREATE INDEX idx_book_title_fts ON Book USING GIN(to_tsvector('russian', Title || ' ' || Description));
Это всё — достаточная база для реализации реляционной модели библиотеки в $3NF$ с практичными индексами для типичных запросов.