Анализ разрешений и запретов на операции с табличными данными для различных пользователей
В данной ИС будут присутствовать следующие пользователи:
Менеджер по организации рейсов заключает договоры с авиакомпаниями;
Кассир принимает наличные деньги и оформляет финансовые документы;
Системный администратор имеет полное право доступа к БД, отвечает за её работу.
Общий пример разрешений и запретов:
Менеджер по организации рейсов:
Может просматривать данные во всех таблицах
Может добавлять новые записи в таблицу "рейсы" и "авиакомпании"
Может обновлять данные в таблице "рейсы" и "авиакомпании"
Не может удалять записи ни в одной из таблиц
Кассир:
Может просматривать данные в таблицах "пассажиры", "рейсы" и "тикеты".
Может добавлять новые записи в таблицу "tikets"
Может обновлять данные в таблице "tikets".
Не может удалять записи ни в одной из таблиц
Системный администратор:
Может просматривать, добавлять, обновлять и удалять записи во всех таблицах.
Были созданы 3 пользователя: Менеджер по организации рейсов (flight_manager), Кассир(cashier), Системный администратор(admin). Создание пользователей и их доступов представлено в листинге 12. Результат создания пользователей представлен на рисунке 13.
Листинг 12 – Создание пользователей
CREATE USER 'flight_manager' IDENTIFIED BY 'flight123';
GRANT SELECT ON airport.* TO 'flight_manager'; GRANT SELECT, INSERT, UPDATE ON airport.flights TO 'flight_manager'; GRANT SELECT, INSERT, UPDATE ON airport.airlines TO 'flight_manager';
CREATE USER 'cashier' IDENTIFIED BY 'cashier123';
GRANT SELECT, INSERT, UPDATE ON airport.passangers TO 'cashier'; GRANT SELECT, INSERT, UPDATE ON airport.flights TO 'cashier'; GRANT SELECT, INSERT, UPDATE ON airport.tikets TO 'cashier';
CREATE USER 'admin' IDENTIFIED BY 'admin123';
GRANT ALL PRIVILEGES ON *.* TO 'admin'; |
Рисунок
13 – Список пользователей и их привилегий
Проектирование пользовательского интерфейса
6.1 Реализация всех запросов через вызовы хранимых процедур
Все запросы были реализованы через вызовы хранимых процедур.
Реализация запроса 1, вывод информации о пассажирах по данному вылету, в процедуру pr1 представлена в листинге 13. Примеры вызова процедуры представлены на рисунках 14 и на рисунках 15.
Листинг 13 – Реализация запроса 1 в процедуру pr1
DELIMITER $$ CREATE PROCEDURE `pr1`(IN flight_id INT) BEGIN SELECT passangers.name, passangers.gender, passangers.passport, passangers.birthday, tikets.seat, tikets.cost, tikets.sale_date FROM passangers JOIN tikets ON passangers.pass_id = tikets.pass_id WHERE tikets.flight_id = flight_id;
IF NOT EXISTS (SELECT * FROM tikets WHERE tikets.flight_id = flight_id) THEN SELECT 'Данные по этому рейсу не найдены' as 'Error'; END IF; END $$ DELIMITER ; |
Рисунок
14 – Пример вызова процедуры pr1 при
входящей переменной равной
«1»
Рисунок
15 – Пример вызова процедуры pr1 при
входящей переменной равной
«20»
Реализация запроса 2, вывод списока самолетов по годам выпуска в заданном интервале конкретной авиакомпании, в процедуру pr2 представлена в листинге 14. Примеры вызова процедуры представлены на рисунках 16 и на рисунках 17.
Листинг 14 – Реализация запроса 2 в процедуру pr2
DELIMITER $$ CREATE PROCEDURE `pr2`(IN start_year INT, IN end_year INT, IN airline_code VARCHAR(2)) BEGIN SELECT planes.plane_id, planes.type, planes.year, planes.seats, planes.firm FROM planes JOIN flights ON planes.plane_id = flights.plane_id WHERE year >= start_year AND year <= end_year AND flights.airline_code = airline_code;
IF NOT EXISTS (SELECT * FROM planes JOIN flights ON planes.plane_id = flights.plane_id WHERE planes.year BETWEEN start_year AND end_year AND flights.airline_code = airline_code) THEN SELECT 'Данные по этому году и авиакомпании не найдены' as 'Error'; END IF; END $$ DELIMITER ; |
Рисунок
16 – Пример вызова процедуры pr2 при
входящих переменных равными
«2010», «2020» и «DP»
Рисунок
17 – Пример вызова процедуры pr2 при
входящих переменных равными
«2000», «2020» и «DP»
Реализация запроса 3, вывод списока экипажа на данный рейс, в процедуру pr3 представлена в листинге 15. Примеры вызова процедуры представлены на рисунках 18 и на рисунках 19.
Листинг 15 – Реализация запроса 3 в процедуру pr3
DELIMITER $$ CREATE PROCEDURE `pr3`(IN flight_id INT) BEGIN SELECT emp_id, name, pos, skill FROM employees JOIN crews ON employees.crew_id = crews.crew_id JOIN flights ON crews.crew_id = flights.crew_id WHERE flights.flight_id = flight_id;
IF NOT EXISTS (SELECT * FROM flights WHERE flights.flight_id = flight_id) THEN SELECT 'Данные по этому рейсу не найдены' as 'Error'; END IF; END $$ DELIMITER ; |
Рисунок
18 – Пример вызова процедуры pr3 при
входящей переменной равной
«36»
Рисунок
19 – Пример вызова процедуры pr3 при
входящей переменной равной
«99»
Реализация запроса 4, вывод проданных билетов по каждой авиакомпании за определенный период, в процедуру pr4 представлена в листинге 16. Примеры вызова процедуры представлены на рисунках 20 и на рисунках 21.
Листинг 16 – Реализация запроса 4 в процедуру pr4
DELIMITER $$ CREATE PROCEDURE `pr4`(IN start_date DATE, IN end_date DATE) BEGIN SELECT airlines.name, COUNT(ticket_id) as tickets_sold FROM tikets JOIN flights ON tikets.flight_id = flights.flight_id JOIN airlines ON flights.airline_code = airlines.airline_code WHERE tikets.sale_date BETWEEN start_date AND end_date GROUP BY airlines.name;
IF NOT EXISTS (SELECT * FROM tikets WHERE tikets.sale_date BETWEEN start_date AND end_date) THEN SELECT 'Данные за этот период не найдены' as 'Error'; END IF; END $$ DELIMITER ; |
Рисунок
20 – Пример вызова процедуры pr4 при
входящих переменных равными
«2022-11-01» и «2022-12-01»
Рисунок
21 – Пример вызова процедуры pr4 при
входящих переменных равными
«2021-11-01» и «2021-12-01»
Реализация запроса 5, вывод забронированных мест на пассажира, в процедуру pr5 представлена в листинге 17. Примеры вызова процедуры представлены на рисунках 22 и на рисунках 23.
Листинг 17 – Реализация запроса 5 в процедуру pr5
DELIMITER $$ CREATE PROCEDURE `pr5`(IN pass_id INT) BEGIN SELECT passangers.pass_id, passangers.name, COUNT(tikets.seat) as reserved_seats FROM tikets JOIN passangers ON tikets.pass_id = passangers.pass_id WHERE tikets.pass_id = pass_id GROUP BY passangers.name;
IF NOT EXISTS (SELECT * FROM tikets WHERE tikets.pass_id = pass_id) THEN SELECT 'Данные по этому пассажиру не найдены' as 'Error'; END IF;
END $$ DELIMITER ; |
Рисунок
22 – Пример вызова процедуры pr5 при
входящей переменной равной
«13»
Рисунок
23 – Пример вызова процедуры pr5 при
входящей переменной равной
«68»
Реализация запроса 6, вывод информации рейсах в данный пункт назначения, в процедуру pr6 представлена в листинге 18. Примеры вызова процедуры представлены на рисунках 24 и на рисунках 25.
Листинг 18 – Реализация запроса 6 в процедуру pr6
DELIMITER $$ CREATE PROCEDURE `pr6`(IN des_city VARCHAR(255)) BEGIN SELECT flights.flight_id, airlines.name, flights.dep_city, flights.dep_time, flights.time, planes.type FROM flights JOIN airlines ON flights.airline_code = airlines.airline_code JOIN planes ON flights.plane_id = planes.plane_id WHERE flights.des_city = des_city;
IF NOT EXISTS (SELECT * FROM flights WHERE flights.des_city = des_city) THEN SELECT 'Не найдено ни одного рейса в этом направлении' as 'Error'; END IF;
END $$ DELIMITER ; |
Рисунок
24 – Пример вызова процедуры pr6 при
входящей переменной равной
«Сочи»
Рисунок
25 – Пример вызова процедуры pr6 при
входящей переменной равной
«Иркутск»
Реализация запроса 7, вывод свободных мест на данный рейс и дату, в процедуру pr7 представлена в листинге 19. Примеры вызова процедуры представлены на рисунках 26 и на рисунках 27.
Листинг 19 – Реализация запроса 7 в процедуру pr7
DELIMITER $$ CREATE PROCEDURE `pr7`(IN flight_id INT, IN book_date DATE) BEGIN SELECT planes.seats - COUNT(tikets.seat) as available_seats FROM planes JOIN flights ON planes.plane_id = flights.plane_id LEFT JOIN tikets ON flights.flight_id = tikets.flight_id WHERE flights.flight_id = flight_id AND tikets.book_date = book_date GROUP BY flights.flight_id;
IF NOT EXISTS (SELECT * FROM flights WHERE flights.flight_id = flight_id) THEN SELECT 'Не найдено ни одного рейса с таким идентификатором' as 'Error'; ELSEIF NOT EXISTS (SELECT * FROM tikets WHERE tikets.book_date = book_date) THEN SELECT 'Билеты на эту дату не найдены' as 'Error'; END IF;
END $$ DELIMITER ; |
Рисунок
26 – Пример вызова процедуры pr7 при
входящих переменных равными
«32» и «2022-08-04»
Рисунок
27 – Пример вызова процедуры pr7 при
входящих переменных равными
«32» и «2022-10-01»
Реализация запроса 8, вывод общего дохода каждой авиакомпании за определенный период, в процедуру pr8 представлена в листинге 20. Примеры вызова процедуры представлены на рисунках 28 и на рисунках 29.
Листинг 20 – Реализация запроса 8 в процедуру pr8
DELIMITER $$ CREATE PROCEDURE pr7(IN start_date DATE, IN end_date DATE) BEGIN SELECT airlines.name, SUM(tikets.cost) as total_revenue FROM tikets JOIN flights ON tikets.flight_id = flights.flight_id JOIN airlines ON flights.airline_code = airlines.airline_code WHERE tikets.sale_date BETWEEN start_date AND end_date GROUP BY airlines.name; IF (found_rows() = 0) THEN SELECT 'Не найдено данных для указанного диапазона дат' as 'Error'; END IF; END $$ DELIMITER ; |
Рисунок
28 – Пример вызова процедуры pr8 при
входящих переменных равными
«2022-11-01» и «2022-12-01»
Рисунок
29 – Пример вызова процедуры pr8 при
входящих переменных равными
«2022-08-01» и «2022-09-01»
Реализация запроса 9, вывод средней продолжительности полета для каждого типа самолета, в процедуру pr9 представлена в листинге 21. Примеры вызова процедуры представлены на рисунках 30 и на рисунках 31.
Листинг 21 – Реализация запроса 9 в процедуру pr9
DELIMITER $$ CREATE PROCEDURE pr9(IN planeType VARCHAR(255)) BEGIN
SELECT planes.type, AVG(flights.time) as avg_duration FROM flights JOIN planes ON flights.plane_id = planes.plane_id WHERE planes.type = planeType; IF (found_rows() = 0) THEN SELECT 'Нет результатов для данного типа самолета' as 'Error'; END IF; END $$ DELIMITER ; |
Рисунок
30 – Пример вызова процедуры pr9 при
входящей переменной равной
«Ил-96-300»
Рисунок
31 – Пример вызова процедуры pr9 при
входящей переменной равной
«Су-35»
Реализация запроса 10, вывод количества рейсов, которые были задержаны для конкретной авиакомпании, в процедуру pr9 представлена в листинге 22. Примеры вызова процедуры представлены на рисунках 32 и на рисунках 33.
Листинг 22 – Реализация запроса 10 в процедуру pr10
DELIMITER $$ CREATE PROCEDURE pr10(IN airline_name VARCHAR(255)) BEGIN DECLARE result INT; SELECT COUNT(flights.flight_id) INTO result FROM flights JOIN airlines ON flights.airline_code = airlines.airline_code WHERE flights.dep_time > flights.time AND airlines.name=airline_name;
IF result=0 THEN SELECT 'Задержанных рейсов для этой авиакомпании не найдено' AS 'Error'; ELSE SELECT airlines.name, COUNT(flights.flight_id) as delayedflights FROM flights JOIN airlines ON flights.airline_code = airlines.airline_code WHERE flights.dep_time > flights.time AND airlines.name = airline_name GROUP BY airlines.name; END IF; END $$ DELIMITER ; |
Рисунок
32 – Пример вызова процедуры pr10 при
входящей переменной равной
«S7»
Рисунок
33 – Пример вызова процедуры pr10 при
входящей переменной равной
«Test»