山东科技大学图书管理系统数据库设计

1、数据库设计课程实 验 报 告学 院： 计算机科学与工程学院 班 级： 计算机 班 完 成 人：姓 名： 范浩 学 号： 姓 名： 顾约翰 学 号： 姓 名： 李文哲 学 号： 指导教师： 何明祥 山 东 科 技 大 学2018年10月数 据 库 设 计 实 验 任 务 书一、课程实验题目： 图书管理系统数据库设计 二、课程实验应解决的主要问题：（1） 业务分析应该分析准确，明确,防止遗漏 _ （2）实体描述准确，规范, 语义要合理 （3） 对数据库进行逻辑设计，要完整比如（外键主键，实体，属性 实体关联等.）_（4 设计的关系模式需要规范化处理，每个关系模式应该达到3NF （5） 绘制事务图及

2、用到触发器，完整性约束，存储过程，等操作 三、任务发出日期： 2018- - 课程实验完成日期： 2018- - 指导教师对课程实验的评价成绩： 指导教师签字： 年 月 日 目录1. 题目概述31.1 系统开发背景和意义31.2 开发环境42.系统需求分析42.1功能需求4图2.1 现行图书管理业务流程图42.2 数据描述(数据流图+数据字典)4图2.2 图书管理系统的基本系统模型5图2.3 图书管理系统数据流图（顶层DFD图）5图2.4 细化后数据流图5图2.5 “图书管理”展开52.2.1 图书基本信息的关系模式62.2.2 图书信息的关系模式62.2.3图书借阅的关系模式62.2.4图书

3、归还的关系模式62.2.5图书罚款的关系模式62.2.6图书注销的关系模式62.2.7管理员的关系模式62.3 数据安全与约束62.4 功能需求63.系统总体设计73.1 新的业务流程73.2 系统功能模块图73.3 系统的流程图83.3.1学生流程图84.详细设计94.1 数据库的设计94.1.2学生基本信息表104.1.3书籍信息表114.1.4book_sort:信息表114.1.5borrow:信息管理表114.1.6存储学生的归还信息124.1.7存储学生的罚单：124.1.8manager:信息表125.数据库代码135.1数据库表设计135.2应用程序设计与编码实现216.心得体

4、会301. 题目概述1.1 系统开发背景和意义图书管理是高校内每一个系部或院部都必须切实面对的工作，但一直以来人们使用传统的人工方式管理图书资料。这种方式存在着许多缺点，如效率低、保密性差且较为繁琐。另外，随着图书资料数量的增加，其工作量也将大大增加，这必将增加图书资料管理者的工作量和劳动强度，这将给图书资料信息的查找、更新和维护都带来了很多困难。经过详细的调查，目前我国各类高等学校中有相当一部分单位图书资料管理还停留在人工管理的基础上。这样的管理机制已经不能适应时代的发展，其管理方法将浪费许多人力和物力。随着科学技术的不断提高，这种传统的手工管理方法必然被以计算机为基础的信息管理方法所取代。

5、图书管理作为计算机应用的一个分支，有着手工管理无法比拟的优点，如检索迅速、查找方便、可靠性高、存储量大、保密性好、寿命长、成本低等。这些优点能够极大地提高图书管理的效率。因此，开发一套能够为用户提供充足的信息和快捷的查询手段的图书管理系统，将是非常必要的，也是十分及时的。1.2 开发环境开发环境的选择会影响到数据库的设计，所以在这里给出图书管理系统开发与运行环境的选择如下：开发环境：Windows 7数据库管理系统： mysql2.系统需求分析2.1功能需求1.学生可以直接通过借阅图书客户端来查阅书籍信息，同时也可以查阅自己的借阅信息。2.当学生需要借阅书籍时，通过账号密码登陆借阅系统，借阅系

