电子书管理系统

lmio 收录于类别数据库和系列 mysql数据库

2023-05-26 2023-05-26 约 3878 字预计阅读 8 分钟

系列 - mysql数据库

一. 设计背景与需求分析

设计背景

电子书管理的现状电子书即数字化的书籍，一般以电子文本或者图片格式存储文字内容，以供人们在电子显示设备上阅读。随着信息产业的发展，电子设备和互联网的日益普及，作为传统纸质图书的替代品，电子书逐渐成为人们生活中必不可少的一部分，甚至已经渐渐取代了传统的阅读方式。随着互联网及 4G/5G 网络的高速宽带服务的推广，智能移动终端的使用环境越来越方便，因此在互联网及智能移动终端上阅读电子书的需求也越来越大

现有的电子书格式主要包括 txt、html、chm、pdf、epub、mobi等, 这些电子书主要是以文件的格式存储电子书内容，其中 html、epub、mobi 等新兴的电子书可以支持多媒体以及章节版式以及一些图书基本信息，如图书编号。
选题的目的、意义

电子书系统数据库有着手工管理无法比拟的优点，如检索迅速、查找方便、可靠性高、存储量大、保密性好，成本低等等。这些优点能极大提高电子书管理的效率，因此，开发一套能够为用户提供充足的信息和快捷的查询手段的电子书管理系统是十分必要的。

功能需求

电子书储存 通过建立图书与读者的联系，图书与出版社的联系，图书评论，收藏等对电子书储存
电子书检索 支持多种检索按书名检索，按书出版时间检索，按出版社检索等
电子书上传与下载 通过管理员上传，读者购买后即可下载
读者与电子书联系 读者可以收藏图书，评论图书，购买图书，建立图书书单等

系统开发环境

系统：Windows 11
开发平台：vscode

二. 数据库概念结构设计

实体型结构

出版社实体
图书实体

图书类型实体
读者实体
评论实体
书单实体
图书格式实体
图书管理员实体

实体间的联系

一个出版社对应多个图书，一个图书对应一个出版社，出版社和图书是一对多联系。
一个图书类型对应多本图书，一本图书对应多个图书类型，图书类型和图书是多对多联系。
一本书单对应多个图书，一本图书对应多个书单，书单和图书是多对多联系。
一个读者对应多本图书，一本图书对应多个读者，读者和图书之间是多对多联系。
一个书单对应多本书，一本书对应多个书单，图书和书单属于多对多联系
一个管理员管理多本书，一本书对应多个管理员，管理员和图书之间属于多对多联系。
一个图书格式对应多本书，一本书对应一个图书格式，图书格式和图书之间属于一多对多联系。
一个读者可以创建多个书单，一个书单对应一个读者，读者创建书单属于一对多联系。
一个读者可以发表多个评论，一个评论对应一个读者，读者和评论属于一对多联系。
一本图书有多个评论，一个评论对应一本图书，图书和评论属于一对多联系。
一本图书有多个图书类型，一个图书类型对应多本图书，图书类型和图书属于多对多联系。
一个读者可以收藏多个书单，一个书单对应多个读者，读者收藏书单属于一对多联系。

整体简化 E-R 图

三. 数据库逻辑结构设计

E-R 图向关系模式转换的原则

一个 实体型 转换为一个 关系模式。

关系的属性：实体型的属性

关系的码：实体型的码
一个 1：1 联系 可以转换为一个独立的关系模式，也可以与任何 一 端对应的关系模式合并。
一个 1：n 联系 可以转换为一个独立的关系模式，也可以与 n 端对应的关系模式合并。
一个 m: n 联系 转换为一个关系模式。
三个或三个以上实体间的一个 多元关系 可以转换为一个关系模式。
具有相同 码 的关系模式可合并。

目的：减少系统中关系个数
同一实体集的实体之间的联系即 自联系，也可以按 1：1，1：n 和 m: n 三种情况分别处理。

各个表的逻辑结构

出版社

列名	数据类型	长度	主键	非空
出版社地址	varchar	50
出版社名称	varchat	50		√
出版社编号	int		√

图书表

列名	数据类型	长度	主键	非空	备注
图书编号	int		√
图书格式	varchar	50		√	外码
出版社编号	int			√	外码
简介	varchar	400
ISBN 编号	varchar	20		√
出版时间	Datetime			√
下载次数	int				默认为 0
页数	int			√
文件大小	int			√
作者	varchat	20		√
语言	varchar	20		√
价格	Numeric (8, 2)			√
书名	varchar	50		√

图书类型

列名	数据类型	长度	主键	非空
图书类型编号	int		√
图书数量	int			√
图书类型名	varchar	20		√

读者表

