Oracle機票預(yù)定信息系統(tǒng)數(shù)據(jù)庫設(shè)計參考模板

1、數(shù)據(jù)庫原理及應(yīng)用課程設(shè)計設(shè)計題目 機票預(yù)訂信息系統(tǒng) 系 部 信息技術(shù)工程學(xué)院 班 級 網(wǎng)絡(luò) 1302 學(xué) 號 姓 名 2016 年 1 月1 / 16目錄一、需求分析3Ø背景分析3Ø需求分析31)系統(tǒng)功能：32)系統(tǒng)信息：33)數(shù)據(jù)流圖：34)數(shù)據(jù)字典：4二、結(jié)構(gòu)設(shè)計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Ø關(guān)系模式9基本表

2、9虛表（視圖）9三、數(shù)據(jù)庫實現(xiàn)9Ø創(chuàng)建表空間與賦權(quán)限9Ø飛機信息表建立9Ø航班信息表建立10Ø機票信息表建立10Ø旅客信息表建立10Ø賬單信息表建立10Ø航班坐位視圖10Ø取票通知視圖11Ø外鍵語句11四、數(shù)據(jù)庫實施11Ø添加飛機11Ø添加航班11Ø添加用戶12Ø預(yù)定機票(存儲過程)12Ø打印取票通知(存儲函數(shù))13Ø生成賬單(觸發(fā)器和存儲過程)13五、總結(jié)14一、 需求分析Ø 背景分析以信息技術(shù)為主要標志的科技進步日新月異，高科技成果

3、向現(xiàn)實生產(chǎn)力的轉(zhuǎn)化越來越快。 縱觀全球經(jīng)濟發(fā)展， 信息技術(shù)和信息產(chǎn)業(yè)已經(jīng)成為經(jīng)濟增長的主要推動力之一， 正在改變著傳統(tǒng)的生產(chǎn)和經(jīng)營方式以至生活方式， 發(fā)達國家經(jīng)過產(chǎn)業(yè)結(jié)構(gòu)的升級和經(jīng)濟結(jié)構(gòu) 的轉(zhuǎn)型已進入信息經(jīng)濟階段。 機票預(yù)訂系統(tǒng)也開始影響著人們?nèi)粘Ｉ詈统鲂?，并且變得越來越重要。而原有的系統(tǒng)隨著航空公司載客量的迅猛增長和人們對便捷性要求的提高，已經(jīng)變得無法滿足需求。原有的系統(tǒng)不僅效率比較低下，而且在安全性、準確性等方面有很多不足。為了實現(xiàn)航空公司以及旅游行業(yè)的現(xiàn)代化管理，進一步提高工作效率，方便旅客，需要開發(fā)一個機票預(yù)訂系統(tǒng)。Ø 需求分析1) 系統(tǒng)功能：l 航班信息的輸入，修改查詢。

4、l 航班座位信息的輸入，修改查詢。l 根據(jù)旅客輸入的基本信息，為旅客安排航班，打印取票通知和帳單。l 旅客在飛機起飛前一天憑取票通知交款取票。l 旅客能夠退訂機票。l 能夠查詢每個航班的預(yù)定情況、計算航班的滿座率。2) 系統(tǒng)信息：l 航班信息: 航班編號、飛機編號、起飛地、目的地、起飛時間、飛行時間等。l 飛機信息：飛機編號、飛機型號、座位數(shù)。l 機票信息：航班編號、旅客編號、座位號、票價等。l 旅客信息：姓名、性別、聯(lián)系電話、身份證號等。3) 數(shù)據(jù)流圖：工作人員編輯航班飛機表航班表機票表用戶表賬單表旅客機票操作用戶信息編輯賬單操作4) 數(shù)據(jù)字典：根據(jù)總體結(jié)構(gòu)圖設(shè)計機票預(yù)定系統(tǒng)基本表結(jié)構(gòu)，其相

5、應(yīng)標的定義如下：表4-1 飛機信息<plain>字段名稱類型長度是否非空主關(guān)鍵字備注plain_idVARCHAR210Y飛機編號modelVARCHAR210N飛機型號seatint2N總座位表4-2 航班信息< flight >字段名稱類型長度是否非空主關(guān)鍵字備注flight_idVARCHAR210Y航班編號plain_idVARCHAR210N飛機編號startVARCHAR210N起飛地endVARCHAR210N目的地departure_timedatetimeN起飛時間durationint4N飛行時間表4-3 機票信息<tickets>字段名

6、稱類型長度是否非空主關(guān)鍵字備注tickets_idVARCHAR210Y機票編號flight_idVARCHAR210Y航班編號traveler_idVARCHAR210N旅客編號seating_listint4N座位號cabin_ratingVARCHAR210N機艙等級updata_timedatetimeN更新時間statusint10N狀態(tài)表4-4 旅客信息<traveler>字段名稱類型長度是否非空主關(guān)鍵字備注traveler_idVARCHAR210Y旅客編號nameVARCHAR210N姓名sexint1N性別id_valueVARCHAR220N身份證號phoneV

7、ARCHAR211N聯(lián)系方式表4-5 賬單信息<bill>字段名稱類型長度是否非空主關(guān)鍵字備注bill_idVARCHAR210Y賬單編號traveler_idVARCHAR210Y旅客編號tickets_idVARCHAR210Y機票編號paymentfloat(2)NN 價格updata_timedatetimeN更新時間statusint4N付款狀態(tài)二、 結(jié)構(gòu)設(shè)計Ø E-R圖根據(jù)需求分析抽象出信息結(jié)構(gòu)，可得該系統(tǒng)的E-R圖。 飛機E-R圖，如圖4-1所示。飛機飛機編號座位數(shù)飛機型號 圖4-1 飛機E-R圖 航班E-R圖，如圖4-2所示。航班出發(fā)地航班編號起

