【導(dǎo)讀】通過小型信息系統(tǒng)設(shè)計(jì)和開發(fā)，使我們了解數(shù)據(jù)庫設(shè)計(jì)在軟件工程中的地位，掌握數(shù)據(jù)庫設(shè)計(jì)的方法，并把數(shù)據(jù)庫運(yùn)用于編程，鞏固我們?cè)谡n堂上學(xué)習(xí)到的知識(shí)，并培養(yǎng)我們作為未來軟件開發(fā)，測(cè)試者應(yīng)該具有的合作精神，主動(dòng)學(xué)習(xí)運(yùn)用新知識(shí)。針對(duì)特定應(yīng)用設(shè)計(jì)系統(tǒng)功能模塊結(jié)構(gòu)和數(shù)據(jù)庫表結(jié)構(gòu)，達(dá)到將《數(shù)據(jù)庫系統(tǒng)原理》課程的理論知識(shí)綜合運(yùn)用的目的。在圖書管理系統(tǒng)中，圖書管理員要為每個(gè)讀者建立借閱賬戶，記錄讀者的個(gè)人信息和圖書借閱信息。l師生可借閱圖書，借閱圖書有期限限制和數(shù)量限制，逾期不還還需要罰款；l每本書的館藏?cái)?shù)量不同，圖書管理員需對(duì)這些書進(jìn)行管理，書籍借出和歸還時(shí)須注意是否有損毀。Ø罰款信息，包括數(shù)據(jù)項(xiàng)有：流水號(hào)、罰款原因、罰款金額、日期。ER模型的“聯(lián)系”用于刻畫實(shí)體之間的關(guān)聯(lián)。一種完整的方式是對(duì)局部結(jié)構(gòu)中任意兩個(gè)實(shí)體類型，依據(jù)需求分析的結(jié)果，考察局部結(jié)構(gòu)中任意兩個(gè)實(shí)體類型之間是否存在聯(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è)置合適的存取方式，主要采用以下兩類存取方式：建立索引以及存儲(chǔ)過程。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)達(dá)到最大借書數(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é)果：對(duì)比數(shù)據(jù)庫結(jié)果驗(yàn)證：CREATE PROCEDURE proc_GetTopReaderAS SELECT ReaderID,ReaderName,BorrowedSum FROM Reader ORDER BY BorrowedSum DESCGO在這次的學(xué)習(xí)過程中有了很多的收獲，對(duì)于數(shù)據(jù)庫有了更加深刻的認(rèn)識(shí)。遇到了很多的困難，總的來說還是比較成功的實(shí)現(xiàn)了關(guān)于圖書管理系統(tǒng)的數(shù)據(jù)庫設(shè)計(jì)。希望大家都能努力學(xué)習(xí)，收獲更多真實(shí)的自己，更多知識(shí)。指導(dǎo)老師評(píng)語及得分：簽名： 年 月 日