民航订票管理系统

实验十三数据库管理系统综合应用-------民航订票管理系统一、实验目的：通过完成从用户需求分析、数据库设计到上机编程、调试和应用等全过程，进一步了解和掌握所讲解的内容。

二、实验简述：民航订票系统主要分为机场、航空公司和客户三方的服务。

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

客户又可以分为两类，一类是普通客户，对于普通客户只有普通的查询功能和订票功能，没有相应的机票优惠，另一种是经常旅客，需要办理注册手续，但增加了里程积分功能和积分优惠政策。

机场还要紧急应对措施，在航班出现延误时，要发送相应的信息。

三、实验要求：完成该系统的数据库设计；用SQL实现数据库的设计，并在SQL Server上调试通过。

四、参考答案：1、需求分析（1）航空公司航空公司的操作流程如图C.1所示。

图C.1 航空公司操作分类表（2）客户客户的操作流程如图C.2所示。

图C.2 客户操作分类表（3）机场机场的任务是根据航空公司提供的航线和飞机，安排航班，以及航班的机票。

如果出现晚点等情况，要记录并发送信息，对特殊客户记录其消费信息，并相应提供优惠。

（4）客户订票客户订票涉及到多个因素：由客户提出订票申请；由机场管理航班机票；对于特殊客户，除给予票价优惠以外，还要累计里程；订票后需判断是否超员。

这些因素涉及到客户资料、航班资料以及由航空公司提供的航线（里程）和飞机（座位数）资料中所提供的相关数据。

客户订票的操作流程如图C.3所示。

2、概念模型设计数据库需要表述的信息有以下几种：（1）航空公司信息（2）客户信息（3）飞机信息（4）航线信息（5）航班信息（6）订票信息（7）特殊客户积分可以用E/R模型表述该模型的设计，E/R图如图C.4所示。

图C.4 模型的E-R图3、逻辑设计通过E/R模型到关系模型的转化，可以得到如下关系模式：（1）Airline(AID, Name, Addr, Cont)（2）Customer(CID, Name, Cont, IsSpec, Points)（3）Plane(PID, Type, SeatsNum, AID)（4）Line(LID, SPosition, EPosition, Distance, AID)（5）Flight(FID, PID, LID, Ftime, Price)（6）BookTicket(BID, FID, CID, Pay)每个关系模式的键码都用下划线标出。

外键码用斜体标出。

4、物理设计为了提高在表中搜索元组的速度，在实际实现的时候应该基于键码建立索引。

下面各表中建立索引的表项。

