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

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

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

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

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

5、客户的数目。（6） 查询总共从东方航空公司（EASTERN AIRLINES）订购了多少张机票。3、试用SQL完成如下的更新操作（1） 酒店新进了一名打扫客房的雇员，其雇员号为“E110”，姓名是“张新”，雇佣日期为“7/20/2007”，试将该雇员的信息插入相应的表。（2） 身份证号为“”，姓名是“张明”，年龄为35岁的男客户到酒店订了一间客房号为“1100”的客房，请对相应的表进行相应的操作。（3） 身份证号为“”的客户退房了，请对相应的表进行相应的操作。（4） 将“E101”雇员的投诉次数加1。（5） 酒店欲解雇被投诉次数大于5次的雇员，试对相应的表进行相应的操作。实验三 视图、存储过程

6、的使用实验目的：掌握数据库视图、存储过程的创建、修改和删除的方法实验要求：使用Oracle 11g实现数据库视图、存储过程的创建和使用实验条件：计算机，Oracle 11g 实验内容：该实验用学生借书数据库XSBOOK，学生借书数据库相关信息如下：数据库名：XSBOOK 数据文件名：XSBOOK日志文件名：XSBOOK _Log图书借阅系统的数据库为 XSBOOK，该数据库主要由学生（XS）、图书（BOOK）、借阅（JY）3个表构成，依托3个基本表创建视图和存储过程。表1 学生信息表（XS）表结构字段名类型与宽度是否主码是否允许空值说 明借书证号CHAR(8)姓名VARCHAR2(8)专业VA

7、RCHAR2(20)性别CHAR(2)借书量INT默认值为0表2 学生信息表（XS）样本数据借书证号姓 名专 业 名性 别借 书 量王娟计算机女4李宏计算机男3朱小波计算机男3李小丽英语女2吴涛英语男0表3 图书信息表（BOOK）结构字段名类型与宽度是否主码是否允许空值说明ISBNCHAR(16)书名VARCHAR2(30)作者VARCHAR2(8)出版社VARCHAR2(20)价格FLOAT复本量INT当对书进行插入、删除时，复本量应等于库存量库存量INT表4 图书信息表（BOOK）样本数据ISBN书 名作 者出 版 社价格复本量库存量7-111-06359-7Web站点安全刘宗田机械工业出

8、版社17527-113-04908-7ASP.NET程序设计陈惠贞机械工业出版社551097-115-07715-0ASP & WEB数据库王国荣人民邮电出版社59527-115-10162-0计算机网络教程谢希仁人民邮电出版社2812107-302-03035-9C语言程序设计谭浩强清华大学出版社481067-505-37908-9WEB程序设计吉根林电子工业出版社2587表5 借阅表（JY）结构字段名类型与宽度是否主码是否允许空值说明借书证号CHAR(8)当借一本书时，BOOK的库存量应减1，同时，借书人的借书量应加1；当还一本书时，BOOK的库存量应加1，同时，借书人的借书量应减1ISB

9、NCHAR(16)索书号CHAR(10)借书时间DATE表6 借阅表（JY）样本数据借书证号ISBN索书号借书时间7-111-06359-72011-03-017-111-06359-72011-05-107-115-07715-02011-05-107-505-37908-92011-09-017-111-06359-72011-03-107-113-04908-72011-03-117-302-03035-92011-03-117-302-03035-92011-04-107-302-03035-92011-04-107-115-10162-02011-05-197-115-10162-0

10、2011-04-197-302-03035-92011-04-191、视图的创建和使用（1）把3个基本表联系起来，方便需要3表关联的功能使用。SQL命令如下：CREATE VIEW RBL AS SELECT XS.借书证号, JY.索书号, JY.ISBN, BOOK.书名, BOOK.出版社,BOOK.价格, JY.借书时间 FROM XS INNER JOIN JY ON XS.借书证号=JY.借书证号 INNER JOIN BOOK ON BOOK.ISBN=JY.ISBN（2） 功能测试SELECT * FROM RBL观察3表关联的字段数据正确性。2、存储过程的创建和使用 参数借书

11、证号（in_ReaderID）、ISBN（in_ISBN）、图书ID（in_BookID）、执行信息（out_str）。 实现功能根据存储过程的前3个参数，实现读者图书“借阅”。第4个参数为输出参数，将存储过程的执行情况以字符串形式赋予此参数。 编写思路（1）根据“借书证号”查询XS表是否存在该读者，如果不存在，则将输出参数out_str赋值为“该读者不存在”并返回0，存储过程结束，表示不能借书。（2）根据“ISBN”查询BOOK中是否存在该图书，如果不存在，则将输出参数赋值为“该图书不存在”并返回0，存储过程结束，表示不能借书。（3）根据“借书证号”查询XS表中该读者的借书量。如果借书量=5

