1、SQL圖書管理系統(tǒng)設(shè)計課題報告班級：計網(wǎng)0901班小組成員：柳錄秧周龍2010-12-03目錄1 .圖書管理系統(tǒng)ER圖2 .系統(tǒng)功能設(shè)計3 .數(shù)據(jù)表的創(chuàng)建4 .數(shù)據(jù)庫完整性設(shè)計5 . SQL Server數(shù)據(jù)庫對象設(shè)計1.圖書管理系統(tǒng)設(shè)計E-R圖2.系統(tǒng)功能設(shè)計(1)信息錄入功能1)添加新圖書信息。當(dāng)圖書館收藏新圖書時，系統(tǒng)向用戶提供新圖書信息錄入功能，由于同一種書可能會有多本，因此，新圖書的信息有兩類：某一個 ISBN 類別的圖書信息，包括：ISBN書號、圖書類別、書名、作者、出版日期、價格、 館藏數(shù)量、可借數(shù)量、圖書簡介；另一個具體到每一本書的信息，包括： ISBN 書號、圖書書號、是否可

2、借。每一個ISBN書號和同一個ISBN書號的多本書之間 是一對多的關(guān)系，每一本書的圖書書號是唯一的。2)添加讀者信息。用于登記新讀者信息。包括：借書證號、性別、身份證號、 職稱、可借數(shù)量、已借數(shù)量、工作部門、聯(lián)系等。3)借閱信息。用于登記讀者的借閱情況信息。包括：借書證號、借閱書號、借出日期、借閱期限、歸還日期等信息。歸還日期為空值表示該圖書未歸還。(2)數(shù)據(jù)修改和刪除功能1)修改和刪除圖書信息。圖書被借出時，系統(tǒng)需要更新圖書信息的可借數(shù)量， 當(dāng)可借數(shù)量為0時，表示該圖書都已被借出。當(dāng)輸入的圖書信息有錯誤或需要進 行必要更新時，可以修改圖書信息；當(dāng)一種圖書所有館藏圖書都已損毀或遺失并 且不能重

3、新買到時，該圖書信息需要刪除。2)修改和刪除讀者信息。當(dāng)讀者的自身信息發(fā)生變動，如部門間調(diào)動或調(diào)離本 單位，或違反圖書館規(guī)定需要限制其可借閱圖書數(shù)量時，需要修改讀者信息。3)還書處理。讀者歸還圖書時，更新圖書借閱信息表中的歸還日期，讀者信息 表中的已借數(shù)量及ISBN類別信息表中該圖書的可借數(shù)量。(3)查詢和統(tǒng)計功能1)圖書查詢功能。根據(jù)圖書的各種已知條件來查詢圖書的詳細信息，如書名、 作者、ISBN書號等支持模糊查詢。2)讀者信息查詢。輸入讀者的借書證號、工作部門等信息，查詢讀者的基本信 息。對查詢到的每一個讀者，能夠顯示其未歸還的圖書編號和書名。3)查詢所有到期未歸還的圖書信息。要求結(jié)果顯示

4、圖書編號、書名、讀者、借 書證、借出日期等信息。4)統(tǒng)計指定讀者一段時間的某類圖書或所有類別圖書借閱次數(shù)及借閱總次數(shù)。3.數(shù)據(jù)表的創(chuàng)建根據(jù)功能要求的說明創(chuàng)建下列數(shù)據(jù)表：(1)圖書ISBN類別信息表圖書ISBN類別信息表包括以下字段：ISBN書號、圖書類別、書名、作者、出版日期、價格、館藏數(shù)量、可借數(shù)量、 圖書簡介。(2)圖書信息表圖書信息表包括以下字段：ISBN書號、圖書書號、是否可借。(3)讀者信息表讀者信息表包括以下字段：借書證號、性別、出生年月、身份證號、職稱、可借數(shù)量、已借數(shù)量、工作部 門、家庭地址、聯(lián)系。(4)借閱信息表(圖書-讀者關(guān)系)借閱信息表包括以下字段：借書證號、借閱書號、借

5、出日期、借閱期限、歸還日期。(5)數(shù)據(jù)表結(jié)構(gòu)如下表：讀者彳言息表附：紅色表主鍵，藍色表外鍵字段名稱數(shù)據(jù)類型字段長度是否為空借書證號varchar10否varchar8否性別char2否出生年月datetime否身份證號varchar18否職稱char10否可借數(shù)量int否已借數(shù)量int否工作部門varchar100是家庭地址varchar100是聯(lián)系varchar15是借閱表字段名稱數(shù)據(jù)類型字段長度是否為空序號int否借書證號char10否借閱書號char20否借閱日期datetime否借閱期限int否歸還日期datetime否ISBN信息表字段名稱數(shù)據(jù)類型字段長度是否為空ISBN書號char

