机票预定信息管理系统.doc

1、目 录1 前言12 需求分析12.1 课程设计目的12.2 课程设计任务12.3 设计环境22.4 开发语言23 分析和设计23.1 系统E-R模型23.2 表空间及表的设计33.3 视图设计83.4 存储过程、函数、包的设计123.5 触发器设计173.6 角色、用户、权限设计183.7 备份方案设计194 课程设计总结20参考文献21致 谢211 前言航空客运业务诞生已有近一个世纪了，作为现有交通工具中最方便快捷的一种，它确确实实地给民众的生活、出行带来了极大的方便。随着社会的进步，人们的生活水平得到很大的提高，民航的发展也很迅速，乘坐飞机的旅客也大大增多。假如现在没有售票系统，民航将也不

2、会存在，人们的出行极大不便。换句话说，一个功能完善的机票预定信息管理系统对人们的日常生活已经显得尤其重要。现设计一个机票预定信息管理系统的数据库。首先根据问题需求设计E-R模型，包括几个主要的实体，如航空公司、飞机、乘客、航班、机票、业务员等以及相关的联系。然后再转根据E-R模型设计出相应的关系模型，对于关系模型的每一个关系，可以设计出相应的表，接着分配表空间、创建视图、设计存储过程、函数和触发器以及设计安全与备份策略。2 需求分析2.1 课程设计目的通过专业课程设计，即大型数据库系统课程设计，有助于培养学生综合运用数据库相关知识解决实际问题的能力。本设计要求对实际问题进行需求分析，提炼实际问

3、题中的数据，建立关系模型，并在大型数据库中得以实现。同时要求对数据库的运营、管理及使用上进行必要的规划和实现。2.2 课程设计任务系统需要管理以下主要信息：（1）航班基本信息，包括航班的编号、飞机名称、机舱等级等。（2）机票信息，包括票价、折扣、当前预售状态及经手业务员等。（3）客户基本信息，包括姓名、联系方式、证件及号码、付款情况等。基本要求：（1）根据需求，补充必要的数据库实体，建立ER模型，通过ER图表示。（2）在Oracle中创建该系统的数据库，并在数据库中实现各表，写入一定的数据。（3）从实际查询应用出发，为一些主要的应用模块设计至少3个参数化视图。（4）从数据检验的角度出发，为相关

4、的表建立至少1个触发器。（5）从数据更新或修改的角度出发，设计至少1个存储过程。（6）从安全的角度出发，规划系统的角色、用户、权限，并通过相关的SQL实现。（7）预计每个表的大致容量和增长速度，指定备份的方案，写出相关的备份命令。2.3 设计环境（1）WINDOWS 2000/2003/XP系统（2）Oracle数据库管理系统2.4 开发语言PL/SQL语言3 分析和设计3.1 系统E-R模型经过分析可以知道，机票预定信息管理系统一共有航空公司、飞行、航班、机舱、机票、乘客和业务员这几个实体，航空公司有公司编号、公司名、公司电话和公司地址这几个属性；飞机有飞机编号、飞机名称两个属性；航班有航班

5、号、出发地、目的地、起飞时刻和飞行时间这几个属性；机舱有机舱等级、座位数、定价和折扣这几个属性；机票有机票编号、登机日期、预定状态、座位号这几个属性；乘客有身份证号、姓名、联系电话、住址这几个属性；业务员有业务员编号、业务员姓名、业务员身份证号、联系电话和住址这几个属性。一个航空公司有多架飞机和多名业务员，一架飞机可有多个航班，一个航班有多种机舱等级，一个机舱可有多张机票；乘客、业务员和机票之间有售票联系，售票联系有售票日期这一属性。根据以上分析可以画出系统E-R图，系统E-R图如图3.1所示：图3.1 系统E-R图3.2 表空间及表的设计（1）表空间的设计。经过分析可知，乘客表、机票信息表和

