Разработка структуры и основных устройств микро-ЭВМ

 

Федеральное государственное бюджетное образовательное учреждение

высшего профессионального образования

«Владимирский государственный университет

имени Александра Григорьевича и Николая Григорьевича Столетовых»

Кафедра информационных систем и программной инженерии

Курсовая работа

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

Выполнил: студент гр. ИСГ-11

Самодуров И. А.

Принял: асс. Проскурина Г.В.

Владимир 2013

1. Введение

Современная жизнь немыслима без эффективного управления. Важной категорией являются системы обработки информации, от которых во многом зависит эффективность работы любого предприятия ли учреждения.

Наверное, самой удобной системой обработки информации является база данных. Современные базы данных можно адаптировать под любые задачи. На любом производстве, в любой организации будет полезен быстрый удобный доступ к нужным данным, быстрое редактирование записей и реестров, выделение из множества информации самой нужной, получение общих и детализированных отчетов по итогам работы.

Для обеспечения этих функций созданы специализированные средства - системы управления базами данных (СУБД). Современные СУБД - многопользовательские системы управления базой данных, которые специализируется на управлении массивом информации одним или множеством одновременно работающих пользователей. Современные СУБД обеспечивают набор средств для поддержки таблиц и отношений между связанными таблицами - развитый пользовательский интерфейс, который позволяет вводить и модифицировать информацию, выполнять поиск и представлять информацию в графическом или текстовом режиме - средства программирования высокого уровня, с помощью которых можно создавать собственные приложения.

2. Постановка задачи

Необходимо разработать прототип программной системы «Аэропорт», содержащий не менее 5 взаимосвязанных таблиц, 5 процедур, 3 функций, 2 триггеров с использованием операторов (функций) ветвления и циклов. Также необходимо обосновать и реализовать как минимум 1 представление и 1 курсор.

Для выполнения работы необходимо использовать

·СУБД MySQL 5.1

·система создания ER-диаграмм:Open Model Sphere

·язык программирования: SQL

·Среда программирования - клиент командной строки MySQL

3. Проектирование БД

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

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

Аэропорт - комплекс сооружений, предназначенный для приёма, отправки, базирования воздушных судов и обслуживания воздушных перевозок, имеющий для этих целей аэродром, аэровокзал (в крупных аэропортах нередко несколько аэровокзалов), один или несколько грузовых терминалов и другие наземные сооружения и необходимое оборудование.

База данных должна удовлетворять информационные потребности и содержать в себе информацию о полетах, самолетах, аэропортах. Также должна содержаться детальная информация о заказанных билетах на рейсы. Конкретно - Ф.И.О. заказчика, информация о его рейсе и статус оплаты.

.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 - Самолёты

ПолеТипНазначениеIDINTEGERИдентификаторPlaneNameVARCHAR(255)Модель самолёта

Таблица 3.2 - Места в самолёте

ПолеТипНазначениеIDINTEGERИдентификаторID_PlaneINTEGERНомер самолётаSeatCountINTEGERКласс местSeatTypeINTEGERКоличество мест

Таблица 3.3 - Аэропорты

ПолеТипНазначениеIDINTEGERИдентификаторAirPortNameVARCHAR(255)Название аэропортаCityVARCHAR(255)ГородCountryVARCHAR(255)Страна

Таблица 3.4 - Полёты

ПолеТипНазначениеIDINTEGERИдентификаторID_PlaneINTEGERНомер самолётаUpTimeTIMEВремя взлетаDownTimeTIMEВремя посадкиID_AP_UpINTEGERАэропорт отправленияID_AP_DownINTEGERАэропорт прибытия

Таблица 3.5 - Заказы

ПолеТипНазначениеIDINTEGERИдентификаторID_FlightINTEGERНомер полётаFlightDateDATEДата полётаID_SeatsINTEGERИндиф. номер местSeatCountINTEGERКоличество местPayedBooleanОплата (оплачено/нет)FIOSTRING3.3 Логическая схема базы данных

ER - модель логического уровня

.4 Обоснование связей между сущностями

1)Связь «Наездник» - «Таблица забегов»: один ко многим, так как участвует не один наездник.

2)Связь «Лошадь» - «Таблица забегов»: один ко многим, так как участвует не одна лошадь.

)Связь «Клиенты» - «Ставки»: один ко многим, так как один клиент может сделать несколько ставок

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. Реализация компонентов системы

