一.需求分析

1.1项目需求分析简介

本数据库的用户主要是学校人员，通过对用户需求的收集和分析，获得用户对数据库的如下要求。

1. 信息需求分析
   (1) 图书信息：包括书籍编号，书籍名称，出版社，作者，库存量，出版日期，价格，库存，剩余量，类别等；
   (2) 借阅证信息：包括借阅证编号，办证日期，可借数量等；
   (3) 读者信息：读者编号，读者姓名，读者性别，读者电话
   (4) 图书借还信息：包括图书编号，借阅证编号，读者编号，借阅日期，借阅数量，归还日期，归还数量，超期天数，罚款金额等；

2. 功能需求
   学校图书借阅系统主要由：图书信息管理，读者信息管理，借阅证信息管理，借还信息管理，规则信息等模块组成。进入改系统后，用户可以对系统中的信息进行添加、修改、删除、查询等操作，包括以下功能：
   (1) 图书信息管理模块：对图书信息进行增、删、改、查等操作；
   (2) 读者信息管理模块：对读者信息进行增、删、改、查等操作；
   (3) 借阅证信息管理模块：增加办卡，注销借阅卡等操作；
   (4) 借还信息管理模块：进行借书、续借、还书等操作，查找出逾期的人及其逾期的天数；
   (5) 操作记录管理模块：对读者的借，还，续借进行实时记录，便于管理员查找出指定读者的所有借阅记录。
   (6) 规则信息管理模块：记录借书逾期的人及其逾期的天数，罚款。

1.2数据字典

1.2.1数据项

1. 数据项：书籍类别编号
   含义说明：书籍类别的编号
   类型：varchar
   长度：5
2. 数据项：书籍类别名称
   含义说明：书籍类别的名称
   类型：varchar
   长度：100
3. 数据项：书籍编号
   含义说明：书籍的编号
   类型：int
   长度：30
4. 数据项：书籍名称
   含义说明：书籍的名称
   类型：varchar
   长度：30
5. 数据项：作者
   含义说明：书籍的作者
   类型：varchar
   长度：30
6. 数据项：出版日期
   含义说明：书籍的出版日期
   类型：date
7. 数据项：出版社
   含义说明：书籍的出版社
   类型：varchar
   长度：50
8. 数据项：价格
   含义说明：书籍的价格
   类型：int
   长度：10
9. 数据项：库存
   含义说明：相同书籍的库存
   类型：int
   长度：100
   取值范围：自然数
10. 数据项：剩余量
    含义说明：相同书籍的剩余量
    类型：int
    长度：100
    取值范围：自然数且小于库存量
11. 数据项：借阅证编号
    含义说明：读者所办理的借阅证编号
    类型：char
    长度：5
12. 数据项：办证日期
    含义说明：读者所办理的借阅证日期
    类型：date
13. 数据项：可借数量
    含义说明：该借阅证的可借数量
    类型：int
    长度：4
14. 数据项：读者编号
    含义说明：读者的编号
    类型：varchar
    长度：10
15. 数据项：读者姓名
    含义说明：读者的姓名
    类型：varchar
    长度：20
16. 数据项：性别
    含义说明：读者的性别
    类型：char
    长度：10
17. 数据项：电话
    含义说明：读者的电话
    类型：int
    长度：20
18. 数据项：借书日期
    含义说明：图书被借出的日期
    类型：date
19. 数据项：归还日期
    含义说明：借出的图书归还的日期
    类型：date
20. 数据项：是否续借
    含义说明：0为否，1为续借
    类型：int
    长度：2
21. 数据项：借书数量
    含义说明： 借书的数量
    类型：int
    长度：100
22. 数据项：还书数量
    含义说明： 还书的数量
    类型：int
    长度：100
23. 数据项：超期天数
    含义说明：借书期限超出了10天
    类型：int
    长度：30
24. 数据项：本书
    含义说明：超期的本书
    类型：int
    长度：100
25. 数据项：罚款金额
    含义说明：超期罚款（元/本/天）
    类型：varchar
    长度：100
26. 数据项：说明
    含义说明：对操作进行备注
    类型：varchar
    长度：100

1.2.2数据结构

1. 数据结构：图书
   含义说明：是学校图书借阅管理系统的主要数据结构，定义了图书的信息
   组成：图书编号，书名，作者，出版日期，出版社，库存，剩余量，类别；
