【導(dǎo)讀】通過小型信息系統(tǒng)設(shè)計和開發(fā)，使我們了解數(shù)據(jù)庫設(shè)計在軟件工程中的地位，掌握數(shù)據(jù)庫設(shè)計的方法，并把數(shù)據(jù)庫運用于編程，鞏固我們在課堂上學(xué)習(xí)到的知識，并培養(yǎng)我們作為未來軟件開發(fā)，測試者應(yīng)該具有的合作精神，主動學(xué)習(xí)運用新知識。針對特定應(yīng)用設(shè)計系統(tǒng)功能模塊結(jié)構(gòu)和數(shù)據(jù)庫表結(jié)構(gòu)，達到將《數(shù)據(jù)庫系統(tǒng)原理》課程的理論知識綜合運用的目的。在圖書管理系統(tǒng)中，圖書管理員要為每個讀者建立借閱賬戶，記錄讀者的個人信息和圖書借閱信息。l師生可借閱圖書，借閱圖書有期限限制和數(shù)量限制，逾期不還還需要罰款；l每本書的館藏數(shù)量不同，圖書管理員需對這些書進行管理，書籍借出和歸還時須注意是否有損毀。Ø罰款信息，包括數(shù)據(jù)項有：流水號、罰款原因、罰款金額、日期。ER模型的“聯(lián)系”用于刻畫實體之間的關(guān)聯(lián)。一種完整的方式是對局部結(jié)構(gòu)中任意兩個實體類型，依據(jù)需求分析的結(jié)果，考察局部結(jié)構(gòu)中任意兩個實體類型之間是否存在聯(lián)系。

　　

