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

1、網(wǎng)絡(luò)數(shù)據(jù)庫課程綜合性實驗報告_圖書借閱系統(tǒng)數(shù)據(jù)庫設(shè)計 華北科技學(xué)院計算機系綜合性實驗 實 驗 報 告 課程名稱 網(wǎng)絡(luò) 數(shù)據(jù)庫 實驗學(xué)期 至 學(xué)年 第 學(xué)期 學(xué)生所在系部 年級 專業(yè)班級 學(xué)生姓名 學(xué)號 任課教師 實驗成績 計算機系制 華北科技學(xué)院計算機系綜合性實驗報告 第 1 頁 實驗報告須知 1、 學(xué)生上交實驗報告時，必須為打印稿（a4 紙）。頁面空間不夠，可以順延。 2、 學(xué)生應(yīng)該填寫的內(nèi)容包括：封面相關(guān)欄目、實驗地點、時間、目的、設(shè)備環(huán)境、內(nèi)容、結(jié)果及分析等。 3、 教師應(yīng)該填寫的內(nèi)容包括：實驗成績、教師評價等。 4、 教師根據(jù)本課程的綜合性實驗指導(dǎo)單中實驗內(nèi)容的要求，評定學(xué)生的綜合性實

2、驗成績；要求在該課程期末考試前將實驗報告交給任課教師。綜合性實驗中，所涉及的程序，文檔等在交實驗報告前，拷貝給任課教師。任課教師統(tǒng)一刻錄成光盤，與該課程的期末考試成績一同上交到系里存檔。 5、 未盡事宜，請參考該課程的實驗大綱和教學(xué)大綱。 華北科技學(xué)院計算機系綜合性實驗報告 第 2 頁 網(wǎng)絡(luò) 數(shù)據(jù)庫 課程綜合性實驗報告 開課實驗室： 軟件 一室 室 1 年月 日 日 實驗題目 圖書借閱系統(tǒng)數(shù)據(jù)庫設(shè)計 一、實驗?zāi)康?通過該實驗把數(shù)據(jù)庫的理論知識（數(shù)據(jù)庫和數(shù)據(jù)表的設(shè)計理論、數(shù)據(jù)完整性的實現(xiàn)、存儲過程、觸發(fā)器、數(shù)據(jù)庫安全等）應(yīng)用到具體的綜合實例中，達到數(shù)據(jù)庫知識整合的目的。 二、設(shè)備與環(huán)境 硬件：多

3、媒體計算機 軟件：windowsxp 以上的操作系統(tǒng)、visual basic 或其它可視化語言及 sql server 2021 版本 三、實驗內(nèi)容及要求 1.數(shù)據(jù)庫設(shè)計 要求數(shù)據(jù)庫設(shè)計要合理，對數(shù)據(jù)庫設(shè)計作必要的說明并抓圖。 數(shù)據(jù)庫名必須與自己真實姓名有關(guān)，所有同學(xué)不能同名。 圖不要太大，看清即可。 2數(shù)據(jù)表設(shè)計 要求數(shù)據(jù)表設(shè)計要合理，要符合數(shù)據(jù)庫設(shè)計的理論范式，對數(shù)據(jù)表設(shè)計作必要的說明并抓圖。 數(shù)據(jù)表名必須與自己真實姓名有關(guān)，所有同學(xué)不能同名。 3視圖設(shè)計 要求根據(jù)系統(tǒng)需求作必要的視圖設(shè)計，如在一次查詢中涉及到多個表，應(yīng)該創(chuàng)建視圖。不可以只取一個表的幾個字段就算創(chuàng)建視圖。 4索引設(shè)計 要

