图书管理系统数据库设计-MYSQL实现|江阴雨辰互联

2023年7月27日发(作者：)

图书管理系统数据库设计

一、系统概述

1、系统简介

图书管理是每个图书馆都需要进行的工作。一个设计良好的图书管理系统数据库能够给图书管理带来很大的便利。

2、需求分析

图书管理系统的需求定义为：

1.学生可以直接通过借阅终端来查阅书籍信息，同时也可以查阅自己的借阅信息。

2.当学生需要借阅书籍时，通过账号密码登陆借阅系统，借阅系统处理学生的借阅，同时修改图书馆保存的图书信息，修改被借阅的书籍是否还有剩余，同时更新学生个人的借阅信息。

3.学生借阅图书之前需要将自己的个人信息注册，登陆时对照学生信息。

4.学生直接归还图书，根据图书编码修改借阅信息

5.管理员登陆管理系统后，可以修改图书信息，增加或者删除图书信息

6.管理员可以注销学生信息。

通过需求定义，画出图书管理系统的数据流图：

数据流图

学生信息注册图书信息学生学生信息借阅信息学生查询登陆借阅学生归还学生借阅信息管理员信息图书信息管理员登陆图书管理学生管理管理员学生信息

二、系统功能设计

画出系统功能模块图并用文字对各功能模块进行详细介绍。

系统功能模块图：

图书管理系统借阅者模块管理员模块查询图书归还图书借阅图书查询借阅信息注册个人信息查询图书修改图书信息增加/删除图书查询借阅信息删除学生信息

三、数据库设计方案图表

1、系统E-R模型

总体E-R图：

学生借阅图书管理管理员

精细化的局部E-R图：

访问模块借阅者登陆管理员登陆学生借阅-归还E-R图：

学生ID年级年龄性别诚信级学生ID归还时间图书ID图书ID学生专业图书归还表归还借阅学生ID图书借阅表图书ID处罚表登记日期借阅时间学生ID处罚金额超期图书ID书名图书作者数量出版社分类

管理员E-R图：

姓名ID号年龄所属单位管理员联系电话管理管理类别编号类别名称学生

图书属于图书类别

2、设计表

给出设计的表名、结构以及表上设计的完整性约束。

student：

列名

stu_id

stu_name

stu_sex

stu_age

stu_pro

stu_grade

stu_integrity

book:

列名

book_id

book_name

book_author

book_pub

book_num

book_sort

book_record

book_sort:

列名

sort_id

sort_name

列名

student_id

book_id

borrow_date

expect_return_date

列名

student_id

book_id

borrow_date

return_date

列名

student_id

book_id

over_date

数据类型

int

varchar

int

varchar

int

数据类型

int

varchar

int

varchar

datatime

数据类型

varchar

数据类型

varchar

datatime

datetime

数据类型

varchar

datetime

datatime

数据类型

varchar

int

是否为空/性质

not null /PK

not null

not null/default=1

是否为空/性质

not null / PK

not null

null

是否为空/性质

not null / PK

not null

是否为空/性质

not null / PK

null

是否为空/性质

not null / PK

null

是否为空/性质

not null / PK

null

说明

标明学生唯一学号

学生姓名

学生性别

学生年龄

学生专业

学生年级

学生诚信级

说明

唯一书籍序号

书籍名称

书籍作者

书籍出版社

书籍是否在架上

书籍分类

书籍登记日期

说明

类型编号

类型名称

说明

学生编号

书籍编号

借书时间

预期归还时间

说明

学生编号

书籍编号

借书时间

实际还书时间

说明

学生编号

书籍编号

超期天数

borrow:存储学生的借书信息

return_table:存储学生的归还信息

ticket:存储学生的罚单信息 ticket_fee

manager:

列名

manager_id

manager_name

manager_age

manager_phone

float

数据类型

varchar

null

是否为空/性质

not null / PK

not null

处罚金额

说明

管理员编号

管理员姓名

管理员年龄

管理员电话

3、设计索引

给出在各表上建立的索引以及使用的语句。

student：

1.为stu_id创建索引，升序排序

sql:create index index_id on student(stu_id asc);

2.为stu_name创建索引，并且降序排序

sql:alter table student add index index_name(stu_name, desc);

