《網絡數(shù)據庫》課程綜合性實驗報告圖書借閱系統(tǒng)數(shù)據庫設計

1、華北科技學院計算機系綜合性實驗報告華北科技學院計算機系綜合性實驗實 驗 報 告 課程名稱 網絡數(shù)據庫 實驗學期 至 學年 第 學期學生所在系部 年級 專業(yè)班級 學生姓名 學號 任課教師 實驗成績 計算機系制實驗報告須知1、 學生上交實驗報告時，必須為打印稿（a4紙）。頁面空間不夠，可以順延。2、 學生應該填寫的內容包括：封面相關欄目、實驗地點、時間、目的、設備環(huán)境、內容、結果及分析等。3、 教師應該填寫的內容包括：實驗成績、教師評價等。4、 教師根據本課程的綜合性實驗指導單中實驗內容的要求，評定學生的綜合性實驗成績；要求在該課程期末考試前將實驗報告交給任課教師。綜合性實驗中，所涉及的程序，文檔

2、等在交實驗報告前，拷貝給任課教師。任課教師統(tǒng)一刻錄成光盤，與該課程的期末考試成績一同上交到系里存檔。5、 未盡事宜，請參考該課程的實驗大綱和教學大綱。網絡數(shù)據庫課程綜合性實驗報告開課實驗室：軟件一室 1 年月 日實驗題目圖書借閱系統(tǒng)數(shù)據庫設計一、實驗目的通過該實驗把數(shù)據庫的理論知識（數(shù)據庫和數(shù)據表的設計理論、數(shù)據完整性的實現(xiàn)、存儲過程、觸發(fā)器、數(shù)據庫安全等）應用到具體的綜合實例中，達到數(shù)據庫知識整合的目的。二、設備與環(huán)境硬件：多媒體計算機軟件：windowsxp以上的操作系統(tǒng)、visual basic或其它可視化語言及sql server 2008版本三、實驗內容及要求1.數(shù)據庫設計要求數(shù)據庫

3、設計要合理，對數(shù)據庫設計作必要的說明并抓圖。數(shù)據庫名必須與自己真實姓名有關，所有同學不能同名。圖不要太大，看清即可。2數(shù)據表設計要求數(shù)據表設計要合理，要符合數(shù)據庫設計的理論范式，對數(shù)據表設計作必要的說明并抓圖。數(shù)據表名必須與自己真實姓名有關，所有同學不能同名。3視圖設計要求根據系統(tǒng)需求作必要的視圖設計，如在一次查詢中涉及到多個表，應該創(chuàng)建視圖。不可以只取一個表的幾個字段就算創(chuàng)建視圖。4索引設計要求根據系統(tǒng)需求作必要的索引設計，本系統(tǒng)需要的聚集索引、非聚集索引、唯一索引、全文索引等。5數(shù)據完整性設計根據系統(tǒng)需求作必要的數(shù)據完整性設計，本系統(tǒng)需要的實體完整性體現(xiàn)、域完整性體現(xiàn)、參照完整性體現(xiàn)等。6

4、存儲過程和觸發(fā)器設計根據系統(tǒng)需求作必要的存儲過程和觸發(fā)器設計，本系統(tǒng)需要的存儲過程和觸發(fā)器設計。必要的存儲過程和觸發(fā)器設計都要寫全說明，圖可以是一個表的完整存儲過程或觸發(fā)器。7備份與恢復設計根據系統(tǒng)需求作必要的備份與恢復設計，如需要對那些內容備份，備份策略、由誰來做備份、什么時間做備份等。8數(shù)據庫安全設計根據系統(tǒng)需求作必要的數(shù)據庫安全設計，如本系統(tǒng)分幾級用戶、分別是什么角色成員具有什么操作權限等。四、實驗結果及分析1.數(shù)據庫設計（一）設計思想圖書管理系統(tǒng)數(shù)據庫的主要任務是對讀者信息、管理員信息、圖書資料信息、借閱歸還圖書信息、罰款信息的基本信息的操作及處理。此系統(tǒng)功能分為面向讀者和面向管理員兩

5、部分，其中讀者可以進行借閱、續(xù)借、歸還和查詢書籍等操作，管理員可以完成書籍和讀者信息的增加，刪除和修改，對數(shù)據表維護。根據以上功能分析，圖書管理系統(tǒng)應該包含：讀者信息表、管理員信息表、圖書信息表、借閱信息表。針對圖書管理系統(tǒng)目前的用戶量（假設為15000人）和圖書量（假設為10萬冊）考慮，系統(tǒng)主數(shù)據文件初始大小設置為：110m（15000*512byte（讀者表一條記錄大?。? 100000*1024byte（圖書表一條記錄大?。? 其他表大?。?，自動增長設置為20m；日志文件設置為：5m，自動增長設置為5m?？紤]圖書管理系統(tǒng)最經常的操作是查找，所以為了提高查找效率，應為圖書表、讀者表建立索引

