Проектирование и реализация базы данных по учету продаж авиабилетов

Размещено на http://www.allbest.ru/

Введение

учет продажа авиабилет кодировка

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

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

Целью данной курсовой работы является проектирование и реализация базы данных по учету продаж авиабилетов. Для достижения поставленной цели необходимо решить следующие задачи:

1) Детально изучить и описать предметную область;

2) Разработать концептуальную схему базы данных;

3) Реализовать базу данных;

4) Реализовать запросы к базе данных.



1. Описание предметной области базы данных по учету продаж авиабилетов

На кассе продаются авиабилеты. Оформление билета возможно только на совершеннолетнее лицо. Для оформления билета необходимо предъявить серию и номер паспорта, имя, фамилию. Билет оформляется на рейс. В билете указываются класс и место в классе, а также пассажир, на которого оформлен билет (таким образом сотрудники пропускных пунктов могут контролировать попытки прохода на рейс несовершеннолетних без сопровождения взрослых (оформивших билет)).

Каждый рейс имеет свой номер, по которому пассажиры могут его узнать. Рейсам выставляются борта, маршруты - аэропорт вылета и аэропорт посадки, время вылета и посадки, дата вылета и посадки, а также цена рейса. Каждый рейс планируется заранее и согласовывается с другими рейсами во избежание столкновений в воздушном или наземном пространстве, поэтому время вылета не зависит от маршрута.

Производители самолетов выставляют максимально разрешенный вес самолета, поэтому у каждого лайнера свое максимально разрешенное кол-во мест. Как укомплектовать борт решают уже в авиакомпании, поэтому у каждого борта могут быть разные классы мест и количество мест в классе, но суммарное количество мест не должно превышать максимально установленное производителем.



2. Разработка базы данных

2.1 Разработка концептуальной схемы базы данных по учету продаж авиабилетов

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

Сущность «авиалайнер» имеет атрибуты: код авиалайнера, модель самолета и максимальное количество мест.

Сущность «борт» имеет атрибуты: класс, номер борта и количество мест.

Сущность «билет» имеет атрибуты: класс и место.

Сущность «маршрут» имеет атрибуты: точка назначения, точка вылета и номер маршрута.

Сущность «пассажир» имеет атрибуты: дата рождения, номер визы, серия номера паспорта, фамилия, имя и отчество.

Сущность «рейс» имеет атрибуты: номер рейса, время вылета, время прилета, дата вылета, дата прилета, цена.

На рисунке 1 представлена концептуальная схема базы данных по учету продаж авиабилетов.



Рисунок 1. Концептуальная схема базы данных по учету продаж авиабилетов

2.2 Разработка логической схемы базы данных по учету продажи авиабилетов

Логическая схема выполнена в нотации IDEF1X. В данной нотации есть сущности, атрибуты и связи. Логическая схема строится на основе концептуальной схемы базы данных. Сущности логической схемы «Рейс», «Борт», «Авиалайнер» соответствуют сущностям концептуальной схемы.

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

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

Идентифицирующими связями будут соединены: сущности «Борт» и «Класс». Для создания связи многие ко многим определяется третья таблица Борт имеет класс», которая называется промежуточной таблицей. Первичный «ключ промежуточной таблицы состоит из внешних ключей «Номер борта» и «ИД класса».

Неидентифицирующими связями будут соединены: сущности Авиалайнер» и «Борт», сущности «Пассажир» и «Билет», сущности «Класс» и «Билет», сущности «Борт» и «Рейс», сущности «Рейс» и «Билет», сущности «Маршрут» и «Рейс» и сущности «Аэропорт» и «Маршрут».

После, полученную схему нормализуют, т.е. приводят к необходимой нормальной форме (далее, НФ). В данной курсовой работе приведем базу данных к 3НФ. Рассмотрим все НФ до третьей включительно.

Отношение находится в 1НФ, если все значения атрибута принимают простые значения, не являющиеся множеством или кортежем из более элементарных составляющих.

