当前位置：文档库 › 数据库课程设计--机票预订系统

数据库课程设计--机票预订系统

1 前言 (1)

2 需求分析 (1)

2.1 课程设计目的 (1)

2.2 课程设计任务 (1)

2.3 设计环境 (2)

2.4 开发语言 (2)

3 分析和设计 (2)

3.1 系统E-R模型 (2)

3.2 表空间及表的设计 (3)

3.3 视图设计 (8)

3.4 存储过程、函数、包的设计 (12)

3.5 触发器设计 (17)

3.6 角色、用户、权限设计 (18)

3.7 备份方案设计 (19)

4 课程设计总结 (20)

参考文献 (21)

致谢 (21)

1 前言

航空客运业务诞生已有近一个世纪了，作为现有交通工具中最方便快捷的一种，它确确实实地给民众的生活、出行带来了极大的方便。随着社会的进步，人们的生活水平得到很大的提高，民航的发展也很迅速，乘坐飞机的旅客也大大增多。假如现在没有售票系统，民航将也不会存在，人们的出行极大不便。换句话说，一个功能完善的机票预定信息管理系统对人们的日常生活已经显得尤其重要。

现设计一个机票预定信息管理系统的数据库。首先根据问题需求设计E-R模型，包括几个主要的实体，如航空公司、飞机、乘客、航班、机票、业务员等以及相关的联系。然后再转根据E-R模型设计出相应的关系模型，对于关系模型的每一个关系，可以设计出相应的表，接着分配表空间、创建视图、设计存储过程、函数和触发器以及设计安全与备份策略。

2 需求分析

2.1 课程设计目的

通过专业课程设计Ⅱ，即大型数据库系统课程设计，有助于培养学生综合运用数据库相关知识解决实际问题的能力。本设计要求对实际问题进行需求分析，提炼实际问题中的数据，建立关系模型，并在大型数据库中得以实现。同时要求对数据库的运营、管理及使用上进行必要的规划和实现。

2.2 课程设计任务

系统需要管理以下主要信息：

（1）航班基本信息，包括航班的编号、飞机名称、机舱等级等。

（2）机票信息，包括票价、折扣、当前预售状态及经手业务员等。

（3）客户基本信息，包括姓名、联系方式、证件及号码、付款情况等。

基本要求：

（1）根据需求，补充必要的数据库实体，建立ER模型，通过ER图表示。

（2）在Oracle中创建该系统的数据库，并在数据库中实现各表，写入一定的数据。

（3）从实际查询应用出发，为一些主要的应用模块设计至少3个参数化视图。

（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模型

经过分析可以知道，机票预定信息管理系统一共有航空公司、飞行、航班、机舱、机票、乘客和业务员这几个实体，航空公司有公司编号、公司名、公司电话和公司地址这几个属性；飞机有飞机编号、飞机名称两个属性；航班有航班号、出发地、目的地、起飞时刻和飞行时间这几个属性；机舱有机舱等级、座位数、定价和折扣这几个属性；机票有机票编号、登机日期、预定状态、座位号这几个属性；乘客有身份证号、姓名、联系电话、住址这几个属性；业务员有业务员编号、业务员姓名、业务员身份证号、联系电话和住址这几个属性。

一个航空公司有多架飞机和多名业务员，一架飞机可有多个航班，一个航班有多种机舱等级，一个机舱可有多张机票；乘客、业务员和机票之间有售票联系，售票联系有售票日期这一属性。

根据以上分析可以画出系统E-R图，系统E-R图如图3.1所示：

乘客姓名

身份证号

联系电话

住址

业务员姓名

业务员编号

身份证号

住址

联系电话

航空公司公司名

公司编号

公司电话

公司地址

飞机

飞机编号

飞机名称

售票属于

机票座位号折扣

n 属于

n 1属于

航班航班号

出发地目的地

起飞时刻

飞行时间

11属于n

1机票编号

销售日期

定价

属于

登机日期购票n

1机舱机舱等级

座位数属于

1航班号

销售日期

图3.1 系统E-R 图

3.2 表空间及表的设计

（1）表空间的设计。经过分析可知，乘客表、机票信息表和机票销售表数据量比较大，可单独分配表空间，其他的表数据量较少，可一起使用一个表空间。

创建表分配表空间和添加数据文件，SQL 语句如下：

CREATE SMALLFILE TABLESPACE "PASSENGER"

DATAFILE 'F:\APP\ORACLE\ORADATA\ORCL\TICKETSALE\passenger.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; CREATE SMALLFILE TABLESPACE "TICKET"

DATAFILE 'F:\APP\ORACLE\ORADATA\ORCL\TICKETSALE\ticket.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; CREATE SMALLFILE TABLESPACE "TICKETSALE"

DATAFILE 'F:\APP\ORACLE\ORADATA\ORCL\TICKETSALE\ticketsale.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE SMALLFILE TABLESPACE "OTHERS"

DATAFILE 'F:\APP\ORACLE\ORADATA\ORCL\TICKETSALE\others .dbf ' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED

LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

表空间结构如图3.2所示：

图3.2 表空间结构图

（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）

ticketsale（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 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(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 "SYSTEM"."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 ("ANO") 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

) 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", "CBLEVEL") 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") 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 CTEL CADDRESS

C0001朝云航空151********广东省广州市

C0002北京航空186********北京市

C0003长沙航空186********湖南省长沙市

表3.1 company表的数据

salesman的数据如表3.2所示：

SNO SID SNAME STEL SADDRESS CNO S0001440902198811111111邓春国06682888888广东省茂名市茂南区C0001 S0002440902198811111111王军010********福建省漳州市C0002 S0003440902198822222222丁磊0739*******湖南省邵阳市C0003 S0004440902198811111118暮云06682888886广东省茂名市茂南区C0001

表3.2 salesman表的数据

airplane的数据如表3.3所示：

ANO ANAME CNO

A0001 波音737 C0001

A0002 波音777 C0001

A0003 波音737 C0002

A0004 麦道82 C0003

表3.3 airplane表的数据

flight的数据如表3.4所示：

FNO DEPARTURE ARRIVAL TO_CHAR(TIME,'HH-MI-SS') FLYTIME ANO F0001广州北京07-50-000 3:30:0.0A0001 F0002北京广州12-30-000 3:30:0.0A0001 F0003广州长沙08-00-000 1:5:0.0A0002 F0004长沙广州10-20-000 1:5:0.0A0002 F0005北京长沙09-10-000 2:50:0.0A0003 F0006长沙北京12-50-000 2:50:0.0A0003 F0007长沙广州07-35-000 1:10:0.0A0004 F0008广州长沙10-05-000 1:10:0.0A0004

表3.4 flight表的数据

cabin的数据如表3.5所示：

FNO CBLEVEL SEATS PRICE

F0001150900

F0001280700

F0002150900

F0002280700

F0003130500

F0003250400

F0003370300

F0004130500

F0004250400

F0004370300

F0005150800

F0005270600

F0006150800

F0006270600

F00071120400

F00081120400

表3.5 cabin表的数据

3.3 视图设计

应用程序需要查询航班信息，因此需要创建一个航班信息的视图，根据参数航班号或者出发地以及目的地查询航班信息，显示航班号、公司名、飞机名称、出发时间、到达时间、出发地和目的地。由于oracle的视图不支持参数，但可以利用临时表作为参数进行传递，因此需要创建一个临时表，创建临时表的SQL语句如下：CREATE GLOBAL 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,time,time+flytime,departure,arrival

FROM flight,company,airplane,input_to_flight

WHERE flight.ano=airplane.ano

AND https://www.wendangku.net/doc/7752026.html,o=https://www.wendangku.net/doc/7752026.html,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,departure,arrival

FROM flight,company,airplane,input_to_flight

WHERE flight.ano=airplane.ano

AND https://www.wendangku.net/doc/7752026.html,o=https://www.wendangku.net/doc/7752026.html,o

AND departure=input_to_flight.T_departure

AND arrival=input_to_flight.T_arrival;

应用程序还可以根据航班号和航班日期查询余票信息，因此需要创建一个余票信息的视图，显示某一航班某一日期不同机舱等级的剩余座位数、定价和折扣等信息。因此创建一个参数化的视图，其中计算余票使用函数count_ticket，创建视图的SQL 语句如下：

CREATE 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_to_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所示：

图3.4 remain_seats_view视图查询的数据

在乘客确定好要预订的机票的时候，系统需要打印机票，机票上需要显示机票编号、航班号、公司名、飞机名称、出发地、目的地、机票日期、出发时间、到达时间、机舱等级、座位号、定价、折扣、售价以及乘客姓名、乘客身份证号和业务员姓名，因此可以创建一个视图，创建视图的SQL语句如下：

CREATE OR REPLACE VIEW "SYSTEM"."TICKET_INFO_VIEW"

