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

База данных учёта сотрудников и проектов

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

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

Имя базы: company_db

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

employees — информация о сотрудниках
id full_name birth_date position
1 Иван Иванов 1985-02-14 аналитик
2 Петр Петров 1990-08-10 разработчик
3 Мария Смирнова 1987-05-22 тестировщик
projects — информация о проектах
id project_name start_date end_date
1 CRM-система 2023-01-01 2023-12-31
2 Веб-приложение 2023-03-01 NULL
employee_projects — участие сотрудников в проектах
id employee_id project_id role hours_worked
1 1 1 аналитик 120
2 2 1 разработчик 200
3 3 2 тестировщик 80
project_changes — журнал изменений (изначально пустая)
id project_id operation operation_time
change_logs — журнал изменений в системе
id entity_id old_value new_value change_time

id	full_name	birth_date	position
1	Иван Иванов	1985-02-14	аналитик
2	Петр Петров	1990-08-10	разработчик
3	Мария Смирнова	1987-05-22	тестировщик

id	project_name	start_date	end_date
1	CRM-система	2023-01-01	2023-12-31
2	Веб-приложение	2023-03-01	NULL

id	employee_id	project_id	role	hours_worked
1	1	1	аналитик	120
2	2	1	разработчик	200
3	3	2	тестировщик	80

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

company_db.sql

SET foreign_key_checks = 0;

CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

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

INSERT INTO employees (full_name, birth_date, position) VALUES
('Иван Иванов', '1985-02-14', 'аналитик'),
('Петр Петров', '1990-08-10', 'разработчик'),
('Мария Смирнова', '1987-05-22', 'тестировщик');

CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_name VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE DEFAULT NULL
);

INSERT INTO projects (project_name, start_date, end_date) VALUES
('CRM-система', '2023-01-01', '2023-12-31'),
('Веб-приложение', '2023-03-01', NULL);

CREATE TABLE employee_projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    project_id INT NOT NULL,
    role VARCHAR(100) NOT NULL,
    hours_worked INT DEFAULT 0,
    FOREIGN KEY (employee_id) REFERENCES employees (id),
    FOREIGN KEY (project_id) REFERENCES projects (id)
);

INSERT INTO employee_projects (employee_id, project_id, role, hours_worked) VALUES
(1, 1, 'аналитик', 120),
(2, 1, 'разработчик', 200),
(3, 2, 'тестировщик', 80);

CREATE TABLE project_changes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    operation VARCHAR(255) NOT NULL,
    operation_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects (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;

Задачи

Разработать хранимую процедуру assign_employee_to_project:
- Параметры:
  - p_employee_id — идентификатор сотрудника.
  - p_project_id — идентификатор проекта.
  - p_role — роль сотрудника.
  - p_hours — количество часов.
- Логика работы:
  - Проверяет существование сотрудника в таблице employees и проекта в таблице projects.
    - Если сотрудник или проект не найдены, возвращает ошибку:
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Сотрудник или проект не найдены';
  - Проверяет, не участвует ли сотрудник уже в указанном проекте.
    - Если сотрудник участвует в проекте, возвращает варнинг:
      SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT = 'Сотрудник уже участвует в проекте';
    - Данные при этом не добавляются.
  - Если проверки прошли успешно:
    - Добавляет запись в таблицу employee_projects.
    - Логирует успешное добавление записи в таблицу project_changes с операцией assign.
    - Все действия должны быть реализованы с использованием транзакции.
  - В случае любой другой ошибки откатывает транзакцию.
Создать пользовательскую функцию get_project_count_for_employee:
- Параметр:
  - p_employee_id — идентификатор сотрудника.
- Возвращает: количество проектов, в которых участвует сотрудник.
- Если сотрудник не найден, возвращает 0 как результат без генерации ошибок.
Создать триггер log_project_update для таблицы projects:
- Срабатывает:
  - При любом обновлении записи в таблице projects.
- Действие:
  - Логирует изменения в таблицу change_logs.
  - Добавляет в лог следующие данные:
    - entity_id — ID изменённого проекта.
    - old_value — предыдущее название проекта.
    - new_value — новое название проекта.
    - change_time — момент изменения.
Создать представление employee_project_summary:
- Выводит:
  - full_name — ФИО сотрудника.
  - position — должность.
  - project_name — название проекта.
  - hours_worked — количество отработанных часов.
- Логика работы: объединяет данные из таблиц employees, projects и employee_projects.

Результат

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

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

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

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

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

Пример содержимого файла task_1.sql

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

-- Пример успешного вызова: добавление сотрудника в проект
CALL assign_employee_to_project(1, 2, 'аналитик', 50);

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

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

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

-- Проверка результата
SELECT * FROM employee_projects WHERE employee_id = 1 AND project_id = 2;

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

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

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

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

-- Пример вызова: обновление данных о проекте
UPDATE projects
SET project_name = 'Новая CRM-система'
WHERE id = 1;

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

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

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