.1 Физическая структура базы данных

ER - модель физического уровня:

1 Таблица Airplanes- код:

Create table Airplanes (

> ID INTEGER not null primary key,

> Plane VARCHAR (255) not null

>);

2 Таблица Airports- код:

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- код:

Create table Mesta (

> ID INTEGER not null primary key,

> SeatCount INTEGER not null,

>SeatType INTEGER not null

>);

4 Таблица Klients- код:

Create table Flying (

> ID INTEGER not null primary key,

> UpTime TIME not nul,

> DownTime TIME not nul

>);

5 Таблица Orders- код:

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);table Flying add FOREIGN KEY(FK_ID_AP_Up) REFERENCES Airports(ID);table Flying add FOREIGN KEY(FK_ID_AP_Down) REFERENCES Airports(ID);table Mesta add FOREIGN KEY(FK_ID_Plane) REFERENCES Airplanes(ID);table Orders add FOREIGN KEY(FK_ID_Flight) REFERENCES Flying(ID);table Orders add FOREIGN KEY(FK_ID_Seats) REFERENCES Mesta(ID);

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

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

Функция get_airplane показывает название самолета на котором должен лететь клиент.

SQL - код:

delimiter |FUNCTION get_airplane(lstnm VARCHAR(255), nm VARCHAR(255), otch VARCHAR(255))VARCHAR(255)(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)));|

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

SQL - код:

CREATE FUNCTION get_uptime(reys_id INTEGER)TIME(SELECT UpTime FROM Flying WHERE ID = reys_id);|

3 Функция order_num возвращает номер рейса по фамилии клиента.

SQL - код:

CREATE FUNCTION order_num(client_name VARCHAR(255))INTEGER(SELECT `ID` FROM Orders WHERE lastname = client_name ORDER BY FlightDate DESC LIMIT 1);|

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

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

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

SQL - код:

CREATE PROCEDURE `count_orders`(IN `str` INT(1))COUNT(*) FROM Orders WHERE `Payed` = str; //

Процедура fly_info выводит время вылета всех рейсов.

SQL - код:

CREATE PROCEDURE fly_info()Airplanes.Plane, Flying.ID, Flying.UpTime FROM Airplanes RIGHT JOIN Flying ON Airplanes.ID=Flying.FK_ID_Plane;|

3 Процедура change_price, меняющая ставку клиента по id ставки используя курсор.

SQL - код:

create procedure change_price(in id integer,in m2 integer)done integer default 0;money integer;cur cursor for select ID_price from price where ID_price = id;continue handler for sqlstate '02000' set done=1;cur;done=0 docur into money;price set Price = m2 where ID_price = money;while;cur;|

4 Процедура add_cl добавляющая клиента, а также его ставку.

SQL - код:

reate procedure add_cl(in fam text,in name text, in fat text, in pr integer,in hor integer)into klients (Family_client,Name_client, Father_name_client) values (fam,name,fat);max(ID_client) from klients into @kl;into price (Price, ID_horse, ID_client) values (pr,hor,@kl);

end|

5 Процедура del_cl удаляющая клиента из всех таблиц.

Для работы функции в начале работы с ней нужно прописать:

SET AUTOCOMMIT=0;FOREIGN_KEY_CHECKS=0;

SQL - код:

create procedure del_cl(in id integer)from klients where ID_client=id;from price where ID_client=id;

end|

6 Процедура info_ippodrom показывающая основную информацию об ипподроме, подсчитанную с помощью триггеров.

SQL - код:

create procedure info_ippodrom()@summoney as sum_money, @counthorse as count_horse, @countclient as count_client, @countrunner as count_runner;

end

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

Триггер sum_money срабатывает после добавления в таблицу ставки, подсчитывает сумму всех ставок.

SQL - код:

create trigger sum_money after insert on priceeach rowsum(Price) from price into @summoney;

end

2 Триггер count_horse срабатывает после добавления в таблицу лошади, подсчитывает количество лошадей.

SQL - код:

create trigger count_horse after insert on horseeach rowcount(ID_horse) from horse into @counthorse;

end|

3 Триггер count_klients срабатывает после добавления в таблицу клиенты, подсчитывает количество клиентов.