6、机票销售表数据量比较大，可单独分配表空间，其他的表数据量较少，可一起使用一个表空间。创建表分配表空间和添加数据文件，SQL语句如下：CREATE SMALLFILE TABLESPACE PASSENGER DATAFILE F:APPORACLEORADATAORCLTICKETSALEpassenger.dbfSIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;CREATE SMALLFILE TABLESPACE TI

7、CKET DATAFILE F:APPORACLEORADATAORCLTICKETSALEticket.dbf SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDLOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;CREATE SMALLFILE TABLESPACE TICKETSALE DATAFILE F:APPORACLEORADATAORCLTICKETSALEticketsale.dbf SIZE 100M AUTOEXTEND ON NEXT 5M MAXSI

8、ZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;CREATE SMALLFILE TABLESPACE OTHERS DATAFILE F:APPORACLEORADATAORCLTICKETSALEothers .dbf SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;表空间结构如图3.2所示：图3.2 表空间结构图（

9、2）关系模型的设计。根据E-R模型，可以得出相应的关系模型，关系模型如下：company（cno，cname，ctel，caddress）passenger（pID，pname，ptel，paddress）salesman（sno，sID，sname，stel，saddress，cno）airplane（ano，aname，cno）flight（fno，departure，arrival，time，flytime，ano）cabin（fno，cblevel，seats，price）ticket（tno，fno，cblevel，flydate，status，seat，discount）ticke

10、tsale（tno，pID，sno，saledate）（3）表的创建。对于关系模型的每一个关系，可以得出相应的一张表，创建表的SQL语句如下：CREATE TABLE SYSTEM.COMPANY (CNO VARCHAR2(10) NOT NULL , CNAME VARCHAR2(20) NOT NULL , CTEL VARCHAR2(20), CADDRESS VARCHAR2(50), PRIMARY KEY (CNO) VALIDATE ) TABLESPACE OTHERS;CREATE TABLE SYSTEM.PASSENGER ( PID VARCHAR2(20) NOT

11、NULL , PNAME VARCHAR2(20) NOT NULL , PTEL VARCHAR2(20), PADDRESS VARCHAR2(50), PRIMARY KEY (PID) VALIDATE ) TABLESPACE PASSENGER;CREATE TABLE SYSTEM.SALESMAN ( SNO VARCHAR2(10) NOT NULL , SID VARCHAR2(20) NOT NULL , SNAME VARCHAR2(20) NOT NULL , STEL VARCHAR2(20), SADDRESS VARCHAR2(50), CNO VARCHAR2

12、(10) NOT NULL , PRIMARY KEY (SNO) VALIDATE ,FOREIGN KEY (CNO) REFERENCES SYSTEM.COMPANY (CNO) VALIDATE) TABLESPACE OTHERS;CREATE TABLE SYSTEM.AIRPLANE ( ANO VARCHAR2(10) NOT NULL , ANAME VARCHAR2(20) NOT NULL , CNO VARCHAR2(10) NOT NULL ,PRIMARY KEY (ANO) VALIDATE ,FOREIGN KEY (CNO) REFERENCES SYSTE

13、M.COMPANY (CNO) VALIDATE) TABLESPACE OTHERS;CREATE TABLE SYSTEM.FLIGHT ( FNO VARCHAR2(10) NOT NULL , DEPARTURE VARCHAR2(20) NOT NULL , ARRIVAL VARCHAR2(20) NOT NULL , TIME DATE NOT NULL , FLYTIME INTERVAL DAY TO SECOND NOT NULL , ANO VARCHAR2(10) NOT NULL , PRIMARY KEY (FNO) VALIDATE ,FOREIGN KEY (A

14、NO) REFERENCES SYSTEM.AIRPLANE (ANO) VALIDATE ) TABLESPACE OTHERS;CREATE TABLE SYSTEM.CABIN ( FNO VARCHAR2(10) NOT NULL , CBLEVEL NUMBER(1) NOT NULL , SEATS NUMBER(3) NOT NULL ,PRICE NUMBER(5) NOT NULL , PRIMARY KEY (FNO, CBLEVEL) VALIDATE ,FOREIGN KEY (FNO) REFERENCES SYSTEM.FLIGHT (FNO) VALIDATE)

