Oracle机票预定信息系统数据库设计

1、数据库原理及应用课程设计设计题目 机票预订信息系统 系 部 信息技术工程学院 班 级 网络 1302 学 号 姓 名 2016 年 1 月目录一、需求分析3Ø背景分析3Ø需求分析31)系统功能：32)系统信息：33)数据流图：34)数据字典：4二、结构设计5ØE-R图5飞机E-R图，如图4-1所示6航班E-R图，如图4-2所示。6机票E-R图，如图4-3所示。6旅客E-R图，如图4-4所示。6账单E-R图，如图4-5所示。7航班坐位E-R图，如图4-6所示。7取票通知E-R图，如图4-7所示。7总E-R图，如图4-8所示。8Ø关系模式9基本表9虚表（视图

2、）9三、数据库实现9Ø创建表空间与赋权限9Ø飞机信息表建立9Ø航班信息表建立10Ø机票信息表建立10Ø旅客信息表建立10Ø账单信息表建立10Ø航班坐位视图10Ø取票通知视图11Ø外键语句11四、数据库实施11Ø添加飞机11Ø添加航班11Ø添加用户12Ø预定机票(存储过程)12Ø打印取票通知(存储函数)13Ø生成账单(触发器和存储过程)13五、总结14一、 需求分析Ø 背景分析以信息技术为主要标志的科技进步日新月异，高科技成果向现实生产力

3、的转化越来越快。 纵观全球经济发展， 信息技术和信息产业已经成为经济增长的主要推动力之一， 正在改变着传统的生产和经营方式以至生活方式， 发达国家经过产业结构的升级和经济结构 的转型已进入信息经济阶段。 机票预订系统也开始影响着人们日常生活和出行，并且变得越来越重要。而原有的系统随着航空公司载客量的迅猛增长和人们对便捷性要求的提高，已经变得无法满足需求。原有的系统不仅效率比较低下，而且在安全性、准确性等方面有很多不足。为了实现航空公司以及旅游行业的现代化管理，进一步提高工作效率，方便旅客，需要开发一个机票预订系统。Ø 需求分析1) 系统功能：l 航班信息的输入，修改查询。l 航班座位

4、信息的输入，修改查询。l 根据旅客输入的基本信息，为旅客安排航班，打印取票通知和帐单。l 旅客在飞机起飞前一天凭取票通知交款取票。l 旅客能够退订机票。l 能够查询每个航班的预定情况、计算航班的满座率。2) 系统信息：l 航班信息: 航班编号、飞机编号、起飞地、目的地、起飞时间、飞行时间等。l 飞机信息：飞机编号、飞机型号、座位数。l 机票信息：航班编号、旅客编号、座位号、票价等。l 旅客信息：姓名、性别、联系电话、身份证号等。3) 数据流图：工作人员编辑航班飞机表航班表机票表用户表账单表旅客机票操作用户信息编辑账单操作4) 数据字典：根据总体结构图设计机票预定系统基本表结构，其相应标的定义如

5、下：表4-1 飞机信息<plain>字段名称类型长度是否非空主关键字备注plain_idVARCHAR210Y飞机编号modelVARCHAR210N飞机型号seatint2N总座位表4-2 航班信息< flight >字段名称类型长度是否非空主关键字备注flight_idVARCHAR210Y航班编号plain_idVARCHAR210N飞机编号startVARCHAR210N起飞地endVARCHAR210N目的地departure_timedatetimeN起飞时间durationint4N飞行时间表4-3 机票信息<tickets>字段名称类型长度是

6、否非空主关键字备注tickets_idVARCHAR210Y机票编号flight_idVARCHAR210Y航班编号traveler_idVARCHAR210N旅客编号seating_listint4N座位号cabin_ratingVARCHAR210N机舱等级updata_timedatetimeN更新时间statusint10N状态表4-4 旅客信息<traveler>字段名称类型长度是否非空主关键字备注traveler_idVARCHAR210Y旅客编号nameVARCHAR210N姓名sexint1N性别id_valueVARCHAR220N身份证号phoneVARCHAR

7、211N联系方式表4-5 账单信息<bill>字段名称类型长度是否非空主关键字备注bill_idVARCHAR210Y账单编号traveler_idVARCHAR210Y旅客编号tickets_idVARCHAR210Y机票编号paymentfloat(2)NN 价格updata_timedatetimeN更新时间statusint4N付款状态二、 结构设计Ø E-R图根据需求分析抽象出信息结构，可得该系统的E-R图。 飞机E-R图，如图4-1所示。飞机飞机编号座位数飞机型号 图4-1 飞机E-R图 航班E-R图，如图4-2所示。航班出发地航班编号起飞时间飞行时

