Разработка структуры базы данных для информационной системы «Аэропорт»

-> 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 Спецификация функций

Перечислим функции ИС:

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|

5.2.2 Функция get_uptime показывает время отлета указанного рейса

SQL – код:

CREATE FUNCTION get_uptime(reys_id INTEGER)

RETURNS TIME

BEGIN

RETURN (SELECT UpTime FROM Flying WHERE ID = reys_id);

END|

5.2.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 Спецификация процедур

Перечислим процедуры ИС:

5.3.1 Процедура count_orders подсчитывает количество оплаченных или не оплаченных заказов. На вход подается 0 или 1

SQL – код:

CREATE PROCEDURE `count_orders`(IN `str` INT(1))

BEGIN

SELECT COUNT(*) FROM Orders WHERE `Payed` = str;

END //

5.3.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|

5.3.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|

5.3.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.3.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|

5.3.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 Спецификация триггеров

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|

5.4.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|

5.4.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|

5.4.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 показывающую основную информацию об ипподроме, подсчитанную с помощью триггеров. 
   

Информация которая подсчитывалась триггерами приведена в этой таблице.

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.  Тестирование представлений

Представление хранит виртуальную  таблицу  info в которой содержатся данные о ставках на ипподроме.

 

Выполним запрос к  представлению

 

 

В результате получим  подробный список ставок.

 

7 ЗАКЛЮЧЕНИЕ

В ходе работы была разработана структурная база данных, содержащая 5 взаимосвязанных таблиц по информационной области «Ипподром». Так же был реализован функционал, для удобства работы с данными. Функционал включил в седя 6 процедур, 4 триггера, 3 функции, 1 представление и 1 курсор. Была выполнена работа по выделению сущностей, нормализации таблиц. Затем была создана база данных, которая была заполнена данными. Затем был реализован функционал, впоследствии удачно прошедший тестирования. Также были составлены иллюстрации логической и физической моделей предметной области с помощью средств Open Model Sphere.

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

1. MySQL 5.1 Reference Manual Copyright.
2. Максим Кузнецов, Игорь Симдянов. Самоучитель MySQL 5.
3. Кузнецов С. Д.  « Основы баз данных», 2-е издание.

ПРИЛОЖЕНИЕ А 
Скрипт создания базы данных

SQL – код:

 

create database ippodrom;

 

use ippodrom

 

create table Runner(

     ID_runner integer not null primary key unique auto_increment,

     Family text not null,

     Name text not null,

     Father_name text null,

     Age_runner integer not null,

     Weight integer not null);

 

create table Horse(

     ID_horse integer not null primary key unique auto_increment,

     Name_horse text not null,

     Type_horse text not null,

     Age_horse integer not null);

 

create table Klients(

     ID_client integer not null primary key unique auto_increment,

     Family_client text not null,

     Name_client text not null,

     Father_name_client text null);

 

 

 

 

 

 

create table Table_runs(

     ID_run integer not null primary key unique auto_increment,

     Type_run text not null,

     Place integer not null,

     ID_runner integer not null,

     Date_run date not null,

     ID_horse integer not null,

     foreign key (ID_runner) references Runner (ID_runner),

     foreign key (ID_horse) references Horse (ID_horse));

 

create table Price(

     ID_price integer not null primary key unique auto_increment,

     Price integer not null,

     ID_horse integer not null,

     ID_client integer not null,

     foreign key (ID_horse) references Horse (ID_horse),

    -> foreign key (ID_client) references Klients (ID_client));

Query OK, 0 rows affected (0.23 sec)

ПРИЛОЖЕНИЕ Б 
ЗАПОЛНЕНИЕ БАЗЫ ДАННЫХ

Заполнение таблицы Runner

 

insert into Runner values

     (1,'Ivanov','Ivan','Ivanovich','30','75'),

     (2,'Petrov','Petr','Petrovic','25','65'),

     (3,'Semenov','Semen','Semenovic','32','80'),

     (4,'Peter','Vagner','','40','78'),

     (5,'Ioghann','Shpitz','','22','72'),

     (6,'Ditrich','Valtz','','27','74'),

     (7,'Michael','Paterson','','33','68'),

     (8,'Donatan','McCinley','','36','82'),

     (9,'George','Jourley','','41','73'),

     (10,'Kornelio','Spiterri','','31','77');

 

Заполнение таблицы Horse

 

insert into Horse values

     (1,'Belka','White',7),

     (2,'Daemon','Black',8),

     (3,'Kelly','Brown','9'),

     (4,'Strelka','White',7),

     (5,'Fire','Brown',7),

     (6,'Carol','Black',9),

     (7,'Snezhok','White',8),

     (8,'Ghost','Brown',6),

     (9,'Nostra','Black',7),

     (10,'Gary','Yellow',8);

 

 

 

 

 

Заполнение таблицы Klients

 

insert into Klients values

     (1,'Figner','Nick',''),

     (2,'Polskich','Garry',''),

     (3,'Sydorov','Andrey','Alexeevich'),

     (4,'Volkov','Vladimir','Alexandrovich'),

     (5,'Smertyn','Venyamin','Ivanovich'),

     (6,'Zendler','Ghans',''),

     (7,'Fatler','Gerard',''),

     (8,'Delly','Carl',''),

     (9,'Tyler','James',''),

     (10,'Adams','Willham','');

 

Заполнение таблицы Table_runs

 

insert into Table_runs values

(1,'Sprint',5,3,'12.02.13',3,'11:00:00'),