《數(shù)據(jù)庫原理及應(yīng)用》實(shí)驗(yàn)指導(dǎo)書

1、數(shù)據(jù)庫原理及應(yīng)用實(shí)驗(yàn)指導(dǎo)書實(shí)驗(yàn)一 數(shù)據(jù)庫的建立實(shí)驗(yàn)?zāi)康模赫莆諗?shù)據(jù)庫的創(chuàng)建、基本表的創(chuàng)建和數(shù)據(jù)插入的方法實(shí)驗(yàn)要求：使用 Oracle 11g 進(jìn)行數(shù)據(jù)庫的創(chuàng)建、基本表的創(chuàng)建和數(shù)據(jù)插入實(shí)驗(yàn)條件：計(jì)算機(jī)， Oracle 11g實(shí)驗(yàn)內(nèi)容：1、假設(shè)你是DBA， 需要定義系統(tǒng)中的所有表。 試根據(jù)下面給出的關(guān)系模式， 用 SQL完成數(shù)據(jù)表的建立。假設(shè)“阿里巴巴”酒店數(shù)據(jù)庫中有如下的幾個(gè)關(guān)系模式：（ 1） 客戶個(gè)人信息（身份證號(hào)，姓名，性別，年齡，工作單位）（ 2） 客戶入住信息（身份證號(hào)，客房號(hào)，入住時(shí)間，退房時(shí)間）（ 3） 客房（客房號(hào)，客房類型號(hào)）（ 4） 定價(jià)（客房類型號(hào)，客房類型名，單價(jià)）（ 5）

2、訂票（身份證號(hào)，機(jī)票號(hào)，訂票時(shí)間）（ 6） 機(jī)票（機(jī)票號(hào)，起始地，目的地，起飛時(shí)間，到達(dá)時(shí)間，機(jī)票價(jià)格，機(jī)票狀態(tài)，航空公司號(hào)）注：機(jī)票狀態(tài)只有三種 0 ：未出票1 ：已出票，但未被客戶取走2 ：已出票且已被客戶取走（ 7） 航空公司（航空公司號(hào)，航空公司名，電話）（ 8） 雇員（雇員號(hào)，姓名，雇員年齡，雇傭日期，被投訴次數(shù)）（ 9） 打掃（雇員號(hào)，客房號(hào)）說明：數(shù)據(jù)庫表中各個(gè)字段的數(shù)據(jù)類型及寬度，請(qǐng)根據(jù)實(shí)際情況自定。數(shù)據(jù)庫名、表名、字段名中英文均可，但需要有一定的實(shí)際含義。2、試根據(jù)下面的完整性約束要求，用SQLM上面已經(jīng)建立好的“阿里巴巴”酒店數(shù)據(jù)庫表進(jìn)行完整性約束定義。1 雇員的年齡必須大

3、于 20 歲小于 55 歲，被投訴次數(shù)不能超過10 次。2 客戶的性別只能是“男”或“女” 。3 客戶每次入住的客房號(hào)和入住時(shí)間不能為空。4 各種類型客房的定價(jià)不得低于 100 元，也不得高于2000 元。5 機(jī)票的狀態(tài)只能有三種：0：未出票1 ：已出票，但未被客戶取走2 ：已出票且已被客戶取走實(shí)驗(yàn)二 數(shù)據(jù)庫的查詢、更新實(shí)驗(yàn)?zāi)康模赫莆諗?shù)據(jù)庫的查詢、更新的方法實(shí)驗(yàn)要求：使用 Oracle 11g 實(shí)現(xiàn)數(shù)據(jù)庫的查詢、更新操作實(shí)驗(yàn)條件：計(jì)算機(jī)， Oracle 11g實(shí)驗(yàn)內(nèi)容：1、試用SQLI成下面10個(gè)查詢操作。（ 1） 查詢各客戶的姓名和工作單位。（ 2） 查詢“張樺”客戶的身份證號(hào)和年齡。（ 3