4、求根據(jù)系統(tǒng)需求作必要的索引設(shè)計，本系統(tǒng)需要的聚集索引、非聚集索引、唯一索引、全文索引等。 5數(shù)據(jù)完整性設(shè)計 根據(jù)系統(tǒng)需求作必要的數(shù)據(jù)完整性設(shè)計，本系統(tǒng)需要的實體完整性體現(xiàn)、域完整性體現(xiàn)、參照完整性體現(xiàn)等。 6存儲過程和觸發(fā)器設(shè)計 根據(jù)系統(tǒng)需求作必要的存儲過程和觸發(fā)器設(shè)計，本系統(tǒng)需要的存儲過程和觸發(fā)器設(shè)計。必要的存儲過程和觸發(fā)器設(shè)計都要寫全說明，圖可以是一個表的完整存儲 華北科技學(xué)院計算機系綜合性實驗報告 第 3 頁 過程或觸發(fā)器。 7備份與恢復(fù)設(shè)計 根據(jù)系統(tǒng)需求作必要的備份與恢復(fù)設(shè)計，如需要對那些內(nèi)容備份，備份策略、由誰來做備份、什么時間做備份等。 8數(shù)據(jù)庫安全設(shè)計 根據(jù)系統(tǒng)需求作必要的數(shù)據(jù)庫

5、安全設(shè)計，如本系統(tǒng)分幾級用戶、分別是什么角色成員具有什么操作權(quán)限等。 四、實驗結(jié)果及分析 1.數(shù)據(jù)庫設(shè)計 （一）設(shè)計思想 圖書管理系統(tǒng)數(shù)據(jù)庫的主要任務(wù)是對讀者信息、管理員信息、圖書資料信息、借閱歸還圖書信息、罰款信息的基本信息的操作及處理。此系統(tǒng)功能分為面向讀者和面向管理員兩部分，其中讀者可以進行借閱、續(xù)借、歸還和查詢書籍等操作，管理員可以完成書籍和讀者信息的增加，刪除和修改，對數(shù)據(jù)表維護。 根據(jù)以上功能分析，圖書管理系統(tǒng)應(yīng)該包含：讀者信息表、管理員信息表、圖書信息表、借閱信息表。 針對圖書管理系統(tǒng)目前的用戶量（假設(shè)為 15000 人）和圖書量（假設(shè)為 10 萬冊）考慮，系統(tǒng)主數(shù)據(jù)文件初始大小

6、設(shè)置為：110m（15000*512byte（讀者表一條記錄大?。? 100000*1024byte（圖書表一條記錄大?。? 其他表大?。詣釉鲩L設(shè)置為 20m；日志文件設(shè)置為：5m，自動增長設(shè)置為 5m。 考慮圖書管理系統(tǒng)最經(jīng)常的操作是查找，所以為了提高查找效率，應(yīng)為圖書表、讀者表建立索引。 （二）具體實現(xiàn) 通過 sql 語句來創(chuàng)建圖書管理數(shù)據(jù)庫，創(chuàng)建代碼如下： create database tsgl_133 on ( name = "tsgl", filename = "j:tsgl.mdf", size = 110 mb, maxsize =

7、unlimited, filegrowth = 20 mb ) log on ( name = "tsgl_log", filename = "j:tslg_log.ldf", size = 5 mb, maxsize = unlimited, filegrowth = 5 mb ); 華北科技學(xué)院計算機系綜合性實驗報告 第 4 頁 2數(shù)據(jù)表設(shè)計 （一）設(shè)計思想 根據(jù)以上對圖書管理系統(tǒng)的功能分析，需要設(shè)計讀者信息表、管理員信息表、圖書信息表和借閱信息表。 （1）讀者表(學(xué)號、姓名、性別、系部、專業(yè)班級、借閱數(shù)量、欠罰款、聯(lián)系電話) 讀者表主要記錄讀者信息

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

