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

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

《数据库课程设计机票预订系统.docx》由会员分享，可在线阅读，更多相关《数据库课程设计机票预订系统.docx（29页珍藏版）》请在冰豆网上搜索。

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

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）WINDOWS2000/2003/XP系统

（2）Oracle数据库管理系统

2、4开发语言

PL/SQL语言

3分析与设计

3、1系统E-R模型

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

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

根据以上分析可以画出系统E-R图,系统E-R图如图3、1所示:

图3、1系统E-R图

3、2表空间及表的设计

（1）表空间的设计。

经过分析可知,乘客表、机票信息表与机票销售表数据量比较大,可单独分配表空间,其她的表数据量较少,可一起使用一个表空间。

创建表分配表空间与添加数据文件,SQL语句如下:

CREATESMALL"PASSENGER"

DATAFILE'F:

\APP\ORACLE\ORADATA\ORCL\TICKETSALE\passenger、dbf'

SIZE100MAUTOEXTENDONNEXT5MMAXSIZEUNLIMITED

LOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;

CREATESMALL"TICKET"

DATAFILE'F:

\APP\ORACLE\ORADATA\ORCL\TICKETSALE\ticket、dbf'

SIZE100MAUTOEXTENDONNEXT5MMAXSIZEUNLIMITED

LOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;

CREATESMALL"TICKETSALE"

DATAFILE'F:

\APP\ORACLE\ORADATA\ORCL\TICKETSALE\ticketsale、dbf'

SIZE100MAUTOEXTENDONNEXT5MMAXSIZEUNLIMITED

LOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;

CREATESMALL"OTHERS"

DATAFILE'F:

\APP\ORACLE\ORADATA\ORCL\TICKETSALE\others、dbf'

SIZE100MAUTOEXTENDONNEXT5MMAXSIZEUNLIMITED

LOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO;

表空间结构如图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语句如下:

CREATETABLE"SYSTEM"、"COMPANY"（

"CNO"VARCHAR2（10）NOTNULL,

"CNAME"VARCHAR2（20）NOTNULL,

"CTEL"VARCHAR2（20）,

"CADDRESS"VARCHAR2（50）,

PRIMARYKEY（"CNO"）VALIDATE

）TABLESPACE"OTHERS";

CREATETABLE"SYSTEM"、"PASSENGER"（

"PID"VARCHAR2（20）NOTNULL,

"PNAME"VARCHAR2（20）NOTNULL,

"PTEL"VARCHAR2（20）,

"PADDRESS"VARCHAR2（50）,

PRIMARYKEY（"PID"）VALIDATE

）TABLESPACE"PASSENGER";

CREATETABLE"SYSTEM"、"SALESMAN"（

"SNO"VARCHAR2（10）NOTNULL,

"SID"VARCHAR2（20）NOTNULL,

"SNAME"VARCHAR2（20）NOTNULL,

"STEL"VARCHAR2（20）,

"SADDRESS"VARCHAR2（50）,

"CNO"VARCHAR2（10）NOTNULL,

PRIMARYKEY（"SNO"）VALIDATE,

FOREIGNKEY（"CNO"）REFERENCES"SYSTEM"、"COMPANY"（"CNO"）VALIDATE

）TABLESPACE"OTHERS";

CREATETABLE"SYSTEM"、"AIRPLANE"（

"ANO"VARCHAR2（10）NOTNULL,

"ANAME"VARCHAR2（20）NOTNULL,

"CNO"VARCHAR2（10）NOTNULL,

PRIMARYKEY（"ANO"）VALIDATE,

FOREIGNKEY（"CNO"）REFERENCES"SYSTEM"、"COMPANY"（"CNO"）VALIDATE

）TABLESPACE"OTHERS";

CREATETABLE"SYSTEM"、"FLIGHT"（

"FNO"VARCHAR2（10）NOTNULL,

"DEPARTURE"VARCHAR2（20）NOTNULL,

"ARRIVAL"VARCHAR2（20）NOTNULL,

"TIME"DATENOTNULL,

"FLYTIME"INTERVALDAYTOSECONDNOTNULL,

"ANO"VARCHAR2（10）NOTNULL,

PRIMARYKEY（"FNO"）VALIDATE,

FOREIGNKEY（"ANO"）REFERENCES"SYSTEM"、"AIRPLANE"（"ANO"）VALIDATE

）TABLESPACE"OTHERS";

CREATETABLE"SYSTEM"、"CABIN"（

"FNO"VARCHAR2（10）NOTNULL,

"CBLEVEL"NUMBER

（1）NOTNULL,

"SEATS"NUMBER（3）NOTNULL,

"PRICE"NUMBER（5）NOTNULL,

