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

本文格式為Word版，下載可任意編輯——《數(shù)據(jù)庫(kù)原理及應(yīng)用》試驗(yàn)指導(dǎo)書(shū)《數(shù)據(jù)庫(kù)原理及應(yīng)用》試驗(yàn)指導(dǎo)書(shū)

試驗(yàn)一數(shù)據(jù)庫(kù)的建立

試驗(yàn)?zāi)康模喊盐諗?shù)據(jù)庫(kù)的創(chuàng)立、基本表的創(chuàng)立和數(shù)據(jù)插入的方法

試驗(yàn)要求：使用Oracle11g進(jìn)行數(shù)據(jù)庫(kù)的創(chuàng)立、基本表的創(chuàng)立和數(shù)據(jù)插入試驗(yàn)條件：計(jì)算機(jī)，Oracle11g試驗(yàn)內(nèi)容：

1、假設(shè)你是DBA，需要定義系統(tǒng)中的所有表。試根據(jù)下面給出的關(guān)系模式，用SQL完成數(shù)據(jù)表的建立。

假設(shè)“阿里巴巴〞酒店數(shù)據(jù)庫(kù)中有如下的幾個(gè)關(guān)系模式：

（1）客戶(hù)個(gè)人信息（身份證號(hào)，姓名，性別，年齡，工作單位）（2）客戶(hù)入住信息（身份證號(hào)，客房號(hào)，入住時(shí)間，退房時(shí)間）（3）客房（客房號(hào)，客房類(lèi)型號(hào)）

（4）定價(jià)（客房類(lèi)型號(hào)，客房類(lèi)型名，單價(jià)）（5）訂票（身份證號(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：已出票，但未被客戶(hù)取走2：已出票且已被客戶(hù)取走（7）航空公司（航空公司號(hào)，航空公司名，電話(huà)）

（8）雇員（雇員號(hào)，姓名，雇員年齡，雇傭日期，被投訴次數(shù)）

（9）清潔（雇員號(hào)，客房號(hào)）

說(shuō)明：數(shù)據(jù)庫(kù)表中各個(gè)字段的數(shù)據(jù)類(lèi)型及寬度，請(qǐng)根據(jù)實(shí)際狀況自定。數(shù)據(jù)

庫(kù)名、表名、字段名中英文均可，但需要有一定的實(shí)際含義。

2、試根據(jù)下面的完整性約束要求，用SQL對(duì)上面已經(jīng)建立好的“阿里巴巴〞酒店數(shù)據(jù)庫(kù)表進(jìn)行完整性約束定義。

（1）雇員的年齡必需大于20歲小于55歲，被投訴次數(shù)不能超過(guò)10次。（2）客戶(hù)的性別只能是“男〞或“女〞。

（3）客戶(hù)每次入住的客房號(hào)和入住時(shí)間不能為空。

（4）各種類(lèi)型客房的定價(jià)不得低于100元，也不得高于2000元。（5）機(jī)票的狀態(tài)只能有三種：0：未出票

1：已出票，但未被客戶(hù)取走2：已出票且已被客戶(hù)取走

試驗(yàn)二數(shù)據(jù)庫(kù)的查詢(xún)、更新

試驗(yàn)?zāi)康模喊盐諗?shù)據(jù)庫(kù)的查詢(xún)、更新的方法

試驗(yàn)要求：使用Oracle11g實(shí)現(xiàn)數(shù)據(jù)庫(kù)的查詢(xún)、更新操作試驗(yàn)條件：計(jì)算機(jī)，Oracle11g試驗(yàn)內(nèi)容：

1、試用SQL完成下面10個(gè)查詢(xún)操作。

（1）查詢(xún)各客戶(hù)的姓名和工作單位。（2）查詢(xún)“張樺〞客戶(hù)的身份證號(hào)和年齡。（3）查詢(xún)標(biāo)準(zhǔn)房的價(jià)格。

（4）查詢(xún)“李明〞先生所住的客房號(hào)碼及入住的時(shí)間。（5）查詢(xún)“張樺〞先生所訂客房的價(jià)格。（6）查詢(xún)還有哪些機(jī)票已經(jīng)出票但未被取走。（7）查詢(xún)被投訴的雇員的姓名及次數(shù)。（8）查詢(xún)各雇員姓名及所負(fù)責(zé)清潔的客房號(hào)。（9）查詢(xún)“張庭〞雇員負(fù)責(zé)清潔的客房的號(hào)碼。（10）查詢(xún)各航空公司的名稱(chēng)和電話(huà)。2、試用SQL完成下面6個(gè)查詢(xún)操作。

