學(xué)校圖書(shū)借閱管理系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)

1、精選優(yōu)質(zhì)文檔-傾情為你奉上數(shù)據(jù)庫(kù)課程設(shè)計(jì)報(bào)告學(xué)校圖書(shū)借閱管理系統(tǒng) 學(xué)生姓名： 郭 曉 東 學(xué) 號(hào)： 專業(yè)年級(jí)： 計(jì)算機(jī)科學(xué)與技術(shù)10級(jí) 指導(dǎo)教師： 朱 東 芹 目錄數(shù)據(jù)庫(kù)設(shè)計(jì)說(shuō)明書(shū)一、結(jié)構(gòu)設(shè)計(jì)1、邏輯結(jié)構(gòu)設(shè)計(jì)（1） 總體E-R圖如圖1圖1讀者E-R圖如圖2圖2圖書(shū)E-R圖如圖3圖3權(quán)限設(shè)置E-R圖如圖4圖4權(quán)限設(shè)置E-R圖如圖5圖5權(quán)限設(shè)置E-R圖如圖6圖62、 關(guān)系圖關(guān)系圖如圖7圖73、物理結(jié)構(gòu)設(shè)計(jì)Tb_reader讀者表：屬性名類型備注說(shuō)明readeridVarchar(10)主鍵讀者idrnameVarchar(20)不允許空姓名sexVarchar(2)不允許空(男 or 女)性別gr

2、adeVarchar(1)不允許空年級(jí)ProfessionVarchar(20)不允許空專業(yè)ifeffectivetinyint不允許空(1 of 0)是否有效emailVarchar(20)允許空郵箱Tb_manager管理員表：屬性名類型備注說(shuō)明manageridVarchar(10)主鍵管理員idloginnaneVarchar(20)不允許空登錄名passwordVarchar(20)不允許空密碼Tb_borrow借閱表：屬性名類型備注說(shuō)明borrowidint主鍵(自增)idbookidVarchar(10)不允許空書(shū)號(hào)readeridVarchar(10)不允許空讀者號(hào)manage

3、ridVarchar(10)不允許空管理員號(hào)borrowTimeSmalldatetime不允許空借書(shū)時(shí)間backTimeSmalldatetime允許空(backTime>=borrowtime)還書(shū)時(shí)間ifreborrowtinyint不允許空(1 or 0)是否續(xù)借Tb_book圖書(shū)表：屬性名類型備注說(shuō)明bookidVarchar(10)主鍵書(shū)號(hào)bnameVarchar(20)不允許空書(shū)名typeVarchar(20)允許空類型authorVarchar(20)允許空作者pricefloat允許空價(jià)格publishVarchar(20)允許空出版社quantityTinyint不允

4、許空總數(shù)remainTinyint不允許空(remain<=quantity)剩余Tb_punishment超期處罰表：屬性名類型備注說(shuō)明punishmentidint主鍵(自增)idreaderidVarchar(10)不允許空讀者號(hào)bookidVarchar(10)不允許空書(shū)號(hào)dayssmallint不允許空超期天數(shù)bmoneyfloat不允許空罰款金額Tb_purview管理員權(quán)限設(shè)置表：屬性名類型備注說(shuō)明manageridVarchar(10)主鍵管理員號(hào)systemsettinyint不允許空(1 or 0)系統(tǒng)設(shè)置權(quán)限r(nóng)eadersettinyint不允許空(1 or 0)讀

5、者權(quán)限booksettinyint不允許空(1 or 0)圖書(shū)管理權(quán)限borrowbacktinyint不允許空(1 or 0)圖書(shū)借還權(quán)限systemquerytinyint不允許空(1 or 0)系統(tǒng)查詢權(quán)限4、 關(guān)系模式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

6、(bookid,bname,type,author,publish,price,quantity,remain)Tb_punishment(punishmentid,readerid,bookid,days,bmoney)Tb_purview(managerid,systemset,readerset,bookset,borrowback,systemquery)二、存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程，輸入讀者id查詢出書(shū)名、借閱時(shí)間、歸還時(shí)間、管理員id、是否續(xù)借情況create procedure preaderid_in varchar(10),bookid_in varchar(10),bname_

7、out varchar(20) output,borrowTime_out datetime output,backTime_out datetime output,managerid_out varchar(10) output,ifreborrow_out tinyint outputas select bname_out=bname,borrowTime_out=borrowTime,backTime_out=backTime,managerid_out=managerid,ifreborrow_out=ifreborrowfrom tb_book,tb_borrowwhere tb_b

