Skip to content

module_1_practice.md

Latest commit

 

History

History
142 lines (96 loc) · 9.01 KB

module_1_practice.md

File metadata and controls

142 lines (96 loc) · 9.01 KB

Практическая работа — Модуль 1

Предметная область: Городская библиотека

Вы проектируете базу данных для небольшой городской библиотеки. Библиотека хранит книги, ведёт учёт читателей и фиксирует каждую выдачу книги.

Часть 1. Проектирование схемы

Сущности и их атрибуты

Авторы (authors)

  • Уникальный идентификатор
  • Имя автора — обязательное
  • Страна — необязательная

Книги (books)

  • Уникальный идентификатор
  • Название — обязательное
  • Год издания — целое число, необязательное
  • Количество страниц — целое число, необязательное, не может быть отрицательным
  • Ссылка на автора — обязательная

Читатели (readers)

  • Уникальный идентификатор
  • Имя читателя — обязательное
  • Email — обязательный, уникальный
  • Дата регистрации — текст, обязательный

Выдачи (loans)

  • Уникальный идентификатор
  • Ссылка на книгу — обязательная
  • Ссылка на читателя — обязательная
  • Дата выдачи — текст, обязательный
  • Дата возврата — текст, необязательная (если книга ещё не возвращена — NULL)
  • Одна и та же книга не может быть выдана одному читателю дважды одновременно — составной UNIQUE по book_id и reader_id

Схема связей

authors                books
┌─────────────┐        ┌──────────────────────┐
│ id    INT   │◄───────│ author_id  INT        │
│ name  TEXT  │        │ id         INT   (PK) │
│ country TEXT│        │ title      TEXT       │
└─────────────┘        │ year       INT        │
                       │ pages      INT        │
                       └──────────────────────┘
                                ▲
readers                         │
┌──────────────────┐   loans    │
│ id    INT   (PK) │   ┌────────┴─────────────┐
│ name  TEXT       │   │ id          INT  (PK) │
│ email TEXT       │   │ book_id     INT       │
│ reg_date TEXT    │◄──│ reader_id   INT       │
└──────────────────┘   │ loan_date   TEXT      │
                       │ return_date TEXT      │
                       └──────────────────────┘

Требования к схеме

  • Все первичные ключи — INTEGER PRIMARY KEY AUTOINCREMENT
  • Внешние ключи объявлены через FOREIGN KEY ... REFERENCES
  • Ограничения NOT NULL расставлены согласно описанию
  • CHECK на столбце pages — значение должно быть больше 0
  • Составной UNIQUE на loans по (book_id, reader_id)

Часть 2. Наполнение данными

Создайте файл library_seed.sql и заполните каждую таблицу данными согласно описанию ниже. Для каждой таблицы — от 5 до 7 строк. Используйте многострочный INSERT INTO ... VALUES.

Авторы (5–6 записей): Несколько отечественных и зарубежных авторов. Например: Лев Толстой (Россия), Фёдор Достоевский (Россия), Джордж Оруэлл (Великобритания), Габриэль Гарсиа Маркес (Колумбия), Харпер Ли (США). Можно добавить своего.

Книги (6–7 записей): По одной-две книги на автора. Укажите реальные годы издания и примерное количество страниц. Например: "Война и мир" (1869, 1274 стр.), "Преступление и наказание" (1866, 608 стр.), "1984" (1949, 328 стр.), "Скотный двор" (1945, 112 стр.), "Сто лет одиночества" (1967, 448 стр.), "Убить пересмешника" (1960, 336 стр.).

Читатели (5–7 записей): Читатели с разными email-адресами. Даты регистрации в формате YYYY-MM-DD — разброс от 2023 до 2024 года. Например: Анна Петрова (anna@mail.ru, 2023-03-15), Сергей Волков (sergey@mail.ru, 2023-07-22), Мария Соколова (maria@gmail.com, 2023-11-05), Игорь Зайцев (igor@yandex.ru, 2024-01-18), Елена Морозова (elena@gmail.com, 2024-04-10), Павел Орлов (pavel@mail.ru, 2024-05-30)

Выдачи (6–7 записей):

  • Несколько выдач уже завершены (есть return_date)
  • Несколько книг ещё на руках (return_date = NULL)
  • Один читатель должен иметь минимум две выдачи (разные книги)
  • Одна книга должна встречаться в двух разных выдачах (разным читателям, в разное время)

Часть 3. Запросы

Напишите SQL-запросы для каждого из заданий. Каждый запрос — отдельный файл или отдельный блок в одном файле library_queries.sql.

Запрос 1. Выведите все книги с именем автора. Столбцы: title, author_name, year. Отсортируйте по году издания.

Запрос 2. Найдите все книги которые сейчас находятся на руках у читателей (не возвращены). Выведите: название книги, имя читателя, дату выдачи.

Запрос 3. Выведите читателей которые брали книги хотя бы один раз. Используйте EXISTS. Выведите name и email.

Запрос 4. Посчитайте сколько книг написал каждый автор в базе. Выведите имя автора и количество книг. Отсортируйте по убыванию.

Запрос 5. Найдите книги которые ни разу не выдавались. Выведите title и year.

Запрос 6. Посчитайте сколько раз каждая книга была выдана (включая книги с нулём выдач). Выведите title и loan_count. Отсортируйте по убыванию.

Запрос 7. Найдите читателя который взял больше всего книг. Выведите name и количество выдач.

Запрос 8. Выведите все выдачи с полной информацией: имя читателя, название книги, имя автора, дата выдачи, дата возврата. Если книга не возвращена — в столбце return_date должно быть значение 'не возвращена' вместо NULL (используйте COALESCE(loans.return_date, 'не возвращена')). Отсортируйте по дате выдачи.

Запрос 9. Используя CTE, найдите авторов у которых средний объём книг (страниц) выше среднего по всей библиотеке. Выведите имя автора и среднее количество страниц.

Запрос 10. Используя транзакцию: добавьте нового читателя, выдайте ему одну из существующих книг. Зафиксируйте транзакцию и проверьте результат через SELECT.

Что сдаётся

  • library_seed.sql — создание таблиц и наполнение данными
  • library_queries.sql — все 10 запросов с комментариями (-- Запрос 1, -- Запрос 2, ...)