插入索引操作和结果如下所示：

mysql> create index index_id on student(stu_id asc);

Query OK, 0 rows affected

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table student add index index_name(stu_name desc);

Query OK, 0 rows affected

Records: 0 Duplicates: 0 Warnings: 0

mysql>

book:

1.为book_id创建索引，升序排列

sql:create index index_bid on book(book_id);

2.为book_record创建索引，以便方便查询图书的登记日期信息，升序：

sql:create index index_brecord on book(book_record);

插入索引的操作和结果如下所示：

mysql> create index index_bid on book(book_id);

Query OK, 0 rows affected

Records: 0 Duplicates: 0 Warnings: 0

mysql> create index index_brecord on book(book_record);

Query OK, 0 rows affected

Records: 0 Duplicates: 0 Warnings: 0

borrow:

1.为stu_id和book_id创建多列索引：

sql:create index index_sid_bid on borrow(stu_id asc, book_id asc);

插入索引的操作和结果如下所示：

mysql> create index index_sid_bid on borrow(stu_id asc, book_id asc);

Query OK, 0 rows affected

Records: 0 Duplicates: 0 Warnings: 0

return_table:

1.为stu_id和book_id创建多列索引：

sql:create index index_sid_bid on return_table(stu_id asc, book_id asc);

插入索引的操作和结果如下所示：

mysql> create index index_sid_bid_r on return_table(stu_id asc, book_id asc);

Query OK, 0 rows affected

Records: 0 Duplicates: 0 Warnings: 0

ticket:

1. 为stu_id和book_id创建多列索引：

sql:create index index_sid_bid on ticket(stu_id asc, book_id asc);

插入索引的操作和结果如下所示：

mysql> create index index_sid_bid on ticket(stu_id asc, book_id asc);

Query OK, 0 rows affected

Records: 0 Duplicates: 0 Warnings: 0

manager:

1.为manager_id创建索引：

sql:create index index_mid on manager(manager_id);

插入索引的操作和结果如下所示：

mysql> create index index_mid on manager(manager_id);

Query OK, 0 rows affected

Records: 0 Duplicates: 0 Warnings: 0

4、设计视图

给出在各表上建立的视图以及使用的语句。

1.在表student上创建计算机专业(cs)学生的视图stu_cs：

sql: create view stu_cs as

select *

from student

where pro = ‘cs’;

操作和结果：

mysql> create view stu_cs as

select *

from student

where stu_pro = 'cs';

Query OK, 0 rows affected

2. 在表student, borrow和book上创建借书者的全面信息视图stu_borrow：

sql: create view stu_borrow as

select _id, _id, _name, _name, borrow_date，adddate(borrow_date,30) expect_return_date

from student, book, borrow

where _id = _id and _id = _id;

操作和结果：

mysql> create view stu_borrow as

select _id, _id, _name, _name, borrow_date，adddate(borrow_date,30) expect_return_date

from student, book, borrow

where _id = _id and _id = _id;

Query OK, 0 rows affected

3.创建类别1的所有图书的视图cs_book：

sql: create view cs_book as

select *

from book

where _sort in

(select book_

from book_sort

where sort_id = 1);

操作和结果显示：

mysql> create view cs_book as

select *

from book

where _sort in

(select book__name

from book_sort

where sort_id = 1);

Query OK, 0 rows affected

4.创建个人所有借书归还纪录视图stu_borrow_return:

sql:

create view stu_borrow_return as

select _id, _name, _id,

_name,return__date,return__date

from student, book, return_table

where _id = return__id and _id = return__id;

5、设计触发器

给出在各表上建立的触发器以及使用的语句。

1.设计触发器borrow, 当某学生借书成功后，图书表相应的图书不在架上，变为0：

sql:

create trigger borrow

after insert on borrow

for each row

begin

update book set book_num = book_num – 1

where book_id = _id;

end

操作与结果显示：

mysql> delimiter $$

mysql> create trigger trigger_borrow

-> after insert on borrow

-> for each row

-> begin

-> update book set book_num = book_num - 1

-> where book_id = _id;

-> end

-> $$

Query OK, 0 rows affected

在插入表borrow之前，book_id = 1 的图书还在架上，为1：

学生1借了这本书后，在borrow中插入了一条记录：