("TNO","FNO","CNAME","ANAME","DEPARTURE","ARRIVAL","FLYDATE","TIME","ARRIVAL_TI ME","CBLEVEL","SEAT","PRICE","DISCOUNT","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, cabin WHERE ticket.fno =flight.fno

AND flight.ano=airplane.ano

AND https://www.wendangku.net/doc/7752026.html,o=https://www.wendangku.net/doc/7752026.html,o

AND ticketsale.tno=ticket.tno

AND ticketsale.pid=passenger.pid

AND ticketsale.sno=salesman.sno

AND ticket.fno=cabin.fno

AND ticket.cblevel=cabin.cblevel;

应用程序需要查询售票记录，因此可以创建一个视图来查询机票编号、乘客姓名、乘客身份证号、业务员编号、业务员姓名和购票日期，创建视图的SQL语句如下：CREATE OR REPLACE 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的查询结果如图所示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

WHERE salesman.sno =ticketsale.sno

AND https://www.wendangku.net/doc/7752026.html,o =https://www.wendangku.net/doc/7752026.html,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）存储过程设计

管理员需要对机票信息的录入，由于机票信息的数据量庞大，手工输入是不现实的事情，因此需要采用存储过程来进行对数据的录入。为了使机票编号有序且不重复，

因此需要创建一个表，表存放当前机票编号，创建表和存储过程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 count(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,p_flydate,1,v_j,p_discount);

v_tno:=v_tno+1;

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 FNO CBLEVEL TO_CHAR(FLYDATE,'YYYY-MM-DD') STATUS SEAT DISCOUNT

1 F0003 1 2011-6-10 1 1 0.7

2 F000

3 1 2011-6-10 1 2 0.7

3 F0003 1 2011-6-10 1 3 0.7

4 F0003 1 2011-6-10 1 4 0.7

5 F0003 1 2011-6-10 1 5 0.7

6 F0003 1 2011-6-10 1 6 0.7

7 F0003 1 2011-6-10 1 7 0.7

8 F0003 1 2011-6-10 1 8 0.7

表3.6 ticket表的部分数据

乘客在买票时，需要对售票（ticketsale）表进行插入数据，因此可以创建一个存

储过程对售票这一过程进行操作，并且打印机票的信息，存储过程把需要打印的票的信息存放在临时表上，创建临时表和存储过程的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),

"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_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 ticket_info_view WHERE tno=v_tno;

INSERT INTO print_ticket VALUES

(v_row.tno,v_row.fno,v_https://www.wendangku.net/doc/7752026.html,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;

假设乘客邓春国要购买一张航班号为F0003、日期为6月1号的机票，售票员工号为S0001，则系统先让乘客输入身份证号，查询该乘客是否存在，存在的话显示乘客信息并提示是否需要更新，否则添加一个乘客信息，假设该乘客的记录不存在，则先进行插入操作，然后执行CREATE_RECORD 过程并且查询临时表PRINT_TICKET的数据用于打印机票，SQL语句如下：

INSERT INTO passenger VALUES('440902************','邓春国','151********','广东省茂名市茂南区');

CALL create_record

('F0003',to_date('2011-6-1','yyyy-mm-dd'),1,'440902************','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 number;

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_cblevel 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 PACKAGE BODY "SYSTEM"."SALES_SYSTEM"

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 ) AS v_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 ticket_info_view WHERE tno=v_tno;

INSERT INTO print_ticket VALUES

(v_row.tno,v_row.fno,v_https://www.wendangku.net/doc/7752026.html,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;

FUNCTION COUNT_TICKET

(

p_fno flight.fno%TYPE,p_flydate date,p_cblevel cabin.cblevel%TYPE )

RETURN number

AS v_count number;

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;

PROCEDURE 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 count(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,p_flydate,1,v_j,p_discount);

v_tno:=v_tno+1;

END LOOP;

UPDATE t_number SET tno=v_tno;

END;

3.5 触发器设计

在输入机票信息时，需要保证航班日期是在当前日期的下一天，因此可以用触发器来实现，触发器创建的SQL语句如下：

CREATE OR REPLACE TRIGGER "SYSTEM"."CHECK_FLYDATE" BEFORE

INSERT OR UPDATE OF "FLYDATE" ON "SYSTEM"."TICKET" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW

BEGIN

IF :new.flydate<=sysdate THEN raise_application_error(-20000,'out of range');

END IF;

END CHECK_FLYDATE;

假如想要插入5月1号的机票，则系统会提示错误，如图3.8所示：

图3.8 触发器返回结果图

3.6 角色、用户、权限设计

整个机票预定系统需要两个应用程序，一个是售票管理员使用的，用来录入和更新信息，另一个是售票员使用的前台应用程序，因此需要两个角色，分别为管理员和售票员。角色sales_manager和saler的信息如图3.9所示：

图3.9 角色SALES_MANAGER和SALES信息

接着创建两个用户user_manager和user_sales，并分别授予sales_manager和saler 角色。创建用户的SQL如下：

CREATE USER "USER_MANAGER" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "SYSTEM" ACCOUNT UNLOCK

GRANT "CONNECT" TO "USER_MANAGER"

GRANT "SALES_MANAGER" TO "USER_MANAGER"

CREATE USER "USER_SALES" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "SYSTEM" ACCOUNT UNLOCK

GRANT "CONNECT" TO "USER_SALES"

GRANT "SALER" TO "USER_SALES"

对于每一个员工，可以创建一个用户表，包括员工编号、密码和员工类型，对于业务员来说，员工号为业务员表的业务员编号。当员工输入编号和密码正确时，应用程序将会根据员工类型连接到user_manager或user_sales用户,否则应用程序禁止员工访问数据库。

3.7 备份方案设计

系统运行中难免会发生故障，因此需要定期做备份，管理员可以在系统不繁忙的时候进行备份，因此定制一个调度备份作业进行备份。备份类型为完全备份，调度类型为重复，频率类型为每周星期一，开始时间为3：00，备份的概要如图3.10所示：

图3.10 数据库备份概要