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

《数据库原理及应用》实验指导书实验一数据库的建立实验目的：掌握数据库的创建、基本表的创建和数据插入的方法实验要求：使用Oracle 11g进行数据库的创建、基本表的创建和数据插入实验条件：计算机，Oracle 11g实验内容：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：已出票且已被客户取走实验二数据库的查询、更新实验目的：掌握数据库的查询、更新的方法实验要求：使用Oracle 11g实现数据库的查询、更新操作实验条件：计算机，Oracle 11g实验内容：1、试用SQL完成下面10个查询操作。

（1）查询各客户的姓名和工作单位。

（2）查询“张桦”客户的身份证号和年龄。

（3）查询标准房的价格。

（4）查询“李明”先生所住的客房号码及入住的时间。

（5）查询“张桦”先生所订客房的价格。

（6）查询还有哪些机票已经出票但未被取走。

（7）查询被投诉的雇员的姓名及次数。

（8）查询各雇员姓名及所负责打扫的客房号。

（9）查询“张庭”雇员负责打扫的客房的号码。

（10）查询各航空公司的名称和电话。

2、试用SQL完成下面6个查询操作。

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

（2）查询身份证号为“310222************”客户最近入住酒店的日期是什么，住了几天。

（3）查询空双人房的数目。

（4）哪些机票已出票，但还未被取走，查询订购这些机票的房客姓名和客房号，以便及时通知他们来取票。

（5）查询从未订过机票的客户的数目。

（6）查询总共从东方航空公司（EASTERN AIRLINES）订购了多少张机票。

3、试用SQL完成如下的更新操作（1）酒店新进了一名打扫客房的雇员，其雇员号为“E110”，姓名是“张新”，雇佣日期为“7/20/2007”，试将该雇员的信息插入相应的表。

（2）身份证号为“310222************”，姓名是“张明”，年龄为35岁的男客户到酒店订了一间客房号为“1100”的客房，请对相应的表进行相应的操作。

（3）身份证号为“310222************”的客户退房了，请对相应的表进行相应的操作。

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

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

实验三视图、存储过程的使用实验目的：掌握数据库视图、存储过程的创建、修改和删除的方法实验要求：使用Oracle 11g实现数据库视图、存储过程的创建和使用实验条件：计算机，Oracle 11g实验内容：该实验用学生借书数据库XSBOOK，学生借书数据库相关信息如下：数据库名：XSBOOK数据文件名：XSBOOK日志文件名：XSBOOK _Log图书借阅系统的数据库为XSBOOK，该数据库主要由学生（XS）、图书（BOOK）、借阅（JY）3个表构成，依托3个基本表创建视图和存储过程。

表1 学生信息表（XS）表结构表2 学生信息表（XS）样本数据表3 图书信息表（BOOK）结构表4 图书信息表（BOOK）样本数据表5 借阅表（JY）结构表6 借阅表（JY）样本数据1、视图的创建和使用（1）把3个基本表联系起来，方便需要3表关联的功能使用。

SQL命令如下：CREATE VIEW RBLASSELECT XS.借书证号, JY.索书号, JY.ISBN, BOOK.书名,BOOK.出版社,BOOK.价格, JY.借书时间FROM XSINNER JOIN JY ON XS.借书证号=JY.借书证号INNER JOIN BOOK ON BOOK.ISBN=JY.ISBN （2）功能测试SELECT * FROM RBL观察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如下：CREATE OR REPLACE PROCEDURE Book_Borrow(in_ReaderID in char,in_ISBN in char,in_BookID in char,out_str out char)ISCt_Reader Number(10);Ct_ISBN Number(10);Ct_MaxReader Number(10);Ct_Store Number(10);Ct_HaveRead Number(10);Ct_BookID Number(10);Error_Numer Number(10);no_result Exception;BEGINError_Numer:= 0;out_str:='';SELECT count(*) into Ct_Reader FROM XS WHERE 借书证号=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_MaxReader>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 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 V ALUES(in_ReaderID, in_ISBN, in_BookID, 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_LINE('执行过程中遇到错误!');DBMS_OUTPUT.PUT_LINE(out_str);ROLLBACK;WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);END;➢功能测试创建完存储过程Book_Borrow后可以使用PL/SQL语句验证图书借阅是否能够实现：SELECT 借书证号, 借书量FROM XS WHERE 借书证号= '10000001';/*查询10000001读者的原借书量*/SELECT ISBN, 库存量FROM BOOK WHERE ISBN='7-115-10162-0' ;/*查询图书的原库存量*/DECLARE out_str char(30);beginBook_Borrow('10000001', '7-115-10162-0', '5100000007',out_str); end;/*执行存储过程，实现图书借阅*/SELECT 借书证号, 借书量FROM XS WHERE 借书证号= '10000001';/*查询10000001号读者的现有借书量*/SELECT ISBN, 库存量FROM BOOK WHERE ISBN='7-115-10162-0' ;/*查询图书的现有库存量*/实验四数据库系统的安全性管理实验目的：掌握账户的建立，数据库用户权限的分配与回收实验要求：使用Oracle 11g实现账户的建立，数据库用户权限的分配与回收实验条件：计算机，Oracle 11g实验内容：1、为阿里巴巴酒店数据库创建以下用户：用户名：张敏，王林，李新，王立，王铭，张天密码：自行设定2、作为DBA的你，现在要负责给不同的数据库用户授予不同的权限，各用户可以执行的操作如下：（1）张敏和王林有权查询酒店客户的个人信息。