（1）查詢(xún)空標(biāo)準(zhǔn)房的房間號(hào)。

（2）查詢(xún)身份證號(hào)為客戶(hù)最近入住酒店的日期是什

么，住了幾天。（3）查詢(xún)空雙人房的數(shù)目。

（4）哪些機(jī)票已出票，但還未被取走，查詢(xún)訂購(gòu)這些機(jī)票的房客姓名和客房

號(hào)，以便及時(shí)通知他們來(lái)取票。（5）查詢(xún)從未訂過(guò)機(jī)票的客戶(hù)的數(shù)目。

（6）查詢(xún)總共從東方航空公司（EASTERNAIRLINES）訂購(gòu)了多少?gòu)垯C(jī)票。3、試用SQL完成如下的更新操作

（1）酒店新進(jìn)了一名清潔客房的雇員，其雇員號(hào)為“E110〞，姓名是“張新〞，雇

傭日期為“7/20/2023〞，試將該雇員的信息插入相應(yīng)的表。

（2）身份證號(hào)為，姓名是“張明〞，年齡為35歲的男客

戶(hù)到酒店訂了一間客房號(hào)為“1100〞的客房，請(qǐng)對(duì)相應(yīng)的表進(jìn)行相應(yīng)的操作。

（3）身份證號(hào)為的客戶(hù)退房了，請(qǐng)對(duì)相應(yīng)的表進(jìn)行

相應(yīng)的操作。

（4）將“E101〞雇員的投訴次數(shù)加1。

（5）酒店欲開(kāi)除被投訴次數(shù)大于5次的雇員，試對(duì)相應(yīng)的表進(jìn)行相應(yīng)的操作。

試驗(yàn)三視圖、存儲(chǔ)過(guò)程的使用

試驗(yàn)?zāi)康模喊盐諗?shù)據(jù)庫(kù)視圖、存儲(chǔ)過(guò)程的創(chuàng)立、修改和刪除的方法試驗(yàn)要求：使用Oracle11g實(shí)現(xiàn)數(shù)據(jù)庫(kù)視圖、存儲(chǔ)過(guò)程的創(chuàng)立和使用試驗(yàn)條件：計(jì)算機(jī)，Oracle11g試驗(yàn)內(nèi)容：

該試驗(yàn)用學(xué)生借書(shū)數(shù)據(jù)庫(kù)XSBOOK，學(xué)生借書(shū)數(shù)據(jù)庫(kù)相關(guān)信息如下：數(shù)據(jù)庫(kù)名：XSBOOK數(shù)據(jù)文件名：XSBOOK日志文件名：XSBOOK_Log

圖書(shū)借閱系統(tǒng)的數(shù)據(jù)庫(kù)為XSBOOK，該數(shù)據(jù)庫(kù)主要由學(xué)生（XS）、圖書(shū)（BOOK）、借閱（JY）3個(gè)表構(gòu)成，依托3個(gè)基本表創(chuàng)立視圖和存儲(chǔ)過(guò)程。

表1學(xué)生信息表（XS）表結(jié)構(gòu)

字段名借書(shū)證號(hào)姓名專(zhuān)業(yè)類(lèi)型與寬度CHAR(8)VARCHAR2(8)VARCHAR2(20)是否主碼是否允許空值√×××××說(shuō)明性別借書(shū)量CHAR(2)INT××

××默認(rèn)值為0表2學(xué)生信息表（XS）樣本數(shù)據(jù)

借書(shū)證號(hào)1000000110000002姓名王娟李宏專(zhuān)業(yè)名計(jì)算機(jī)計(jì)算機(jī)計(jì)算機(jī)英語(yǔ)英語(yǔ)

表3圖書(shū)信息表（BOOK）結(jié)構(gòu)

字段名ISBN書(shū)名類(lèi)型與寬度CHAR(16)VARCHAR2(30)VARCHAR2(8)是否主碼是否允許空值√××××××

×××××××說(shuō)明當(dāng)對(duì)書(shū)進(jìn)行插入、刪除時(shí)，復(fù)本量應(yīng)等于庫(kù)存量性別女男男女男借書(shū)量4332010000003朱小波20000001李小麗20000002吳濤出版社VARCHAR2(20)價(jià)格復(fù)本量庫(kù)存量FLOATINTINT

