数据库课程设计航空订票管理系统1_毕业论文

数据库课程设计

学院计算机

专业计算机科学与技术班级

学号

姓名

指导教师左亚饶

（2013年1 月）

目录

一、需求分析 (3)

二、概念结构设计 (4)

三、逻辑结构设计 (4)

四、数据库设计实现及运行 (5)

五、系统实现主要模块描述以及关键代码 (18)

七、心得与体会 (23)

一、需求分析

1)、问题描述：

航空订票管理系统主要是为机场、航空公司和客户三方服务。航空公司提供航线和飞机的资料，机场则对在本机场起飞和降落的航班和机票进行管理，而客户能得到的服务应该有查询航班路线和剩余票数，以及网上订票等功能。

客户可以分为两类：一类是普通客户，对于普通客户只有普通的查询功能和订票功能没有相应的票价优惠；另一类是经常旅客，需要办理注册手续，但增加了里程积分功能和积分优惠政策。机场还要有紧急应对措施，在航班出现延误时，要发送相应的信息。

2)、系统功能描述：

航班查询：根据用户输入的“起飞机场”和“降落机场”信息找到符合要求的航班号，并显示相应的起飞时间、飞行里程、各舱等的余票情况、各舱等的票价情况。供用户进行选择。

订票：根据用户输入的“所订航班号”和“订票乘客信息”，为用户查看该航班是否有余票：有则生成一条新的票务信息，根据该用户的积分情况制定票价，修改其积分里程；若已无余票，则将该乘客登机在侯票名单之中。

退票：根据用户输入的“所退票流水号”信息，修改订票乘客的积分信息，将相关的票务记录删除；并在侯票记录中找到符合航班要求的第一名乘客，为其办理订票服务，相关的修改其积分。

售票情况统计：统计每个航班的各个舱位等级的已售票数目，侯票数目。

积分功能：为每一个已订购的乘客记录其飞行里程积分，当积分达到一定程度后，为其提供打折的票价。

航班延误：当要延误一个航班时，找到已订购该航班的乘客，发送提示信息。

3)、有何安全性与完整性方面的要求：

订票事务的原子性：每个乘客的订票过程是互斥执行的，防止有一张票卖给两个人的情况发生。通过订票存储过程实现。

飞机、航班、票务的级联：当删除一个飞机，级联的删除该飞机所飞行的航班，以及订购了该航班的乘客订票。通过触发器实现。

二、概念结构设计

画出系统整体的E-R模型；并对模型中所出现的实体及属性等信息加以说明。

三、逻辑结构设计

1）、模式设计：按系统整体E-R模型，写出关系模式；并利用数据字典加以描述。

航空公司表：（公司名称，公司网址）；

飞机表：（飞机号，机型，头等舱定额数，普通舱定额数，所属航空公司）；航线表：（航线号，起飞机场，降落机场，飞行里程，所属航空公司）；

航班表：（航班号，执行飞机号，所飞航线号，头等舱票价，普通舱票价，起飞时间）；

乘客表：（乘客姓名，里程积分，E_mail）；

机票表：（机票流水号，所乘航班号，订票乘客姓名，所乘舱位等级，票价）；侯票表：（所侯航班，侯票乘客姓名，所需舱位等级，乘客E_mail）；

除各主键外，其余各非主属性也为非空。“头等舱定额数，普通舱定额数，飞行里程，机票流水号，票价，里程积分”为数值型，约束不能小于0，其余为字符型，舱位等级只能选“头等舱或普通舱”。

2）、子模式设计：即针对自己的设计模块的应用需定义什么样的视图表结构；并利用数据字典加以描述。

售票情况视图：（航班号，头等舱数目，头等舱已售出数目，头等舱侯票数目，普通舱数目，普通舱已售出数目，普通舱侯票数目）

用于方便机场管理票务，统计各舱等的售票、须票情况。

航班选择视图：（航班号，起飞机场，降落机场，飞行里程，头等舱票价，头等舱是否有票，普通舱票价，普通舱是否有票）

提供航班飞行的基本情况，包括判断是否有票，以便选择订票。

票务信息视图：（机票流水号，订票乘客姓名，所订航班，所订舱等，票价，飞行里程）

提供机票的相关信息，以便退票时事物处理。

四、数据库设计实现及运行

1)、数据库的创建

T-SQL语句：

create database BookTicket_6222 on (

name = N'BookTicket_6222_Data', filename = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\BookTicket_6222_Data.MDF' ,

size = 1, filegrowth = 10%)

log on (

name = N'BookTicket_6222_Log', filename = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\BookTicket_6222_Log.LDF' ,

size = 1, filegrowth = 10%)

2）、数据表的创建（可同时在创建时定义相关的约束）

T-SQL语句：