PRIMARYKEY（"FNO","CBLEVEL"）VALIDATE,

FOREIGNKEY（"FNO"）REFERENCES"SYSTEM"、"FLIGHT"（"FNO"）VALIDATE

）TABLESPACE"OTHERS";

CREATETABLE"SYSTEM"、"TICKET"（

"TNO"NUMBER（10）NOTNULL,

"FNO"VARCHAR2（10）NOTNULL,

"CBLEVEL"NUMBER

（1）NOTNULL,

"FLYDATE"DATENOTNULL,

"STATUS"NUMBER

（1）DEFAULT1NOTNULL,

"SEAT"NUMBER（3）NOTNULL,

"DISCOUNT"NUMBER（3,2）NOTNULL,

PRIMARYKEY（"TNO"）VALIDATE,

FOREIGNKEY（"FNO","CBLEVEL"）REFERENCES"SYSTEM"、"CABIN"（"FNO","CBLEVEL"）VALIDATE

）TABLESPACE"TICKET";

CREATETABLE"SYSTEM"、"TICKETSALE"（

"TNO"NUMBER（10）NOTNULL,

"PID"VARCHAR2（20）NOTNULL,

"SNO"VARCHAR2（10）NOTNULL,

"SALEDATE"DATENOTNULL,

PRIMARYKEY（"TNO","PID","SNO"）VALIDATE,

FOREIGNKEY（"TNO"）REFERENCES"SYSTEM"、"TICKET"（"TNO"）VALIDATE,

FOREIGNKEY（"PID"）REFERENCES"SYSTEM"、"PASSENGER"（"PID"）VALIDATE,

FOREIGNKEY（"SNO"）REFERENCES"SYSTEM"、"SALESMAN"（"SNO"）VALIDATE

）TABLESPACE"TICKETSALE";

（4）为表添加数据

由于表company、salesman、airplane、flight与cabin就是公司管理员通过应用程序的管理端预先录入的,因此应用程序需要为这些表添加一定数据,应用程序将会执行INSERT语句对表进行插入数据。

company的数据如表3、1所示:

CNO

CNAME

CTEL

CADDRESS

C0001

朝云航空

广东省广州市

C0002

北京航空

北京市

C0003

长沙航空

湖南省长沙市

表3、1company表的数据

salesman的数据如表3、2所示:

SNO

SID

SNAME

STEL

SADDRESS

CNO

S0001

邓春国

广东省茂名市茂南区

C0001

S0002

王军

福建省漳州市

C0002

S0003

丁磊

湖南省邵阳市

C0003

S0004

暮云

广东省茂名市茂南区

C0001

表3、2salesman表的数据

airplane的数据如表3、3所示:

ANO

ANAME

CNO

A0001

波音737

C0001

A0002

波音777

C0001

A0003

波音737

C0002

A0004

麦道82

C0003

表3、3airplane表的数据

flight的数据如表3、4所示:

FNO

DEPARTURE

ARRIVAL

TO_CHAR（TIME,'HH-MI-SS'）

FLYTIME

ANO

F0001

广州

北京

07-50-00

03:

30:

0、0

A0001

F0002

北京

广州

12-30-00

03:

30:

0、0

A0001

F0003

广州

长沙

08-00-00

01:

5:

0、0

A0002

F0004

长沙

广州

10-20-00

01:

5:

0、0

A0002

F0005

北京

长沙

09-10-00

02:

50:

0、0

A0003

F0006

长沙

北京

12-50-00

02:

50:

0、0

A0003

F0007

长沙

广州

07-35-00

01:

10:

0、0

A0004

F0008

广州

长沙

10-05-00

01:

10:

0、0

A0004

表3、4flight表的数据

cabin的数据如表3、5所示:

FNO

CBLEVEL

SEATS

PRICE

F0001

1

50

900

F0001

2

80

700

F0002

1

50

900

F0002

2

80

700

F0003

1

30

500

F0003

2

50

400

F0003

3

70

300

F0004

1

30

500

F0004

2

50

400

F0004

3

70

300

F0005

1

50

800

F0005

2

70

600

F0006

1

50

800

F0006

2

70

600

F0007

1

120

400

F0008

1

120

400

表3、5cabin表的数据

3、3视图设计

应用程序需要查询航班信息,因此需要创建一个航班信息的视图,根据参数航班号或者出发地以及目的地查询航班信息,显示航班号、公司名、飞机名称、出发时间、到达时间、出发地与目的地。

由于oracle的视图不支持参数,但可以利用临时表作为参数进行传递,因此需要创建一个临时表,创建临时表的SQL语句如下:

CREATEGLOBALTEMPORARYTABLE"SYSTEM"、"INPUT_TO_FLIGHT"（