表4圖書(shū)信息表（BOOK）樣本數(shù)據(jù)

ISBN7-111-06359-Web站點(diǎn)安全77-113-04908-ASP.NET程序設(shè)陳惠貞機(jī)械工業(yè)出版社557計(jì)109劉宗田機(jī)械工業(yè)出版社1752書(shū)名作者出版社價(jià)格復(fù)本量庫(kù)存量7-115-07715-ASP&WEB數(shù)據(jù)王國(guó)榮人民郵電出版社5907-115-10162-計(jì)算機(jī)網(wǎng)絡(luò)教程謝希仁人民郵電出版社2807-302-03035-C語(yǔ)言程序設(shè)計(jì)譚浩強(qiáng)清華大學(xué)出版社4897-505-37908-WEB程序設(shè)計(jì)9

表5借閱表（JY）結(jié)構(gòu)

字段名類(lèi)型與寬度是否主碼是否允許空值借書(shū)證號(hào)CHAR(8)ISBNCHAR(16)××√×××××說(shuō)明當(dāng)借一本書(shū)時(shí)，BOOK的庫(kù)存量應(yīng)減1，同時(shí)，借書(shū)人的借書(shū)量應(yīng)加1；當(dāng)還一本書(shū)時(shí)，BOOK的庫(kù)存量應(yīng)加1，同時(shí)，借書(shū)人吉根林電子工業(yè)出版社25871061210庫(kù)52索書(shū)號(hào)CHAR(10)借書(shū)時(shí)間DATE的借書(shū)量應(yīng)減1

表6借閱表（JY）樣本數(shù)據(jù)

借書(shū)證號(hào)100000011000000110000001100000011000000210000002100000021000000310000003100000032000000120000001

1、視圖的創(chuàng)立和使用

（1）把3個(gè)基本表聯(lián)系起來(lái)，便利需要3表關(guān)聯(lián)的功能使用。SQL命令如下：

ISBN索書(shū)號(hào)借書(shū)時(shí)間2023-03-012023-05-102023-05-102023-09-012023-03-102023-03-112023-03-112023-04-102023-04-102023-05-192023-04-192023-04-197-111-06359-711000000017-111-06359-711000000027-115-07715-031000000017-505-37908-961000000017-111-06359-711000000037-113-04908-721000000017-302-03035-951000000017-302-03035-951000000027-302-03035-951000000037-115-10162-041000000017-115-10162-041000000027-302-03035-95100000004CREATEVIEWRBL

AS

SELECTXS.借書(shū)證號(hào),JY.索書(shū)號(hào),JY.ISBN,BOOK.書(shū)名,

BOOK.出版社,BOOK.價(jià)格,JY.借書(shū)時(shí)間FROMXS

INNERJOINJYONXS.借書(shū)證號(hào)=JY.借書(shū)證號(hào)INNERJOINBOOKONBOOK.ISBN=JY.ISBN

（2）功能測(cè)試SELECT*FROMRBL

觀測(cè)3表關(guān)聯(lián)的字段數(shù)據(jù)正確性。2、存儲(chǔ)過(guò)程的創(chuàng)立和使用?參數(shù)

借書(shū)證號(hào)（in_ReaderID）、ISBN（in_ISBN）、圖書(shū)ID（in_BookID）、執(zhí)行信息（out_str）。?實(shí)現(xiàn)功能

根據(jù)存儲(chǔ)過(guò)程的前3個(gè)參數(shù)，實(shí)現(xiàn)讀者圖書(shū)“借閱〞。第4個(gè)參數(shù)為輸出參數(shù)，將存儲(chǔ)過(guò)程的執(zhí)行狀況以字符串形式賦予此參數(shù)。?編寫(xiě)思路

（1）根據(jù)“借書(shū)證號(hào)〞查詢(xún)XS表是否存在該讀者，假使不存在，則將輸出參數(shù)out_str賦值為“該讀者不存在〞并返回0，存儲(chǔ)過(guò)程終止，表示不能借書(shū)。

（2）根據(jù)“ISBN〞查詢(xún)BOOK中是否存在該圖書(shū)，假使不存在，則將輸出參數(shù)賦值為“該圖書(shū)不存在〞并返回0，存儲(chǔ)過(guò)程終止，表示不能借書(shū)。

