图书综合管理系统数据库设计MYSQL实现.docx

图书管理系统数据库设计
一、系统概述
1、系统介绍
图书管理是每个图书馆全部需要进行工作。一个设计良好图书管理系统数据库能够给图书管理带来很大便利。
2、需求分析
图书管理系统需求定义为：
1.学生能够直接经过借阅终端来查阅书籍信息，同时也能够查阅自己借阅信息。
2.当学生需要借阅书籍时，经过账号密码登陆借阅系统，借阅系统处理学生借阅，同时修改图书馆保留图书信息，修改被借阅书籍是否还有剩下，同时更新学生个人借阅信息。
3.学生借阅图书之前需要将自己个人信息注册，登陆时对照学生信息。
4.学生直接归还图书，依据图书编码修改借阅信息
5.管理员登陆管理系统后，能够修改图书信息，增加或删除图书信息
6.管理员能够注销学生信息。
经过需求定义，画出图书管理系统数据流图：
数据流图

二、系统功效设计
画出系统功效模块图并用文字对各功效模块进行具体介绍。
系统功效模块图：

三、数据库设计方案图表
1、系统E-R模型
总体E-R图：

精细化局部E-R图：
学生借阅-归还E-R图：

管理员E-R图：

2、设计表
给出设计表名、结构和表上设计完整性约束。
student：
列名
数据类型
是否为空/性质
说明
stu_id
int
not null /PK
标明学生唯一学号
stu_name
varchar
not null
学生姓名
stu_sex
varchar
not null
学生性别
stu_age
int
not null
学生年纪
stu_pro
varchar
not null
学生专业
stu_grade
varchar
not null
学生年级
stu_integrity
int
not null/default=1
学生诚信级
book:
列名
数据类型
是否为空/性质
说明
book_id
int
not null / PK
唯一书籍序号
book_name
varchar
not null
书籍名称
book_author
varchar
not null
书籍作者
book_pub
varchar
not null
书籍出版社
book_num
int
not null
书籍是否在架上
book_sort
varchar
not null
书籍分类
book_record
datatime
null
书籍登记日期
book_sort:
列名
数据类型
是否为空/性质
说明
sort_id
varchar
not null / PK
类型编号
sort_name
varchar
not null
类型名称
borrow:存放学生借书信息
列名
数据类型
是否为空/性质
说明
student_id
varchar
not null / PK
学生编号
book_id
varchar
not null / PK
书籍编号
borrow_date
datatime
null
借书时间
expect_return_date
datetime
null
预期归还时间
return_table:存放学生归还信息
列名
数据类型
是否为空/性质
说明
student_id
varchar
not null / PK
学生编号
book_id
varchar
not null / PK
书籍编号
borrow_date
datetime
null
借书时间
return_date
datatime
null
实际还书时间
ticket:存放学生罚单信息
列名
数据类型
是否为空/性质
说明
student_id
varchar
not null / PK
学生编号
book_id
varchar
not null / PK
书籍编号
over_date
int
null
超期天数
ticket_fee
float
null
处罚金额
manager:
列名
数据类型
是否为空/性质
说明
manager_id
varchar
not null / PK
管理员编号
manager_name
varchar
not null
管理员姓名
manager_age
varchar
not null
管理员年纪
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);
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 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

3.创建类别1全部图书视图cs_book：
sql: create view cs_book as
select *
from book
where book.book_sort in
(select book_sort.sort.name
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
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;

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
-> 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：

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插入表项：

此时图书归还架上：

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=new.stu_id)>30 then
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、系统实现中存放函数和存放过程设计
要求给出功效描述和代码。
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;
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 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
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 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);
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:
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 ticket.stu_id = stuid and ticket.book_id = bookid;
select ‘succeed’;
end
交罚单，调用proc_payoff:
call proc_payoff(1, 2);
交罚单成功，payoff = 0；

2、功效实现
按各功效模块进行描述。要求：画出步骤图并给出实现代码。
l 创建学生统一账户,账户名：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';
l 创建管理员统一账户，账户名：manager_account, 而且授予全部权限：
sql：
create user 'manager_account'@'localhost' identified by '123';
grant all on library_management to 'manager_account'@'localhost';
l 查询图书信息
按书名查找：
select * from book where book_name = ‘sql’;

按作者查找：
select * from book where book_author = ‘author’;

l 借书功效: 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：

l 还书功效: proc_return(in stu_id int, in book_id int, in return_date datetime)
call proc_return(1, 1, now());
命令行操作：

表return_table:

表borrow：

表book：

l 交罚单功效:proc_payoff(in stuid int, in bookid int)
call proc_payoff(1,1);
试验操作和结果见上节：“6. 交罚单存放过程“
l 管理员添加图书：
insert into book
values(…);
操作和结果：

l 管理员删除图书：
delete from book
where (condition);
l 管理员注销学生信息：
delete from student
where (condition);
l 管理员恢复学生诚信级：
update student
set stu_integrity=1
where (condition);
学生借书-归还步骤图：

管理员管理步骤图：

数据库设计结果：

五、实习体会
自己写