8、飛時間飛行時間目的地飛機編號圖4-2 航班E-R圖機票E-R圖，如圖4-3所示。機票機艙等級機票編號狀態(tài)座位號航班編號旅客編號號更新時間圖4-3 機票E-R圖旅客E-R圖，如圖4-4所示。旅客姓名性別身份證號號碼碼旅客編號聯(lián)系電話碼碼圖4-4 旅客E-R圖賬單E-R圖，如圖4-5所示。賬單機艙等級機票編號狀態(tài)座位號航班編號旅客編號號更新時間賬單編號圖4-5 賬單E-R圖航班坐位E-R圖，如圖4-6所示。航班坐位狀態(tài)座位號航班編號 圖4-6 航班坐位E-R圖取票通知E-R圖，如圖4-7所示。取票通知狀態(tài)座位號航班編號旅客編號   圖4-7 取票通知E-R圖總E-R圖，如圖4-

9、8所示。飛機飛機編號 座位數(shù)飛機型號 航班出發(fā)地航班編號飛行時間目的地飛機編號起飛時間 有11機票機艙等級機票編號狀態(tài)座位號  航班編號旅客編號號更新時間包含1m購買1旅客姓名 性別 身份證號號碼碼旅客編號 聯(lián)系電話碼碼1賬單機票編號狀態(tài)付款旅客編號號更新時間屬于11賬單編號圖4-8 總E-R圖Ø 關(guān)系模式基本表飛機信息表（飛機編號、飛機型號、飛機座位）航班信息表（航班編號、飛機編號、 起飛地、目的地、起飛時間、飛行時間）機票信息表（機票編號、航班編號、旅客編號、機艙等級、座位號、更新時間、狀態(tài)）旅客信息

10、表（旅客編號、姓名、性別、聯(lián)系電話、身份證號）賬單表 (賬單編號、旅客編號、機票編號、價格、更新時間、狀態(tài))虛表（視圖）航班坐位情況表 (機票信息表.航班編號、機票信息表.座位號、機票信息表.狀態(tài))取票通知表 (機票信息表.航班編號、機票信息表. 旅客編號、 機票信息表.座位號、機票信息表.狀態(tài))三、 數(shù)據(jù)庫實現(xiàn)Ø 創(chuàng)建表空間與賦權(quán)限create temporary tablespace HKSP_TEMPtempfile'C:oraclexeapporacleoradataXEHKSP_TEMP.dbf'size 50Mautoextend onnext 50M m

11、axsize 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,r

12、esource,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

13、_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 "

14、;HKSP"."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 ,"up

15、data_time" 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" VARC

16、HAR2(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 BYTE) NOT NULL ,"

17、;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",

18、HKSP."tickets"."status"FROMHKSP."tickets"Ø 取票通知視圖CREATE OR REPLACE FORCE VIEW "HKSP"."notice_view" AS SELECTHKSP."tickets"."flight_id",HKSP."tickets"."traveler_id",HKSP."tickets"."status&quo

19、t;FROMHKSP."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&qu

20、ot;."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_i

21、d") ON DELETE CASCADE;ALTER TABLE "HKSP"."tickets" ADD FOREIGN KEY ("traveler_id") REFERENCES "HKSP"."traveler" ("traveler_id") ON DELETE CASCADE;四、 數(shù)據(jù)庫實施Ø 添加飛機INSERT INTO "HKSP"."plain" VALUES ('F-T1235

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

23、LUES ('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、quot;."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

25、_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:SS'), '3');Ø 添加

26、用戶INSERT INTO "HKSP"."traveler" VALUES ('LK00001', '小明', '1', '#39;, '123456781');INSERT INTO "HKSP"."traveler" VALUES ('LK00002', '小紅', '2', '#39;, '1234

27、56782');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', '14052219931111

29、1116', '123456786');Ø 預(yù)定機票(存儲過程)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" %TY

30、PE;temp varchar(32);test_value NUMBER:=0;BEGIN-生成機票IDtemp:=sys_guid(); ticketsid:= substr(temp,1,6)|substr(temp,28,4);-測試座位是否被預(yù)定 SELECT count(*) INTO test_ticketsidFROM "tickets"WHERE "seating_list" = seating;-查看結(jié)果IF test_ticketsid = 0 THEN-座位未被預(yù)定-判斷倉位等級test_value := seating/10;C

31、ASE WHEN test_value < 1 THENtest_cabin:='商務(wù)艙'WHEN test_value < 2 THENtest_cabin:='二等艙'ELSEBEGINtest_cabin:='經(jīng)濟艙'END; END CASE; -預(yù)定機票INSERT INTO "HKSP"."tickets" VALUES (ticketsid, flight_id, traveler_id, seating, test_cabin, SYSDATE,1); DBMS_OUTPUT.P

32、UT_LINE('預(yù)定成功');ELSE-座位已被預(yù)定DBMS_OUTPUT.PUT_LINE('座位已被預(yù)定');END IF;END;Ø 打印取票通知(存儲函數(shù))CREATE OR REPLACE FUNCTION get_voucher (flight_id IN VARCHAR2, traveler_id IN VARCHAR2)RETURN VARCHAR2AStest_tickets "tickets"."tickets_id" %TYPE;BEGIN -獲得機票IDSELECT "tick

33、ets"."tickets_id" INTO test_ticketsFROM "tickets"WHERE "flight_id" = flight_id AND "traveler_id" = traveler_id; -更新狀態(tài)UPDATE "tickets" SET "status"=2WHERE "tickets"."tickets_id" = test_tickets; -返回機票IDRETURN test_tickets;EXCEPTIONWHEN OTHERS THENRETURN '你沒