图书管理系统数据库设计MYSQL实现(20210123135943)

图书管理系统数据库设计

一、系统概述

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

2、需求分析图书管理系统的需求定义为：

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

2.当学生需要借阅书籍时，通过账号密码登陆借阅系统，借阅系统处理学生的借阅，同时

修改图书馆保存的图书信息，修改被借阅的书籍是否还有剩余，同时更新学生个人的借阅信息。

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

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

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

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

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

二、系统功能设计

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

三、数据库设计方案图表

1 、系统 E-R 模型总体 E-R 图：精细化的局部 E-R 图：学生借阅 - 归还 E-R 图：管理员 E-R 图：

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

ticket: 存储学生的罚单信息

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

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 , , , , borrow_date ， adddate(borrow_date,30) expect_return_date from student, book, borrow

where = and = ;

操作和结果：

mysql> create view stu_borrow as

select , , , , borrow_date ， adddate(borrow_date,30) expect_return_date from student, book, borrow

where = and = ;

Query OK, 0 rows affected

3.创建类别 1 的所有图书的视图 cs_book ： sql: create view cs_book as

select *

from book

where in

from book_sort

where sort_id = 1); 操作和结果显示：

mysql> create view cs_book as

select *

from book

where in (select from book_sort

where sort_id = 1);

Query OK, 0 rows affected

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

create view stu_borrow_return as select , , , ,,

from student, book, return_table

where = and = ;

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 = ;

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 = ;

-> 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 = ;

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=>30 then update student set

stu_integrity = 0 where stu_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,,*datediff(cur_date,

from stu_borrow

where cur_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 = 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 = 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 = stu_id and =book_id) = 1 then /* 判断是否交了罚单， 1 表示没有交 */

select 'please pay off the ticket';

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

set borrowdate = (select borrow_date from borrow where = stu_id and = book_id);

insert into return_table values(stu_id, book_id, borrowdate,

return_date); delete from borrow where = stu_id and = 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 = stuid and = 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 = a‘uthor '; 借书功能 : 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);

学生借书 - 归还流程图：管理员管理流程图：数据库设计结果：

五、实习体会

自己写