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

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

实验一 数据库的建立

实验目的：掌握数据库的创建、基本表的创建和数据插入的方法

实验要求：使用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） 查询身份证号为“310222196711040064”客户最近入住酒店的日期是什

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

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

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

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

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

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

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

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

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

相应的操作。

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

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

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

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

该实验用学生借书数据库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）样本数据

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

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

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

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

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

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

表5 借阅表（JY）结构

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

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

借书证号 10000001 10000001 10000001 10000001 10000002 10000002 10000002 10000003 10000003 10000003 20000001 20000001

1、视图的创建和使用

（1）把3个基本表联系起来，方便需要3表关联的功能使用。 SQL命令如下：

ISBN 索书号 借书时间 2011-03-01 2011-05-10 2011-05-10 2011-09-01 2011-03-10 2011-03-11 2011-03-11 2011-04-10 2011-04-10 2011-05-19 2011-04-19 2011-04-19 7-111-06359-7 1100000001 7-111-06359-7 1100000002 7-115-07715-0 3100000001 7-505-37908-9 6100000001 7-111-06359-7 1100000003 7-113-04908-7 2100000001 7-302-03035-9 5100000001 7-302-03035-9 5100000002 7-302-03035-9 5100000003 7-115-10162-0 4100000001 7-115-10162-0 4100000002 7-302-03035-9 5100000004 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、存储过程的创建和使用 ? 参数

借书证号（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) IS

Ct_Reader Ct_ISBN

Number(10); Number(10);

Ct_MaxReader Number(10);

Ct_Store Number(10); Ct_HaveRead Number(10); Ct_BookID

Number(10);

Error_Numer Number(10); no_result 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 IF;

SELECT count(*) into Ct_ISBN FROM BOOK WHERE ISBN=in_ISBN; IF Ct_ISBN < 1 THEN BEGIN

out_str:= '该图书不存在'; Error_Numer:= 1; END;

Exception;

END IF;

SELECT 借书量 into Ct_MaxReader FROM XS WHERE 借书证号=in_ReaderID;

IF Ct_MaxReader>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 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 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;

EXCEPTION

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 借书证号= '10000001'; /*查询10000001读者的原借书量*/

SELECT ISBN, 库存量 FROM BOOK WHERE ISBN='7-115-10162-0' ; /*查询图书的原库存量*/

DECLARE out_str char(30); begin

Book_Borrow('10000001', '7-115-10162-0', '5100000007',out_str); end;

/*执行存储过程，实现图书借阅*/

本文来源：https://www.bwwdw.com/article/zhrp.html