SQL图书管理系统设计课题报告

1

SQL图书管理系统设计课题报告

班级：计网0901班 小组成员：柳录秧 周龙

2010-12-03

目录

1．图书管理系统ER图

2．系统功能设计 3．数据表的创建 4．数据库完整性设计 5. SQL Server数据库对象设计

2

1.图书管理系统设计E-R图

2.系统功能设计 （1）信息录入功能

1）添加新图书信息。当图书馆收藏新图书时，系统向用户提供新图书信息录入功能，由于同一种书可能会有多本，因此，新图书的信息有两类：某一个ISBN类别的图书信息，包括：ISBN书号、图书类别、书名、作者、出版社、出版日期、价格、馆藏数量、可借数量、图书简介；另一个具体到每一本书的信息，包括：ISBN书号、图书书号、是否可借。每一个ISBN书号和同一个ISBN书号的多本书之间是一对多的关系，每一本书的图书书号是唯一的。

2）添加读者信息。用于登记新读者信息。包括：借书证号、姓名、性别、身份证号、职称、可借数量、已借数量、工作部门、联系电话等。

3）借阅信息。用于登记读者的借阅情况信息。包括：借书证号、借阅书号、借出日期、借阅期限、归还日期等信息。归还日期为空值表示该图书未归还。

（2）数据修改和删除功能

3

1）修改和删除图书信息。图书被借出时，系统需要更新图书信息的可借数量，当可借数量为0时，表示该图书都已被借出。当输入的图书信息有错误或需要进行必要更新时，可以修改图书信息；当一种图书所有馆藏图书都已损毁或遗失并且不能重新买到时，该图书信息需要删除。

2）修改和删除读者信息。当读者的自身信息发生变动，如部门间调动或调离本单位，或违反图书馆规定需要限制其可借阅图书数量时，需要修改读者信息。 3）还书处理。读者归还图书时，更新图书借阅信息表中的归还日期，读者信息表中的已借数量及ISBN类别信息表中该图书的可借数量。 （3）查询和统计功能

1）图书查询功能。根据图书的各种已知条件来查询图书的详细信息，如书名、作者、出版社、ISBN书号等支持模糊查询。

2）读者信息查询。输入读者的借书证号、姓名、工作部门等信息，查询读者的基本信息。对查询到的每一个读者，能够显示其未归还的图书编号和书名。 3）查询所有到期未归还的图书信息。要求结果显示图书编号、书名、读者姓名、借书证号码、借出日期等信息。 4）统计指定读者一段时间内的某类图书或所有类别图书借阅次数及借阅总次数。

3.数据表的创建

根据功能要求的说明创建下列数据表： （1）图书ISBN类别信息表

图书ISBN类别信息表包括以下字段：

ISBN书号、图书类别、书名、作者、出版社、出版日期、价格、馆藏数量、可借数量、图书简介。 （2）图书信息表

图书信息表包括以下字段：

ISBN书号、图书书号、是否可借。 （3）读者信息表

读者信息表包括以下字段：

借书证号、姓名、性别、出生年月、身份证号、职称、可借数量、已借数量、工作部门、家庭地址、联系电话。 （4）借阅信息表（图书-读者关系） 借阅信息表包括以下字段：

借书证号、借阅书号、借出日期、借阅期限、归还日期。 （5）数据表结构如下表：

读者信息表 附：红色表主键，蓝色表外键

字段名称 借书证号 姓名 性别 出生年月 身份证号 职称 数据类型 char char char datetime varchar char 字段长度 10 8 2 18 10 是否为空 否 是 是 是 是 是 4

可借数量 已借数量 工作部门 家庭地址 联系电话 int int varchar varchar varchar 100 100 15 否 否 是 是 是 借阅表

字段名称 序号 借书证号 借阅书号 借阅日期 借阅期限 归还日期 字段名称 ISBN书号 图书书号 是否可借 数据类型 int char char datetime int datetime 数据类型 char char char 字段长度 10 20 字段长度 20 20 2 字段长度 20 20 60 10 60 100 是否为空 否 否 否 否 否 否 是否为空 否 否 否 是否为空 否 否 否 否 否 否 否 否 否 是 ISBN信息表 图书表 字段名称 数据类型 char ISNBN书号 图书类别 书名 作者 出版社 出版日期 价格 馆藏数量 可借数量 图书简介 char varchar char varchar datetime money int int varchar 4.数据库完整性设计