列名	数据类型	长度	主键	非空	备注
读者编号	int		√
读者电话	varchar	20		√
密码	varchar	20		√
简介	varchar	100
地址	varchar	50
昵称	varchar	20		√
出生日期	Datetime			√
年龄	int			√
余额	Numeric (8, 2)			√	账户余额
头像	varchat	100		√	头像 url
邮箱	varchar	50		√
性别	char	10		√	只能为“男”或“女”

评论表

列名	数据类型	长度	主键	非空	备注
评论编号	int		√
评论内容	varchar	200		√
评论时间	Datetime			√
点赞数	int				默认为 0
图书编号	int			√	外码
读者编号	int			√	外码

书单表

列名	数据类型	长度	主键	非空
书单编号	int		√
书单名	varchar	20		√
简介	varchar	100

管理员表

列名	数据类型	长度	主键	非空
管理员编号	int		√
管理员账号	varchar	20		√
账号密码	varchar	20		√

图书格式表

列名	数据类型	长度	主键	非空	备注
图书格式编号	int		√
格式名	varchar	10		√

图书收藏关系表

列名	数据类型	长度	主键	非空	备注
读者编号	int			√	外码
图书编号	int			√

书单创建关系表

列名	数据类型	长度	主键	非空	备注
书单编号	int		√
书单名	varchar	20		√
简介	varchar	100
读者编号	int			√	外码

图书购买关系表

列名	数据类型	非空	备注
读者编号	int	√	外码
图书编号	int	√	外码
下单时间	Datetime	√
订单编号	int	√

书单包括关系表

列名	数据类型	长度	主键	非空	备注
书单编号	int			√	外码
图书编号	int			√	外码

书单收藏关系表

列名	数据类型	长度	主键	非空	备注
书单编号	int			√	外码
读者编号	int			√	外码

图书格式关系表

列名	数据类型	长度	非空	备注
图书编号	int		√	外码
图书格式编号	int		√	外码
下载链接	varchar	50

视图结构

图书类型分类视图

列名	来源表
图书类型编号	图书类型表
图书类型名	图书类型表
收藏次数总和	图书收藏表

下载数前 100 视图

列名	来源表
图书编号	图书表
书名	图书表
下载次数总和	图书表

图书下载链接视图

列名来源表

书名图书表

格式名图书格式表

下载链接图书格式关系表

列名	来源表
书名	图书表
格式名	图书格式表
下载链接	图书格式关系表

四. 数据库物理结构设计

索引的设计原则

（1）如果 一个（或 一组）属性经常在查询条件中出现，则考虑早这个（或这组）属性建立索引。

（2）如果一个属性经常作为最大值和最小值等聚集函数的参数，则考虑这个属性上建立索引。

（3）如果一个（或一组）属性经常在连接操作的连接条件中出现，则考虑在这个（或这组）属性上建立索引。

索引结构设计

五. 数据库实施

建表语句

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116


