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

База данных автосалона

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

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

Имя базы: car_dealership_db

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

1. cars — информация об автомобилях

   id	vin	model	mileage	price	status
   1	1HGCM82633A123456	Toyota Camry	20000	2500000	available
   2	WBA3A5C58DF123457	BMW X5	10000	5500000	available

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

   id	full_name	phone
   1	Алексей Смирнов	8007001111
   2	Елена Кузнецова	8007002222

3. managers — информация о менеджерах

   id	full_name	phone	commission_rate
   1	Иван Иванов	8005551234	0.01

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

   id	client_id	car_id	manager_id	order_date	total

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

   id	entity_id	old_value	new_value	change_time

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

SET foreign_key_checks = 0;

CREATE DATABASE IF NOT EXISTS car_dealership_db;
USE car_dealership_db;

CREATE TABLE cars (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vin VARCHAR(17) NOT NULL UNIQUE,
    model VARCHAR(255) NOT NULL,
    mileage INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    status ENUM('available', 'sold') NOT NULL DEFAULT 'available'
);

INSERT INTO cars (vin, model, mileage, price, status) VALUES
('1HGCM82633A123456', 'Toyota Camry', 20000, 2500000, 'available'),
('WBA3A5C58DF123457', 'BMW X5', 10000, 5500000, 'available'),
('KM8J3CA46KU123458', 'Hyundai Solaris', 15000, 1200000, '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 managers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    commission_rate DECIMAL(5,2) NOT NULL DEFAULT 0.01
);

INSERT INTO managers (full_name, phone) VALUES
('Иван Иванов', '8005551234'),
('Пётр Петров', '8005555678');

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    car_id INT NOT NULL,
    manager_id INT NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients (id),
    FOREIGN KEY (car_id) REFERENCES cars (id),
    FOREIGN KEY (manager_id) REFERENCES managers (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_car:

   • Параметры:
     • p_client_id — идентификатор клиента.
     • p_car_id — идентификатор автомобиля.
     • p_manager_id — идентификатор менеджера.
   • Логика работы:

     • Проверяет существование клиента, автомобиля и менеджера в соответствующих таблицах.

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

         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Клиент, автомобиль или менеджер не найдены';
         

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

       • Если автомобиль уже продан (статус sold), возвращает ошибку:

         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Автомобиль уже продан';
         

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

       • Обновляет статус автомобиля на sold в таблице cars.
       • Добавляет запись о заказе в таблицу orders с указанием общей суммы (total = price), и устанавливает дату заказа (order_date).
       • Все действия должны быть реализованы с использованием транзакции.

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

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

   • Параметр:
     • p_order_id — идентификатор заказа.
   • Возвращает: размер комиссии менеджера за конкретный заказ (вычисляется как total * commission_rate).
   • Если заказ не найден, функция возвращает 0 без генерации ошибок.

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

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

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

   • Выводит:
     • manager_name — ФИО менеджера.
     • total_cars_sold — общее количество проданных автомобилей.
     • total_commission — общая сумма комиссий менеджера.
   • Логика работы: объединяет данные из таблиц managers, cars и orders.

Результат

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

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

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

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

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

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

-- Пример успешного вызова: продажа автомобиля
CALL sell_car(1, 1, 1);

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

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

-- Проверка результата
SELECT * FROM orders WHERE client_id = 1 AND car_id = 1;

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

-- Пример вызова: вычисление комиссии менеджера
SELECT calculate_commission(1) AS commission;

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

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

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

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

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

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