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

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

2、息6.管理员可以注销学生信息。通过需求定义，画出图书管理系统的数据流图：'.数据流图学生信息注册图书信息学生查询归还借阅信息管理员信息.学生学生信息借阅信息登陆借阅学生学生图书信息管理员登陆图书管理学生管理管理员学生信息'.二、系统功能设计画出系统功能模块图并用文字对各功能模块进行详细介绍。系统功能模块图：图书管理系统借阅者模块管理员模块访问模块增加/查询图书归还图书借阅图书查询图书借阅者登陆管理员登陆查询借阅注信册息个人信息修改图书信息查询借阅删信除息学生信息删除图书三、数据库设计方案图表1、系统 E-R 模型总体 E-R 图：学生借阅图书管理管理员'.精细化的局部E

3、-R 图：学生借阅 - 归还 E-R 图：学生 ID年级年龄性别诚信级学生专业学生 ID归还时间图书 ID图书 ID学生 ID管理员 E-R 图：ID号图书归还表归还借阅处罚表处罚金额超期图书图书 ID书名出版社分类姓名年龄所属单位管理员联系电话管理管理类别编号学生 ID图书借阅表图书 ID登记日期借阅时间作者数量类别名称学生图书属于图书类别'.2、设计表给出设计的表名、结构以及表上设计的完整性约束。student ：列名数据类型是否为空 / 性质说明stu_idintnot null /PK标明学生唯一学号stu_namevarcharnot null学生姓名stu_sexvarch

4、arnot null学生性别stu_ageintnot null学生年龄stu_provarcharnot null学生专业stu_gradevarcharnot null学生年级stu_integrityintnot null/default=1学生诚信级book:列名数据类型是否为空 / 性质说明book_idintnot null / PK唯一书籍序号book_namevarcharnot null书籍名称book_authorvarcharnot null书籍作者book_pubvarcharnot null书籍出版社book_numintnot null书籍是否在架上book_sor

5、tvarcharnot null书籍分类book_recorddatatimenull书籍登记日期book_sort:列名数据类型是否为空 / 性质说明sort_idvarcharnot null / PK类型编号sort_namevarcharnot null类型名称borrow: 存储学生的借书信息列名数据类型是否为空 / 性质说明student_idvarcharnot null / PK学生编号book_idvarcharnot null / PK书籍编号borrow_datedatatimenull借书时间expect_return_datedatetimenull预期归还时间ret

6、urn_table: 存储学生的归还信息列名数据类型是否为空 / 性质说明student_idvarcharnot null / PK学生编号book_idvarcharnot null / PK书籍编号borrow_datedatetimenull借书时间return_datedatatimenull实际还书时间ticket: 存储学生的罚单信息列名数据类型是否为空 / 性质说明student_idvarcharnot null / PK学生编号'.book_idvarcharnot null / PK书籍编号over_dateintnull超期天数ticket_feefloatnu

7、ll处罚金额manager:列名数据类型是否为空 / 性质说明manager_idvarcharnot null / PK管理员编号manager_namevarcharnot null管理员姓名manager_agevarcharnot null管理员年龄manager_phonevarcharnot null管理员电话3、设计索引给出在各表上建立的索引以及使用的语句。student ：1.为 stu_id 创建索引，升序排序sql:create index index_id on student(stu_id asc);2.为 stu_name 创建索引，并且降序排序sql:alter t

8、able student add index index_name(stu_name, desc); 插入索引操作和结果如下所示：mysql> create index index_id on student(stu_id asc);Query OK, 0 rows affectedRecords: 0Duplicates: 0Warnings: 0mysql> alter table student add index index_name(stu_name desc); Query OK, 0 rows affectedRecords: 0Duplicates: 0Warnin

9、gs: 0mysql>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 affectedRecords: 0Duplicates: 0Warnings: 0&#