4、） 查詢標(biāo)準(zhǔn)房的價(jià)格。（ 4） 查詢“李明”先生所住的客房號(hào)碼及入住的時(shí)間。（ 5） 查詢“張樺”先生所訂客房的價(jià)格。（ 6） 查詢還有哪些機(jī)票已經(jīng)出票但未被取走。（ 7） 查詢被投訴的雇員的姓名及次數(shù)。（ 8） 查詢各雇員姓名及所負(fù)責(zé)打掃的客房號(hào)。（ 9） 查詢“張庭”雇員負(fù)責(zé)打掃的客房的號(hào)碼。（ 10）查詢各航空公司的名稱和電話。2、試用SQLI成下面6個(gè)查詢操作。（ 1） 查詢空標(biāo)準(zhǔn)房的房間號(hào)。（ 2） 查詢身份證號(hào)為“ 客戶最近入住酒店的日期是什么，住了幾天。（ 3） 查詢空雙人房的數(shù)目。（ 4） 哪些機(jī)票已出票， 但還未被取走， 查詢訂購這些機(jī)

5、票的房客姓名和客房號(hào)，以便及時(shí)通知他們來取票。（ 5） 查詢從未訂過機(jī)票的客戶的數(shù)目。（6）查詢總共從東方航空公司（EASTERN AIRLINE?S訂購了多少張機(jī)票。3、試用SQLI成如下的更新操作（1） 酒店新進(jìn)了一名打掃客房的雇員， 其雇員號(hào)為“E110” ， 姓名是 “張新” ，雇傭日期為“ 7/20/2007 ” ，試將該雇員的信息插入相應(yīng)的表。2 身份證號(hào)為 ，姓名是“張明”，年齡為 35 歲的男客戶到酒店訂了一間客房號(hào)為“1100”的客房，請(qǐng)對(duì)相應(yīng)的表進(jìn)行相應(yīng)的操作。3 身份證號(hào)為的客戶退房了，請(qǐng)對(duì)相

6、應(yīng)的表進(jìn)行相應(yīng)的操作。(4)將“E101”雇員的投訴次數(shù)加1。5 酒店欲解雇被投訴次數(shù)大于5 次的雇員， 試對(duì)相應(yīng)的表進(jìn)行相應(yīng)的操作。實(shí)驗(yàn)三 視圖、存儲(chǔ)過程的使用實(shí)驗(yàn)?zāi)康模赫莆諗?shù)據(jù)庫視圖、存儲(chǔ)過程的創(chuàng)建、修改和刪除的方法實(shí)驗(yàn)要求：使用Oracle 11g實(shí)現(xiàn)數(shù)據(jù)庫視圖、存儲(chǔ)過程的創(chuàng)建和使用實(shí)驗(yàn)條件：計(jì)算機(jī)，Oracle 11g實(shí)驗(yàn)內(nèi)容：該實(shí)驗(yàn)用學(xué)生借書數(shù)據(jù)庫XSBOQ K學(xué)生借書數(shù)據(jù)庫相關(guān)信息如下：數(shù)據(jù)庫名：XSBOOK數(shù)據(jù)文件名：XSBOOK日志文件名：XSBOOK _Log圖書借閱系統(tǒng)的數(shù)據(jù)庫為XSBOOK該數(shù)據(jù)庫主要由學(xué)生（XS）、圖書（BOOK、借 閱（JY） 3個(gè)表構(gòu)成，依托3個(gè)基本

7、表創(chuàng)建視圖和存儲(chǔ)過程。表1學(xué)生信息表（XS）表結(jié)構(gòu)字段名類型與寬度是否主碼是否允許空值說明借書證號(hào)CHAR(8)VX姓名VARCHAR2(8)xX專業(yè)VARCHAR2(20)xX性別CHAR(2)xX借書量INTxX默認(rèn)值為0表2學(xué)生信息表（XS）樣本數(shù)據(jù)借書證號(hào)姓名專業(yè)名性別借書量10000001王娟計(jì)算機(jī)女410000002李宏計(jì)算機(jī)男310000003朱小波計(jì)算機(jī)男320000001李小麗英語女220000002吳濤英語男0表3 圖書信息表（BOOK結(jié)構(gòu)字段名類型與寬度是否主碼是否允許空值說明ISBNCHAR(16)VX書名VARCHAR2(30)xX作者VARCHAR2(8)xX出版社

8、VARCHAR2(20)xX價(jià)格FLOATxX復(fù)本量INTxX當(dāng)對(duì)書進(jìn)行插入、刪除時(shí)， 復(fù)本量應(yīng)等于庫存量庫存量INTXX表4圖書信息表（BOOK樣本數(shù)據(jù)ISBN書名作者出版社價(jià)格復(fù)本量庫存量7-111-06359-7We驚占點(diǎn)安全劉宗田機(jī)械工業(yè)出版社17527-113-04908-7，（SP.NET程序設(shè)計(jì)陳惠貞機(jī)械工業(yè)出版社551097-115-07715-0 ;SP & WEB數(shù)據(jù)庫王國榮人民郵電出版社59527-115-10162-0計(jì)算機(jī)網(wǎng)絡(luò)教程謝希仁人民郵電出版社2812107-302-03035-9C語百程序設(shè)計(jì)譚浩強(qiáng)清華大學(xué)出版社481067-505-37908-9WE

