毕业设计-课程设计飞机订票系统.doc

数据库课程设计 题 目 _飞机订票系统_ _学 院 计算机学院 专 业 计算机科学与技术 年级班别 学 号 学生姓名 指导教师 左亚尧 2013 年 1 月 15 日目 录一、需求分析.1二、系统设计.3三、详细设计6四、课程设计总结16五、参考文献16六、附录16一、需求分析（一）系统综合需求航空订票管理系统主要是为机场、航空公司和客户三方服务。航空公司提供航线和飞机的资料，机场则对在本机场起飞和降落的航班和机票进行管理，而客户能得到的服务应该有查询航班路线和剩余票数，以及网上订票等功能。客户可以分为两类：一类是普通客户，对于普通客户只有普通的查询功能和订票功能没有相应的票价优惠；另一类是经常旅客，需要办理注册手续，但增加了里程积分功能和积分优惠政策。机场还要有紧急应对措施，在航班出现延误时，要发送相应的信息。（二）系统功能航班查询：根据用户输入的“起飞机场” 和“降落机场”信息找到符合要求的航班号，并显示相应的起飞时间、飞行里程、各舱等的余票情况、各舱等的票价情况。供用户进行选择。订票： 根据用户输入的“所订航班号”和“订票乘客信息”，为用户查看该航班是否有余票：有则生成一条新的票务信息，根据该用户的积分情况制定票价，修改其积分里程；若已无余票，则将该乘客登机在侯票名单之中。退票：根据用户输入的“所退票流水号”信息，修改订票乘客的积分信息，将相关的票务记录删除；并在侯票记录中找到符合航班要求的第一名乘客，为其办理订票服务，相关的修改其积分。售票情况统计：统计每个航班的各个舱位等级的已售票数目，侯票数目。积分功能：为每一个已订购的乘客记录其飞行里程积分，当积分达到一定程度后，为其提供打折的票价。航班延误：当要延误一个航班时，找到已订购该航班的乘客，发送提示信息。2数据字典该软件的数据库由下述四张数据表组成： 1、航空公司： Company 字段字段名类型宽度说明1company_name varchar20航空公司2phone varchar15公司电话2、飞机信息：Plane 字段字段名类型宽度说明1plane_number varchar1.0飞机编号2plane_type varchar20飞机类型3quota_f int 头等舱定额数，4quota_s int普通舱定额数5company_namevarchar20所属公司3、航线信息 Line 字段字段名类型宽度说明1line_numbervarchar10航线号码2airport_s varchar20起飞地点3airport_evarchar20起飞终点4distance int距离5company_namevarchar20所属公司4、航班信息： Flight 字段字段名类型宽度说明1flight_numbervarchar10航班信息2plane_number varchar10飞机号3line_number varchar10航线4price_f int头等舱价格5price_sint普通舱价格6flydate varchar20飞行日期5、客户信息 ： Passenger 字段字段名类型宽度说明1passenger_namevarchar 10 客户名2distancesint 积分3 E_mail varchar 20 邮箱6、订票信息 ：Ticket 字段字段名类型宽度说明1ticket_numberint票号 （自动增一）2flight_number varchar10航班号3passenger_name varchar10乘客名4cabin varchar头等舱还是普通舱5payint价格6、候选票信息 ： Wait 字段字段名类型宽度说明1passenger_namevarchar乘客名2flight_number varchar10航班号3cabin varchar头等舱还是普通舱4E_mail varchar10邮箱三、系统设计（一）数据库概念设计E-R图如下：（二）数据库逻辑设计将数据库的概念模型转换为关系模型：航空公司表：（公司名称，公司电话）；飞机表：（飞机号， 机型， 头等舱定额数， 普通舱定额数， 所属航空公司）；航线表：（航线号， 起飞机场， 降落机场， 飞行里程， 所属航空公司）；航班表：（航班号， 执行飞机号， 所飞航线号， 头等舱票价， 普通舱票价， 起飞时间）；乘客表：（乘客姓名， 里程积分， E_mail）；机票表：（机票流水号， 所乘航班号，订票乘客姓名， 所乘舱位等级， 票价）；侯票表：（所侯航班， 侯票乘客姓名， 所需舱位等级， 乘客E_mail）；（三）数据库与数据表设计 物理结构设计在SQL数据库中需要建立3个数据表：表、部门信息数据表和工资数据表。1.数据库与数据表设计company Flight LinePassengerPlaneTicket Wait 2.数据完整性设计1）给每个表实施主键约束。主键图中PK2）实施CHECK约束全部not null3）外键约束外键途中FK4）惟一约束 每个表的主键需定义为惟一性：5) 强制约束6）规则cabin 约束为 (cabin = 头等舱 or cabin = 普通舱)两种类型。四、详细设计（一）建数据库CREATE DATABASE PLANE_DB ON PRIMARY(NAME=PLANE_DB ,FILENAME=D:PLANE_DB .mdf,SIZE=4MB,MAXSIZE=6MB,FILEGROWTH=2MB)LOG ON (NAME =PLANE_DB _log, FILENAME =D:PLANE_DB .ldf, SIZE=1MB,MAXSIZE=25MB,FILEGROWTH=10%)（二）建表create table Company (company_name varchar(20) primary key,phone varchar(15) not null)create table Plane (plane_number varchar(10) primary key,plane_type varchar(20) not null,quota_f int not null,quota_s int not null,company_name varchar(20) foreign key references Company(company_name) )create table Line (line_number varchar(10) primary key,airport_s varchar(20) not null,airport_e varchar(20) not null,distance int not null,company_name varchar(20) foreign key references Company(company_name) )create table Flight (flight_number varchar(10) primary key,plane_number varchar(10) foreign key references Plane(plane_number) ,line_number varchar(10) foreign key references Line(line_number) ,price_f int not null,price_s int not null,flydate varchar(20) not null)create table Passenger (passenger_name varchar(10) primary key,distances int not null,E_mail varchar(20) not null)create table Ticket (ticket_number int identity(1, 1) primary key,flight_number varchar(10) foreign key references Flight(flight_number),passenger_name varchar(10) foreign key references Passenger(passenger_name),cabin varchar(10) check (cabin = 头等舱 or cabin = 普通舱) not null,pay int check (pay0) not null)create table Wait (passenger_name varchar(10) foreign key references Passenger(passenger_name) ,flight_number varchar(10) foreign key references Flight(flight_number) ,cabin_w varchar(10) not null,primary key (passenger_name, flight_number),E_mail varchar(20) not null)（三）视图(1)查询头等舱订票量create view flight_cf (flight_number, count_f)asselect flight_number, count(ticket_number)from Ticket where cabin = 头等舱 group by flight_number(2)查询普通舱订票量create view flight_cs (flight_number, count_s)asselect flight_number, count(ticket_number)from Ticket where cabin = 普通舱 group by flight_number(3)查询某航班的普通舱和头等舱总量create 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_number （四）存储过程-订票create proc book_ticketflight_number varchar (10), passenger_name varchar (10), cabin varchar(10), E_mail varchar(20)asbeginbegin trandeclare 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 else 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) 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 = 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 = ticket_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 cabin_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 flight_number = flight_number update Passenger set distances = distances + distance where passenger_name = passenger_name endcommit tranend（五）触发器-级联删除飞机所对应的航班create trigger pln_delon Planefor deleteasbeginbegin trandeclare plane_number varchar(10)select plane_number = deleted.plane_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_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 tranend（六）索引（七）运行和维护1. 关系图的建立 2简单查询1)航空公司基本信息情况 代码如下：select *from Company;2） 航线的基本信息情况代码如下： select *from Flight; 3）航班的订票信息。代码如下：select *from Ticket;4)航线信息代码如下：select *from Line;5）查询订票客户代码如下：select *from Ticket;3复杂查询1）查询航班F001所剩下的头等舱座位：代码如下：select Flight.flight_number as 航班号,(Plane.quota_f-flight_cf.count_f) as 头等舱剩余票数 from Plane,Flight,flight_cfwhere Flight.Flight_number=F001 AND Flight.plane_number=Plane.plane_number AND flight_cf.flight_number=F001; 2) 查询航班F001所剩下的普通舱座位：select Flight.flight_number as 航班号,(Plane.quota_s-flight_cs.count_s) as 普通舱剩余票数 from Plane,Flight,flight_cswhere Flight.Flight_number=F001 AND Flight.plane_number=Plane.plane_number AND flight_cs.flight_number=F001; 5数据库的备份6.数据的导入与导出五、课程设计总结经过一周的时间，同学，互联网的帮助下，基本完成本次课程设计，基本达到了飞机订票系统的要求。这次数据库课程设计给我的最大的印象是：数据存储在数据库系统有很多种方法。本次实验，我熟悉利用范式来构建数据库的逻辑结构。有的人说：流程是一切的根本；架构是重要的；很多程序要在机子上跑一遍才知道自己理论的不娴熟。计算机就是一门以实验为主的科目。从做这个数据库开始无论遇到什么困难，要学会利用互联网这个资源才能很好地解决问题。正所谓磨刀不误砍柴功，在设计过程中，一开始我习惯凭空想象，不画流程图，但问题百出，后来，试着实验的方法，先，逐步按标准的方法去设计数据库，才是最有效率的。做实例过程中要注意与说明书中数据项数据类型，数据长度等的一致，命令中心输入任何命令都要谨记一条原则，那就是严格按照格式来，考虑各种约束才行。在本系统的设计与实现的过程中，对于客户来说订票，买票，查询所买的票的飞机信息的主要设计。系统设计部分，包括功能模块设计、数据库结构设计等。系统设计为整个程序构建了骨架,各个功能模块实现各个细节部分。 六、参考文献1 数据库系统概论：高等教育出版社; 七、附录 部分源代码21