《数据库原理及应用》试验指导书

本文格式为Word版，下载可任意编辑——《数据库原理及应用》试验指导书《数据库原理及应用》试验指导书

试验一数据库的建立

试验目的：把握数据库的创立、基本表的创立和数据插入的方法

试验要求：使用Oracle11g进行数据库的创立、基本表的创立和数据插入试验条件：计算机，Oracle11g试验内容：

1、假设你是DBA，需要定义系统中的所有表。试根据下面给出的关系模式，用SQL完成数据表的建立。

假设“阿里巴巴〞酒店数据库中有如下的几个关系模式：

（1）客户个人信息（身份证号，姓名，性别，年龄，工作单位）（2）客户入住信息（身份证号，客房号，入住时间，退房时间）（3）客房（客房号，客房类型号）

（4）定价（客房类型号，客房类型名，单价）（5）订票（身份证号，机票号，订票时间）

（6）机票（机票号，起始地，目的地，起飞时间，到达时间，机票价格，机

票状态，航空公司号）

注：机票状态只有三种0：未出票

1：已出票，但未被客户取走2：已出票且已被客户取走（7）航空公司（航空公司号，航空公司名，电话）

（8）雇员（雇员号，姓名，雇员年龄，雇佣日期，被投诉次数）

（9）清洁（雇员号，客房号）

说明：数据库表中各个字段的数据类型及宽度，请根据实际状况自定。数据

库名、表名、字段名中英文均可，但需要有一定的实际含义。

2、试根据下面的完整性约束要求，用SQL对上面已经建立好的“阿里巴巴〞酒店数据库表进行完整性约束定义。

（1）雇员的年龄必需大于20岁小于55岁，被投诉次数不能超过10次。（2）客户的性别只能是“男〞或“女〞。

（3）客户每次入住的客房号和入住时间不能为空。

（4）各种类型客房的定价不得低于100元，也不得高于2000元。（5）机票的状态只能有三种：0：未出票

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

试验二数据库的查询、更新

试验目的：把握数据库的查询、更新的方法

试验要求：使用Oracle11g实现数据库的查询、更新操作试验条件：计算机，Oracle11g试验内容：

1、试用SQL完成下面10个查询操作。

（1）查询各客户的姓名和工作单位。（2）查询“张桦〞客户的身份证号和年龄。（3）查询标准房的价格。

（4）查询“李明〞先生所住的客房号码及入住的时间。（5）查询“张桦〞先生所订客房的价格。（6）查询还有哪些机票已经出票但未被取走。（7）查询被投诉的雇员的姓名及次数。（8）查询各雇员姓名及所负责清洁的客房号。（9）查询“张庭〞雇员负责清洁的客房的号码。（10）查询各航空公司的名称和电话。2、试用SQL完成下面6个查询操作。

（1）查询空标准房的房间号。

（2）查询身份证号为客户最近入住酒店的日期是什

么，住了几天。（3）查询空双人房的数目。

（4）哪些机票已出票，但还未被取走，查询订购这些机票的房客姓名和客房

号，以便及时通知他们来取票。（5）查询从未订过机票的客户的数目。

（6）查询总共从东方航空公司（EASTERNAIRLINES）订购了多少张机票。3、试用SQL完成如下的更新操作

（1）酒店新进了一名清洁客房的雇员，其雇员号为“E110〞，姓名是“张新〞，雇

佣日期为“7/20/2023〞，试将该雇员的信息插入相应的表。

（2）身份证号为，姓名是“张明〞，年龄为35岁的男客

户到酒店订了一间客房号为“1100〞的客房，请对相应的表进行相应的操作。

（3）身份证号为的客户退房了，请对相应的表进行

相应的操作。

（4）将“E101〞雇员的投诉次数加1。

（5）酒店欲开除被投诉次数大于5次的雇员，试对相应的表进行相应的操作。

试验三视图、存储过程的使用

试验目的：把握数据库视图、存储过程的创立、修改和删除的方法试验要求：使用Oracle11g实现数据库视图、存储过程的创立和使用试验条件：计算机，Oracle11g试验内容：

该试验用学生借书数据库XSBOOK，学生借书数据库相关信息如下：数据库名：XSBOOK数据文件名：XSBOOK日志文件名：XSBOOK_Log