15、TABLESPACE OTHERS;CREATE TABLE SYSTEM.TICKET ( TNO NUMBER(10) NOT NULL , FNO VARCHAR2(10) NOT NULL , CBLEVEL NUMBER(1) NOT NULL , FLYDATE DATE NOT NULL , STATUS NUMBER(1) DEFAULT 1 NOT NULL , SEAT NUMBER(3) NOT NULL , DISCOUNT NUMBER(3, 2) NOT NULL ,PRIMARY KEY (TNO) VALIDATE ,FOREIGN KEY (FNO, CBLE

16、VEL) REFERENCES SYSTEM.CABIN (FNO, CBLEVEL) VALIDATE) TABLESPACE TICKET;CREATE TABLE SYSTEM.TICKETSALE ( TNO NUMBER (10) NOT NULL , PID VARCHAR2(20) NOT NULL , SNO VARCHAR2(10) NOT NULL , SALEDATE DATE NOT NULL , PRIMARY KEY (TNO, PID, SNO) VALIDATE ,FOREIGN KEY (TNO) REFERENCES SYSTEM.TICKET (TNO)

17、VALIDATE , FOREIGN KEY (PID) REFERENCES SYSTEM.PASSENGER (PID) VALIDATE , FOREIGN KEY (SNO) REFERENCES SYSTEM.SALESMAN (SNO) VALIDATE) TABLESPACE TICKETSALE;（4）为表添加数据由于表company、salesman、airplane、flight和cabin是公司管理员通过应用程序的管理端预先录入的，因此应用程序需要为这些表添加一定数据，应用程序将会执行INSERT语句对表进行插入数据。company的数据如表3.1所示：CNO CNAME

18、CTELCADDRESSC0001朝云航空15173913883广东省广州市C0002北京航空18601088888北京市C0003长沙航空18607318888湖南省长沙市表3.1 company表的数据salesman的数据如表3.2所示：SNO SIDSNAMESTELSADDRESSCNOS0001440902198811111111邓春国06682888888广东省茂名市茂南区C0001S0002440902198811111111王军01022888888福建省漳州市C0002S0003440902198822222222丁磊07398888888湖南省邵阳市C0003S00044

19、40902198811111118暮云06682888886广东省茂名市茂南区C0001表3.2 salesman表的数据airplane的数据如表3.3所示：ANO ANAMECNOA0001波音737C0001A0002波音777C0001A0003波音737C0002A0004麦道82C0003表3.3 airplane表的数据flight的数据如表3.4所示：FNO DEPARTUREARRIVALTO_CHAR(TIME,HH-MI-SS)FLYTIMEANOF0001广州北京07-50-000 3:30:0.0A0001F0002北京广州12-30-000 3:30:0.0A000

20、1F0003广州长沙08-00-000 1:5:0.0A0002F0004长沙广州10-20-000 1:5:0.0A0002F0005北京长沙09-10-000 2:50:0.0A0003F0006长沙北京12-50-000 2:50:0.0A0003F0007长沙广州07-35-000 1:10:0.0A0004F0008广州长沙10-05-000 1:10:0.0A0004表3.4 flight表的数据cabin的数据如表3.5所示：FNO CBLEVELSEATSPRICEF0001150900F0001280700F0002150900F0002280700F0003130500F0

21、003250400F0003370300F0004130500F0004250400F0004370300F0005150800F0005270600F0006150800F0006270600F00071120400F00081120400表3.5 cabin表的数据3.3 视图设计应用程序需要查询航班信息，因此需要创建一个航班信息的视图，根据参数航班号或者出发地以及目的地查询航班信息，显示航班号、公司名、飞机名称、出发时间、到达时间、出发地和目的地。由于oracle的视图不支持参数，但可以利用临时表作为参数进行传递，因此需要创建一个临时表，创建临时表的SQL语句如下： CREATE GLO

