数据库原理及应用实验报告 9

实验成绩

《数据库系统原理及应用》

实 验 报 告

九

专业班级: 计算机科学与技术 学 号: 201116910233 姓 名: 范晓曈 指导教师: 苏小玲

2013年 12 月 06 日

1

实验九名称： 数据库系统案例

一、实验内容：

实现一个简单的图书管理系统，完成该系统的数据库设计功能，一个简单的图书管理系统包括图书馆内书籍的信息、学校在校学生的信息以及学生的借阅信息。

此系统功能分为面向学生和面向管理员两部分，其中学生可以进行借阅、续借、归还和查询书籍等操作，管理员可以完成书籍和学生的增加、删除和修改以及学生借阅、续借、归还的确认。设计数据库的概念模型、关系模型并确定约束。

二、实验目的：

系统的掌握基本数据库的开发和管理，熟练T-SQL和SSMS两种方式的使用。

三、实验步骤：

任务1：简单图书管理系统数据库设计 1．系统需求分析

通过对数据库用户的调查，对用户的需求进行分析得知：

(1)该图书管理系统数据库涉及到学生、图书及其两者的借阅关系，管理员管理图书关系，学生注册图书管理系统分析。所以需要建立五张表。分别是学表生stu，图书表book，借阅表sb，管理员表manager,注册表regist。

(2)stu表中的属性有：学号、姓名、性别、院系、班级、出生日期 Book表中的属性有：编号、书名、出版社、作者、价格 Sb表中的属性有：学号、编号、借出日期、归还日期 Manager表中属性有：工作号、管理员姓名、性别、联系方式 Regist表中属性有：学生号、密码、邮箱、联系方式

sb表是通过stu表和book连接起来形成的借阅表

在面向学生的部分只能进行查看自己借阅的图书情况，所有图书信息，归还和续借。

在面向管理员部分要实现书籍和学生的增加、删除、修改、以及 学生借阅、续借、归还的确认。

2

2．概念模型设计

请你根据系统需求分析设计E-R图。

学 号 邮 箱 注册表 密 码 联系方式 注 册 学 号 性 别 姓 名 学 生 班 级 院 系 出生日 期 借出日期 借 阅 归还状态 编 号 归还日期 作 者 名 图 书 书 出版者 价 格 管理 工作号 性 别 管理员 姓 名 联系方式

3

3．关系模型设计

请你跟据E-R图转换成对应的关系模型，并标出主码

学生、图书、以及学生与图书之间的借阅情况可以用下面三个关系模式表示： 学生（学号，姓名，院系，性别，班级，出生日期）主键是“学号”。 图书（编号，书名，作者，出版社，价格）主键是“编号”。

借阅（学号，编号，借出日期，归还日期,归还状态）其中学号和编号是主键，也是引用“学生”和“图书”的外键。

管理员（工作号、姓名、性别、联系方式）主键是“工作号”。

注册（学号、密码、邮箱、联系方式）主键是“学号”，也是引用”学生”的外键。

4．约束关系

(1)实体完整性

Stu表中主键为“学号”，表中不允许存在两个学号一样的学生。 Book表中主键为“编号”，表中不允许存在两个编号一样的图书。

Sb表中主键为“学号”、“编号”，表中不允许不同的学生同时借阅同一本书。 Manager表中主键为“工作号”，表中不允许编号一样的管理员。

Regist表中主键为“学号”，表中可以注册的学生必须是学校的学生，也就是学生表中存在的学生。

(2)参照完整性

Sb表中的“学号”和“编号”的取值范围分别不能超过stu表中的“学号”和图书表中的“编号”范围。Sb表中的主键是引用的stu表和book表的主键，用来表示stu和book之间的关联关系。Regist表中的学生必须存在于学生表中，即regist表中的主键“学号”是引用学生表的外键。

5．关系结构描述

请你参考下图给出你设计的相应数据库的关系结构描述。

4

表1 学生数据库关系结构描述

