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

《图书管理系统数据库设计MYSQL实现.pdf》由会员分享，可在线阅读，更多相关《图书管理系统数据库设计MYSQL实现.pdf（21页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

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

2、图书信息 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 nul

3、l 学生性别 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

4、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 dat

5、atime 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 学生

6、编号 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

7、_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 affected Records:0 Duplicates:0 Warnings:0 mysql alter table student add inde

8、x index_namestu_name desc;Query OK,0 rows affected Records:0 Duplicates:0 Warnings:0 mysql 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 boo

9、kbook_id;Query OK,0 rows affected Records:0 Duplicates:0 Warnings:0 mysql create index index_brecord on bookbook_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 borrowstu_id asc,book_id asc;插入索引的操作和结果如下所示：mysql cr

10、eate index index_sid_bid on borrowstu_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_tablestu_id asc,book_id asc;插入索引的操作和结果如下所示：mysql create index index_sid_bid_r on return_tablestu_id as

11、c,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 ticketstu_id asc,book_id asc;插入索引的操作和结果如下所示：mysql create index index_sid_bid on ticketstu_id asc,book_id asc;Query OK,0 rows affected Records:0 Duplicates:0 W

12、arnings:0 manager:1.为 manager_id 创建索引：sql:create index index_mid on managermanager_id;插入索引的操作和结果如下所示：mysql create index index_mid on managermanager_id;Query OK,0 rows affected Records:0 Duplicates:0 Warnings:0 4、设计视图 给出在各表上建立的视图以及使用的语句;1.在表 student 上创建计算机专业 cs 学生的视图 stu_cs：sql:create view stu_cs as

13、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,adddatebor

14、row_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,adddateborrow_date,30 expect_return_date from student,book,b

15、orrow 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

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

17、ble 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

18、 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.设计

19、触发器 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,若发现当前有预期归还时间小于当前时间,则判断为超期,生成处罚记

20、录,这个定时器将每天定时触发存储过程 proc_gen_ticket：sql:create event if not exists eventJob on schedule every 1 DAY /每天触发/on completion PRESERVE do call proc_gen_ticketgetdate;/调用存储过程/set global event_scheduler=1;alter event eventJob on completion preserve enable;/开启定时器/操作和结果显示：1.学生 1 借了图书 1,生成借书记录 stu_borrow 视图,如下：

21、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_id30 then update student set stu_integrity=0 where stu_id=new.stu

22、_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 cu

23、rrentdate datetime BEGIN declare cur_date datetime;set cur_date=currentdate;replace into ticketstu_id,book_id,over_date,ticket_fee select stu_id,book_id,datediffcur_date,stu_borrow.expect_return_date,0.1datediffcur_date,stu_borrow.expect_return_date from stu_borrow where cur_datestu_borrow.expect_re

24、turn_date;end 操作和结果显示：1.学生 1 借了图书 1,生成借书记录 stu_borrow 视图,如下：2.当他在 1 月 27 日前还书时,没有生成罚单：3.当他在 1 月 27 日后还书时,生成罚单：2.设计学生注册信息存储过程：学生注册信息 stu_register sql: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 varchar20 begin

25、insert into studentstu_id,stu_name,stu_sex,stu_age,stu_pro,stu_grade valuesstu_id,stu_name,stu_sex,stu_age,stu_pro,stu_grade;end 3.设计管理员注册信息存储过程：ma_register sql:create procedure ma_registerin ma_id int,in ma_name varchar20,in ma_age int,in ma_phone int BEGIN insert into manager valuesma_id,ma_name,m

26、a_age,ma_phone;END 4.借书过程的实现:1)设计存储函数,func_get_credit,返回学生的诚信级：create function func_get_creditstu_id int returns int begin returnselect stu_integrity from student where student.stu_id=stu_id;end 2)设计存储函数,func_get_booknum,返回书籍是否在架上：create function func_get_booknumbook_id int returns int begin returns

27、elect book_num from book where book.book_id=book_id;end 3)设计存储过程proc_borrow,调用func_get_credit和func_get_booknum,判断这个学生诚信度和书籍是否在架上,若为真,则借书成功,在 borrrow表中插入纪录；否则提示失败：create procedure proc_borrowin stu_id int,in book_id int,in borrow_date datetime begin if func_get_creditstu_id=1 and func_get_booknumbook

28、_id=1 then insert into borrow valuesstu_id,book_id,borrow_date;else select failed to borrow;end if;end 实验操作与结果显示：borrow 纪录为空：执行函数,学生 1 借图书 2：call proc_borrow1,2,now;学生 1 的诚信级为 0：借书失败：修改学生 1 诚信级为 1：此时借书成功：5.还书存储过程 proc_return：当还书时,查看是否书是否超期,即查询 ticket 表项,当发现超期,提示交罚单后再次还书,如没有超期,则纪录归还项目到 return_table 中

29、,并且删除借书纪录以免还书后定时器仍然扫描这个纪录：sql：create procedure proc_returnin 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/纪录归还项目到 re

30、turn_table 中,并且删除借书纪录/set borrowdate=select borrow_date from borrow where borrow.stu_id=stu_id and borrow.book_id=book_id;insert into return_table valuesstu_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：超期产

31、生了罚单,没有交罚单,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 int begin update ticket set payoff=0 w

32、here 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_accountlocalhost;grant insert,select on student to student_accountlocalhost;grant

33、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 iden

34、tified 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_borrowin stu_id int,in book_id int,in borrow_date datetime 如果要接的书还在架上,并且学生的诚信级为 1,那么可以借书 call proc_borrow1,1,now;命令行操作

35、：表 borrow：视图 stu_borrow:表 book：还书功能:proc_returnin stu_id int,in book_id int,in return_date datetime call proc_return1,1,now;命令行操作：表 return_table:表 borrow：表 book：交罚单功能:proc_payoffin stuid int,in bookid int call proc_payoff1,1;实验操作和结果见上节：“6.交罚单存储过程“管理员添加图书：insert into book values;操作与结果：管理员删除图书：delete from book where condition;管理员注销学生信息：delete from student where condition;管理员恢复学生的诚信级：update student set stu_integrity=1 where condition;学生借书-归还流程图：管理员管理流程图：数据库设计结果：五、实习体会 自己写