8、orrow.readerid=readerid_in and tb_borrow.bookid=bookid_in and tb_book.bookid=tb_borrow.bookid存儲(chǔ)過(guò)程使用declare readerid_in varchar(10),bookid_in varchar(10),bname_out varchar(20),borrowTime_out datetime,backTime_out datetime,managerid_out varchar(10),ifreborrow_out tinyintselect readerid_in=''se

9、lect bookid_in='005'exec p readerid_in,bookid_in,bname_out output,borrowTime_out output,backTime_out output,managerid_out output,ifreborrow_out outputprint '書(shū)名：'+bname_outprint '借書(shū)時(shí)間：'+rtrim(borrowTime_out)print '還書(shū)時(shí)間：'+rtrim(backTime_out)print '管理員編號(hào)：'+manage

10、rid_outprint '是否續(xù)借：'+rtrim(ifreborrow_out)三、觸發(fā)器1、 借書(shū)觸發(fā)器，當(dāng)借書(shū)時(shí)，圖書(shū)表中的remain(剩余圖書(shū))自動(dòng)減一。create trigger t_borrowon tb_borrow for insertas declare bookid_read varchar(10)select bookid_read=bookid from insertedbeginupdate tb_book set remain=remain-1 where bookid=bookid_readEnd2、 還書(shū)觸發(fā)器，當(dāng)還書(shū)時(shí)，圖書(shū)表中的rema

11、in自動(dòng)加一。create trigger t_backon tb_borrow for updateas if update(backTime)declare bookid_read varchar(10)select bookid_read=bookid from insertedbeginupdate tb_book set remain=remain+1 where bookid=bookid_readEnd3、 罰款觸發(fā)器，當(dāng)借閱圖書(shū)超期時(shí)，自動(dòng)在punishment表中添加罰款記錄，記錄罰款讀者、書(shū)名、天數(shù)、金額，并且自動(dòng)將reader表中的ifeffective置為0，是該讀者不

12、能再借書(shū)。create trigger t_punishon tb_borrow for updateasif update(backTime)begindeclare backTime datetime,borrowTime datetime, ifreborrow tinyint,days int,days1 int,bmoney int,readerid varchar(10),bookid varchar(10)select backTime=backTime from insertedselect borrowTime=borrowTime from insertedselect i

13、freborrow=ifreborrow from insertedselect days=convert(int,backTime)-convert(int,borrowTime)select readerid=readerid from insertedselect bookid=bookid from insertedif days>30beginif ifreborrow=1beginif days>60beginselect days1=days-60select bmoney=days1*0.1insert into tb_punishment(readerid,boo

14、kid,days,bmoney) values(readerid,bookid,days1,bmoney)update tb_reader set ifeffective=0 where readerid=readeridendendif ifreborrow=0beginselect days1=days-30select bmoney=days1*0.1insert into tb_punishment(readerid,bookid,days,bmoney) values(readerid,bookid,days1,bmoney)update tb_reader set ifeffect

15、ive=0 where readerid=readeridendendend4、 讀者是否有效觸發(fā)器，當(dāng)讀者借書(shū)時(shí)，檢查reader表中的ifeffective屬性，若為0則說(shuō)明有超期罰款，不能借書(shū)。create trigger t_ifcanborrowon tb_borrow for insertas declare readerid varchar(10),ifeffective tinyintselect readerid=readerid from insertedselect ifeffective=ifeffective from tb_reader where readerid

16、=readeridif ifeffective=0beginprint '您有超期罰款！'rollback transactionend四、視圖腳本1、 創(chuàng)建視圖查詢各種圖書(shū)的書(shū)號(hào)、書(shū)名、總數(shù)和在冊(cè)數(shù)。create view view_queryas select bookid,bname,quantity,remain from tb_book2、 創(chuàng)建視圖查詢讀者的超期罰款情況，其中包括讀者號(hào)，讀者姓名，罰款書(shū)名，罰款金額。create view view_readeras select tb_punishment.readerid,rname,bname,bmoney f

17、rom tb_punishment,tb_book,tb_reader where tb_punishment.bookid=tb_book.bookid and tb_punishment.readerid=tb_reader.readerid五、數(shù)據(jù)庫(kù)恢復(fù)與備份數(shù)據(jù)庫(kù)的完全備份use db_librarygobackup database db_library to disk='H:LibarySystembackupf_db_library'with initGo數(shù)據(jù)庫(kù)的恢復(fù)use db_librarygobackup database db_library to disk='H:LibarySystembackupf_db_library'with initGo數(shù)據(jù)庫(kù)差異備份use db_librarygobackup database db_library to disk='