22、BAL TEMPORARY TABLE SYSTEM.INPUT_TO_FLIGHT ( T_FNO VARCHAR2(10), T_DEPARTURE VARCHAR2(20), T_ARRIVAL VARCHAR2(20),T_FLYDATE DATE) ON COMMIT PRESERVE ROWS;创建参数化视图的SQL语句如下：CREATE OR REPLACE VIEW SYSTEM.FLIGHT_VIEW_BYFNO (FNO,CNAME,ANAME,TIME,ARRIVAL_TIME,DEPARTURE,ARRIVAL) AS SELECT fno,cname,aname,ti

23、me,time+flytime,departure,arrival FROM flight,company,airplane,input_to_flight WHERE flight.ano=airplane.ano AND o=o AND fno=input_to_flight.T_fno;CREATE OR REPLACE VIEW SYSTEM.FLIGHT_VIEW_BYSITE (FNO,CNAME,ANAME,TIME,ARRIVAL_TIME,DEPARTURE,ARRIVAL) AS SELECT fno,cname,aname,time,time+flytime,depart

24、ure,arrival FROM flight,company,airplane,input_to_flight WHERE flight.ano=airplane.ano AND o=o AND departure=input_to_flight.T_departure AND arrival=input_to_flight.T_arrival;应用程序还可以根据航班号和航班日期查询余票信息，因此需要创建一个余票信息的视图，显示某一航班某一日期不同机舱等级的剩余座位数、定价和折扣等信息。因此创建一个参数化的视图，其中计算余票使用函数count_ticket，创建视图的SQL语句如下：CREA

25、TE OR REPLACE VIEW SYSTEM.REMAIN_SEATS_VIEW (FNO,FLYDATE,CBLEVEL,COUNT) AS SELECT DISTINCT fno,flydate,cblevel, count_ticket(fno,flydate,cblevel) FROM ticket,input_to_flight WHERE fno=input_to_flight.t_fno AND flydate=input_to_flight.T_FLYDATE;假设应用程序要查询“茂名长沙”的航班信息时，应用程序先执行以下SQL语句：INSERT INTO input_t