SQL - код:

create trigger count_klients after insert on klientseach rowcount(ID_client) from klients into @countclient;

end|

4 Триггер count_runners срабатывает после добавления в таблицу наездника, подсчитывает количество наездников.

SQL - код:

reate trigger count_runners after insert on runnereach rowcount(ID_runner) from runner into @countrunner;

end|

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

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

SQL - код:

create view info (Family_client,Name_client,Father_name_client,Price, Name_horse) asFamily_client,Name_client,Father_name_client,Price,Name_horse fromjoin 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. Тестирование системы

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

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

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

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

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

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

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

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

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

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

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

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

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

1)Тестируем процедуру count_price подсчитывающую сумму всех ставок на определенный забег. Входные параметры - дата и время забега.

Получаем, что на этот забег сделано ставок на 27000.

)Тестируем процедуру win, которая выводит номера клиентов, чья ставка оказалась выигрышной. Также выводит тип забега, в котором был выигрыш.

Получаем, что есть два клиента с выигрышными ставками в двух разных забегах.

)Тестируем процедуру add_cl добавляющую клиента, а также его ставку. Входные параметры: фамилия, имя, отчество, размер ставки, id лошади.

Получаем нового добавленного клиента и ставку.

)Тестируем процедуру del_cl удаляющую клиента и связанную с ним ставку. Входной параметр - id клиента.

Для работы функции в начале работы с ней нужно прописать:

SET AUTOCOMMIT=0;FOREIGN_KEY_CHECKS=0;

Клиент и связанная с ним ставка удалены из БД.

)Тестируем процедуру change_price, меняющую ставку клиента по id ставки используя курсор. Входные параметры - id ставки и сумма новой ставки.

Ставка с id 1 поменялась с 3000 на 5000.

)Тестируем процедуру Процедура info_ippodrom показывающую основную информацию об ипподроме, подсчитанную с помощью триггеров.

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

6.5Тестирование триггеров

)Протестируем работу триггера 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 в которой содержатся данные о ставках на ипподроме.

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

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

7. Заключение

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

8. Список использованных источников

1.MySQL 5.1 Reference Manual Copyright.

2.Максим Кузнецов, Игорь Симдянов. Самоучитель MySQL 5.

.Кузнецов С. Д. «Основы баз данных», 2-е издание.

Приложение А

Скрипт создания базы данных

SQL - код:

database ippodrom;ippodromtable Runner(_runner integer not null primary key unique auto_increment,text not null,text not null,_name text null,_runner integer not null,integer not null);table Horse(_horse integer not null primary key unique auto_increment,_horse text not null,_horse text not null,_horse integer not null);table Klients(_client integer not null primary key unique auto_increment,_client text not null,_client text not null,_name_client text null);table Table_runs(_run integer not null primary key unique auto_increment,_run text not null,integer not null,_runner integer not null,_run date not null,_horse integer not null,key (ID_runner) references Runner (ID_runner),key (ID_horse) references Horse (ID_horse));table Price(_price integer not null primary key unique auto_increment,integer not null,_horse integer not null,_client integer not null,key (ID_horse) references Horse (ID_horse),

> foreign key (ID_client) references Klients (ID_client));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

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

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

into Table_runs values

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

(2,'Sprint',4,2,'12.02.13',2,'11:00:00'),

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

(4,'Sprint',2,4,'12.02.13',5,'11:00:00'),

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

(6,'Open Stakes',5,9,'12.02.15',7,'13:00:00'),

(7,'Open Stakes',4,7,'12.02.15',9,'13:00:00'),

(8,'Open Stakes',3,8,'12.02.15',8,'13:00:00'),

(9,'Open Stakes',2,10,'12.02.15',10,'13:00:00'),

(10,'Open Stakes',1,6,'12.02.15',6,'13:00:00');OK, 10 rows affected (0.17 sec): 10 Duplicates: 0 Warnings: 0

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

into price (Price,ID_horse,ID_client) values

(3000,1,1),

(5000,2,2),

(4000,3,4),

(7000,4,3),

(6000,5,5),

(4000,6,6),

(3500,7,8),

(3500,8,7),

(6500,9,9),

(9500,10,10);

Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования «Владимирский государственный университет и

Больше работ по теме: