Проектирование схемы библиотеки и написание SQL‑запросов

Условие

Необходимо спроектировать структуру данных для библиотеки, включающую три сущности: Автор, Книга и Читатель.

• Каждый экземпляр книги существует в единственном числе и может находиться только у одного читателя одновременно. Сформируйте таблицы так, чтобы это ограничение было отражено.
• У книги может быть несколько авторов (отношение «многие‑ко‑многим»).

Составьте следующие SQL‑запросы:

1. Выбрать названия всех книг, которые находятся у читателей.
2. Выбрать названия всех книг, находящихся в фонде, у которых количество авторов превышает три.
3. Выбрать имена трёх самых часто читаемых авторов на текущий момент.

Ответ

Нужны книги, авторы, читатели, связь book_authors и история выдач loans. Уникальный частичный индекс запрещает две открытые выдачи одной книги.

Считаем books отдельными экземплярами, а «в фонде» — сейчас не выданными. Для PostgreSQL:

CREATE TABLE authors (id bigint PRIMARY KEY, name text NOT NULL);
CREATE TABLE books (id bigint PRIMARY KEY, title text NOT NULL);
CREATE TABLE readers (id bigint PRIMARY KEY, name text NOT NULL);
CREATE TABLE book_authors (
  book_id bigint REFERENCES books(id),
  author_id bigint REFERENCES authors(id),
  PRIMARY KEY (book_id, author_id)
);
CREATE TABLE loans (
  id bigint PRIMARY KEY,
  book_id bigint NOT NULL REFERENCES books(id),
  reader_id bigint NOT NULL REFERENCES readers(id),
  taken_at timestamptz NOT NULL,
  returned_at timestamptz
);
CREATE UNIQUE INDEX one_active_loan ON loans(book_id)
WHERE returned_at IS NULL;

SELECT b.title
FROM books b JOIN loans l ON l.book_id = b.id
WHERE l.returned_at IS NULL;

SELECT b.title
FROM books b JOIN book_authors ba ON ba.book_id = b.id
WHERE NOT EXISTS (
  SELECT 1 FROM loans l
  WHERE l.book_id = b.id AND l.returned_at IS NULL
)
GROUP BY b.id, b.title
HAVING COUNT(*) > 3;

SELECT a.id, a.name, COUNT(*) AS reads
FROM authors a
JOIN book_authors ba ON ba.author_id = a.id
JOIN loans l ON l.book_id = ba.book_id
GROUP BY a.id, a.name
ORDER BY reads DESC, a.id
LIMIT 3;

Последний запрос трактует популярность как число всех выдач за историю к текущему моменту. Если нужны только книги на руках сейчас, добавьте returned_at IS NULL. При соавторстве одна выдача засчитывается каждому автору; требование иного распределения надо оговорить.