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

1、數(shù)據(jù)庫(kù)原理及應(yīng)用課程設(shè)計(jì)設(shè)計(jì)題目 機(jī)票預(yù)訂信息系統(tǒng) 系 部 信息技術(shù)工程學(xué)院 班 級(jí) 網(wǎng)絡(luò) 1302 學(xué) 號(hào) 姓 名 2016 年 1 月1 / 16目錄一、需求分析3Ø背景分析3Ø需求分析31)系統(tǒng)功能：32)系統(tǒng)信息：33)數(shù)據(jù)流圖：34)數(shù)據(jù)字典：4二、結(jié)構(gòu)設(shè)計(jì)5ØE-R圖5飛機(jī)E-R圖，如圖4-1所示6航班E-R圖，如圖4-2所示。6機(jī)票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ù)庫(kù)實(shí)現(xiàn)9Ø創(chuàng)建表空間與賦權(quán)限9Ø飛機(jī)信息表建立9Ø航班信息表建立10Ø機(jī)票信息表建立10Ø旅客信息表建立10Ø賬單信息表建立10Ø航班坐位視圖10Ø取票通知視圖11Ø外鍵語(yǔ)句11四、數(shù)據(jù)庫(kù)實(shí)施11Ø添加飛機(jī)11Ø添加航班11Ø添加用戶12Ø預(yù)定機(jī)票(存儲(chǔ)過(guò)程)12Ø打印取票通知(存儲(chǔ)函數(shù))13Ø生成賬單(觸發(fā)器和存儲(chǔ)過(guò)程)13五、總結(jié)14一、 需求分析Ø 背景分析以信息技術(shù)為主要標(biāo)志的科技進(jìn)步日新月異，高科技成果

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

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

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

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

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

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

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

10、表（旅客編號(hào)、姓名、性別、聯(lián)系電話、身份證號(hào)）賬單表 (賬單編號(hào)、旅客編號(hào)、機(jī)票編號(hào)、價(jià)格、更新時(shí)間、狀態(tài))虛表（視圖）航班坐位情況表 (機(jī)票信息表.航班編號(hào)、機(jī)票信息表.座位號(hào)、機(jī)票信息表.狀態(tài))取票通知表 (機(jī)票信息表.航班編號(hào)、機(jī)票信息表. 旅客編號(hào)、 機(jī)票信息表.座位號(hào)、機(jī)票信息表.狀態(tài))三、 數(shù)據(jù)庫(kù)實(shí)現(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;Ø 飛機(jī)信息表建立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 )Ø 機(jī)票信息表建立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;Ø 外鍵語(yǔ)句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ù)庫(kù)實(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', '北京', '馬來(lái)西亞', 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ù)定機(jī)票(存儲(chǔ)過(guò)程)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-生成機(jī)票IDtemp:=sys_guid(); ticketsid:= substr(temp,1,6)|substr(temp,28,4);-測(cè)試座位是否被預(yù)定 SELECT count(*) INTO test_ticketsidFROM "tickets"WHERE "seating_list" = seating;-查看結(jié)果IF test_ticketsid = 0 THEN-座位未被預(yù)定-判斷倉(cāng)位等級(jí)test_value := seating/10;C

31、ASE WHEN test_value < 1 THENtest_cabin:='商務(wù)艙'WHEN test_value < 2 THENtest_cabin:='二等艙'ELSEBEGINtest_cabin:='經(jīng)濟(jì)艙'END; END CASE; -預(yù)定機(jī)票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;Ø 打印取票通知(存儲(chǔ)函數(shù))CREATE OR REPLACE FUNCTION get_voucher (flight_id IN VARCHAR2, traveler_id IN VARCHAR2)RETURN VARCHAR2AStest_tickets "tickets"."tickets_id" %TYPE;BEGIN -獲得機(jī)票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; -返回機(jī)票IDRETURN test_tickets;EXCEPTIONWHEN OTHERS THENRETURN '你沒(méi)