學校圖書借閱管理系統(tǒng)數(shù)據(jù)庫設計

學校圖書借閱管理系統(tǒng)數(shù)據(jù)庫設計學校圖書借閱管理系統(tǒng)數(shù)據(jù)庫設計學校圖書借閱管理系統(tǒng)數(shù)據(jù)庫設計學校圖書借閱管理系統(tǒng)數(shù)據(jù)庫設計編制僅供參考審核批準生效日期地址：電話：傳真:郵編:數(shù)據(jù)庫課程設計報告學校圖書借閱管理系統(tǒng)學生姓名：郭曉東學號：2010508033專業(yè)年級：計算機科學與技術10級指導教師：朱東芹目錄TOC\o"1-3"\h\u15221一、結構設計 3215631、邏輯結構設計 3121862、關系圖 5210823、物理結構設計 565764、關系模式 72651二、存儲過程 79220三、觸發(fā)器 95368四、視圖腳本 1217796五、數(shù)據(jù)庫恢復與備份 12數(shù)據(jù)庫設計說明書一、結構設計1、邏輯結構設計總體E-R圖如圖1圖1讀者E-R圖如圖2圖2圖書E-R圖如圖3圖3權限設置E-R圖如圖4圖4權限設置E-R圖如圖5圖5權限設置E-R圖如圖6圖6關系圖關系圖如圖7圖73、物理結構設計Tb_reader讀者表：屬性名類型備注說明readeridVarchar(10)主鍵讀者idrnameVarchar(20)不允許空姓名sexVarchar(2)不允許空(‘男’or’女’)性別gradeVarchar(1)不允許空年級ProfessionVarchar(20)不允許空專業(yè)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)是否續(xù)借Tb_book圖書表：屬性名類型備注說明bookidVarchar(10)主鍵書號bnameVarchar(20)不允許空書名typeVarchar(20)允許空類型authorVarchar(20)允許空作者pricefloat允許空價格publishVarchar(20)允許空出版社quantityTinyint不允許空總數(shù)remainTinyint不允許空(remain<=quantity)剩余Tb_punishment超期處罰表：屬性名類型備注說明punishmentidint主鍵(自增)idreaderidVarchar(10)不允許空讀者號bookidVarchar(10)不允許空書號dayssmallint不允許空超期天數(shù)bmoneyfloat不允許空罰款金額Tb_purview管理員權限設置表：屬性名類型備注說明manageridVarchar(10)主鍵管理員號systemsettinyint不允許空(1or0)系統(tǒng)設置權限r(nóng)eadersettinyint不允許空(1or0)讀者權限booksettinyint不允許空(1or0)圖書管理權限borrowbacktinyint不允許空(1or0)圖書借還權限systemquerytinyint不允許空(1or0)系統(tǒng)查詢權限關系模式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)二、存儲過程創(chuàng)建存儲過程，輸入讀者id查詢出書名、借閱時間、歸還時間、管理員id、是否續(xù)借情況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'是否續(xù)借：'+rtrim(@ifreborrow_out)三、觸發(fā)器借書觸發(fā)器，當借書時，圖書表中的remain(剩余圖書)自動減一。createtriggert_borrowontb_borrowforinsertasdeclare@bookid_readvarchar(10) select@bookid_read=bookidfrominsertedbeginupdatetb_booksetremain=remain-1wherebookid=@bookid_readEnd還書觸發(fā)器，當還書時，圖書表中的remain自動加一。createtriggert_backontb_borrowforupdateasifupdate(backTime)declare@bookid_readvarchar(10) select@bookid_read=bookidfrominsertedbeginupdatetb_booksetremain=remain+1wherebookid=@bookid_readEnd罰款觸發(fā)器，當借閱圖書超期時，自動在punishment表中添加罰款記錄，記錄罰款讀者、書名、天數(shù)、金額，并且自動將reader表中的ifeffective置為0，是該讀者不能再借書。createtriggert_punishontb_borrowforupdateasifupdate(backTime)begin declare@backTimedatetime, @borrowTimedatetime, @ifreborrowtinyint, @daysint, @days1int, @bmoneyint, @readeridvarchar(10), @bookidvarchar(10) select@backTime=backTimefrominserted select@borrowTime=borrowTimefrominserted select@ifreborrow=ifreborrowfrominserted select@days=convert(int,@backTime)-convert(int,@borrowTime) select@readerid=readeridfrominserted select@bookid=bookidfrominserted if@days>30 begin if@ifreborrow=1 begin if@days>60 begin select@days1=@days-60 select@bmoney=@days1*0.1 insertintotb_punishment(readerid,bookid,days,bmoney)values(@readerid,@bookid,@days1,@bmoney) updatetb_readersetifeffective=0wherereaderid=@readerid end end if@ifreborrow=0 begin select@days1=@days-30 select@bmoney=@days1*0.1 insertintotb_punishment(readerid,bookid,days,bmoney)values(@readerid,@bookid,@days1,@bmoney) updatetb_readersetifeffective=0wherereaderid=@readerid end endend讀者是否有效觸發(fā)器，當讀者借書時，檢查reader表中的ifeffective屬性，若為0則說明有超期罰款，不能借書。createtriggert_ifcanborrowontb_borrowforinsertasdeclare@readeridvarchar(10), @ifeffectivetinyintselect@readerid=readeridfrominsertedselect@ifeffective=ifeffectivefromtb_readerwherereaderid=@readeridif@ifeffective=0beginprint'您有超期罰款！'rollbacktransactionend四、視圖腳本創(chuàng)建視圖查詢各種圖書的書號、書名、總數(shù)和在冊數(shù)。createviewview_queryasselectbookid,bname,quantity,remainfromtb_book創(chuàng)建視圖查詢讀者的超期罰款情況，其中包括讀者號，讀者姓名，罰款書名，罰款金額。createviewview_readerasselecttb_punishment.readerid,rname,bname,bmoneyfromtb_punishment,tb_book,tb_read