"T_FNO"VARCHAR2（10）,

"T_DEPARTURE"VARCHAR2（20）,

"T_ARRIVAL"VARCHAR2（20）,

"T_FLYDATE"DATE

）ONCOMMITPRESERVEROWS;

创建参数化视图的SQL语句如下:

CREATEORREPLACEVIEW"SYSTEM"、"FLIGHT_VIEW_BYFNO"

（"FNO","CNAME","ANAME","TIME","ARRIVAL_TIME","DEPARTURE","ARRIVAL"）

ASSELECTfno,cname,aname,time,time+flytime,departure,arrival

FROMflight,company,airplane,input_to_flight

WHEREflight、ano=airplane、ano

ANDairplane、cno=company、cno

ANDfno=input_to_flight、T_fno;

CREATEORREPLACEVIEW"SYSTEM"、"FLIGHT_VIEW_BYSITE"

（"FNO","CNAME","ANAME","TIME","ARRIVAL_TIME","DEPARTURE","ARRIVAL"）

ASSELECTfno,cname,aname,time,time+flytime,departure,arrival

FROMflight,company,airplane,input_to_flight

WHEREflight、ano=airplane、ano

ANDairplane、cno=company、cno

ANDdeparture=input_to_flight、T_departure

ANDarrival=input_to_flight、T_arrival;

应用程序还可以根据航班号与航班日期查询余票信息,因此需要创建一个余票信息的视图,显示某一航班某一日期不同机舱等级的剩余座位数、定价与折扣等信息。

因此创建一个参数化的视图,其中计算余票使用函数count_ticket,创建视图的SQL语句如下:

CREATEORREPLACEVIEW"SYSTEM"、"REMAIN_SEATS_VIEW"

（"FNO","FLYDATE","CBLEVEL","COUNT"）

ASSELECTDISTINCTfno,flydate,cblevel,count_ticket（fno,flydate,cblevel）

FROMticket,input_to_flight

WHEREfno=input_to_flight、t_fno

ANDflydate=input_to_flight、T_FLYDATE;

假设应用程序要查询“茂名——长沙”的航班信息时,应用程序先执行以下SQL语句:

INSERTINTOinput_to_flightVALUES（'','茂名','长沙',''）;

SELECT*FROMflight_view_bysite;

此时系统会返回查询的结果如图3、3所示:

图3、3flight_view_bysite视图查询的数据

这时候假设再想查询航班F0003、日期为2011年6月1日的余票信息,应用程序会执行以下SQL语句:

INSERTINTOinput_to_flight

VALUES（'F0003','','',to_date（'2011-6-1','yyyy-mm-dd'））;

SELECT*FROMremain_seats_viewORERBYcblevel;

此时系统会返回查询的结果如图3、4所示:

图3、4remain_seats_view视图查询的数据

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

CREATEORREPLACEVIEW"SYSTEM"、"TICKET_INFO_VIEW"

（"TNO","FNO","CNAME","ANAME","DEPARTURE","ARRIVAL","FLYDATE","TIME","ARRIVAL_TIME","CBLEVEL","SEAT","PRICE","DISCOUNT","FINAL_PRICE","PNAME","PID","SNAME"）

ASSELECTticket、tno,ticket、fno,cname,aname,departure,arrival,flydate,time,time+flytime,ticket、cblevel,seat,price,discount,price*discount,pname,passenger、pID,sname

FROMticket,flight,airplane,company,passenger,salesman,ticketsale,cabin

WHEREticket、fno=flight、fno

ANDflight、ano=airplane、ano

ANDairplane、cno=company、cno

ANDticketsale、tno=ticket、tno

ANDticketsale、pid=passenger、pid

ANDticketsale、sno=salesman、sno

ANDticket、fno=cabin、fno

ANDticket、cblevel=cabin、cblevel;

应用程序需要查询售票记录,因此可以创建一个视图来查询机票编号、乘客姓名、乘客身份证号、业务员编号、业务员姓名与购票日期,创建视图的SQL语句如下:

CREATEORREPLACEVIEW"SYSTEM"、"SALERECORD_VIEW"

（"TNO","PNAME","PID","SNO","SNAME","SALEDATE"）

ASSELECTticketsale、tno,pname,ticketsale、pID,ticketsale、sno,sname,saledate

FROMticket,passenger,salesman,ticketsale

WHEREticket、tno=ticketsale、tno

ANDticketsale、pid=passenger、pid

ANDticketsale、sno=salesman、sno;

视图salerecord_view的查询结果如图所示3、5:

图3、5salerecord_view视图查询的数据

应用程序需要统计业务员的业绩,需要显示业务员编号、业务员