CREATE TABLE IF NOT EXISTS 出版社(
    出版社编号 INT PRIMARY KEY AUTO_INCREMENT,
    出版社名称 VARCHAR(50) NOT NULL,
    出版社地址 VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS 书单 (
    书单编号 INT PRIMARY KEY AUTO_INCREMENT,
    简介 VARCHAR(100),
    书单名 VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS 图书类型 (
    图书类型编号 INT PRIMARY KEY AUTO_INCREMENT,
    图书类型名 VARCHAR(50) NOT NULL,
图书数量 INT NOT NULL
);

CREATE TABLE IF NOT EXISTS 图书 (
    图书编号 INT PRIMARY KEY AUTO_INCREMENT,
    出版社编号 INT NOT NULL,
    图书类型编号 INT NOT NULL,
    书名 VARCHAR(50) NOT NULL,
    作者 VARCHAR(20) NOT NULL,
    价格 NUMERIC(8, 2) NOT NULL,
    页数 INT NOT NULL,
    出版时间 DATETIME,
    下载次数 INT DEFAULT 0,
    文件大小 INT NOT NULL,
    ISBN编号 VARCHAR(20) NOT NULL,
    简介 VARCHAR(400),
    FOREIGN KEY(出版社编号) REFERENCES 出版社(出版社编号),
    FOREIGN KEY(图书类型编号) REFERENCES 图书类型(图书类型编号)
);

CREATE TABLE IF NOT EXISTS 读者 (
    读者编号 INT PRIMARY KEY AUTO_INCREMENT,
    姓名 VARCHAR(50) NOT NULL,
    性别 CHAR(10) CHECK (性别 IN ('男', '女')),
    出生日期 DATETIME NOT NULL,
    年龄 INT NOT NULL,
    头像 VARCHAR(100),
    邮箱 VARCHAR(50),
    余额 NUMERIC(8, 2),
    读者电话 VARCHAR(20) NOT NULL,
    昵称 VARCHAR(20) NOT NULL,
    密码 VARCHAR(20) NOT NULL
);

CREATE TABLE IF NOT EXISTS 管理员 (
    管理员编号 INT PRIMARY KEY AUTO_INCREMENT,
    管理员账号 VARCHAR(20) NOT NULL,
    账号密码 VARCHAR(20) NOT NULL
);

CREATE TABLE IF NOT EXISTS 图书格式 (
    图书格式编号 INT PRIMARY KEY AUTO_INCREMENT,
    格式名 VARCHAR(20) NOT NULL
);

CREATE TABLE IF NOT EXISTS 评论 (
    评论编号 INT PRIMARY KEY AUTO_INCREMENT,
    评论内容 VARCHAR(200) NOT NULL,
    评论时间 DATETIME NOT NULL,
    点赞数 INT DEFAULT 0,
    图书编号 INT NOT NULL,
    读者编号 INT NOT NULL,
    FOREIGN KEY(读者编号) REFERENCES 读者(读者编号),
    FOREIGN KEY(图书编号) REFERENCES 图书(图书编号)
);

CREATE TABLE IF NOT EXISTS 图书收藏关系 (
    图书编号 INT NOT NULL,
    读者编号 INT NOT NULL,
    FOREIGN KEY(读者编号) REFERENCES 读者(读者编号),
    FOREIGN KEY(图书编号) REFERENCES 图书(图书编号)
);

CREATE TABLE IF NOT EXISTS 书单创建关系 (
    书单编号 INT PRIMARY KEY AUTO_INCREMENT,
    书单名 VARCHAR(20) NOT NULL,
    简介 VARCHAR(100),
    读者编号 INT NOT NULL,
    FOREIGN KEY(读者编号) REFERENCES 读者(读者编号)
);

CREATE TABLE IF NOT EXISTS 图书购买关系 (
    下单时间 DATETIME NOT NULL,
    订单编号 INT,
    图书编号 INT NOT NULL,
    读者编号 INT NOT NULL,
    FOREIGN KEY(读者编号) REFERENCES 读者(读者编号),
    FOREIGN KEY(图书编号) REFERENCES 图书(图书编号)
);

CREATE TABLE IF NOT EXISTS 书单包括关系 (
    图书编号 INT NOT NULL,
    书单编号 INT NOT NULL,
    FOREIGN KEY(书单编号) REFERENCES 书单(书单编号),
    FOREIGN KEY(图书编号) REFERENCES 图书(图书编号)
);

CREATE TABLE IF NOT EXISTS 书单收藏关系 (
    读者编号 INT NOT NULL,
    书单编号 INT NOT NULL,
    FOREIGN KEY(书单编号) REFERENCES 书单(书单编号),
    FOREIGN KEY(读者编号) REFERENCES 读者(读者编号)
);

CREATE TABLE IF NOT EXISTS 图书格式关系 (
    图书编号 INT NOT NULL,
    图书格式编号 INT NOT NULL,
    下载链接 VARCHAR(50),
    FOREIGN KEY(图书编号) REFERENCES 图书(图书编号),
    FOREIGN KEY(图书格式编号) REFERENCES 图书格式(图书格式编号)
);

建视图语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


CREATE VIEW 图书分类(图书类型名, 图书数量) AS
SELECT 
    图书类型名,
    图书数量
FROM 图书类型
ORDER BY 图书数量 DESC;

CREATE VIEW 图书收藏排名(书名, 收藏数量) AS
SELECT
    书名,
    COUNT(*) 收藏数量
FROM 图书,图书收藏关系
WHERE 图书.图书编号=图书收藏关系.图书编号
ORDER BY 收藏数量 DESC;

CREATE VIEW 下载次数排名(书名, 下载次数) AS
SELECT 
    书名,
    下载次数
FROM 图书
ORDER BY 下载次数 DESC;

CREATE VIEW 图书下载链接(书名, 格式名, 下载链接) AS
SELECT 图书.书名, 图书格式.格式名, 图书格式关系.下载链接
FROM 图书,图书格式,图书格式关系
WHERE 图书.图书编号=图书格式关系.图书编号 AND 图书格式.图书格式编号=图书格式关系.图书格式编号

建索引语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


CREATE INDEX 书名索引 ON 图书(书名);

CREATE INDEX 出版社编号索引 ON 图书(出版社编号);

CREATE INDEX 图书类型编号索引 ON 图书(图书类型编号);

CREATE INDEX 作者索引 ON 图书(作者);

CREATE INDEX 书单索引 ON 书单(书单编号);

CREATE INDEX 昵称索引 ON 读者(昵称);

CREATE INDEX 图书下载次数索引 ON 图书(下载次数);

CREATE INDEX 图书编号索引 ON 图书收藏(图书编号);

六. 设置触发器

读者收藏时该图书收藏数+1

1
2
3
4
5
6
7
8


CREATE TRIGGER AFTER_COLLECT
AFTER INSERT
ON 图书收藏关系
FOR EACH ROW
BEGIN
UPDATE 图书 SET 收藏数量=收藏数量+1
WHERE 图书编号=NEW.图书编号;
END

目录