Практика 3-5. Реляционная схема библиотеки и начальные данные

Цель

Продолжить работу с предметной областью «Библиотека» из Практики 2: преобразовать ER-модель в реляционную схему, создать таблицы на SQL и наполнить их начальными данными.

В этой работе объединены навыки из тем о реляционной модели, преобразовании ER-диаграмм, DDL-командах и начальном наполнении базы данных.

Исходная модель

Используйте ER-модель, которую вы построили в Практике 2. В ней должны быть отражены следующие сущности и связи:

  • Reader — читатель библиотеки.
  • Book — книга, хранящаяся в библиотеке.
  • Author — автор книги.
  • Loan — факт выдачи книги читателю.
  • Authorship — связь книги и автора, так как у книги может быть несколько авторов.

Если ваша диаграмма немного отличается, адаптируйте задание под нее, но сохраните смысл: книги, читатели, авторы, авторство и выдачи должны быть представлены в базе данных.

Задание 1. Преобразование ER-модели в реляционную схему

Опишите, какие таблицы получаются из вашей ER-модели. Для каждой таблицы укажите столбцы, первичный ключ и внешние ключи.

Минимальная схема должна включать:

  • Readers — читатели.
  • Books — книги.
  • Authors — авторы.
  • Book_Authors — связующая таблица для связи M:N между книгами и авторами.
  • Loans — выдачи книг читателям.

Обратите внимание на связь Book и Author: хранить автора прямо в таблице книг недостаточно, потому что одна книга может иметь нескольких авторов, а один автор может написать несколько книг.

Задание 2. Создание таблиц

Напишите SQL-скрипт создания таблиц для PostgreSQL. Используйте понятные английские имена таблиц и столбцов, первичные ключи, внешние ключи и ограничения целостности.

В скрипте должны быть реализованы следующие требования:

  • Для читателей создайте таблицу с автоинкрементным идентификатором, ФИО и уникальным телефоном. ФИО и телефон не должны быть пустыми.
  • Для книг создайте таблицу, где ISBN является первичным ключом. Также храните название и год издания. Название должно быть обязательным, а год издания должен проверяться ограничением CHECK.
  • Для авторов создайте таблицу с автоинкрементным идентификатором и обязательным ФИО автора.
  • Для связи книг и авторов создайте связующую таблицу. В ней должны быть два внешних ключа: на книгу и на автора. Первичный ключ этой таблицы должен быть составным.
  • Для выдач создайте таблицу с автоинкрементным идентификатором, внешними ключами на читателя и книгу, датой выдачи, планируемой датой возврата и фактической датой возврата.
  • Фактическая дата возврата должна допускать значение NULL, если книга еще не возвращена. Добавьте проверки, чтобы даты возврата не были раньше даты выдачи.

Добавьте к скрипту короткие комментарии: какая таблица какую сущность или связь реализует.

Задание 3. Начальное наполнение базы данных

Заполните таблицы тестовыми данными. Данные должны быть связаны между собой: у каждой выдачи должны существовать читатель и книга, у каждой записи об авторстве — существующие книга и автор.

3.1 Читатели

Добавьте не менее четырех читателей:

  • Анна Петрова, телефон +7-900-111-22-33
  • Иван Соколов, телефон +7-900-222-33-44
  • Мария Ким, телефон +7-900-333-44-55
  • Олег Васильев, телефон +7-900-444-55-66

3.2 Книги и авторы

Добавьте не менее пяти книг и пяти авторов. Среди книг обязательно должна быть хотя бы одна с двумя авторами.

Пример набора данных:

  • 978-5-17-118366-8 — «Мастер и Маргарита», 1967, Михаил Булгаков.
  • 978-5-389-06256-6 — «Преступление и наказание», 1866, Федор Достоевский.
  • 978-5-04-116716-3 — «Война и мир», 1869, Лев Толстой.
  • 978-5-699-12014-7 — «Золотой теленок», 1931, Илья Ильф и Евгений Петров.
  • 978-5-389-03713-7 — «Пикник на обочине», 1972, Аркадий Стругацкий и Борис Стругацкий.

3.3 Авторство

Заполните таблицу Book_Authors. Для книг с двумя авторами добавьте две строки: одну для каждого автора.

3.4 Выдачи книг

Добавьте не менее шести записей в таблицу Loans:

  • не менее двух активных выдач, где actual_return_date равен NULL;
  • не менее двух завершенных выдач с заполненной фактической датой возврата;
  • хотя бы один читатель должен иметь больше одной выдачи.

Задание 4. Изменение и удаление данных

После начального наполнения выполните несколько операций DML.

  1. У одного из читателей изменился номер телефона. Обновите его с помощью UPDATE.
  2. Одна активная выдача завершилась. Заполните для нее actual_return_date.
  3. Удалите тестового читателя, у которого нет выдач. Если у всех читателей есть выдачи, сначала добавьте отдельного тестового читателя без выдач.

В каждом запросе UPDATE и DELETE обязательно используйте условие WHERE.

Форма отчетности

Сдайте один SQL-файл или текстовый отчет, в котором есть:

  1. Краткое описание полученной реляционной схемы.
  2. SQL-код создания всех таблиц.
  3. SQL-код наполнения таблиц начальными данными.
  4. SQL-код операций UPDATE и DELETE.
  5. Краткий вывод: какие связи из ER-модели были реализованы внешними ключами.