Нормализация отношений: схема бд
Был составлен список связей таблиц, который представлен в таблице №16.
Таблица №16 – Список связей таблиц
№ |
Название связи |
Сущности, участвующие в связи |
Назначение |
1 |
1:М |
Авиакомпании-Рейсы |
Каждая авиакомпания может иметь несколько рейсов, но каждый рейс может принадлежать только одной авиакомпании. |
2 |
1:М |
Самолёты-Рейсы |
Каждый самолет может использоваться для нескольких полетов, но в каждом рейсе может быть только один самолет. |
3 |
1:М |
Экипажи-Сотрудники |
Каждый экипаж может иметь несколько сотрудников, но каждый сотрудник может входить в состав только одного экипажа. |
4 |
1:М |
Экипажи-Рейсы |
Каждый экипаж может быть назначен на несколько рейсов, но в каждом полете может быть только один экипаж. |
5 |
1:М |
Рейсы-Билеты |
Каждый рейс может иметь несколько билетов, но каждый билет может принадлежать только одному рейсу. |
6 |
1:М |
Пассажиры-Билеты |
Каждый пассажир может иметь несколько билетов, но каждый билет может принадлежать только одному пассажиру. |
Была построена схема данных базы данных, которая представлена на рисунке 2.
Рисунок
2 – Схема данных
Все отношения (соответствующие сущностям) в предварительной схеме данных были приведены к третьей нормальной форме.
Запросы
В среде MySQL Workbench была создана база данных, а также описанные выше таблицы с атрибутами, в каждую таблицу были введены данные. Создание базы данных представлено в листинге 1. Создание таблиц представлено в приложении 1. Добавление данных в таблице представлено в приложении 2. Скриншоты заполненных таблиц в БД представлены в приложении 3.
Листинг 1 – Создание базы данных
create database airport; |
Были написаны запросы, которые могут быть полезны работы с данными, представленными в таблицах.
Запрос 1 выводит информацию о пассажирах по данному вылету. Запрос 1 представлен в листинге 2. Можно заменить flight_id на любой фактический ID рейса, для которого хотитим получить информацию о пассажирах. Этот запрос использует оператор JOIN для объединения данных из таблиц "tiket" и "passangers" на основе общего атрибута "pass_id", а затем фильтрует результаты, чтобы показать только те строки, в которых атрибут "flight_id" соответствует указанному значению. Результат выполнения запроса представлен на рисунке 3.
Листинг 2 – Запрос 1
SELECT passangers.pass_id, name, gender, birthday, passport, nationality FROM tikets JOIN passangers ON tikets.pass_id = passangers.pass_id WHERE flight_id = 25; |
Рисунок
3 – Результат выполнения запроса 1
Запрос 2 выводит список самолетов по годам выпуска в заданном интервале конкретной авиакомпании. Запрос 2 представлен в листинге 3. Этот запрос использует оператор JOIN для объединения данных из таблиц "planes" и "flights" на основе общего атрибута "plane_id", а затем фильтрует результаты, чтобы показать строки, в которых атрибут "year" находится в указанном диапазоне, а атрибут "airline_code" соответствует заданному коду. Результат выполнения запроса представлен на рисунке 4.
Листинг 3 – Запрос 2
SELECT planes.plane_id, type, year, seats, firm FROM planes JOIN flights ON planes.plane_id = flights.plane_id WHERE year >= 2000 AND year <= 2010 AND airline_code = 'DP' |
Рисунок
4 – Результат выполнения запроса 2
Запрос 3 выводит список экипажа на данный рейс. Запрос 3 представлен в листинге 4. Этот запрос использует операторы JOIN для объединения данных из таблиц "employees", "crews" и "flights" на основе их общих атрибутов "crew_id" соответственно, а затем фильтрует результаты, чтобы показать только те строки, в которых атрибут "flight_id" соответствует указанному значению. Результат выполнения запроса представлен на рисунке 5.
Листинг 4 – Запрос 3
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 = 36; |
Рисунок
5 – Результат выполнения запроса 3
Запрос 4 выводит проданные билеты по каждой авиакомпании за определенный период. Запрос 4 представлен в листинге 5. Этот запрос использует операторы JOIN для объединения данных из таблиц "tikets", "flights" и "airlines" на основе их общих атрибутов "flight_id" и "airline_code" соответственно. Затем он фильтрует результаты, чтобы показать только те строки, в которых атрибут "sale_date" находится между указанными начальной и конечной датами. Затем запрос группирует результаты по атрибуту "airlines.name" и подсчитывает количество ticket_id для каждой группы. Результат выполнения запроса представлен на рисунке 6.
Листинг 5 – Запрос 4
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 '2022.11.01' AND '2022.12.01' GROUP BY airlines.name |
Рисунок
6 – Результат выполнения запроса 4
Запрос 5 выводит забронированные места на пассажира. Запрос 5 представлен в листинге 6. Этот запрос использует оператор JOIN для объединения данных из таблиц "tikets" и "passangers" на основе их общего атрибута "pass_id". Затем результаты группируются по атрибуту "passangers.name" и подсчитывается количество мест для каждой группы. Результат выполнения запроса представлен на рисунке 7.
Листинг 6 – Запрос 5
SELECT passangers.name, COUNT(tikets.seat) as reserved_seats FROM tikets JOIN passangers ON tikets.pass_id = passangers.pass_id GROUP BY passangers.name |
Рисунок
7 – Результат выполнения запроса 5
Запрос 6 выводит информация о рейсах в данный пункт назначения. Запрос 6 представлен в листинге 7. Этот запрос использует операторы JOIN для объединения данных из таблиц "flights", "airlines" и "planes" на основе их общих атрибутов "airline_code" и "plane_id" соответственно. Затем он фильтрует результаты, чтобы показать только те строки, в которых атрибут "des_city" совпадает с указанным пунктом назначения. Результат выполнения запроса представлен на рисунке 8.
Листинг 7 – Запрос 6
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 = 'Сочи' |
Рисунок
8 – Результат выполнения запроса 6
Запрос 7 выводит свободные места на данный рейс и дату. Запрос 7 представлен в листинге 8. Этот запрос использует оператор JOIN для объединения данных из таблиц "самолеты" и "рейсы" на основе их общего атрибута "plane_id" и оператор LEFT JOIN для объединения данных из таблицы "tikets" на основе общего атрибута "flight_id". Затем он фильтрует результаты, чтобы показать только те строки, в которых атрибут "flight_id" соответствует указанному рейсу, а атрибут "book_date" соответствует определенной дате.
Затем вычисляется количество свободных мест путем вычитания количества забронированных мест из общего количества мест в самолете. Результат выполнения запроса представлен на рисунке 9.
Листинг 8 – Запрос 7
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 = 32 AND tikets.book_date = '2022-08-04' GROUP BY flights.flight_id |
Рисунок
9 – Результат выполнения запроса 7
Запрос 8 выводит общий доход каждой авиакомпании за определенный период. Запрос 8 представлен в листинге 9. Этот запрос использует операторы JOIN для объединения данных из таблиц "tikets", "flights" и "airlines" на основе их общих атрибутов "flight_id" и "airline_code" соответственно. Затем он фильтрует результаты, чтобы показать только те строки, в которых атрибут "sale_date" находится между указанными начальной и конечной датами. Затем запрос группирует результаты по атрибуту "airlines.name" и суммирует стоимость билета для каждой группы. Результат выполнения запроса представлен на рисунке 10.
Листинг 9 – Запрос 8
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 '2022.11.01' AND '2022.12.01' GROUP BY airlines.name |
Рисунок
10 – Результат выполнения запроса 8
Запрос 9 выводит среднюю продолжительность полета для каждого типа самолета. Этот запрос использует оператор JOIN для объединения данных из таблиц "flights" и "planes" на основе их общего атрибута "plane_id". Затем результаты группируются по атрибуту "planes.type" и вычисляется средняя продолжительность полета для каждой группы. Запрос 9 представлен в листинге 10. Результат выполнения запроса представлен на рисунке 11.
Листинг 10 – Запрос 9
SELECT planes.type, AVG(flights.time) as avg_duration FROM flights JOIN planes ON flights.plane_id = planes.plane_id GROUP BY planes.type |
Рисунок
11 – Результат выполнения запроса 9
Запрос 10 выводит количество рейсов, которые были задержаны для каждой авиакомпании. Запрос 10 представлен в листинге 11. Этот запрос использует оператор JOIN для объединения данных из таблиц "flights" и "airlines" на основе их общего атрибута "airline_code". Затем он фильтрует результаты, чтобы показать только те строки, в которых время вылета больше, чем время полета. Затем запрос группирует результаты по атрибуту "airlines.name" и подсчитывает количество рейсов для каждой группы. Результат выполнения запроса представлен на рисунке 12.
Листинг 11 – Запрос 10
SELECT airlines.name, COUNT(flights.flight_id) as delayed_flights FROM flights JOIN airlines ON flights.airline_code = airlines.airline_code WHERE flights.dep_time > flights.time GROUP BY airlines.name |
Рисунок
12 – Результат выполнения запроса 10