（1）Airline(AID)（2）Customer(CID)（3）Plane(PID)（4）Line(LID)（5）Flight(FID)（6）BookTicket(BID)5、用SQL实现设计（1）建立航空公司表CREATE TABLE Airline(AID varchar(20) PRIMARY KEY,Name varchar(100) NOT NULL,Addr varchar(100),Cont varchar(200));（2）建立客户表CREATE TABLE Customer(CID varchar(20) PRIMARY KEY,Name varchar(50) NOT NULL,Cont varchar(200),IsSpec char(1) NOT NULL DEFAULT ‘N’,Points int NOT NULL DEFAULT 0);（3）建立飞机信息表CREATE TABLE Plane(PID varchar(20) PRIMARY KEY,Type varchar(50) NOT NULL,SeatsNum int NOT NULL,AID varchar(20), CONSTRAINT FK_PLANE_AIDFOREIGN KEY(AID) REFERENCES Airline(AID) );（4）建立航线表CREATE TABLE Line (LID varchar(20) PRIMARY KEY,SPosition varchar(100) NOT NULL,EPosition varchar(100) NOT NULL,Distance real NOT NULLAID varchar(20), CONSTRAINT FK_LINE_AIDFOREIGN KEY(AID) REFERENCES Airline(AID) );（5）建立航班表CREATE TABLE Flight(FID varchar(20) PRIMARY KEY,Ftime datetime NOT NULL,PID varchar(20), CONSTRAINT FK_FLIGHT_PIDFOREIGN KEY(PID) REFERENCES Plane(PID) LID varchar(20), CONSTRAINT FK_FLIGHT_LIDFOREIGN KEY(LID) REFERENCES Line(LID) Price real NOT NULL);（6）建立订票表CREATE TABLE BookTicket (BID int IDENTITY(1,1) PRIMARY KEY,FID varchar(20), CONSTRAINT FK_BOOKTICKET_FIDFOREIGN KEY(FID) REFERENCES Flight(FID) CID varchar(20), CONSTRAINT FK_ BOOKTICKET _CIDFOREIGN KEY(CID) REFERENCES CustomerCID) Pay real NOT NULL);（7）航空公司操作1）注册INSERT INTO Airline(AID, Name, Addr, Cont)V ALUES(#AID, #Name, #Addr, #Cont);这里的#AID必须是惟一的,否则不可以插入到数据库中.2）注销DELETE FROM Airline WHERE(AID = #AID);3）修改公司信息UPDATE AirlineSET Name=#Name, Addr= #Addr, Cont= #Cont WHERE(AID = #AID); 4）增加飞机INSERT INTO Plane(PID, Type, SeatsNum, AID)V ALUES(#PID, #Type, #SeatsNum, #AID);5）删除飞机DELETE FROM Plane WHERE(PID = #PID);6）修改飞机UPDATE PlaneSET Type= #Type, SeatsNum= #SeatsNum WHERE(PID = #PID);7）增加航线INSERT INTO Line(LID, SPosition, EPosition, Distance, AID)V ALUES(#LID, #SPosition, #EPosition, #Distance, #AID);8）删除航线DELETE FROM Line WHERE(LID = #LID);9）修改航线UPDATE LineSET SPosition=#SPosition, EPosition=#EPosition, Distance= #Distance WHERE(PID = #PID);（8）客户1）注册INSERT INTO Customer(CID, Name, Cont)V ALUES(#CID, #Name, #Cont);客户注册的时候,CID必须是惟一的，否则不可以插入数据库。

2）注销DELETE FROM Customer WHERE(CID = #CID);3）修改个人信息UPDATE LineSET Name= #Name, Cont= #Cont WHERE(CID = #CID);4）订票CREATE PROC Book_Ticket@FID varchar(20),@CID varchar(6)ASDECLARE @TransName V ARCHAR(20)SELECT @ TransName=’Book_Ticket’BEGIN TRANSACTION @TransNameDECLARE @booked int, @seat int, @IsSpec char(1)DECLARE @distance real, @discount real, @dist real, @price real----查看客户是否为特殊客户,如果不是,票价不打折扣。

----否则如果客户累计航程超过10万公里，票价打九折；超过20公里，打八折。

----下面的程序用来计算折扣SELECT @IsSpec=IsSpec, @distance=PointsFROM Customer WHERE CID=@CIDSELECT @discount=1IF @IsSpec=’Y’BEGINIF @distance>200000SELECT @distance=0.8ELSE IF @distance>100000SELECT @distance=0.9END----选择出票价SELECT @price= Price FROM Flight WHERE FID=@FID----加入客户订票信息INSERT INTO BookTicket(FID, CID, Pay)V ALUES(@FID, @CID, @price*@discount)----将客户新订票里程的信息累计到用户信息里面SELECT @dist= Distance FROM LineWHERE LID=(SELECT LID FROM Flight WHERE FID=@FID)UPDATE CustomerSET Points= Points+@dist WHERE(CID = #CID);----查看客户订票后，是否超过可容纳的座位数目，如果超过，取消所有操作。

SELECT @seats=SeatsNum FROM PlaneWHERE PID=(SELECT PID FROM Flight WHERE FID=@FID)SELECT booked=count(*) FROM BookTicket WHERE FID=@FIDIF @booked>@seatsROLLBACK TRANSACTION @TransNameELSECOMMIT TRANSACTION @TramsNameGO订票过程一定要做成事务，因为订票的操作应该同时只能有一个人进行。