create table Company (

company_name varchar(20) constraint pk_cpy primary key,

phone varchar(15) not null)

create table Plane (

plane_number varchar(10) constraint pk_pln primary key,

plane_type varchar(20) not null,

quota_f int not null,

quota_s int not null,

company_name varchar(20) constraint fk_cp

foreign key references Company(company_name) on delete cascade)

create table Line (

line_number varchar(10) constraint pk_lne primary key,

airport_s varchar(20) not null,

airport_e varchar(20) not null,

distance int not null,

company_name varchar(20) constraint fk_cl

foreign key references Company(company_name) on delete

cascade)

create table Flight (

flight_number varchar(10) constraint pk_flt primary key,

plane_number varchar(10) constraint fk_pf

foreign key references Plane(plane_number) on delete cascade, line_number varchar(10) constraint fk_lf

foreign key references Line(line_number) on delete cascade, price_f int not null,

price_s int not null,

flydate varchar(20) not null)

create table Passenger (

passenger_name varchar(10) constraint pk_psg primary key,

distances int not null,

E_mail varchar(20) not null)

create table Ticket (

ticket_number int identity(1, 1) constraint pk_tkt primary key,

flight_number varchar(10) constraint fk_ft

foreign key references Flight(flight_number) on delete cascade, passenger_name varchar(10) constraint fk_pt

foreign key references Passenger(passenger_name) on delete cascade,

cabin varchar(10) constraint ch_cbn check

(cabin = '头等舱' or cabin = '普通舱') not null,

pay int constraint ch_pay check

(pay>0) not null)

create table Wait (

passenger_name varchar(10) constraint fk_pw

foreign key references Passenger(passenger_name) on delete cascade,

flight_number varchar(10) constraint fk_fw

foreign key references Flight(flight_number) on delete cascade, cabin_w varchar(10) not null,

primary key (passenger_name, flight_number),

E_mail varchar(20) not null)

3）、视图的创建（仅需给出自己设计模块中所用的视图）

T-SQL语句：

--方便乘客进行航班查询的视图

create view flight_cf (flight_number, count_f)

as

select flight_number, count(ticket_number)

from Ticket where cabin = '头等舱' group by flight_number

create view flight_cs (flight_number, count_s)

as

select flight_number, count(ticket_number)

from Ticket where cabin = '普通舱' group by flight_number

create view flight_wf (flight_number, wait_f)

as

select flight_number, count(passenger_name)

from Wait where cabin_w = '头等舱' group by flight_number

create view flight_ws (flight_number, wait_s)

as

select flight_number, count(passenger_name)

from Wait where cabin_w = '普通舱' group by flight_number

create view flight_q (flight_number, quota_f, quota_s)

as

select Flight.flight_number, quota_f, quota_s

from Flight left join Plane on Flight.plane_number = Plane.plane_number

create view book_info (flight_number, quota_f, count_f, wait_f,quota_s, count_s, wait_s)

as

select flight_q.flight_number, quota_f, count_f, wait_f,quota_s, count_s, wait_s

from flight_q left join flight_cf on flight_q.flight_number = flight_cf.flight_number left join flight_cs on flight_q.flight_number = flight_cs.flight_number

left join flight_wf on flight_q.flight_number = flight_wf.flight_number

left join flight_ws on flight_q.flight_number = flight_ws.flight_number

create view flight_choose (flight_number, airport_s, airport_e, distance, flydate, price_f, remain_f, price_s, remain_s)

as

select Flight.flight_number, airport_s, airport_e, distance, flydate, price_f,

remain_f = case

when (quota_f - count_f) <= 0 then '无'

else '有'

end, price_s,

remain_s = case

when (quota_s - count_s) <= 0 then '无'

else '有'

end

from Line, Flight, book_info

where Line.line_number = Flight.line_number and Flight.flight_number = book_info.flight_number

create view ticket_info (ticket_number, passenger_name, flight_number, cabin, pay, distance)

as

select ticket_number, passenger_name, Ticket.flight_number, cabin, pay, flight_choose.distance

from Ticket left join flight_choose on Ticket.flight_number = flight_choose.flight_number

4）、存储过程的定义实现（仅需给出自己设计模块中所用的）

T-SQL语句：

--订票

create proc book_ticket

@flight_number varchar (10), @passenger_name varchar (10), @cabin varchar(10), @E_mail varchar(20)

as

begin

begin tran

declare @remain varchar(10), @pay float, @distance int

if (@cabin = '头等舱')

begin

select @remain = remain_f from flight_choose where flight_number = @flight_number

select @pay = price_f from flight_choose where flight_number = @flight_number end

else

begin

select @remain = remain_s from flight_choose where flight_number = @flight_number

