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

1、数据库原理及应用实验指导书实验一 数据库的建立实验目的：掌握数据库的创建、基本表的创建和数据插入的方法实验要求：使用 Oracle 11g 进行数据库的创建、基本表的创建和数据插入实验条件：计算机， Oracle 11g实验内容：1、假设你是DBA， 需要定义系统中的所有表。 试根据下面给出的关系模式， 用 SQL完成数据表的建立。假设“阿里巴巴”酒店数据库中有如下的几个关系模式：（ 1） 客户个人信息（身份证号，姓名，性别，年龄，工作单位）（ 2） 客户入住信息（身份证号，客房号，入住时间，退房时间）（ 3） 客房（客房号，客房类型号）（ 4） 定价（客房类型号，客房类型名，单价）（ 5）

2、订票（身份证号，机票号，订票时间）（ 6） 机票（机票号，起始地，目的地，起飞时间，到达时间，机票价格，机票状态，航空公司号）注：机票状态只有三种 0 ：未出票1 ：已出票，但未被客户取走2 ：已出票且已被客户取走（ 7） 航空公司（航空公司号，航空公司名，电话）（ 8） 雇员（雇员号，姓名，雇员年龄，雇佣日期，被投诉次数）（ 9） 打扫（雇员号，客房号）说明：数据库表中各个字段的数据类型及宽度，请根据实际情况自定。数据库名、表名、字段名中英文均可，但需要有一定的实际含义。2、试根据下面的完整性约束要求，用SQLM上面已经建立好的“阿里巴巴”酒店数据库表进行完整性约束定义。1 雇员的年龄必须大

3、于 20 岁小于 55 岁，被投诉次数不能超过10 次。2 客户的性别只能是“男”或“女” 。3 客户每次入住的客房号和入住时间不能为空。4 各种类型客房的定价不得低于 100 元，也不得高于2000 元。5 机票的状态只能有三种：0：未出票1 ：已出票，但未被客户取走2 ：已出票且已被客户取走实验二 数据库的查询、更新实验目的：掌握数据库的查询、更新的方法实验要求：使用 Oracle 11g 实现数据库的查询、更新操作实验条件：计算机， Oracle 11g实验内容：1、试用SQLI成下面10个查询操作。（ 1） 查询各客户的姓名和工作单位。（ 2） 查询“张桦”客户的身份证号和年龄。（ 3

4、） 查询标准房的价格。（ 4） 查询“李明”先生所住的客房号码及入住的时间。（ 5） 查询“张桦”先生所订客房的价格。（ 6） 查询还有哪些机票已经出票但未被取走。（ 7） 查询被投诉的雇员的姓名及次数。（ 8） 查询各雇员姓名及所负责打扫的客房号。（ 9） 查询“张庭”雇员负责打扫的客房的号码。（ 10）查询各航空公司的名称和电话。2、试用SQLI成下面6个查询操作。（ 1） 查询空标准房的房间号。（ 2） 查询身份证号为“ 客户最近入住酒店的日期是什么，住了几天。（ 3） 查询空双人房的数目。（ 4） 哪些机票已出票， 但还未被取走， 查询订购这些机

5、票的房客姓名和客房号，以便及时通知他们来取票。（ 5） 查询从未订过机票的客户的数目。（6）查询总共从东方航空公司（EASTERN AIRLINE?S订购了多少张机票。3、试用SQLI成如下的更新操作（1） 酒店新进了一名打扫客房的雇员， 其雇员号为“E110” ， 姓名是 “张新” ，雇佣日期为“ 7/20/2007 ” ，试将该雇员的信息插入相应的表。2 身份证号为 ，姓名是“张明”，年龄为 35 岁的男客户到酒店订了一间客房号为“1100”的客房，请对相应的表进行相应的操作。3 身份证号为的客户退房了，请对相

6、应的表进行相应的操作。(4)将“E101”雇员的投诉次数加1。5 酒店欲解雇被投诉次数大于5 次的雇员， 试对相应的表进行相应的操作。实验三 视图、存储过程的使用实验目的：掌握数据库视图、存储过程的创建、修改和删除的方法实验要求：使用Oracle 11g实现数据库视图、存储过程的创建和使用实验条件：计算机，Oracle 11g实验内容：该实验用学生借书数据库XSBOQ K学生借书数据库相关信息如下：数据库名：XSBOOK数据文件名：XSBOOK日志文件名：XSBOOK _Log图书借阅系统的数据库为XSBOOK该数据库主要由学生（XS）、图书（BOOK、借 阅（JY） 3个表构成，依托3个基本

7、表创建视图和存储过程。表1学生信息表（XS）表结构字段名类型与宽度是否主码是否允许空值说明借书证号CHAR(8)VX姓名VARCHAR2(8)xX专业VARCHAR2(20)xX性别CHAR(2)xX借书量INTxX默认值为0表2学生信息表（XS）样本数据借书证号姓名专业名性别借书量10000001王娟计算机女410000002李宏计算机男310000003朱小波计算机男320000001李小丽英语女220000002吴涛英语男0表3 图书信息表（BOOK结构字段名类型与宽度是否主码是否允许空值说明ISBNCHAR(16)VX书名VARCHAR2(30)xX作者VARCHAR2(8)xX出版社

