Спецификация функций. -> ID INTEGER not null primary key,

Таблица Mesta

SQL – код:

Create table Mesta (

-> ID INTEGER not null primary key,

-> SeatCount INTEGER not null,

->SeatType INTEGER not null

->);

Таблица 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);

Спецификация функций

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

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|

Спецификация процедур

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

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

SQL – код:

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

BEGIN

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

END //

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

Спецификация триггеров

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|

Представление

Представление хранит виртуальную таблицу 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|

5. Тестирование системы

5.1 Заполнение БД актуальными данными

Все таблицы БД заполнены актуальными данными. Каждая из таблиц содержит данные, характеризующие соответствующую сущность. Листинг заполнения БД приведён в приложении Б.

Описание вспомогательных 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;

Результат – таблица со сведениями о ставках.

5.2 Тестирование функций

1) Вызовем функцию count_clients, подсчитывающую количество клиентов, т.е. персон сделавших ставки.

Количество клиентов.

2) Вызовем функцию count_price, выдающую количество ставок, сделанных клиентом. Входным параметром является id клиента.

Получаем количество ставок у 5 клиента.

3) Вызовем функцию get_name, показывающую на лошади, с какой кличкой выступает наездник. Результат – имя лошади. Входным параметром является id наездника.

Получаем что наездник с номером 4 управляет лошадью с кличкой Fire.

5.3 Тестирование процедур

база данные аэропорт триггер

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.4 Тестирование триггеров

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;

Количество наездников.