Отношение находится в 2НФ, если оно находится в 1НФ и каждый неключевой атрибут зависит от всего первичного ключа (не зависит от части ключа).

Отношение находится в 3НФ, если оно находится в 2НФ, и если все не ключевые атрибуты отношения взаимно независимы и полностью зависят от первичного ключа.

Построим логическую схему базы данных по учету продажи авиабилетов. Схема представлена на рисунке 2.



Рисунок 2. Логическая схема базы данных по учету продаже авиабилетов

Рассмотрим таблицу «рейс», изображенную на рисунке 3.

Рисунок 3. Сущность «Рейс»

Сущность «рейс» находится в 1НФ, т.к. все не ключевые атрибуты сущности, а именно «время вылета», «время прилета», «дата вылета» и «дата прилета» принимают простые значения, не состоящие из более простых значений. Сущность «рейс» находится во 2НФ, т.к. находится в 1НФ и все перечисленные раннее не ключевые атрибуты сущности неприводимо зависят от первичного ключа этой сущности, а именно от атрибута «номер рейса».

Сущность «рейс» находится в 3НФ, т.к. она находится во 2НФ и все перечисленные раннее не ключевые атрибуты этой сущности не находятся в транзитивной зависимости от потенциальных ключей этой сущности, то есть от атрибутов «номер маршрута» или «номер борта».

Аналогично рассмотрим другие сущности, представленные на рисунках.

Рисунок 4. Сущность «Авиалайнер»

Рисунок 5. Сущность «Борт»

Рисунок 6. Сущность «Класс»

Рисунок 7. Сущность «Пассажир»

Рисунок 8. Сущность «Билет»

Рисунок 9. Сущность «Аэропорт»

Рисунок 10. Сущность «Маршрут»

Сущности Авиалайнер, Борт, Класс, Пассажир, Билет, Аэропорт, Маршрут находятся в 1НФ, т.к. не ключевые атрибуты - модель самолета и максимально возможное кол-во мест сущности Авиалайнер; название класса сущности Класс; номер визы, имя, фамилия, отчество, дата рождения сущности Пассажир, место и цена сущности Билет; название города сущности Аэропорт - принимают простые значения, не состоящие из кортежей более простых значений. Перечисленные сущности находятся во 2НФ, т.к. находятся в 1НФ и все не ключевые атрибуты этих сущностей неприводимо зависят от всего первичного ключа своей сущности соответственно. Сущности находятся в 3НФ, т.к. у перечисленных атрибутов сущностей нет транзитивных функциональных зависимостей от потенциальных ключей своей сущности соответственно.

2.3 Разработка физической схемы базы данных по учету продаж авиабилетов

Для проектирования физической модели базы данных, представленной на концептуальной схеме, следует учитывать особенности выбранного языка и СУБД, которые могут отличаться типами данных. В данном случае, были выбрана СУБД MySQL, которая отличается простотой и интуитивной понятностью. Как говорилось выше, для осуществления связей «много ко многим» необходимы вспомогательные таблицы. В нашем случае была введена сущность: «border_has_class». Для связи таблиц типа «один ко многим» были введены внешние ключи. Так как названия сущностей и атрибутов кириллицей при проектировании физической схемы базы данных является плохим тоном, а также такие наименования могут повлечь за собой проблемы совместимости кодировок на этапе реализации, то все наименования были переведены на английский язык и записаны с помощью латиницы. Для работы с данными были выбраны следующие типы данных СУБД:

Varchar(X) - символьные данные переменной длины. Под X обозначается количество символов. Используется для хранения записей, например, название, имя, адрес.

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

Date - Дата. Поддерживается интервал от '1000-01-01' до '9999-12-31'.

Time - Время. Поддерживается интервал от '00:00:00' до '23:59:59'



Рисунок 11. Физическая схема базы данных по продаже авиабилетов

2.4 Бизнес правила

1) В самолете не может быть суммарно мест больше, чем максимально установленное кол-во производителем

