圖書借閱系統數據庫設計實驗報告-文庫吧

【正文】 些需要預先執行的任務。 （ 2）具體實現： 在數據庫 MBook203 中創建加密的讀者借書存儲過程，當向 Lend 表插入一條記錄 時： ①檢查該記錄的借書證號 ReaderID 在 Reader 表中是否存在； ②檢查該記錄的圖書 ISBN 號是否在 Book 表中是否存在； ③檢查該記錄的圖書庫存量 Invnum 是否 等于 0； 華北科技學院 計算機系綜合性實驗報告 第 11 頁 ④檢查該記錄是否借閱過 該圖書； ⑤檢查該記錄的 圖書 BookID 是否在 借閱表 Lend 中已經存在； 若有一項為否，則不允許插入，并回滾事務，否則，插入記錄并將讀者 表中該讀者借閱數量 加 1 ，圖書表中庫存量 減 1 ，并返回借書成功信息。 滿足上述條件，則開始一個事務，將借書記錄插入到借閱表中： 華北科技學院 計算機系綜合性實驗報告 第 12 頁 讀者表中借書數量（ Num）加 1，在上面語句中已經執行，接著執行：圖書表中的圖書庫存量（ Invnum） 減 1； 如果所有語句都不出錯，則提交事務并返回 借書成功 信息，否則，回滾事務，并返回錯誤信息。 在數據庫 MBook203 中創建加密的讀者還書存儲過程，當刪除 Lend 表中一條記錄時： ①檢查借閱表中是否存在該借閱記錄，如果不存在則回滾事務； ②將該記錄插入到歷史借閱表； ③將該記錄的還書日期 rbbt 獲取當前系統時間，并插入到對應記錄的還書日期字段； ④將該記錄的讀者借閱數量 減 1； ⑤將該記錄的圖書庫存量 加 1； ⑥返回還書成功信息； 若有一項為執行不成功，則不允許刪除，并回滾事務。 滿足上述條件，則開始一個事務，將還書記錄插入到歷史借閱表中： 華北科技學院 計算機系綜合性實驗報告 第 13 頁 接著執行： ①刪除借閱表中的借書記錄； ②更新讀者表中的借書數量（ Num） 減 1； ③圖書表中的圖書庫存量（ Invnum） 加 1； 如果 所有語句都不出錯，則提交事務并返回 還書成功 信息，否則，回滾事務，并返回錯誤信息。 超期未歸還圖書的借書記錄（當前借閱表中）、超期未歸還圖書的還書記錄（歷史借閱表中），讀者的借書證號、姓名、院部、專業、圖書 ISBN 號、書名以及借書日期、當前日期（或還書日期）。 ①列出當前借閱表中超期未歸還圖書的借書記錄（ Book_Current_Overdue） ②讀者的借閱期為 90 天。 ③計算應還書日期用 DATEADD 或 DATEDIFF 函數。 ④ getdate()函數返回當前數據庫系統日期，返回值的類 型為 datetime。 ⑤列出歷史借閱表中有超期未歸還圖書的還書記錄（ Book_History_Overdue）。 華北科技學院 計算機系綜合性實驗報告 第 14 頁 7．備份與恢復設計 （ 1）設計思想： 在圖書借閱系統中，分為四個用戶定義文件組和一個主文件組，其中操作最為頻繁的是文件組 4（存放歷史借閱表）、文件組 3（存放借閱表）、其次為，文件組 2（存放圖書表）、文件組 1（存放讀者表）。但是主文件組又是整個數據庫可以啟動、運行的基礎，所以不可忽視。備份操作由授予固定服務器角色 db backupoperator（允許進行數據庫備份的用戶）來操作 ，完全備份時間，在每周二上午，圖書館內務整理時進行，文件組的差異備份與事務日志備份在每天閉館后進行。 （ 2） 具體實現： 綜合以上考慮，在本地計算機上，指定一個單獨的磁盤，存放每周二進行的數據庫完全備份（包括主文件組、用戶定義文件組、事務日志），在本地局域網內另設一臺主機專門用來存放完全備份，該主機權限只能由系統管理員登錄。由屬于固定服務器角色 db backupoperator 用戶進行完全備份操作。 文件組差異備份與事務日志備份在每天閉館后進行。主要針對四個用戶自定義文件組、主文件組、事務日志進行差異備份，指定備份 在本地計算機一個單獨的磁盤。 8．數據庫安全設計 （ 1）設計思想： ①在圖書借閱系統中，在服務器級別，可以分為系統管理員（ sysadmin）、安全管理員（ securityadmin）、服務器管理員（ serveradmin）、設置管理員（ setupadmin）、華北科技學院 計算機系綜合性實驗報告 第 15 頁 進程管理員（ processadmin）、管理磁盤文件（ diskadmin）、數據庫創建者（ dbcreator）、指定格式復制數據文件（ bulkadmin）。 ②在數據庫級別，可以分為數據庫所有者（ db_owner）、數據庫訪問權限管理者（ db_accessadmin）、數據庫安全管理員（ db_securityadmin）、數據庫 DDL 管理員（ db_ddladmin）、數據庫備份操作員（ db_backupoperator）、數據庫數據讀取者（ db_datareader）、數據庫數據寫入者（ db_datawriter）。 ③此外還包括自定義數據庫角色（學生、教師、圖書館普通員工、圖書館借閱處工作人員、圖書館采編部工作人員）、應用程序角色（圖書館網站）。 ④自定義數據庫角色都具有對圖書表的查詢權限、具有對圖書 ID 與 ISBN 對應表的查詢權限。 ⑤學生對與自己的借 書記錄、還書記錄有查詢權限、讀者信息中的地址（ Addr）字段有更新權利、其他字段只允許查詢。學生借閱數量設置為（ 0=Num=10），借書期限為 90 天。 ⑥教師對與自己的借書記錄、還書記錄有查詢權限、讀者信息中的地址（ Addr）字段有更新權利、其他字段只允許查詢。教師借閱數量設置為（ 0=Num=15），借書期限為 120 天。 ⑦圖書館普通員工對與自己的借書記錄、還書記錄有查詢權限、讀者信息中的地址（ Addr）字段有更新權利、其他字段只允許查詢。普通員工借閱數量設置為（ 0=Num=10），借書期限 為 90 天。 ⑧圖書館借閱處工作人員同時屬于普通員工、借閱處工作人員兩個角色，新增權限當讀者借書、還書時，借閱處工作人員可以提交借書、還書記錄。對讀者表的借閱量字段、圖書表的庫存量字段，通過應用程序角色可以進行更新。 ⑨圖書館采編部工作人員同時屬于普通員工、采編部工作人員兩個角色，新增權限對歷史借閱表所有行具有查詢權限，可以根據統計數據，決定新增上架圖書、下架某些圖書，具有對圖書表中數據的更新、刪除。 （ 2） 具體實現： 測試：以 SQL Server 用戶 ： 202007024218， 登錄服務器 ： ADMINISTRATOR 默認數據庫： MBook203， 數據庫用戶 ： 202007024218， 屬于角色：學生 具有權限：對圖書表（ Book）的查詢權限。 執行圖書檢索存儲過程，執行結果如下： 華北科技學院 計算機系綜合性實驗報告 第 16 頁 五、體會 在圖書借閱系統數據設計的庫綜合實驗中，由于需求分析不夠深入，對圖書借閱系統的業務流程，以及所涉及到的角色沒有做到深入的了解。比如讀者，圖書館工作人員、管理員，他們應該具有詳細的角色劃分，由此設計數據表、以及對應數據表中的各個字段，比如借閱數量，可以根據角色設置不同的約束條件。對于不同的角色也可以分配 不同的權限，也方便了管理員對用戶權限的管理。 在設計表結構時存在一些不足，比如，在歷史借閱表中，為了保證實體完整性，而將讀者號、圖書 ISBN、圖書 ID、借書日期、還書日期都設置為了主鍵，可以考慮加入一個字段作為標志列，用標識列作為主鍵，可以保證還書記錄在歷史借閱表中的唯一性，標志列可以唯一的標志一行。另外一個是圖書 ID 與 ISBN 的對應表，在該表中同一個 ISBN 對應了不同的 ID，表中用圖書 ID 作為主鍵不太合理。在圖書表中，圖書應該是分類存放，所以應該考慮添加分類標號，這樣在圖書檢索的時候可以提高檢索效率，同時 圖書按類存放，也使得圖書表中數據便于維護。 對于服務器固定角色、數據庫固定角色、用戶自定義角色、應用程序角色的理解也不夠，對于他們應有權限以及權限的分配不清楚，在為圖書借閱數據庫創建了學生角色后，指定學生角色所有者為 dbo，為學生角色授權可以查詢圖書表，將創建的 SQL Server 登錄用戶對應的數據庫用戶添加到學生角色，使用該用戶登錄后，對其他表中數據仍然具有查詢權限，還可以操作借書、還書存儲過程。 對于讀者登錄驗證，可以單獨創建一個表，包含讀者號、讀者登錄密碼，對于登錄密碼在數據庫中可以采用加密存儲，保證 讀者個人信息的安全性。 管理員的權限也要有明確的分配，對管理員的操作也要具有審計功能。 總體而言，對于數據設計，應該遵循數據庫設計的理論指導，需求分析、概念結構設計、邏輯結構設計、物理設計，結合實際系統在數據庫設計的每一步都應該認真分析、設計，是后期投入運行的重中之重。 六、參考文獻 SQL Server 實用教程（第 3 版）鄭阿奇 主編 華北科技學院 計算機系綜合性實驗報告 第 17 頁 七、 SQL 代碼 /* MBook203 */ CREATE DATABASE MBook203 ON PRIMARY ( NAME = 39。MBook203_DATA39。, FILENAME = 39。D:\db\39。, SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ), FILEGROUP MyDB_FG1 ( NAME = 39。MyDB_FG1_Data139。, FILENAME = 39。D:\db\39。, SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ), FILEGROUP MyDB_FG2 ( NAME = 39。MyDB_FG2_Data139。, FILENAME = 39。D:\db\39。, SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ), FILEGROUP MyDB_FG3 ( NAME = 39。MyDB_FG3_Data139。, 華北科技學院 計算機系綜合性實驗報告 第 18 頁 FILENAME = 39。D:\db\39。, SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ), FILEGROUP MyDB_FG4 ( NAME = 39。MyDB_FG4_Data139。, FILENAME = 39。D:\db\39。, SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ) LOG ON ( NAME = 39。MBook203_LOG39。, filename = 39。D:\db\39。, size = 10MB, maxsize = unlimited, filegrowth = 10% )。 /* ,讀者表、 (圖書表、 BookID_ISBN)、借閱表、歷史借閱表 分別放在 文件組 MyDB_FG MyDB_FG MyDB_FG MyDB_FG4 讀者表（ Reader）、圖書表（ Book）、圖書 ID 與 ISBN 對應表 (BookID_ISBN)、借閱表（ Lend）和借閱歷史表（ HistoryLend） */ USE MBook203 GO CREATE TABLE Reader 華北科技學院 計算機系綜合性實驗報告 第 19 頁 ( ReaderID char(12) NOT NULL PRIMARY KEY, Name varchar(8) NOT NULL, Sex bit CHECK(Sex = 39。039。 OR Sex = 39。139。), 1: 男 0: 女 Born date not null, Dept varchar(20), Spec varchar(20), Num int NOT NULL DEFAULT 0, Photo varbinary(MAX) NULL, Addr xml NULL )ON MyDB_FG1 GO CREATE TABLE Book ( ISBN varchar(16) NOT NULL PRIMARY KEY, Bname varchar(40) NOT NULL, Author varchar(16) NOT NULL, Pub varchar(30) NOT NULL, Price float NOT NULL, Copnum int NOT NULL DEFAULT 0, Invnum int NOT NULL DEFAULT 0, Photo varbinary(MAX) NULL, Summary xml NULL ) ON MyDB_FG2 GO CREATE TABLE BookID_ISBN ( ISBN varchar(16) NOT NU