МИНИСТЕРСТВО НАУКИ И ВЫСШЕГО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ

федеральное государственное автономное образовательное учреждение высшего образования

«САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ»

ИНСТИТУТ НЕПРЕРЫВНОГО И ДИСТАНЦИОННОГО ОБРАЗОВАНИЯ

КАФЕДРА 41

ОЦЕНКА

ПРЕПОДАВАТЕЛЬ

канд.тех.наук, доцент				Е. Л. Турнецкая
должность, уч. степень, звание		подпись, дата		инициалы, фамилия

ОТЧЕТ О ЛАБОРАТОРНОЙ РАБОТЕ №2

Связь СУБД MySQL и R. Визуализация данных в R.

по дисциплине: Базы данных

РАБОТУ ВЫПОЛНИЛ

СТУДЕНТ ГР. №	Z9411				Р. С. Кафка
	номер группы		подпись, дата		инициалы, фамилия
Студенческий билет №	2019/3603

Санкт-Петербург 2023

СОДЕРЖАНИЕ

1. Цель работы 3

2. Вариант задания 3

2.1. Тема работы 3

2.2. Описание предметной области 3

2.3. Схема данных 6

3. Ход работы 7

3.1. Код для запроса на выборку и скриншот результата 7

3.2. Текстовое описание созданных запросов, SQL-код, скриншоты полученных графиков и пояснения к ним 8

ЗАКЛЮЧЕНИЕ 12

СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ 13

ПРИЛОЖЕНИЕ А 14

1. Цель работы

Произвести связь базы данных в MySQL и R, извлечь данные из таблиц базы данных и выполнить анализ данных в БД с помощью визуализации в R.

2. Вариант задания

   1. Тема работы

Обслуживание рейсов в аэропорту.

2. Описание предметной области

Аэропорт обслуживает рейсы разных авиакомпаний. У каждой авиакомпании есть несколько рейсов. Авиакомпании предоставляют самолеты различного типа (вместимости). Самолеты характеризуются типом, годом выпуска, количеством мест и фирмой–производителем. В один город могут быть несколько рейсов в разное время, осуществляемых различными авиакомпаниями. Самолеты обслуживаются экипажем: командир корабля, второй пилот, штурман, бортинженер и стюардессы. О сотрудниках хранится следующая информация: Ф.И.О., должность, квалификация, экипаж. Рейс имеет свой номер, пункт отправления, пункт прибытия, время вылета, время в пути, тип самолета, название авиакомпании, обслуживающей данный рейс. Билет на самолет имеет свой номер и № рейса, ФИО пассажира, № места, стоимость, дата продажи, дата бронирования. Пассажир, приобретая билет на самолет, сообщает о себе паспортные данные. Клиент может забронировать билет по Интернету и выкупить его за 3 часа до вылета.

В результате проведённого анализа предметной области базы данных «Обслуживание рейсов в аэропорту» легко перечислить основные сущности этой БД. В реляционную модель проектированной БД будут входить следующие таблицы: Авиакомпании, Самолёты, Рейсы, Сотрудники, Билеты, Пассажиры, Экипажи. Список сущностей представлен в таблице №1.

Таблица №1 – Список сущностей

№	Название	Назначение
1	Авиакомпании	Информация об авиакомпаниях, которые обслуживает аэропорт
2	Самолёты	Информация о самолётах, предоставляемых авиакомпаниями
3	Сотрудники	Информация о сотрудниках самолёта
4	Рейсы	Информация о рейсах
5	Билеты	Данные о рейсе для пассажиров и аэропорта
6	Пассажиры	Данные о пассажирах
7	Экипажи	Данные об экипажах

Для каждой таблицы (сущности) были приведены описания её атрибутов. Атрибуты – колонки таблицы, которые выражают определённое свойство. Списки атрибутов для сущностей представлены в таблице №2, таблице №3, таблице №4, таблице №5, таблице №6, таблице №7, таблице №8 соответственно.

Таблица №2 – Описание атрибутов сущности Авиакомпании

Название	Назначение	Тип данных	Может быть NaN
Код авиакомпании	Идентификатор авиакомпании	Текстовый	Нет
Название авиакомпании	Название авиакомпании	Текстовый	Нет
Дата основания	Дата основания авиакомпании	Дата	Нет
Штаб-квартира	Штаб-квартира авиакомпании	Текстовый	Нет

Таблица №3 – Описание атрибутов сущности Самолёты