2. 数据结构：读者
   含义说明：是学校图书借阅管理系统的主要数据结构，定义了借阅者的基本信息
   组成：读者编号，姓名，性别，电话号码；
   3.数据结构：借阅证
   含义说明：是学校图书借阅管理系统的主要数据结构，定义了借阅证的信息
   组成：借阅证号，办证日期，可借数量；
   4.数据结构：借，还，续借
   含义说明：是学校图书借阅管理系统的主要数据结构，定义了图书借还的详细情况
   组成：图书编号，借阅证编号，读者编号，借还日期，借还数量，是否续借
   5.数据结构：规则
   含义说明：是学校图书借阅管理系统的主要数据结构，定义了规则的详细情况
   组成：读者编号，图书编号，借阅证编号，超期天数，超期本书，超期处罚

二.数据库概念结构设计

2.1数据流图

1.数据流程图
学校图书管理系统数据流程图设计，如图2-1所示：

2.系统E-R图
（1）图书实体E-R图如图2-2所示：

（2）图书类别实体E-R图如图2-3所示：

（3）借阅证实体E-R图如图2-4所示：

图2-4 借阅证实体E-R图

（4）读者实体E-R图如图2-5所示：

图2-5 读者实体E-R图

（5）规则实体E-R图如图2-6所示：

2.2数据库逻辑结构设计

