2022年图书管理系统数据库设计_MYSQL实现.pdf

图书管理系统数据库设计_MYSQL 实现图书管理系统数据库设计一、系统概述1、系统简介图书管理就是每个图书馆都需要进行的工作。一个设计良好的图书管理系统数据库能够给图书管理带来很大的便利。2、需求分析图书管理系统的需求定义为: 1、学生可以直接通过借阅终端来查阅书籍信息, 同时也可以查阅自己的借阅信息。2、 当学生需要借阅书籍时, 通过账号密码登陆借阅系统, 借阅系统处理学生的借阅, 同时修改图书馆保存的图书信息, 修改被借阅的书籍就是否还有剩余, 同时更新学生个人的借阅信息。3、学生借阅图书之前需要将自己的个人信息注册, 登陆时对照学生信息。4、学生直接归还图书, 根据图书编码修改借阅信息5、管理员登陆管理系统后, 可以修改图书信息, 增加或者删除图书信息6、管理员可以注销学生信息。通过需求定义 , 画出图书管理系统的数据流图: 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 1 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现数据流图学生查询图书信息登陆借阅学生信息借阅信息归还借阅信息学生注册学生信息学生学生管理员登陆管理员信息图书管理学生管理图书信息学生信息管理员精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 2 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现二、系统功能设计画出系统功能模块图并用文字对各功能模块进行详细介绍。系统功能模块图: 图书管理系统借阅者模块管理员模块访问模块查询图书借阅图书归还图书查询图书修改图书信息增加/删除图书查询借阅信息注册个人信息查询借阅信息删除学生信息管理员登陆借阅者登陆三、数据库设计方案图表 1 、系统 E-R 模型总体 E-R 图: 学生借阅图书管理员管理精细化的局部E-R 图: 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 3 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现学生借阅 -归还 E-R 图: 学生学生ID年级年龄性别专业诚信级借阅图书图书ID书名出版社分类数量作者登记日期图书借阅表学生ID图书ID归还学生ID图书ID归还时间借阅时间图书归还表处罚表图书ID学生 ID超期处罚金额管理员 E-R 图: 管理员ID号姓名年龄所属单位联系电话管理管理学生图书属于图书类别类别编号类别名称精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 4 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现 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 超期天数精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 5 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现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 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 6 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现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 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 7 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现 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 * 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 8 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现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 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 9 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现操作与结果显示: 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 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 10 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现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: 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 11 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现四、应用程序设计与编码实现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_datestu_borrow、expect_return_date; end 操作与结果显示: 1) 、 学生 1 借了图书 1, 生成借书记录stu_borrow视图 , 如下 : 2) 、 当她在 1 月 27 日前还书时 , 没有生成罚单 : 3) 、 当她在 1 月 27 日后还书时 , 生成罚单 : 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 12 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现2、设计学生注册信息存储过程: 学生注册信息stu_registersql: 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_registersql: 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; 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 13 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现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); 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 14 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_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: 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;精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 15 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现end 交罚单 , 调用 proc_payoff: call proc_payoff(1, 2); 交罚单成功 ,payoff = 0; 2、功能实现按各功能模块进行描述。要求: 画出流程图并给出实现代码。创建学生统一账户, 账户名 :student_account,并且授予权限 : sql: create user student_accountlocalhost; grant insert,select on student to student_accountlocalhost; grant select on book to student_accountlocalhost; grant insert,select on borrow to student_accountlocalhost; grant insert,select on return_table to student_accountlocalhost; grant select on ticket to student_accountlocalhost; 创建管理员统一账户, 账户名 :manager_account, 并且授予全部权限: sql: create user manager_accountlocalhost identified by 123; grant all on library_management to manager_accountlocalhost; 查询图书信息按书名查找 :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(); 命令行操作 : 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 16 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现表 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( );操作与结果 : 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 17 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现管理员删除图书: delete from book where (condition); 管理员注销学生信息: delete from student where (condition); 管理员恢复学生的诚信级: update student set stu_integrity=1 where (condition); 学生借书 -归还流程图 : 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 18 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现登入数据库用户名： student_accont按书名 /按作者名 .查询 or 归还注册学生信息Call stu_register借书： call proc_borrow往表borrow 插入纪录成功？定时器 enventJob触发器 borrow ，修改表 book图书超期？Call proc_gen_ticket生成罚单Call proc_return查询表 ticket无纪录 or 交完罚单往表return_table 插入纪录触发器 return ，修改表 book在borrow 中删除借书纪录查询成功Yes归还结束开始精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 19 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现管理员管理流程图: 开始注册管理员信息Call ma_register事务中心添加图书Insert into book删除图书Delete from book修改学生信息Update student注销学生信息Delete from student数据库设计结果: 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 20 页，共 21 页 - - - - - - - - - - 图书管理系统数据库设计_MYSQL 实现五、实习体会自己写精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 21 页，共 21 页 - - - - - - - - - -