关系名称 学生 图书 借阅 属性名称 学号 姓名 院系 出生年月 性别 班级 编号 书名 作者 出版社 价格 学号 编号 借出日期 归还日期 归还状态 工作号 姓名 性别 联系方式 学号 密码 邮箱 联系方式 属性名称 学号 姓名 电话 出生年月 性别 课程号 课程名 先修课程号 状态 学号 课程号 成绩 数据类型 字符串 字符串 字符串 日期时间型 字符串 字符串 字符串 字符串 字符串 字符串 浮点数据型 字符串 字符串 日期时间型 日期时间型 整型 字符串 字符串 字符串 字符串 字符串 字符串 字符串 字符串 数据类型 字符串 字符串 字符串 日期时间型 字符串 日期时间型 字符串 字符串 货币型 整数 字符串 字符串 字符串 宽度 10 20 20 20 1 20 10 20 20 10 20 10 10 20 20 2 10 10 10 10 10 10 20 20 宽度 10 8 20 8 15 8 15 6 8 1 10 40 16 说明 主键 不为空 不为空 默认“男” 不为空 主键 不为空 不为空 主键 主键 不为空 不为空 取值为0、1 主键 不为空 默认“男” 不为空 主键 不为空 不为空 不为空 说明 取值为0、1 5

管理员 注册 关系名称 学生 课程 学生选课

11

2．数据查询

在案例中，给出在任务1中进行相应查询的sql命令。

(1)查询所有图书信息。查询语句和查询结果如下图所示。

select * from book

图1 查询(1)的语句和结果

(2) 查询某个学生相应的借阅信息。查询语句和查询结果如下图所示。

select r.id 学号,name 姓名,borrowdate 借出日期 from sb join regist r on sb.id=r.id join stu on r.id=stu.id where r.id='001'

图2查询(2)的语句和结果

(3) 查询某个学生借书数目。查询语句和查询结果如下图所示。

select name 姓名,count(num) 借书数目,dept 系别 from sb join stu on sb.id=stu.id where sb.id='001' group by name,dept

图3查询(3)的语句和结果

12

2．数据查询

在案例中，给出在任务1中进行相应查询的sql命令。

（1）查询所有图书信息。查询语句和查询结果如下图所示。

select * from book

图1 查询(1)的语句和结果

（2）查询某个学生相应的借阅信息。查询语句和查询结果如下图所示。select r.id 学号,name 姓名,borrowdate 借出日期 from sb join regist r on sb.id=r.id join stu on r.id=stu.id where r.id='002'

图2查询(2)的语句和结果

(4) 查询某个学生借书数目。查询语句和查询结果如下图所示。

select name 姓名,count(num) 借书数目,dept 系别 from sb join stu on sb.id=stu.id where sb.id='001' group by name,dept

图3查询(3)的语句和结果

13

任务5：建立简单图书管理系统数据库的触发器和存储过程 1．问题的提出

每个学生借书的数目有一定的限制，不能超过4本。超过4本就输出提示信息“借书数目超过四本，不能再继续借书！”

2．在sb表上创建tri_limit触发器

步骤： (1) 步骤描述

? 创建tri_limit触发器，限制每个学生的借书数目不能超过4本，一旦超过就

不能继续借书，输出提示信息。

? 以学号为006的学生“李景瑞”为例，目前他已经借了4本书，归还状态为

1\\1\\1\\1。

? 再继续插入一行记录，查看触发器工作。

(2) 在编辑窗口中，输入以下Transact-SQL命令：

create trigger tri_limit

on sb after insert as

if(select count(num) from sb where id=(select id from inserted) and state=1)>4 begin

print'借书数目超过四本，不能再继续借书！' rollback End

14

目前006号学生手里有4本书

select *from sb

继续借书，插入一行记录

insert into sb

values('006','A002','2013.12.12','2014.01.03',1)

3．编写相应的存储过程

问题要求：

建立查询指定学生的借书情况，如果该学生没有注册，就返回1，并输出提示信息：“该学生没有注册，没有借书资格！”，如果该学生注册了但是没有借书，就返回2，并输出提示信息：“该学生没有借书！”，如果该学生借书了，就返回他的借书信息，包括（书名，作者，归还状态（0表示已还，1表示未还））。

存储过程设计

入口参数：学生学号

