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

图书管理系统数据库设计一、系统概述1、系统简介图书管理是每个图书馆都需要进行的工作;一个设计良好的图书管理系统数据库能够给图书管理带来很大的便利;2、需求分析图书管理系统的需求定义为：1.学生可以直接通过借阅终端来查阅书籍信息,同时也可以查阅自己的借阅信息;2.当学生需要借阅书籍时,通过账号密码登陆借阅系统,借阅系统处理学生的借阅,同时修改图书馆保存的图书信息,修改被借阅的书籍是否还有剩余,同时更新学生个人的借阅信息;3.学生借阅图书之前需要将自己的个人信息注册,登陆时对照学生信息;4.学生直接归还图书,根据图书编码修改借阅信息5.管理员登陆管理系统后,可以修改图书信息,增加或者删除图书信息6.管理员可以注销学生信息;通过需求定义,画出图书管理系统的数据流图：数据流图二、系统功能设计画出系统功能模块图并用文字对各功能模块进行详细介绍; 系统功能模块图：三、数据库设计方案图表1、系统E-R模型总体E-R图：精细化的局部E-R图：学生借阅-归还E-R图：管理员E-R图：2、设计表给出设计的表名、结构以及表上设计的完整性约束; student：book:book_sort:borrow:存储学生的借书信息return_table:存储学生的归还信息ticket:存储学生的罚单信息manager:3、设计索引给出在各表上建立的索引以及使用的语句;student：1.为stu_id创建索引,升序排序sql:create index index_id on studentstu_id asc;2.为stu_name创建索引,并且降序排序sql:alter table student add index index_namestu_name, desc;插入索引操作和结果如下所示：mysql> create index index_id on studentstu_id asc;Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0mysql> alter table student add index index_namestu_name desc; Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0mysql>book:1.为book_id创建索引,升序排列sql:create index index_bid on bookbook_id;2.为book_record创建索引,以便方便查询图书的登记日期信息,升序：sql:create index index_brecord on bookbook_record;插入索引的操作和结果如下所示：mysql> create index index_bid on bookbook_id;Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0mysql> create index index_brecord on bookbook_record;Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0borrow:1.为stu_id和book_id创建多列索引：sql:create index index_sid_bid on borrowstu_id asc, book_id asc;插入索引的操作和结果如下所示：mysql> create index index_sid_bid on borrowstu_id asc, book_id asc;Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0return_table:1.为stu_id和book_id创建多列索引：sql:create index index_sid_bid on return_tablestu_id asc, book_id asc; 插入索引的操作和结果如下所示：mysql> create index index_sid_bid_r on return_tablestu_id asc, book_id asc;Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0ticket:1. 为stu_id和book_id创建多列索引：sql:create index index_sid_bid on ticketstu_id asc, book_id asc;插入索引的操作和结果如下所示：mysql> create index index_sid_bid on ticketstu_id asc, book_id asc;Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 0manager:1.为manager_id创建索引：sql:create index index_mid on managermanager_id;插入索引的操作和结果如下所示：mysql> create index index_mid on managermanager_id;Query OK, 0 rows affectedRecords: 0 Duplicates: 0 Warnings: 04、设计视图给出在各表上建立的视图以及使用的语句;1.在表student上创建计算机专业cs学生的视图stu_cs：sql: create view stu_cs asselectfrom studentwhere pro = ‘cs’;操作和结果：mysql> create view stu_cs asfrom studentwhere stu_pro = 'cs';Query OK, 0 rows affected2. 在表student, borrow和book上创建借书者的全面信息视图stu_borrow：sql: create view stu_borrow asselect student.stu_id, book.book_id, student.stu_name, book.book_name, borrow_date,adddateborrow_date,30 expect_return_date from student, book, borrowwhere student.stu_id = borrow.stu_id and book.book_id = borrow.book_id;操作和结果：mysql> create view stu_borrow asselect student.stu_id, book.book_id, student.stu_name, book.book_name, borrow_date,adddateborrow_date,30 expect_return_datefrom student, book, borrowwhere student.stu_id = borrow.stu_id and book.book_id = borrow.book_id;Query OK, 0 rows affected3.创建类别1的所有图书的视图cs_book：sql: create view cs_book asfrom bookwhere book.book_sort infrom book_sortwhere sort_id = 1;操作和结果显示：mysql> create view cs_book asselectfrom bookwhere book.book_sort inselect book_sort.sort_namefrom book_sortwhere sort_id = 1;Query OK, 0 rows affected4.创建个人所有借书归还纪录视图stu_borrow_return:sql:create view stu_borrow_return asselect student.stu_id, student.stu_name, book.book_id, book.book_name,return_table.borrow_date,return_table.return_date from student, book, return_tablewhere student.stu_id = return_table.stu_id and book.book_id = return_table.book_id;5、设计触发器给出在各表上建立的触发器以及使用的语句;1.设计触发器borrow, 当某学生借书成功后,图书表相应的图书不在架上,变为0：sql:create trigger borrowafter insert on borrowfor each rowbeginupdate book set book_num = book_num – 1where 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_returnafter insert on return_tablefor each rowbeginupdate book set book_num = book_num + 1where book_id = new.book_id;end还书时在return_table插入表项：此时图书归还架上：3.定义定时器事件eventJob,每天自动触发一次,扫描视图stu_borrow,若发现当前有预期归还时间小于当前时间,则判断为超期,生成处罚记录,这个定时器将每天定时触发存储过程proc_gen_ticket：sql:create event if not exists eventJobon schedule every 1 DAY /每天触发/on completion PRESERVEdo call proc_gen_ticketgetdate; /调用存储过程/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_creditafter insert on ticketfor each rowbeginif 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_ticketin currentdate datetimeBEGINdeclare cur_date datetime;set cur_date = currentdate;replace into ticketstu_id, book_id, over_date, ticket_feeselect stu_id, book_id, datediffcur_date,stu_borrow.expect_return_date,0.1datediffcur_date ,stu_borrow.expect_return_datefrom stu_borrowwhere cur_date>stu_borrow.expect_return_date;end操作和结果显示：1. 学生1借了图书1,生成借书记录stu_borrow视图,如下：2. 当他在1月27日前还书时,没有生成罚单：3. 当他在1月27日后还书时,生成罚单：2.设计学生注册信息存储过程：学生注册信息stu_registersql:create procedure stu_registerin stu_id int, in stu_name varchar20, in stu_sex varchar20, in stu_age int, in stu_pro varchar20, in stu_grade varchar20begininsert into studentstu_id, stu_name, stu_sex, stu_age, stu_pro, stu_gradevaluesstu_id, stu_name, stu_sex, stu_age, stu_pro, stu_grade;end3. 设计管理员注册信息存储过程：ma_registersql:create procedure ma_registerin ma_id int, in ma_name varchar20, in ma_age int, in ma_phone intBEGINinsert into managervaluesma_id, ma_name, ma_age, ma_phone;END4. 借书过程的实现:1)设计存储函数,func_get_credit,返回学生的诚信级：create function func_get_creditstu_id int returns intreturnselect stu_integrity from student where student.stu_id = stu_id;end2)设计存储函数,func_get_booknum,返回书籍是否在架上：create function func_get_booknumbook_id int returns intbeginreturnselect book_num from book where book.book_id = book_id;end3)设计存储过程proc_borrow,调用func_get_credit和func_get_booknum,判断这个学生诚信度和书籍是否在架上,若为真,则借书成功,在borrrow 表中插入纪录；否则提示失败：create procedure proc_borrowin stu_id int, in book_id int, in borrow_date datetimebeginif func_get_creditstu_id = 1 and func_get_booknumbook_id =1 theninsert into borrowvaluesstu_id, book_id, borrow_date;elseselect 'failed to borrow';end if;实验操作与结果显示：borrow纪录为空：执行函数,学生1借图书2：call proc_borrow1,2,now;学生1的诚信级为0：借书失败：修改学生1诚信级为1：此时借书成功：5. 还书存储过程proc_return：当还书时,查看是否书是否超期,即查询ticket表项,当发现超期,提示交罚单后再次还书,如没有超期,则纪录归还项目到return_table中,并且删除借书纪录以免还书后定时器仍然扫描这个纪录：sql：create procedure proc_returnin stu_id int, in book_id int, in return_date datetimebeginDECLARE 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_tablevaluesstu_id, book_id, borrowdate, return_date;delete from borrowwhere borrow.stu_id = stu_id and borrow.book_id = book_id;end if;end实验操作与结果显示：学生1借了图书2：超期产生了罚单,没有交罚单,payoff=1：此时调用还书过程：call proc_return1, 2, now;提示交罚单：交罚单,调用proc_payoff:call proc_payoff1, 2;交罚单成功,payoff = 0；此时再次调用还书过程：call proc_return1, 2, now;还书成功,在return_table生成了还书纪录：6. 交罚单存储过程：修改罚单中payoff段为0,表明罚单已交：create procedure proc_payoffin stuid int, in bookid intbeginupdate ticketset payoff = 0where ticket.stu_id = stuid and ticket.book_id = bookid;select ‘succeed’;end交罚单,调用proc_payoff:call proc_payoff1, 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_borrowin stu_id int, in book_id int, in borrow_date datetime如果要接的书还在架上,并且学生的诚信级为1,那么可以借书call proc_borrow1, 1, now;命令行操作：表borrow：视图stu_borrow:表book：还书功能:proc_returnin stu_id int, in book_id int, in return_date datetimecall proc_return1, 1, now;命令行操作：表return_table:表borrow：表book：交罚单功能:proc_payoffin stuid int, in bookid int call proc_payoff1,1;实验操作和结果见上节：“6. 交罚单存储过程“管理员添加图书：insert into bookvalues…;操作与结果：管理员删除图书：delete from bookwhere condition;管理员注销学生信息：delete from studentwhere condition;管理员恢复学生的诚信级：update studentset stu_integrity=1where condition;学生借书-归还流程图：管理员管理流程图：数据库设计结果：五、实习体会自己写。