毕业设计-数据库航空订票管理系统1.doc

《毕业设计-数据库航空订票管理系统1.doc》由会员分享，可在线阅读，更多相关《毕业设计-数据库航空订票管理系统1.doc（23页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库课程设计学 院 计算机 专 业 计算机科学与技术 班 级 学 号 姓 名 指导教师 左亚饶 （2013年1 月）目录一、需求分析3二、概念结构设计4三、逻辑结构设计4四、数据库设计实现及运行5五、系统实现主要模块描述以及关键代码18七、心得与体会23一、需求分析1)、问题描述： 航空订票管理系统主要是为机场、航空公司和客户三方服务。航空公司提供航线和飞机的资料，机场则对在本机场起飞和降落的航班和机票进行管理，而客户能得到的服务应该有查询航班路线和剩余票数，以及网上订票等功能。客户可以分为两类：一类是普通客户，对于普通客户只有普通的查询功能和订票功能没有相应的票价优惠；另一类是经常旅客，需

2、要办理注册手续，但增加了里程积分功能和积分优惠政策。机场还要有紧急应对措施，在航班出现延误时，要发送相应的信息。2)、系统功能描述：航班查询：根据用户输入的“起飞机场” 和“降落机场”信息找到符合要求的航班号，并显示相应的起飞时间、飞行里程、各舱等的余票情况、各舱等的票价情况。供用户进行选择。订票： 根据用户输入的“所订航班号”和“订票乘客信息”，为用户查看该航班是否有余票：有则生成一条新的票务信息，根据该用户的积分情况制定票价，修改其积分里程；若已无余票，则将该乘客登机在侯票名单之中。退票：根据用户输入的“所退票流水号”信息，修改订票乘客的积分信息，将相关的票务记录删除；并在侯票记录中找到符

3、合航班要求的第一名乘客，为其办理订票服务，相关的修改其积分。售票情况统计：统计每个航班的各个舱位等级的已售票数目，侯票数目。积分功能：为每一个已订购的乘客记录其飞行里程积分，当积分达到一定程度后，为其提供打折的票价。航班延误：当要延误一个航班时，找到已订购该航班的乘客，发送提示信息。3)、有何安全性与完整性方面的要求：订票事务的原子性：每个乘客的订票过程是互斥执行的，防止有一张票卖给两个人的情况发生。通过订票存储过程实现。飞机、航班、票务的级联：当删除一个飞机，级联的删除该飞机所飞行的航班，以及订购了该航班的乘客订票。通过触发器实现。二、概念结构设计画出系统整体的E-R模型；并对模型中所出现的

4、实体及属性等信息加以说明。三、逻辑结构设计1）、模式设计：按系统整体E-R模型，写出关系模式；并利用数据字典加以描述。航空公司表：（公司名称，公司网址）；飞机表：（飞机号， 机型， 头等舱定额数， 普通舱定额数， 所属航空公司）；航线表：（航线号， 起飞机场， 降落机场， 飞行里程， 所属航空公司）；航班表：（航班号， 执行飞机号， 所飞航线号， 头等舱票价， 普通舱票价， 起飞时间）；乘客表：（乘客姓名， 里程积分， E_mail）；机票表：（机票流水号， 所乘航班号，订票乘客姓名， 所乘舱位等级， 票价）；侯票表：（所侯航班， 侯票乘客姓名， 所需舱位等级， 乘客E_mail）；除各主键外

5、，其余各非主属性也为非空。“头等舱定额数， 普通舱定额数，飞行里程，机票流水号，票价，里程积分”为数值型，约束不能小于0，其余为字符型，舱位等级只能选“头等舱或普通舱”。2）、子模式设计：即针对自己的设计模块的应用需定义什么样的视图表结构；并利用数据字典加以描述。售票情况视图：（航班号， 头等舱数目，头等舱已售出数目， 头等舱侯票数目， 普通舱数目，普通舱已售出数目， 普通舱侯票数目）用于方便机场管理票务，统计各舱等的售票、须票情况。航班选择视图：（航班号，起飞机场， 降落机场， 飞行里程，头等舱票价， 头等舱是否有票，普通舱票价，普通舱是否有票）提供航班飞行的基本情况，包括判断是否有票，以便

6、选择订票。票务信息视图：（机票流水号， 订票乘客姓名， 所订航班，所订舱等， 票价， 飞行里程）提供机票的相关信息，以便退票时事物处理。四、数据库设计实现及运行1)、数据库的创建T-SQL语句：create database BookTicket_6222 on (name = NBookTicket_6222_Data, filename = NC:Program FilesMicrosoft SQL ServerMSSQLdataBookTicket_6222_Data.MDF ,size = 1, filegrowth = 10%)log on (name = NBookTicket_6

7、222_Log, filename = NC:Program FilesMicrosoft SQL ServerMSSQLdataBookTicket_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) c

8、onstraint 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(2

9、0) 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

10、 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 ke

11、y,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 r

