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

База данных ресторана

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

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

Имя базы: restaurant_db

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

1. tables — информация о столах

   id	table_number	capacity	status
   1	1	4	available
   2	2	2	available
   3	3	6	available

2. clients — информация о клиентах

   id	full_name	phone
   1	Иван Иванов	8007001111
   2	Мария Смирнова	8007002222

3. waiters — информация об официантах

   id	full_name	phone	salary
   1	Анна Кузнецова	8005551234	50000.00
   2	Сергей Волков	8005555678	48000.00

4. orders — информация о заказах (изначально пустая)

   id	table_id	client_id	waiter_id	order_time	total_cost

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

   id	entity_id	old_value	new_value	change_time

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

SET foreign_key_checks = 0;

CREATE DATABASE IF NOT EXISTS restaurant_db;
USE restaurant_db;

CREATE TABLE tables (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_number INT NOT NULL UNIQUE,
    capacity INT NOT NULL,
    status ENUM('available', 'occupied') NOT NULL DEFAULT 'available'
);

INSERT INTO tables (table_number, capacity, status) VALUES
(1, 4, 'available'),
(2, 2, 'available'),
(3, 6, 'available');

CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL
);

INSERT INTO clients (full_name, phone) VALUES
('Иван Иванов', '8007001111'),
('Мария Смирнова', '8007002222'),
('Пётр Петров', '8007003333');

CREATE TABLE waiters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    salary DECIMAL(10,2) NOT NULL DEFAULT 50000.00
);

INSERT INTO waiters (full_name, phone, salary) VALUES
('Анна Кузнецова', '8005551234', 50000.00),
('Сергей Волков', '8005555678', 48000.00);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_id INT NOT NULL,
    client_id INT NOT NULL,
    waiter_id INT NOT NULL,
    order_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total_cost DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (table_id) REFERENCES tables (id),
    FOREIGN KEY (client_id) REFERENCES clients (id),
    FOREIGN KEY (waiter_id) REFERENCES waiters (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. Разработать хранимую процедуру place_order:

   • Параметры:
     • p_table_id — идентификатор стола.
     • p_client_id — идентификатор клиента.
     • p_waiter_id — идентификатор официанта.
     • p_total_cost — общая сумма заказа.
   • Логика работы:

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

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

         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Стол, клиент или официант не найдены';
         

     • Проверяет, свободен ли стол для заказа (статус available).

       • Если стол занят (occupied), возвращает ошибку:

         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Стол уже занят';
         

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

       • Добавляет запись о заказе в таблицу orders.
       • Обновляет статус стола на occupied в таблице tables.
       • Все действия должны быть реализованы с использованием транзакции.

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

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

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

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

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

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

   • Выводит:
     • waiter_name — ФИО официанта.
     • total_orders — количество выполненных заказов.
     • total_earnings — общая сумма заработка официанта.
   • Логика работы: объединяет данные из таблиц waiters и orders.

Результат

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

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

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

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

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

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

-- Пример успешного вызова: размещение заказа
CALL place_order(1, 1, 1, 1500.00);

-- Пример вызова с ошибкой: стол занят
CALL place_order(1, 1, 1, 1500.00);
-- Ожидаемый результат: ошибка "Стол уже занят"

-- Пример вызова с ошибкой: стол не найден
CALL place_order(999, 1, 1, 1500.00);
-- Ожидаемый результат: ошибка "Стол, клиент или официант не найдены"

-- Проверка результата
SELECT * FROM orders;

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

-- Пример вызова: расчёт заработка официанта
SELECT calculate_waiter_earnings(1) AS waiter_earnings;

-- Пример вызова: официант не найден
SELECT calculate_waiter_earnings(999) AS waiter_earnings;
-- Ожидаемый результат: возвращается 0

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

-- Пример вызова: изменение статуса стола
UPDATE tables
SET status = 'occupied'
WHERE id = 1;

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

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

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