图书管理系统数据库设计-MYSQL实现(2).docx

图书管理系统数据库设计-MYSQL实现(2) 图书管理系统数据库设计 一、系统概述 1、系统简介图书管理是每个图书馆都需要进行的工作。一个设计良好的图书管理系统数据库能够给图书管理带来很大的便利。 2、需求分析 图书管理系统的需求定义为： 1.学生可以直接通过借阅终端来查阅书籍信息，同时也可以查阅自己的借阅信息。 2.当学生需要借阅书籍时，通过账号密码登陆借阅系统，借阅系统处理学生的借阅，同时修改图书馆保存的图书信息，修改被借阅的书籍是否还有剩余，同时更新学生个人的借阅信息。 3.学生借阅图书之前需要将自己的个人信息注册，登陆时对照学生信息。 4.学生直接归还图书，根据图书编码修改借阅信息 5.管理员登陆管理系统后，可以修改图书信息，增加或者删除图书信息 6.管理员可以注销学生信息。 通过需求定义，画出图书管理系统的数据流图： 数据流图 二、系统功能设计 画出系统功能模块图并用文字对各功能模块进行详细介绍系统功能模块图： 三、数据库设计方案图表 1、系统E-R模型 总体E-R图： 精细化的局部E-R图： 学生借阅-归还E-R图： 管理员E-R图： 2、设计表 给出设计的表名、结构以及表上设计的完整性约束。student : book: book_so比 borrow:存储学生的借书信息 return_table: 存储学生的归还信息 存储学生的罚单信息 man ager: 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, asc); 插入索引的操作和结果如下所示： mysql> create index index_sid_bid_r on return_table(stu_id book_id asc); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 ticket:book_id asc, 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 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