【正文】 ype表User表Amerce表AmerceType表CREATE VIEW BookViewASSELECT Book.*, BookConcern.*, BookType.*FROM Book INNER JOIN BookConcern ON = INNER JOIN BookType ON = CREATE VIEW ReaderViewASSELECT AS Expr1, Reader.*, , FROM Reader INNER JOIN ReaderType ON = INNER JOIN PaperType ON =CREATE VIEW GiveInfoViewASSELECT GiveInfo.*, , , , , , , , , , , , , , , , , , , , , , ReaderView.[Email], , , , , , , , , , , FROM GiveInfo INNER JOIN BookView ON = INNER JOIN ReaderView ON = 為提高數(shù)據(jù)庫數(shù)據(jù)的存取速度，而需要設(shè)置合適的存取方式，主要采用以下兩類存取方式：建立索引以及存儲過程。CREATE PROCEDURE proc_AddBook@BookID VARCHAR(10),@BookName VARCHAR(20),@WritterName VARCHAR(10),@Interpret VARCHAR(20),@BookType VARCHAR(4),@BookConcern VARCHAR(4),@PublishDate DATETIME,@Price INT,@PageCount INT,@Accessories VARCHAR(20),@BookView TEXTAS INSERT INTO Book(BookID,BookName,WritterName,Interpret,BookType,BookConcern,PublishDate,Price,PageCount,Accessories,BookView) VALUES(@BookID,@BookName,@WritterName,@Interpret,@BookType,@BookConcern,@PublishDate,@Price,@PageCount,@Accessories,@BookView)GO執(zhí)行結(jié)果：CREATE PROCEDURE proc_AddReader@ReaderID varchar(10),@ReaderName varchar(10),@ReaderSex varchar(2),@ReaderBirth datetime,@ReaderTel varchar(13),@ReaderAddr varchar(100),@ReaderTypeID int,@PaperType varchar(4),@PaperNum varchar(20),@ReaderWork varchar(20),@Email varchar(30),@Remark varchar(100)AS INSERT INTO Reader(ReaderID,ReaderName,ReaderSex,ReaderBirth,ReaderTel,ReaderAddr,ReaderTypeID,PaperType,PaperNum,ReaderWork,[Email],Remark) VALUES(@ReaderID,@ReaderName,@ReaderSex,@ReaderBirth,@ReaderTel,@ReaderAddr,@ReaderTypeID,@PaperType,@PaperNum,@ReaderWork,@Email,@Remark)GO執(zhí)行結(jié)果： CREATE PROCEDURE proc_CheakContinueBook@BookID VARCHAR(10),@Result INT OUTPUTAS IF NOT EXISTS(SELECT BookID FROM Book WHERE BookID=@BookID) SET @Result=0 ELSE IF EXISTS(SELECT BooKID FROM Book WHERE BookID=@BookID AND Status=39。在館39。) SET @Result=1 ELSE IF((SELECT TOP 1 ReturnTime FROM GiveInfo WHERE BookID=@BookID ORDER BY GiveTime DESC) GETDATE()) SET @Result=2 ELSE SET @Result=3GOCREATE PROCEDURE proc_CheckGiveBook@BookID VARCHAR(10),@Result INT OUTPUTAS IF NOT EXISTS(SELECT BookID FROM Book WHERE BookID=@BookID) SET @Result=0 ELSE IF EXISTS(SELECT BooKID FROM Book WHERE BookID=@BookID AND Status=39。借出39。) SET @Result=1 ELSE SET @Result=2GOCREATE PROCEDURE proc_CheckReader@ReaderID VARCHAR(10),@Result INT OUTPUTAS IF EXISTS(SELECT ReaderStatus FROM Reader WHERE ReaderID=@ReaderID AND ReaderStatus=39。掛失39。) SET @Result = 0 卡已經(jīng)掛失 ELSE IF EXISTS(SELECT BorrowedCount FROM Reader WHERE ReaderID=@ReaderID AND BorrowedCount=(SELECT BorrowBookNum FROM ReaderType WHERE ID=(SELECT ReaderTypeID FROM Reader WHERE ReaderID=@ReaderID))) SET @Result = 1 卡已經(jīng)達到最大借書數(shù) ELSE IF EXISTS(SELECT * FROM Reader WHERE ReaderID=@ReaderID) SET @Result = 3 可以借書 ELSE SET @Result = 2 用戶不存在GOCREATE PROCEDURE proc_CheckReturnBook@BookID VARCHAR(10),@Result INT OUTPUTAS IF NOT EXISTS(SELECT BookID FROM Book WHERE BookID=@BookID) SET @Result=0 ELSE IF EXISTS(SELECT BooKID FROM Book WHERE BookID=@BookID AND Status=39。在館39。) SET @Result=1 ELSE SET @Result=2GOCREATE PROCEDURE proc_CleanAllDataAS DELETE FROM Book DELETE FROM BookType DELETE FROM BookConcern DELETE FROM Reader DELETE FROM ReaderType DELETE FROM PaperType DELETE FROM GiveInfo DELETE FROM Amerce DELETE FROM AmerceTypeGOCREATE PROCEDURE proc_CleanReturnDataAS DELETE FROM GiveInfo WHERE IsReturnTime IS NOT NULLGOCREATE PROCEDURE proc_ContinueBook@ID INT,@BookID VARCHAR(10),@ReturnTime DATETIMEAS UPDATE GiveInfo SET IsAddTime=1,ReturnTime=@ReturnTime WHERE ID=@IDGOCREATE PROCEDURE proc_GetAllBookAS SELECT * FROM BookGOCREATE PROCEDURE proc_GetGiveInfo@BookID VARCHAR(10)AS SELECT TOP 1 * FROM GiveInfo WHERE BookID=@BookID ORDER BY GiveTime DESCGOCREATE PROCEDURE proc_GetStatBookTypeAS SELECT COUNT(BookType) FROM Book WHERE BookType IN (SELECT BookType FROM BookType)GOCREATE PROCEDURE proc_GetTopBookAS SELECT TOP 10 BookID,BookName,LentCount FROM Book ORDER BY LentCount DESCGO執(zhí)行結(jié)果：對比數(shù)據(jù)庫結(jié)果驗證：CREATE PROCEDURE proc_GetTopReaderAS SELECT ReaderID,ReaderName,BorrowedSum FROM Reader ORDER BY BorrowedSum DESCGO在這次的學(xué)習(xí)過程中有了很多的收獲，對于數(shù)據(jù)庫有了更加深刻的認(rèn)識。遇到了很多的困難，總的來說還是比較成功的實現(xiàn)了關(guān)于圖書管理系統(tǒng)的數(shù)據(jù)庫設(shè)計。希望大家都能努力學(xué)習(xí)，收獲更多真實的自己，更多知識。指導(dǎo)老師評語及得分：簽名： 年 月 日