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

База данных гостиницы

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

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

Имя базы: hotel_db

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

1. rooms — информация о номерах

   id	room_number	room_type	price_per_day	status
   1	101	standard	3000.00	available
   2	202	deluxe	5000.00	available
   3	303	suite	8000.00	available

2. guests — информация о гостях

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

3. employees — информация о сотрудниках

   id	full_name	position
   1	Анна Кузнецова	администратор
   2	Сергей Волков	менеджер

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

   id	guest_id	room_id	employee_id	check_in_date	check_out_date	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 hotel_db;
USE hotel_db;

CREATE TABLE rooms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    room_number INT NOT NULL UNIQUE,
    room_type VARCHAR(50) NOT NULL,
    price_per_day DECIMAL(10,2) NOT NULL,
    status ENUM('available', 'booked') NOT NULL DEFAULT 'available'
);

INSERT INTO rooms (room_number, room_type, price_per_day, status) VALUES
(101, 'standard', 3000.00, 'available'),
(202, 'deluxe', 5000.00, 'available'),
(303, 'suite', 8000.00, 'available');

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

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

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    position VARCHAR(100) NOT NULL
);

INSERT INTO employees (full_name, position) VALUES
('Анна Кузнецова', 'администратор'),
('Сергей Волков', 'менеджер');

CREATE TABLE bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    guest_id INT NOT NULL,
    room_id INT NOT NULL,
    employee_id INT NOT NULL,
    check_in_date DATE NOT NULL,
    check_out_date DATE NOT NULL,
    total_cost DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (guest_id) REFERENCES guests (id),
    FOREIGN KEY (room_id) REFERENCES rooms (id),
    FOREIGN KEY (employee_id) REFERENCES employees (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. Разработать хранимую процедуру book_room:

   • Параметры:
     • p_guest_id — идентификатор гостя.
     • p_room_id — идентификатор номера.
     • p_employee_id — идентификатор сотрудника.
     • p_check_in_date — дата заселения.
     • p_check_out_date — дата выселения.
   • Логика работы:

     • Проверяет существование гостя, номера и сотрудника.

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

         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Гость, номер или сотрудник не найдены';
         

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

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

         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Номер уже забронирован';
         

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

       • Вычисляет общую стоимость проживания как (количество дней * price_per_day).
       • Добавляет запись о бронировании в таблицу bookings с указанием всех параметров.
       • Обновляет статус номера на booked в таблице rooms.
       • Все действия должны быть реализованы с использованием транзакции.

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

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

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

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

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

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

   • Выводит:
     • employee_name — ФИО сотрудника.
     • total_bookings — количество бронирований, оформленных сотрудником.
     • total_revenue — общая сумма по бронированиям.
   • Логика работы: объединяет данные из таблиц employees, rooms и bookings.

Результат

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

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

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

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

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

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

-- Пример успешного вызова: бронирование номера
CALL book_room(1, 1, 1, '2024-06-01', '2024-06-05');

-- Пример вызова с ошибкой: номер уже забронирован
CALL book_room(1, 1, 1, '2024-06-01', '2024-06-05');
-- Ожидаемый результат: ошибка "Номер уже забронирован"

-- Пример вызова с ошибкой: гость не найден
CALL book_room(999, 1, 1, '2024-06-01', '2024-06-05');
-- Ожидаемый результат: ошибка "Гость, номер или сотрудник не найдены"

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

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

-- Пример вызова: расчёт стоимости проживания
SELECT calculate_stay_cost(1, 4) AS stay_cost;

-- Пример вызова: неверный номер
SELECT calculate_stay_cost(999, 4) AS stay_cost;
-- Ожидаемый результат: возвращается 0

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

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

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

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

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