8、间目的地飞机编号图4-2 航班E-R图机票E-R图，如图4-3所示。机票机舱等级机票编号状态座位号航班编号旅客编号号更新时间图4-3 机票E-R图旅客E-R图，如图4-4所示。旅客姓名性别身份证号号码码旅客编号联系电话码码图4-4 旅客E-R图账单E-R图，如图4-5所示。账单机舱等级机票编号状态座位号航班编号旅客编号号更新时间账单编号图4-5 账单E-R图航班坐位E-R图，如图4-6所示。航班坐位状态座位号航班编号 图4-6 航班坐位E-R图取票通知E-R图，如图4-7所示。取票通知状态座位号航班编号旅客编号   图4-7 取票通知E-R图总E-R图，如图4-8所示。飞机

9、飞机编号 座位数飞机型号 航班出发地航班编号飞行时间目的地飞机编号起飞时间 有11机票机舱等级机票编号状态座位号  航班编号旅客编号号更新时间包含1m购买1旅客姓名 性别 身份证号号码码旅客编号 联系电话码码1账单机票编号状态付款旅客编号号更新时间属于11账单编号图4-8 总E-R图Ø 关系模式基本表飞机信息表（飞机编号、飞机型号、飞机座位）航班信息表（航班编号、飞机编号、 起飞地、目的地、起飞时间、飞行时间）机票信息表（机票编号、航班编号、旅客编号、机舱等级、座位号、更新时间、状态）旅客信息表（旅客编号

10、、姓名、性别、联系电话、身份证号）账单表 (账单编号、旅客编号、机票编号、价格、更新时间、状态)虚表（视图）航班坐位情况表 (机票信息表.航班编号、机票信息表.座位号、机票信息表.状态)取票通知表 (机票信息表.航班编号、机票信息表. 旅客编号、 机票信息表.座位号、机票信息表.状态)三、 数据库实现Ø 创建表空间与赋权限create temporary tablespace HKSP_TEMPtempfile'C:oraclexeapporacleoradataXEHKSP_TEMP.dbf'size 50Mautoextend onnext 50M maxsize

11、 1048Mextent management local;create tablespace HKSP_DATAatafile 'C:oraclexeapporacleoradataXEHKSP_DATA.dbf'size 50Mautoextend onnext 50M maxsize 1048Mextent management local;create user HKSP identified by 123456default tablespace HKSP_DATAtemporary tablespace HKSP_TEMP;grant connect,resourc

12、e,dba to HKSP;Ø 飞机信息表建立CREATE TABLE "HKSP"."plain" ("plain_id" VARCHAR2(10 BYTE) NOT NULL primary key ,"model" VARCHAR2(10 BYTE) NOT NULL ,"seat" NUMBER NOT NULL )Ø 航班信息表建立CREATE TABLE "HKSP"."flight" ("flight_id&qu

13、ot; VARCHAR2(10 BYTE) NOT NULL primary key,"plain_id" VARCHAR2(10 BYTE) NOT NULL ,"start" VARCHAR2(20 BYTE) NOT NULL ,"end" VARCHAR2(20 BYTE) NOT NULL ,"departure_time" DATE NOT NULL ,"duration" NUMBER NOT NULL )Ø 机票信息表建立CREATE TABLE "HKSP&

14、quot;."tickets" ("tickets_id" VARCHAR2(10 BYTE) NOT NULL primary key,"flight_id" VARCHAR2(10 BYTE) NOT NULL ,"traveler_id" VARCHAR2(10 BYTE) NOT NULL ,"seating_list" NUMBER NOT NULL ,"cabin_rating" VARCHAR2(10 BYTE) NOT NULL ,"updata_t

15、ime" DATE NOT NULL ,"status" NUMBER NOT NULL )Ø 旅客信息表建立CREATE TABLE "HKSP"."traveler" ("traveler_id" VARCHAR2(10 BYTE) NOT NULL primary key ,"name" VARCHAR2(10 BYTE) NOT NULL ,"sex" NUMBER NOT NULL ,"id_value" VARCHAR2(2

16、0 BYTE) NOT NULL ,"phone" VARCHAR2(11 BYTE) NOT NULL ) Ø 账单信息表建立CREATE TABLE "HKSP"."bill" ("bill_id" VARCHAR2(10 BYTE) NOT NULL primary key,"traveler_id" VARCHAR2(10 BYTE) NOT NULL ,"tickets_id" VARCHAR2(10 BYTE) NOT NULL ,"payme

17、nt" FLOAT(10) NOT NULL ,"updata_time" DATE NOT NULL ,"status" NUMBER NOT NULL )Ø 航班坐位视图CREATE OR REPLACE FORCE VIEW "HKSP"."seating_view" AS SELECTHKSP."tickets"."flight_id",HKSP."tickets"."seating_list",HKSP.&

18、quot;tickets"."status"FROMHKSP."tickets"Ø 取票通知视图CREATE OR REPLACE FORCE VIEW "HKSP"."notice_view" AS SELECTHKSP."tickets"."flight_id",HKSP."tickets"."traveler_id",HKSP."tickets"."status"FROM

19、HKSP."tickets"WHEREHKSP."tickets"."status" = 2;Ø 外键语句ALTER TABLE "HKSP"."bill" ADD FOREIGN KEY ("traveler_id") REFERENCES "HKSP"."traveler" ("traveler_id") ON DELETE CASCADE;ALTER TABLE "HKSP".&q

20、uot;bill" ADD FOREIGN KEY ("tickets_id") REFERENCES "HKSP"."tickets" ("tickets_id") ON DELETE CASCADE;ALTER TABLE "HKSP"."flight" ADD FOREIGN KEY ("plain_id") REFERENCES "HKSP"."plain" ("plain_id"

21、;) ON DELETE CASCADE;ALTER TABLE "HKSP"."tickets" ADD FOREIGN KEY ("traveler_id") REFERENCES "HKSP"."traveler" ("traveler_id") ON DELETE CASCADE;四、 数据库实施Ø 添加飞机INSERT INTO "HKSP"."plain" VALUES ('F-T1235', &#

22、39;T12306', '100');INSERT INTO "HKSP"."plain" VALUES ('F-T1236', 'T12306', '100');INSERT INTO "HKSP"."plain" VALUES ('F-T1237', 'T12306', '100');INSERT INTO "HKSP"."plain" VALUES (

23、'F-T1234', 'T12306', '100');Ø 添加航班INSERT INTO "HKSP"."flight" VALUES ('MH370', 'F-T1235', '北京', '马来西亚', TO_DATE('2016-01-18 10:12:09', 'YYYY-MM-DD HH24:MI:SS'), '5');INSERT INTO "HKSP".