9、置等信息。 （4）借閱表（圖書編號、學(xué)號、借閱日期、應(yīng)還日期、歸還日期、罰款金額、繳納狀態(tài)） 讀者通過借閱表查詢借書情況，例如：借閱日期、應(yīng)還日期、歸還日期等信息。 （二）具體實現(xiàn) 通過 sql 語句來創(chuàng)建圖書管理數(shù)據(jù)表，創(chuàng)建代碼如下： use tsgl_133 go create table t_reader ( 學(xué)號 char(12) not null primary key, 姓名 nchar(4) not null, 性別 bit null default 1, 系部 nchar(10) null, 專業(yè)班級 nchar(8) null, 借閱數(shù)量 int null default 0

10、, 欠罰款 float null, 聯(lián)系電話 char(11) null ) go create 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, ) go 華北科技學(xué)院計算機系綜合性實驗報告 第 5 頁 create table t_book ( 圖書編號 int id

11、entity 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, ) go create table t_lend ( 圖書編號 int not null, 學(xué)號 char(12) not null, 借閱日期 date not null, 應(yīng)還日期 date not null,

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

13、金額。其中，罰款金額通過計算得出。 （4）讀者需要查看圖書信息，所以建立圖書信息視圖，其字段包括：isbn 條碼、書名、圖書總量、可外借數(shù)量、館藏位置。其中圖書總量和可外借數(shù)量通過計算得出。 （5）為數(shù)據(jù)庫所有表創(chuàng)建單獨的視圖，如圖書表視圖、管理員表視圖、借閱表視圖和讀者表視圖。 （二）具體實現(xiàn) （1）讀者借閱情況視圖實現(xiàn)代碼： use tsgl_133 go create view reader_lended_view as 華北科技學(xué)院計算機系綜合性實驗報告 第 6 頁 select t_reader.學(xué)號,姓名,性別=( case when 性別=1 then "男"

14、 else "女" end),系部,專業(yè),班級, 圖書編號,借閱日期,應(yīng)還日期,歸還日期,罰款金額,繳納狀態(tài) from t_lend,t_reader where t_lend.學(xué)號 = t_reader.學(xué)號 （2）圖書借閱情況視圖實現(xiàn)代碼： use tsgl_133 go create view book_lended_view as select t_book.圖書編號,isbn 條碼,書名,借閱狀態(tài)="被借閱", 借閱日期,應(yīng)還日期 from t_book,t_lend where t_book.圖書編號=t_lend.圖書編號 and 借閱狀態(tài)

15、 = 1 （3）逾期未還圖書信息視圖使用 getdate()函數(shù)獲取系統(tǒng)時間；使用借閱表中"應(yīng)還日期'字段信息與系統(tǒng)當(dāng)前日期對比判斷讀者借閱書籍是否超期未還；使用 datediff()函數(shù)計算讀者借閱書籍的超期天數(shù)并求出相應(yīng)的罰款金額。（注：罰款金額以超出一天罰款 0.1 元計算） 逾期未還圖書信息視圖具體實現(xiàn)代碼如下： use tsgl_133 go create view not_return_view as select t_lend.學(xué)號,姓名,系部,專業(yè)班級,書名,借閱日期,應(yīng)還日期,罰款金額 = convert(float,datediff(day,應(yīng)還日期,ca

16、st(left(getdate(),10) as date)/10 from t_lend,t_book,t_reader where t_lend.學(xué)號 = t_reader.學(xué)號 and t_lend.圖書編號 = t_book.圖書編號 and 歸還日期 is null and cast(left(getdate(),10) as date) gt; 應(yīng)還日期 上述代碼運行結(jié)果如下圖"圖 1 超期未還讀者'。 圖 圖 1 超期未還讀者 （4）讀者查閱圖書信息視圖實現(xiàn)代碼如下： use tsgl_133 go create view bookinfo_reader_vie

17、w as select distinct t_book.isbn 條碼,書名,圖書總量,可借閱數(shù)量,館藏位置 from t_book, (select isbn 條碼,圖書總量 = count(isbn 條碼) from t_book 華北科技學(xué)院計算機系綜合性實驗報告 第 7 頁 group by isbn 條碼) as book1, (select isbn 條碼,可借閱數(shù)量 = count(isbn 條碼) from t_book where 借閱狀態(tài) = 0 group by isbn 條碼) as book2 where t_book.isbn 條碼 = book1.isbn 條碼

18、and t_book.isbn 條碼 = book2.isbn 條碼 上述代碼運行結(jié)果： 圖 圖 2 讀者查閱圖書信息視圖 （5）讀者表視圖實現(xiàn)代碼： use tsgl_133 go create view reader_view as select * from t_reader 管理員表視圖、圖書表視圖和借閱表視圖與上述代碼類似，此處不重復(fù)列出。 4索引的建立 （一）設(shè)計思想 為了提高搜索效率，應(yīng)該為表建立相關(guān)索引。 （1） 對讀者表建立索引 在建立讀者表時，已經(jīng)對讀者表的"學(xué)號'字段建立了主鍵索引，且其為聚集索引，為了提高對讀者表的檢索效率，還需要為經(jīng)常查詢的字段增加非

19、聚集索引，例如為"姓名' 華北科技學(xué)院計算機系綜合性實驗報告 第 8 頁 字段建立非聚集索引。 （2）對圖書表建立索引 在建立圖書表時，已經(jīng)對圖書表的"圖書編號'字段建立了主鍵索引，且其為聚集，為了提高對圖書表的檢索效率，還需要為該表添加"isbn 條碼'字段和"書名'字段建立非聚集索引。 （二）具體實現(xiàn) （1）為讀者表建立"姓名'字段非聚集索引 圖 圖 3 新建 讀者表"姓名'字段索引 5數(shù)據(jù)完整性設(shè)計 （一）設(shè)計思想 圖書管理系統(tǒng)包括：圖書表、讀者表、借閱表和管理員表。下面分別就實體

20、完整性、域完整性、參照完整性分析設(shè)計數(shù)據(jù)庫完整性。 （1）實體完整性 圖書表根據(jù)"圖書編號'字段唯一表示一條記錄，所以定義"圖書編號'為主鍵來實現(xiàn)圖書表的實體完整性。 讀者表定義"學(xué)號'為主鍵實現(xiàn)實體完整性；借閱表定義"學(xué)號'和"圖書編號'兩 華北科技學(xué)院計算機系綜合性實驗報告 第 9 頁 個組合字段為主鍵實現(xiàn)實體完整性；管理員表定義"工號'實現(xiàn)實體完整性。 （2）域完整性 設(shè)定圖書表 "isbn 條碼'、"書名'字段不為空，實現(xiàn)圖書表域完整性；設(shè)定讀

21、者表"姓名'字段設(shè)定不為空，實現(xiàn)讀者表域完整性；設(shè)置借閱表中"借閱日期'、"應(yīng)還日期'不為空實現(xiàn)借閱表域完整性；設(shè)置管理員信息表中"姓名'、"管理員身份'、"登陸口令'、"登陸密碼'字段設(shè)置不為空實現(xiàn)管理員表域完整性。 （3）參照完整性 對讀者表、借閱表和圖書表建立表關(guān)系。其中讀者表與借閱表通過"學(xué)號'關(guān)聯(lián)，借閱表"學(xué)號'字段參照讀者表"學(xué)號'字段，是讀者表的外鍵；圖書表與借閱表通過"圖書編號'關(guān)聯(lián)

22、，借閱表的"圖書編號'字段參照圖書表"圖書編號'字段，是圖書表的外鍵。 （二）具體實現(xiàn) 圖 圖 4 管理員表完整性設(shè)計 圖 圖 5 借閱表完整性設(shè)計 華北科技學(xué)院計算機系綜合性實驗報告 第 10 頁 圖 圖 6 圖書表完整性設(shè)計 圖 圖 7 讀者表完整性 圖 圖 8 數(shù)據(jù)表關(guān)系圖 華北科技學(xué)院計算機系綜合性實驗報告 第 11 頁 6存儲過程和觸發(fā)器設(shè)計 （一）設(shè)計思想 （1）存儲過程設(shè)計 讀者需要查詢自己的借閱情況，需要建立根據(jù)學(xué)號查詢該讀者借閱情況；讀者需要檢索圖書信息，需要建立根據(jù)圖書名查詢圖書信息。 （2）觸發(fā)器設(shè)計 讀者借書時，觸發(fā)借閱表插入記錄的觸

23、發(fā)器，執(zhí)行操作包括：填補借閱表"應(yīng)還時間'字段信息，即將"應(yīng)還時間'設(shè)置為"借閱時間'開始計算的后 2 個月的時間；設(shè)置讀者表"借閱數(shù)量字段'增加 1；設(shè)置圖書表"借閱狀態(tài)'字段為"true'。 讀者還書時，設(shè)置讀者表 "借閱數(shù)量'減 1；設(shè)置該圖書"借閱狀態(tài)'為"false'；判斷讀者"還書時間'與"應(yīng)還時間'關(guān)系，如果"還書時間'超出了"應(yīng)還時間'，計算罰款金

24、額，更新到該條借閱記錄的"罰款金額'字段，并將罰款金額累加到讀者表該讀者的"欠罰款'字段。 （二）具體實現(xiàn) （1）存儲過程實現(xiàn) 根據(jù)學(xué)號查詢該讀者借閱情況的存儲過程實現(xiàn)代碼： use tsgl_133 go create procedure stulendinfo_byxuehao xuehao char(12) as select t_reader.學(xué)號,姓名,(case when 性別 = 1 then "男" else "女" end) as 性別,系部, 專業(yè)班級,圖書編號,借閱日期,應(yīng)還日期,歸還日期,罰款金

25、額,繳納狀態(tài) from t_lend inner join t_reader on t_lend.學(xué)號 = t_reader.學(xué)號 where t_reader.學(xué)號=xuehao go 根據(jù)圖書名查詢圖書信息的實現(xiàn)代碼： use tsgl_133 go create procedure bookinfo_byboonname bookname nchar(30) as select distinct t_book.isbn 條碼,書名,圖書總量,可借閱數(shù)量,館藏位置 from t_book, (select isbn 條碼,圖書總量 = count(isbn 條碼) from t_book

26、group by isbn 條碼) as book1, (select isbn 條碼,可借閱數(shù)量 = count(isbn 條碼) from t_book where 借閱狀態(tài) = 0 group by isbn 條碼) as book2 where t_book.isbn 條碼 = book1.isbn 條碼 and t_book.isbn 條碼 = book2.isbn 條碼 and 書名 like bookname go 華北科技學(xué)院計算機系綜合性實驗報告 第 12 頁 （2）觸發(fā)器實現(xiàn) 借閱表插入記錄的觸發(fā)器實現(xiàn)代碼： use tsgl_133 go create trigger l

27、end_insert on t_lend after insert as begin declare booknum int,readernum nchar(12),lendcount int,lenddate date select booknum=圖書編號,readernum=學(xué)號,lenddate=借閱日期 from inserted select lendcount=借閱數(shù)量 from t_reader where 學(xué)號=readernum update t_book set 借閱狀態(tài)=1 where 圖書編號=booknum update t_lend set 應(yīng)還日期=datead

28、d(m,2,借閱日期) update t_reader set 借閱數(shù)量=lendcount+1 where 學(xué)號=readernum print "圖書借閱狀態(tài)已修改！" print "讀者借閱數(shù)量已修改！" end 借閱表"歸還日期'字段的觸發(fā)器實現(xiàn)代碼： use tsgl_133 go create trigger lend_update on t_lend after update as begin if(update(歸還日期) begin declare booknum int,readernum nchar(12), le

29、ndcount int,returndate date,money float select booknum=圖書編號,readernum=學(xué)號,returndate=應(yīng)還日期 from deleted select lendcount=借閱數(shù)量,money=欠罰款 from t_reader where 學(xué)號=readernum update t_book set 借閱狀態(tài)=0 where 圖書編號=booknum update t_reader set 借閱數(shù)量=lendcount-1 where 學(xué)號=readernum print "圖書借閱狀態(tài)已修改！" print "讀者借閱數(shù)量已修改！" end if(cast(left(getdate(),10) as date) gt; returndate) begin update t_lend set 罰款金額=conve