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

1、数据库系统原理课程设计数据库系统原理课程设计课题名称：课题名称： 书刊租借信息管理系统书刊租借信息管理系统 姓姓 名：名： 班班 级：级： 计算机计算机 11021102 学学 号：号： 311109010225311109010225 指导老师：指导老师： 2013 年 9 月 10 日1目录目录一、系统需求分析一、系统需求分析 . 1 11.1 背景 . 11.2 系统主要功能 . 11.3 数据流程图（DFD）. 2二、概念结构设计二、概念结构设计 . 3 32.1 书刊信息 E-R 图 . 32.2 会员信息 E-R 图 . 32.3 管理员信息 E-R 图 . 32.4 书刊租借信息

2、管理系统总 E-R 图 . 4三、逻辑结构设计三、逻辑结构设计 . 6 63.1 关系模式 . 6四、表结构定义四、表结构定义 . 6 6五、数据实施和维护五、数据实施和维护 . 8 85.1 创建数据库 . 85.2 数据初始化 . 115.3 单表查询 . 155.4 数据查询，更新及触发器，存储过程的创建 . 17六、设计小结六、设计小结 . 2626七、参考文献七、参考文献 . 27272一、一、 系统需求分析系统需求分析1.11.1 背景背景书刊租借工作繁琐，借阅频繁，包含大量的信息数据，面对着如此庞大的信息量，传统的人工方式管理会导致书刊租借管理上的混乱，人力与物力过多浪费，因此，

3、就需要一个合理、有效，规范和实用的书刊租借信息管理系统来实现对这些数据的有效管理。提高书刊租借管理工作效率，作到信息的规范管理，科学统计和快速查询，更方便于顾客的租借。1.21.2 系统主要功能系统主要功能1. 会员基本信息的输入，包括会员证号、姓名、性别，年龄，电话，地址，已借书数等。2会员基本信息的查询、修改，包括会员证号、姓名、性别，年龄，电话，地址，已借书数等。3书刊库存信息的输入，包括书刊编号，书刊名称，作者，出版社，出版日期，类别，年份，库存量，借出量。4书刊库存信息的查询，修改，包括书刊编号，书刊名称，作者，出版社，出版日期，类别，年份，库存量，借出量。5借书信息的输入，包括会员

4、证号，书刊编号，借书日期，借书天数，租借费用。6借书信息的查询、修改，包括会员证号，书刊编号，借书日期，借书天数，租借费用。7还书信息的输入，包括会员证号，书刊编号，还书日期。 8还书信息的查询和修改，包括还书会员证号、会员姓名、书刊编号、书刊名称、借书日期、还书日期等。9超期还书罚款输入，还书超出期限包括超出期限还书的会员证号，管理员编号，书刊编号，借书日期，超期罚款。10超期还书罚款查询，删除，包括会员证号、会员姓名、书刊编号、书刊名称，超期罚款等。1.31.3 数据流程图（数据流程图（DFDDFD）3管理员登记会员信息管理书刊登记会员登记书刊信息管理管理员信息管理数据库数据库数据库数据库

5、数据库借阅信息管理基本信息录入基本信息录入书刊信息返回借阅信息录入超期罚款会员信息返回基本信息录入归还信息录入数据库二、二、 概念结构设计概念结构设计2.12.1 书刊信息书刊信息 E-RE-R 图：图：书刊信息书刊编号书刊名称出版日期库存量借出量出版社类别作者42.22.2 会员信息会员信息 E-RE-R 图：图：会员信息会员证号已借书数年龄最大借书数账户余额性别地址姓名电话2.32.3 管理员信息管理员信息 E-RE-R 图：图：管理员管理员编号姓名电话地址会员信息租借书刊信息MN书刊编号会员证号租借费用借书天数借书日期shijian归还时间shijian52.42.4 书刊租借信息管理系

6、统总书刊租借信息管理系统总 E-RE-R 图：图：会员信息会员证号已借书数年龄最大借书数账户余额性别地址姓名电话书刊信息书刊编号书刊名称出版日期库存量借出量出版社类别作者租借租借费用借书天数借书日期shijian书刊编号会员证号管理员管理员编号姓名电话地址管理罚款MN1NMN归还日期6三、逻辑结构设计三、逻辑结构设计3.13.1 关系模式：关系模式：通过 ER 模型到关系模型的转化，可以得到如下关系模式：会员信息（会员证号，姓名，性别，年龄，电话，地址，最大借书数，已借书数，账户余额）管理员（管理员编号，姓名，地址，电话）书刊信息（书刊编号，书刊名称，作者，出版社，出版日期，类别，库存量，借出