6、统处理学生的借阅，同时修改图书馆保存的图书信息，修改被借阅的书籍是否还有剩余，同时更新学生个人的借阅信息。3.学生借阅图书之前需要将自己的个人信息注册，登陆时对照学生信息。4.学生直接归还图书，根据图书编码修改借阅信息5.管理员登陆管理系统后，可以修改图书信息，增加或者删除图书信息6.管理员可以注销学生信息。7.新的图书购进后，分门别类地进行归并并汇总，加以编号。8.新书编号后，上到书架以备学生借阅。根据以上调查分析，可以给出其现行业务流程图如图2.1所示。图2.1 现行图书管理业务流程图2.2 数据描述(数据流图+数据字典)经过详细的调查，我们已经清楚了解了现行的业务流程，接下来给出系统的逻

7、辑模型。构造系统逻辑模型的工具是数据流图和数据字典。根据现行图书管理的业务流程，首先把数据流图中的源点和终点都选定为学生，因此可以得到图书管理系统的基本系统模型，如图2.2所示。图2.2 图书管理系统的基本系统模型根据基本系统模型，对其逐步细化，得到描述逻辑系统细化后的数据流图，如图2.3所示。图2.3 图书管理系统数据流图（顶层DFD图）图2.4 细化后数据流图对顶层DFD图中的一个加工“图书管理”进行展开，如图2.5。图2.5 “图书管理”展开有了系统的数据流图后，还有相当多的数据信息如图书、借阅、归还等信息需要进一步描述，这就是需要定义数据字典，才能把现有的系统描述清楚。图2.7列出了系

8、统的主要数据字典2.2.1 图书基本信息的关系模式图书基本信息（book_id，book_name，book_author，book_pub，book_num，book_sort，book_record）其中book_id号是主键2.2.2 图书信息的关系模式图书信息（唯一书籍序号，名称，作者，出版社，是否在架，书籍分类，书籍登记日期）其中编号是主码2.2.3图书借阅的关系模式图书借阅（学生编号，借阅编号，图书编号，借书时间，预计归还时间，）其中借阅编号是主码，图书编号和读者编号是外码2.2.4图书归还的关系模式图书归还（归还编号，学生编号，书籍编号，借书时间，实际还书时间）其中归还编号是本表

9、的主码，图书编号和读者编号是外码2.2.5图书罚款的关系模式图书罚款 (罚款编号、学生编号，书籍编号，超期天数，处罚金额)其中罚款编号是主码，书籍编号和学生编号是外码2.2.6图书注销的关系模式图书注销（注销编号、图书编号、注销时间）其中注销编号是主码，图书编号是外码2.2.7管理员的关系模式管理员（管理员编号、姓名、年龄，电话）其中管理员是主码2.3 数据安全与约束系统安全性要求体现在数据库安全性、信息安全性和系统平台的安全性等方面。安全性先通过视图机制，不同的用户只能访问系统授权的视图，这样可提供系统数据一定程度上的安全性，再通过分配权限、设置权限级别来区别对待不同操作者对数据库的操作来提

10、高数据库的安全性；系统平台的安全性体现在操作系统的安全性、计算机系统的安全性和网络体系的安全性等方面。数据是在外部互联网络以及图书馆内部局域网中进行流动和存储的，要保证其在这一过程中的安全稳定。对于图书馆数据安全来说，就是要防止数据在传输和使用的过程中被非法复制、更改、删除和使用等。为了达到这一目的，就需要开发相应的信息管理技术和建立相应的图书馆数据信息管理系统。通过保障图书馆软件系统和硬件系统的安全稳定运行，使得图书馆数据服务系统可以持续工作，不因内部数据错误和外界人为或环境的干扰而出现中断，达到保护数据安全的最终目标。2.4 功能需求经过以上详细的用户调查，在现行业务流程和数据分析的基础上

11、，基本可以确定系统设计必须达到的目标。 以下是图书管理系统必须具备的功能：1.新进图书的登记功能：对于购进的新书，系统必须具备图书信息资料的录入功能。2.图书的查询修改功能：当图书资料发生变化，如图书丢失或有错误信息输入时，则应能够及时对数据进行修改和补充。3.借阅的登记，归还的登记功能：系统的主要功能之一，供本校学生借阅图书、归还图书，并进行登记。4.学生信息的增加、删除和修改功能：系统主要功能之一，建立学生信息，并对其进行维护。3.系统总体设计3.1 新的业务流程根据需求分析的得到的现行业务处理流程，在用户反复研究后，首先确定目标系统的业务流程，其处理流程如图3.1。3.2 系统功能模块图