2) Билет можно оформить только на совершеннолетнее лицо

3) Нельзя оформить билет на место, которого нет

4) Нельзя оформить билет в класс, которого в самолете нет

5) Нельзя оформить билет на уже занятое место

6) Билеты на ребёнка покупаются вместе со взрослым

7) При вылете заграницу необходимо указать визу

8) Цена билета известна заранее



3. Реализация базы данных по учету продаж авиабилетов

3.1 Реализация и заполнение базы демо-информацией

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

Создание таблицы «airlainer»

create table airlainer(

airlainer_code int primary key,

aircraft_model varchar (70) not null,

allowed_places int not null

);

Заполнениетаблицы «airlainer»

insert into airlainer

values (737, 'Boeing 737', 750)

(320, 'Airbus 320', 550)

(777, 'Boeing 777', 320)

3.2 Триггеры

Процедуры в базах данных представляют собой наборы SQL-инструкций, которые компилируются один раз и хранятся на сервере.

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

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

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

Триггер - процедура, которая вызывается не пользователем, а каким-либо провоцирующим событием в базе данных.

Скрипт триггера проверки пересечения времени работы над одним и тем же объектом представлен ниже. (Остальные скрипты приведены в приложении В).

Триггер, проверяющий, что в самолете не может быть больше мест, чем максимально установленное кол-во производителем

create exception places_sum_e 'Превышено максимально разрешенное

кол-во мест!'

create trigger places_sum_t for board_has_class

active after insert or update

as

declare variable al_plac int;

declare variable aircrft int;

declare variable plac_sum int;

begin

aircrft = (select board.airlainer_code from board where new.board_number

=

board.board_number);

al_plac = (select allowed_places from airlainerwhere :aircrft =

airlainer_code);

plac_sum = (select SUM(board_has_class.places_in_class)

from board_has_class where board_has_class.board_number

new.board_number);

=

if(:plac_sum> :al_plac) then exception places_sum_e;

end

3.3 Запросы

Задача базы данных - хранить и выдавать информацию пользователю или приложению, взаимодействующему с ней. При выборке данных используют тот же инструмент, что и при создании таблиц - запросы. Только в этот раз нам понадобятся не DDL или DML запросы, а DQL запросы, точнее единственная относящая к этому виду запросов команда SELECT.

Скрипт для вывода всех билетов и данных пассажиров, оформивших билет.

select

ticket.ticket_id,

ticket.flight_number,

class.class_name,

ticket.sit_place,

passanger.surname, passanger.name from ticket left join passanger

on ticket.pasport = passanger.pasport left join class

on class.class_id = ticket.class_id



Заключение

В ходе выполнения курсовой работы были решены следующие задачи:

1. Была детально изучена и описана предметная область;

2. Разработана концептуальная схема базы данных;

3. Реализована база данных;

4. Реализованы запросы по извлечению информации из базы данных.

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



Список использованной литературы

1. Хелен Б. - Firebird. Руководство разработчика баз данных / Александр Бондарь - СПб: БХВ-Петербург, 2010. - 48 с.

2. Кириллов В.В. Введение в реляционные базы данных / В.В. Кириллов, Г.Ю. Громов. - Спб.: БХВ-Петербург, 2009. - 464 с.

3. Нормальная форма [Электронный ресурс]. https://ru.wikipedia.org/wiki/Нормальная_форма (дата обращения: 14.06.2021).



Приложение А

Создание таблицы «airlainer»

createtableairlainer(

airlainer_codeintprimary key,

aircraft_model varchar (70) not null,

allowed_places int not null

);

Создание таблицы «board»

create table board(

board_number int primary key,

airlainer_code int not null,

foreign key (airlainer_code) references airlainer(airlainer_code)

);

Создание таблицы «class»

create table class(

class_id int primary key,

class_name varchar(50) not null

);

Создание таблицы «board_has_class»

