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

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

1、图书管理系统数据库设计 M Y SQ L 实现标准化管理部编码-99968T-6889628-J68568-1689N图书管理系统数据库设计一、系统概述1、系统简介图书管理是每个图书馆都需要进行的工作。一个设计良好的图书管理系统数据库能够给图书管理带来很大的便利。2、需求分析图书管理系统的需求定义为：1. 学生可以直接通过借阅终端来查阅书籍信息，同时也可以查阅自己的借阅信息。2. 当学生需要借阅书籍时，通过账号密码登陆借阅系统，借阅系统处理学生的借阅， 同时修改图书馆保存的图书信息，修改被借阅的书籍是否还有剩余，同时更新学生个人的借阅信息。3. 学生借阅图书之前需要将自己的个人信息注册，登陆时

2、对照学生信息。4. 学生直接归还图书，根据图书编码修改借阅信息5. 管理员登陆管理系统后，可以修改图书信息，增加或者删除图书信息6. 管理员可以注销学生信息。通过需求定义，画出图书管理系统的数据流图：数据流图二、系统功能设计画出系统功能模块图并用文字对各功能模块进行详细介绍。 系统功能模块图：三、数据库设计方案图表1、系统 E-R 模型总体 E-R 图：精细化的局部 E-R 图： 学生借阅-归还 E-R 图： 管理员 E-R 图：2、设计表给出设计的表名、结构以及表上设计的完整性约束。student：列名数据类型是否为空/性质说明stu_idintnot null /PK标明学生唯一学号stu

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

4、ook_numintnot null书籍是否在架上book_sortvarcharnot null书籍分类book_recorddatatimenull书籍登记日期book_sort:列名数据类型是否为空/性质说明sort_idvarcharnot null / PK类型编号sort_namevarcharnot null类型名称borrow:存储学生的借书信息列名数据类型是否为空/性质说明student_idvarcharnot null / PK学生编号book_idvarcharnot null / PK书籍编号borrow_datedatatimenull借书时间expect_retu

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

6、icket_feefloatnull处罚金额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 创建索引，并且降

7、序排序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 affectedRecords: 0Duplicates: 0Warnings: 0mysql alter table studentadd indexindex_name(stu_name desc);Query OK, 0 rows affectedRecords: 0Duplicates: 0Wa

8、rnings:0mysqlbook: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: 0m

9、ysql create index index_brecord on book(book_record); Query OK, 0 rows affectedRecords: 0Duplicates: 0Warnings: 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);

10、Query OK, 0 rows affectedRecords: 0Duplicates: 0Warnings: 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 affectedRecords: 0

11、Duplicates: 0Warnings: 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 affectedRecords: 0Duplicates: 0Warnings: 0 manager:1.为 manager_id 创建索引：

12、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 studentwhere pro

13、= cs; 操作和结果：mysqlcreate view stu_cs as select *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，adddate(borrow_date,30) expe

14、ct_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_return_datefrom student, book, borroww

15、here 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 in from book_sortwhere sort_id = 1); 操作和结果显示：mysqlcreate view cs_book as select *from bookwhere book.book_sort

16、in (select book_sort.sort_name from 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, boo

17、k, return_tablewhere 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 rowbeginupdate book set book_num = book_num 1 where book_id = new.book_

18、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 中插入这条

19、记录后，book_id =1 的图书，不在架上，为 0：2. 设计触发器 trigger_return, 还书成功后，对应的书籍book_num 变为 1： sql:create trigger trigger_return after insert on return_table for each rowbeginupdate book set book_num = book_num + 1 where book_id = new.book_id;end还书时在 return_table 插入表项： 此时图书归还架上：3. 定义定时器（事件）eventJob，每天自动触发一次，扫描视图stu

20、_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 = 1;alter event eventJob on completion preserve enable; /*开启

21、定时器*/操作和结果显示：1). 学生 1 借了图书 1，生成借书记录 stu_borrow 视图，如下：2). 当他在 1 月 27 日前还书时，没有生成罚单：3). 当他在 1 月 27 日后还书时，生成罚单：4. 设计触发器 trigger_credit，若处罚记录超过 30 条，则将这个学生的诚信级设置为 0，下次不允许借书：sql:create trigger trigger_credit after insert on ticketfor each row beginthen new.stu_id;endif (select count(*) from ticket where s

22、tu_id=new.stu_id)30 update student set stu_integrity = 0 where stu_id =end if;操作和结果显示，测试时选择插入 ticket 项大于 3，因为 30 太大了，不容易测试： 学生 1 超过 3 次超期归还图书后，产生了 4 条罚单：此时触动触发器 trigger_credit，将学生 1 的诚信级设置为 0：四、应用程序设计与编码实现1、系统实现中存储函数和存储过程的设计要求给出功能描述和代码。1. 设计存储过程，产生罚单 proc_gen_ticket：当日期超过预定归还日期时，产生罚单，并将记录写入表 ticket