6、20否圖書書號char20否是否可借char2否圖書表字段名稱數(shù)據(jù)類型字段長度是否為空ISNBN書號char20否圖書類別char20否書名varchar60否作者char10否varchar60否出版日期datetime否價格money否館藏數(shù)量int否可借數(shù)量int否圖書簡介varchar100是4.數(shù)據(jù)庫完整性設(shè)計設(shè)計者應(yīng)認真分析和思考各個表之間的關(guān)系，合理設(shè)計和實施數(shù)據(jù)完整性原則1）給每個表實施主鍵及外鍵約束。2）設(shè)定缺省約束。如性別。use JDGLgoalter table dbo. 讀者add constraint dz_xbiedefault ' 男'for 性

7、別go3） 設(shè)置非空約束如圖書信息表中的書名。use JDGLgoalter table dbo . 圖書表add constraint tu_sm - 約束名default not null for 書名4）實施CHEC的束。如ISBN類別表中的可借數(shù)量小于館藏數(shù)量。use JDGLgoalter table dbo . ISBN信息表add constraint du_kj_kccheck （ 可借數(shù)量館藏數(shù)量）print ' 可借數(shù)量要小于館藏數(shù)量'goalter table dbo . 借閱表- 為借閱表里字段歸還時間要大于借閱時間建立約束add constraint

8、jy_jysj_ghsjcheck （ 歸還時間借閱時間）goalter table dbo . 借閱表- 為借閱期限建立約束add constraint jy_jyqxcheck （ 借閱期限 =30）go5）實施規(guī)則。如身份證必須為18 位。use JDGLgoalter table dbo . 讀者add constraintdz_sfzcheck （ len （ 身份證號 ）= 18）5.SQL Server 數(shù)據(jù)庫對象設(shè)計1）設(shè)計一個存儲過程，以圖書ISBN編號為輸入?yún)?shù)，返回借閱該圖書但未歸還的讀者和借書證號。 Is null =null =create proc ds_ISBNI

9、SBN char （20）asselect , dbo. 讀者 . 借書證號from dbo . 讀者 join dbo . 借閱表 on dbo . 借閱表 . 借書證號 =dbo. 讀者 . 借書證號join dbo . 圖書信息表on dbo . 圖書信息表. 圖書書號 =dbo. 借閱表 . 借閱書號join dbo . ISBN信息on dbo .圖書信息表.ISBN書號=dbo. ISBN信息. ISBN書號where dbo .圖書信息表.ISBN書號=ISBNand （歸還時間is null）exec ds_ISBN '1001'-查詢ISBN編號為1001圖書

10、未還圖書的讀者和借書證號drop proc ds_ISBN -刪除此存儲過程以書名為輸入?yún)?shù)建立圖書信息的模糊查詢的存儲過程create proc du_sm smvarchar ( 40) asselect dbo . ISBN信息表*, dbo.圖書信息表.是否可借，圖書書號from dbo. ISBN信息表join dbo.圖書信息表 on dbo.圖書信息表.ISBN書號=dbo. ISBN信息表.ISBN 書號where 書名 like '%' +sm+'%' goexec du_sm 政 - 查詢政治這本書的模糊查詢2) 讀者資料查詢：設(shè)計一個有多個

11、輸入?yún)?shù)的存儲過程， 返回讀者的詳細信息。設(shè)計另一存儲過程并以讀者借書證號為輸入?yún)?shù)， 返回該讀者未歸還的圖書名稱 和圖書編號。以讀者和借書證為例作為輸入?yún)?shù)返回讀者基本信息的存儲過程create proc du_jszh_xm - 此存儲過程名稱jszh char ( 10), - 借書證號 xmchar (8)-as select * from dbo . 讀者where 借書證號 =jszh and =xmexec du_jszh_xm '001' , ' 三 ' - 查詢此存儲過程 以借書證號作為輸入?yún)?shù)返回該讀者未歸還的圖書名稱和書號create pr

12、oc du_jszhjszh char ( 10)- 借書證號asselect dbo .讀者.借書證號，dbo.圖書信息表.圖書書號，dbo. ISBN信息41 .書名from dbo . 讀者 join dbo . 借閱表 on dbo . 讀者 . 借書證號 =dbo. 借閱表 . 借書證號join dbo . 圖書信息表on dbo . 借閱表 . 借閱書號 =dbo. 圖書信息表. 圖書書號join dbo . ISBN信息on dbo .圖書信息表.ISBN書號=dbo. ISBN信息. ISBN書號 where dbo . 借閱表 . 歸還時間 is null and dbo .