create table board_has_class(

class_id int not null,

board_number int not null,

places_in_class int not null,

primary key(class_id, board_number),

foreign key (class_id) references class(class_id),

foreign key (board_number) references board(board_number)

);

Создание таблицы «passanger»

create table passanger(

pasport varchar(10) primary key,

visa varchar(15),

name varchar(80) not null,

surname varchar(80) not null,

patronymic varchar(80),

birthday date not null

);

Создание таблицы «city»

create table city(

city_code int primary key,

city_name varchar(80) not null

);

Создание таблицы « route »

create table route(

route_number int primary key,

city_code_start int not null,

city_code_end int not null,

foreign key (city_code_start) references city(city_code),

foreign key (city_code_end) references city(city_code)

);

Создание таблицы « flight »

create table flight(

flight_number int primary key,

flight_start time not null,

flight_end time not null,

flight_start_date date not null,

flight_end_date date not null,

route_number int not null,

board_number int not null,

foreign key (route_number) references route(route_number),

foreign key (board_number) references board(board_number)

);

Создание таблицы «ticket»

create table ticket(

ticket_id int primary key,

pasport varchar(10) not null,

sit_place int not null,

flight_number int not null,

class_id int not null,

price float not null,

foreign key (flight_number) references flight(flight_number),

foreign key (class_id) references class(class_id)

);

Заполнение таблицы «airlainer»

insert into airlainer

values (737, 'Boeing 737', 750)

(320, 'Airbus 320', 550)

(777, 'Boeing 777', 320)

Заполнение таблицы «board»

insert into board

values (10, 737)

(11, 737)

(12, 777)

(13, 320)

Заполнение таблицы «class»

insert into class

values (0, 'Эконом')

(1, 'Бизнес')

Заполнение таблицы «board_has_class»

insert into board_has_class

values (0, 10, 500)

(1, 10, 250)

(1, 11, 550)

(0, 12, 230)

Заполнение таблицы «city»

insert into city

values (0, 'Москва')

(1, 'Вашингтон')

(2, 'Лондон')

(3, 'Париж')

(4, 'Прага')

Заполнение таблицы «route»

insert into route

values (0, 0, 3)

(1, 4, 3)

(2, 2, 4)

(3, 0, 1)

Заполнение таблицы «passanger»

insert into passanger

values ('8463526754', null, 'Petya', 'Cozurev', null, '30.01.1987')

('5426632352', '82746274626', 'Anya', 'Arbuzova', null, '26.05.1977')