1. 学校图书管理系统关系模型
   通过E-R图转换得到关系模型如下：
   （1） book(book_id，book_name，author，pubdate，publish，price，inventory，residue，typeid)
   （2） book_type(typeid，typename)
   （3） js_crad(js_id，period，read_id，state，kj_number)
   （4） reader(read_id,read_name,read_sex,read_tel,js_id
   （5） borrow_back(js_id，read_id，book_id，borrowdate，due，shf_xvjie，js_count，hs_count，text_jh)
   （6） sf_rule(read_id,book_id,js_id,days,num,mony)
   （7） record(jishu, read_id, time, text)
   说明：
   （1） book关系模型中有9个属性，其中book_id（书籍编号）为主键，typeid为外键；
   （2） book_type表有2个属性，其中typeid为主键，图书分类；
   （3） js_crad表有5个属性，其中js_id为主键，read_id为外键；
   （4） reader表中有5个属性，其中read_id为主键，js_id为外键
   （5） borrow_back有9个属性，其中js_id,read_id,book_id为外键，没有主键
   （6） sf_rule表有6个属性，其中js_id,read_id,book_id为外键，没有主键
   （7） record表有4个属性，其中jishu,为主键。

2. 关系模型对应的二维表
   （1） book表信息如下表所示：(书籍信息)
   
   
   （3） js_card表信息如下表所示：(借阅证信息)
   
   
   
   
   

三.数据库物理结构设计

3.1创建数据库

CREATE DATEBASE library;

3.2创建表同时创建参照完整性约束

3.2.1 book表(图书)

CREATE TABLE book (
book_id INT(10) NOT NULL,
book_name VARCHAR(30) NOT NULL,
author VARCHAR(30) NOT NULL,
pubdate DATE NOT NULL COMMENT ‘出版日期’,
publish VARCHAR(50) NOT NULL COMMENT ‘出版社’,
Price INT(20) NOT NULL,
inventory INT(100) NOT NULL COMMENT ‘总数量’,
residue INT(100) NOT NULL COMMENT ‘剩余量’,
typeid VARCHAR(20) NOT NULL COMMENT ‘书籍类别’,
PRIMARY KEY (book_id),
KEY bo1 (typeid),
CONSTRAINT book_ibfk_1 FOREIGN KEY (typeid) REFERENCES book_type (typeid)
)

3.2.2 book_type表(图书类别)

CREATE TABLE book_type (
typeid VARCHAR(5) NOT NULL,
typename VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (typeid)
)

3.2.3 js_card表(借阅证)

CREATE TABLE js_card (
js_id CHAR(5) NOT NULL,
period DATE DEFAULT NULL COMMENT ‘签证日期’,
read_id VARCHAR(10) DEFAULT NULL,
state VARCHAR(50) DEFAULT NULL COMMENT ‘说明’,
kj_number INT(4) DEFAULT NULL COMMENT ‘可借数量’,
is-kejie VARCHAR(10) DEFAULT NULL COMMENT ‘是否可借’,
PRIMARY KEY (js_id),
KEY read_id (read_id),
CONSTRAINT js_card_ibfk_1 FOREIGN KEY (read_id) REFERENCES reader (read_id)
)

3.2.4 reader表

CREATE TABLE reader (
read_id VARCHAR(10) NOT NULL,
read_name VARCHAR(20) NOT NULL,
read_sex CHAR(2) NOT NULL,
read_tel INT(20) NOT NULL,
js_id CHAR(5) NOT NULL,
PRIMARY KEY (read_id),
KEY 1 (js_id),
CONSTRAINT reader_ibfk_1 FOREIGN KEY (js_id) REFERENCES js_card (js_id)
)

3.2.5 corrow_back表(借，还，续借)

CREATE TABLE borrow_back (
js_id VARCHAR(10) DEFAULT NULL COMMENT ‘借书证号’,
read_id VARCHAR(10) DEFAULT NULL COMMENT ‘读者编号’,
book_id INT(10) DEFAULT NULL COMMENT ‘书籍编号’,
borrowdate DATE DEFAULT NULL COMMENT ‘出借日期’,
due DATE DEFAULT NULL COMMENT ‘归还日期’,
shf_xvjie TINYINT(4) DEFAULT NULL COMMENT ‘是否续借’,
js_count INT(100) DEFAULT NULL COMMENT ‘借书个数（本）’,
hs_count INT(100) DEFAULT NULL COMMENT ‘还书个数（本）’,
text_jh VARCHAR(100) DEFAULT NULL COMMENT ‘借还说明’,
KEY bb1 (js_id),
KEY bb2 (read_id),
KEY bb3 (book_id),
CONSTRAINT bb1 FOREIGN KEY (js_id) REFERENCES js_card (js_id),
CONSTRAINT bb2 FOREIGN KEY (read_id) REFERENCES reader (read_id),
CONSTRAINT bb3 FOREIGN KEY (book_id) REFERENCES book (book_id)
)

3.2.6 sf_rele规则表

CREATE TABLE sf_rule ( read_id varchar(10) DEFAULT NULL,
book_id int(10) DEFAULT NULL COMMENT ‘书籍编号’,
js_id varchar(10) DEFAULT NULL COMMENT ‘借书证号’,
days int(30) DEFAULT NULL COMMENT ‘期限’,
num int(100) DEFAULT NULL COMMENT ‘本书’,
mony varchar(100) DEFAULT NULL COMMENT ‘超期处罚’,
KEY js-id (js_id),
KEY read_id (read_id),
KEY book_id (book_id),
CONSTRAINT sf_rule_ibfk_3 FOREIGN KEY (js_id) REFERENCES js_card (js_id),
CONSTRAINT sf_rule_ibfk_1 FOREIGN KEY (read_id) REFERENCES reader (read_id),
CONSTRAINT sf_rule_ibfk_2 FOREIGN KEY (book_id) REFERENCES book (book_id)

3.2.7 recorf (操作记录表)

CREATE TABLE record (
jishu int(100) NOT NULL AUTO_INCREMENT,
read_id varchar(10) NOT NULL,
time datetime DEFAULT NULL,
text varchar(200) DEFAULT NULL,
PRIMARY KEY (jishu)
)

四.数据库的实现与维护

4.1数据的载入与功能的实现

4.1.1数据的载入

1.book表信息
SQL语句：INSERT INTO library.book
VALUES(4, 《底层逻辑》,刘一,2021-12-20, 人民出版社, 34, 100, 100, B1);
结果如下图4-1：

2.js_card表信息

SQL语句：INSERT INTO library.js_card
VALUES(a11,2021-12-2,re1,最多借5本，不得超期,5);

结果如图4-2所示：
3.reader表信息
SQL语句：INSERT INTO library.reader
VALUES(re1,刘三,男,199999991，a11);
结果如图4-3所示：

4.1.2数据库的功能实现

1.实现信息的添加
结果如图4-4所示：

2. 实现信息的删除
删除操作记录表中读者编号为re3的借还记录
结果如图4-5所示：

3.信息修改操作
UPDATE js_card SET state=‘不得超期10天’ WHERE js_id=‘b11’;
结果如图4-6所示：

4.信息查询操作
SELECT * FROM book_type;
结果如图4-7所示：

5. 创建触发器实现借阅图书时，自动更新图书剩余量

CREATE TRIGGER jie_1 – 借书减少剩余量
AFTER INSERT
ON borrow_back
FOR EACH ROW
UPDATE book SET residue=residue-NEW .js_count WHERE book_id=NEW .book_id ;

结果如图4-8所示：

6. 创建触发器实现借阅图书时，自动更新借阅证的可借量

CREATE TRIGGER jie_2 – 借书减少借阅证的可借量
BEFORE INSERT
ON borrow_back
FOR EACH ROW
UPDATE js_card SET kj_number=kj_number-NEW .js_count WHERE js_id=NEW .js_id;

结果如图4-9所示：

7. 创建触发器实现还书图书时，自动更新图书的剩余量

DELIMITER @@
CREATE TRIGGER huan_1 – 还书增加剩余量
BEFORE UPDATE
ON borrow_back
FOR EACH ROW
BEGIN
IF OLD .due IS NULL AND NEW .due IS NOT NULL
THEN
UPDATE book SET residue=residue+NEW .hs_count WHERE book_id=NEW .book_id;
END IF;
END @@

8.创建触发器实现归还图书时，自动更新借阅卡的可借量

CREATE TRIGGER huan_2 – 还书增加借阅卡的可借量
BEFORE UPDATE
ON borrow_back
FOR EACH ROW
UPDATE js_card SET kj_number=kj_number+NEW .hs_count WHERE js_id=NEW .js_id;

结果如图4-11所示：

9. 创建触发器实现借还图书时，记录的自动更新

DELIMITER@@-- 创建触发器，实现借，还，续借时，记录的自动更新
CREATE TRIGGER record222
AFTER UPDATE ON borrow_back
FOR EACH ROW
BEGIN
INSERT INTO record
VALUES(NULL,NEW .read_id,NOW(),NEW .text_jh);
END@@

结果如图4-12所示：

11.创建存储过程查询指定读者的借书情况

DELIMITER @@
CREATE PROCEDURE reader_xx(IN a VARCHAR(10))
BEGIN
SELECT * FROM record WHERE read_id=a ;
END@@

结果如图4-14所示：

12.创建视图查询图书的书籍编号，书籍名称，库存量，剩余量

CREATE VIEW v_book
AS
SELECT book_id AS 书籍编号 ,book_name 书籍名称,
inventory库存量,residue 剩余量
FROM book;
结果如图4-15所示：

13.查找超期10天的人

SELECT *,TIMESTAMPDIFF(DAY,borrowdate,due)-10 超期天数
FROM borrow_back
WHERE TIMESTAMPDIFF(DAY,borrowdate,due)>=10;

结果如图4-16所示：

14.创建存储过程计算罚款金额

DELIMITER @@
CREATE PROCEDURE mony(IN c VARCHAR(10))
BEGIN
SELECT , 0.2days*num AS 罚款金额 FROM sf_rule WHERE js_id=c;
END@@

DELIMITER ;
CALL mony(‘a11’);

结果如图4-17所示：

五.数据库的转储与恢复

1.在数据库中备份数据，文件名为myemployees.sql
如图5-1所示：

2.转储与备份的导入
如图5-2所示：

六.总结

对于这次的学校图书借阅管理系统的设计上，还存在一些细化的问题，该系统只能满足一般的功能需求，存在一定的缺陷。比如，先在借还表中查找借书逾期人员，然后需要手动将信息导入规则表中。
在读者进行借书时，缺少权限的设置。应该创建一个存储过程，传入借阅证号和书籍编号，没有图书时，输出“该书籍不存在”；借阅证的可借量为0时，输出“该卡不能借阅”。但对于数据库语法不够熟练，出错太多，该功能未能实现
最后，感谢老师和同学的指导和帮助，以后我会更加用心学习知识，并运用到实际中。

参考文献：
[1]数据库原理及应用：MySQL版：微课视频版/李月军，付良延编著.—北京：清华大学出版社，2019（2021.2重印）.
[2] 钱雪忠、陈国俊等，数据库原理及应用实验指导[M].北京:北京邮电大学出版社，2015.