1、Oracle机票预定信息系统数据库设计说明数据库原理及应用课程设计设计题目 机票预订信息系统系 部信息技术工程学院班级 网络1302学 号 姓 名 2016年 1月一、需求分析背景分析以信息技术为主要标志的科技进步日新月异，高科技成果向现实生产力的转化越来越快。 纵观全球经济发展， 信息技术和信息产业已经成为经济增长的主要推动力之一， 正在改变着传统的生产和经营方式以至生活方式， 发达国家经过产业结构的升级和经济结构 的转型已进入信息经济阶段。 机票预订系统也开始影响着人们日常生活和出行，并且变得越来越重要。而原有的系统随着航空公司载客量的迅猛增长和人们对便捷性要求的提高，已经变得无法满足需求
2、。原有的系统不仅效率比较低下，而且在安全性、准确性等方面有很多不足。为了实现航空公司以及旅游行业的现代化管理，进一步提高工作效率，方便旅客，需要开发一个机票预订系统。需求分析1)系统功能：航班信息的输入，修改查询。航班座位信息的输入，修改查询。根据旅客输入的基本信息，为旅客安排航班，打印取票通知和帐单。旅客在飞机起飞前一天凭取票通知交款取票。旅客能够退订机票。能够查询每个航班的预定情况、计算航班的满座率。2)系统信息：航班信息: 航班编号、飞机编号、起飞地、目的地、起飞时间、飞行时间等。飞机信息：飞机编号、飞机型号、座位数。机票信息：航班编号、旅客编号、座位号、票价等。旅客信息：、性别、联系、
3、号等。3)数据流图：4)数据字典：根据总体结构图设计机票预定系统基本表结构，其相应标的定义如下：表4-1 飞机信息字段名称类型长度是否非空主关键字备注plain_idVARCHAR210Y飞机编号modelVARCHAR210N飞机型号seatint2N总座位表4-2 航班信息字段名称类型长度是否非空主关键字备注flight_idVARCHAR210Y航班编号plain_idVARCHAR210N飞机编号startVARCHAR210N起飞地endVARCHAR210N目的地departure_timedatetimeN起飞时间durationint4N飞行时间表4-3 机票信息字段名称类型长
4、度是否非空主关键字备注tickets_idVARCHAR210Y机票编号flight_idVARCHAR210Y航班编号traveler_idVARCHAR210N旅客编号seating_listint4N座位号cabin_ratingVARCHAR210N机舱等级updata_timedatetimeN更新时间statusint10N状态表4-4 旅客信息字段名称类型长度是否非空主关键字备注traveler_idVARCHAR210Y旅客编号nameVARCHAR210Nsexint1N性别id_valueVARCHAR220N号phoneVARCHAR211N联系方式表4-5 账单信息字段
5、名称类型长度是否非空主关键字备注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所示。图4-2 航班E-R图机票E-R图，如图4-3所示。图4-3 机票E-R图旅客E-R图，如图4-4所示。图4-4 旅客E-R图账单E-R图，如图4-5所示
6、。图4-5账单E-R图航班坐位E-R图，如图4-6所示。图4-6航班坐位E-R图取票通知E-R图，如图4-7所示。图4-7取票通知E-R图总E-R图，如图4-8所示。图4-8 总E-R图关系模式基本表飞机信息表（飞机编号、飞机型号、飞机座位）航班信息表（航班编号、飞机编号、 起飞地、目的地、起飞时间、飞行时间）机票信息表（机票编号、航班编号、旅客编号、机舱等级、座位号、更新时间、状态）旅客信息表（旅客编号、性别、联系、号）账单表 (账单编号、旅客编号、机票编号、价格、更新时间、状态)虚表（视图）航班坐位情况表 (机票信息表.航班编号、机票信息表.座位号、机票信息表.状态)取票通知表 (机票信息
7、表.航班编号、机票信息表. 旅客编号、 机票信息表.座位号、机票信息表.状态)三、数据库实现创建表空间与赋权限create temporary tablespace HKSP_TEMPtempfileC:oraclexeapporacleoradataXEHKSP_TEMP.dbfsize 50Mautoextend onnext 50M maxsize 1048Mextent management local;create tablespace HKSP_DATAatafile C:oraclexeapporacleoradataXEHKSP_DATA.dbfsize 50Mautoexte
8、nd onnext 50M maxsize 1048Mextent management local;create user HKSP identified by 123456default tablespace HKSP_DATAtemporary tablespace HKSP_TEMP;grant connect,resource,dba to HKSP;飞机信息表建立CREATE TABLE HKSP.plain (plain_id VARCHAR2(10 BYTE) NOT NULL primary key ,model VARCHAR2(10 BYTE) NOT NULL ,sea
9、t NUMBER NOT NULL )航班信息表建立CREATE TABLE HKSP.flight (flight_id 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.tickets (ticket
10、s_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_time DATE NOT NULL ,status NUMBER NOT NULL )旅客信息表建立CREATE TABLE HKSP.traveler (traveler_id VARCHAR2(
11、10 BYTE) NOT NULL primary key ,name VARCHAR2(10 BYTE) NOT NULL ,sex NUMBER NOT NULL ,id_value VARCHAR2(20 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 BYT
12、E) NOT NULL ,payment 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.tickets.statusFROMHKSP.tickets;取票通知视图CREATE OR REPLACE FORCE VIEW HKSP.notice_view AS SELECT
13、HKSP.tickets.flight_id,HKSP.tickets.traveler_id,HKSP.tickets.statusFROMHKSP.ticketsWHEREHKSP.tickets.status = 2;外键语句ALTER TABLE HKSP.bill ADD FOREIGN KEY (traveler_id) REFERENCES HKSP.traveler (traveler_id) ON DELETE CASCADE;ALTER TABLE HKSP.bill ADD FOREIGN KEY (tickets_id) REFERENCES HKSP.tickets
14、(tickets_id) ON DELETE CASCADE;ALTER TABLE HKSP.flight ADD FOREIGN KEY (plain_id) REFERENCES HKSP.plain (plain_id) 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, T123
15、06, 100);INSERT INTO HKSP.plain VALUES (F-T1236, T12306, 100);INSERT INTO HKSP.plain VALUES (F-T1237, T12306, 100);INSERT INTO HKSP.plain VALUES (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
16、.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(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
17、:SS), 3);添加用户INSERT INTO HKSP.traveler VALUES (LK00001, 小明, 1, 1, 123456781);INSERT INTO HKSP.traveler VALUES (LK00002, 小红, 2, 2, 123456782);INSERT INTO HKSP.traveler VALUES (LK00003, 小芳, 2, 3, 123456783);INSERT INTO HKSP.traveler VALUES (LK00004, 小小, 1, 4, 123456784);INSERT INTO HKSP.traveler VALUE
18、S (LK00005, 小亮, 1, 5, 123456785);INSERT INTO HKSP.traveler VALUES (LK00006, 小心, 2, 6, 123456786);预定机票(存储过程)CREATE OR REPLACE PROCEDURE book_tickets (flight_id IN VARCHAR2, traveler_id IN VARCHAR2, seating IN NUMBER)AS ticketsid tickets.tickets_id %TYPE; test_ticketsid NUMBER; test_cabin tickets.cabi
19、n_rating %TYPE; temp varchar(32); test_value NUMBER:=0;BEGIN -生成机票ID temp:=sys_guid(); ticketsid:= substr(temp,1,6)|substr(temp,28,4); -测试座位是否被预定 SELECT count(*) INTO test_ticketsid FROM tickets WHERE seating_list = seating; -查看结果 IF test_ticketsid = 0 THEN -座位未被预定 -判断仓位等级 test_value := seating/10;
20、CASE WHEN test_value 1 THEN test_cabin:=商务舱; WHEN test_value 2 THEN test_cabin:=二等舱; ELSE BEGIN test_cabin:=经济舱; END; END CASE; -预定机票 INSERT INTO HKSP.tickets VALUES (ticketsid, flight_id, traveler_id, seating, test_cabin, SYSDATE,1); DBMS_OUTPUT.PUT_LINE(预定成功); ELSE -座位已被预定 DBMS_OUTPUT.PUT_LINE(座位已
21、被预定); 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-获得机票ID SELECT tickets.tickets_id INTO test_tickets FROM tickets WHERE flight_id = flight_id AND traveler_id = traveler_id;
22、-更新状态 UPDATE tickets SET status=2 WHERE tickets.tickets_id = test_tickets;-返回机票ID RETURN test_tickets;EXCEPTION WHEN OTHERS THEN RETURN 你没有预定机票;END;生成账单(触发器和存储过程)插入账单存储过程CREATE OR REPLACE PROCEDURE insert_bill (traveler_id IN VARCHAR2, tickets_id IN VARCHAR2,status IN NUMBER)AStemp varchar2(32);uuid
23、 varchar2(10);BEGIN temp:=sys_guid(); uuid:=substr(temp,1,10); IF status = 2 THEN INSERT INTO HKSP.bill (bill_id,traveler_id,tickets_id,payment,updata_time,status) VALUES (uuid, traveler_id, tickets_id, 100 , SYSDATE, 1); END IF;END;机票状态触发器CREATE OR REPLACE TRIGGER bill_add AFTER UPDATE OF status ON
24、 HKSP.ticketsFOR EACH ROWBEGIN INSERT_BILL(:OLD.traveler_id,:OLD.tickets_id,:NEW.status);END;五、总结经过一周的努力，本次课程设计终于完成了。通过这次课程设计，我对数据库这门课程有了更深入的理解，数据库是一门实践性较强的课程，同时我明白了理论与实际应用相结合的重要性，掌握并熟练运用SQL语句，提高了我的综合运用所学知识的能力。1.独立创建表空间，创建用户，并赋予权限。2.创建表，指定主键，指定外键。3.Select ,include,update,delect等语句的使用。4.存储过程，存储函数，触发器的使用。5.对数据库的创建过程有了一个系统的理解。在本次课程设计和过程中，由于时间不是很长，再加上要求独立完成，系统需求分析上可能不是很全面，程序中还存在很多缺，希望老师能给予批评和指导。经过这次课程设计，我的知识得到了很大，经验也更加丰富。希望能在今后的不断的学习中和努力中，把程序做得更好。最后，感指导老师给予的帮助。
