設(shè)計(jì)并實(shí)現(xiàn)一個(gè)簡單的學(xué)校圖書館數(shù)據(jù)庫系統(tǒng)

1、精選優(yōu)質(zhì)文檔-傾情為你奉上實(shí)驗(yàn)題目：設(shè)計(jì)并實(shí)現(xiàn)一個(gè)簡單的學(xué)校圖書館數(shù)據(jù)庫系統(tǒng)實(shí)驗(yàn)報(bào)告：一、需求分析1、 E-R圖2、 建立基本表1）圖書（書號，書名，作者，出版社，定價(jià)，類別，借閱次數(shù)，總借閱時(shí)間，總冊數(shù)，剩余冊書）2）學(xué)生讀者（學(xué)號，姓名，所在系，性別，可借書量，是否有書逾期）3）學(xué)生借閱（書號，學(xué)號，姓名，書名，借書日期，應(yīng)還日期，實(shí)還日期）4）教師讀者（教師號，姓名，單位，性別，可借書量，是否有書逾期）5）教師借閱（書號，教師號，姓名，書名，借書日期，應(yīng)還日期，實(shí)還日期，是否續(xù)借）2、關(guān)系圖二、查詢功能的實(shí)現(xiàn)1、判斷某位讀者是否有逾期的圖書（以學(xué)生讀者為例）CREATE PROCEDUR

