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

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

《《数据库原理及应用》实验指导书.docx》由会员分享，可在线阅读，更多相关《《数据库原理及应用》实验指导书.docx（14页珍藏版）》请在冰豆网上搜索。

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

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

实验一数据库的建立

实验目的：

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

实验要求：

使用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/2007”，试将该雇员的信息插入相应的表。

（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）样本数据

借书证号

姓名

专业名

性别

借书量

王娟

计算机

女

4

李宏

计算机

男

3

朱小波

计算机

男

3

李小丽

英语

女

2

吴涛

英语

男

0

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

字段名

类型与宽度

是否主码

是否允许空值

说明

ISBN

CHAR（16）

√

×

书名

VARCHAR2（30）

×

×

作者

VARCHAR2（8）

×

×

出版社

VARCHAR2（20）

×

×

价格

FLOAT

×

×

复本量

INT

×

×

当对书进行插入、删除时，复本量应等于库存量

库存量

INT

×

×

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

ISBN

书名

作者

出版社

价格

复本量

库存量

7-111-06359-7

Web站点安全

刘宗田

机械工业出版社

17

5

2

7-113-04908-7

ASP.NET程序设计

陈惠贞

机械工业出版社

55

10

9

7-115-07715-0

ASP&WEB数据库

王国荣

人民邮电出版社

59

5

2

7-115-10162-0

计算机网络教程

谢希仁

人民邮电出版社

28

12

10

7-302-03035-9

C语言程序设计

谭浩强

清华大学出版社

48

10

6

7-505-37908-9

WEB程序设计

吉根林

电子工业出版社

25

8

7

表5借阅表（JY）结构

字段名

类型与宽度

是否主码

是否允许空值

说明

借书证号

CHAR（8）

×

×

当借一本书时，BOOK的库存量应减1，同时，借书人的借书量应加1；当还一本书时，BOOK的库存量应加1，同时，借书人的借书量应减1

ISBN

CHAR（16）

×

×

索书号

CHAR（10）

√

×

借书时间

DATE

×

×

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

借书证号

ISBN

索书号

借书时间

7-111-06359-7

2011-03-01

7-111-06359-7

2011-05-10

7-115-07715-0

2011-05-10

7-505-37908-9

2011-09-01

7-111-06359-7

2011-03-10

7-113-04908-7

2011-03-11

7-302-03035-9

2011-03-11

7-302-03035-9

2011-04-10

7-302-03035-9

2011-04-10

7-115-10162-0

2011-05-19

7-115-10162-0

2011-04-19

7-302-03035-9

2011-04-19

1、视图的创建和使用

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

SQL命令如下：

CREATEVIEWRBL

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_ReaderNumber（10）;

Ct_ISBNNumber（10）;

Ct_MaxReaderNumber（10）;

Ct_StoreNumber（10）;

Ct_HaveReadNumber（10）;

Ct_BookIDNumber（10）;

Error_NumerNumber（10）;

no_resultException;

BEGIN

Error_Numer:

=0;

out_str:

='';

SELECTcount（*）intoCt_ReaderFROMXSWHERE借书证号=in_ReaderID;

IFCt_Reader<1THEN

BEGIN

out_str:

='该读者不存在';

Error_Numer:

=1;

END;

ENDIF;

SELECTcount（*）intoCt_ISBNFROMBOOKWHEREISBN=in_ISBN;

IFCt_ISBN<1THEN

BEGIN

out_str:

='该图书不存在';

Error_Numer:

=1;

END;

ENDIF;

SELECT借书量intoCt_MaxReaderFROMXSWHERE借书证号=in_ReaderID;

IFCt_MaxReader>5THEN

BEGIN

out_str:

='读者借书量不能大于5';

Error_Numer:

=1;

END;

ENDIF;

SELECT库存量intoCt_StoreFROMBOOKWHEREISBN=in_ISBN;

IFCt_Store=0THEN

BEGIN

out_str:

='图书库存量为0';

Error_Numer:

=1;

END;

ENDIF;

SELECTcount（*）intoCt_HaveReadFROMJYWHERE借书证号=in_ReaderIDandISBN=in_ISBN;

IFCt_HaveRead>0THen

BEGIN

out_str:

='读者已经借过该书';

Error_Numer:

=1;

END;

ENDIF;

SELECTcount（*）intoCt_BookIDFROMJYWHERE索书号=in_BookID;

IFCt_BookID>0THEN

BEGIN

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%NOTFOUNDTHEN

RAISEno_result;

ENDIF;

COMMIT;

dbms_output.put_line（out_str）;

ELSE

dbms_output.put_line（out_str）;

ENDIF;

EXCEPTION

WHENno_resultTHEN

DBMS_OUTPUT.PUT_LINE（'执行过程中遇到错误!

'）;

DBMS_OUTPUT.PUT_LINE（out_str）;

ROLLBACK;

WHENOTHERSTHEN

DBMS_OUTPUT.PUT_LINE（SQLCODE||'---'||SQLERRM）;

END;

Ø功能测试

创建完存储过程Book_Borrow后可以使用PL/SQL语句验证图书借阅是否能够实现：

SELECT借书证号,借书量FROMXSWHERE借书证号='';

/*查询读者的原借书量*/

SELECTISBN,库存量FROMBOOKWHEREISBN='7-115-10162-0';

/*查询图书的原库存量*/

DECLAREout_strchar（30）;

begin

Book_Borrow（'','7-115-10162-0','',out_str）;

end;

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

SELECT借书证号,借书量FROMXSWHERE借书证号='';

/*查询号读者的现有借书量*/

SELECTISBN,库存量FROMBOOKWHEREISBN='7-115-10162-0';

/*查询图书的现有库存量*/

实验四数据库系统的安全性管理

实验目的：

掌握账户的建立，数据库用户权限的分配与回收

实验要求：

使用Oracle11g实现账户的建立，数据库用户权限的分配与回收

实验条件：

计算机，Oracle11g

实验内容：

1、为阿里巴巴酒店数据库创建以下用户：

用户名：

张敏，王林，李新，王立，王铭，张天

密码：

自行设定

2、作为DBA的你，现在要负责给不同的数据库用户授予不同的权限，各用户可以执行的操作如下：

（1）张敏和王林有权查询酒店客户的个人信息。

（2）李新负责更新酒店客户的个人信息。

（3）王立有权查询各种类型的客房号及所属的类型和定价。

（4）王铭负责更新各类型客房的定价。

（5）张天不仅可以查询航空公司的名称和电话信息，他还有权查询机票信息。

（6）李新有权更新雇员的信息。

试用GRANT语句实现上述要求。

3、由于工作调动，需要收回张天和李新的权限，试用REVOKE语句实现。