文章目录
1.1项目需求分析简介
本数据库的用户主要是学校人员,通过对用户需求的收集和分析,获得用户对数据库的如下要求。
- 信息需求分析
(1) 图书信息:包括书籍编号,书籍名称,出版社,作者,库存量,出版日期,价格,库存,剩余量,类别等;
(2) 借阅证信息:包括借阅证编号,办证日期,可借数量等;
(3) 读者信息:读者编号,读者姓名,读者性别,读者电话
(4) 图书借还信息:包括图书编号,借阅证编号,读者编号,借阅日期,借阅数量,归还日期,归还数量,超期天数,罚款金额等;
- 功能需求
学校图书借阅系统主要由:图书信息管理,读者信息管理,借阅证信息管理,借还信息管理,规则信息等模块组成。进入改系统后,用户可以对系统中的信息进行添加、修改、删除、查询等操作,包括以下功能:
(1) 图书信息管理模块:对图书信息进行增、删、改、查等操作;
(2) 读者信息管理模块:对读者信息进行增、删、改、查等操作;
(3) 借阅证信息管理模块:增加办卡,注销借阅卡等操作;
(4) 借还信息管理模块:进行借书、续借、还书等操作,查找出逾期的人及其逾期的天数;
(5) 操作记录管理模块:对读者的借,还,续借进行实时记录,便于管理员查找出指定读者的所有借阅记录。
(6) 规则信息管理模块:记录借书逾期的人及其逾期的天数,罚款。
1.2数据字典
1.2.1数据项
- 数据项:书籍类别编号
含义说明:书籍类别的编号
类型:varchar
长度:5- 数据项:书籍类别名称
含义说明:书籍类别的名称
类型:varchar
长度:100- 数据项:书籍编号
含义说明:书籍的编号
类型:int
长度:30- 数据项:书籍名称
含义说明:书籍的名称
类型:varchar
长度:30- 数据项:作者
含义说明:书籍的作者
类型:varchar
长度:30- 数据项:出版日期
含义说明:书籍的出版日期
类型:date- 数据项:出版社
含义说明:书籍的出版社
类型:varchar
长度:50- 数据项:价格
含义说明:书籍的价格
类型:int
长度:10- 数据项:库存
含义说明:相同书籍的库存
类型:int
长度:100
取值范围:自然数- 数据项:剩余量
含义说明:相同书籍的剩余量
类型:int
长度:100
取值范围:自然数且小于库存量- 数据项:借阅证编号
含义说明:读者所办理的借阅证编号
类型:char
长度:5- 数据项:办证日期
含义说明:读者所办理的借阅证日期
类型:date- 数据项:可借数量
含义说明:该借阅证的可借数量
类型:int
长度:4- 数据项:读者编号
含义说明:读者的编号
类型:varchar
长度:10- 数据项:读者姓名
含义说明:读者的姓名
类型:varchar
长度:20- 数据项:性别
含义说明:读者的性别
类型:char
长度:10- 数据项:电话
含义说明:读者的电话
类型:int
长度:20- 数据项:借书日期
含义说明:图书被借出的日期
类型:date- 数据项:归还日期
含义说明:借出的图书归还的日期
类型:date- 数据项:是否续借
含义说明:0为否,1为续借
类型:int
长度:2- 数据项:借书数量
含义说明: 借书的数量
类型:int
长度:100- 数据项:还书数量
含义说明: 还书的数量
类型:int
长度:100- 数据项:超期天数
含义说明:借书期限超出了10天
类型:int
长度:30- 数据项:本书
含义说明:超期的本书
类型:int
长度:100- 数据项:罚款金额
含义说明:超期罚款(元/本/天)
类型:varchar
长度:100- 数据项:说明
含义说明:对操作进行备注
类型:varchar
长度:100
1.2.2数据结构
- 数据结构:图书
含义说明:是学校图书借阅管理系统的主要数据结构,定义了图书的信息
组成:图书编号,书名,作者,出版日期,出版社,库存,剩余量,类别; - 数据结构:读者
含义说明:是学校图书借阅管理系统的主要数据结构,定义了借阅者的基本信息
组成:读者编号,姓名,性别,电话号码;
3.数据结构:借阅证
含义说明:是学校图书借阅管理系统的主要数据结构,定义了借阅证的信息
组成:借阅证号,办证日期,可借数量;
4.数据结构:借,还,续借
含义说明:是学校图书借阅管理系统的主要数据结构,定义了图书借还的详细情况
组成:图书编号,借阅证编号,读者编号,借还日期,借还数量,是否续借
5.数据结构:规则
含义说明:是学校图书借阅管理系统的主要数据结构,定义了规则的详细情况
组成:读者编号,图书编号,借阅证编号,超期天数,超期本书,超期处罚
2.1数据流图
数据流程图
学校图书管理系统数据流程图设计,如图2-1所示:
系统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数据库逻辑结构设计
-
学校图书管理系统关系模型
通过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,为主键。 -
关系模型对应的二维表
(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
),
KEYbo1
(typeid
),
CONSTRAINTbook_ibfk_1
FOREIGN KEY (typeid
) REFERENCESbook_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
),
KEYread_id
(read_id
),
CONSTRAINTjs_card_ibfk_1
FOREIGN KEY (read_id
) REFERENCESreader
(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
),
KEY1
(js_id
),
CONSTRAINTreader_ibfk_1
FOREIGN KEY (js_id
) REFERENCESjs_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 ‘借还说明’,
KEYbb1
(js_id
),
KEYbb2
(read_id
),
KEYbb3
(book_id
),
CONSTRAINTbb1
FOREIGN KEY (js_id
) REFERENCESjs_card
(js_id
),
CONSTRAINTbb2
FOREIGN KEY (read_id
) REFERENCESreader
(read_id
),
CONSTRAINTbb3
FOREIGN KEY (book_id
) REFERENCESbook
(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 ‘超期处罚’,
KEYjs-id
(js_id
),
KEYread_id
(read_id
),
KEYbook_id
(book_id
),
CONSTRAINTsf_rule_ibfk_3
FOREIGN KEY (js_id
) REFERENCESjs_card
(js_id
),
CONSTRAINTsf_rule_ibfk_1
FOREIGN KEY (read_id
) REFERENCESreader
(read_id
),
CONSTRAINTsf_rule_ibfk_2
FOREIGN KEY (book_id
) REFERENCESbook
(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数据的载入
book表信息
SQL语句:INSERT INTO library
.book
VALUES(4, 《底层逻辑》,刘一,2021-12-20, 人民出版社, 34, 100, 100, B1);
结果如下图4-1:
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数据库的功能实现
实现信息的添加
结果如图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
ONborrow_back
FOR EACH ROW
UPDATEbook
SETresidue
=residue
-NEW .js_count
WHEREbook_id
=NEW .book_id
;
结果如图4-8所示:
6. 创建触发器实现借阅图书时,自动更新借阅证的可借量
CREATE TRIGGER jie_2 – 借书减少借阅证的可借量
BEFORE INSERT
ONborrow_back
FOR EACH ROW
UPDATEjs_card
SETkj_number
=kj_number
-NEW .js_count
WHEREjs_id
=NEW .js_id
;
结果如图4-9所示:
7. 创建触发器实现还书图书时,自动更新图书的剩余量
DELIMITER @@
CREATE TRIGGER huan_1 – 还书增加剩余量
BEFORE UPDATE
ONborrow_back
FOR EACH ROW
BEGIN
IF OLD .due
IS NULL AND NEW .due
IS NOT NULL
THEN
UPDATEbook
SETresidue
=residue
+NEW .hs_count
WHEREbook_id
=NEW .book_id
;
END IF;
END @@
创建触发器实现归还图书时,自动更新借阅卡的可借量
CREATE TRIGGER huan_2 – 还书增加借阅卡的可借量
BEFORE UPDATE
ONborrow_back
FOR EACH ROW
UPDATEjs_card
SETkj_number
=kj_number
+NEW .hs_count
WHEREjs_id
=NEW .js_id
;
结果如图4-11所示:
9. 创建触发器实现借还图书时,记录的自动更新
DELIMITER@@-- 创建触发器,实现借,还,续借时,记录的自动更新
CREATE TRIGGER record222
AFTER UPDATE ONborrow_back
FOR EACH ROW
BEGIN
INSERT INTOrecord
VALUES(NULL,NEW .read_id
,NOW(),NEW .text_jh
);
END@@
结果如图4-12所示:
11.创建存储过程查询指定读者的借书情况
DELIMITER @@
CREATE PROCEDURE reader_xx(IN a VARCHAR(10))
BEGIN
SELECT * FROMrecord
WHEREread_id
=a ;
END@@
结果如图4-14所示:
12.创建视图查询图书的书籍编号,书籍名称,库存量,剩余量
CREATE VIEW v_book
AS
SELECTbook_id
AS 书籍编号 ,book_name
书籍名称,
inventory
库存量,residue
剩余量
FROMbook
;
结果如图4-15所示:
查找超期10天的人
SELECT *,TIMESTAMPDIFF(DAY,
borrowdate
,due
)-10 超期天数
FROMborrow_back
WHERE TIMESTAMPDIFF(DAY,borrowdate
,due
)>=10;
结果如图4-16所示:
14.创建存储过程计算罚款金额
DELIMITER @@
CREATE PROCEDURE mony(IN c VARCHAR(10))
BEGIN
SELECT , 0.2days
*num
AS 罚款金额 FROMsf_rule
WHEREjs_id
=c;
END@@
DELIMITER ;
CALL mony(‘a11’);
结果如图4-17所示:
在数据库中备份数据,文件名为myemployees.sql
如图5-1所示:
2.转储与备份的导入
如图5-2所示:
对于这次的学校图书借阅管理系统的设计上,还存在一些细化的问题,该系统只能满足一般的功能需求,存在一定的缺陷。比如,先在借还表中查找借书逾期人员,然后需要手动将信息导入规则表中。
在读者进行借书时,缺少权限的设置。应该创建一个存储过程,传入借阅证号和书籍编号,没有图书时,输出“该书籍不存在”;借阅证的可借量为0时,输出“该卡不能借阅”。但对于数据库语法不够熟练,出错太多,该功能未能实现
最后,感谢老师和同学的指导和帮助,以后我会更加用心学习知识,并运用到实际中。
参考文献:
[1]数据库原理及应用:MySQL版:微课视频版/李月军,付良延编著.—北京:清华大学出版社,2019(2021.2重印).
[2] 钱雪忠、陈国俊等,数据库原理及应用实验指导[M].北京:北京邮电大学出版社,2015.
来源地址:https://blog.csdn.net/m0_53524766/article/details/125821268