在borrow中插入这条记录后，book_id =1的图书，不在架上，为0：

2.设计触发器trigger_return, 还书成功后，对应的书籍book_num变为1：

sql:

create trigger trigger_return

after insert on return_table

for each row

begin

update book set book_num = book_num + 1

where book_id = _id;

end

还书时在return_table插入表项：

此时图书归还架上：

3.定义定时器（事件）eventJob，每天自动触发一次，扫描视图stu_borrow，若发现当前有预期归还时间小于当前时间，则判断为超期，生成处罚记录，这个定时器将每天定时触发存储过程proc_gen_ticket：

sql:

create event if not exists eventJob

on schedule every 1 DAY /*每天触发*/

on completion PRESERVE

do call proc_gen_ticket(getdate()); /*调用存储过程*/

set global event_scheduler = 1;

alter event eventJob on completion preserve enable; /*开启定时器*/

操作和结果显示：

1). 学生1借了图书1，生成借书记录stu_borrow视图，如下：

2). 当他在1月27日前还书时，没有生成罚单：

3). 当他在1月27日后还书时，生成罚单：

4.设计触发器trigger_credit，若处罚记录超过30条，则将这个学生的诚信级设置为0，下次不允许借书：

sql:

create trigger trigger_credit

after insert on ticket

for each row

begin

if (select count(*) from ticket where stu_id=_id)>30 then

update student set stu_integrity = 0 where stu_id = _id;

end if;

end

操作和结果显示，测试时选择插入ticket项大于3，因为30太大了，不容易测试：

学生1超过3次超期归还图书后，产生了4条罚单：

此时触动触发器trigger_credit，将学生1的诚信级设置为0：

四、应用程序设计与编码实现

1、系统实现中存储函数和存储过程的设计

要求给出功能描述和代码。

1. 设计存储过程，产生罚单proc_gen_ticket：

当日期超过预定归还日期时，产生罚单，并将记录写入表ticket中，这个存储过程在定时器eventJob中调用：

sql：

create procedure proc_gen_ticket(in currentdate datetime)

BEGIN

declare cur_date datetime;

set cur_date = currentdate;

replace into ticket(stu_id, book_id, over_date, ticket_fee)

select stu_id, book_id,

datediff(cur_date,stu__return_date),0.1*datediff(cur_date,stu__return_date)

from stu_borrow

where cur_date>stu__return_date;

end

操作和结果显示：

1). 学生1借了图书1，生成借书记录stu_borrow视图，如下：

2). 当他在1月27日前还书时，没有生成罚单：

3). 当他在1月27日后还书时，生成罚单：

2.设计学生注册信息存储过程：学生注册信息stu_register

sql:

create procedure stu_register(in stu_id int, in stu_name varchar(20), in stu_sex varchar(20),

in stu_age int, in stu_pro varchar(20), in stu_grade varchar(20))

begin

insert into student(stu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grade)

values(stu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grade);

end

3. 设计管理员注册信息存储过程：ma_register

sql:

create procedure ma_register(in ma_id int, in ma_name varchar(20), in ma_age int, in

ma_phone int)

BEGIN

insert into manager

values(ma_id, ma_name, ma_age, ma_phone);

END

4. 借书过程的实现:

1) 设计存储函数，func_get_credit，返回学生的诚信级：

create function func_get_credit(stu_id int) returns int

begin

return(select stu_integrity from student where _id = stu_id);

end

2) 设计存储函数，func_get_booknum，返回书籍是否在架上：

create function func_get_booknum(book_id int) returns int

begin

return(select book_num from book where _id = book_id);

end

3) 设计存储过程proc_borrow，调用func_get_credit和func_get_booknum，判断这个学生诚信度和书籍是否在架上，若为真，则借书成功，在borrrow表中插入纪录；否则提示失败：

create procedure proc_borrow(in stu_id int, in book_id int, in borrow_date datetime)

begin

if func_get_credit(stu_id) = 1 and func_get_booknum(book_id) = 1 then

insert into borrow

values(stu_id, book_id, borrow_date);

else

select 'failed to borrow';

end if;

end

实验操作与结果显示：

borrow纪录为空：

执行函数，学生1借图书2：

call proc_borrow(1,2,now());