13、 讀者 . 借書證號 =jszh ;drop proc du_jszh - 刪除此存儲過程exec du_jszh '001' - 查詢借書證號為001的讀者未歸還的圖書信息3)到期圖書查詢：設(shè)計一個視圖，返回所有逾期未歸還的圖書的編號、書名、讀者等信息。 Datepart(day, 借閱日期， getdate() create view jybiao asselect dbo .讀者.,dbo.圖書信息表.圖書書號，dbo. ISBN信息.書名from dbo . 讀者 join dbo . 借閱表 on dbo . 讀者 . 借書證號 =dbo. 借閱表 . 借書證號joi

14、n dbo . 圖書信息表on dbo . 借閱表 . 借閱書號 =dbo. 圖書信息表. 圖書書號join dbo . ISBN信息on dbo .圖書信息表.ISBN書號=dbo. ISBN信息. ISBN書號where（ getdate （）（ 借閱時間 +借閱期限 ）and （ 歸還時間 is null）- 建立所有逾期圖書的視圖select *from jybiao - 查詢視圖表4）統(tǒng)計圖書借閱次數(shù)：設(shè)計一個以兩個日期作為輸入?yún)?shù)的存儲過程，計算這一段時間各類別圖書被借閱的次數(shù)，返回圖書類別、借閱次數(shù)的信息。create proc ISBN_jysj -cssj datetime

15、, - 初始時間jcsj datetime - 結(jié)束時間 asselect dbo . ISBN信息.圖書類別，count （dbo. ISBN信息. ISBN書號）as 次數(shù) from dbo . 讀者 join dbo . 借閱表 on dbo . 借閱表 . 借書證號 =dbo. 讀者 . 借書證號 join dbo . 圖書信息表on dbo . 圖書信息表. 圖書書號 =dbo. 借閱表 . 借閱書號join dbo . ISBN信息on dbo .圖書信息表.ISBN書號=dbo. ISBN信息. ISBN書號 where 借閱時間 =cssj and jcsj =借閱時間grou

16、p by dbo . ISBN信息.圖書類別，dbo. ISBN信息表.ISBN書號設(shè)以兩個輸入的時間參數(shù)exec ISBN_jysj '2009.4.3', '2011.4.3' - 以這兩個時間參數(shù)為例查詢該存儲過程5）加快數(shù)據(jù)檢索速度，用圖書編號為圖書信息表建立索引。create index dsxxb_tssh_index on dbo . 圖書信息表（ 圖書書號 ）以圖書編號為圖書創(chuàng)建索引名為 dsxxb_tssh_index6）為讀者信息表創(chuàng)建一個刪除觸發(fā)器，當(dāng)一個讀者調(diào)出本單位時，將此讀者的資料從讀者信息表中刪除。 注意實施業(yè)務(wù)規(guī)則： 有借閱書的讀

17、者不得從讀者信息表中刪除。create trigger delete_duzh - 讀者on dbo . 讀者 for delete asif （ select 已借數(shù)量 from dbo . 借閱表 inner join deletedon dbo . 借閱表 . 借書證號 =deleted . 借書證號 ） 0 beginprint （ ' 還有書未還不可刪除該條數(shù)據(jù)' ）rollback transaction endelseprint （ ' 記錄已刪除' ）drop trigger delete_duzh-刪除讀者觸發(fā)器7）借閱處理：為借閱信息表設(shè)計I

18、NSERTS發(fā)器，在讀者借閱時更改ISBN類別信息表，且可借數(shù)量減1，圖書信息表是否可借列的值變?yōu)椤安豢山琛?，讀者信息表中該類讀者已借閱數(shù)加 1。create trigger insert_cr on dbo . 借閱表for insertasdeclarejyshchar（20）- 借閱書號declarejszhchar（10）- 借書證號declareISBNchar（20）-ISBN書號select jysh =inserted . 借閱書號 , jszh=inserted . 借書證號 , ISBN=（ select ISBN 書號 from dbo. 圖書信息表where dbo

19、. 圖書信息表. 圖書書號 =inserted . 借閱書號 ）from inserted , dbo. 借閱表if jysh not in （ select 圖書書號 from dbo . 圖書信息表）begin print （' 該圖書書號不存在 ' ） rollback transaction endelse if jszh not in （ select 借書證號 from dbo . 讀者 ） begin print （ ' 該借書證號不存在' ）rollback transactionendelse if ISBN not in （select ISBN書號from dbo . ISBN信息表） begin rollback transactionend update dbo . 讀者set 已借數(shù)量=已借數(shù)量+1, 可借數(shù)量=可借數(shù)量-1where dbo . 讀者 . 借書證號 =jszhupdate dbo . 圖書信息表 set dbo . 圖書信息表. 是否可借 =' 否'where dbo . 圖書信息表. 圖書書號 = jysh update dbo . I