6、。（二）具體實現(xiàn) 通過sql語句來創(chuàng)建圖書管理數(shù)據庫，創(chuàng)建代碼如下：create database tsgl_133on(name = tsgl,filename = j:tsgl.mdf,size = 110 mb,maxsize = unlimited,filegrowth = 20 mb)log on(name = tsgl_log,filename = j:tslg_log.ldf,size = 5 mb,maxsize = unlimited,filegrowth = 5 mb);2數(shù)據表設計（一）設計思想根據以上對圖書管理系統(tǒng)的功能分析，需要設計讀者信息表、管理員信息表、圖書信息表

7、和借閱信息表。（1）讀者表(學號、姓名、性別、系部、專業(yè)班級、借閱數(shù)量、欠罰款、聯(lián)系電話)讀者表主要記錄讀者信息，通過讀者表可以查看讀者的具體信息，如：學號、姓名、性別、系部、專業(yè)班級、借閱數(shù)量、欠罰款等。（2）管理員表(工號、姓名、性別、管理員身份、登陸口令、登陸密碼、聯(lián)系電話)管理員表主要記錄管理員信息，通過管理員表可以查詢管理員信息以及用于驗證管理員身份及其管理權限。（3）圖書表（圖書編號、isbn條碼、書名、作者、出版社、出版日期、單價、分類、借閱狀態(tài)、管藏位置）圖書表主要記錄圖書的各種信息，包括：isbn條碼、書名、作者、出版社、出版日期、單價、分類、借閱狀態(tài)、管藏位置。讀者可以通過

