图书管理系统数据库设计
一、系统概述
1、系统简介
图书管理是每个图书馆都需要进行的工作。一个设计良好的图书管理系统数据库能够给图书管理带来很大的便利。
2、需求分析
图书管理系统的需求定义为：
1.学生可以直接通过借阅终端来查阅书籍信息，同时也可以查阅自己的借阅信息。
2.当学生需要借阅书籍时，通过账号密码登陆借阅系统，借阅系统处理学生的借阅，同时修改图书馆保存的图书信息，修改被借阅的书籍是否还有剩余，同时更新学生个人的借阅信息。
3.学生借阅图书之前需要将自己的个人信息注册，登陆时对照学生信息。
4.学生直接归还图书，根据图书编码修改借阅信息
1
图书管理系统数据库设计-MYSQL实现
5.管理员登陆管理系统后，可以修改图书信息，增加或者删除图书信息
6.管理员可以注销学生信息。
通过需求定义，画出图书管理系统的数据流图： 数据流图
2
图书管理系统数据库设计-MYSQL实现
学生信息注册图书信息学生学生信息借阅信息学生查询登陆借阅学生归还学生借阅信息管理员信息图书信息管理员登陆图书管理学生管理管理员学生信息
二、系统功能设计
3
图书管理系统数据库设计-MYSQL实现 画出系统功能模块图并用文字对各功能模块进行详细介绍。 系统功能模块图： 借阅者模块查询图书归还图书借阅图书查询借阅信息注册个人信息
三、数据库设计方案图表
1、系统E-R模型 总体E-R图：
图书管理系统管理员模块查询图书修改图书信息增加/删除图书查询借阅信息删除学生信息4
访问模块借阅者登陆管理员登陆图书管理系统数据库设计-MYSQL实现 学生借阅图书管理管理员 精细化的局部E-R图： 学生借阅-归还E-R图： 学生ID年级年龄性别诚信级学生ID归还时间图书ID图书ID学生专业图书归还表归还借阅学生ID图书借阅表图书ID处罚表登记日期借阅时间学生ID处罚金额超期图书ID书名图书作者数量出版社分类 管理员E-R图：
5
图书管理系统数据库设计-MYSQL实现 姓名ID号年龄所属单位管理员联系电话管理管理类别编号类别名称学生 图书属于图书类别 2、设计表 给出设计的表名、结构以及表上设计的完整性约束。 student：
列名 stu_id stu_name stu_sex stu_age stu_pro 数据类型 int varchar varchar int varchar 是否为空/性质 not null /PK not null not null not null not null 说明 标明学生唯一学号 学生姓名 学生性别 学生年龄 学生专业 6
图书管理系统数据库设计-MYSQL实现 stu_grade stu_integrity varchar int not null not null/default=1 学生年级 学生诚信级 book:
列名 book_id book_name book_author book_pub book_num book_sort book_record 数据类型 int varchar varchar varchar int varchar datatime 是否为空/性质 not null / PK not null not null not null not null not null null 说明 唯一书籍序号 书籍名称 书籍作者 书籍出版社 书籍是否在架上 书籍分类 书籍登记日期 book_sort:
列名 sort_id sort_name 数据类型 varchar varchar 是否为空/性质 not null / PK not null 说明 类型编号 类型名称 borrow:存储学生的借书信息
列名 student_id book_id 数据类型 varchar varchar 是否为空/性质 not null / PK not null / PK 说明 学生编号 书籍编号 7
图书管理系统数据库设计-MYSQL实现 borrow_date expect_return_date datatime datetime null null 借书时间 预期归还时间 return_table:存储学生的归还信息
列名 student_id book_id borrow_date return_date 数据类型 varchar varchar datetime datatime 是否为空/性质 not null / PK not null / PK null null 说明 学生编号 书籍编号 借书时间 实际还书时间 ticket:存储学生的罚单信息
列名 student_id book_id over_date ticket_fee 数据类型 varchar varchar int float 是否为空/性质 not null / PK not null / PK null null 说明 学生编号 书籍编号 超期天数 处罚金额 manager:
列名 manager_id manager_name manager_age 数据类型 varchar varchar varchar 是否为空/性质 not null / PK not null not null 说明 管理员编号 管理员姓名 管理员年龄 8
图书管理系统数据库设计-MYSQL实现 manager_phone
varchar 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);
9
图书管理系统数据库设计-MYSQL实现
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
10
图书管理系统数据库设计-MYSQL实现
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
11
图书管理系统数据库设计-MYSQL实现
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);
插入索引的操作和结果如下所示：
12
图书管理系统数据库设计-MYSQL实现
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、设计视图
给出在各表上建立的视图以及使用的语句。
13
图书管理系统数据库设计-MYSQL实现
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：
14
图书管理系统数据库设计-MYSQL实现
sql: create view stu_borrow as
select student.stu_id, book.book_id, student.stu_name, book.book_name, borrow_date，adddate(borrow_date,30) expect_return_date
from student, book, borrow
where student.stu_id = borrow.stu_id and book.book_id = borrow.book_id;
操作和结果：
mysql> create view stu_borrow as
select student.stu_id, book.book_id, student.stu_name, book.book_name, borrow_date，adddate(borrow_date,30) expect_return_date
from student, book, borrow
where student.stu_id = borrow.stu_id and book.book_id = borrow.book_id;
Query OK, 0 rows affected
15
图书管理系统数据库设计-MYSQL实现
3.创建类别1的所有图书的视图cs_book： sql: create view cs_book as select * from book
where book.book_sort in (select boo from book_sort where sort_id = 1); 操作和结果显示：
mysql> create view cs_book as select * from book
where book.book_sort in (select book_sort.sort_name
16
图书管理系统数据库设计-MYSQL实现
from book_sort where sort_id = 1);
Query OK, 0 rows affected
4.创建个人所有借书归还纪录视图stu_borrow_return: sql:
create view stu_borrow_return as
select student.stu_id, student.stu_name, book.book_id, book.book_name,return_table.borrow_date,return_table.return_date
from student, book, return_table
where student.stu_id = return_table.stu_id and book.book_id = return_table.book_id;
17
图书管理系统数据库设计-MYSQL实现
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 = new.book_id; end
操作与结果显示： mysql> delimiter $$
mysql> create trigger trigger_borrow
18
图书管理系统数据库设计-MYSQL实现
-> after insert on borrow -> for each row -> begin
-> update book set book_num = book_num - 1 -> where book_id = new.book_id; -> end -> $$
Query OK, 0 rows affected
在插入表borrow之前，book_id = 1 的图书还在架上，为1：
学生1借了这本书后，在borrow中插入了一条记录：
在borrow中插入这条记录后，book_id =1的图书，不在架上，为0：
19
图书管理系统数据库设计-MYSQL实现
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 = new.book_id; end
还书时在return_table插入表项：
此时图书归还架上：
20
图书管理系统数据库设计-MYSQL实现
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日前还书时，没有生成罚单：
21
图书管理系统数据库设计-MYSQL实现
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=new.stu_id)>30 then
22
图书管理系统数据库设计-MYSQL实现
update student set stu_integrity = 0 where stu_id = new.stu_id;
end if; end
操作和结果显示，测试时选择插入ticket项大于3，因为30太大了，不容易测试：
学生1超过3次超期归还图书后，产生了4条罚单：
此时触动触发器trigger_credit，将学生1的诚信级设置为0：
四、应用程序设计与编码实现
1、系统实现中存储函数和存储过程的设计
23
图书管理系统数据库设计-MYSQL实现
要求给出功能描述和代码。
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_borrow.expect_return_date),0.1*datediff(cur_date,stu_borrow.expect_return_date)
from stu_borrow
where cur_date>stu_borrow.expect_return_date;
24
图书管理系统数据库设计-MYSQL实现
end
操作和结果显示：
1). 学生1借了图书1，生成借书记录stu_borrow视图，如下：
2). 当他在1月27日前还书时，没有生成罚单：
3). 当他在1月27日后还书时，生成罚单：
2.设计学生注册信息存储过程：学生注册信息stu_register sql:
25
图书管理系统数据库设计-MYSQL实现
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);
26
图书管理系统数据库设计-MYSQL实现
END
4. 借书过程的实现:
1) 设计存储函数，func_get_credit，返回学生的诚信级：
create function func_get_credit(stu_id int) returns int
begin
return(select stu_integrity from student where student.stu_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 book.book_id = book_id);
end
27
图书管理系统数据库设计-MYSQL实现
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纪录为空：
28
图书管理系统数据库设计-MYSQL实现
执行函数，学生1借图书2： call proc_borrow(1,2,now()); 学生1的诚信级为0：
借书失败：
修改学生1诚信级为1：
此时借书成功：
5. 还书存储过程proc_return：
29
图书管理系统数据库设计-MYSQL实现
当还书时，查看是否书是否超期，即查询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 ticket.stu_id = stu_id and ticket.book_id=book_id) = 1 then /*判断是否交了罚单，1表示没有交*/
select 'please pay off the ticket';
else /*纪录归还项目到return_table中，并且删除借书纪录*/ set borrowdate = (select borrow_date from borrow where borrow.stu_id = stu_id and borrow.book_id = book_id);
insert into return_table
values(stu_id, book_id, borrowdate, return_date);
30
图书管理系统数据库设计-MYSQL实现
delete from borrow
where borrow.stu_id = stu_id and borrow.book_id = book_id; end if; end
实验操作与结果显示： 学生1借了图书2：
超期产生了罚单，没有交罚单，payoff=1：
此时调用还书过程：
call proc_return(1, 2, now()); 提示交罚单：
交罚单，调用proc_payoff:
31
图书管理系统数据库设计-MYSQL实现
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, begin update ticket set payoff = 0
32
bookid int)
in 图书管理系统数据库设计-MYSQL实现
where ticket.stu_id = stuid and ticket.book_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';
33
图书管理系统数据库设计-MYSQL实现
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’;
34
图书管理系统数据库设计-MYSQL实现
按作者查找：
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：
35
图书管理系统数据库设计-MYSQL实现
还书功能: 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)
36
图书管理系统数据库设计-MYSQL实现
call proc_payoff(1,1);
实验操作和结果见上节：“6. 交罚单存储过程“ 管理员添加图书： insert into book values(…); 操作与结果：
管理员删除图书： delete from book where (condition); 管理员注销学生信息： delete from student where (condition); 管理员恢复学生的诚信级：
37
图书管理系统数据库设计-MYSQL实现
update student set stu_integrity=1 where (condition); 学生借书-归还流程图：
38
图书管理系统数据库设计-MYSQL实现 开始注册学生信息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生成罚单39
图书管理系统数据库设计-MYSQL实现 管理员管理流程图： 开始注册管理员信息Call ma_register添加图书Insert into book事务中心修改学生信息Update student删除图书Delete from book注销学生信息Delete from student 数据库设计结果： 40
图书管理系统数据库设计-MYSQL实现
五、实习体会 自己写
41
因篇幅问题不能全部显示，请点此查看更多更全内容