12、依据需求分析阶段得到的数据流图，采用软件工程中软件设计的概念和原理，与用户成分协商后，在保证系统基本功能要求的前提下，结合系统新的业务流程确定系统必须具备的所有功能，由此给出图书管理系统的系统功能模块图如图3.2所示3.3 系统的流程图 3.3.1学生流程图图书管理员流程图：4详细设计4.1 数据库的设计在需求分析阶段已完成该系统所有的数据分析。根据该阶段所建立的概念模型，已经得出满足系统设计要求的几个关系描述，该阶段的主要工作就是把前一阶段的成果转化为具体的数据库。下面给出概念结构设计得E-R图 E-R图：表4.1.1 图书信息表的结构 4.1.2学生基本信息表列名数据类型是否为空/性质说明

13、stu_idintnotnull/PK标明学生唯一学号stu_namevarcharnotnull学生姓名stu_sexvarcharnotnull学生性别stu_ageintnotnull学生年龄stu_provarcharnotnull学生专业stu_gradevarcharnotnull学生年级stu_integrityintnonull/default=1学生信用等级4.1.3书籍信息表 4.1.4book_sort:信息表 4.1.5borrow:信息管理表 4.1.6存储学生的归还信息4.1.7存储学生的罚单：4.1.8manager:信息表5.数据库代码5.1数据库表设计 5.1

14、.1设置索引student：.为stu_id创建索引，升序排序sql:create index index_id on student(stu_id asc);为stu_name创建索引，并且降序排序sql:alter table student add index index_name(stu_name, desc);book:为book_id创建索引，升序排列sql:create index index_bid on book(book_id);.为book_record创建索引，以便方便查询图书的登记日期信息，升序：sql:create index index_brecord on bo

15、ok(book_record);borrow:.为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 affectedRecords: 0 Duplicates: 0 Warnings: 0return_table:.为stu_id和book_id创建多列索引：sql:crea

16、te index index_sid_bid on return_table(stu_id asc, book_id asc);插入索引的操作和结果如下所示：ticket:. 为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: 0 Dupli

17、cates: 0 Warnings: 0manager:.为manager_id创建索引：sql:create index index_mid on manager(manager_id);插入索引的操作和结果如下所示： 5.1.2设计视图 给出在各表上建立的视图以及使用的语句。.在表student上创建计算机专业(cs)学生的视图stu_cs：sql: create view stu_cs asselect *from studentwhere pro = cs;操作和结果：. 在表student, borrow和book上创建借书者的全面信息视图stu_borr

18、ow：sql: 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, borrowwhere student.stu_id = borrow.stu_id and book.book_id = borrow.book_id;操作和结果：.创建类别1的所有图书的视图cs_book：sql: creat

19、e view cs_book asselect *from bookwhere book.book_sort in(select book_from book_sortwhere sort_id = 1);.创建个人所有借书归还纪录视图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

20、.return_datefrom student, book, return_tablewhere student.stu_id = return_table.stu_id and book.book_id = return_table.book_id; 5.1.3、设计触发器 给出在各表上建立的触发器以及使用的语句。设计触发器borrow, 当某学生借书成功后，图书表相应的图书不在架上，变为0：sql:create trigger borrowafter insert on borrow for each rowbeginupdate book set book_num = b

21、ook_num 1 where book_id = new.book_id;end在插入表borrow之前，book_id = 1 的图书还在架上，为1：学生1借了这本书后，在borrow中插入了一条记录：在borrow中插入这条记录后，book_id =1的图书，不在架上，为0：.设计触发器trigger_return, 还书成功后，对应的书籍book_num变为1：sql:create trigger trigger_returnafter insert on return_table for each rowbeginupdate book set book_num = b

