МДК.11.01 - Практический экзамен - Вариант №2

База данных книжного магазина

Вы работаете с базой данных книжного магазина, который ведёт учёт книг, авторов, поставщиков и продаж. Ваша задача — дополнить существующую базу данных необходимыми объектами, написать SQL-скрипты для выполнения операций над базой данных и продемонстрировать использование хранимых процедур, пользовательских функций, триггеров и представлений.

Готовая база данных

Имя базы: bookstore_db

Таблицы и данные:

1. books — информация о книгах

   id	title	author_id	supplier_id	price	stock
   1	Война и мир	1	1	500.00	20
   2	Преступление и наказание	2	2	300.00	15
   3	Мастер и Маргарита	3	1	400.00	10

2. authors — информация об авторах

   id	name	birth_date
   1	Лев Толстой	1828-09-09
   2	Фёдор Достоевский	1821-11-11
   3	Михаил Булгаков	1891-05-15

3. suppliers — информация о поставщиках

   id	name	contact_person	phone
   1	ООО "Книжный Мир"	Иван Иванов	8005551234
   2	ИП "Книги для всех"	Пётр Петров	8005555678

4. sales — учёт продаж (изначально пустая)

   id	book_id	sale_date	quantity	total

5. change_logs — журнал изменений в системе

   id	entity_id	old_value	new_value	change_time

Для создания соответствующей БД используйте SQL-скрипт:

SET foreign_key_checks = 0;

CREATE DATABASE IF NOT EXISTS bookstore_db;
USE bookstore_db;

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT NOT NULL,
    supplier_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors (id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers (id)
);

INSERT INTO books (title, author_id, supplier_id, price, stock) VALUES
('Война и мир', 1, 1, 500.00, 20),
('Преступление и наказание', 2, 2, 300.00, 15),
('Мастер и Маргарита', 3, 1, 400.00, 10);

CREATE TABLE authors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    birth_date DATE NOT NULL
);

INSERT INTO authors (name, birth_date) VALUES
('Лев Толстой', '1828-09-09'),
('Фёдор Достоевский', '1821-11-11'),
('Михаил Булгаков', '1891-05-15');

CREATE TABLE suppliers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL
);

INSERT INTO suppliers (name, contact_person, phone) VALUES
('ООО "Книжный Мир"', 'Иван Иванов', '8005551234'),
('ИП "Книги для всех"', 'Пётр Петров', '8005555678');

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    sale_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    quantity INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (book_id) REFERENCES books (id)
);

CREATE TABLE change_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_id INT,
    old_value VARCHAR(255),
    new_value VARCHAR(255),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SET foreign_key_checks = 1;

Задачи

1. Разработать хранимую процедуру sell_book:

   • Параметры:
     • p_book_id — идентификатор книги.
     • p_quantity — количество проданных книг.
   • Логика работы:

     • Проверяет, существует ли книга в таблице books.

       • Если книга не найдена, возвращает ошибку:

         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Книга не найдена';
         

     • Проверяет, достаточно ли книг на складе.

       • Если книг не хватает, возвращает варнинг:

         SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Недостаточно книг на складе';
         

       • Продажа не осуществляется.

     • Если проверки прошли успешно:

       • Уменьшает количество книг на складе (stock) в таблице books.
       • Добавляет запись о продаже в таблицу sales с вычислением общей суммы (total = price * quantity).
       • Логирует успешную продажу в таблицу sales.
       • Все действия должны быть реализованы с использованием транзакции.

     • В случае любой другой ошибки откатывает транзакцию.

2. Создать пользовательскую функцию get_books_by_supplier:

   • Параметр:
     • p_supplier_id — идентификатор поставщика.
   • Возвращает: количество книг, поставленных указанным поставщиком.
   • Если поставщик не найден, функция возвращает 0 без генерации ошибок.

3. Создать триггер log_stock_update для таблицы books:

   • Срабатывает:
     • При изменении значения stock (например, при продаже книги).
   • Действие:
     • Логирует изменения в таблицу change_logs.
     • Записывает:
       • entity_id — ID книги.
       • old_value — предыдущее количество на складе.
       • new_value — новое количество на складе.
       • change_time — момент изменения.

4. Создать представление supplier_sales_summary:

   • Выводит:
     • supplier_name — название поставщика.
     • total_books — общее количество книг, проданных этим поставщиком.
     • total_revenue — общий доход от продаж книг этого поставщика.
   • Логика работы: объединяет данные из таблиц suppliers, books и sales.

Результат

Задание должно быть выполнено в локальной файловой системе компьютера. Папка со всеми файлами задания должна называться:

МДК.11.01 - Вариант 2 - Иванов И.И.

где вместо Иванов И.И. должны быть указаны фамилия и инициалы сдающего.

Результат выполнения задания должен быть оформлен в виде файлов:

1. Файл дампа итоговой базы данных с созданными объектами (хранимая процедура, триггер, функция, представление) и внесёнными изменениями. Должен называться db_2.sql.
2. Файл с последовательными SQL-запросами для тестирования каждого объекта. Должен называться task_2.sql.

-- ===========================
-- 1. Тестирование хранимой процедуры sell_book
-- ===========================

-- Пример успешного вызова: продажа книги
CALL sell_book(1, 5);

-- Пример вызова с ошибкой: книга не найдена
CALL sell_book(999, 5);
-- Ожидаемый результат: ошибка "Книга не найдена"

-- Пример вызова с варнингом: недостаточно книг на складе
CALL sell_book(1, 50);
-- Ожидаемый результат: варнинг "Недостаточно книг на складе"

-- Проверка результата
SELECT * FROM sales WHERE book_id = 1;

-- ===========================
-- 2. Тестирование пользовательской функции get_books_by_supplier
-- ===========================

-- Пример вызова: подсчёт книг для поставщика
SELECT get_books_by_supplier(1) AS books_count;

-- Пример вызова: поставщик не найден
SELECT get_books_by_supplier(999) AS books_count;
-- Ожидаемый результат: возвращается 0

-- ===========================
-- 3. Тестирование триггера log_stock_update
-- ===========================

-- Пример вызова: уменьшение количества книг на складе
UPDATE books
SET stock = stock - 2
WHERE id = 1;

-- Проверка результата: логи изменений
SELECT * FROM change_logs;

-- ===========================
-- 4. Тестирование представления supplier_sales_summary
-- ===========================

-- Пример вызова: получение данных из представления
SELECT * FROM supplier_sales_summary;