毕业设计_数据库航空订票管理系统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_cpforeign 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_clforeign key references Company(company_name) on deletecascade)create table Flight (flight_number varchar(10) constraint pk_flt primary key,plane_number varchar(10) constraint fk_pfforeign key references Plane(plane_number) on delete cascade, line_number varchar(10) constraint fk_lfforeign 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_ftforeign key references Flight(flight_number) on delete cascade, passenger_name varchar(10) constraint fk_ptforeign 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_pwforeign key references Passenger(passenger_name) on delete cascade,flight_number varchar(10) constraint fk_fwforeign 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)asselect flight_number, count(ticket_number)from Ticket where cabin = '头等舱' group by flight_numbercreate view flight_cs (flight_number, count_s)asselect flight_number, count(ticket_number)from Ticket where cabin = '普通舱' group by flight_numbercreate view flight_wf (flight_number, wait_f)asselect flight_number, count(passenger_name)from Wait where cabin_w = '头等舱' group by flight_numbercreate view flight_ws (flight_number, wait_s)asselect flight_number, count(passenger_name)from Wait where cabin_w = '普通舱' group by flight_numbercreate view flight_q (flight_number, quota_f, quota_s)asselect Flight.flight_number, quota_f, quota_sfrom Flight left join Plane on Flight.plane_number = Plane.plane_numbercreate view book_info (flight_number, quota_f, count_f, wait_f,quota_s, count_s, wait_s)asselect flight_q.flight_number, quota_f, count_f, wait_f,quota_s, count_s, wait_sfrom 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_numberleft join flight_wf on flight_q.flight_number = flight_wf.flight_numberleft join flight_ws on flight_q.flight_number = flight_ws.flight_numbercreate view flight_choose (flight_number, airport_s, airport_e, distance, flydate, price_f, remain_f, price_s, remain_s)asselect Flight.flight_number, airport_s, airport_e, distance, flydate, price_f,remain_f = casewhen (quota_f - count_f) <= 0 then '无'else '有'end, price_s,remain_s = casewhen (quota_s - count_s) <= 0 then '无'else '有'endfrom Line, Flight, book_infowhere Line.line_number = Flight.line_number and Flight.flight_number = book_info.flight_numbercreate view ticket_info (ticket_number, passenger_name, flight_number, cabin, pay, distance)asselect ticket_number, passenger_name, Ticket.flight_number, cabin, pay, flight_choose.distancefrom Ticket left join flight_choose on Ticket.flight_number = flight_choose.flight_number4）、存储过程的定义实现（仅需给出自己设计模块中所用的）T-SQL语句：--订票create proc book_ticket@flight_number varchar (10), @passenger_name varchar (10), @cabin varchar(10), @E_mail varchar(20)asbeginbegin trandeclare @remain varchar(10), @pay float, @distance intif (@cabin = '头等舱')beginselect @remain = remain_f from flight_choose where flight_number = @flight_numberselect @pay = price_f from flight_choose where flight_number = @flight_number endelsebeginselect @remain = remain_s from flight_choose where flight_number = @flight_numberselect @pay = price_s from flight_choose where flight_number = @flight_number endselect @distance = distance from flight_choose where flight_number = @flight_numberif (@remain = '有')beginif (exists(select passenger_name from Passenger where passenger_name = @passenger_name))begindeclare @distances floatselect @distances = distances from Passenger where passenger_name = @passenger_nameif (@distances >= 200000)select @distances = 0.8else if (@distances >= 100000)select @distances = 0.9insert 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_nameupdate Passenger set E_mail = @E_mail where passenger_name = @passenger_nameendelsebegininsert 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)endendelsebeginif (exists(select passenger_name from Passenger where passenger_name = @passenger_name))update Passenger set E_mail = @E_mail where passenger_name = @passenger_nameinsert 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)endcommit tranend--退票create proc cancel_ticket@ticket_number intasbeginbegin trandeclare @passenger_name varchar(10), @flight_number varchar(10), @cabin varchar(10), @distance int, @pay intselect @passenger_name = passenger_name from ticket_info where ticket_number = @ticket_numberselect @flight_number = flight_number from ticket_info where ticket_number = @ticket_numberselect @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_numberupdate Passenger set distances = distances - @distance where passenger_name = @passenger_namedelete from Ticket where ticket_number = @ticket_numberif (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 = @cabininsert 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_numberupdate Passenger set distances = distances + @distance where passenger_name = @passenger_nameendcommit tranend在宿主语言中调用存储过程代码段（粘贴）：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_delon Planefor deleteasbeginbegin trandeclare @plane_number varchar(10)select @plane_number = deleted.plane_number from deleted delete from Flight where Flight.plane_number = @plane_number commit tranend--级联删除航线所对应的航班create trigger lne_delon Linefor deleteasbeginbegin trandeclare @line_number varchar(10)select @line_number = deleted.line_number from deleteddelete from Flight where Flight.line_number = @line_numbercommit tranend--级联删除航班所对应的票务create trigger flt_delon Flightfor deleteasbeginbegin trandeclare @flight_number varchar(10)select @flight_number = deleted.flight_number from deleteddelete from Ticket where Ticket.flight_number = @flight_numberdelete from Wait where Wait.flight_number = @flight_numbercommit tranend--延误航班时，触发对订票车功乘客的提示create trigger flt_updon Flightinstead of updateasbeginbegin trandeclare @flight_number varchar(10)select @flight_number = inserted.flight_number from insertedselect Ticket.passenger_name, E_mail from Ticket, Passenger whereTicket.passenger_name = Passenger.passenger_name and Ticket.flight_number = @flight_numbercommit tranend6）、自行设计各模块中所涉及的操作语句①、插入数据操作功能界面简单描述；根据用户输入的所订航班信息和订票乘客信息，为用户查看该航班是否有余票：有则插入票务信息，根据该用户的积分情况制定票价，修改其积分里程；若已无余票，则将该乘客登机在侯票名单之中。