26、o_flight VALUES(,茂名,长沙,); SELECT * FROM flight_view_bysite;此时系统会返回查询的结果如图3.3所示：图3.3 flight_view_bysite视图查询的数据这时候假设再想查询航班F0003、日期为2011年6月1日的余票信息，应用程序会执行以下SQL语句：INSERT INTO input_to_flight VALUES(F0003, to_date(2011-6-1,yyyy-mm-dd); SELECT * FROM remain_seats_view ORER BY cblevel;此时系统会返回查询的结果如图3.4所示：图

27、3.4 remain_seats_view视图查询的数据在乘客确定好要预订的机票的时候，系统需要打印机票，机票上需要显示机票编号、航班号、公司名、飞机名称、出发地、目的地、机票日期、出发时间、到达时间、机舱等级、座位号、定价、折扣、售价以及乘客姓名、乘客身份证号和业务员姓名，因此可以创建一个视图，创建视图的SQL语句如下：CREATE OR REPLACE VIEW SYSTEM.TICKET_INFO_VIEW (TNO,FNO,CNAME,ANAME,DEPARTURE,ARRIVAL,FLYDATE,TIME,ARRIVAL_TIME,CBLEVEL,SEAT,PRICE,DISCOUN

28、T,FINAL_PRICE,PNAME,PID,SNAME) AS SELECT ticket.tno, ticket.fno , cname , aname , departure, arrival, flydate, time, time+flytime, ticket.cblevel , seat , price , discount, price*discount , pname, passenger.pID, sname FROM ticket , flight , airplane, company, passenger,salesman,ticketsale, cabinWHER

29、E ticket.fno =flight.fno AND flight.ano=airplane.ano AND o=o AND ticketsale.tno=ticket.tno AND ticketsale.pid=passenger.pid AND ticketsale.sno=salesman.snoAND ticket.fno=cabin.fnoAND ticket.cblevel=cabin.cblevel;应用程序需要查询售票记录，因此可以创建一个视图来查询机票编号、乘客姓名、乘客身份证号、业务员编号、业务员姓名和购票日期，创建视图的SQL语句如下：CREATE OR REPLA

30、CE VIEW SYSTEM.SALERECORD_VIEW (TNO,PNAME,PID,SNO,SNAME,SALEDATE) AS SELECT ticketsale.tno,pname,ticketsale.pID,ticketsale.sno,sname,saledate FROM ticket,passenger,salesman,ticketsale WHERE ticket.tno=ticketsale.tno AND ticketsale.pid=passenger.pid AND ticketsale.sno=salesman.sno;视图salerecord_view的查

31、询结果如图所示3.5： 图3.5 salerecord_view视图查询的数据应用程序需要统计业务员的业绩，需要显示业务员编号、业务员姓名、航空公司名和销售总额。因此创建视图sale_grade_view来查询销售总额，SQL如下所示：CREATE OR REPLACE VIEW SYSTEM.SALE_GRADE_VIEW (SNO,SNAME,CNAME,SUM) AS SELECT ticketsale.sno, sname , cname , SUM(price*discount) FROM ticketsale , salesman , company ,ticket , cabin

32、 WHERE salesman.sno =ticketsale.sno AND o =o AND ticket.tno =ticketsale.tno AND cabin.fno =ticket.fno AND cabin.cblevel=ticket.cblevel GROUP BY ticketsale.sno, sname , cname视图sale_grade_view查询结果如图3.6所示：图3.6 sale_grade_view视图查询的数据3.4 存储过程、函数、包的设计（1）存储过程设计管理员需要对机票信息的录入，由于机票信息的数据量庞大，手工输入是不现实的事情，因此需要采用存

33、储过程来进行对数据的录入。为了使机票编号有序且不重复，因此需要创建一个表，表存放当前机票编号，创建表和存储过程SQL语句如下：CREATE TABLE SYSTEM.T_NUMBER ( TNO NUMBER(10);CREATE OR REPLACE PROCEDURE SYSTEM.CREATE_TICKET ( p_fno varchar2, p_flydate date, p_discount number ) as v_cblevel_count number; v_ticket_count_by_cblevel number;v_tno number; begin SELECT c

34、ount(1) INTO v_cblevel_count FROM cabin WHERE fno=p_fno; SELECT tno INTO v_tno FROM t_number;FOR v_i in 1.v_cblevel_count loop SELECT seats INTO v_ticket_count_by_cblevel FROM cabin WHERE fno=p_fno AND cblevel=v_i; FOR v_j IN 1.v_ticket_count_by_cblevel loop INSERT INTO ticket VALUES(v_tno,p_fno,v_i

35、,p_flydate,1,v_j,p_discount);v_tno:=v_tno+1; END LOOP; END LOOP; UPDATE t_number SET tno=v_tno;END;假设管理员需要插入航班号为F0003、日期为2011年6月10日、折扣为0.7的机票，这需要执行过程CREATE_TICKET，过程执行的SQL语句如下：CALL create_ticket(F0003,to_date(2011-6-10,yyyy-mm-dd),0.7);此时ticket表的数据就会更新，ticket表的部分数据如表3.6所示：TNO FNOCBLEVELTO_CHAR(FLYDA

36、TE,YYYY-MM-DD)STATUSSEATDISCOUNT1F000312011-6-10 110.72F000312011-6-10 120.73F000312011-6-10 130.74F000312011-6-10140.75F000312011-6-10150.76F000312011-6-10160.77F000312011-6-10170.78F000312011-6-10 180.7表3.6 ticket表的部分数据乘客在买票时，需要对售票（ticketsale）表进行插入数据，因此可以创建一个存储过程对售票这一过程进行操作，并且打印机票的信息，存储过程把需要打印的票的信

37、息存放在临时表上，创建临时表和存储过程的SQL语句如下所示：CREATE GLOBAL TEMPORARY TABLE SYSTEM.PRINT_TICKET ( TNO VARCHAR2(10), FNO VARCHAR2(10), CNAME VARCHAR2(20), ANAME VARCHAR2(20), DEPARTURE VARCHAR2(20), ARRIVAL VARCHAR2(20), FLYDATE DATE, TIME DATE , ARRIVAL_TIME DATE, CBLEVEL NUMBER(1), SEAT NUMBER(3), PRICE NUMBER(5),

38、 DISCOUNT NUMBER(3, 2), FINAL_PRICE NUMBER, PNAME VARCHAR2(20), PID VARCHAR2(20), SNAME VARCHAR2(20) ) ON COMMIT PRESERVE ROWS;CREATE OR REPLACE PROCEDURE SYSTEM.CREATE_RECORD ( p_fno ticket.fno%TYPE,p_flydate date,p_cblevel ticket.cblevel%TYPE,p_pid passenger.pid%TYPE,p_sno salesman.sno%TYPE ) AS v

39、_tno ticket.tno%TYPE; v_row ticket_info_view%ROWTYPE;BEGIN SELECT min(tno) INTO v_tno FROM ticket WHERE fno=p_fno AND flydate=p_flydate AND cblevel=p_cblevel AND status=1; INSERT INTO ticketsale VALUES(v_tno,p_pid,p_sno, sysdate);UPDATE ticket SET status=0 WHERE tno=v_tno; SELECT * INTO v_row FROM t

40、icket_info_view WHERE tno=v_tno;INSERT INTO print_ticket VALUES(v_row.tno,v_row.fno,v_ame,v_row.aname,v_row.departure,v_row.arrival,v_row.flydate,v_row.time,v_row.arrival_time,v_row.cblevel,v_row.seat,v_row.price,v_row.discount,v_row.final_price,v_row.pname,v_row.pid,v_row.sname);END;假设乘客邓春国要购买一张航班号

41、为F0003、日期为6月1号的机票，售票员工号为S0001，则系统先让乘客输入身份证号，查询该乘客是否存在，存在的话显示乘客信息并提示是否需要更新，否则添加一个乘客信息，假设该乘客的记录不存在，则先进行插入操作，然后执行CREATE_RECORD 过程并且查询临时表PRINT_TICKET的数据用于打印机票，SQL语句如下：INSERT INTO passenger VALUES(440902198803310855, 邓春国, 15173913883, 广东省茂名市茂南区);CALL create_record(F0003,to_date(2011-6-1,yyyy-mm-dd),1,440

42、902198803310855,S0001);SELECT * FROM print_ticket;查询结果如图3.7所示：图3.7 print_ticket表的数据（2）函数设计在售票的时候需要计算剩余票的数量，因此可以创建一个函数来计算剩余票的数量，函数的参数为航班号、航班日期和机舱等级，函数的创建如下：CREATE OR REPLACE FUNCTION SYSTEM.COUNT_TICKET ( p_fno flight.fno%TYPE,p_flydate date,p_cblevel cabin.cblevel%TYPE ) RETURN number AS v_count num

43、ber; BEGIN SELECT count(1) INTO v_count from ticket WHERE fno=p_fno AND flydate=p_flydate AND cblevel=p_cblevel AND status=1; RETURN v_count; END;（1）包设计可以把以上的存储过程和函数封装到包里面，创建包的SQL语句如下：CREATE OR REPLACE PACKAGE SYSTEM.SALES_SYSTEM AS FUNCTION count_ticket(p_fno flight.fno%TYPE,p_flydate date,p_cbleve

44、l cabin.cblevel%TYPE )RETURN number; PROCEDURE create_ticket( p_fno varchar2, p_flydate date, p_discount number ); PROCEDURE create_record( p_fno ticket.fno%TYPE, p_flydate date, p_cblevel ticket.cblevel%TYPE, p_pid passenger.pid%TYPE, p_sno salesman.sno%TYPE ); END;创建包体的SQL语句如下：CREATE OR REPLACE PACK