前言
大家好，我是天罡gg，一个有十多年丰富经验的高级架构师，参与过很多系统的数据库设计，在数据库设计方面有相当丰富的经验。正赶上这篇实战专栏的数据库设计，所以今天让我们来一起做一下《图书借阅系统的数据库设计》，一篇既有理论知识，又能实战落地的数据库设计！
本文从【需求分析】开始，到【概念结构设计】的画ER图, 再到【逻辑结构设计】的ER图转关系模型, 最后到【物理结构设计】的MySQL表设计, 再加上【具体业务分析】的SQL实现，你是不是已经怀疑真有这么齐全吗？你没看错，全文HTML统计1万3千多字，Markdown统计1万6千多字，设计图片24张，让我们拭目以待！
- 需求分析：分析用户的需求，包括数据、功能和性能需求
- 概念结构设计：主要采用E-R模型进行设计，包括画E-R图
- 逻辑结构设计：通过将E-R图转换成表，实现从E-R模型到关系模型的转换
- 物理结构设计：主要是为所设计的数据库选择合适的存储结构和存取路径
- 数据库实施：包括编程、测试和试运行
- 数据库的运行和维护：在运行过程中不断对其进行调整、修改。
一、背景和需求分析
1.1 背景
图书馆作为一种信息资源的交换场所，图书和用户借阅资料繁多，包含很多数据信息的管理，因此实现图书管理的电子信息化，可以简化繁琐的工作模式，有效解决图书借阅过程中的诸多问题，给图书管理员和借阅者带来极大的便利。
图书借阅系统是为了满足图书馆借阅图书的工作而设计的，它的功能主要分为数据维护和基本功能两大模块。其中数据维护模块包括图书的数据维护和借阅者的数据维护，基本功能模块包括对图书和借阅者的信息浏览、借书、还书、查询、统计等功能。
1.2 信息需求
学生： 可以在线查找自己想借的图书，能查看图书的基本信息是最低要求。为了更好的借书体验，可以查看图书的借阅次数、评论数和具体评论 以及 借阅状态。通过查看借阅次数和评论，增加对图书的兴趣，当查看图书的借阅状态是闲置中时，可以直接在线发起借阅；如果图书已经在借阅中，我们可以提供一个收藏功能，方便日后再借阅；那么当对图书有话说时，还可以发起评论，这些都是电子信息化带来的好处。
管理员： 主要职责是对图书进行维护，对学生进行管理。为了更好的运营，可以发布一些新闻公告、做一些新书推荐，为了更规范运作，需要拥有对学生的借阅借的审批、对学生借书的审批、对学生还书的审批登记、以及对评论的审批等工作。
1.3 功能需求
管理员
学生
-
学生注册
-
我要借阅
1.4 数据流图
待补充
二、概念结构设计
1. 抽象出系统实体
学生-》申请 -》借阅证，可以得出【学生】和【借阅证】两个实体，【申请】是联系。
学生-》借阅 -》图书，可以得出【图书】实体，【借阅】是联系。
学生-》收藏 -》图书，可以得出【收藏】是联系。
学生-》评论 -》图书，可以得出【评论】是联系。
管理员-》发布 -》公告，可以得出【管理员】和【公告】两个实体，【发布】是联系。
管理员-》管理 -》图书，可以得出【管理】是联系。
涉及的5个实体有：
-
- . 学生
-
- . 借阅证
-
- . 图书
-
- . 管理员
-
- . 公告
5个实体之间的联系如下：
- 1名学生，只能
申请1个借阅证，一个借阅证也只能属于一名学生，所以是一对一的联系； - 1名学生，可以
借阅N本图书，1本图书，也可以被N名学生借阅，所以学生与图书是多对多的借阅联系； - 1名学生，可以
收藏N本图书，1本图书，也可以被N名学生收藏，所以学生与图书是多对多的收藏联系； - 1名学生，可以
评论N本图书，1本图书，也可以被N名学生评论，所以学生与图书是多对多的评论联系; - 1名管理员，可以
发布N条公告，但1条公告，只能由一名管理员发布，所以是一对多联系; - 1名管理员，可以
管理N本图书，1本图书，只能由一名管理员最后管理，所以是一对多联系。
2. 局部E-R图
E-R图：Entity Relationship，实体关系图。
常见的有4个部分：
2.1 学生 E-R图
这是一个实体。
根据需求得知学生基本信息包括：学生编号、学生姓名、学生昵称、学生所属(例如XX学院XX系XX班)、学生证照片
2.2 借阅证 E-R图
这是一个实体。
2.3 图书 E-R图
这是一个实体。
根据需求得知图书的基本信息包括：图书编号、图书名称、图书类型、作者、图书简介、图书封面、出版社、出版时间
还有2个引申出来的额外属性：
2.4 管理员 E-R图
这是一个实体。
2.5 公告 E-R图
这是一个实体。
2.6 申请 E-R图
这是一个联系，【学生】和【借阅证】之间存在着【申请】的联系。
2.7 借阅 E-R图
这是一个联系，【学生】和【图书】之间存在着【借阅】的联系。
2.8 评论 E-R图
这是一个联系，【学生】和【图书】之间存在着【评论】的联系。
2.9 收藏 E-R图
这是一个联系，【学生】和【图书】之间存在着【收藏】的联系。
【收藏】不需要【管理员】审批，但可以【取消】，所以包括如下属性：
3. 全局E-R图
我省略了属性，带颜色的实体和联系需要自行补充属性，根据上面的局部E-R图补充，别偷懒，实际画一下你可能会有更多的领悟！
三、逻辑结构设计
说明：把E-R图转成关系模式。
3.1 转换规则
一个实体型转换为一个关系模式，有一对一、一对多、多对多的联系。不同的联系转关系模式也不同。
-
一对一：两个一对一的实体可以转成两个关系模式，也可以合成成一个关系模式，它们之间的联系可以单独转成一个关系模式，也可以合并到与之关联的任一实体中记录下来。
-
一对多：每一个实体还是转成一个关系模式，可以单独把它们之间的联系转成一个关系模式，也可把联系记录到多（N）对应的实体中。
-
多对多：每一个实体还是转成一个关系模式，它们之间的联系必须转成一个关系模式。
3.2 关系模式
3.2.1 按转换规则分析
-
学生-》申请 -》借阅证
-
学生-》借阅 -》图书
-
学生-》收藏 -》图书
- 【
收藏】需要保存记录，所以可以转换为一个关系模式，并增加【学生编号】、【图书编号】属性。
- 【
-
学生-》评论 -》图书
-
管理员-》发布 -》公告
-
管理员-》管理 -》图书
- 【管理】不需要保存记录，所以不需要转换。
3.2.2 实际转换结果
- 用户表：
用户编号、用户名、密码、角色(0-学生 1-管理员) - 学生表：
学生编号、用户编号、学生姓名、学生昵称、学生所属、学生证图片、是否申请通过(0-未通过 1-通过)、是否冻结(0-正常 1-冻结) - 借阅证申请记录表：
申请编号、学生编号、申请时间、管理员操作时间、申请状态(0-待审核 1-通过 2-驳回)、驳回原因、管理员编号 - 图书表：
图书编号、图书名称、图书类型、作者、图书简介、图书封面、出版社、出版时间、图书状态(0-闲置 1-借阅中)、是否是新书推荐(0-否 1-是) 、借阅次数、评论数 - 图书借阅记录表：
借阅记录编号、学生编号、图书编号、借阅时间、借阅状态(0-待审核 1-ok 2-拒绝 3-已还)、拒绝原因、审核时间、归还时间、管理员编号 - 图书收藏表：
收藏编号、学生编号、图书编号、收藏时间、是否已取消、取消时间 - 图书评论表：
评论编号、学生编号、图书编号、评分(1,2,3,4,5)、评论内容、评论时间、审核状态(0-待审核 1-通过 2-驳回)、管理员编号 - 公告表：
公告编号、标题、内容、发布时间、是否已删除、删除时间、管理员编号
对8个表之间的关系分析如下：
- 学生与用户
1名学生，即是1个用户，所以学生与用户之间是1对1(1 : 1)关系;
- [学生/管理员]与[借阅证申请记录]
1名学生，可以发起N次借阅证申请(存在不通过再次申请的情况)，而1次借阅证申请只能由1名学生发起，所以学生与借阅证申请记录之间是1对多(1 : n)关系;
1名管理员，可以对N次借阅证申请审批，而1次借阅证申请只能由1名管理员审批，所以管理员与借阅证申请记录之间是1对多(1 : n)关系;
- [学生/管理员]与[图书借阅记录]
1名学生，可以发起N次借阅，而1次借阅只能由1名学生发起，所以学生与图书借阅记录之间是1对多(1 : n)关系;
1名管理员，可以对N次借阅审批，而1次借阅只能由1名管理员审批，所以管理员与图书借阅记录之间是1对多(1 : n)关系;
1本图书，可以被N次借阅，而1次借阅只能针对1本图书，所以图书与图书借阅记录之间是1对多(1 : n)关系。
注意：如果1次可以借N本图书的话，那么图书与图书借阅记录之间将是多对多(m : n)关系
- [学生/管理员]与[图书评论]
1名学生，可以发起N条评论，而1条评论只能由1名学生发起，所以学生与评论之间是1对多(1 : n)关系;
1名管理员，可以对N条评论审批，而1条评论只能由1名管理员审批，所以管理员与评论之间是1对多(1 : n)关系;
1本图书，可以有N条评论，而1条评论只能针对1本图书，所以图书与评论之间是1对多(1 : n)关系;
- 学生与图书收藏
1名学生，可以收藏N本图书，而1次收藏只能属于1名学生，所以学生与收藏之间是1对多(1 : n)关系;
1本图书，可以被不同学生收藏N次，而1次收藏只能针对1本图书，所以图书与图书收藏之间是1对多(1 : n)关系;
- 管理员与公告
1名管理员，可以发布N条公告，而1个公告只能由1名管理员发布，所以管理员与公告之间是1对多(1 : n)关系;
3.3 表结构
- 用户表：
- 学生表：
- 图书表：
- 公告表：
- 借阅证申请记录表：
- 图书借阅记录表：
- 图书评论表：
- 图书收藏表：
四、物理结构设计与实施
4.1 创建数据库
CREATE DATABASE `db_book` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
4.2 创建数据表
-
表必备三字段：id, gmt_create, gmt_modified。gmt_create 创建时间, gmt_modified 更新时间，类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新
-
禁用保留字，请参考 MySQL 官方保留字
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名',
`password` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密码',
`role` tinyint(1) UNSIGNED NOT NULL COMMENT '角色(0-学生 1-管理员)',
`gmt_create` datetime(0) NOT NULL COMMENT '创建时间',
`gmt_modified` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学生编号',
`user_id` int(11) UNSIGNED NOT NULL COMMENT '用户编号',
`student_no` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生编号',
`student_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生姓名',
`nick_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生昵称',
`department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生所属(例如XX学院XX系XX班)',
`id_card_image` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '学生证照片',
`is_approved` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否申请通过(0-未通过 1-通过)',
`is_frozen` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否冻结(0-正常 1-冻结)',
`gmt_create` datetime(0) NOT NULL COMMENT '创建时间',
`gmt_modified` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- 剩余6张表的完整代码，请私信我、或添加文章底部微信联系我
SET FOREIGN_KEY_CHECKS = 1;
4.3 初始化数据
INSERT INTO `user` VALUES (1,'admin','2469c47aa7b758eefff3d75939b20c7b',1,NOW(),NOW());
五、具体业务分析
5.1 录入图书
录入四大名著
INSERT INTO `book` VALUES (1, 'ts_00001', '西游记', 1, '吴承恩', '《西游记》主要描写了孙悟空、猪八戒、沙僧三人保护唐僧西行取经，唐僧从投胎到取经归来共遇到八十一难，一路降妖伏魔，化险为夷，最后到达西天、取得真经的故事。','xxx出版社','1990-10-10', '', 0, 0, 0, 1, 1, NULL, '2023-04-07 00:00:10', '2023-04-07 00:00:10');
INSERT INTO `book` VALUES (2, 'ts_00002', '三国演义', 1, '罗贯中', '《三国演义》可大致分为黄巾起义、董卓之乱、群雄逐鹿、三国鼎立、三国归晋五大部分，描写了从东汉末年到西晋初年之间近百年的历史风云，以描写战争为主，诉说了东汉末年的群雄割据混战和魏、蜀、吴三国之间的政治和军事斗争，最终司马炎一统三国，建立晋朝的故事。','xxx出版社','1993-10-10', '', 0, 0, 0, 1, 1, NULL, '2023-04-07 00:00:10', '2023-04-07 00:00:10');
INSERT INTO `book` VALUES (3, 'ts_00003', '水浒传', 1, '施耐庵', '《水浒传》是第一部描写农民起义的小说，全书围绕“官逼民反”这一线索展开情节，表现了一群不堪暴政欺压的“好汉”揭杆而起，聚义水泊梁山，直至接受招安致使起义失败的全过程。','xxx出版社','1995-10-10', '', 0, 0, 0, 1, 1, NULL, '2023-04-07 00:00:10', '2023-04-07 00:00:10');
INSERT INTO `book` VALUES (4, 'ts_00004', '红楼梦', 1, '曹雪芹', '《红楼梦》是以贾、史、王、薛四大家族的兴衰为背景，以富贵公子贾宝玉为视角，以贾宝玉与林黛玉、薛宝钗的爱情婚姻悲剧为主线，描绘了一些闺阁佳人的人生百态，展现了真正的人性美和悲剧美，是一部从各个角度展现女性美以及中国古代社会百态的史诗性著作。','xxx出版社','1992-10-10', '', 0, 0, 0, 1, 1, NULL, '2023-04-07 00:00:10', '2023-04-07 00:00:10');
5.2 学生注册 + 申请借阅证
5.2.1 注册
INSERT INTO `user` VALUES(2,'1032975@qq.com', '2469c47aa7b758eefff3d75939b20c7b',0,NOW(),NOW());
5.2.2 填写学生信息，申请借阅证
-- 填写学生信息
INSERT INTO `student` VALUES (1, 2, 's_00001', '张三', 'better man', 'XX学院XX系XX班', '', 0, 0, '2023-04-08 00:00:00', '2023-04-08 00:00:00');
-- student_id=1的学生，申请借阅证
INSERT INTO `qualification`(`id`, `student_id`, `status`, `verify_time`, `verify_user_id`, `reject_reason`, `gmt_create`, `gmt_modified`) VALUES (1, 1, 0, NULL, NULL, '', '2023-04-08 00:00:00', '2023-04-08 00:00:00');
5.3 管理员审核借阅证通过
-- verify_user_id=1的管理员，审批通过 申请借阅证记录id=1的申请，status=1审批通过
update qualification set `status` = 1,
verify_time ='2023-04-08 00:10:00',
verify_user_id = 1,
gmt_modified = '2023-04-08 00:10:00'
where id = 1;
-- 让id=1学生，拥有借阅资格，is_approved = 1为有借阅资格
update student set is_approved = 1,
gmt_modified = '2023-04-08 00:10:00'
where id = 1;
5.4 搜索图书
select id, book_name, cover_image, borrow_count, comment_count from book
where book_type = 1 and book_name LIKE '红%'
order by borrow_count desc
limit 0,10
select id, book_name, cover_image, borrow_count, comment_count from book
where book_type = 1 and book_name LIKE '三%'
order by publish_date
limit 20,10
5.5 查看图书详情
-- id=1的图书基本信息
select * from book where id = 1;
-- student_id=1的学生，对book_id=1的图书，未取消收藏 的 收藏记录
select * from book_favorite
where book_id = 1 and student_id = 1 and is_cancelled = 0;
-- student_id=1的学生，对book_id=1的图书，审核通过 的 评论，按创建时间倒序
select * from book_comment
where book_id = 1 and student_id = 1 and `status` = 1
order by gmt_create desc;
5.6 图书收藏 和 取消收藏
5.6.1 收藏
student_id=1的学生，收藏 book_id=1的图书
INSERT INTO book_favorite(`student_id`, `book_id`, `favorite_time`, `is_cancelled`, `cancel_time`, `gmt_create`, `gmt_modified`) VALUES (1, 1, '2023-04-08 00:10:00', 0, NULL, '2023-04-08 00:10:00', '2023-04-08 00:10:00');
5.6.2 取消收藏
student_id=1的学生，取消收藏 book_id=1的图书
update book_favorite
set is_cancelled = 1, cancel_time = '2023-04-08 02:10:00'
where book_id = 1 and student_id = 1;
5.7 图书评论 和 审核评论
5.7.1 评论
student_id=1的学生，评论 book_id=1的图书：status=0待审批
INSERT INTO `book_comment`(`student_id`, `book_id`, `score`, `comment`, `status`, `gmt_create`, `gmt_modified`) VALUES (1, 1, 5, '好书值得推荐', 0, '2023-04-08 00:10:00', '2023-04-08 00:10:00');
5.7.2 审核评论
verify_user_id=1的管理员，审核id=1的评论： status = 1审批通过
审批后, 图书评论次数+1
update book_comment
set `status` = 1,
verify_time = '2023-04-08 03:10:00',
verify_user_id = 1,
gmt_modified = '2023-04-08 03:10:00'
where id = 1;
-- 审批后, 图书评论次数+1
update book
comment_count = comment_count+1,
gmt_modified = '2023-04-08 03:10:00'
where id = 1;
5.8 借阅 和 借阅审核
5.8.1 借阅
student_id=1的学生，借阅 book_id=1的图书
INSERT INTO `book_borrowing`(`student_id`, `book_id`, `borrow_time`, `status`, `gmt_create`, `gmt_modified`)
VALUES (1, 1, '2023-04-08 03:10:00', 0, '2023-04-08 03:10:00', '2023-04-08 03:10:00');
5.8.2 借阅审核
verify_user_id=1的管理员，审核 id=1的借阅记录： status=1通过审批
审批后, 图书借阅次数+1
update book_borrowing
set `status` = 1,
verify_time = '2023-04-08 04:10:00',
verify_user_id = 1,
gmt_modified = '2023-04-08 04:10:00'
where id = 1;
-- 审批后, 图书借阅次数+1，状态置为借阅中
update book
set status = 1,
borrow_count = borrow_count+1,
gmt_modified = '2023-04-08 04:10:00'
where id = 1;
5.8.3 归还
id=1的借阅记录，书已归还：status = 3归还
归还后, 更新图书状态为空闲
update book_borrowing
set `status` = 3,
return_time = '2023-04-18 05:10:00',
gmt_modified = '2023-04-18 05:10:00'
where id = 1;
-- 归还后, 更新图书状态为空闲
update book
set status = 0,
gmt_modified = '2023-04-18 05:10:00'
where id = 1;
参考
https://blog.csdn.net/Artificial_idiots/article/details/106830628
https://blog.csdn.net/weixin_44584292/article/details/106636642
最后
本篇数据库设计花了很长时间来润笔，另外，还征求了大学生朋友的建议，这里特别感谢会洗碗的CV工程师为我提供了课设的相关资料，更为本文提供了很多建设性的建议，希望能在实战的同时能给大学生朋友更多的帮助，如果有任何建议和意见，也欢迎大家留言或私信，我计划将持续更新，目的只为打造精品，互相切磋，共同成长！能让大家通过这一篇数据库设计，学到实战中设计技巧！当然，这只是一个小小的系统，有很多设计技巧还无法体现，一个合理的设计非常重要，可以支撑更大的业务场景，所以仅以本设计抛转引玉，后面有机会我们可以拿更多的场景或系统来设计，如果你有需求，也可以找我来设计，我尽可能满足！
服务端实战 ：SpringBoot+Vue前后端分离项目实战
前端实战 ：Vue + SpringBoot前后端分离项目实战
另外，别忘了关注：天罡gg ，发布新文不容易错过： https://blog.csdn.net/scm_2008
原文地址:https://blog.csdn.net/scm_2008/article/details/130072010
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.7code.cn/show_17587.html
如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！