12、eferences Passenger(passenger_name) on delete cascade,cabin varchar(10) constraint ch_cbn check (cabin = 头等舱 or cabin = 普通舱) not null,pay int constraint ch_pay check (pay0) not null)create table Wait (passenger_name varchar(10) constraint fk_pw foreign key references Passenger(passenger_name) on del

13、ete 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_num

14、ber, 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)asse

15、lect 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 Fl

16、ight.flight_number, quota_f, quota_sfrom 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)asselect flight_q.flight_number, quota_f, count_f, wait_f,quota_s, count_s, wait_sfrom flight_q left joi

17、n flight_cf on flight_q.flight_number = flight_cf.flight_numberleft 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_choos

18、e (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 = case when (quota_f - count_f) = 0 then 无else 有end, price_s,remain_s = case when (quota_s - count_s) = 200000) sel

19、ect 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 Pa

20、ssenger 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 e

21、nd 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 else insert into Passenger (passenger_name, distances, E_mail) values (passenger_name, 0, E_mail) insert into Wait (flight_n

22、umber, passenger_name, cabin_w, E_mail) values (flight_number, passenger_name, cabin, E_mail) endcommit tranend-退票create proc cancel_ticketticket_number intasbeginbegin trandeclare passenger_name varchar(10), flight_number varchar(10), cabin varchar(10), distance int, pay intselect passenger_name =

23、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_numberselect distance = distance from ticket_info where ticket_number = ticke

24、t_numberselect 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 ca

25、bin_w = cabin) begin 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 f

26、light_number = flight_number update Passenger set distances = distances + distance where passenger_name = passenger_name endcommit tranend在宿主语言中调用存储过程代码段（粘贴）：public void executeProc (int ticket_number)trycon = DriverManager.getConnection(url);PreparedStatement cmd = con.prepareStatement(call cancel_

27、ticket(?);cmd.setInt(1, ticket_number);cmd.executeUpdate();catch(Exception ex) ex.printStackTrace();public void executeProc (String fn, String pn, String cb, String em)trycon = DriverManager.getConnection(url);PreparedStatement cmd = con.prepareStatement(call book_ticket (?, ?, ?, ?);cmd.setString(1

28、, 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.pla

29、ne_number from deleteddelete from Flight where Flight.plane_number = plane_numbercommit 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_n

30、umbercommit 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_numberco

31、mmit 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_nam

32、e and Ticket.flight_number = flight_numbercommit tranend6）、自行设计各模块中所涉及的操作语句、插入数据操作功能界面简单描述；根据用户输入的所订航班信息和订票乘客信息，为用户查看该航班是否有余票：有则插入票务信息，根据该用户的积分情况制定票价，修改其积分里程；若已无余票，则将该乘客登机在侯票名单之中。T-SQL语句与宿主语言嵌套使用代码段（粘贴）；/Jaca public void actionPerformed(ActionEvent e) if(e.getSource() = btn_book) String flight_numbe

33、r = txt_fgt.getText().trim(); String passenger_name = txt_name.getText().trim(); String cabin = txt_cabin.getText().trim(); String E_mail = txt_mail.getText().trim(); DBBase bt= new DBBase(); bt.executeProc(flight_number, passenger_name, cabin, E_mail); -SQLexec book_ticket 0003，张三，头等舱，sanzhang、删除数据

34、操作功能界面简单描述；根据用户输入的“所退票流水号”信息，修改订票乘客的积分信息，将相关的票务记录删除；并在侯票记录中找到符合航班要求的第一名乘客，为其办理订票服务，相关的修改其积分。T-SQL语句与宿主语言嵌套使用代码段；/Java if(e.getSource() = btn_cancel) String ticket_number = txt_ticket.getText().trim(); int ticket_n = Integer.parseInt(ticket_number); DBBase ct= new DBBase(); ct.executeProc(ticket_n);

35、-SQLexec cancel_ticket 17）、若有原子性事务的显示定义。create proc cancel_ticketticket_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

36、flight_number = flight_number from ticket_info where ticket_number = ticket_numberselect cabin = cabin from ticket_info where ticket_number = ticket_numberselect distance = distance from ticket_info where ticket_number = ticket_numberselect pay = pay from ticket_info where ticket_number = ticket_num

37、berupdate 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) begin select passenger_name = passenger_name from Wait wher

38、e 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 +

39、distance where passenger_name = passenger_name endcommit tranend五、系统实现主要模块描述以及关键代码JPanel ticket_info = new JPanel(); /售票情况总览 ticket_info.setLayout(new BorderLayout();JScrollPane jsp_t = new JScrollPane(tab_ti);String cols_ticket = 航班号, 头等舱额定票数, 头等舱已售票数, 头等舱候补等票票数 ,普通舱定额票数, 普通舱已售票数, 普通舱候补等票数; DefaultTableModel model_tkt = new DefaultTableModel(cols_ticket, 0); String sql_tkt = select * from book_info; DBBase tkt = new DBBase(); ResultSet rs_tkt = tkt.executeQuery(sql_tkt);try while(rs_tkt.next() String flight_number = rs_tkt.getString(1); String quota_f = rs_tkt.getString(2); String co