23、中，这个存储过程在定时器 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_date,stu_borrow.expect_return_date),0.1*datediff(cur_date

24、,s tu_borrow.expect_return_date)from stu_borrowwhere cur_datestu_borrow.expect_return_date;end操作和结果显示：1). 学生 1 借了图书 1，生成借书记录 stu_borrow 视图，如下：2). 当他在 1 月 27 日前还书时，没有生成罚单：3). 当他在 1 月 27 日后还书时，生成罚单：2. 设计学生注册信息存储过程：学生注册信息 stu_register sql:create procedure stu_register(in stu_id int, in stu_name varchar

25、(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);end3. 设计管理员注册信息存储过程：ma_register sql:create procedure ma_registe

26、r(in ma_id int, in ma_name varchar(20), in ma_age int, in ma_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 int beginreturn(select stu_integrity from student where student

27、.stu_id = stu_id);end2) 设计存储函数，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和func_get_booknum，判断这个学生诚信度和书籍是否在架上，若为真，则借书成功，在 borrrow 表中插入纪录；否则提

28、示失败：create procedure proc_borrow(in stu_id int, in book_id int, in borrow_date datetime)beginif func_get_credit(stu_id) = 1 and func_get_booknum(book_id) =1 thenelseinsert into borrowvalues(stu_id, book_id, borrow_date);select failed to borrow;end if;end实验操作与结果显示： borrow 纪录为空：执行函数，学生 1 借图书 2： call p

29、roc_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)beginDECLARE borrowdate

30、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 whereborrow.stu_id = stu_id and borrow.book_id = boo

31、k_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;endend if;实验操作与结果显示： 学生 1 借了图书 2：超期产生了罚单，没有交罚单，payoff=1： 此时调用还书过程：call proc_return(1, 2, now(); 提示交罚单：交罚单，调用 proc_payoff:call proc_payoff(1, 2);

32、交罚单成功，payoff = 0； 此时再次调用还书过程：call proc_return(1, 2, now();还书成功，在 return_table 生成了还书纪录：6. 交罚单存储过程：修改罚单中 payoff 段为 0，表明罚单已交：create procedure proc_payoff(in stuid int, in bookid int) beginendupdate ticket set payoff = 0where ticket.stu_id = stuid and ticket.book_id = bookid; select succeed;交罚单，调用 proc_

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

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

35、 查询图书信息按书名查找：select * from book where book_name = sql; 按作者查找：select * from book where book_author = author;借 书 功 能 :proc_borrow(instu_idint,inbook_idint,in borrow_date datetime)如果要接的书还在架上，并且学生的诚信级为 1，那么可以借书call proc_borrow(1, 1, now();命令行操作： 表borrow：视图stu_borrow: 表book：还 书 功 能 :proc_return(instu_idi

36、nt,inbook_id 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. 交罚单存储过程“ 管理员添加图书：int,ininsert into book values();操作与结果：管理员删除图书： delete from book where (condition); 管理员注销学生信息： delete from student where (condition);管理员恢复学生的诚信级： update studentset stu_integrity=1where (condition);学生借书-归还流程图： 管理员管理流程图： 数据库设计结果：五、实习体会自己写