数据库课程设计-书刊租借信息管理系统.doc

数据库系统原理课程设计
课题名称： 书刊租借信息管理系统
姓 名:
班 级：计算机1102
学 号： 311109010225
指导老师:
2013年 9 月 10 日
目录
一、系统需求分析 。。。。.。。..。。。.。。.。。。.。...。.。..。.。。。。。. 1
1.1 背景 ..。.。。。。.。。。。。...。。。。。......。。..。...。。。.。。..。.。。。。 1
1.2 系统主要功能 。。.。.。.。...。。。。.。。。。...。。。.。。。.。.。.。。。.。。。 1
1。3 数据流程图（DFD）.。.。.。。.。.。..。.。。。.。..。.。。。。。。。。。..。.. 2
二、概念结构设计 。.。。.。。。....。.....。..。..。.。。。。。。.。。。。 3
2。1 书刊信息E—R图 。。。。..。。。。.....。。。。..。。。.。...。.。。。。。。。。 3
2。2 会员信息E-R图 .。..。。.。..。。.。。。。.。....。。.。.。.。。。.。。.。。 3
2.3 管理员信息E-R图 。。。.。。。.。。。。..。.。。。.。..。。。。。.。.。。..。。 3
2。4 书刊租借信息管理系统总E—R图 .。。。.。。..。.。。。。。。.。.。。。.。 4
三、逻辑结构设计 。..。。。。。。。。。.。。。。。。。.。.。。。...。。.。。。。。 6
3。1 关系模式 。.。..。.。。。.。.。.。.。。.。。。。。。。。。.。。。。。.。.。..。。.. 6
四、表结构定义 ..。。。。..。。。。。。。。。。。。。。..。.。。.。。。。。。.。.。 6
五、数据实施和维护 。.。。。...。..。。。。.。。.。..。。..。。。。。。..。 8
5.1 创建数据库 .。。。.。。。.。..。。。。。。。。。。..。。。.。.。。.。。.。。。.。.。 8
5。2 数据初始化 .。。。。..。.。。.。。.。...。。..。..。。。。.。。..。..。.。。。 11
5.3 单表查询 。。.。。。。。。.。。。。。。。。。。.。.。。.。。.。.。。。。。。。.。.。。。。 15
5.4 数据查询,更新及触发器，存储过程的创建 .。..。..。。。.。。。。 17
六、设计小结 .。。。。。..。。.。。。。。...。。。。..。。。。。..。..。。。。。 26
七、参考文献 。。..。。。。。....。.。。。...。。。。.。。。。。。.。.。。..。 27
一、 系统需求分析
1。1背景
书刊租借工作繁琐,借阅频繁，包含大量的信息数据，面对着如此庞大的信息量，传统的人工方式管理会导致书刊租借管理上的混乱，人力与物力过多浪费，因此，就需要一个合理、有效，规范和实用的书刊租借信息管理系统来实现对这些数据的有效管理。提高书刊租借管理工作效率，作到信息的规范管理，科学统计和快速查询，更方便于顾客的租借。
1。2系统主要功能
1。 会员基本信息的输入，包括会员证号、姓名、性别，年龄，电话，地址，已借书数等.
2．会员基本信息的查询、修改，包括会员证号、姓名、性别，年龄，电话，地址，已借书数等。
3．书刊库存信息的输入，包括书刊编号，书刊名称,作者,出版社，出版日期，类别，年份，库存量,借出量。
4．书刊库存信息的查询，修改，包括书刊编号，书刊名称，作者，出版社,出版日期，类别，年份，库存量，借出量。
5．借书信息的输入,包括会员证号,书刊编号,借书日期，借书天数，租借费用。
6．借书信息的查询、修改，包括会员证号，书刊编号，借书日期，借书天数，租借费用。
7．还书信息的输入，包括会员证号,书刊编号,还书日期.
8．还书信息的查询和修改,包括还书会员证号、会员姓名、书刊编号、书刊名称、借书日期、还书日期等。
9．超期还书罚款输入,还书超出期限包括超出期限还书的会员证号，管理员编号，书刊编号，借书日期，超期罚款。
10．超期还书罚款查询,删除，包括会员证号、会员姓名、书刊编号、书刊名称,超期罚款等。
1。3数据流程图（DFD）
管理员登记
会员信息管理
书刊登记
会员登记
书刊信息管理
管理员信息管理
数据库
数据库
数据库
数据库
数据库
借阅信息管理
基本信息录入
基本信息录入
书刊信息返回
借阅信息录入
超期罚款
会员信息返回