7、量）书刊借阅（会员证号，书刊编号，借书日期，借书天数，租借费用， ）书刊归还（会员证号，书刊编号，还书日期）罚款（会员证号，管理员编号，借书日期，超期罚款）四、四、 表结构定义表结构定义userinf 管理员信息表列名数据类型可否为空说明useridchar(7)NOT NULL（主键）管理员编号usernamechar(20)NOT NULL姓名useradresschar(30)NULL地址usertelchar(12)NOT NULL电话borrow_record 书刊借阅表列名数据类型可否为空说明memberidchar(7)NOT NULL会员证号bookidchar(7)NOT N

8、ULL书刊编号borrowdateDateTimeNOT NULL借书日期borrownumintNOT NULL借书天数bpricefloatNOT NULL租借费用7system_books 书刊信息表列名数据类型可否为空说明bookidchar(7)NOT NULL书刊编号booknamevarchar(40)NOT NULL书刊名称bookstylechar(10)NOT NULL类别bookpubdateDatetimeNULL出版日期bookpubchar(50)NULL出版社bookauthorchar(20)NOT NULL作者stockcountintNOT NULL库存量b

9、orrowcchar(2)NOT NULL借出量memberinf 会员信息表列名数据类型可否为空说明membernamechar(20)NOT NULL会员姓名memberidchar(7)NOT NULL会员证号membertelchar(12)NOT NULL联系电话memberadressvarchar(30)NOT NULL地址membersexchar(7)NULL性别memberageintNULL年龄maxnumintNOT NULL最大借书数bbcountintNOT NULL已借书数balancefloatNOT NULL账户余额return_record 书刊归还表列名数

10、据类型可否为空说明memberidchar(7)NOT NULL会员证号bookidchar(7)NOT NULL书刊编号returndateDateTimeNOT NULL还书日期8member_fee 罚款记录表列名数据类型可否为空说明memberidchar(7)NOT NULL会员证号bookidchar(7)NOT NULL书刊编号borrowdateDateTimeNOT NULL借书日期useridchar(7)NULL管理员编号bookfeefloatNOT NULL罚款金额五、五、 数据实施和维护数据实施和维护5.15.1 数据库建立：数据库建立：5.1.15.1.1 创建数

11、据库创建数据库CREATE DATABASE bookstoresystemON ( NAME = bookstoresystem, FILENAME = d:bookstoresystem.mdf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )LOG ON( NAME = bookstore, FILENAME = d:bookstoresystem.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )GO5.1.25.1.2 创建基本表创建基本表1.管理员信息表 create table userinf