10、39;.mysql> create index index_brecord on book(book_record);Query OK, 0 rows affectedRecords: 0Duplicates: 0Warnings: 0borrow: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

11、_id asc); Query OK, 0 rows affectedRecords: 0Duplicates: 0Warnings: 0return_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 affec

12、tedRecords: 0Duplicates: 0Warnings: 0ticket: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 affectedRecords: 0Duplicates: 0Warnings: 0manager:'.

13、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 affectedRecords: 0Duplicates: 0Warnings: 04、设计视图给出在各表上建立的视图以及使用的语句。1.在表 student上创建计算机专业(cs) 学生的视图stu_cs ：sql: create view stu_cs asselect *from

14、 studentwhere pro = cs ;操作和结果：mysql>create view stu_cs asselect *from 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，a

15、dddate(borrow_date,30) expect_return_datefrom 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 ， adddate(borrow_date,30) expect_retu

16、rn_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 asselect *from bookwhere book.book_sort infrom book_sortwhere sort_id = 1);操作和结果显示：mysql>create view cs_book ass

17、elect *from bookwhere book.book_sort in(select 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_tabl

18、e.return_datefrom 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_n

19、um1where 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_

20、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_ta

21、ble 插入表项：此时图书归还架上：3.定义定时器（事件）eventJob ，每天自动触发一次，扫描视图stu_borrow ，若发现当前有预期归还时间小于当前时间，则判断为超期， 生成处罚记录， 这个定时器将每天定时触发存储过程 proc_gen_ticket ：sql:create event if not exists eventJobon schedule every 1 DAY/* 每天触发 */on completion PRESERVEdo call proc_gen_ticket(getdate(); /*调用存储过程 */set global event_scheduler

22、= 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 row'.begi

23、nif (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.

24、设计存储过程，产生罚单proc_gen_ticket：当日期超过预定归还日期时，产生罚单，并将记录写入表ticket 中，这个存储过程在定时器eventJob中调用：sql ：create procedure proc_gen_ticket(in currentdate datetime)BEGINdeclare 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_d

25、ate,stu_borrow.expect_return_date),0.1*datediff(cur_date,stu_borrow.expect_ return_date)from stu_borrowwhere cur_date>stu_borrow.expect_return_date;end'.操作和结果显示：1). 学生 1 借了图书1，生成借书记录stu_borrow 视图，如下：2). 当他在 1 月 27 日前还书时，没有生成罚单：3). 当他在 1 月 27 日后还书时，生成罚单：2.设计学生注册信息存储过程：学生注册信息stu_registersql:cre

26、ate 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)begininsert 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);en

27、d3. 设计管理员注册信息存储过程：ma_registersql:create procedure ma_register(in ma_idint, inma_name varchar(20), inma_age int, inma_phone int)BEGINinsert into managervalues(ma_id, ma_name, ma_age, ma_phone);END4. 借书过程的实现 :1) 设计存储函数 ， func_get_credit ，返回学生的诚信级： create function func_get_credit(stu_id int) returns in

28、t beginreturn(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 beginreturn(select book_num from book where book.book_id = book_id);end3) 设计存储过程 proc_borrow ，调用 func_get_credit

29、和 func_get_booknum ，判断这个学生诚信度和书籍是否在架上， 若为真，则借书成功， 在 borrrow 表中插入纪录；否则提示失败：createprocedureproc_borrow(instu_idint,inbook_idint,inborrow_datedatetime)beginif func_get_credit(stu_id) = 1 and func_get_booknum(book_id) = 1 then insert into borrowvalues(stu_id, book_id, borrow_date);elseselect 'failed

30、 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 st

31、u_id int, in book_id int, in return_date datetime)beginDECLARE 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= (se

32、lect borrow_datefromborrowwhereborrow.stu_id=stu_id and borrow.book_id = book_id);insert into return_tablevalues(stu_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

33、 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)beginupdate ticketset payoff = 0where ticket

34、.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

35、_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 'ma