图书借阅系统的数据库为XSBOOK，该数据库主要由学生（XS）、图书（BOOK）、借阅（JY）3个表构成，依托3个基本表创立视图和存储过程。

表1学生信息表（XS）表结构

字段名借书证号姓名专业类型与宽度CHAR(8)VARCHAR2(8)VARCHAR2(20)是否主码是否允许空值√×××××说明性别借书量CHAR(2)INT××

××默认值为0表2学生信息表（XS）样本数据

借书证号1000000110000002姓名王娟李宏专业名计算机计算机计算机英语英语

表3图书信息表（BOOK）结构

字段名ISBN书名类型与宽度CHAR(16)VARCHAR2(30)VARCHAR2(8)是否主码是否允许空值√××××××

×××××××说明当对书进行插入、删除时，复本量应等于库存量性别女男男女男借书量4332010000003朱小波20000001李小丽20000002吴涛出版社VARCHAR2(20)价格复本量库存量FLOATINTINT

表4图书信息表（BOOK）样本数据

ISBN7-111-06359-Web站点安全77-113-04908-ASP.NET程序设陈惠贞机械工业出版社557计109刘宗田机械工业出版社1752书名作者出版社价格复本量库存量7-115-07715-ASP&WEB数据王国荣人民邮电出版社5907-115-10162-计算机网络教程谢希仁人民邮电出版社2807-302-03035-C语言程序设计谭浩强清华大学出版社4897-505-37908-WEB程序设计9

表5借阅表（JY）结构

字段名类型与宽度是否主码是否允许空值借书证号CHAR(8)ISBNCHAR(16)××√×××××说明当借一本书时，BOOK的库存量应减1，同时，借书人的借书量应加1；当还一本书时，BOOK的库存量应加1，同时，借书人吉根林电子工业出版社25871061210库52索书号CHAR(10)借书时间DATE的借书量应减1

表6借阅表（JY）样本数据

借书证号100000011000000110000001100000011000000210000002100000021000000310000003100000032000000120000001

1、视图的创立和使用

（1）把3个基本表联系起来，便利需要3表关联的功能使用。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表关联的字段数据正确性。2、存储过程的创立和使用?参数

借书证号（in_ReaderID）、ISBN（in_ISBN）、图书ID（in_BookID）、执行信息（out_str）。?实现功能

根据存储过程的前3个参数，实现读者图书“借阅〞。第4个参数为输出参数，将存储过程的执行状况以字符串形式赋予此参数。?编写思路

（1）根据“借书证号〞查询XS表是否存在该读者，假使不存在，则将输出参数out_str赋值为“该读者不存在〞并返回0，存储过程终止，表示不能借书。

（2）根据“ISBN〞查询BOOK中是否存在该图书，假使不存在，则将输出参数赋值为“该图书不存在〞并返回0，存储过程终止，表示不能借书。

（3）根据“借书证号〞查询XS表中该读者的借书量。假使借书量=5，则将输出参

数赋值为“读者借书量不能大于5〞并返回0，存储过程终止，表示不能借书。（4）根据“ISBN〞查询BOOK表中该图书的库存量。假使库存量=0，则将输出参数赋值为“图书库存量为0〞并返回0，存储过程终止，表示不能借书。

（5）查询JY表中该读者是否已经借阅该图书，假使已经借过，则将输出参数赋值为“读者已经借过该书〞并返回0，存储过程终止，表示不能借书。

（6）查询JY表中该索书号是否已经存在，假使存在则将输出参数赋值为“该索书号已存在〞并返回0，存储过程终止，表示不能添加借书记录。

（7）使JY表增加一条该读者借书记录；XS表中该读者的借书量加1；BOOK表中该图书（对应ISBN）记录的库存量减1。存储过程终止，将输出参数赋值为“借书成功〞并返回1，表示借书成功。

（8）假使存储过程执行过程中遇到错误，则回滚之前进行的操作，并将输出参数赋值为“执行过程中遇到错误〞并返回0，表示存储过程执行过程中遇到错误，回滚到执行存储过程前的状态。?实现方法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:='读者已经借过该书';

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