AKochin lab3

Нижегородский Государственный Технический Университет
им. Р. Е. Алексеева
Кафедра ВСТ
Отчет по лабораторной работе №3
Базы Данных
Выполнил:
Кочин А.О
Проверил:
Супруненко А.В.
Нижний Новгород
2012 г.
Задание:
1. Спроектировать базу данных по заданию, приведенному в варианте №3 - составить ER-диаграмму (таблицы должны удовлетворять требованиям НФ3);
2. Реализовать БД в MySQL: использовать таблицы InnoDB, внешние ключи;
3. Заполнить таблицы данными, проверить обеспечение целостности данных;
4. Создать представления для запросов выборки. Выполнение:
1. Спроектирована база данных согласно заданию. ER-диаграмма приведена ниже:
2. Создана БД "AviaCompany":
Были созданы все необходимые таблицы при помощи следующего скрипта:
createTables_3.sql:
CREATE TABLE Planes
(
id SMALLINT,
name VARCHAR(45),
type ENUM('Wide-body',
'Narrow-body',
'Regional',
'Local'),
PRIMARY KEY(id)
)
ENGINE = InnoDb;
DESCRIBE Planes;
CREATE TABLE Flights
(
id SMALLINT,
planes_id SMALLINT,
direction VARCHAR(45),
flightDuration TIME,
flightDate DATE,
PRIMARY KEY(id),
FOREIGN KEY(planes_id) REFERENCES Planes(id)
ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = InnoDb;
DESCRIBE Flights;
CREATE TABLE Staff
(
id SMALLINT,
firstName VARCHAR(45),
lastName VARCHAR(45),
position_st ENUM('First pilot',
'Second pilot',
'Flight attendant',
'Flight mechanic',
'Airborne radio operator'),
flightHours INT,
PRIMARY KEY(id)
)
ENGINE = InnoDb;
DESCRIBE Staff;
CREATE TABLE Staff_and_Flights
(
staff_id SMALLINT,
flights_id SMALLINT,
FOREIGN KEY(staff_id) REFERENCES Staff(id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(flights_id) REFERENCES Flights(id)
ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = InnoDb;
DESCRIBE Staff_and_Flights;
3. Таблицы были заполнены данными из следующих файлов:
planeData.txt
0Beechcraft Baron4
1Saab 20003
2An-243
3Yak-403
4Boeing-7472
5Tu-1542
6A3202
7A3801
8Boeing-7771
flightsData.txt
08Moscow00:07:002012-04-21
18Kazan00:04:302012-05-05
37Tomsk00:08:302012-05-06
46Volgograd00:05:002012-05-07
56St. Petersburg00:04:302012-05-08
65Astrahan00:12:002012-05-09
74Tula00:13:002012-05-10
83Samara00:08:002012-05-11
92Pskov00:11:002012-05-12
101Novgogrod00:02:302012-05-13
staffData.txt
0JohnFly1520
1MikeDeadLoop2220
2AlexGimmebeer3120
3NickCrashmaster4720
4DanielRadar5720
5TylerPowerflight1570
6DanGrey2100
7JuliaGimmesnacks3450
8GeorgeMacfly4500
9RyanCell5720
sAndFData.txt
00
10
20
30
40
51
11
03
13
54
14
05
15
56
16
07
67
58
18
09
69
010
110
Следующим шагом необходимо проверить целостность данных.
В таблице Flights нет рейса с id=2, произведём попытку обновления таблицы Staff_and_Flights, ссылаясь на несуществующий рейс:
Как видно из результата, то нельзя добавить ссылку на несуществующий ключ из внешней таблицы.
Далее, попробуем удалить рейс из таблицы Flights:
Видно, что после удаления из таблицы Flights также удалилась соответствующая запись в таблице Staff_and_Flights.
Произведём проверку целостности данных между таблицами Staff и Staff_and_Flights. Обновим id у первого пилота по имени John:
4. Создадим представления для вывода отчётов согласно заданию. Используем следующий подготовленный скрипт файл:
createViews_3.sql
# List of all staff with sum flight hours
CREATE VIEW allStaff AS
(
SELECT firstName AS 'First Name', lastName AS 'Last Name',
flightHours AS 'Flight Hours'
FROM Staff
)
UNION ALL
(
SELECT "-----------","------------","------------" FROM Staff LIMIT 1
)
UNION ALL
(
SELECT "","Sum Hours", SUM(flightHours) FROM Staff
)
;
SELECT * FROM allStaff;
# Candidates to first pilots
CREATE VIEW candidates AS
(
SELECT firstName AS 'First Name', lastName AS 'Last Name',
flightHours AS 'Flight Hours'
FROM Staff
WHERE (flightHours > 200) AND (position_st = 2)
);
SELECT * FROM candidates;
# Planes with appropriated first pilots
CREATE VIEW planesWithPilots AS
(
SELECT Planes.id AS 'Planes ID', Planes.name AS 'Plane Name',
Staff.firstName AS 'First Name', Staff.lastName AS 'Last Name'
FROM (Staff_and_Flights JOIN Staff ON Staff.id=Staff_id) JOIN
(Flights JOIN Planes ON Planes.id=planes_id) ON Flights_id=Flights.id WHERE Staff.position_st = 1
ORDER BY Planes.id
);
SELECT * FROM planesWithPilots;
2