12、(useridchar(7)NOT NULL primary key,username char(20) NOT NULL,useradress char(30)NULL,usertelchar(12) NOT NULL)92. 创建书刊信息表create table system_books(bookid char(7) NOT NULL primary key,bookname varchar(40) NOT NULL,bookstyle char(10) NOT NULL,bookpubdateDatetime NULL,bookpubchar(50) NULL,bookauthor c

13、har(20) NOT NULL,stockcount int NOT NULL,borrowcchar(2) NOT NULL)3. 创建会员信息表create table memberinf(memberid char(7) NOT NULL primary key,membername char(20)NOT NULL,membertel char(12) NOT NULL,memberadress varchar(30) NOT NULL,membersex char(7) NULL,memberage int NULL,maxnum int NOT NULL,bbcount intN

14、OT NULL,10balancefloat NOT NULL)4. 创建书刊借阅表create table borrow_record( bookid char(7) NOT NULL primary key, memberid char(7) NOT NULL, borrowdate datetime NOT NULL, borrownumintNOT NULL, bprice float NOT NULL, foreign key (bookid) references system_books(bookid), foreign key (memberid) references mem

15、berinf(memberid),)5. 创建书刊归还表create table return_record( bookid char(7) primary key, memberid char(7), returndate datetime, foreign key (bookid) references system_books(bookid), foreign key (memberid) references memberinf(memberid),)6.创建罚款单表create table member_fee(11memberid char(7) NOT NULL,bookid c

16、har(7) NOT NULL primary key,borrowdate DateTimeNOT NULL,userid char(7) NULL,bookfeefloat NOT NULL)5.25.2数据初始化：数据初始化：1. 将管理员信息加入userinfinsert into userinfvalues(2013003,李华,商丘夏邑骆集乡,6223736);insert into userinfvalues(2013006,言丽丽,商丘夏邑康复路,6222766);2.将已有的书刊加入system_booksinsert into system_books(bookid,boo

17、kname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(2013013,计算机组成原理,工程技术类,2001-01-03,清华大学出版社,王爱英,8,3)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(2013086,数据库原理,工程技术类,2007-07-02,高等教育出版社,萨师煊,10,4)insert into system_boo

18、ks12(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(2013016,计算机组成原理,工程技术类,2001-01-03,清华大学出版社,王爱英,8,3)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(2013081,C程序设计,工程技术类,2002-04-02,清华大学出版社,谭浩强,12,6)insert

19、into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(2013034,数据结构（C语言版）,工程技术类,2002-06-28,清华大学出版社,吴伟民，严蔚敏,6,2)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(5455515,中华历史年,人文艺术类,2005-04-03,北京大学

20、出版社,吴强,6,1);insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(1546568,微观经济学,政治经济类,2000-10-03,北京大学出版社,李小刚,12,2)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(0151153,古代埃及,社会科学类,2001-

21、02-02,北京大学出版社,赵文华,4,1)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(1514514,日本文化,人文艺术类,2002-04-02,北京大学出版社,吴小鹏,7,0)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(5658,影视文学,图片艺术类,

22、1999-02-28,北京大学出版社,苏庆东,5,2)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(5658020,探索宇宙奥秘,自然科学类,1999-02-28,北京大学出版社,苏庆东,10,3)133.将已有的会员加入memberinfinsert into memberinf(membername,memberid,membertel,memberadress,membersex,memberage,maxnum,bbc

23、ount,balance)values(陈远鹏,07,6213048,商丘夏邑,男,21,5,2,32)insert into memberinf(membername,memberid,membertel,memberadress,membersex,memberage,maxnum,bbcount,balance)values(李德,17,6234048,郑州,男,31,7,3,62)insert into memberinf(membername,memberid,membertel,memberadress,membersex,memberage,maxnum,bbcount,bala

24、nce)values(赵铭静,02,6223078,商丘,女,18,4,4,30)insert into memberinf(membername,memberid,membertel,memberadress,membersex,memberage,maxnum,bbcount,balance)values(李丽,37,6213565,开封,女,26,8,3,80)insert into memberinf(membername,memberid,membertel,memberadress,membersex,memberage,maxnum,bbcount,balance)values(

25、蒋伟,13,6233348,周口,男,24,6,2,18)insert into memberinf(membername,memberid,membertel,memberadress,membersex,memberage,maxnum,bbcount,balance)values(赵鑫,16,6218888,焦作,女,25,6,2,60)144. 添加借阅信息到borrow_record表insert into borrow_record(memberid,bookid,borrowdate,borrownum,bprice)values(17,2013013,2013-07-20,30

26、,5)insert into borrow_record(memberid,bookid,borrowdate,borrownum,bprice)values(02,2013081,2013-08-03,30,3)insert into borrow_record(memberid,bookid,borrowdate,borrownum,bprice)values(13,1546568,2013-09-02,30,4)insert into borrow_record(memberid,bookid,borrowdate,borrownum,bprice)values(16,2013034,2

27、013-08-16,30,7)insert into borrow_record(memberid,bookid,borrowdate,borrownum,bprice)values(17,0151153,2013-07-28,30,2)insert into borrow_record(memberid,bookid,borrowdate,borrownum,bprice)values(37,2013086,2013-09-02,30,5)5添加归还信息到return_record表155.35.3单表查询：单表查询：5.3.15.3.1简单查询简单查询1.表userinf中查询select

28、 *from userinf2.表ststem_books中查询select *from system_books3.表memberinf中查询select *from memberinf164表borrow_record和return_record中查询select *from borrow_recordselect *from return_record5.查询已借数量大于等于3会员的所有信息select *from memberinfwhere bbcount=3176.查询借书会员的已借书天数select borrow_record.memberid 会员证号,membername 会

29、员姓名,bookname 书刊名称,borrowdate 借书日期,datediff(day,convert(smalldatetime,borrowdate),getdate() 已借天数from borrow_record,memberinf,system_bookswhere borrow_record.bookid=system_books.bookid and borrow_record.memberid=memberinf.memberid7.查询未被借出的书本select *from system_bookswhere bookid not in (select bookid f

30、rom borrow_record)5.45.4数据查询，更新及触发器，存储过程的创建：数据查询，更新及触发器，存储过程的创建：185.4.15.4.1创建触发器创建触发器1.在return_record表中建立一个插入触发器(returnbook),功能：当还书操作时，要在还书记录中添加一条记录，同时删除相应书本的借阅记录。SQL 语言：create trigger returnbookon return_recordafter insertasdeletefrom borrow_recordwhere borrow_record.bookid=(select borrow_record.b

31、ookid from borrow_record,inserted where borrow_record.bookid=inserted.bookid)2. 在return_record表中再建立一个插入触发器(returnbook2)，功能：当还书操作时，同时对相应表system_books， memberinf做相应的改动，system_books的属性：库存量加1，借出量减1，memberinf的属性：已借书数减1。SQL 语言：create trigger returnbook2on return_recordafter insertasupdate system_booksset

32、stockcount=stockcount+1,borrowc=borrowc-1where system_books.bookid in (select system_books.bookid from system_books,inserted where system_books.bookid=inserted.bookid) and system_books.borrowc0update memberinfset bbcount=bbcount-1where memberinf.memberid in (select memberinf.memberid from memberinf,

33、inserted where memberinf.memberid=inserted.memberid) and memberinf.bbcount0假设归还书刊编号为2013086代码insert into return_recordvalues(2013086,37,2013-9-6)19执行前system_books表：执行后system_books表：执行前memberinf表：20执行后memberinf表：3.在borrow_record表中建立一个插入触发器(borrowbook),当借书操作时，要改变memberinf表的部分属性，已借书数加 1，账户余额减去相应的租借费用。S

34、QL 语言：create trigger borrowbookon borrow_recordafter insertasupdate memberinfset 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

35、memberinf.memberid=inserted .memberid)假设借书刊编号为5658代码insert into borrow_recordvalues(5658,02,2013-09-01,30,5)21执行代码前memberinf表：执行代码后memberinf表：4.在borrow_record表中建立一个插入触发器(borrowbook2),当借书操作时，要改变system_books表的部分属性，库存量减 1，借出量加 1。SQL 语言：create trigger borrowbook2on borrow_recordafter insertasupdate syst

36、em_booksset stockcount=stockcount-1,borrowc=borrowc+122where system_books.bookid in (select system_books.bookid from system_books,inserted where system_books.bookid=inserted .bookid)成功创建截图5.4.25.4.2创建存储过程：创建存储过程：1.创建一个按照书刊类型查找该类型书刊的所有信息的存储过程SQL 语言：create procedure bstyle类别char(10)asselect *from syst

37、em_bookswhere bookstyle=类别执行存储过程查询工程技术类书刊信息232.创建一个当会员属性已借书数等于最大借书数时，提示不能进行借书的存储过程SQL 语言create procedure memer会员证号char(7),会员姓名char(20)asif(select bbcount from memberinf where memberid=会员证号)=(select maxnum from memberinf where memberid=会员证号)print 会员姓名+已借书数等于最大借书数，不能再借书elseprint 会员姓名+可以继续借书执行存储过程分别查询赵

38、铭静，与陈远鹏是否可以借书memberinf表对比图：3创建一个存储过程根据会员证号，查询出会员借书信息24SQL 语句create procedure selectinf会员证号char(7)asselect memberinf.memberid 会员证号,membername 会员姓名, bbcount 已借书数, balance 账户余额,system_books.bookid 书刊编号,bookname 书刊名称,borrowdate 借书日期,borrownum 借书天数,bprice 租借费用from borrow_record,system_books,memberinfwher

39、e borrow_record.bookid=system_books.bookid and borrow_record.memberid=memberinf.memberid and memberinf.memberid=会员证号查找会员证号为02,37,13借书信息exec selectinf 02exec selectinf 37exec selectinf 13参与对照表borrow_record表：25所创建的触发器，存储过程列表图： 5.4.35.4.3超期处理：超期处理：1.对已借书的会员进行查询借书是否超期（为了方便借书天数全为30）说明：当前的日期为2013年9月13日计算S

40、QL 语言：select memberinf.memberid 会员证号,memberinf.membername 会员姓名, system_books.bookid 书刊编号,bookname 书刊名称,borrowdate 借书日期,datediff(day,convert(smalldatetime,borrowdate),getdate()-30 超出天数from borrow_record,memberinf,system_bookswhere borrow_record.bookid=system_books.bookid and borrow_record.memberid=memberinf.memberidand datediff(day,convert(smalldatetime,borrowdate),getdate()=30运行结果图：26对照表borrow_record表的截图：2.对超过天数的读者进行罚款，将信息插入到member_fee表超出一天按0.2元扣除计算：inser