学校图书借阅管理系统数据库设计

数据库课程设计报告学校图书借阅管理系统学生姓名：郭晓东学号：2010508033专业年级：计算机科学与技术10级指导教师：朱东芹目录一、结构设计...................................................................................................................................31、逻辑结构设计.....................................................................................................................32、关系图...............................................................................................................................53、物理结构设计.....................................................................................................................64、关系模式...........................................................................................................................7二、存储过程...................................................................................................................................8三、触发器.......................................................................................................................................9四、视图脚本.................................................................................................................................12五、数据库恢复与备份.................................................................................................................12数据库设计说明书一、结构设计1、逻辑结构设计（1）总体E-R图如图1图1读者E-R图如图2图2图书E-R图如图3图3权限设置E-R图如图4图4权限设置E-R图如图5图5权限设置E-R图如图6图62、关系图关系图如图7图73、物理结构设计Tb_reader读者表：属性名类型备注说明readeridVarchar(10)主键读者idrnameVarchar(20)不允许空姓名sexVarchar(2)不允许空(‘男’or’女’)性别gradeVarchar(1)不允许空年级ProfessionVarchar(20)不允许空专业ifeffectivetinyint不允许空(1of0)是否有效emailVarchar(20)允许空邮箱Tb_manager管理员表：属性名类型备注说明manageridVarchar(10)主键管理员idloginnaneVarchar(20)不允许空登录名passwordVarchar(20)不允许空密码Tb_borrow借阅表：属性名类型备注说明borrowidint主键(自增)idbookidVarchar(10)不允许空书号readeridVarchar(10)不允许空读者号manageridVarchar(10)不允许空管理员号borrowTimeSmalldatetime不允许空借书时间backTimeSmalldatetime允许空(backTime=borrowtime)还书时间ifreborrowtinyint不允许空(1or0)是否续借Tb_book图书表：属性名类型备注说明bookidVarchar(10)主键书号bnameVarchar(20)不允许空书名typeVarchar(20)允许空类型authorVarchar(20)允许空作者pricefloat允许空价格publishVarchar(20)允许空出版社quantityTinyint不允许空总数remainTinyint不允许空(remain=quantity)剩余Tb_punishment超期处罚表：属性名类型备注说明punishmentidint主键(自增)idreaderidVarchar(10)不允许空读者号bookidVarchar(10)不允许空书号dayssmallint不允许空超期天数bmoneyfloat不允许空罚款金额Tb_purview管理员权限设置表：属性名类型备注说明manageridVarchar(10)主键管理员号systemsettinyint不允许空(1or0)系统设置权限readersettinyint不允许空(1or0)读者权限booksettinyint不允许空(1or0)图书管理权限borrowbacktinyint不允许空(1or0)图书借还权限systemquerytinyint不允许空(1or0)系统查询权限4、关系模式Tb_reader(readerid,rname,sex,grade,profession,email,ifeffective)Tb_borrow(borrowid,readerid,bookid,managerid,borrowTime,backTime,ifreborrow)Tb_manager(managerid,loginname,password)Tb_book(bookid,bname,type,author,publish,price,quantity,remain)Tb_punishment(punishmentid,readerid,bookid,days,bmoney)Tb_purview(managerid,systemset,readerset,bookset,borrowback,systemquery)二、存储过程创建存储过程，输入读者id查询出书名、借阅时间、归还时间、管理员id、是否续借情况createprocedurep@readerid_invarchar(10),@bookid_invarchar(10),@bname_outvarchar(20)output,@borrowTime_outdatetimeoutput,@backTime_outdatetimeoutput,@managerid_outvarchar(10)output,@ifreborrow_outtinyintoutputasselect@bname_out=bname,@borrowTime_out=borrowTime,@backTime_out=backTime,@managerid_out=managerid,@ifreborrow_out=ifreborrowfromtb_book,tb_borrowwheretb_borrow.readerid=@readerid_inandtb_borrow.bookid=@bookid_inandtb_book.bookid=tb_borrow.bookid存储过程使用declare@readerid_invarchar(10),@bookid_invarchar(10),@bname_outvarchar(20),@borrowTime_outdatetime,@backTime_outdatetime,@managerid_outvarchar(10),@ifreborrow_outtinyintselect@readerid_in='2010508033'select@bookid_in='005'execp@readerid_in,@bookid_in,@bname_outoutput,@borrowTime_outoutput,@backTime_outoutput,@managerid_outoutput,@ifreborrow_outoutputprint'书名：'+@bname_outprint'借书时间：'+rtrim(@borrowTime_out)print'还书时间：'+rtrim(@backTime_out)print'管理员编号：'+@managerid_outprint'是否续借：'+rtrim(@ifreborrow_out)三、触发器1、借书触发器，当借书时，图书表中的remain(剩余图书)自动减一。createtriggert_borrowontb_borrowforinsertasdeclare@bookid_readvarchar(10)select@bookid_read=bookidfrominsertedbeginupdatetb_booksetremain=remain-1wherebookid=@bookid_readEnd2、还书触发器，当还书时，图书表中的remain自动加一。createtriggert_backontb_borrowforupdateasifupdate(backTime)declare@bookid_readvarchar(10)select@bookid_read=bookidfrominsertedbeginupdatetb_booksetremain=remain+1wherebookid=@bookid_readEnd3、罚款触发器，当借阅图书超期时，自动在punishment表中添加罚款记录，记录罚款读者、书名、天数、金额，并且自动将reader表中的ifeffective置为0，是该读者不能再借书。createtriggert_punishontb_borrowforupdateasifupdate(backTime)begindeclare@backTimedatetime,@borrowTimedatetime,@ifreborrowtinyint,@daysint,@days1int,@bmoneyint,@readeridvarchar(10),@bookidvarchar(10)select@backTime=backTimefrominsertedselect@borrowTime=borrowTimefrominsertedselect@ifreborrow=ifreborrowfrominsertedselect@days=convert(int,@backTime)-convert(int,@borrowTime)select@readerid=readeridfrominsertedselect@bookid=bookidfrominsertedif@days30beginif@ifreborrow=1beginif@days60beginselect@days1=@days-60select@bmoney=@days1*0.1insertintotb_punishment(readerid,bookid,days,bmoney)values(@readerid,@bookid,@days1,@b