学生1的诚信级为0：

借书失败：

修改学生1诚信级为1：

此时借书成功：

5. 还书存储过程proc_return：

当还书时，查看是否书是否超期，即查询ticket表项，当发现超期，提示交罚单后再次还书，如没有超期，则纪录归还项目到return_table中，并且删除借书纪录（以免还书后定时器仍然扫描这个纪录）：

sql：

create procedure proc_return(in stu_id int, in book_id int, in return_date datetime)

begin

DECLARE borrowdate datetime;

if (select payoff from ticket where _id = stu_id and _id=book_id)

= 1 then /*判断是否交了罚单，1表示没有交*/

select 'please pay off the ticket';

else /*纪录归还项目到return_table中，并且删除借书纪录*/

set borrowdate = (select borrow_date from borrow where _id =

stu_id and _id = book_id);

insert into return_table

values(stu_id, book_id, borrowdate, return_date);

delete from borrow

where _id = stu_id and _id = book_id;

end if;

end

实验操作与结果显示：

学生1借了图书2：

超期产生了罚单，没有交罚单，payoff=1：

此时调用还书过程：

call proc_return(1, 2, now());

提示交罚单：

交罚单，调用proc_payoff:

call proc_payoff(1, 2);

交罚单成功，payoff = 0；

此时再次调用还书过程：

call proc_return(1, 2, now());

还书成功，在return_table生成了还书纪录：

6. 交罚单存储过程：

修改罚单中payoff段为0，表明罚单已交：

create procedure proc_payoff(in stuid int, in bookid int)

begin

update ticket

set payoff = 0

where _id = stuid and _id = bookid;

select ‘succeed’;

end

交罚单，调用proc_payoff:

call proc_payoff(1, 2);

交罚单成功，payoff = 0；

2、功能实现

按各功能模块进行描述。要求：画出流程图并给出实现代码。

 创建学生统一账户,账户名：student_account，并且授予权限：

sql：

create user 'student_account'@'localhost';

grant insert,select on student to 'student_account'@'localhost';

grant select on book to 'student_account'@'localhost';

grant insert,select on borrow to 'student_account'@'localhost';

grant insert,select on return_table to 'student_account'@'localhost';

grant select on ticket to 'student_account'@'localhost';

 创建管理员统一账户，账户名：manager_account, 并且授予全部权限：

sql：

create user 'manager_account'@'localhost' identified by '123';

grant all on library_management to 'manager_account'@'localhost';

 查询图书信息

按书名查找：

select * from book where book_name = ‘sql’;

按作者查找：

select * from book where book_author = ‘author’;

 借书功能: proc_borrow(in stu_id int, in book_id int, in borrow_date datetime)

如果要接的书还在架上，并且学生的诚信级为1，那么可以借书

call proc_borrow(1, 1, now());

命令行操作：

表borrow：

视图stu_borrow:

表book：

 还书功能: proc_return(in stu_id int, in book_id int, in return_date datetime)

call proc_return(1, 1, now());

命令行操作：

表return_table:

表borrow：

表book：

 交罚单功能:proc_payoff(in stuid int, in bookid int)

call proc_payoff(1,1);

实验操作和结果见上节：“6. 交罚单存储过程“

 管理员添加图书：

insert into book

values(…);

操作与结果：

 管理员删除图书：

delete from book

where (condition);

 管理员注销学生信息：

delete from student

where (condition);

 管理员恢复学生的诚信级：

update student

set stu_integrity=1

where (condition);

学生借书-归还流程图：

开始注册学生信息Call stu_register登入数据库用户名：student_accont查询 or 归还归还Call proc_return查询查询表ticket按书名/按作者名...无纪录 or 交完罚单借书：call proc_borrow往表return_table插入纪录在borrow中删除借书纪录成功？触发器return，修改表book成功往表borrow插入纪录结束定时器enventJob触发器borrow，修改表book图书超期？YesCall proc_gen_ticket生成罚单19

管理员管理流程图：

开始注册管理员信息Call ma_register添加图书Insert into book事务中心修改学生信息Update student删除图书Delete from book数据库设计结果：

注销学生信息Delete from

student

五、实习体会

自己写

发布者：admin，转转请注明出处：http://www.yc00.com/web/1690430719a348864.html