1、數據庫原理及應用課程設計設計題目 機票預訂信息系統 系 部 信息技術工程學院 班 級 網絡 1302 學 號 姓 名 2016 年 1 月1 / 16目錄一、需求分析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基本表

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

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

4、l 航班座位信息的輸入，修改查詢。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身份證號phoneV

7、ARCHAR211N聯系方式表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-

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

10、表（旅客編號、姓名、性別、聯系電話、身份證號）賬單表 (賬單編號、旅客編號、機票編號、價格、更新時間、狀態)虛表（視圖）航班坐位情況表 (機票信息表.航班編號、機票信息表.座位號、機票信息表.狀態)取票通知表 (機票信息表.航班編號、機票信息表. 旅客編號、 機票信息表.座位號、機票信息表.狀態)三、 數據庫實現Ø 創建表空間與賦權限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;四、 數據庫實施Ø 添加飛機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');Ø 預定機票(存儲過程)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);-測試座位是否被預定 SELECT count(*) INTO test_ticketsidFROM "tickets"WHERE "seating_list" = seating;-查看結果IF test_ticketsid = 0 THEN-座位未被預定-判斷倉位等級test_value := seating/10;C

31、ASE WHEN 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.P

32、UT_LINE('預定成功');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 "tick

33、ets"."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 '你沒