民航订票管理系统

1实验十三数据库管理系统综合应用-------民航订票管理系统一、实验目的：通过完成从用户需求分析、数据库设计到上机编程、调试和应用等全过程，进一步了解和掌握所讲解的内容。二、实验简述：民航订票系统主要分为机场、航空公司和客户三方的服务。航空公司提供航线和飞机的资料，机场则对本机场起飞和降落的航班和机票进行管理，而客户能得到的服务应该有航班线路和剩余票数的查询，以及网上订票等功能。客户又可以分为两类，一类是普通客户，对于普通客户只有普通的查询功能和订票功能，没有相应的机票优惠，另一种是经常旅客，需要办理注册手续，但增加了里程积分功能和积分优惠政策。机场还要紧急应对措施，在航班出现延误时，要发送相应的信息。三、实验要求：完成该系统的数据库设计；用SQL实现数据库的设计，并在SQLServer上调试通过。四、参考答案：1、需求分析（1）航空公司航空公司的操作流程如图C.1所示。注册注销增加航线/飞机修改航线/飞机删除航线飞机修改公司信息图C.1航空公司操作分类表（2）客户客户的操作流程如图C.2所示。2注册注销查询航班查询余票预定机票修改个人信息图C.2客户操作分类表（3）机场机场的任务是根据航空公司提供的航线和飞机，安排航班，以及航班的机票。如果出现晚点等情况，要记录并发送信息，对特殊客户记录其消费信息，并相应提供优惠。（4）客户订票客户订票涉及到多个因素：由客户提出订票申请；由机场管理航班机票；对于特殊客户，除给予票价优惠以外，还要累计里程；订票后需判断是否超员。这些因素涉及到客户资料、航班资料以及由航空公司提供的航线（里程）和飞机（座位数）资料中所提供的相关数据。客户订票的操作流程如图C.3所示。供应商提出顾客提出交换申请交易员提出交换申请图C.3客户订票的操作流程按普通价格提供订票查找适合的积分优惠政策判断是否是优惠客户?按照优惠价格提供订票增加客户相应里程积分NY2、概念模型设计数据库需要表述的信息有以下几种：（1）航空公司信息（2）客户信息（3）飞机信息（4）航线信息（5）航班信息（6）订票信息（7）特殊客户积分3可以用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）建立航空公司表CREATETABLEAirline(AIDvarchar(20)PRIMARYKEY,Namevarchar(100)NOTNULL,4Addrvarchar(100),Contvarchar(200));（2）建立客户表CREATETABLECustomer(CIDvarchar(20)PRIMARYKEY,Namevarchar(50)NOTNULL,Contvarchar(200),IsSpecchar(1)NOTNULLDEFAULT‘N’,PointsintNOTNULLDEFAULT0);（3）建立飞机信息表CREATETABLEPlane(PIDvarchar(20)PRIMARYKEY,Typevarchar(50)NOTNULL,SeatsNumintNOTNULL,AIDvarchar(20),CONSTRAINTFK_PLANE_AIDFOREIGNKEY(AID)REFERENCESAirline(AID));（4）建立航线表CREATETABLELine(LIDvarchar(20)PRIMARYKEY,SPositionvarchar(100)NOTNULL,EPositionvarchar(100)NOTNULL,DistancerealNOTNULLAIDvarchar(20),CONSTRAINTFK_LINE_AIDFOREIGNKEY(AID)REFERENCESAirline(AID));（5）建立航班表CREATETABLEFlight(FIDvarchar(20)PRIMARYKEY,FtimedatetimeNOTNULL,PIDvarchar(20),CONSTRAINTFK_FLIGHT_PIDFOREIGNKEY(PID)REFERENCESPlane(PID)LIDvarchar(20),CONSTRAINTFK_FLIGHT_LIDFOREIGNKEY(LID)REFERENCESLine(LID)PricerealNOTNULL);（6）建立订票表CREATETABLEBookTicket(BIDintIDENTITY(1,1)PRIMARYKEY,FIDvarchar(20),CONSTRAINTFK_BOOKTICKET_FIDFOREIGNKEY(FID)REFERENCESFlight(FID)CIDvarchar(20),CONSTRAINTFK_BOOKTICKET_CID5FOREIGNKEY(CID)REFERENCESCustomerCID)PayrealNOTNULL);（7）航空公司操作1）注册INSERTINTOAirline(AID,Name,Addr,Cont)VALUES(#AID,#Name,#Addr,#Cont);这里的#AID必须是惟一的,否则不可以插入到数据库中.2）注销DELETEFROMAirlineWHERE(AID=#AID);3）修改公司信息UPDATEAirlineSETName=#Name,Addr=#Addr,Cont=#ContWHERE(AID=#AID);4）增加飞机INSERTINTOPlane(PID,Type,SeatsNum,AID)VALUES(#PID,#Type,#SeatsNum,#AID);5）删除飞机DELETEFROMPlaneWHERE(PID=#PID);6）修改飞机UPDATEPlaneSETType=#Type,SeatsNum=#SeatsNumWHERE(PID=#PID);7）增加航线INSERTINTOLine(LID,SPosition,EPosition,Distance,AID)VALUES(#LID,#SPosition,#EPosition,#Distance,#AID);8）删除航线DELETEFROMLineWHERE(LID=#LID);9）修改航线UPDATELineSETSPosition=#SPosition,EPosition=#EPosition,Distance=#DistanceWHERE(PID=#PID);（8）客户1）注册INSERTINTOCustomer(CID,Name,Cont)VALUES(#CID,#Name,#Cont);客户注册的时候,CID必须是惟一的，否则不可以插入数据库。2）注销DELETEFROMCustomerWHERE(CID=#CID);3）修改个人信息UPDATELineSETName=#Name,Cont=#ContWHERE(CID=#CID);4）订票CREATEPROCBook_Ticket@FIDvarchar(20),@CIDvarchar(6)6ASDECLARE@TransNameVARCHAR(20)SELECT@TransName=’Book_Ticket’BEGINTRANSACTION@TransNameDECLARE@bookedint,@seatint,@IsSpecchar(1)DECLARE@distancereal,@discountreal,@distreal,@pricereal----查看客户是否为特殊客户,如果不是,票价不打折扣。----否则如果客户累计航程超过10万公里，票价打九折；超过20公里，打八折。----下面的程序用来计算折扣SELECT@IsSpec=IsSpec,@distance=PointsFROMCustomerWHERECID=@CIDSELECT@discount=1IF@IsSpec=’Y’BEGINIF@distance200000SELECT@distance=0.8ELSEIF@distance100000SELECT@distance=0.9END----选择出票价SELECT@price=PriceFROMFlightWHEREFID=@FID----加入客户订票信息INSERTINTOBookTicket(FID,CID,Pay)VALUES(@FID,@CID,@price*@discount)----将客户新订票里程的信息累计到用户信息里面SELECT@dist=DistanceFROMLineWHERELID=(SELECTLIDFROMFlightWHEREFID=@FID)UPDATECustomerSETPoints=Points+@distWHERE(CID=#CID);----查看客户订票后，是否超过可容纳的座位数目，如果超过，取消所有操作。SELECT@seats=SeatsNumFROMPlaneWHEREPID=(SELECTPIDFROMFlightWHEREFID=@FID)SELECTbooked=count(*)FROMBookTicketWHEREFID=@FIDIF@booked@seatsROLLBACKTRANSACTION@TransNameELSECOMMITTRANSACTION@TramsNameGO订票过程一定要做成事务，因为订票的操作应该同时只能有一个人进行。（9）机场1）航班安排INSERTINTOFlight(FID,PID,LID,Ftime,Price)VALUES(#FID,#PID,#LID,#Ftime,#Price)；72）通知客户航班变化SELECT[Name]，ContFROMCustomerWHERECID=IN（SELECTCIDFROMBookTicketWHEREFID=#FID）；6、实验总结（请读者结合自己的实际情况，认真总结自己的收获、体会以及有待改进之处。）