设计者应认真分析和思考各个表之间的关系，合理设计和实施数据完整性原则。

1） 给每个表实施主键及外键约束。 2） 设定缺省约束。如性别。 use JDGL

go

alter table dbo.读者 add constraint dz_xbie default '男' for 性别 go

5

3） 设置非空约束如图书信息表中的书名。

use JDGL go

alter table dbo.图书表

add constraint tu_sm --约束名 default not null for 书名

4） 实施CHECK约束。如ISBN类别表中的可借数量小于馆藏数量。

use JDGL go

alter table dbo.ISBN信息表 add constraint du_kj_kc check(可借数量<馆藏数量) print'可借数量要小于馆藏数量' go

alter table dbo.借阅表 --为借阅表里字段归还时间要大于借阅时间建立约束 add constraint jy_jysj_ghsj check(归还时间>借阅时间) go

alter table dbo.借阅表 --为借阅期限建立约束 add constraint jy_jyqx check(借阅期限<=30) go

5） 实施规则。如身份证号码必须为18位。

use JDGL go

alter table dbo.读者 add constraint dz_sfz check(len(身份证号)=18)

5.SQL Server数据库对象设计

1）设计一个存储过程，以图书ISBN编号为输入参数，返回借阅该图书但未归还的读者姓名和借书证号。 Is null =null =’’

create proc ds_ISBN @ISBN char(20) as

select 姓名,dbo.读者.借书证号

from dbo.读者join dbo.借阅表on dbo.借阅表.借书证号=dbo.读者.借书证号 join dbo.图书信息表on dbo.图书信息表.图书书号=dbo.借阅表.借阅书号 join dbo.ISBN信息表on dbo.图书信息表.ISBN书号=dbo.ISBN信息表.ISBN书号 where dbo.图书信息表.ISBN书号=@ISBN and (归还时间is null)

exec ds_ISBN '1001' –查询ISBN编号为1001图书未还图书的读者姓名和借书证号

6

drop proc ds_ISBN –删除此存储过程

以书名为输入参数建立图书信息的模糊查询的存储过程 create proc du_sm @sm varchar(40) as

select dbo.ISBN信息表.*,dbo.图书信息表.是否可借,图书书号

from dbo.ISBN信息表join dbo.图书信息表on dbo.图书信息表.ISBN书号=dbo.ISBN信息表.ISBN书号

where 书名like '%'+@sm+'%' go

exec du_sm 政 --查询政治这本书的模糊查询

2）读者资料查询：设计一个有多个输入参数的存储过程，返回读者的详细信息。设计另一存储过程并以读者借书证号为输入参数，返回该读者未归还的图书名称和图书编号。

以读者姓名和借书证为例作为输入参数返回读者基本信息的存储过程 create proc du_jszh_xm --此存储过程名称 @jszh char(10), --借书证号 @xm char(8) --姓名 as select * from dbo.读者

where 借书证号=@jszh and 姓名=@xm

exec du_jszh_xm '001','张三' --查询此存储过程 以借书证号作为输入参数返回该读者未归还的图书名称和书号 create proc du_jszh @jszh char(10) --借书证号 as

select dbo.读者.借书证号,dbo.图书信息表.图书书号,dbo.ISBN信息表.书名 from dbo.读者join dbo.借阅表on dbo.读者.借书证号=dbo.借阅表.借书证号 join dbo.图书信息表on dbo.借阅表.借阅书号=dbo.图书信息表.图书书号 join dbo.ISBN信息表on dbo.图书信息表.ISBN书号=dbo.ISBN信息表.ISBN书号 where dbo.借阅表.归还时间is null and dbo.读者.借书证号=@jszh; drop proc du_jszh --删除此存储过程

exec du_jszh '001' --查询借书证号为001的读者未归还的图书信息

3）到期图书查询：设计一个视图，返回所有逾期未归还的图书的编号、书名、读者姓名等信息。 Datepart(day,借阅日期，getdate())

create view jybiao as

select dbo.读者.姓名,dbo.图书信息表.图书书号,dbo.ISBN信息表.书名 from dbo.读者join dbo.借阅表on dbo.读者.借书证号=dbo.借阅表.借书证号 join dbo.图书信息表on dbo.借阅表.借阅书号=dbo.图书信息表.图书书号 join dbo.ISBN信息表on dbo.图书信息表.ISBN书号=dbo.ISBN信息表.ISBN书号

7