24、"flight" VALUES ('MH371', 'F-T1236', '北京', '西安', TO_DATE('2016-01-19 09:20:13', 'YYYY-MM-DD HH24:MI:SS'), '4');INSERT INTO "HKSP"."flight" VALUES ('MH372', 'F-T1237', '北京, '上海', TO_DATE(

25、'2016-01-20 20:14:17', 'YYYY-MM-DD HH24:MI:SS'), '11');INSERT INTO "HKSP"."flight" VALUES ('MH373', 'F-T1234', '北京', '杭州', TO_DATE('2016-01-19 05:29:21', 'YYYY-MM-DD HH24:MI:SS'), '3');Ø 添加用户INSE

26、RT INTO "HKSP"."traveler" VALUES ('LK00001', '小明', '1', '#39;, '123456781');INSERT INTO "HKSP"."traveler" VALUES ('LK00002', '小红', '2', '#39;, '123456782&

27、#39;);INSERT INTO "HKSP"."traveler" VALUES ('LK00003', '小芳', '2', '#39;, '123456783');INSERT INTO "HKSP"."traveler" VALUES ('LK00004', '小小', '1', '#39;, '

28、123456784');INSERT INTO "HKSP"."traveler" VALUES ('LK00005', '小亮', '1', '#39;, '123456785');INSERT INTO "HKSP"."traveler" VALUES ('LK00006', '小心', '2', '#

29、39;, '123456786');Ø 预定机票(存储过程)CREATE OR REPLACE PROCEDURE book_tickets (flight_id IN VARCHAR2, traveler_id IN VARCHAR2, seating IN NUMBER)ASticketsid "tickets"."tickets_id" %TYPE; test_ticketsid NUMBER;test_cabin "tickets"."cabin_rating" %TYPE;tem

30、p varchar(32);test_value NUMBER:=0;BEGIN-生成机票IDtemp:=sys_guid(); ticketsid:= substr(temp,1,6)|substr(temp,28,4);-测试座位是否被预定 SELECT count(*) INTO test_ticketsidFROM "tickets"WHERE "seating_list" = seating;-查看结果IF test_ticketsid = 0 THEN-座位未被预定-判断仓位等级test_value := seating/10;CASE WH

31、EN test_value < 1 THENtest_cabin:='商务舱'WHEN test_value < 2 THENtest_cabin:='二等舱'ELSEBEGINtest_cabin:='经济舱'END; END CASE; -预定机票INSERT INTO "HKSP"."tickets" VALUES (ticketsid, flight_id, traveler_id, seating, test_cabin, SYSDATE,1); DBMS_OUTPUT.PUT_LIN

32、E('预定成功');ELSE-座位已被预定DBMS_OUTPUT.PUT_LINE('座位已被预定');END IF;END;Ø 打印取票通知(存储函数)CREATE OR REPLACE FUNCTION get_voucher (flight_id IN VARCHAR2, traveler_id IN VARCHAR2)RETURN VARCHAR2AStest_tickets "tickets"."tickets_id" %TYPE;BEGIN -获得机票IDSELECT "tickets&qu

33、ot;."tickets_id" INTO test_ticketsFROM "tickets"WHERE "flight_id" = flight_id AND "traveler_id" = traveler_id; -更新状态UPDATE "tickets" SET "status"=2WHERE "tickets"."tickets_id" = test_tickets; -返回机票IDRETURN test_tickets;EXCEPTIONWHEN OTHERS THENRETURN '你没