8、查詢此表得到需要的與圖書相關的信息。例如：讀者可以通過查詢圖書的isbn號，查看該圖書當前的借閱狀態(tài)和館藏位置等信息。（4）借閱表（圖書編號、學號、借閱日期、應還日期、歸還日期、罰款金額、繳納狀態(tài)）讀者通過借閱表查詢借書情況，例如：借閱日期、應還日期、歸還日期等信息。（二）具體實現(xiàn)通過sql語句來創(chuàng)建圖書管理數(shù)據表，創(chuàng)建代碼如下：use tsgl_133gocreate table t_reader(學號 char(12) not null primary key,姓名 nchar(4) not null,性別 bit null default 1,系部 nchar(10) null,專業(yè)班級

9、 nchar(8) null,借閱數(shù)量 int null default 0,欠罰款 float null,聯(lián)系電話 char(11) null)gocreate table t_admin(工號 char(12) not null primary key,姓名 nchar(4) not null,性別 bit null default 1,管理員身份 nchar(10) not null,登陸口令 char(12) not null,登陸密碼 char(16) not null,聯(lián)系電話 char(11) null,)gocreate table t_book(圖書編號 int identi

10、ty primary key,isbn條碼 char(20) not null,書名 nchar(30) not null,作者 nchar(20) null,出版社 nchar(20) null,出版日期 date null,單價 float null,分類 nchar(10) null,借閱狀態(tài) bit null,館藏位置 nchar(20) null,)gocreate table t_lend(圖書編號 int not null,學號 char(12) not null,借閱日期 date not null,應還日期 date not null,歸還日期 date not null,罰

11、款金額 float null,繳納狀態(tài) bit null,primary key(圖書編號,學號)go3視圖設計（一）設計思想（1）為了查詢讀者借閱情況時方便，建立讀者借閱情況視圖。其字段信息包括：學號、姓名、性別、系部、專業(yè),班級、圖書編號、借閱日期、應還日期、歸還日期、罰款金額、繳納狀態(tài)（2）建立圖書借閱情況視圖。其字段信息包括：圖書編號、isbn、書名、借閱狀態(tài)，借閱日期，應還日期。（3）工作人員需要查看當前逾期未還的圖書，所以需要建立逾期未還圖書信息視圖，其字段包括：學號、姓名、系部、專業(yè)班級、書名、借閱日期、應還日期、罰款金額。其中，罰款金額通過計算得出。（4）讀者需要查看圖書信息，

12、所以建立圖書信息視圖，其字段包括：isbn條碼、書名、圖書總量、可外借數(shù)量、館藏位置。其中圖書總量和可外借數(shù)量通過計算得出。（5）為數(shù)據庫所有表創(chuàng)建單獨的視圖，如圖書表視圖、管理員表視圖、借閱表視圖和讀者表視圖。（二）具體實現(xiàn)（1）讀者借閱情況視圖實現(xiàn)代碼：use tsgl_133gocreate view reader_lended_viewasselect t_reader.學號,姓名,性別=(case when 性別=1 then 男 else 女 end),系部,專業(yè),班級,圖書編號,借閱日期,應還日期,歸還日期,罰款金額,繳納狀態(tài)from t_lend,t_readerwhere t

13、_lend.學號 = t_reader.學號（2）圖書借閱情況視圖實現(xiàn)代碼：use tsgl_133gocreate view book_lended_view as select t_book.圖書編號,isbn條碼,書名,借閱狀態(tài)=被借閱,借閱日期,應還日期 from t_book,t_lend where t_book.圖書編號=t_lend.圖書編號 and 借閱狀態(tài) = 1（3）逾期未還圖書信息視圖使用getdate()函數(shù)獲取系統(tǒng)時間；使用借閱表中“應還日期”字段信息與系統(tǒng)當前日期對比判斷讀者借閱書籍是否超期未還；使用 datediff()函數(shù)計算讀者借閱書籍的超期天數(shù)并求出相應的

14、罰款金額。（注：罰款金額以超出一天罰款0.1元計算）逾期未還圖書信息視圖具體實現(xiàn)代碼如下：use tsgl_133gocreate view not_return_viewas select t_lend.學號,姓名,系部,專業(yè)班級,書名,借閱日期,應還日期,罰款金額 = convert(float,datediff(day,應還日期,cast(left(getdate(),10) as date)/10 from t_lend,t_book,t_reader where t_lend.學號 = t_reader.學號 and t_lend.圖書編號 = t_book.圖書編號 and 歸還日

15、期 is null and cast(left(getdate(),10) as date) 應還日期 上述代碼運行結果如下圖“圖1 超期未還讀者”。圖1 超期未還讀者（4）讀者查閱圖書信息視圖實現(xiàn)代碼如下：use tsgl_133gocreate view bookinfo_reader_view asselect distinct t_book.isbn條碼,書名,圖書總量,可借閱數(shù)量,館藏位置from t_book, (select isbn條碼,圖書總量 = count(isbn條碼)from t_bookgroup by isbn條碼) as book1, (select isbn條

16、碼,可借閱數(shù)量 = count(isbn條碼)from t_bookwhere 借閱狀態(tài) = 0group by isbn條碼) as book2where t_book.isbn條碼 = book1.isbn條碼 and t_book.isbn條碼 = book2.isbn條碼上述代碼運行結果：圖2 讀者查閱圖書信息視圖（5）讀者表視圖實現(xiàn)代碼：use tsgl_133gocreate view reader_viewas select *from t_reader管理員表視圖、圖書表視圖和借閱表視圖與上述代碼類似，此處不重復列出。4索引的建立（一）設計思想為了提高搜索效率，應該為表建立相關

17、索引。（1） 對讀者表建立索引在建立讀者表時，已經對讀者表的“學號”字段建立了主鍵索引，且其為聚集索引，為了提高對讀者表的檢索效率，還需要為經常查詢的字段增加非聚集索引，例如為“姓名”字段建立非聚集索引。（2）對圖書表建立索引在建立圖書表時，已經對圖書表的“圖書編號”字段建立了主鍵索引，且其為聚集，為了提高對圖書表的檢索效率，還需要為該表添加“isbn條碼”字段和“書名”字段建立非聚集索引。（二）具體實現(xiàn)（1）為讀者表建立“姓名”字段非聚集索引圖3 新建讀者表“姓名”字段索引5數(shù)據完整性設計（一）設計思想圖書管理系統(tǒng)包括：圖書表、讀者表、借閱表和管理員表。下面分別就實體完整性、域完整性、參照完

18、整性分析設計數(shù)據庫完整性。（1）實體完整性圖書表根據“圖書編號”字段唯一表示一條記錄，所以定義“圖書編號”為主鍵來實現(xiàn)圖書表的實體完整性。讀者表定義“學號”為主鍵實現(xiàn)實體完整性；借閱表定義“學號”和“圖書編號”兩個組合字段為主鍵實現(xiàn)實體完整性；管理員表定義“工號”實現(xiàn)實體完整性。（2）域完整性設定圖書表 “isbn條碼”、“書名”字段不為空，實現(xiàn)圖書表域完整性；設定讀者表“姓名”字段設定不為空，實現(xiàn)讀者表域完整性；設置借閱表中“借閱日期”、“應還日期”不為空實現(xiàn)借閱表域完整性；設置管理員信息表中“姓名”、“管理員身份”、“登陸口令”、“登陸密碼”字段設置不為空實現(xiàn)管理員表域完整性。（3）參照完

19、整性對讀者表、借閱表和圖書表建立表關系。其中讀者表與借閱表通過“學號”關聯(lián)，借閱表“學號”字段參照讀者表“學號”字段，是讀者表的外鍵；圖書表與借閱表通過“圖書編號”關聯(lián)，借閱表的“圖書編號”字段參照圖書表“圖書編號”字段，是圖書表的外鍵。（二）具體實現(xiàn)圖4 管理員表完整性設計圖5 借閱表完整性設計圖6 圖書表完整性設計圖7 讀者表完整性圖8 數(shù)據表關系圖6存儲過程和觸發(fā)器設計（一）設計思想（1）存儲過程設計讀者需要查詢自己的借閱情況，需要建立根據學號查詢該讀者借閱情況；讀者需要檢索圖書信息，需要建立根據圖書名查詢圖書信息。（2）觸發(fā)器設計讀者借書時，觸發(fā)借閱表插入記錄的觸發(fā)器，執(zhí)行操作包括：填

20、補借閱表“應還時間”字段信息，即將“應還時間”設置為“借閱時間”開始計算的后2個月的時間；設置讀者表“借閱數(shù)量字段”增加1；設置圖書表“借閱狀態(tài)”字段為“true”。讀者還書時，設置讀者表 “借閱數(shù)量”減1；設置該圖書“借閱狀態(tài)”為“false”；判斷讀者“還書時間”與“應還時間”關系，如果“還書時間”超出了“應還時間”，計算罰款金額，更新到該條借閱記錄的“罰款金額”字段，并將罰款金額累加到讀者表該讀者的“欠罰款”字段。（二）具體實現(xiàn)（1）存儲過程實現(xiàn)根據學號查詢該讀者借閱情況的存儲過程實現(xiàn)代碼：use tsgl_133gocreate procedure stulendinfo_byxueh

21、ao xuehao char(12)as select t_reader.學號,姓名,(case when 性別 = 1 then 男 else 女 end) as 性別,系部,專業(yè)班級,圖書編號,借閱日期,應還日期,歸還日期,罰款金額,繳納狀態(tài)from t_lend inner join t_reader on t_lend.學號 = t_reader.學號where t_reader.學號=xuehaogo根據圖書名查詢圖書信息的實現(xiàn)代碼：use tsgl_133gocreate procedure bookinfo_byboonname bookname nchar(30)as sele

22、ct distinct t_book.isbn條碼,書名,圖書總量,可借閱數(shù)量,館藏位置from t_book, (select isbn條碼,圖書總量 = count(isbn條碼)from t_bookgroup by isbn條碼) as book1, (select isbn條碼,可借閱數(shù)量 = count(isbn條碼)from t_bookwhere 借閱狀態(tài) = 0group by isbn條碼) as book2where t_book.isbn條碼 = book1.isbn條碼 and t_book.isbn條碼 = book2.isbn條碼 and 書名 like book

23、namego（2）觸發(fā)器實現(xiàn)借閱表插入記錄的觸發(fā)器實現(xiàn)代碼：use tsgl_133gocreate trigger lend_inserton t_lend after insertasbegin declare booknum int,readernum nchar(12),lendcount int,lenddate dateselect booknum=圖書編號,readernum=學號,lenddate=借閱日期 from insertedselect lendcount=借閱數(shù)量 from t_reader where 學號=readernumupdate t_book set 借

24、閱狀態(tài)=1 where 圖書編號=booknumupdate t_lend set 應還日期=dateadd(m,2,借閱日期)update t_reader set 借閱數(shù)量=lendcount+1 where 學號=readernumprint 圖書借閱狀態(tài)已修改！print 讀者借閱數(shù)量已修改！end借閱表“歸還日期”字段的觸發(fā)器實現(xiàn)代碼：use tsgl_133gocreate trigger lend_updateon t_lend after update asbegin if(update(歸還日期)begindeclare booknum int,readernum nchar

25、(12),lendcount int,returndate date,money floatselect booknum=圖書編號,readernum=學號,returndate=應還日期 from deletedselect lendcount=借閱數(shù)量,money=欠罰款 from t_reader where 學號=readernumupdate t_book set 借閱狀態(tài)=0 where 圖書編號=booknumupdate t_reader set 借閱數(shù)量=lendcount-1 where 學號=readernumprint 圖書借閱狀態(tài)已修改！print 讀者借閱數(shù)量已修改！endif(cast(left(getdate(),10) as date) returndate)beginupdate t_lend set 罰款金額=convert(float,datediff(day,returndate,cast(left(getdate(),10) as date)/10where 圖書編號=booknumupdate t_reader set 欠罰款=欠罰款+convert(float,datediff(day,returndate,