2、E 判斷某位學(xué)生讀者是否有逾期的圖書 (學(xué)號_1 char(5)ASIF(SELECT COUNT (書號)FROM 學(xué)生借閱WHERE (DATEDIFF(day,(SELECT 應(yīng)還日期 FROM 學(xué)生借閱 WHERE 學(xué)號=學(xué)號_1),getdate()>0) AND (學(xué)號=學(xué)號_1)=0) /*計(jì)算是否逾期*/BEGINprint'該讀者無逾期的書'UPDATE 學(xué)生讀者 SET是否有書逾期=1WHERE 學(xué)號=學(xué)號_1ENDELSE print'該讀者有逾期的書'GO功能：該存儲過程通過對輸入的讀者號所對應(yīng)的預(yù)期借閱信息進(jìn)行統(tǒng)計(jì)，若結(jié)果集非空則

3、通知逾期，同時(shí)將是否有書逾期置為1，否則通知逾期。（教師讀者查詢同理，此處不再贅述）2、圖書按借閱量排序CREATE PROCEDURE 圖書按借閱量排序 ASSELECT 圖書.書號,圖書.書名,(圖書.總冊數(shù)-圖書.剩余冊數(shù)) as 借出量FROM 圖書ORDER BY 圖書.借出量 ASC /*按借出量升序排序*/GO功能：該存儲過程通過圖書屬性中總冊數(shù)與剩余冊數(shù)作差作為借出量，并在結(jié)果集中按升序排列。3、查詢已全部借出的書CREATE PROCEDURE 查詢已全部借出的書 ASSELECT 圖書.書號,圖書.書名FROM 圖書WHERE 圖書.剩余冊數(shù)=0GO功能：該存儲過程通過對剩

4、余冊書是否為零的判斷實(shí)現(xiàn)對是否全部借出的判斷。4、查詢未借出的書CREATE PROCEDURE 查詢未借出的書 ASSELECT 圖書.書號,圖書.書名,圖書.剩余冊數(shù)FROM 圖書WHERE 圖書.總冊數(shù)=圖書.剩余冊數(shù)GO功能：該存儲過程通過對總冊數(shù)與剩余冊數(shù)的比較實(shí)現(xiàn)對未借出的圖書的判斷。5圖書按類統(tǒng)計(jì)CREATE PROCEDURE 圖書按類統(tǒng)計(jì) ASSELECT 類別,sum(剩余冊數(shù)) as 館藏冊數(shù),(sum(總冊數(shù))-sum(剩余冊數(shù)) as 借出冊數(shù),(sum(總借閱時(shí)間) as 總借閱時(shí)間FROM 圖書GROUP BY 類別GO功能：該存儲過程通過分組統(tǒng)計(jì)函數(shù)實(shí)現(xiàn)對基本屬

5、性的運(yùn)算，并在結(jié)果集中以新屬性名列出。三、存儲過程和觸發(fā)器1、 存儲過程1）新增圖書信息CREATE PROCEDURE 新增圖書信息(書號_1 char(10), 書名_2 varchar(50), 作者_(dá)3 varchar(50), 出版社_4 varchar(50), 定價(jià)_5 float, 類別_6 varchar(50), 借閱次數(shù)_7 int, 總借閱時(shí)間_8 int, 總冊數(shù)_9 int, 剩余冊數(shù)_10 int)AS INSERT INTO PB 宮永超.dbo.圖書 ( 書號, 書名, 作者, 出版社, 定價(jià), 類別, 借閱次數(shù), 總借閱時(shí)間, 總冊數(shù), 剩余冊數(shù)) VALU

6、ES ( 書號_1, 書名_2, 作者_(dá)3, 出版社_4, 定價(jià)_5, 類別_6, 借閱次數(shù)_7, 總借閱時(shí)間_8, 總冊數(shù)_9, 剩余冊數(shù)_10)GO說明：該存儲過程實(shí)現(xiàn)新圖書信息向圖書表中的添加，每次只能添加一個(gè)元組。2）新增學(xué)生讀者信息CREATE PROCEDURE 新增學(xué)生讀者信息(學(xué)號_1 char(10), 姓名_2 varchar(10), 所在系_3 char(3), 性別_4 char(2), 可借書量_5 int, 是否有書逾期_6 bit)AS INSERT INTO 學(xué)生讀者 ( 學(xué)號, 姓名, 所在系, 性別, 可借書量, 是否有書逾期) VALUES ( 學(xué)號_1

7、, 姓名_2, 所在系_3, 性別_4, 可借書量_5, 是否有書逾期_6)GO說明：該存儲過程實(shí)現(xiàn)新學(xué)生讀者信息向?qū)W生讀者表中的添加，每次只能添加一個(gè)元組。3）新增教師讀者信息CREATE PROCEDURE 新增教師讀者信息(教師號_1 char(10), 姓名_2 varchar(50), 單位_3 varchar(50), 性別_4 char(2), 可借書量_5 int, 是否有書逾期_6 bit)AS INSERT INTO PB 宮永超.dbo.教師讀者 ( 教師號, 姓名, 單位, 性別, 可借書量, 是否有書逾期) VALUES ( 教師號_1, 姓名_2, 單位_3, 性別

8、_4, 可借書量_5, 是否有書逾期_6)GO說明：該存儲過程實(shí)現(xiàn)新教師讀者信息向教師讀者表中的添加，每次只能添加一個(gè)元組。4）插入學(xué)生借閱信息CREATE PROCEDURE 插入學(xué)生借閱信息(書號_1 char(10), 學(xué)號_2 char(5), 姓名_3 varchar(10), 書名_4 varchar(10), 借書日期_5 datetime)AS INSERT INTO 學(xué)生借閱 ( 書號, 學(xué)號, 姓名, 書名, 借書日期) VALUES ( 書號_1, 學(xué)號_2, 姓名_3, 書名_4, 借書日期_5)GO說明：該存儲過程將輸入信息在教師借閱表中產(chǎn)生一個(gè)新的元組，即插入新的借

9、閱信息，從而實(shí)現(xiàn)借書功能。5）插入教師借閱信息CREATE PROCEDURE 插入教師借閱信息(書號_1 char(10), 教師號_2 char(4), 姓名_3 varchar(10), 書名_4 varchar(10), 借書日期_5 datetime)AS INSERT INTO PB 宮永超.dbo.教師借閱 ( 書號, 教師號, 姓名, 書名, 借書日期) VALUES ( 書號_1, 教師號_2, 姓名_3, 書名_4, 借書日期_5)GO說明：該存儲過程將輸入信息在教師借閱表中產(chǎn)生一個(gè)新的元組，即插入新的借閱信息，從而實(shí)現(xiàn)借書功能。6） 教師借閱數(shù)據(jù)更新CREATE PROC

10、EDURE 教師借閱數(shù)據(jù)更新(書號_1 char, 教師號_2 char, 是否續(xù)借_6 int)AS UPDATE 教師借閱SET 是否續(xù)借 = 是否續(xù)借_6 /*“是否續(xù)借”置為“1”*/WHERE ( 書號 = 書號_1 AND 教師號 = 教師號_2)IF( 是否續(xù)借_6=1) UPDATE 教師借閱 SET應(yīng)還日期=DATEADD(day,30,教師借閱.應(yīng)還日期) /*將應(yīng)還日期推遲一個(gè)月*/FROM 教師借閱GO說明：該存儲過程通過輸入的教師號和書號找到對應(yīng)的借閱信息后對其進(jìn)行更新，將標(biāo)記信息“是否續(xù)借”置為“1”，并將應(yīng)還日期推遲一個(gè)月，從而實(shí)現(xiàn)續(xù)借功能。7）學(xué)生還書CREAT

11、E PROCEDURE 學(xué)生還書 學(xué)號_1 char(5), 書號_1 char(5), 實(shí)還日期_1 datetime, 是否丟失 int ASDECLARE 罰金 floatDECLARE 借期_1 intDECLARE 超期_2 intBEGINSET 借期_1=DATEDIFF(day, /*計(jì)算借期并用變量借期_1表示*/(SELECT 借書日期 FROM 學(xué)生借閱 WHERE 學(xué)號=學(xué)號_1 AND 書號=書號_1),實(shí)還日期_1)SET 超期_2=DATEDIFF(day, /*計(jì)算超期并用變量超期_2表示*/(SELECT 應(yīng)還日期 FROM 學(xué)生借閱 WHERE 學(xué)號=學(xué)號_

12、1 AND 書號=書號_1),實(shí)還日期_1)IF (超期_2>0) /*計(jì)算超期罰金并打印結(jié)果*/SET 罰金=超期_2*0.05PRINT '超期罰款'+CAST(罰金 AS char(5)IF (是否丟失=1) /*計(jì)算丟書罰金并打印結(jié)果*/SET 罰金= 2*(SELECT 定價(jià) FROM 圖書 WHERE 書號_1=書號 )PRINT '丟書罰款'+CAST(罰金 AS char(5)UPDATE 圖書 SET /*對對應(yīng)圖書信息進(jìn)行更新*/剩余冊數(shù)=剩余冊數(shù)+1， 總借閱時(shí)間=總借閱時(shí)間+借期_1WHERE 書號=書號_1UPDATE 學(xué)生借閱

13、SET /*對對應(yīng)學(xué)生借閱信息進(jìn)行更新*/實(shí)還日期=實(shí)還日期_1WHERE 學(xué)號=學(xué)號_1UPDATE 學(xué)生讀者 SET /*對對應(yīng)學(xué)生讀者信息進(jìn)行更新*/可借書量=可借書量+1WHERE 學(xué)號=學(xué)號_1DELETE /*刪除該借閱信息，實(shí)現(xiàn)還書功能*/FROM 學(xué)生借閱 WHERE 學(xué)號=學(xué)號_1 AND 書號=書號_1ENDGO說明：該存儲過程主要根據(jù)輸入信息實(shí)現(xiàn)還書、計(jì)算并打印罰款（包括超期和丟書）的功能，同時(shí)對其他表中相關(guān)信息作出更新，最后刪除該借閱信息。在實(shí)驗(yàn)過程中本想用觸發(fā)器實(shí)現(xiàn)，但遇到了一系列問題，最后選擇用存儲過程實(shí)現(xiàn)，并成功地實(shí)現(xiàn)了以上各功能。8）教師還書（注釋、說明與上一存

14、儲過程類似，不再贅述）CREATE PROCEDURE dbo.教師還書 教師號_1 char(5), 書號_1 char(5), 實(shí)還日期_1 datetime, 是否丟失 int ASDECLARE 罰金 floatDECLARE 借期_1 intDECLARE 超期_2 intBEGINSET 借期_1=DATEDIFF(day,(SELECT 借書日期 FROM 教師借閱 WHERE 教師號=教師號_1 AND 書號=書號_1),實(shí)還日期_1)SET 超期_2=DATEDIFF(day,(SELECT 應(yīng)還日期 FROM 教師借閱 WHERE 教師號=教師號_1 AND 書號=書號_1

15、),實(shí)還日期_1)IF (超期_2>0)SET 罰金=超期_2*0.05PRINT '超期罰款'+CAST(罰金 AS char(5)IF (是否丟失=1)SET 罰金= 2*(SELECT 定價(jià) FROM 圖書 WHERE 書號_1=書號 )PRINT '丟書罰款'+CAST(罰金 AS char(5)UPDATE 圖書 SET 剩余冊數(shù)=剩余冊數(shù)+1，圖書.總借閱時(shí)間=圖書.總借閱時(shí)間+借期_1WHERE 圖書.書號=書號_1UPDATE 教師借閱 SET實(shí)還日期=實(shí)還日期_1WHERE 教師號=教師號_1UPDATE 教師讀者 SET可借書量=可借書

16、量+1WHERE 教師號=教師號_1DELETE FROM 教師借閱 WHERE 教師號=教師號_1 AND 書號=書號_1ENDGO2、 觸發(fā)器1）Tri_學(xué)生借閱CREATE TRIGGER Tri_學(xué)生借閱 ON 學(xué)生借閱 FOR INSERT ASIF /*借書要求，有逾期、借書量滿或借同一本書不可借，回滾INSERT操作*/(SELECT 學(xué)生讀者.是否有書逾期 FROM 學(xué)生讀者,Inserted WHERE 學(xué)生讀者.學(xué)號=Inserted.學(xué)號)=1) /*有逾期*/OR(SELECT 學(xué)生讀者.可借書量 FROM 學(xué)生讀者,Inserted WHERE 學(xué)生讀者.學(xué)號=Ins

17、erted.學(xué)號)=0) /*借書量滿*/OR(SELECT 學(xué)生借閱.書號FROM 學(xué)生借閱,Inserted WHERE 學(xué)生借閱.書號=Inserted.書號 AND 學(xué)生借閱. 學(xué)號=Inserted.學(xué)號)!=NULL) /*借同一本書*/ROLLBACK TRANSACTIONBEGIN /*符合借書要求，執(zhí)行以下操作，對相關(guān)表的相關(guān)屬性值進(jìn)行更新*/UPDATE 圖書 SET圖書.借閱次數(shù)=圖書.借閱次數(shù)+1,圖書.剩余冊數(shù)=圖書.剩余冊數(shù)-1FROM 圖書,InsertedWHERE 圖書.書號=Inserted.書號UPDATE 學(xué)生讀者 SET學(xué)生讀者.可借書量=學(xué)生讀者.

18、可借書量-1FROM 學(xué)生讀者,InsertedWHERE 學(xué)生讀者.學(xué)號=Inserted.學(xué)號UPDATE 學(xué)生借閱 SET學(xué)生借閱.應(yīng)還日期=DATEADD(day,60,學(xué)生借閱.借書日期)FROM 學(xué)生借閱,InsertedWHERE 學(xué)生借閱.書號=Inserted.書號END說明：該觸發(fā)器建立在INSERT操作之上，即在插入新的借閱信息時(shí)觸發(fā)器動作，先對讀者的借閱條件進(jìn)行檢查，若不符合要求回滾INSERT操作，即借閱失敗，否則即實(shí)現(xiàn)插入新的借閱信息，借閱成功。之后，觸發(fā)器對相關(guān)聯(lián)的表中的某些屬性值進(jìn)行更新，比如圖書的“剩余冊數(shù)”、讀者的“可借書量”等等實(shí)現(xiàn)自動修改，使其符合實(shí)際要

19、求。2）Tri_教師借閱（注釋、說明與上一觸發(fā)器類似，不再贅述）CREATE TRIGGER Tri_教師借閱 ON 教師借閱 FOR INSERT ASIF(SELECT 教師讀者.是否有書逾期 FROM 教師讀者,Inserted WHERE 教師讀者.教師號=Inserted.教師號)=1)OR(SELECT 教師讀者.可借書量 FROM 教師讀者,Inserted WHERE 教師讀者.教師號=Inserted.教師號)=0)OR(SELECT 教師借閱.書號 FROM 教師借閱,Inserted WHERE 教師借閱.書號=Inserted.書號 AND 教師借閱. 教師號=Inse

20、rted.教師號)!=NULL)ROLLBACK TRANSACTIONBEGINUPDATE 圖書 SET圖書.借閱次數(shù)=圖書.借閱次數(shù)+1,圖書.剩余冊數(shù)=圖書.剩余冊數(shù)-1FROM 圖書,InsertedWHERE 圖書.書號=Inserted.書號UPDATE 教師讀者 SET教師讀者.可借書量=教師讀者.可借書量-1FROM 教師讀者,InsertedWHERE 教師讀者.教師號=Inserted.教師號UPDATE 教師借閱 SET教師借閱.應(yīng)還日期=DATEADD(day,90,教師借閱.借書日期)FROM 教師借閱,InsertedWHERE 教師借閱.書號=Inserted.書號END四、實(shí)驗(yàn)分析與總結(jié)管理規(guī)則的實(shí)現(xiàn)1） 只有本校師生才能借書利用學(xué)號、教師號定義時(shí)的CHECK約束（學(xué)號LIKE“PB0-90-90-9”，教師號LI