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

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

試驗一數(shù)據(jù)庫的建立

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

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

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

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

（1）客戶個人信息（身份證號，姓名，性別，年齡，工作單位）（2）客戶入住信息（身份證號，客房號，入住時間，退房時間）（3）客房（客房號，客房類型號）

（4）定價（客房類型號，客房類型名，單價）（5）訂票（身份證號，機票號，訂票時間）

（6）機票（機票號，起始地，目的地，起飛時間，到達(dá)時間，機票價格，機

票狀態(tài)，航空公司號）

注：機票狀態(tài)只有三種0：未出票

1：已出票，但未被客戶取走2：已出票且已被客戶取走（7）航空公司（航空公司號，航空公司名，電話）

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

（9）清潔（雇員號，客房號）

說明：數(shù)據(jù)庫表中各個字段的數(shù)據(jù)類型及寬度，請根據(jù)實際狀況自定。數(shù)據(jù)

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

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

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

（3）客戶每次入住的客房號和入住時間不能為空。

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

1：已出票，但未被客戶取走2：已出票且已被客戶取走

試驗二數(shù)據(jù)庫的查詢、更新

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

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

1、試用SQL完成下面10個查詢操作。

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

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

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

（2）查詢身份證號為客戶最近入住酒店的日期是什

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

（4）哪些機票已出票，但還未被取走，查詢訂購這些機票的房客姓名和客房

號，以便及時通知他們來取票。（5）查詢從未訂過機票的客戶的數(shù)目。

（6）查詢總共從東方航空公司（EASTERNAIRLINES）訂購了多少張機票。3、試用SQL完成如下的更新操作

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

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

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

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

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

相應(yīng)的操作。

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

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

試驗三視圖、存儲過程的使用

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

該試驗用學(xué)生借書數(shù)據(jù)庫XSBOOK，學(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òu)成，依托3個基本表創(chuàng)立視圖和存儲過程。

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

字段名借書證號姓名專業(yè)類型與寬度CHAR(8)VARCHAR2(8)VARCHAR2(20)是否主碼是否允許空值√×××××說明性別借書量CHAR(2)INT××

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

借書證號1000000110000002姓名王娟李宏專業(yè)名計算機計算機計算機英語英語

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

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

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

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

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

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

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

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

借書證號100000011000000110000001100000011000000210000002100000021000000310000003100000032000000120000001

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

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

ISBN索書號借書時間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.借書證號,JY.索書號,JY.ISBN,BOOK.書名,

BOOK.出版社,BOOK.價格,JY.借書時間FROMXS

INNERJOINJYONXS.借書證號=JY.借書證號INNERJOINBOOKONBOOK.ISBN=JY.ISBN

（2）功能測試SELECT*FROMRBL

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

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

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

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

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

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

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

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

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

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

（8）假使存儲過程執(zhí)行過程中遇到錯誤，則回滾之前進(jìn)行的操作，并將輸出參數(shù)賦值為“執(zhí)行過程中遇到錯誤〞并返回0，表示存儲過程執(zhí)行過程中遇到錯誤，回滾到執(zhí)行存儲過程前的狀態(tài)。?實現(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借書證號=in_ReaderID;

IFCt_Reader5THENBEGIN

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

SELECT庫存量intoCt_StoreFROMBOOKWHEREISBN=in_ISBN;IFCt_Store=0THENBEGIN

out_str:='圖書庫存量為0';Error_Numer:=1;END;ENDIF;

SELECTcount(*)intoCt_HaveReadFROMJYWHERE借書證號=in_ReaderIDandISBN=in_ISBN;IFCt_HaveRead>0THenBEGIN

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

Error_Numer:=1;END;ENDIF;

SELECTcount(*)intoCt_BookIDFROMJYWHERE索書號=in_BookID;IFCt_BookID>0THENBEGIN

out_str:='索書號已存在';Error_Numer:=1;END;ENDIF;

IFError_Numer=0THEN

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

dbms_outpu