（3）根據(jù)“借書(shū)證號(hào)〞查詢(xún)XS表中該讀者的借書(shū)量。假使借書(shū)量=5，則將輸出參

數(shù)賦值為“讀者借書(shū)量不能大于5〞并返回0，存儲(chǔ)過(guò)程終止，表示不能借書(shū)。（4）根據(jù)“ISBN〞查詢(xún)BOOK表中該圖書(shū)的庫(kù)存量。假使庫(kù)存量=0，則將輸出參數(shù)賦值為“圖書(shū)庫(kù)存量為0〞并返回0，存儲(chǔ)過(guò)程終止，表示不能借書(shū)。

（5）查詢(xún)JY表中該讀者是否已經(jīng)借閱該圖書(shū)，假使已經(jīng)借過(guò)，則將輸出參數(shù)賦值為“讀者已經(jīng)借過(guò)該書(shū)〞并返回0，存儲(chǔ)過(guò)程終止，表示不能借書(shū)。

（6）查詢(xún)JY表中該索書(shū)號(hào)是否已經(jīng)存在，假使存在則將輸出參數(shù)賦值為“該索書(shū)號(hào)已存在〞并返回0，存儲(chǔ)過(guò)程終止，表示不能添加借書(shū)記錄。

（7）使JY表增加一條該讀者借書(shū)記錄；XS表中該讀者的借書(shū)量加1；BOOK表中該圖書(shū)（對(duì)應(yīng)ISBN）記錄的庫(kù)存量減1。存儲(chǔ)過(guò)程終止，將輸出參數(shù)賦值為“借書(shū)成功〞并返回1，表示借書(shū)成功。

（8）假使存儲(chǔ)過(guò)程執(zhí)行過(guò)程中遇到錯(cuò)誤，則回滾之前進(jìn)行的操作，并將輸出參數(shù)賦值為“執(zhí)行過(guò)程中遇到錯(cuò)誤〞并返回0，表示存儲(chǔ)過(guò)程執(zhí)行過(guò)程中遇到錯(cuò)誤，回滾到執(zhí)行存儲(chǔ)過(guò)程前的狀態(tài)。?實(shí)現(xiàn)方法PL/SQL如下：

CREATEORREPLACEPROCEDUREBook_Borrow(in_ReaderIDinchar,in_ISBNinchar,in_BookIDinchar,out_stroutchar)IS

Ct_ReaderCt_ISBN

Number(10);Number(10);

Ct_MaxReaderNumber(10);

Ct_StoreNumber(10);Ct_HaveReadNumber(10);Ct_BookID

Number(10);

Error_NumerNumber(10);no_resultBEGIN

Error_Numer:=0;out_str:='';

SELECTcount(*)intoCt_ReaderFROMXSWHERE借書(shū)證號(hào)=in_ReaderID;

IFCt_Reader5THENBEGIN

out_str:='讀者借書(shū)量不能大于5';Error_Numer:=1;END;ENDIF;

SELECT庫(kù)存量intoCt_StoreFROMBOOKWHEREISBN=in_ISBN;IFCt_Store=0THENBEGIN

out_str:='圖書(shū)庫(kù)存量為0';Error_Numer:=1;END;ENDIF;

SELECTcount(*)intoCt_HaveReadFROMJYWHERE借書(shū)證號(hào)=in_ReaderIDandISBN=in_ISBN;IFCt_HaveRead>0THenBEGIN

out_str:='讀者已經(jīng)借過(guò)該書(shū)';

Error_Numer:=1;END;ENDIF;

SELECTcount(*)intoCt_BookIDFROMJYWHERE索書(shū)號(hào)=in_BookID;IFCt_BookID>0THENBEGIN

out_str:='索書(shū)號(hào)已存在';Error_Numer:=1;END;ENDIF;

IFError_Numer=0THEN

INSERTINTOJYVALUES(in_ReaderID,in_ISBN,in_BookID,sysdate);UPDATEXSSET借書(shū)量=借書(shū)量+1WHERE借書(shū)證號(hào)=in_ReaderID;UPDATEBOOKSET庫(kù)存量=庫(kù)存量-1WHEREISBN=in_ISBN;out_str:='借書(shū)成功';IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;COMMIT;

dbms_outpu