select @pay = price_s from flight_choose where flight_number = @flight_number end

select @distance = distance from flight_choose where flight_number = @flight_number

if (@remain = '有')

begin

if (exists(select passenger_name from Passenger where passenger_name = @passenger_name))

begin

declare @distances float

select @distances = distances from Passenger where passenger_name = @passenger_name

if (@distances >= 200000)

select @distances = 0.8

else if (@distances >= 100000)

select @distances = 0.9

insert into Ticket (flight_number, passenger_name, cabin, pay)

values (@flight_number, @passenger_name, @cabin, (@pay * @distances))

update Passenger set distances = distances + @distance where passenger_name = @passenger_name

update Passenger set E_mail = @E_mail where passenger_name = @passenger_name

end

else

begin

insert into Passenger (passenger_name, distances, E_mail)

values (@passenger_name, @distance, @E_mail)

insert into Ticket (flight_number, passenger_name, cabin, pay)

values (@flight_number, @passenger_name, @cabin, @pay)

end

end

else

begin

if (exists(select passenger_name from Passenger where passenger_name = @passenger_name))

update Passenger set E_mail = @E_mail where passenger_name = @passenger_name

insert into Passenger (passenger_name, distances, E_mail)

values (@passenger_name, 0, @E_mail)

insert into Wait (flight_number, passenger_name, cabin_w, E_mail)

values (@flight_number, @passenger_name, @cabin, @E_mail)

end

commit tran

end

--退票

create proc cancel_ticket

@ticket_number int

as

begin

begin tran

declare @passenger_name varchar(10), @flight_number varchar(10), @cabin varchar(10), @distance int, @pay int

select @passenger_name = passenger_name from ticket_info where ticket_number = @ticket_number

select @flight_number = flight_number from ticket_info where ticket_number = @ticket_number

select @cabin = cabin from ticket_info where ticket_number = @ticket_number select @distance = distance from ticket_info where ticket_number = @ticket_number select @pay = pay from ticket_info where ticket_number = @ticket_number

update Passenger set distances = distances - @distance where passenger_name = @passenger_name

delete from Ticket where ticket_number = @ticket_number

if (exists(select top 1 * from Wait where flight_number = @flight_number and cabin_w = @cabin))

select @passenger_name = passenger_name from Wait where flight_number = @flight_number and cabin_w = @cabin

insert into Ticket (flight_number, passenger_name, cabin, pay) values (@flight_number, @passenger_name, @cabin, @pay)

delete from Wait where passenger_name = @passenger_name and flight_number = @flight_number

update Passenger set distances = distances + @distance where passenger_name = @passenger_name

end

commit tran

end

在宿主语言中调用存储过程代码段（粘贴）：

public void executeProc (int ticket_number){

try{

con = DriverManager.getConnection(url);

PreparedStatement cmd = con.prepareStatement("{call cancel_ticket(?)}");

cmd.setInt(1, ticket_number);

cmd.executeUpdate();

}catch(Exception ex) {

ex.printStackTrace();

}

}

public void executeProc (String fn, String pn, String cb, String em){

try{

con = DriverManager.getConnection(url);

PreparedStatement cmd = con.prepareStatement("{call book_ticket (?, ?, ?, ?)}");

cmd.setString(1, fn);

cmd.setString(2, pn);

cmd.setString(3, cb);

cmd.setString(4, em);

cmd.executeUpdate();

}catch(Exception ex) {

ex.printStackTrace();

}

}

5）、触发器的定义实现（仅需给出自己设计模块中所用的）T-SQL语句：

--级联删除飞机所对应的航班

create trigger pln_del

on Plane

for delete

as

begin

begin tran

declare @plane_number varchar(10)

select @plane_number = deleted.plane_number from deleted delete from Flight where Flight.plane_number = @plane_number commit tran

end

--级联删除航线所对应的航班

create trigger lne_del

on Line

for delete

as

begin

begin tran

declare @line_number varchar(10)

select @line_number = deleted.line_number from deleted

delete from Flight where Flight.line_number = @line_number

commit tran

end

--级联删除航班所对应的票务

create trigger flt_del

on Flight

for delete

as

begin

begin tran

declare @flight_number varchar(10)

select @flight_number = deleted.flight_number from deleted

delete from Ticket where Ticket.flight_number = @flight_number

delete from Wait where Wait.flight_number = @flight_number

commit tran

end

--延误航班时，触发对订票车功乘客的提示

create trigger flt_upd

on Flight

instead of update

as

begin

begin tran

declare @flight_number varchar(10)

select @flight_number = inserted.flight_number from inserted

select Ticket.passenger_name, E_mail from Ticket, Passenger where

Ticket.passenger_name = Passenger.passenger_name and Ticket.flight_number = @flight_number

commit tran

end