1、數(shù)據(jù)庫課程設(shè)計(jì)報(bào)告學(xué)校圖書借閱管理系統(tǒng)學(xué)生姓名： 郭曉東學(xué) 號：2010508033專業(yè)年級：計(jì)算機(jī)科學(xué)馬技術(shù) 10級指導(dǎo)教師：朱東芹目錄一、結(jié)構(gòu)設(shè)計(jì)31、邏輯結(jié)構(gòu)設(shè)計(jì)32、 關(guān)系圖53、物理結(jié)構(gòu)設(shè)計(jì) 64、關(guān)系模式7二、存儲(chǔ)過程8三、觸發(fā)器9四、視圖腳本12五、數(shù)據(jù)庫恢復(fù)與備份 12、結(jié)構(gòu)設(shè)計(jì)數(shù)據(jù)庫設(shè)計(jì)說明書1、邏輯結(jié)構(gòu)設(shè)計(jì)(1)總體E-R圖如圖1圖1讀者E-R圖如圖2圖書E-R圖如圖3權(quán)限設(shè)置E-R圖如圖4權(quán)限設(shè)置E-R圖如圖5權(quán)限設(shè)置E-R圖如圖62、關(guān)系圖關(guān)系圖如圖73、物理結(jié)構(gòu)設(shè)計(jì)Tb_reader 讀者表：屬性名備注說明readeridVarchar(10)主鍵讀音idrnameV

2、archar(20)不允許空姓名sexVarchar(2)不允許空(男or"')性別gradeVarchar(1)不允許空年級ProfessionVarchar(20)不允許空專業(yè)ifeffectivetinyint不允許空(1 of 0)是否后效emailVarchar(20)允許空郵箱Tb_manager管理員表:屬性名備注說明manageridVarchar(10)主鍵管理員idloginnaneVarchar(20)不允許空登錄名passwordVarchar(20)不允許空密碼Tb_borrow 借閱表:屬性名備注說明borrowidint主鍵(自增)idbooki

3、dVarchar(10)不允許空書號readeridVarchar(10)不允許空讀音萬manageridVarchar(10)不允許空管理員號borrowTimeSmalldatetime不允許空借書時(shí)間backTimeSmalldatetime允許空(backTime>=borrowtime)還書時(shí)間ifreborrowtinyint不允許空(1 or 0)是否續(xù)借Tb_book圖書表:屬性名備注說明bookidVarchar(10)主鍵書號bnameVarchar(20)不允許空書名typeVarchar(20)允許空authorVarchar(20)允許空作者pricefloat

4、允許空價(jià)格publishVarchar(20)允許空出版社quantityTinyint不允許空總數(shù)remainTinyint不允許空(remain<=quantity)剩余Tb_punishment超期處罰表:屬性名備注說明punishmentidint主鍵(自增)idreaderidVarchar(10)不允許空bookidVarchar(10)不允許空書號dayssmallint不允許空超期天數(shù)bmoneyfloat不允許空罰款金額Tb_purview管理員權(quán)限設(shè)置表:屬性名備注說明manageridVarchar(10)主鍵管理員號systemsettinyint不允許空(1 o

5、r 0)系統(tǒng)設(shè)置權(quán)限r(nóng)eadersettinyint不允許空(1 or 0)讀者權(quán)限booksettinyint不允許空(1 or 0)圖書管理權(quán)限borrowbacktinyint不允許空(1 or 0)圖書借還權(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_mana

6、ger(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)二、存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程，輸入讀者id查詢出書名、借閱時(shí)間、歸還時(shí)間、管理員id、是 否續(xù)借情況create procedure preaderid_in

7、 varchar(10),bookid_in varchar(10),bname_out varchar(20) output,borrowTime_out datetime output,backTime_out datetime output,managerid_out varchar(10) output,ifreborrow_out tinyint outputasselectbname_out=bname,borrowTime_out=borrowTime,backTime_out=backTime,managerid_out=managerid,ifreborrow_out=ifr

8、eborrowfrom tb_book,tb_borrowwhere tb_borrow.readerid=readerid_in andtb_borrow.bookid=bookid_in andtb_book.bookid=tb_borrow.bookid存儲(chǔ)過程使用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

9、 tinyintselect readeridjn='2010508033'select bookid_in='005'exec p readerid_in,bookid_in,bname_outoutput,borrowTime_out output,backTime_out output,managerid_out output,ifreborrow_out output print '書名：'+bname_outprint '借書時(shí)間：'+rtrim(borrowTime_out)print '還書時(shí)間：'+

10、rtrim(backTime_out)print '管理員編號：'+managerid_outprint '是否續(xù)借：'+rtrim(ifreborrow_out)三、觸發(fā)器1、借書觸發(fā)器，當(dāng)借書時(shí)，圖書表中的remain(剩余圖書)自動(dòng)減一。create trigger t_borrowon tb_borrow for insert asdeclare bookid_read varchar(10)select bookid_read=bookid from insertedbeginupdate tb_book set remain=remain-1 whe

11、re bookid=bookid_read End2、還書觸發(fā)器，當(dāng)還書時(shí)，圖書表中的 remain自動(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í)，自動(dòng)在punishment表中添加罰款記錄，記 錄

12、罰款讀者、書名、天數(shù)、金額，并且自動(dòng)將 reader表中的ifeffective 置為 0,是該讀者不能再借書。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 inserted

13、select borrowTime=borrowTime from insertedselect ifreborrow=ifreborrow from insertedselectdays=convert(int,backTime)-convert(int,borrowTime)select readerid=readerid from insertedselect bookid=bookid from insertedif days>30beginbeginif days>60beginselect days1=days-60select bmoney=days1*0.1inse

14、rt intotb_punishment(readerid,bookid,days,bmoney)values(readerid,bookid,days1,bmoney)update tb_reader set ifeffective=0 wherereaderid=readeridendendif ifreborrow=0beginselect days1=days-30select bmoney=days1*0.1insert intotb_punishment(readerid,bookid,days,bmoney)values(readerid,bookid,days1,bmoney)

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

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

17、derid,rname,bname,bmoney fromtb_punishment,tb_book,tb_reader wheretb_punishment.bookid=tb_book.bookid andtb_punishment.readerid=tb_reader.readerid五、數(shù)據(jù)庫恢復(fù)與備份數(shù)據(jù)庫的完全備份use db_librarygobackup database db_libraryto disk='H:LibarySystembackupf_db_library'with initGo數(shù)據(jù)庫的恢復(fù)use db_library gobackup database db_libraryto disk='H:LibarySystembackupf_db_library'with initGo數(shù)據(jù)庫差異備份use db_librarygobackup database db_libr