Выбор средств разработки базы данных для информационного функционирования аэропорта. Выделение и нормализация сущностей. Логическая схема и физическая структура базы данных. Спецификация и тестирование функций, процедур, триггеров, представлений.
Аннотация к работе
Необходимо разработать прототип программной системы «Аэропорт», содержащий не менее 5 взаимосвязанных таблиц, 5 процедур, 3 функций, 2 триггеров с использованием операторов (функций) ветвления и циклов. Проведем нормализацию сущностей: 1) 1NF - требует атомарности данных в таблицах, т.е. данные в таблицах должны быть представлены в виде минимально возможных и далее неделимых частей информации. -> ID INTEGER not null primary key,-> FIO VARCHAR (255) not nul,-> FLIGHTDATE DATE not null,-> SEATNUMBER INTEGER not null,-> Payed BOOLEAN not null,-> lastname VARCHAR (255) not null,-> name VARCHAR (255) not null,-> otchestvo VARCHAR (255) not null, Связываем таблицы alter table Flying add FOREIGN KEY(FK_ID_Plane) REFERENCES Airplanes(ID); SQL - код: reate procedure add_cl(in fam text,in name text, in fat text, in pr integer,in hor integer) begin insert into klients (Family_client,Name_client, Father_name_client) values (fam,name,fat); SQL - код: create view info (Family_client,Name_client,Father_name_client,Price, Name_horse) as select Family_client,Name_client,Father_name_client,Price,Name_horse from klients join horse join price on (klients.ID_client = price.ID_client) and (horse.ID_horse =price.ID_horse) group by Price order by Price desc|В ходе работы была разработана структурная база данных, содержащая 5 взаимосвязанных таблиц по информационной области «Ипподром». Функционал включил в себя 6 процедур, 4 триггера, 3 функции, 1 представление и 1 курсор.
Введение
Современная жизнь немыслима без эффективного управления. Важной категорией являются системы обработки информации, от которых во многом зависит эффективность работы любого предприятия ли учреждения.
Наверное, самой удобной системой обработки информации является база данных. Современные базы данных можно адаптировать под любые задачи. На любом производстве, в любой организации будет полезен быстрый удобный доступ к нужным данным, быстрое редактирование записей и реестров, выделение из множества информации самой нужной, получение общих и детализированных отчетов по итогам работы.
Для обеспечения этих функций созданы специализированные средства - системы управления базами данных (СУБД). Современные СУБД - многопользовательские системы управления базой данных, которые специализируется на управлении массивом информации одним или множеством одновременно работающих пользователей. Современные СУБД обеспечивают набор средств для поддержки таблиц и отношений между связанными таблицами - развитый пользовательский интерфейс, который позволяет вводить и модифицировать информацию, выполнять поиск и представлять информацию в графическом или текстовом режиме - средства программирования высокого уровня, с помощью которых можно создавать собственные приложения.
2. Постановка задачи
Необходимо разработать прототип программной системы «Аэропорт», содержащий не менее 5 взаимосвязанных таблиц, 5 процедур, 3 функций, 2 триггеров с использованием операторов (функций) ветвления и циклов. Также необходимо обосновать и реализовать как минимум 1 представление и 1 курсор.
Для выполнения работы необходимо использовать
· СУБД MYSQL 5.1
· система создания ER-диаграмм:Open Model Sphere
· язык программирования: SQL
· Среда программирования - клиент командной строки MYSQL
3. Проектирование БД
3.1 Описание предметной области
Предметная область курсовой работы представляет собой разработку базы данных для информационного функционирования аэропорта.
Аэропорт - комплекс сооружений, предназначенный для приема, отправки, базирования воздушных судов и обслуживания воздушных перевозок, имеющий для этих целей аэродром, аэровокзал (в крупных аэропортах нередко несколько аэровокзалов), один или несколько грузовых терминалов и другие наземные сооружения и необходимое оборудование.
База данных должна удовлетворять информационные потребности и содержать в себе информацию о полетах, самолетах, аэропортах. Также должна содержаться детальная информация о заказанных билетах на рейсы. Конкретно - Ф.И.О. заказчика, информация о его рейсе и статус оплаты.
3.2 Выделение и нормализация сущностей
Выделим основные сущности базы данных: · Самолеты
· Полеты
· Клиенты
Опишем каждую из сущностей: · Самолеты - содержит информацию о самолетах и посадочных местах в самолете
· Полеты - содержит информацию о рейсах, время прибытия и отбытия, аэропорт прибытия и отбытия и т. д.
· Клиенты - содержит информацию о копленных и забронированных билетах. А конкретно кем и на какой рейс куплен или забронирован билет.
Проведем нормализацию сущностей: 1) 1NF - требует атомарности данных в таблицах, т.е. данные в таблицах должны быть представлены в виде минимально возможных и далее неделимых частей информации.
Таблица 1.1 - Ключевая абстракция «Самолеты»
Характеристика Тип
Название самолета Строковый
Класс мест Целочисленный
Количество мест на каждый класс Целочисленный
Таблица 1.2 - Ключевая абстракция «Полеты»
Характеристика Тип
Самолет Строковый
Аэропорт отправления Строковый
Город аэропорта отправления Строковый
Страна аэропорта отправления Строковый
Аэропорт прибытия Строковый
Город аэропорта прибытия Строковый
Страна аэропорта прибытия Строковый
Время отправления Строковый
Время прибытия Строковый
Таблица 1.3 - Ключевая абстракция «Заказы»
ХАРАКТЕРИСТИКАТИП
Самолет Строковый
Аэропорт отправления Строковый
Город аэропорта отправления Строковый
Страна аэропорта отправления Строковый
Аэропорт прибытия Строковый
Город аэропорта прибытия Строковый
Страна аэропорта прибытия Строковый
Время отправления Время
Время прибытия Время
Дата отправления Дата
Класс мест Числовой целый
Количество билетов(мест) Числовой целый
Оплата Булево значение
Ф.И.О. Строковый
2) 2NF - требует, чтобы структура базы данных удовлетворяла требованиям 1NF и чтобы данные во всех не ключевых столбцах полностью зависели от первичного ключа или каждого поля первичного ключа, если он является составным.
Таблица 2.1 - Реляционная модель БД «Самолеты»
Самолеты
Номер Модель
Таблица 2.2 - Реляционная модель БД «Места»
Места
Номер Номер самолета Класс места Количество мест
Таблица 2.3 - Реляционная модель БД «Полеты»
Полеты
Номер Номер самолета Время взлета Время посадки Аэропорт отправления Аэропорт прибытия
Таблица 2.4 - Реляционная модель БД «Аэропорты»
Аэропорты
Номер Название Город Страна
Таблица 2.5 - Реляционная модель БД «Заказы»
Аэропорты
Номер Номер полета Дата полета Индиф. номер мест Количество мест Оплата (оплачено/нет)
3) 3NF - требует от нас, чтобы структура базы данных удовлетворяла требованиям 1NF и 2NF и чтобы все не ключевые столбцы таблицы зависели от первичного ключа, но были независимы друг от друга.
Таблица 3.1 - Самолеты
Поле Тип Назначение
ID INTEGER Идентификатор
PLANENAME VARCHAR(255) Модель самолета
Таблица 3.2 - Места в самолете
ПОЛЕТИПНАЗНАЧЕНИЕ
ID INTEGER Идентификатор
ID_Plane INTEGER Номер самолета
SEATCOUNT INTEGER Класс мест
SEATTYPE INTEGER Количество мест
Таблица 3.3 - Аэропорты
ПОЛЕТИПНАЗНАЧЕНИЕ
ID INTEGER Идентификатор
AIRPORTNAME VARCHAR(255) Название аэропорта
City VARCHAR(255) Город
Country VARCHAR(255) Страна
Таблица 3.4 - Полеты
Поле Тип Назначение
ID INTEGER Идентификатор
ID_Plane INTEGER Номер самолета
UPTIME TIME Время взлета
DOWNTIME TIME Время посадки
ID_AP_Up INTEGER Аэропорт отправления
ID_AP_Down INTEGER Аэропорт прибытия
Таблица 3.5 - Заказы
Поле Тип Назначение
ID INTEGER Идентификатор
ID_Flight INTEGER Номер полета
FLIGHTDATE DATE Дата полета
ID_Seats INTEGER Индиф. номер мест
SEATCOUNT INTEGER Количество мест
Payed Boolean Оплата (оплачено/нет)
FIO STRING
3.3 Логическая схема базы данных
ER - модель логического уровня
3.4 Обоснование связей между сущностями
1) Связь «Наездник» - «Таблица забегов»: один ко многим, так как участвует не один наездник.
2) Связь «Лошадь» - «Таблица забегов»: один ко многим, так как участвует не одна лошадь.
3) Связь «Клиенты» - «Ставки»: один ко многим, так как один клиент может сделать несколько ставок
4) Связь «Лошадь» - «Ставки»: один ко многим, так как на одну лошадь может быть несколько ставок.
4. Функциональная декомпозиция системы
Проведем функциональную декомпозицию системы. Для этого перечислим все хранимые процедуры и опишем их работу.
1 Сценарий взаимодействия пользователя с системой
Пользователь взаимодействует с СУБД через консоль. Он отправляет SELECT запросы на выборку данных из БД, в ответ он получает данные от сервера. Также для создания расширенных запросов пользователь может использовать хранимые процедуры.
2 Процедура count_orders подсчитывающая количество забронированных и оплаченных рейсов
3 Процедура fly_info выводит время вылета всех рейсов.
4 Процедура add_ord добавляет новый заказ на рейс в базу.
5 Процедура del_ord выполняет удаление записи из таблицы заказов.
6 Процедура change_stat меняет статус брони с не оплачено на оплачено
7 Процедура show_airport показывает все аэропорты указанного города
Функции: 8 Функция get_airplane показывает название самолета на котором должен лететь клиент.
9 Функция get_uptime показывает время отлета указанного рейса.
10 Функция order_num возвращает номер рейса по фамилии клиента
11 Завершение работы с системой
Для завершения работы с СУБД пользователь может использовать команду exit.
5. Реализация компонентов системы
5.1 Физическая структура базы данных
ER - модель физического уровня:
1 Таблица Airplanes
SQL - код: Create table Airplanes (
-> ID INTEGER not null primary key, -> Plane VARCHAR (255) not null
->);
2 Таблица Airports
SQL - код: Create table Airports (
-> ID INTEGER not null primary key, -> AIRPORTNAME VARCHAR (255) not nul, -> City VARCHAR (255) not nul, -> Country VARCHAR (255) not nul, ->);
3 Таблица Mesta
SQL - код: Create table Mesta (
-> ID INTEGER not null primary key, -> SEATCOUNT INTEGER not null, ->SEATTYPE INTEGER not null
->);
4 Таблица Klients
SQL - код: Create table Flying (
-> ID INTEGER not null primary key, -> UPTIME TIME not nul, -> DOWNTIME TIME not nul
->);
5 Таблица Orders
SQL - код: Create table Orders (
-> ID INTEGER not null primary key, -> FIO VARCHAR (255) not nul, -> FLIGHTDATE DATE not null, -> SEATNUMBER INTEGER not null, -> Payed BOOLEAN not null, -> lastname VARCHAR (255) not null, -> name VARCHAR (255) not null, -> otchestvo VARCHAR (255) not null, Связываем таблицы alter table Flying add FOREIGN KEY(FK_ID_Plane) REFERENCES Airplanes(ID);
alter table Flying add FOREIGN KEY(FK_ID_AP_Up) REFERENCES Airports(ID);
alter table Flying add FOREIGN KEY(FK_ID_AP_Down) REFERENCES Airports(ID);
alter table Mesta add FOREIGN KEY(FK_ID_Plane) REFERENCES Airplanes(ID);
alter table Orders add FOREIGN KEY(FK_ID_Flight) REFERENCES Flying(ID);
alter table Orders add FOREIGN KEY(FK_ID_Seats) REFERENCES Mesta(ID);
5.2 Спецификация функций
Перечислим функции ИС: 1 Функция get_airplane показывает название самолета на котором должен лететь клиент.
SQL - код: delimiter |
CREATE FUNCTION get_airplane(lstnm VARCHAR(255), nm VARCHAR(255), otch VARCHAR(255))
RETURNS VARCHAR(255)
BEGIN
RETURN (SELECT Plane FROM Airplanes WHERE ID IN (SELECT FK_ID_Plane FROM Flying WHERE ID IN (SELECT FK_ID_Flight FROM Orders WHERE lastname = lstnm AND name = nm AND otchestvo = otch)));
END|
2 Функция get_uptime показывает время отлета указанного рейса
SQL - код: CREATE FUNCTION get_uptime(reys_id INTEGER)
RETURNS TIME
BEGIN
RETURN (SELECT UPTIME FROM Flying WHERE ID = reys_id);
END|
3 Функция order_num возвращает номер рейса по фамилии клиента.
SQL - код: CREATE FUNCTION order_num(client_name VARCHAR(255))
RETURNS INTEGER
BEGIN
RETURN(SELECT `ID` FROM Orders WHERE lastname = client_name ORDER BY FLIGHTDATE DESC LIMIT 1);
END|
5.3 Спецификация процедур
Перечислим процедуры ИС: 1 Процедура count_orders подсчитывает количество оплаченных или не оплаченных заказов. На вход подается 0 или 1
2 Процедура fly_info выводит время вылета всех рейсов.
SQL - код: CREATE PROCEDURE fly_info()
BEGIN
SELECT Airplanes.Plane, Flying.ID, Flying.UPTIME FROM Airplanes RIGHT JOIN Flying ON Airplanes.ID=Flying.FK_ID_Plane;
END|
3 Процедура change_price, меняющая ставку клиента по id ставки используя курсор.
SQL - код: create procedure change_price(in id integer,in m2 integer) begin declare done integer default 0;
declare money integer;
declare cur cursor for select ID_price from price where ID_price = id;
declare continue handler for sqlstate "02000" set done=1;
open cur;
while done=0 do fetch cur into money;
update price set Price = m2 where ID_price = money;
end while;
close cur;
end|
4 Процедура add_cl добавляющая клиента, а также его ставку.
SQL - код: reate procedure add_cl(in fam text,in name text, in fat text, in pr integer,in hor integer) begin insert into klients (Family_client,Name_client, Father_name_client) values (fam,name,fat);
select max(ID_client) from klients into @kl;
insert into price (Price, ID_horse, ID_client) values (pr,hor,@kl);
end|
5 Процедура del_cl удаляющая клиента из всех таблиц.
Для работы функции в начале работы с ней нужно прописать: SET AUTOCOMMIT=0;
SET FOREIGN_KEY_CHECKS=0;
SQL - код: create procedure del_cl(in id integer) begin delete from klients where ID_client=id;
delete from price where ID_client=id;
end|
6 Процедура info_ippodrom показывающая основную информацию об ипподроме, подсчитанную с помощью триггеров.
SQL - код: create procedure info_ippodrom() begin select @summoney as sum_money, @counthorse as count_horse, @countclient as count_client, @countrunner as count_runner;
end
5.4 Спецификация триггеров
1 Триггер sum_money срабатывает после добавления в таблицу ставки, подсчитывает сумму всех ставок.
SQL - код: create trigger sum_money after insert on price for each row begin select sum(Price) from price into @summoney;
end
2 Триггер count_horse срабатывает после добавления в таблицу лошади, подсчитывает количество лошадей.
SQL - код: create trigger count_horse after insert on horse for each row begin select count(ID_horse) from horse into @counthorse;
end|
3 Триггер count_klients срабатывает после добавления в таблицу клиенты, подсчитывает количество клиентов.
SQL - код: create trigger count_klients after insert on klients for each row begin select count(ID_client) from klients into @countclient;
end|
4 Триггер count_runners срабатывает после добавления в таблицу наездника, подсчитывает количество наездников.
SQL - код: reate trigger count_runners after insert on runner for each row begin select count(ID_runner) from runner into @countrunner;
end|
5.5 Представление
Представление хранит виртуальную таблицу info в которой содержатся все данные по ставкам (имена клиентов, кличка лошади и сумма ставки), сортированную по убыванию ставки.
SQL - код: create view info (Family_client,Name_client,Father_name_client,Price, Name_horse) as select Family_client,Name_client,Father_name_client,Price,Name_horse from klients join horse join price on (klients.ID_client = price.ID_client) and (horse.ID_horse =price.ID_horse) group by Price order by Price desc|
6. Тестирование системы
6.1 Заполнение БД актуальными данными
Все таблицы БД заполнены актуальными данными. Каждая из таблиц содержит данные, характеризующие соответствующую сущность. Листинг заполнения БД приведен в приложении Б.
6.2 Описание вспомогательных SELECT-запросов
1) SELECT - запрос для проверки работы триггера sum_money, срабатывающего после добавления в таблицу ставки, подсчитывает сумму всех ставок.
Чтобы проверить его работу сделаем выборку данных из объекта
@summoney
SQL - код: select @summoney count;
Общая сумма ставок.
2) SELECT - запрос для проверки работы триггера count_horse, вычисляющего количество лошадей в БД.
Чтобы проверить его работу сделаем выборку данных из объекта
@counthorse
SQL - код: select @counthorse count;
Количество лошадей.
3) SELECT - запрос для проверки работы триггера count_klients, вычисляющего количество клиентов.
Чтобы проверить его работу сделаем выборку данных из объекта
@countclient
SQL - код: select @countclient count;
Количество клиентов.
4) SELECT - запрос для проверки работы триггера count_runners, вычисляющего количество наездников.
Чтобы проверить его работу сделаем выборку данных из объекта
@countrunner
SQL - код: select @countrunner count;
Количество наездников.
5) SELECT - запрос для проверки работы представления, хранящего виртуальную таблицу info в которой содержатся все данные по ставкам (имена клиентов, кличка лошади и сумма ставки), сортированную по убыванию ставки.
Выполним запрос к представлению
SQL - код: select * from info;
Результат - таблица со сведениями о ставках.
6.3 Тестирование функций
1) Вызовем функцию count_clients, подсчитывающую количество клиентов, т.е. персон сделавших ставки.
Количество клиентов.
2) Вызовем функцию count_price, выдающую количество ставок, сделанных клиентом. Входным параметром является id клиента.
Получаем количество ставок у 5 клиента.
3) Вызовем функцию get_name, показывающую на лошади, с какой кличкой выступает наездник. Результат - имя лошади. Входным параметром является id наездника.
Получаем что наездник с номером 4 управляет лошадью с кличкой Fire.
6.4 Тестирование процедур база данные аэропорт триггер
1) Тестируем процедуру count_price подсчитывающую сумму всех ставок на определенный забег. Входные параметры - дата и время забега.
Получаем, что на этот забег сделано ставок на 27000.
2) Тестируем процедуру win, которая выводит номера клиентов, чья ставка оказалась выигрышной. Также выводит тип забега, в котором был выигрыш.
Получаем, что есть два клиента с выигрышными ставками в двух разных забегах.
3) Тестируем процедуру add_cl добавляющую клиента, а также его ставку. Входные параметры: фамилия, имя, отчество, размер ставки, id лошади.
Получаем нового добавленного клиента и ставку.
4) Тестируем процедуру del_cl удаляющую клиента и связанную с ним ставку. Входной параметр - id клиента.
Для работы функции в начале работы с ней нужно прописать: SET AUTOCOMMIT=0;
SET FOREIGN_KEY_CHECKS=0;
Клиент и связанная с ним ставка удалены из БД.
5) Тестируем процедуру change_price, меняющую ставку клиента по id ставки используя курсор. Входные параметры - id ставки и сумма новой ставки.
Ставка с id 1 поменялась с 3000 на 5000.
6) Тестируем процедуру Процедура info_ippodrom показывающую основную информацию об ипподроме, подсчитанную с помощью триггеров.
Информация которая подсчитывалась триггерами приведена в этой таблице.
6.5
Тестирование триггеров
1) Протестируем работу триггера sum_money, срабатывающего после добавления в таблицу ставки, подсчитывает сумму всех ставок.
Чтобы проверить его работу сделаем выборку данных из объекта
@summoney
SQL - код: select @summoney count;
Общая сумма ставок.
2) Протестируем работу триггера count_horse, вычисляющего количество лошадей в БД. Триггер срабатывает после добавления в таблицу Horse.
Чтобы проверить его работу сделаем выборку данных из объекта
@counthorse
SQL - код: select @counthorse count;
Количество лошадей.
3) Протестируем работу триггера count_klients, вычисляющего количество клиентов.
Триггер срабатывает после добавления в таблицу Klients.
Чтобы проверить его работу сделаем выборку данных из объекта
@countclient
SQL - код: select @countclient count;
Количество клиентов.
4) Протестируем работу триггера count_runners, вычисляющего количество наездников. Триггер срабатывает после добавления в таблицу Runner. Чтобы проверить его работу сделаем выборку данных из объекта
@countrunner
SQL - код: select @countrunner count;
Количество наездников.
6.6 Тестирование представлений
Представление хранит виртуальную таблицу info в которой содержатся данные о ставках на ипподроме.
Выполним запрос к представлению
В результате получим подробный список ставок.
Вывод
В ходе работы была разработана структурная база данных, содержащая 5 взаимосвязанных таблиц по информационной области «Ипподром». Так же был реализован функционал, для удобства работы с данными. Функционал включил в себя 6 процедур, 4 триггера, 3 функции, 1 представление и 1 курсор. Была выполнена работа по выделению сущностей, нормализации таблиц. Затем была создана база данных, которая была заполнена данными. Затем был реализован функционал, впоследствии удачно прошедший тестирования. Также были составлены иллюстрации логической и физической моделей предметной области с помощью средств Open Model Sphere.
Список литературы
1. MYSQL 5.1 Reference Manual Copyright.
2. Максим Кузнецов, Игорь Симдянов. Самоучитель MYSQL 5.
3. Кузнецов С. Д. «Основы баз данных», 2-е издание.