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

9SQL图书管理系统设计课题报告班级：计网0901班小组成员：柳录秧 周龙2010-12-03目录 1图书管理系统ER图2系统功能设计3数据表的创建 4数据库完整性设计 5. SQL Server数据库对象设计1.图书管理系统设计E-R图2.系统功能设计（1）信息录入功能1）添加新图书信息。当图书馆收藏新图书时，系统向用户提供新图书信息录入功能，由于同一种书可能会有多本，因此，新图书的信息有两类：某一个ISBN类别的图书信息，包括：ISBN书号、图书类别、书名、作者、出版社、出版日期、价格、馆藏数量、可借数量、图书简介；另一个具体到每一本书的信息，包括：ISBN书号、图书书号、是否可借。每一个ISBN书号和同一个ISBN书号的多本书之间是一对多的关系，每一本书的图书书号是唯一的。2）添加读者信息。用于登记新读者信息。包括：借书证号、姓名、性别、身份证号、职称、可借数量、已借数量、工作部门、联系电话等。3）借阅信息。用于登记读者的借阅情况信息。包括：借书证号、借阅书号、借出日期、借阅期限、归还日期等信息。归还日期为空值表示该图书未归还。（2）数据修改和删除功能1）修改和删除图书信息。图书被借出时，系统需要更新图书信息的可借数量，当可借数量为0时，表示该图书都已被借出。当输入的图书信息有错误或需要进行必要更新时，可以修改图书信息；当一种图书所有馆藏图书都已损毁或遗失并且不能重新买到时，该图书信息需要删除。2）修改和删除读者信息。当读者的自身信息发生变动，如部门间调动或调离本单位，或违反图书馆规定需要限制其可借阅图书数量时，需要修改读者信息。3）还书处理。读者归还图书时，更新图书借阅信息表中的归还日期，读者信息表中的已借数量及ISBN类别信息表中该图书的可借数量。（3）查询和统计功能1）图书查询功能。根据图书的各种已知条件来查询图书的详细信息，如书名、作者、出版社、ISBN书号等支持模糊查询。2）读者信息查询。输入读者的借书证号、姓名、工作部门等信息，查询读者的基本信息。对查询到的每一个读者，能够显示其未归还的图书编号和书名。3）查询所有到期未归还的图书信息。要求结果显示图书编号、书名、读者姓名、借书证号码、借出日期等信息。4）统计指定读者一段时间内的某类图书或所有类别图书借阅次数及借阅总次数。3.数据表的创建根据功能要求的说明创建下列数据表：（1）图书ISBN类别信息表图书ISBN类别信息表包括以下字段：ISBN书号、图书类别、书名、作者、出版社、出版日期、价格、馆藏数量、可借数量、图书简介。（2）图书信息表图书信息表包括以下字段：ISBN书号、图书书号、是否可借。（3）读者信息表读者信息表包括以下字段：借书证号、姓名、性别、出生年月、身份证号、职称、可借数量、已借数量、工作部门、家庭地址、联系电话。（4）借阅信息表（图书-读者关系）借阅信息表包括以下字段：借书证号、借阅书号、借出日期、借阅期限、归还日期。（5）数据表结构如下表：读者信息表 附：红色表主键，蓝色表外键字段名称数据类型字段长度是否为空借书证号varchar10否姓名varchar8否性别char2否出生年月datetime否身份证号varchar18否职称char10否可借数量int否已借数量int否工作部门varchar100是家庭地址varchar100是 联系电话varchar15是 借阅表字段名称数据类型字段长度是否为空序号int否借书证号char10否借阅书号char20否借阅日期datetime 否借阅期限int否归还日期datetime否 ISBN信息表字段名称数据类型字段长度是否为空ISBN书号char20否图书书号char20否是否可借char2否 图书表字段名称数据类型字段长度是否为空ISNBN书号char20否图书类别char20否书名varchar60否作者char10否出版社varchar60否出版日期datetime否价格money否馆藏数量int否可借数量int否图书简介varchar100是4.数据库完整性设计设计者应认真分析和思考各个表之间的关系，合理设计和实施数据完整性原则。1） 给每个表实施主键及外键约束。2） 设定缺省约束。如性别。 use JDGLgoalter table dbo.读者add constraint dz_xbiedefault 男 for 性别go 3） 设置非空约束如图书信息表中的书名。use JDGLgoalter table dbo.图书表add constraint tu_sm -约束名default not null for 书名4） 实施CHECK约束。如ISBN类别表中的可借数量小于馆藏数量。use JDGLgoalter table dbo.ISBN信息表add constraint du_kj_kccheck(可借数量借阅时间)goalter table dbo.借阅表 -为借阅期限建立约束add constraint jy_jyqxcheck(借阅期限(借阅时间+借阅期限)and (归还时间is null)-建立所有逾期图书的视图select *from jybiao -查询视图表4）统计图书借阅次数：设计一个以两个日期作为输入参数的存储过程，计算这一段时间内各类别图书被借阅的次数，返回图书类别、借阅次数的信息。create proc ISBN_jysj -cssj datetime,-初始时间jcsj datetime -结束时间asselect 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_index6）为读者信息表创建一个删除触发器，当一个读者调出本单位时，将此读者的资料从读者信息表中删除。注意实施业务规则：有借阅书的读者不得从读者信息表中删除。create trigger delete_duzh -读者on dbo.读者for deleteasif (select 已借数量from dbo.借阅表inner join deletedon dbo.借阅表.借书证号=deleted.借书证号)0beginprint(还有书未还不可删除该条数据)rollback transactionendelseprint(记录已删除)drop trigger delete_duzh 删除读者触发器7）借阅处理：为借阅信息表设计INSERT触发器，在读者借阅时更改ISBN类别信息表，且可借数量减1，图书信息表是否可借列的值变为“不可借”，读者信息表中该类读者已借阅数加1。create trigger insert_cr on dbo.借阅表for insertasdeclare 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.图书信息表)beginprint (该图书书号不存在)rollback transaction endelse if jszh not in (select 借书证号from dbo.读者)beginprint(该借书证号不存在)rollback transaction endelse if ISBN not in (select ISBN书号from dbo.ISBN信息表)beginrollback transaction endupdate dbo.读者set 已借数量=已借数量+1,可借数量=可借数量-1where dbo.读者.借书证号=jszh - update dbo.图书信息表set dbo.图书信息表.是否可借=否where dbo.图书信息表.图书书号= jysh-update dbo.ISBN信息表 -更改ISBN信息表中的信息set 可借数量=可借数量-1where ISBN书号=ISBN 8）还书处理：为借阅信息表设计UPDATE触发器，在该表的归还日期列被更改后，将图书信息表的是否可借列的值变为“可借”，读者信息表中已借数量减1及ISBN类别信息表中可借数量加1。建立一个还书 update 触发器create trigger update_jyb on dbo.借阅表after updateasif update(归还时间)beginupdate dbo.读者set 已借数量=已借数量-1,可借数量=可借数量+1from dbo.读者,dbo.借阅表where dbo.读者.借书证号=dbo.借阅表.借书证号 - update dbo.图书信息表set dbo.图书信息表.是否可借=是from dbo.借阅表