Название	Назначение	Тип данных	Может быть NaN
ID самолёта	Идентификация самолёта	Числовой	Нет
Тип самолёта	Тип самолёта	Текстовый	Нет
Год выпуска	Год выпуска самолёта	Текстовый	Нет
Количество мест	Количество мест в самолёте для пассажиров	Числовой	Нет
Фирма-производитель	Фирма-производитель, которая построила самолёт	Текстовый	Нет

Таблица №4 – Описание атрибутов сущности Сотрудники

Название	Назначение	Тип данных	Может быть NaN
ID сотрудника	Идентификационный номер сотрудника	Числовой	Нет
ФИО сотрудника	Ф.И.О. сотрудника аэропорта	Текстовый	Нет
Должность	Должность сотрудника	Текстовый	Нет
Квалификация	Квалификация сотрудника	Текстовый	Нет
Номер экипажа	К какому экипажу принадлежит сотрудник	Числовой	Нет

Таблица №5 – Описание атрибутов сущности Рейсы

Название	Назначение	Тип данных	Может быть NaN
Номер рейса	Номер рейса для идентификации	Числовой	Нет
Пункт отправления	Пункт отправления рейса	Текстовый	Нет
Пункт прибытия	Пункт прибытия рейса	Текстовый	Нет
Время вылета	Время вылета самолёта	Дата	Нет
Время в пути	Время в пути рейса, указано в количестве минут	Числовой	Нет
ID самолёта	ID самолёта, обслуживающий рейс	Текстовый	Нет
Код авиакомпании	Код авиакомпании, обслуживающий рейс	Текстовый	Нет
Номер экипажа	Номер экипажа, который обслуживает самолёт	Числовой	Нет

Таблица №6 – Описание атрибутов сущности Билеты

Название	Назначение	Тип данных	Может быть NaN
Номер билета	Номер билета для идентификации	Числовой	Нет
Номер рейса	Номер рейса для идентификации	Числовой	Нет
ID пассажира	Идентификация номера пассажира	Текстовый	Нет
Номер места	Номер рейса на самолёт	Текстовый	Нет
Стоимость	Стоимость билета	Числовой	Нет
Дата продажи	Дата продажи билета	Дата	Нет
Дата бронирования	Дата бронирования билета	Дата	Да

Таблица №7 – Описание атрибутов сущности Пассажиры

Название	Назначение	Тип данных	Может быть NaN
ID пассажира	Идентификация номера пассажира	Числовой	Нет
ФИО пассажира	ФИО пассажира по паспорту	Текстовый	Нет
Пол	Пол пассажира	Текстовый	Нет
Дата рождения	Дата рождения пассажира	Дата	Нет
Серия/номер паспорта	Серия/номер паспорта пассажира	Текстовый	Нет
Гражданство	Гражданство пассажира	Текстовый	Нет

Таблица №8 – Описание атрибутов сущности Экипажи

Название	Назначение	Тип данных	Может быть NaN
Номер экипажа	Идентификация номера экипажа	Числовой	Нет

3. Схема данных

Схема данных базы данных представлена на рисунке 1.

Рисунок 1 – Схема данных

Скриншоты заполненных таблиц в БД представлены в приложении А.

3. Ход работы

Чтобы выполнить запрос в R из базы данных "аэропорт", сначала необходимо установить и загрузить библиотеку RMySQL. Это можно сделать, выполнив следующий код, представленный в листинге 1.

Листинг 1 – Установка и загрузка библиотеки RMySQL

install.packages("RMySQL") library(RMySQL)

Далее необходимо установить соединение с базой данных MySQL с помощью функции dbConnect(). В качестве аргументов необходимо указать имя хоста, имя пользователя, пароль и имя базы данных. Код представлен в листинге 2.

Листинг 2 – Соединение R с базой данных MySQL

con <- dbConnect(MySQL(), host = "127.0.0.1", user = "root", password = "admin", dbname = "airport")

1. Код для запроса на выборку и скриншот результата

После установления соединения мы можем запустить запрос на выборку всех данных из таблицы сотрудников с помощью функции dbGetQuery(), как показано в листинге 3.

Листинг 3 – Запрос на выборку всех данных из таблицы сотрудников

employees_data <- dbGetQuery(con, "SELECT * FROM employees")

Мы можем отобразить данные в консоли R, просто запустив имя переменной. Код представлен в листинге 4, результат представлен на рисунке 2.