9、B?序設(shè)計(jì)吉根林電子工業(yè)出版社2587表5借閱表（JY）結(jié)構(gòu)字段名類型與寬度是否主碼是否允許空值說明借書證號(hào)CHAR(8)XX當(dāng)借一本書時(shí)，BOOK勺庫存量應(yīng)ISBNCHAR(16)xX減1,同時(shí)，借書人的借書量應(yīng) 加1;當(dāng)還一本書時(shí)，BOOK勺庫 存量應(yīng)加1,同時(shí)，借書人的借 書量應(yīng)減1索書號(hào)CHAR(10)VX借書時(shí)間DATExX表6借閱表(JY)樣本數(shù)據(jù)借書證號(hào)ISBN索書號(hào)借書時(shí)間100000017-111-06359-711000000012011-03-01100000017-111-06359-711000000022011-05-10100000017-115-07715-03

10、1000000012011-05-10100000017-505-37908-961000000012011-09-01100000027-111-06359-711000000032011-03-10100000027-113-04908-721000000012011-03-11100000027-302-03035-951000000012011-03-11100000037-302-03035-951000000022011-04-10100000037-302-03035-951000000032011-04-10100000037-115-10162-041000000012011

11、-05-19200000017-115-10162-041000000022011-04-19200000017-302-03035-951000000042011-04-191、視圖的創(chuàng)建和使用(1)把3個(gè)基本表聯(lián)系起來，方便需要 3表關(guān)聯(lián)的功能使用SQL命令如下：CREATE VIEW RBLASSELECT XSf 書證號(hào)，JY.索書號(hào)，JY.ISBN, BOOK.書名，BOOK.出版社,BOOK價(jià)格，JY.借書時(shí)間FROM XSINNER JOIN JY ON XS.借書證號(hào)=JY.借書證號(hào)INNER JOIN BOOK ON BOOK.ISBN=JY.ISBN( 2) 功能測試SEL

12、ECT * FROM RBL觀察 3 表關(guān)聯(lián)的字段數(shù)據(jù)正確性。2、存儲(chǔ)過程的創(chuàng)建和使用? 參數(shù)借書證號(hào)(in_ReaderID )、ISBN (in_ISBN)、圖書 ID (in_BookID)、執(zhí)行信息( out_str ) 。? 實(shí)現(xiàn)功能根據(jù)存儲(chǔ)過程的前3 個(gè)參數(shù)，實(shí)現(xiàn)讀者圖書“借閱”。第 4 個(gè)參數(shù)為輸出參數(shù)，將存儲(chǔ)過程的執(zhí)行情況以字符串形式賦予此參數(shù)。? 編寫思路( 1)根據(jù)“借書證號(hào)”查詢XS 表是否存在該讀者，如果不存在，則將輸出參數(shù)out_str 賦值為“該讀者不存在”并返回0，存儲(chǔ)過程結(jié)束，表示不能借書。(2)根據(jù)"ISBN'查詢BOOK是否存在該圖書，如果

13、不存在，則將輸出參數(shù)賦值為“該圖書不存在”并返回 0，存儲(chǔ)過程結(jié)束，表示不能借書。( 3)根據(jù)“借書證號(hào)”查詢XS 表中該讀者的借書量。如果借書量=5，則將輸出參數(shù)賦值為“讀者借書量不能大于5”并返回0，存儲(chǔ)過程結(jié)束，表示不能借書。(4)根據(jù)"ISBN'查詢BOOK!中該圖書的庫存量。如果庫存量=0,則將輸出參數(shù)賦值為“圖書庫存量為0”并返回0，存儲(chǔ)過程結(jié)束，表示不能借書。( 5)查詢JY 表中該讀者是否已經(jīng)借閱該圖書，如果已經(jīng)借過，則將輸出參數(shù)賦值為“讀者已經(jīng)借過該書”并返回0，存儲(chǔ)過程結(jié)束，表示不能借書。( 6)查詢JY 表中該索書號(hào)是否已經(jīng)存在，如果存在則將輸出參數(shù)賦值