22、ook_num + 1 where book_id = new.book_id;end还书时在return_table插入表项：此时图书归还架上：.定义定时器（事件）eventJob，每天自动触发一次，扫描视图stu_borrow，若发现当前有预期归还时间小于当前时间，则判断为超期，生成处罚记录，这个定时器将每天定时触发存储过程proc_gen_ticket：sql:create event if not exists eventJobon schedule every 1 DAY /*每天触发*/on completion PRESERVEdo call proc_gen_ti

23、cket(getdate(); /*调用存储过程*/set global event_scheduler = 1;alter event eventJob on completion preserve enable; /*开启定时器*/操作和结果显示：1). 学生1借了图书1，生成借书记录stu_borrow视图，如下：2). 当他在6月15日前还书时，没有生成罚单：3). 当他在6月27日后还书时，生成罚单：.设计触发器trigger_credit，若处罚记录超过30条，则将这个学生的诚信级设置为0，下次不允许借书：sql:create trigger trigger_cred

24、itafter insert on ticketfor each row beginif (select count(*) from ticket where stu_id=new.stu_id)30 thenupdate student set stu_integrity = 0 where stu_id = new.stu_id;end if;End学生超过2次超期归还图书后，此时触动触发器trigger_credit，将学生的诚信级设置为0：5.2应用程序设计与编码实现5.2.1、系统实现中存储函数和存储过程的设计 要求给出功能描述和代码。. 设计存储过程，产生罚单proc

25、_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_date,stu_borrow.e

26、xpect_return_date),0.1*datediff(cur_date,stu_borrow.expect_return_date)from stu_borrowwhere cur_datestu_borrow.expect_return_date;End1) . 学生王文龙借了图书数据结构，生成借书记录stu_borrow视图，如下：2)2). 当他在7月1日前还书时，没有生成罚单：3) . 当他在7月1日后还书时，生成罚单：.设计学生注册信息存储过程：学生注册信息stu_registersql:create procedure stu_register(in stu

27、_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);end. 设计管理员注册信息存储过程：ma_regist

28、ersql:create procedure ma_register(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);END. 借书过程的实现:1) 设计存储函数，func_get_credit，返回学生的诚信级：create function func_get_credit(stu_id int) returns intbeginreturn(select st

29、u_integrity from student where student.stu_id = stu_id);end2) 设计存储函数，func_get_booknum，返回书籍是否在架上：create function func_get_booknum(book_id int) returns intbeginreturn(select book_num from book where book.book_id = book_id);end3) 设计存储过程proc_borrow，调用func_get_credit和func_get_booknum，判断这个学生诚信度和书籍是否在架上，若为

30、真，则借书成功，在borrrow表中插入纪录；否则提示失败：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 theninsert into borrow values(stu_id, book_id, borrow_date);else select failed to borrow;end if;EndBorrow记录为空执行函数，学生

31、王文龙借图书数据结构：call proc_borrow(1,2,now();学生王文龙的诚信级为0：借书失败修改学生诚信级为1：借书成功. 还书存储过程proc_return：当还书时，查看是否书是否超期，即查询ticket表项，当发现超期，提示交罚单后再次还书，如没有超期，则纪录归还项目到return_table中，并且删除借书纪录（以免还书后定时器仍然扫描这个纪录）：sql：create procedure proc_return(in stu_id int, in book_id int, in return_date datetime)beginDECLARE borro

32、wdate 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 =

33、book_id);insert into return_table values(stu_id, book_id, borrowdate, return_date);delete from borrowwhere borrow.stu_id = stu_id and borrow.book_id = book_id;end if;End实验操作与结果显示：学生借了图书“计算机组成原理”：超期产生了罚单，没有交罚单，payoff=1：call proc_return(“”, “”, now();提示交罚单：交罚单，调用proc_payoff:call proc_payoff(“”, “”,);交罚单成功，payoff = 0； 交罚单存储过程：修改罚单中payoff段为0，表明罚单已交：create procedure proc_payoff(in stuid int, in bookid int)beginupdate ticketset payoff = 0where ticket.stu_id = stuid and ticket.book_i