图书在线管理系统数据库设计
一.系统需求分析
随着图书馆藏量的增加和信息化水平的提高，传统的人工图书管理方式效率低下、易出错。开发一个集图书信息、读者信息、借阅归还于一体的在线管理系统，旨在提高管理效率、优化读者体验、实现资源的数字化管理。本分析旨在明确系统的功能需求与数据需求。
1功能需求
功能需求需要定义系统必须提供的服务和操作。通过需求分析获得系统的前端功能与后台功能需求如下：
1.1前台功能
读者注册/登录：读者可在线注册账号，并通过账号密码登录系统。账号信息需保存在数据库的表中，密码需要加密存储。
读者信息维护：读者可查看和修改个人基本信息；
图书查询：读者可根据书名、作者、ISBN等关键词检索图书，并查看图书详情及可借阅状态。
借阅情况查询：读者可查看本人的当前借阅列表及历史借阅记录。
图书续借：读者可在图书到期前，在线完成续借操作，条件是未被其他读者预约。
图书归还：读者通过该功能可以把图归还，如果存者损坏或在丟失，可以直接赔偿。
1.2 后台管理功能
1.2.1 图书管理模块
图书信息管理：管理员可对图书信息进行增、删、改、查操作。
图书入库/下架：管理员处理新书入库登记以及旧书或破损图书的下架操作。
图书分类管理：管理员可维护图书的分类体系（如文学、科技、历史等）。
1.2.2 借阅管理模块
借书操作：管理员扫描读者证件和图书条码，完成借阅流程，系统自动更新库存并记录借阅信息。
还书操作：管理员扫描图书条码，系统自动完成归还，计算并提示是否有超期罚款。如果有罚款，能自动计算机罚款的金额，且能能过微信或支付宝付款，把付款信息记录到支付记录表中。
续借与预约：处理读者的续借申请，并管理图书的预约队列。
1.2.3 系统管理模块
用户权限管理：超级管理员可创建和管理不同角色的管理员账号，并分配权限。
数据统计与报表：系统能生成各类统计报表，如图书借阅排行榜、读者借阅分析、库存统计等。
罚款管理：系统自动计算超期罚款，并记录读者的罚款缴纳情况。
通过上述分析，得到如图1所示的功能图。
2.数据需求
数据需求要定义系统需要存储和处理的核心数据实体及其属性。通过需求分析，系统需要管理如下数据：
图书分类信息：主要包括分类编号、分类名称、分类代码。
图书信息：图书ID/ISBN、书名、作者、出版社、出版日期、单价、馆藏总量、当前可借数量、入库时间、图书位置。
读者类别信息：主要包括类别名称、借书数量、可借时间等；
读者信息：主要包括姓名、密码、性别、手机号、邮箱、注册日期、账户状态（正常/冻结）。
馆厅信息：主要包括馆厅号与书架数量；
图书管理员信息：主要包括用户名、密码、角色、权限等级、最后登录时间。
系统还要登记借阅数据，主要登记读者ID、图书ID、借出日期、应还日期、实际归还日期、续借次数、借阅状态（借出/已归还/超期）。
系统还要罚款记录，数据有读者ID、借阅记录ID、罚款金额、产生原因（如超期）、产生日期、缴纳状态（未缴/已缴）。
3.数据流图
通过分析，系统的0层数据流图如下所示：
系统1层数据流如下所示。
二、数据概念结构设计
数据库概念结构设计是整个数据库设计的核心与关键，其作用是在需求分析的基础上，抽象出系统所关心的核心信息实体、属性及实体间的内在联系，形成一个独立于任何具体数据库管理系统的概念模型（通常用E-R图表示）。
1.系统实体
通过前面的需求分析，该系统中的主要实体如下：
图书分类信息：主要包括分类编号、分类名称、分类代码以及分类描述。注意此处不采用分级类别，实体如下图所示。
图书信息：图书编号、书名、作者、出版社、出版日期、单价、馆藏总量、当前可借数量、入库时间。实体如下图所示。
读者类别信息：主要包括类别名称、借书数量与借书天数等。实体如下图所示。
读者信息：主要包括姓名、密码、性别、手机号、邮箱、注册日期、账户状态（正常/冻结）。实体如下图所示。
馆厅信息：主要包括馆厅号、书架数量与馆厅描述。实体如下图所示。
图书管理员信息：主要包括用户名、密码、角色、权限等级、最后登录时间。实体如下图所示。
罚款信息：包括罚款编号、金额、是否支付、支付时间、罚款原因等。实体如下图所示。
罚款原因信息：主要包括罚款原因编号，原因，罚款比例，罚款描述等，实体如下图所示。
2.系统E-R图
通过对实体之间关系的描述，得到如下所示的E-R图。
三、数据库逻辑结构设计
数据库逻辑结构设计承上启下，其核心作用是将概念模型（E-R图）转化为特定数据库管理系统（如MySQL）所支持的数据模型，即一系列关联的二维表结构，并确保其满足关系模式的规范化要求，以消除数据冗余和操作异常。
1.关系模式
通过对E-R图中的实体及实体联系分析，得到到如下关系模式：
（1）图书分类(分类编号,分类名称,分类代码,分类描述)。其中，分类编号为主键。
（2）图书(图书编号,分类编号,书名,作者,出版社,出版日期,单价,馆藏总量,馆厅编号,当前可借数量,入库时间)。其中，图书编号为主键，分类编号为外键，馆厅编号为外键。
（3）读者类别(类别编号,类别名称,借书数量,借书天数)。类别编号为主键。
（4）读者(读者编号,类别编号,姓名,密码、性别、手机号、邮箱、注册日期、账户状态)。其中，读者编号为主键，类别编号外键。
（5）馆厅(馆厅架编号,书架数量,描述)。其中馆厅架编号为主键。
（6）图书管理员(管理员编号,用户名,密码,角色,权限,最后登录时间);其中，管理员编号为主键。
（7）借阅明细(读者ID,图书ID,借出日期,应还日期,实际归还日期,续借次数,借阅状态)。其中主键为读者ID+图书ID,读者ID与图书ID为外键。
（8）罚款明细(明细编号,读者ID,图书ID,原因ID,罚款金额,罚款时间)，其中明细编号为主键，读者ID、图书ID、原因ID为外键。
（9）罚款原因(原因编号,原因,赔偿比例,描述),其中原因编号为主键。
2.英文关系模式描述
(1)Book_Category(Category_ID, CategoryName, Category_Code, Category_Description);
(2)Book(Book_ID, Category_ID, Book_Title, Author, Publisher, Publication_Date, Price, Total Collection_Number, Library_Hall ID, Available_Number, Storage_Time)
(3)Reader_Category(Category_ID, Category_Name, Borrowing_Number_Limit, Borrowing_Duration)
(4)Reader(Reader_ID, Reader_Category_ID, Name, Password, Gender, Phone, Email, Registration_Date, Account_Status)
(5)Library_ Hall_Shelf(Shelf_ID, Bookshelves_Number, Description)
(6)Librarian(Librarian_ID, Username, Password, Role, Permission, Last_Login_Time)
(7)Borrowing_Details(Reader_ID, Book_ID, Borrowing_Date, Due_Date, Actual_Return_Date, Renewals_Number, Borrowing_Status)
(8)Fine_Record(Record_ID, Reader_ID, Book_ID, Reason_ID, Fine_Amount, Fine_Time)
(9)Fine_Reason(Reason_ID, Reason, Compensation_Ratio, Description)
2.3 NF范式分析及处理
3NF 的定义是：
满足 2NF（非主属性完全依赖于候选键，且不存在部分依赖）。
非主属性不传递依赖于候选键（即不能有 A → B → C，且 B 不是候选键）。
1. 图书分类
关系：图书分类(分类编号,分类名称,分类代码,分类描述)
主键：分类编号
所有非主属性（分类名称、分类代码、分类描述）都完全依赖于主键，且没有传递依赖，已满足 3NF。
2. 图书
关系：图书(图书编号,分类编号, 书名, 作者, 出版社, 出版日期, 单价, 馆藏总量, 馆厅编号, 当前可借数量, 入库时间)
主键：图书编号
外键：分类编号 → 图书分类(分类编号)，馆厅编号 → 馆厅架(馆厅架编号)
依赖分析：
图书编号 → {书名, 作者, 出版社, 出版日期, 单价, 馆藏总量, 馆厅编号, 当前可借数量, 入库时间}
当前可借数量 = 馆藏总量 - 已借出数量（但已借出数量不在本表，所以这里存储的当前可借数量可能冗余，但它是直接依赖于图书编号的存储字段，不违反 3NF）
没有传递依赖（馆厅编号不决定其他非主属性，除了外键关联的表中的属性，但那些不在本表）。
已满足 3NF
3. 读者类别
关系：读者类别(类别编号, 类别名称, 借书数量, 借书天数)
主键：类别编号
所有属性完全依赖于主键，没有传递依赖，满足 3NF
4. 读者
关系：读者(读者编号,读者类别, 姓名, 密码, 性别, 手机号, 邮箱, 注册日期, 账户状态)
主键：读者编号
外键：读者类别 → 读者类别(类别编号)
依赖分析：
读者编号 → {读者类别, 姓名, 密码, 性别, 手机号, 邮箱, 注册日期, 账户状态}
读者类别 → 类别名称, 借书数量, 借书天数（但这些不在本表中，所以没有传递依赖问题）
已满足 3NF
5. 馆厅架
关系：馆厅架(馆厅架编号, 书架数量, 描述)
主键：馆厅架编号
所有属性完全依赖于主键。已满足 3NF
6. 图书管理员
关系：图书管理员(管理员编号, 用户名, 密码, 角色, 权限, 最后登录时间)
主键：管理员编号
依赖分析：管理员编号 → {用户名, 密码, 角色, 权限, 最后登录时间}
如果“权限”依赖于“角色”，则可能存在传递依赖（管理员编号 → 角色 → 权限），但这里“权限”可能是直接存储的权限字符串，不是通过角色推导，所以要看设计。
如果权限完全由角色决定，则应拆分为：
管理员(管理员编号, 用户名, 密码, 角色, 最后登录时间)
角色权限(角色, 权限)
但题目中未说明，假设权限是直接属性，则满足 3NF。若权限由角色决定，则不满足 3NF；否则满足。
7. 借阅明细
关系：借阅明细(记读者ID, 图书ID, 借出日期, 应还日期, 实际归还日期, 续借次数, 借阅状态)
主键：(读者ID, 图书ID)
外键：读者ID → 读者(读者编号)，图书ID → 图书(图书编号)
依赖分析：
(记读者ID, 图书ID) → {借出日期, 应还日期, 实际归还日期, 续借次数, 借阅状态}
没有部分依赖（因为主键是复合主键，所有非主属性依赖于整个主键）
没有传递依赖，已满足 3NF。
8. 罚款明细
关系：罚款明细(明细编号, 读者ID, 图书ID, 原因ID, 罚款金额, 罚款时间)
主键：明细编号
外键：读者ID → 读者(读者编号)，图书ID → 图书(图书编号)，原因ID → 罚款原因(原因编号)
依赖分析：
明细编号 → {读者ID, 图书ID, 原因ID, 罚款金额, 罚款时间}
罚款金额是否由原因ID决定？如果是，则：明细编号 → 原因ID → 罚款金额，存在传递依赖，违反 3NF。
但这里罚款金额可能独立于原因ID（比如根据超期天数计算），所以要看业务。
假设罚款金额由原因ID决定（如罚款原因表中有赔偿比例，但金额可能还依赖图书价格等，若直接存最终金额则可能不传递依赖）。若罚款金额完全由原因ID决定，则不满足 3NF；否则满足。
9. 罚款原因
关系：罚款原因(原因编号, 原因, 赔偿比例, 描述)
主键：原因编号
所有属性完全依赖于主键，没有传递依赖。已满足 3NF
四、物理实现设计
主要包括数据库设计、表设计与索引的创建
1.表结构
图书分类表（book_categories）
字段名 数据类型 中文含义 约束
category_id INT 分类编号 主键，自增
category_name VARCHAR(20) NOT NULL 分类名称 分类的名称，如“计算机科学”、“文学”.
category_code VARCHAR(20) 分类代码 可设置唯一约束，如 "CS", "LIT"
description TEXT 分类描述 对该分类的详细描述，文本类型更合适
图书表 (books)
字段名 数据类型 中文含义 说明
category_id INT NOT NULL 分类编号 外键，关联到 book_categories 表
title VARCHAR(255) NOT NULL 书名
author VARCHAR(100) NOT NULL 作者
publisher VARCHAR(100) 出版社
publish_date DATE 出版日期 使用 DATE 类型存储日期
price DECIMAL(10, 2) 单价 使用 DECIMAL 类型精确存储金额
total_copies INT DEFAULT 0 馆藏总量 该图书的总采购/馆藏数量
hall_id VARCHAR(50) 馆厅编号 图书所在馆厅的编号或名称
available_copies INT DEFAULT 0 当前可借数量 馆藏总量 - 已借出数量，用于快速查询
storage_time DATETIME DEFAULT CURRENT_TIMESTAMP 入库时间 记录图书入库的具体时间，默认当前时间
3.建数据库与表
-- 创建数据库
CREATE DATABASE book_library DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 使用该数据库
USE book_library;
--创建图书分类表
CREATE TABLE book_categories (
category_id INT PRIMARY KEY AUTO_INCREMENT,
category_name VARCHAR(100) NOT NULL,
category_code VARCHAR(20) UNIQUE,
description TEXT
);
--创建图书表
CREATE TABLE books (
book_id INT PRIMARY KEY AUTO_INCREMENT,
category_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
author VARCHAR(100) NOT NULL,
publisher VARCHAR(100),
publish_date DATE,
price DECIMAL(10, 2),
total_copies INT DEFAULT 0,
hall_id VARCHAR(50),
available_copies INT DEFAULT 0,
storage_time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES book_categories(category_id)
);
3.索引设计
在数据库设计中，索引的建立是为了提高查询效率，通常会在经常用于查询条件、连接条件和排序条件的列上建立索引。以下是针对你提供的各个关系表建议建立的索引：
（1）图书分类(分类编号,分类名称,分类代码,分类描述)
主键索引：已经在分类编号上隐式建立了主键索引。
普通索引：如果经常根据分类名称或分类代码进行查询，因此，在这两列上建立普通索引。
CREATE INDEX idx_classification_name ON 图书分类(分类名称);
CREATE INDEX idx_classification_code ON 图书分类(分类代码);
（2）图书(图书编号,分类编号,书名,作者,出版社,出版日期,单价,馆藏总量,馆厅编号,当前可借数量,入库时间)
主键索引：已经在图书编号上隐式建立了主键索引。
外键索引：分类编号作为外键，经常用于与其他表（图书分类表）进行连接查询，需要建立索引。
普通索引：如果经常根据书名、作者、出版社 进行查询，需要在这几列上建立普通索引。
CREATE INDEX idx_book_category ON图书(分类编号);
CREATE INDEX idx_book_title ON 图书(书名);
CREATE INDEX idx_book_author ON 图书(作者);
CREATE INDEX idx_book_publisher ON 图书(出版社);
（3）读者类别(类别编号, 类别名称, 借书数量, 借书天数)
主键索引：已经在类别编号上隐式建立了主键索引。
普通索引：系统经常根据类别名称进行查询，需要以建立普通索引。
CREATE INDEX idx_reader_category_name ON 读者类别(类别名称);
（4）读者(读者编号, 读者编号（此处重复，假设为读者类别）, 姓名, 密码, 性别, 手机号, 邮箱, 注册日期, 账户状态)
主键索引：已经在读者编号上隐式建立了主键索引。
外键索引：读者类别作为外键，经常用于与读者类别表进行连接查询，需要建立索引。
普通索引：如果经常根据姓名、手机号、邮箱进行查询，需要在这几列上建立普通索引。
CREATE INDEX idx_reader_category ON 读者(读者类别);
CREATE INDEX idx_reader_name ON 读者(姓名);
CREATE INDEX idx_reader_phone ON 读者(手机号);
CREATE INDEX idx_reader_email ON 读者(邮箱);
（5）馆厅架(馆厅架编号, 书架数量, 描述)
主键索引：已经在馆厅架编号上隐式建立了主键索引。
普通索引：经常根据描述进行查询，需要建立普通索引。
CREATE INDEX idx_library_shelf_desc ON 馆厅架(描述);
（6）图书管理员(管理员编号, 用户名, 密码, 角色, 权限, 最后登录时间)
主键索引：已经在管理员编号上隐式建立了主键索引。
普通索引：如果经常根据用户名进行查询，需要以建立普通索引。
CREATE INDEX idx_admin_username ON 图书管理员(用户名);
（7）借阅明细(记读者ID, 图书ID, 借出日期, 应还日期, 实际归还日期, 续借次数, 借阅状态)
主键索引：已经在读者ID + 图书ID上隐式建立了主键索引。
外键索引：读者ID和图书ID作为外键，经常用于与读者表和图书表进行连接查询，需要建立索引。
普通索引：经常根据借出日期、应还日期、借阅状态进行查询，需要在这几列上建立普通索引。
CREATE INDEX idx_borrow_detail_reader ON 借阅明细(记读者ID);
CREATE INDEX idx_borrow_detail_book ON 借阅明细(图书ID);
CREATE INDEX idx_borrow_detail_borrow_date ON 借阅明细(借出日期);
CREATE INDEX idx_borrow_detail_return_date ON 借阅明细(应还日期);
CREATE INDEX idx_borrow_detail_status ON 借阅明细(借阅状态);
（8）罚款明细(明细编号, 读者ID, 图书ID, 原因ID, 罚款金额, 罚款时间)
主键索引：已经在明细编号上隐式建立了主键索引。
外键索引：读者ID、图书ID和原因ID作为外键，经常用于与读者表、图书表和罚款原因表进行连接查询，需要建立索引。
普通索引：经常根据罚款时间进行查询，需要以建立普通索引。
CREATE INDEX idx_fine_detail_reader ON 罚款明细(读者ID);
CREATE INDEX idx_fine_detail_book ON 罚款明细(图书ID);
CREATE INDEX idx_fine_detail_reason ON 罚款明细(原因ID);
CREATE INDEX idx_fine_detail_time ON 罚款明细(罚款时间);
（9）罚款原因(原因编号, 原因, 赔偿比例, 描述)
主键索引：已经在原因编号上隐式建立了主键索引。
普通索引：根据原因 行查询，需要以建立普通索引。
CREATE INDEX idx_fine_reason_reason ON 罚款原因(原因);
索引创建是基于查询需求，实际优化应根据具体的业务场景和查询频率进行适当调整和优化。过多的索引会影响数据库的写入性能，因此需要权衡查询效率和写入性能。
五、应用与优化
根据系统功能图编写核心业务的SQL语句，同时根据据业务需求创建必要的视图与存储过程（包括触发器与事件），且定制备份策略。
一、需要创建如下视图
1.图书库存告警视图 (v_inventory_alert)
目的：供管理员快速识别库存紧张（如可借数为0）或热门（库存量少）的图书。
CREATE VIEW v_inventory_alert AS
SELECT
b.Book_ID,
b.Book_Title,
bc.Category_Name,
b.Available_Number AS Current_Available,
b.Total_Collection_Number AS Total_Stock,
CASE
WHEN b.Available_Number = 0 THEN '库存紧张（可借数为0）'
WHEN b.Available_Number <= b.Total_Collection_Number * 0.1 THEN '库存量少（剩余≤10%）'
ELSE '库存正常'
END AS Alert_Level
FROM
Book b
JOIN
Book_Category bc ON b.Category_ID = bc.Category_ID
WHERE
b.Available_Number = 0
OR b.Available_Number <= (b.Total_Collection_Number * 0.1)
ORDER BY
b.Available_Number ASC;
2. 分类统计视图 (v_category_statistics)
CREATE VIEW v_category_statistics AS
SELECT
bc.Category_ID,
bc.Category_Name,
COUNT(b.Book_ID) AS Book_Count,
SUM(b.Total_Collection_Number) AS Total_Stock,
SUM(b.Available_Number) AS Available_Stock,
AVG(b.Price) AS Avg_Price
FROM
Book_Category bc
LEFT JOIN
Book b ON bc.Category_ID = b.Category_ID
GROUP BY
bc.Category_ID, bc.Category_Name
ORDER BY
Book_Count DESC;
目的：为前台展示或后台分析提供按图书分类统计的数据。
3. 当前借阅视图 (v_current_loans)
目的：快速查询所有尚未归还的图书借阅记录。
CREATE VIEW v_current_loans AS
SELECT
bd.Reader_ID,
r.Name AS Reader_Name,
bd.Book_ID,
b.Book_Title,
bd.Borrowing_Date,
bd.Due_Date,
DATEDIFF(bd.Due_Date, CURRENT_DATE) AS Days_Remaining,
bd.Borrowing_Status
FROM
Borrowing_Details bd
JOIN
Book b ON bd.Book_ID = b.Book_ID
JOIN
Reader r ON bd.Reader_ID = r.Reader_ID
WHERE
bd.Actual_Return_Date IS NULL
AND bd.Borrowing_Status != '已归还'
ORDER BY
bd.Due_Date ASC;
4. 用户借阅历史视图 (v_user_loan_history)
目的：查看指定用户的完整借阅历史。
CREATE VIEW v_user_loan_history AS
SELECT
bd.Reader_ID,
r.Name AS Reader_Name,
bd.Book_ID,
b.Book_Title,
bd.Borrowing_Date,
bd.Due_Date,
bd.Actual_Return_Date,
bd.Renewals_Number,
CASE
WHEN bd.Actual_Return_Date IS NULL THEN '借阅中'
WHEN bd.Actual_Return_Date > bd.Due_Date THEN '逾期归还'
ELSE '按时归还'
END AS Return_Status
FROM
Borrowing_Details bd
JOIN
Book b ON bd.Book_ID = b.Book_ID
JOIN
Reader r ON bd.Reader_ID = r.Reader_ID
ORDER BY
bd.Reader_ID, bd.Borrowing_Date DESC;
注意需要创建如下存储过程处理复杂的业务逻辑。
一、需要创建存储过程
sp_ProcessFine：一个专门处理罚款的存储过程示例
sp_AutoCalculateOverdueFines：这里还有一个自动计算逾期罚款的存储过程：
sp_borrow_book：处理借书业务，包含库存检查和事务保护
sp_return_book：处理还书业务，恢复库存
sp_add_book_inventory：智能处理图书入库（新增或更新）
sp_update_book_info：安全更新图书基本信息
sp_get_user_loan_stats：提供用户借阅行为分析
通过使用存储过程，可以确保数据的一致性，提高系统性能，并为应用程序提供清晰的数据操作接口。
六、安全性设计
对角色与用户权限规划。完成用户权限管理与权限控制。
该系统的用户有系统管理员(System Administrator)、图书管理员 (Librarian)、读者 (Reader/Patron)、访客 (Guest)，每类用户的权限如下表所示。
功能模块 系统管理员 图书管理员 读者 访客
用户管理 ✅ 完全控制 ❌ ❌ ❌
图书管理 ✅ 完全控制 ✅ 管理权限 ✅ 查询 ✅ 查询
借阅管理 ✅ 完全控制 ✅ 办理业务 ✅ 个人记录 ❌
罚款管理 ✅ 完全控制 ✅ 处理权限 ✅ 个人查询 ❌
系统设置 ✅ 完全控制 ❌ ❌ ❌
报表统计 ✅ 完全控制 ✅ 查看权限 ❌ ❌
为了每类用户创建角色，把每类用户分配角色。
七.对设计的数据库进行测试。
模拟系统使用场景，输入测试数据对系统进行测试，确保数据库符合预期需求。
按照这个模板写
本文介绍了一个SQL Server存储过程，用于管理角色权限，包括插入、更新和删除操作。此过程通过参数控制不同的操作，并处理了错误及锁定超时的情况。
扫一扫
1033
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