14、為“該索書號(hào)已存在”并返回0，存儲(chǔ)過程結(jié)束，表示不能添加借書記錄。(7)使JY表增加一條該讀者借書記錄；XS表中該讀者的借書量加1; BOOK中 該圖書(對(duì)應(yīng)ISBN)記錄的庫存量減1。存儲(chǔ)過程結(jié)束，將輸出參數(shù)賦值為“借 書成功”并返回 1，表示借書成功。( 8)如果存儲(chǔ)過程執(zhí)行過程中遇到錯(cuò)誤，則回滾之前進(jìn)行的操作，并將輸出參數(shù)賦值為“執(zhí)行過程中遇到錯(cuò)誤”并返回 0,表示存儲(chǔ)過程執(zhí)行過程中遇到錯(cuò)誤，回滾到執(zhí)行存儲(chǔ)過程前的狀態(tài)。? 實(shí)現(xiàn)方法PL/SQL 如下：CREATE OR REPLACE PROCEDURE Book_Borrow(in_ReaderID in char, in_ISBN

15、in char, in_BookID in char, out_str out char) ISCt_ReaderNumber(10);Ct_ISBNNumber(10);Ct_MaxReader Number(10);Ct_Store Number(10);Ct_HaveRead Number(10);Ct_BookIDNumber(10);Error_Numer Number(10);no_resultException;BEGINError_Numer:= 0;out_str:=''SELECT count(*) into Ct_Reader FROM XS WHERE

16、借書證號(hào)=in_ReaderID;IF Ct_Reader < 1 THENBEGINout_str:=該讀者不存在'Error_Numer:= 1;END;END IF;SELECT count(*) into Ct_ISBN FROM BOOK WHERE ISBN=in_ISBN;IF Ct_ISBN < 1 THENBEGINout_str:= ' 該圖書不存在'Error_Numer:= 1;END;END IF;SELECT 借書量 into Ct_MaxReader FROM XS WHERE借書證號(hào)=in_ReaderID;IF Ct_Max

17、Reader>5 THENBEGINout_str:= ' 讀者借書量不能大于 5'Error_Numer:= 1;END;END IF;SELECT 庫存量 into Ct_Store FROM BOOK WHERE ISBN=in_ISBN;IF Ct_Store =0 THENBEGINout_str:= ' 圖書庫存量為 0'Error_Numer:= 1;END;END IF;SELECT count(*) into Ct_HaveRead FROM JY WHERE 借書證號(hào) =in_ReaderID and ISBN = in_ISBN;IF

18、 Ct_HaveRead > 0 THenBEGINout_str:= ' 讀者已經(jīng)借過該書 'Error_Numer:= 1;END;END IF;SELECT count(*) into Ct_BookID FROM JY WHERE 索書號(hào) =in_BookID;IF Ct_BookID > 0 THENBEGINout_str:= ' 索書號(hào)已存在'Error_Numer:= 1;END;END IF;IF Error_Numer = 0 THENINSERT INTO JY VALUES(in_ReaderID, in_ISBN, in_B

19、ookID, sysdate);UPDATE XS SET昔書量=借書量 +1 WHERE昔書證號(hào)=in_ReaderID ;UPDATE BOOK SET 庫存量 =庫存量 -1 WHERE ISBN=in_ISBN;out_str:= ' 借書成功 'IF SQL%NOTFOUND THENRAISE no_result;END IF;COMMIT;dbms_output.put_line(out_str);ELSEdbms_output.put_line(out_str);END IF;EXCEPTIONWHEN no_result THENDBMS_OUTPUT.PUT

20、_LINE('執(zhí)行過程中遇至勝昔誤！')；DBMS_OUTPUT.PUT_LINE(out_str);ROLLBACK;WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLCODE|'-'|SQLERRM);END;? 功能測試創(chuàng)建完存儲(chǔ)過程Book_Borrow 后可以使用 PL/SQL 語句驗(yàn)證圖書借閱是否能夠?qū)崿F(xiàn)：SELECT（昔書證號(hào),借書量FROM XS WHER借書證號(hào)='10000001'/*查詢 10000001 讀者的原借書量*/SELECT ISBN,庫存量 FROM BOOK WHERE ISBN='7-115-10162-0'/*查詢圖書的原庫存量*/DECLARE out_s