('9583675823', '56578999213', 'Viktor', 'Barabanov', 'Vitalievich',

16.09.1998')

Заполнение таблицы «flight»

insert into flight

values (0, '10:00:00', '15:00:00', '17.05.2019', '17.05.2019', 1, 10)

(1, '23:00:00', '02:00:00', '05.01.2019', '06.01.2019', 3, 13)

Заполнение таблицы «ticket»

insert into ticket

values (0, '5426632352', 44, 0, 1, 1000.99)

(1, '5426632352', 45, 0, 1, 1000.99)

(2, '8463526754', 230, 0, 1, 800.00)

(3, '9583675823', 229, 0, 1, 800.00)



Приложение Б

Вывод всех бортов

select board.board_number, airlainer.aircraft_model from board, airlainer

where board.airlainer_code = airlainer.airlainer_code

Вывод всех билетов и данных пассажиров, оформивших билет

select

ticket.ticket_id,

ticket.flight_number,

class.class_name,

ticket.sit_place,

passanger.surname, passanger.name from ticket left join passanger

on ticket.pasport = passanger.pasport left join class

on class.class_id = ticket.class_id

Вывод рейсов

select

distinct

flight.flight_number,

flight.flight_start,

flight.flight_start_date,

airlainer.aircraft_model, flight.route_number

from flight, board, airlainer, route, city

where flight.board_number = board.board_number and

board.airlainer_code = airlainer.airlainer_code

Вывод всех классов для всех самолетов

select airlainer.aircraft_model, board.board_number, class.class_name,

board_has_class.places_in_class from board_has_class inner join class on

board_has_class.class_id = class.class_id inner join board on

board_has_class.board_number = board.board_number inner join airlainer

on

board.airlainer_code = airlainer.airlainer_code

Вывод всех стартовых точек маршрутов

select route.route_number, city.city_name from route inner join city on

city.city_code = route.city_code_start



Приложение В

Триггер, проверяющий, что в самолете не может быть больше мест, чем максимально установленное кол-во производителем

create exception places_sum_e 'Превышено максимально разрешенное кол-во мест!'

create trigger places_sum_t for board_has_class

active after insert or update

as

declare variable al_plac int;

declare variable aircrft int;

declare variable plac_sum int;

begin

aircrft = (select board.airlainer_code from board where new.board_number

=

board.board_number);

al_plac = (select allowed_places from airlainer where :aircrft =

airlainer_code);

plac_sum = (select SUM(board_has_class.places_in_class)

from board_has_class where board_has_class.board_number

new.board_number);

=

if(:plac_sum > :al_plac) then exception places_sum_e;

end

Триггер, проверяющий, что билет можно оформить только на

совершеннолетнее лицо

create exception ages_e 'На это лицо нельзя оформить билет!'

create trigger ages_t for passanger

active after insert or update

as

declare variable ag date;

declare variable pass_ag date;

begin

ag = (select CURRENT_DATE from RDB$DATABASE);

pass_ag = (select DATEADD(year, 18, passanger.birthday)

from passanger where passanger.pasport = new.pasport);

if (:pass_ag > :ag) then exception ages_e;

end

Триггер, проверяющий, что нельзя оформить билет на место, которого нет

create exception place_wrong 'Такого места нет!'

create trigger place_wrong_t for ticket

active after insert or update

as

declare variable plc_in_cl int;

declare variable brd_num int;

begin

brd_num = (select board_number from flight where new.flight_number =

flight_number);

plc_in_cl = (select board_has_class.places_in_class from board_has_class

where board_has_class.class_id = new.class_id and

board_has_class.board_number = :brd_num);

if (:plc_in_cl < new.sit_place or (new.sit_place < 0)) then exception

place_wrong;

end

Триггер, проверяющий, что нельзя оформить билет в класс, которого в самолете нет

create exception class_wrong 'Такого класса в самолете нет!'

create trigger class_wrong_t for ticket

active after insert or update

as

declare variable brd_num int;

begin

brd_num = (select board_number from flight where new.flight_number =

flight_number);

if ((select count(*) from board_has_class

where board_has_class.board_number = :brd_num and

board_has_class.class_id = new.class_id) = 0) then exception class_wrong;

end

Триггер, проверяющий, что нельзя оформить билет на уже занятое место

create exception place_reserved 'Место уже занято!'

create trigger place_reserved_t for ticket

active before insert or update

as

begin

if ((select count(*) from ticket

where new.class_id = ticket.class_id and

new.flight_number = ticket.flight_number

and new.sit_place = ticket.sit_place) > 0) then exception place_reserved;

end

Процедура добавления нового авиалайнера и назначения его на рейс

create procedure airlainer_create

(airl_cd int, airl_m varchar(70), airl_pl int, brd_nmb int)

as

begin

insert into airlainer

values(:airl_cd, :airl_m, :airl_pl);

insert into board

values(:brd_nmb, :airl_cd);

end

Процедура оформления билета для нового пассажира

create procedure ticket_create

(psprt varchar(10), vs varchar(15), nm varchar(80),

srnm varchar(80), ptrn varchar(80), brthd date,

tckt int, sitpl int, flght int, clss varchar(50),

prc float)

as

declare variable clssid int;

begin

insert into passanger

values(:psprt, :vs, :nm, :srnm, :ptrn, :brthd);

clssid = (select class.class_id from class where class.class_name = :clss);

insert into ticket

values(:tckt, :psprt, :sitpl, :flght, :clssid, :prc);

end

Размещено на Allbest.ru

...