where(getdate()>(借阅时间+借阅期限))and (归还时间is null) --建立所有逾期图书的视图 select *

from jybiao --查询视图表

4）统计图书借阅次数：设计一个以两个日期作为输入参数的存储过程，计算这一段时间内各类别图书被借阅的次数，返回图书类别、借阅次数的信息。

create proc ISBN_jysj -- @cssj datetime,--初始时间 @jcsj datetime --结束时间 as

select dbo.ISBN信息表.图书类别,count(dbo.ISBN信息表.ISBN书号) as 次数 from dbo.读者join dbo.借阅表on dbo.借阅表.借书证号=dbo.读者.借书证号 join dbo.图书信息表on dbo.图书信息表.图书书号=dbo.借阅表.借阅书号 join dbo.ISBN信息表on dbo.图书信息表.ISBN书号=dbo.ISBN信息表.ISBN书号 where 借阅时间>=@cssj and @jcsj>=借阅时间

group by dbo.ISBN信息表.图书类别,dbo.ISBN信息表.ISBN书号 设以两个输入的时间参数

exec ISBN_jysj '2009.4.3','2011.4.3' --以这两个时间参数为例查询该存储过程

5）加快数据检索速度，用图书编号为图书信息表建立索引。

create index dsxxb_tssh_index on dbo.图书信息表(图书书号)

以图书编号为图书创建索引名为 dsxxb_tssh_index

6）为读者信息表创建一个删除触发器，当一个读者调出本单位时，将此读者的资料从读者信息表中删除。注意实施业务规则：有借阅书的读者不得从读者信息表中删除。

create trigger delete_duzh --读者 on dbo.读者 for delete as

if (select 已借数量from dbo.借阅表inner join deleted on dbo.借阅表.借书证号=deleted.借书证号)>0 begin

print('还有书未还不可删除该条数据') rollback transaction end else

print('记录已删除')

drop trigger delete_duzh –删除读者触发器

7）借阅处理：为借阅信息表设计INSERT触发器，在读者借阅时更改ISBN类别信息表，且可借数量减1，图书信息表是否可借列的值变为“不可借”，读者信息表中该类读者已借阅数加1。

create trigger insert_cr on dbo.借阅表 for insert

8

as

declare @jysh char(20) --借阅书号 declare @jszh char(10) --借书证号 declare @ISBN char(20) --ISBN书号

select @jysh=inserted.借阅书号,@jszh=inserted.借书证号,@ISBN=(select ISBN书号from dbo.图书信息表where dbo.图书信息表. 图书书号=inserted.借阅书号) from inserted,dbo.借阅表

if @jysh not in (select 图书书号from dbo.图书信息表) begin

print ('该图书书号不存在') rollback transaction end

else if @jszh not in (select 借书证号from dbo.读者) begin

print('该借书证号不存在') rollback transaction end

else if @ISBN not in (select ISBN书号from dbo.ISBN信息表) begin

rollback transaction end

update dbo.读者

set 已借数量=已借数量+1,可借数量=可借数量-1

where dbo.读者.借书证号=@jszh

---- update dbo.图书信息表

set dbo.图书信息表.是否可借='否' where dbo.图书信息表.图书书号= @jysh ------

update dbo.ISBN信息表 --更改ISBN信息表中的信息 set 可借数量=可借数量-1 where ISBN书号=@ISBN

8）还书处理：为借阅信息表设计UPDATE触发器，在该表的归还日期列被更改后，将图书信息表的是否可借列的值变为“可借”，读者信息表中已借数量减1及ISBN类别信息表中可借数量加1。

建立一个还书 update 触发器

create trigger update_jyb on dbo.借阅表 after update as

if update(归还时间) begin

update dbo.读者

set 已借数量=已借数量-1,可借数量=可借数量+1 from dbo.读者,dbo.借阅表

9

where dbo.读者.借书证号=dbo.借阅表.借书证号

---- update dbo.图书信息表

set dbo.图书信息表.是否可借='是' from dbo.借阅表,dbo.图书信息表

where dbo.借阅表.借阅书号=dbo.图书信息表.图书书号 ------

update dbo.ISBN信息表 set 可借数量=可借数量+1

from dbo.ISBN信息表,dbo.借阅表,dbo.图书信息表

where dbo.图书信息表.图书书号=dbo.借阅表.借阅书号and dbo.图书信息表.ISBN书号=dbo.ISBN信息表.ISBN书号 end

--drop trigger update_jyb --删除触发器

10

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