📝 Простое задание: База данных для библиотеки 🎯 Задача: Создать две связанные таблицы для учета книг в библиотеке и читателей, которые их берут. 📚 Таблица 1: Книги (books)…

Ниже приведено подробное решение с пояснениями и готовыми SQL-структурами для двух связанных таблиц: books и readers. Решение адаптировано под общую школьную базу данных (ANSI-совместимый стиль). Параметр «Цель: Понять» учтён — есть пошаговые объяснения.

Что мы хотим получить

• Таблица books (книги) хранит: id книги, название, автора, год издания, жанр, и reader_id — внешний ключ, указывающий на читателя, который в данный момент взял книгу.
• Таблица readers (читатели) хранит: id читателя, ФИО, номер читательского билета, дату регистрации.
• Связь: одна книга может быть выдана одному читателю в данный момент. Если книга не выдана — reader_id = NULL.
• Ограничения: первичные ключи, обязательные поля (NOT NULL), внешний ключ с опцией, чтобы удаление читателя не ломало данные книг (обычно устанавливают NULL для reader_id при удалении читателя).

1. Проектирование (почему так)

• PK: id в обеих таблицах обеспечивает уникальность каждой записи.
• NOT NULL: все основные характеристики книги и читателя должны быть заполнены.
• Подтверждение уникальности: номер билетa читателя (ticket_number) логично сделать уникальным, чтобы одному читателю принадлежал только один билет.
• Внешний ключ: books.reader_id — REFERENCES readers(id). Это обеспечивает целостность: если книга выдана, её reader_id обязательно ссылается на существующего читателя.
• ON DELETE SET NULL: если читатель удаляется, книги, которые у него «покинулись», станут незакреплёнными (reader_id = NULL). Это соответствует услуге «книга может быть не выдана».
• ON UPDATE CASCADE: если идентификатор читателя случайно поменяется, связь сохраняется.

2. Структуры таблиц (типичные типы данных)

• id: INTEGER (целое число), автоинкрементное значение.
• title, author, genre: строки (VARCHAR).
• year: целое число (SMALLINT или INT, в зависимости от диапазона).
• full_name (ФИО), ticket_number: строки.
• registration_date: дата (DATE).
• reader_id: INTEGER, может быть NULL.

3. Реализация схемы (SQL) Привожу вариант на MySQL-совместимый синтаксис. В других СУБД синтаксис автоинкремента может немного отличаться (например, SERIAL в PostgreSQL).

Создание таблиц:

• Таблица readers CREATE TABLE readers ( id INT NOT NULL AUTO_INCREMENT, full_name VARCHAR(255) NOT NULL, ticket_number VARCHAR(20) NOT NULL UNIQUE, registration_date DATE NOT NULL, PRIMARY KEY (id) );

• Таблица books CREATE TABLE books ( id INT NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, author VARCHAR(255) NOT NULL, year SMALLINT NOT NULL, genre VARCHAR(100) NOT NULL, reader_id INT NULL, PRIMARY KEY (id), FOREIGN KEY (reader_id) REFERENCES readers(id) ON DELETE SET NULL ON UPDATE CASCADE );

Примечание:

• Для PostgreSQL можно использовать SERIAL или GENERATED AS IDENTITY вместо AUTO_INCREMENT:
  • id SERIAL PRIMARY KEY
  • или id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
• Для SQLite можно использовать INTEGER PRIMARY KEY AUTOINCREMENT и т.д.

4. Примеры данных (для демонстрации связи) Добавим читателей и книги, чтобы увидеть работу связи. Пример: два читателя, три книги; одна книга выдана первому читателю, другая — не выдана, третья — второму читателю.

Вставка читателей (без указания id — автоинкремент): INSERT INTO readers (full_name, ticket_number, registration_date) VALUES ('Иванова Анна Сергеевна', '12345', '2024-01-15'), ('Петров Иван Дмитриевич', '12346', '2024-02-20');

Вставка книг (одна книга взята, две свободны): INSERT INTO books (title, author, year, genre, reader_id) VALUES ('Преступление и наказание', 'Ф.М. Достоевский', 1866, 'Роман', 1), ('Мастер и Маргарита', 'М.А. Булгаков', 1967, 'Фантастика', NULL), ('Война и мир', 'Л.Н. Толстой', 1869, 'Роман', 2);

5. Как проверить состояние и работу связи

• Посмотреть все книги и их текущих держателей (если есть): SELECT b.id AS book_id, b.title, r.id AS reader_id, r.full_name AS reader_name FROM books b LEFT JOIN readers r ON b.reader_id = r.id;

• Найти только выданные книги и кто их взял: SELECT b.title, r.full_name, r.ticket_number FROM books b JOIN readers r ON b.reader_id = r.id;

• Проверить, что можно добавить новую выдачу или вернуть книгу (пример обновления reader_id): -- Вернуть книгу (оставить NULL): UPDATE books SET reader_id = NULL WHERE id = 1;

-- Выдать книгу новому читателю (предположим, читатель с id = 2): UPDATE books SET reader_id = 2 WHERE id = 3;

6. Ключевые ограничения и проверки

• Нельзя создать книгу с reader_id, который не существует в readers — это обеспечит FK.
• reader_id может быть NULL, если книга не выдана — это соответствует задаче.
• Если читатель удаляется, связанные книги станут не привязанными к никому (reader_id = NULL) благодаря ON DELETE SET NULL.
• ticket_number у читателя уникален (UNIQUE) — предотвращает дубли.

Итого

• tables: readers и books.
• PK: readers.id, books.id.
• FK: books.reader_id -> readers.id (ON DELETE SET NULL, ON UPDATE CASCADE).
• NOT NULL: основные поля в обеих таблицах.
• NULLABLE: reader_id в books (когда книга не выдана).
• Примеры данных показывают работу связи: некоторые книги выданы, другие — свободны.

Если нужно, могу адаптировать скрипты под конкретную СУБД (MySQL, PostgreSQL, SQL Server и т. д.) или добавить дополнительные поля (например, дата выдачи, срок возврата, статус выдачи и т. п.).