8、VARCHAR2(20)xX价格FLOATxX复本量INTxX当对书进行插入、删除时， 复本量应等于库存量库存量INTXX表4图书信息表（BOOK样本数据ISBN书名作者出版社价格复本量库存量7-111-06359-7We惊占点安全刘宗田机械工业出版社17527-113-04908-7，（SP.NET程序设计陈惠贞机械工业出版社551097-115-07715-0 ;SP & WEB数据库王国荣人民邮电出版社59527-115-10162-0计算机网络教程谢希仁人民邮电出版社2812107-302-03035-9C语百程序设计谭浩强清华大学出版社481067-505-37908-9WE

9、B?序设计吉根林电子工业出版社2587表5借阅表（JY）结构字段名类型与宽度是否主码是否允许空值说明借书证号CHAR(8)XX当借一本书时，BOOK勺库存量应ISBNCHAR(16)xX减1,同时，借书人的借书量应 加1;当还一本书时，BOOK勺库 存量应加1,同时，借书人的借 书量应减1索书号CHAR(10)VX借书时间DATExX表6借阅表(JY)样本数据借书证号ISBN索书号借书时间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、视图的创建和使用(1)把3个基本表联系起来，方便需要 3表关联的功能使用SQL命令如下：CREATE VIEW RBLASSELECT XSf 书证号，JY.索书号，JY.ISBN, BOOK.书名，BOOK.出版社,BOOK价格，JY.借书时间FROM XSINNER JOIN JY ON XS.借书证号=JY.借书证号INNER JOIN BOOK ON BOOK.ISBN=JY.ISBN( 2) 功能测试SEL

12、ECT * FROM RBL观察 3 表关联的字段数据正确性。2、存储过程的创建和使用? 参数借书证号(in_ReaderID )、ISBN (in_ISBN)、图书 ID (in_BookID)、执行信息( out_str ) 。? 实现功能根据存储过程的前3 个参数，实现读者图书“借阅”。第 4 个参数为输出参数，将存储过程的执行情况以字符串形式赋予此参数。? 编写思路( 1)根据“借书证号”查询XS 表是否存在该读者，如果不存在，则将输出参数out_str 赋值为“该读者不存在”并返回0，存储过程结束，表示不能借书。(2)根据"ISBN'查询BOOK是否存在该图书，如果

13、不存在，则将输出参数赋值为“该图书不存在”并返回 0，存储过程结束，表示不能借书。( 3)根据“借书证号”查询XS 表中该读者的借书量。如果借书量=5，则将输出参数赋值为“读者借书量不能大于5”并返回0，存储过程结束，表示不能借书。(4)根据"ISBN'查询BOOK!中该图书的库存量。如果库存量=0,则将输出参数赋值为“图书库存量为0”并返回0，存储过程结束，表示不能借书。( 5)查询JY 表中该读者是否已经借阅该图书，如果已经借过，则将输出参数赋值为“读者已经借过该书”并返回0，存储过程结束，表示不能借书。( 6)查询JY 表中该索书号是否已经存在，如果存在则将输出参数赋值

14、为“该索书号已存在”并返回0，存储过程结束，表示不能添加借书记录。(7)使JY表增加一条该读者借书记录；XS表中该读者的借书量加1; BOOK中 该图书(对应ISBN)记录的库存量减1。存储过程结束，将输出参数赋值为“借 书成功”并返回 1，表示借书成功。( 8)如果存储过程执行过程中遇到错误，则回滚之前进行的操作，并将输出参数赋值为“执行过程中遇到错误”并返回 0,表示存储过程执行过程中遇到错误，回滚到执行存储过程前的状态。? 实现方法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、借书证号=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借书证号=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 借书证号 =in_ReaderID and ISBN = in_ISBN;IF

18、 Ct_HaveRead > 0 THenBEGINout_str:= ' 读者已经借过该书 'Error_Numer:= 1;END;END IF;SELECT count(*) into Ct_BookID FROM JY WHERE 索书号 =in_BookID;IF Ct_BookID > 0 THENBEGINout_str:= ' 索书号已存在'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昔书证号=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('执行过程中遇至胜昔误！')；DBMS_OUTPUT.PUT_LINE(out_str);ROLLBACK;WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLCODE|'-'|SQLERRM);END;? 功能测试创建完存储过程Book_Borrow 后可以使用 PL/SQL 语句验证图书借阅是否能够实现：SELECT（昔书证号,借书量FROM XS WHER借书证号='10000001'/*查询 10000001 读者的原借书量*/SELECT ISBN,库存量 FROM BOOK WHERE ISBN='7-115-10162-0'/*查询图书的原库存量*/DECLARE out_s