12、，则将输出参数赋值为“读者借书量不能大于5”并返回0，存储过程结束，表示不能借书。（4）根据“ISBN”查询BOOK表中该图书的库存量。如果库存量=0，则将输出参数赋值为“图书库存量为0”并返回0，存储过程结束，表示不能借书。（5）查询JY表中该读者是否已经借阅该图书，如果已经借过，则将输出参数赋值为“读者已经借过该书”并返回0，存储过程结束，表示不能借书。（6）查询JY表中该索书号是否已经存在，如果存在则将输出参数赋值为“该索书号已存在”并返回0，存储过程结束，表示不能添加借书记录。（7）使JY表增加一条该读者借书记录；XS表中该读者的借书量加1；BOOK表中该图书（对应ISBN）记录的库存

13、量减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) IS Ct_Reader Number(10); Ct_ISBN Number(10); Ct_

14、MaxReader Number(10); Ct_Store Number(10); Ct_HaveRead Number(10); Ct_BookID Number(10); Error_Numer Number(10); no_result Exception;BEGIN Error_Numer:= 0; out_str:=; SELECT count(*) into Ct_Reader FROM XS WHERE 借书证号=in_ReaderID;IF Ct_Reader 1 THEN BEGIN out_str:= 该读者不存在; Error_Numer:= 1; END; END I

15、F; SELECT count(*) into Ct_ISBN FROM BOOK WHERE ISBN=in_ISBN; IF Ct_ISBN 5 THEN BEGIN out_str:= 读者借书量不能大于5; Error_Numer:= 1; END; END IF; SELECT 库存量 into Ct_Store FROM BOOK WHERE ISBN=in_ISBN; IF Ct_Store =0 THEN BEGIN out_str:= 图书库存量为0; Error_Numer:= 1; END; END IF; SELECT count(*) into Ct_HaveRead

16、 FROM JY WHERE 借书证号=in_ReaderID and ISBN = in_ISBN; IF Ct_HaveRead 0 THen BEGIN out_str:= 读者已经借过该书; Error_Numer:= 1; END; END IF; SELECT count(*) into Ct_BookID FROM JY WHERE 索书号=in_BookID; IF Ct_BookID 0 THEN BEGIN out_str:= 索书号已存在; Error_Numer:= 1; END; END IF; IF Error_Numer = 0 THEN INSERT INTO

17、JY VALUES(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 THEN RAISE no_result; END IF; COMMIT; dbms_output.put_line(out_str); ELSE dbms_output.put_line(out_str); END IF; EXCEPTI

18、ON WHEN no_result THEN DBMS_OUTPUT.PUT_LINE(执行过程中遇到错误!); DBMS_OUTPUT.PUT_LINE(out_str); ROLLBACK; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE|-|SQLERRM);END; 功能测试创建完存储过程Book_Borrow后可以使用PL/SQL语句验证图书借阅是否能够实现：SELECT 借书证号, 借书量 FROM XS WHERE 借书证号= ; /*查询读者的原借书量*/SELECT ISBN, 库存量 FROM BOOK WHERE ISBN=7-

19、115-10162-0 ; /*查询图书的原库存量*/DECLARE out_str char(30);begin Book_Borrow(, 7-115-10162-0, ,out_str);end; /*执行存储过程，实现图书借阅*/SELECT 借书证号, 借书量 FROM XS WHERE 借书证号= ; /*查询号读者的现有借书量*/SELECT ISBN, 库存量 FROM BOOK WHERE ISBN=7-115-10162-0 ; /*查询图书的现有库存量*/实验四 数据库系统的安全性管理实验目的：掌握账户的建立，数据库用户权限的分配与回收实验要求：使用Oracle 11g实

20、现账户的建立，数据库用户权限的分配与回收实验条件：计算机，Oracle 11g实验内容：1、 为阿里巴巴酒店数据库创建以下用户：用户名：张敏，王林，李新，王立，王铭，张天密码：自行设定2、 作为DBA的你，现在要负责给不同的数据库用户授予不同的权限，各用户可以执行的操作如下：（1） 张敏和王林有权查询酒店客户的个人信息。（2） 李新负责更新酒店客户的个人信息。（3） 王立有权查询各种类型的客房号及所属的类型和定价。（4） 王铭负责更新各类型客房的定价。（5） 张天不仅可以查询航空公司的名称和电话信息，他还有权查询机票信息。（6） 李新有权更新雇员的信息。试用GRANT语句实现上述要求。3、 由于工作调动，需要收回张天和李新的权限，试用REVOKE语句实现。