输出参数：提示信息或者查询到的结果集（书名，作者，归还状态（0表示已还，1表示未还）） Transact-SQL程序：

create procedure proc_search

@xuehao char(20) as

if not exists(select * from regist where id=@xuehao)

15

return 1

if not exists(select * from sb where sb.id=@xuehao) return 2 else

select book.name 书名,book.author 作者,sb.state 归还状态1表示未还 from book join sb on book.num=sb.num where sb.id=@xuehao

declare @id int,@ret int exec @ret=proc_search '004'

if @ret =1 print '该学生没有注册，没有借书资格！！' if @ret =2 print '该学生没有借书！'

declare @id int,@ret int exec @ret=proc_search '005'

if @ret =1 print '该学生没有注册，没有借书资格！！' if @ret =2 print '该学生没有借书！'

declare @id int,@ret int exec @ret=proc_search '006'

if @ret =1 print '该学生没有注册，没有借书资格！！' if @ret =2 print '该学生没有借书！'

任务6：简单图书管理系统数据库的完整性控制 1．实体完整性

实体完整性一般在创建表时，通过定义主码完成。若有遗漏应及时修改，使各关系满足实体完整性。

16

2．参照完整性

(1)regist表中的学生学号必须是stu表中存在的，因为只有有这个学生才会有这个学生来注册；

(2) sb表中的学生学号必须是regist表中存在的学号，因为只有注册过的学生在有资格借书，否则没有资格；并且sb中的书必须是book表中存在的书，因为借书是从总书库中借出的。 Transact-SQL程序如下：

create procedure proc_search

@xuehao char(20) as

if not exists(select * from regist where id=@xuehao) return 1

if not exists(select * from sb where sb.id=@xuehao) return 2 else

select book.name 书名,book.author 作者,sb.state 归还状态1表示未还 from book join sb on book.num=sb.num where sb.id=@xuehao

declare @id int,@ret int exec @ret=proc_search '004'

if @ret =1 print '该学生没有注册，没有借书资格！！' if @ret =2 print '该学生没有借书！'

insert into sb

values('001','E001','2013.12.12','2014.01.03',1)

17

3．用户定义的完整性

每个学生一次借出的书不能超过4本，用触发器实现。

USE [图书管理] GO

/****** Object: Trigger [dbo].[tri_limit] Script Date: 12/16/2013 12:23:59 ******/

SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

ALTER trigger [dbo].[tri_limit] on [dbo].[sb] after insert as

if(select count(num) from sb where id=(select id from inserted) and state=1)>4 begin

print '每个学生一次借出的书不能超过4本！' rollback End

insert into sb

values('001','C001','2013.12.12','2014.01.03',1) insert into sb

values('001','A002','2013.12.12','2014.01.03',1) insert into sb

values('001','A003','2013.12.12','2014.01.03',1) insert into sb

values('001','B001','2013.12.12','2014.01.03',1)

任务7：简单图书管理系统数据库的安全性控制

18

(1) 例如：对学生设置权限，不能进行更改，插入图书的操作；但是可以查询。

select * from book

insert into book

values('D002','C++','wai','电子工业出版社出版社','60')

(2)管理员拥有对数据库操作的一切权限。

19

四、实验心得：

设计一个性能良好的数据库系统，明确应用环境对系统的要求是首要的和最基本的。特别是数据应用非常广泛，非常复杂，要是事先没有对信息进行充分和细致的分析，这种设计就很难取得成功。

图书馆在正常运行中总是面对大量的读者信息。书籍信息以及两者相互作用产生的借书信息、还书信息。需要对读者资源、数据资源、借书信息、还书信息进行管理，及时了解各个环节中信息的变更，有利于提高管理效率。

通过这次实验，我更加深入的了解了操纵数据库的知识。并且对数据库里面的存储过程有了比较深入的了解。经过大概一周的奋斗，我完成了本次实验。

这次试验给我最大的印象就是如果自己有了兴趣，就动手去做，困难在你的勇气和毅力下是抬不了头的。从做这个数据库开始无论遇到什么困难，我都没有一丝放弃的念头。出于对知识的渴望，出于对新技术的好奇，出于对一切未知的求知。

20

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