基本信息录入
归还信息录入
数据库

二、 概念结构设计
2.1书刊信息E—R图：
书刊信息
书刊编号
书刊名称
出版日期
库存量
借出量
出版社
类别
作者

2。2会员信息E-R图:
会员信息
会员证号
已借书数
年龄
最大借书数
账户余额
性别
地址
姓名
电话

2。3管理员信息E—R图：
管理员
管理员编号
姓名
电话
地址

会员信息
租借
书刊信息
M
N
书刊编号
会员证号
租借费用
借书天数
借书日期shijian
归还时间shijian

2.4书刊租借信息管理系统总E—R图:
会员信息
会员证号
已借书数
年龄
最大借书数
账户余额
性别
地址
姓名
电话
书刊信息
书刊编号
书刊名称
出版日期
库存量
借出量
出版社
类别
作者
租借
租借费用
借书天数
借书日期shijian
书刊编号
会员证号
管理员
管理员编号
姓名
电话
地址
管理
罚款
M
N
1
N
M
N
归还日期

三、逻辑结构设计
3。1关系模式:
通过E—R模型到关系模型的转化,可以得到如下关系模式：
会员信息（会员证号,姓名,性别，年龄,电话，地址，最大借书数，已借书数，账户余额)
管理员(管理员编号，姓名，地址，电话)
书刊信息（书刊编号,书刊名称，作者，出版社,出版日期,类别,库存量，借出量)
书刊借阅（会员证号,书刊编号，借书日期，借书天数，租借费用，）
书刊归还（会员证号，书刊编号，还书日期）
罚款（会员证号,管理员编号,借书日期,超期罚款）
四、 表结构定义
userinf管理员信息表
列名
数据类型
可否为空
说明
userid
char（7）
NOT NULL（主键）
管理员编号
username
char（20)
NOT NULL
姓名
useradress
char（30)
NULL
地址
usertel
char(12）
NOT NULL
电话
borrow_record书刊借阅表
列名
数据类型
可否为空
说明
memberid
char(7）
NOT NULL
会员证号
bookid
char（7）
NOT NULL
书刊编号
borrowdate
DateTime
NOT NULL
借书日期
borrownum
int
NOT NULL
借书天数
bprice
float
NOT NULL
租借费用
system_books书刊信息表
列名
数据类型
可否为空
说明
bookid
char（7）
NOT NULL
书刊编号
bookname
varchar(40)
NOT NULL
书刊名称
bookstyle
char(10）
NOT NULL
类别
bookpubdate
Datetime
NULL
出版日期
bookpub
char(50）
NULL
出版社
bookauthor
char（20）
NOT NULL
作者
stockcount
int
NOT NULL
库存量
borrowc
char（2）
NOT NULL
借出量
memberinf会员信息表
列名
数据类型
可否为空
说明
membername
char（20）
NOT NULL
会员姓名
memberid
char(7）
NOT NULL
会员证号
membertel
char(12)
NOT NULL
联系电话
memberadress
varchar(30）
NOT NULL
地址
membersex
char(7）
NULL
性别
memberage
int
NULL
年龄
maxnum
int
NOT NULL
最大借书数
bbcount
int
NOT NULL
已借书数
balance
float
NOT NULL
账户余额
return_record书刊归还表
列名
数据类型
可否为空
说明
memberid
char（7）
NOT NULL
会员证号
bookid
char（7)
NOT NULL
书刊编号
returndate
DateTime
NOT NULL
还书日期
member_fee罚款记录表
列名
数据类型
可否为空
说明
memberid
char（7）
NOT NULL
会员证号
bookid
char（7)
NOT NULL
书刊编号
borrowdate
DateTime
NOT NULL
借书日期
userid
char（7）
NULL
管理员编号
bookfee
float
NOT NULL
罚款金额
五、 数据实施和维护
5。1数据库建立：
5。1.1创建数据库
CREATEDATABASE bookstoresystem
ON
（NAME= bookstoresystem,
FILENAME='d：\bookstoresystem。mdf’,
SIZE= 10，
MAXSIZE= 50,
FILEGROWTH= 5 ）
LOGON
(NAME=’bookstore’，
FILENAME=’d:\bookstoresystem。ldf’，
SIZE= 5MB，
MAXSIZE= 25MB，
FILEGROWTH= 5MB )
GO
5。1。2创建基本表
1.管理员信息表
createtable userinf
(useridchar(7）NOTNULLprimarykey,
username char（20)NOTNULL，
useradress char（30)NULL，
usertelchar（12）NOTNULL
)
2。创建书刊信息表
createtable system_books
(
bookid char(7）NOTNULLprimarykey,
bookname varchar(40)NOTNULL，
bookstyle char(10）NOTNULL，
bookpubdateDatetimeNULL,
bookpubchar(50）NULL,
bookauthor char(20)NOTNULL，
stockcount intNOTNULL，
borrowcchar(2)NOTNULL
)
3.创建会员信息表
createtable memberinf
（
memberid char(7）NOTNULLprimarykey,
membername char（20)NOTNULL,
membertel char（12)NOTNULL,
memberadress varchar（30)NOTNULL，
membersex char（7）NULL，
memberage intNULL,
maxnum intNOTNULL,
bbcount intNOTNULL,
balancefloatNOTNULL
）
4。创建书刊借阅表
createtable borrow_record
（
bookid char（7)NOTNULLprimarykey,
memberid char（7）NOTNULL，
borrowdate datetimeNOTNULL，
borrownumintNOTNULL，
bprice floatNOTNULL,
foreignkey(bookid）references system_books(bookid)，
foreignkey(memberid）references memberinf（memberid)，
）
5.创建书刊归还表
createtable return_record
(
bookid char（7）primarykey,
memberid char（7)，
returndate datetime,
foreignkey(bookid）references system_books（bookid），
foreignkey（memberid)references memberinf（memberid)，
）
6。创建罚款单表
createtable member_fee
（
memberid char(7）NOTNULL，
bookid char（7)NOTNULLprimarykey，
borrowdate DateTimeNOTNULL,
userid char（7）NULL,
bookfeefloatNOTNULL
)
5。2数据初始化：
1.将管理员信息加入userinf
insertinto userinf
values（’2013003’,’李华’,’商丘夏邑骆集乡’，'6223736’）；
insertinto userinf
values（’2013006’，’言丽丽'，’商丘夏邑康复路’，'6222766'）；
2。将已有的书刊加入system_books
insertinto system_books
(bookid，bookname，bookstyle，bookpubdate，bookpub,bookauthor，stockcount,borrowc）
values(’2013013',’计算机组成原理’,'工程技术类','2001—01—03’，'清华大学出版社’，'王爱英’，’8’，'3’）
insertinto system_books
(bookid，bookname,bookstyle，bookpubdate，bookpub，bookauthor,stockcount,borrowc)
values（’2013086’，'数据库原理’，’工程技术类’，’2007-07—02'，’高等教育出版社’，'萨师煊’，’10','4’)
insertinto system_books
（bookid,bookname,bookstyle,bookpubdate，bookpub，bookauthor，stockcount，borrowc)
values（'2013016','计算机组成原理',’工程技术类’,'2001-01—03','清华大学出版社’，’王爱英'，’8'，'3’)
insertinto system_books
（bookid，bookname,bookstyle，bookpubdate，bookpub,bookauthor，stockcount，borrowc)
values(’2013081','C程序设计’，’工程技术类’，'2002—04—02’，'清华大学出版社’,'谭浩强’，'12'，'6’）
insertinto system_books
(bookid，bookname,bookstyle，bookpubdate，bookpub，bookauthor，stockcount，borrowc）
values(’2013034'，’数据结构(C语言版）'，’工程技术类'，'2002—06-28',’清华大学出版社',’吴伟民，严蔚敏'，’6’,’2’)
insertinto system_books
（bookid，bookname，bookstyle，bookpubdate,bookpub，bookauthor,stockcount，borrowc)
values（’5455515'，'中华历史年','人文艺术类'，'2005-04—03'，’北京大学出版社'，’吴强’，'6’,'1’）;
insertinto system_books
（bookid,bookname，bookstyle，bookpubdate，bookpub,bookauthor,stockcount，borrowc）
values（’1546568’,’微观经济学’，'政治经济类’，'2000-10—03’,'北京大学出版社’,’李小刚’，’12’，’2')
insertinto system_books
（bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor，stockcount,borrowc）
values（’0151153’，'古代埃及','社会科学类'，’2001—02-02’，'北京大学出版社'，’赵文华’,’4’,’1'）
insertinto system_books
（bookid,bookname，bookstyle，bookpubdate,bookpub，bookauthor，stockcount，borrowc)
values(’1514514'，’日本文化'，’人文艺术类’，'2002-04—02'，'北京大学出版社’，’吴小鹏’,'7’，'0’）
insertinto system_books
(bookid，bookname，bookstyle，bookpubdate,bookpub,bookauthor，stockcount,borrowc）
values（'5658’，'影视文学’,'图片艺术类'，’1999—02—28’,’北京大学出版社’,’苏庆东’,'5’,'2')
insertinto system_books
（bookid,bookname,bookstyle,bookpubdate，bookpub，bookauthor，stockcount，borrowc）
values(’5658020’，'探索宇宙奥秘’,'自然科学类’，’1999—02—28’，’北京大学出版社’，'苏庆东’,’10’，'3’）
3。将已有的会员加入memberinf
insertinto memberinf
(membername，memberid，membertel，memberadress,membersex,memberage,maxnum，bbcount，balance）
values（’陈远鹏’,'07','6213048’，'商丘夏邑’,'男’,'21’，’5’，'2’，'32’)
insertinto memberinf
（membername,memberid，membertel,memberadress，membersex,memberage，maxnum，bbcount，balance）
values（’李德’，'17’，'6234048'，’郑州'，’男'，'31'，’7’，'3’，’62’)
insertinto memberinf
(membername，memberid,membertel，memberadress，membersex，memberage,maxnum,bbcount，balance）
values（’赵铭静’,'02’，'6223078’，’商丘’，’女'，’18'，’4’,'4','30’）
insertinto memberinf
(membername,memberid，membertel，memberadress,membersex,memberage，maxnum，bbcount,balance)
values（'李丽’，'37'，'6213565','开封’,’女’，’26'，’8’,'3’,’80’）
insertinto memberinf
（membername,memberid,membertel，memberadress，membersex,memberage,maxnum,bbcount，balance）
values('蒋伟’,’13',’6233348’,’周口’，’男’，’24'，'6’，’2’,’18’）
insertinto memberinf
（membername，memberid，membertel，memberadress，membersex,memberage,maxnum，bbcount，balance）
values('赵鑫’,’16’,’6218888’，’焦作’，’女’，'25’,’6','2'，’60’）
4。添加借阅信息到borrow_record表
insertinto borrow_record
(memberid，bookid，borrowdate，borrownum，bprice）
values（’17'，’2013013’,'2013—07-20’，'30','5’)
insertinto borrow_record
（memberid，bookid，borrowdate,borrownum，bprice)
values（'02’,'2013081’，’2013—08—03'，’30',’3'）
insertinto borrow_record
（memberid，bookid，borrowdate,borrownum，bprice）
values（’13’,'1546568’,’2013-09—02’，’30',’4’）
insertinto borrow_record
（memberid,bookid,borrowdate，borrownum,bprice)
values（’16’，'2013034’,’2013—08—16’，’30’,'7’）
insertinto borrow_record
（memberid，bookid，borrowdate，borrownum,bprice）
values(’17’，’0151153',’2013-07—28’，’30’,’2’)
insertinto borrow_record
(memberid，bookid，borrowdate，borrownum，bprice)
values(’37’，’2013086'，'2013—09—02’,’30’，'5’）
5添加归还信息到return_record表
5。3单表查询：
5。3。1简单查询
1。表userinf中查询
select＊from userinf
2.表ststem_books中查询
select*from system_books
3。表memberinf中查询
select＊from memberinf
4表borrow_record和return_record中查询
select＊from borrow_record
select＊from return_record
5.查询已借数量大于等于3会员的所有信息
select*
from memberinf
where bbcount>=3
6。查询借书会员的已借书天数
select borrow_record.memberid 会员证号，membername 会员姓名,bookname 书刊名称，borrowdate 借书日期,
datediff（day,convert(smalldatetime，borrowdate）,getdate（）) 已借天数
from borrow_record，memberinf，system_books
where borrow_record。bookid=system_books.bookid and borrow_record.memberid=memberinf。memberid
7.查询未被借出的书本
select＊
from system_books
where bookid notin（select bookid from borrow_record)
5。4数据查询，更新及触发器,存储过程的创建：5.4。1创建触发器
1.在return_record表中建立一个插入触发器（returnbook），功能：当还书操作时,要在还书记录中添加一条记录,同时删除相应书本的借阅记录。
SQL 语言：
createtrigger returnbook
on return_record
after insert
as
delete
from borrow_record
where borrow_record.bookid=(select borrow_record.bookid from borrow_record,inserted where borrow_record。bookid=inserted。bookid)
2. 在return_record表中再建立一个插入触发器（returnbook2），功能：当还书操作时,同时对相应表system_books， memberinf做相应的改动，system_books的属性：库存量加1，借出量减1，memberinf的属性：已借书数减1。
SQL 语言：
createtrigger returnbook2
on return_record
after insert
as
update system_books
set stockcount=stockcount+1,borrowc=borrowc—1
where system_books。bookid in(select system_books。bookid
from system_books，inserted where system_books.bookid=inserted.bookid)and system_books。borrowc>0
update memberinf
set bbcount=bbcount—1
where memberinf。memberid in（select memberinf。memberid
from memberinf,inserted where memberinf。memberid=inserted。memberid)and memberinf.bbcount>0
假设归还书刊编号为‘2013086’代码
insertinto return_record
values（’2013086’,’37’，’2013—9—6')
执行前system_books表:
执行后system_books表：
执行前memberinf表：
执行后memberinf表：
3。在borrow_record表中建立一个插入触发器（borrowbook)，当借书操作时，要改变memberinf表的部分属性，已借书数加 1,账户余额减去相应的租借费用。
SQL 语言：
createtrigger borrowbook
on borrow_record
after insert
as
update memberinf
set bbcount=bbcount—1,balance=balance—（select borrow_record.bprice from borrow_record，inserted where inserted。bookid=borrow_record.bookid）
where memberinf。memberid in(select memberinf.memberid from memberinf,inserted where memberinf.memberid=inserted 。memberid）
假设借书刊编号为‘5658’代码
insertinto borrow_record
values('5658’，’02'，’2013—09—01’,'30’,’5’）
执行代码前memberinf表：
执行代码后memberinf表:
4。在borrow_record表中建立一个插入触发器（borrowbook2），当借书操作时，要改变system_books表的部分属性,库存量减 1,借出量加 1。
SQL 语言：
createtrigger borrowbook2
on borrow_record
after insert
as
update system_books
set stockcount=stockcount—1，borrowc=borrowc+1
where system_books。bookid in
(select system_books.bookid from system_books，inserted where system_books。bookid=inserted 。bookid)
成功创建截图
5.4.2创建存储过程：
1。创建一个按照书刊类型查找该类型书刊的所有信息的存储过程
SQL 语言：
createprocedure bstyle
＠类别char（10）
as
select＊
from system_books
where bookstyle=＠类别
执行存储过程查询工程技术类书刊信息
2.创建一个当会员属性已借书数等于最大借书数时，提示不能进行借书的存储过程
SQL 语言
createprocedure memer
@会员证号char（7），
＠会员姓名char(20)
as
if（（select bbcount from memberinf where memberid=＠会员证号）=(select maxnum from memberinf where memberid=＠会员证号))
print ＠会员姓名+’已借书数等于最大借书数，不能再借书’
else
print ＠会员姓名+’可以继续借书’
执行存储过程分别查询赵铭静，与陈远鹏是否可以借书
memberinf表对比图：
3．创建一个存储过程根据会员证号，查询出会员借书信息
SQL 语句
createprocedure selectinf
＠会员证号char(7）
as
select memberinf.memberid 会员证号，membername 会员姓名， bbcount 已借书数， balance 账户余额,system_books。bookid 书刊编号，
bookname 书刊名称，borrowdate 借书日期，borrownum 借书天数，bprice 租借费用
from borrow_record，system_books,memberinf
where borrow_record。bookid=system_books.bookid and borrow_record。memberid=memberinf。memberid and memberinf。memberid=＠会员证号
查找会员证号为02，37，13借书信息
exec selectinf '02'
exec selectinf ’37'
exec selectinf '13’
参与对照表borrow_record表：
所创建的触发器，存储过程列表图：
5.4.3超期处理：
1。对已借书的会员进行查询借书是否超期（为了方便借书天数全为30）
说明：当前的日期为2013年9月13日计算
SQL 语言：
select memberinf.memberid 会员证号，memberinf。membername 会员姓名， system_books.bookid 书刊编号,
bookname 书刊名称,borrowdate 借书日期，
datediff(day,convert(smalldatetime,borrowdate）,getdate()）-30 超出天数
from borrow_record，memberinf,system_books
where borrow_record.bookid=system_books。bookid and borrow_record。memberid=memberinf.memberid
anddatediff(day,convert（smalldatetime,borrowdate）,getdate（））〉=30
运行结果图：
对照表borrow_record表的截图：
2.对超过天数的读者进行罚款,将信息插入到member_fee表
超出一天按0.2元扣除计算：
insertinto member_fee（memberid，bookid，borrowdate，bookfee）
select borrow_record。memberid ，borrow_record。bookid，borrowdate，
0。2＊（Datediff（day,convert（smalldatetime,borrowdate)，getdate（））—30）
from borrow_record
whereDatediff（day，convert（smalldatetime,borrowdate)，getdate（)）>=30
查询出超期罚款人员的相应信息：
SQL 语言:
select memberinf。memberid 会员证号，membername 会员姓名，borrow_record。bookid 书刊编号,bookname 书刊名称，borrowdate 借书日期，
datediff(day，convert(smalldatetime,borrowdate）,getdate(）) 已借天数,0.2*(datediff(day，convert(smalldatetime，borrowdate),getdate()）—30) 罚款金额
from borrow_record，memberinf，system_books
where borrow_record。bookid=system_books。bookid and borrow_record。memberid=memberinf.memberid anddatediff（day，convert（smalldatetime，borrowdate)，getdate（））〉=30
六、设计小结
通过此次数据库课程设计，让我学到了太多太多，在课程设计的过程中,遇到许多困难，通过努力都一一解决，获益非浅，特别是如何分析、建立、开发一个系统。
课程设计，真正达到了学与用的结合，在此次课程设计，我不尽增强了对数据库方面应用的理解，而且对自己今后参与开发数据库系统积累了不少经验,从建立数据开始，对数据库设计理念及思想上有更高的认识，从需求分析，到概念设计和逻辑设计,E—R图的表示，数据库的创建,懂得了不少有关数据库开发过程中的知识,在实验中建表,及其关系模式，关系代数的建立及理解，对SQL语的查询语句有了更加深入的理解，增强了自己在数据库中应用SQL语言的灵活性，其中包括，插入、删除、修改、查询，牵涉表和表之间的联系，约