Листинг 4 – Запуск имени переменной

employees_data

Рисунок 2 – Вывод результатов запроса на выборку всех данных таблицы «сотрудники»

2. Текстовое описание созданных запросов, sql-код, скриншоты полученных графиков и пояснения к ним

В Листинге 5 представлен программный код подключения к базе данных и построения гистограммы, показывающей количество мест в разных типах самолётов на основе данных, хранящихся таблице planes.

На рисунке 3 представлен пример полученной гистограммы, где по оси х отображены типы самолётов, а по оси y – количество мест.

Листинг 5 – Количество мест в разных типах самолётов

library(RMySQL) library(ggplot2) library(plotly) # Соединение с БД con <- dbConnect(RMySQL::MySQL(), host = "127.0.0.1", username = "root", password = "admin", dbname = "airport") # Запрос 1 res1 <- dbSendQuery(con, "SELECT * FROM planes") stat <- dbFetch(res1) dbClearResult(res1) # Построение гистограммы: p <- ggplot(stat, aes(x=type, y=seats, fill=seats)) +geom_bar(stat="identity") + ylab("Количество мест") +xlab("Тип самолёта") +ggtitle("Количество мест в самолётах") +theme_bw() +theme(legend.position="none") p

Рисунок 3 – Количество мест в разных типах самолётов

В Листинге 6 представлен программный код подключения к базе данных и построения гистограммы, показывающей количество сотрудников разных должностей на основе данных, хранящихся таблице employees.

На рисунке 4 представлен пример полученной гистограммы, где по оси х отображены типы должности, а по оси y – количество сотрудников.

Листинг 6 – Количество сотрудников разных должностей

library(RMySQL) library(ggplot2) library(plotly) # Соединение с БД con <- dbConnect(RMySQL::MySQL(), host = "127.0.0.1", username = "root", password = "admin", dbname = "airport") # Запрос 2 res2 <- dbSendQuery(con, "SELECT * FROM employees") stat <- dbFetch(res2) dbClearResult(res2) tbl <- as.data.frame(table(stat$pos)) # Построение гистограммы: p <- ggplot(tbl, aes(x=Var1, y=Freq, fill=Var1)) +geom_bar(stat="identity") + ylab("Количество работников") +xlab("Должности") +ggtitle("Количество сотрудников по должностям") +theme_bw() +theme(legend.position="none") p

Рисунок 4 – Количество сотрудников разных должностей

В Листинге 7 представлен программный код подключения к базе данных и построения гистограммы, показывающей популярные рейсы в города на основе данных, хранящихся таблице employees.

Этот код создает соединение с базой данных MySQL с помощью функции dbConnect из пакета RMySQL с указанным хостом, именем пользователя, паролем и именем базы данных. Затем он извлекает данные из таблицы "flights", выполняя запрос для выбора столбца "des_city" и количества рейсов в каждый город, сгруппированных по городам, с помощью функции dbGetQuery.

Затем рассчитывается процент рейсов в каждый город путем деления количества рейсов на общее количество рейсов и умножения на 100. Затем с помощью пакета ggplot2 создается круговая диаграмма, где в качестве заливки используется des_city, а для обозначения процента рейсов в каждый город на диаграмме используется geom_text. Диаграмма также включает заголовок, метку оси x и метку оси y для наглядности.

На рисунке 5 представлен пример полученной гистограммы, где по оси х отображены типы должности, а по оси y – количество сотрудников.

Листинг 7 – Популярные рейсы в города

library(RMySQL) library(ggplot2) library(plotly) # Соединение с БД con <- dbConnect(RMySQL::MySQL(), host = "127.0.0.1", username = "root", password = "admin", dbname = "airport") # Запрос 3 flights_data <- dbGetQuery(con, "SELECT des_city, COUNT(des_city) as count FROM flights GROUP BY des_city ORDER BY count DESC") total_flights <- sum(flights_data$count) flights_data$percentage <- (flights_data$count / total_flights) * 100 # Построение диаграммы: ggplot(flights_data, aes(x = "", y = count, fill = des_city)) + geom_bar(width = 1, stat = "identity") + coord_polar("y", start = 0) + geom_text(aes(label = paste0(round(percentage, 2), "%")), position = position_stack(vjust = 0.5)) + ggtitle("Популярные рейсы в города") + xlab("") + ylab("")

Рисунок 5 – Популярные рейсы в города