数据库设计课程 实 验 报 告
学 院： 计算机科学与工程学院
班 级： 计算机 班
完 成 人：姓 名： 范浩 学 号：
姓 名： 顾约翰 学 号： 姓 名： 李文哲 学 号：
指导教师： 何明祥
山 东 科 技 大 学 2018年10月
1
数 据 库 设 计 实 验 任 务 书
一、课程实验题目： 图书管理系统数据库设计
二、课程实验应解决的主要问题：
（1） 业务分析应该分析准确，明确,防止遗漏 ______________ （2）实体描述准确，规范, 语义要合理 （3） 对数据库进行逻辑设计，要完整比如（外键主键，实体，属性 实体关联等...）_ （4 设计的关系模式需要规范化处理，每个关系模式应该达到3NF （5） 绘制事务图及用到触发器，完整性约束，存储过程，等操作
三、任务发出日期： 2018- - 课程实验完成日期： 2018- -
指导教师对课程实验的评价
成绩：
指导教师签字：
年 月 日
2
目录
1. 题目概述 ................................................................................................................................................................. 3
1.1 系统开发背景和意义 ................................................................................................................................... 3 1.2 开发环境 ....................................................................................................................................................... 4 2.系统需求分析 ........................................................................................................................................................... 4
2.1功能需求 ....................................................................................................................................................... 4
图2.1 现行图书管理业务流程图 .............................................................................................................. 4 2.2 数据描述(数据流图+数据字典) .................................................................................................................. 4
图2.2 图书管理系统的基本系统模型 ...................................................................................................... 5 图2.3 图书管理系统数据流图（顶层DFD图） .................................................................................... 5 图2.4 细化后数据流图 .............................................................................................................................. 5 图2.5 “图书管理”展开 .......................................................................................................................... 5 2.2.1 图书基本信息的关系模式 ................................................................................................................ 6 2.2.2 图书信息的关系模式 ........................................................................................................................ 6 2.2.3图书借阅的关系模式 ......................................................................................................................... 6 2.2.4图书归还的关系模式 ......................................................................................................................... 6 2.2.5图书罚款的关系模式 ......................................................................................................................... 6 2.2.6图书注销的关系模式 ......................................................................................................................... 6 2..2.7管理员的关系模式 ............................................................................................................................ 6 2.3 数据安全与约束 ................................................................................................................................... 6 2.4 功能需求 ............................................................................................................................................... 6
3.系统总体设计 ............................................................................................................................................................ 7
3.1 新的业务流程 ....................................................................................................................................... 7 3.2 系统功能模块图 ................................................................................................................................... 7 3.3 系统的流程图 ....................................................................................................................................... 8 3.3.1学生流程图 ......................................................................................................................................... 8
4.详细设计.................................................................................................................................................................... 9
4.1 数据库的设计 ....................................................................................................................................... 9 4.1.2学生基本信息表 ............................................................................................................................... 10 4.1.3书籍信息表 ....................................................................................................................................... 11 4.1.4book_sort:信息表 ............................................................................................................................... 11 4.1.5borrow:信息管理表 ........................................................................................................................... 11 4.1.6存储学生的归还信息 ....................................................................................................................... 12 4.1.7存储学生的罚单： ........................................................................................................................... 12 4.1.8manager:信息表 ................................................................................................................................. 12
5.数据库代码 .............................................................................................................................................................. 13
5.1数据库表设计 ...................................................................................................................................... 13 5.2应用程序设计与编码实现 .................................................................................................................. 21
6.心得体会 ................................................................................................................................................................. 30
3
1. 题目概述
1.1 系统开发背景和意义
图书管理是高校内每一个系部或院部都必须切实面对的工作，但一直以来人们使用传统的人工方式管理图书资料。这种方式存在着许多缺点，如效率低、保密性差且较为繁琐。另外，随着图书资料数量的增加，其工作量也将大大增加，这必将增加图书资料管理者的工作量和劳动强度，这将给图书资料信息的查找、更新和维护都带来了很多困难。
经过详细的调查，目前我国各类高等学校中有相当一部分单位图书资料管理还停留在人工管理的基础上。这样的管理机制已经不能适应时代的发展，其管理方法将浪费许多人力和物力。随着科学技术的不断提高，这种传统的手工管理方法必然被以计算机为基础的信息管理方法所取代。
图书管理作为计算机应用的一个分支，有着手工管理无法比拟的优点，如检索迅速、查找方便、可靠性高、存储量大、保密性好、寿命长、成本低等。这些优点能够极大地提高图书管理的效率。因此，开发一套能够为用户提供充足的信息和快捷的查询手段的图书管理系统，将是非常必要的，也是十分及时的。
1.2 开发环境
开发环境的选择会影响到数据库的设计，所以在这里给出图书管理系统开发与运行环境的选择如下：
开发环境：Windows 7 数据库管理系统： mysql
2.系统需求分析
2.1功能需求
1.学生可以直接通过借阅图书客户端来查阅书籍信息，同时也可以查阅自己的借阅信息。
2.当学生需要借阅书籍时，通过账号密码登陆借阅系统，借阅系统处理学生的借阅，同时修改图书馆保存的图书信息，修改被借阅的书籍是否还有剩余，同时更新学生个人的借阅信息。
3.学生借阅图书之前需要将自己的个人信息注册，登陆时对照学生信息。 4.学生直接归还图书，根据图书编码修改借阅信息
5.管理员登陆管理系统后，可以修改图书信息，增加或者删除图书信息 6.管理员可以注销学生信息。
7.新的图书购进后，分门别类地进行归并并汇总，加以编号。
4
8.新书编号后，上到书架以备学生借阅。
根据以上调查分析，可以给出其现行业务流程图如图2.1所示。
分类、编号学生借阅学生归还图书新图书新图书新图书新图书归还后的图书新图书 图2.1 现行图书管理业务流程图
2.2 数据描述(数据流图+数据字典)
经过详细的调查，我们已经清楚了解了现行的业务流程，接下来给出系统的逻辑模型。构造系统逻辑模型的工具是数据流图和数据字典。根据现行图书管理的业务流程，首先把数据流图中的源点和终点都选定为学生，因此可以得到图书管理系统的基本系统模型，如图2.2所示。
图书管理员图书借阅信息图书管理系统图书归还信息图书管理员
图2.2 图书管理系统的基本系统模型
根据基本系统模型，对其逐步细化，得到描述逻辑系统细化后的数据流图，如图2.3所示。
图书信息2图书信息D2图书信息登记借阅信息D3借阅登记借阅结果管理员13学生借阅图 书图书管理借阅管理归还图书4学生信息归还管理归还图书D4归还登记学生归还结果学生管理学生信息D1学生信息 图2.3 图书管理系统数据流图（顶层DFD图）
5
D2图书信息登记借阅信息D3借阅登记借阅结果管理员学生信息2图书信息3学生借阅图 书图书管理借阅管理归还图书4归还管理归还图书D4归还登记学生归还结果1.1学生信息新学生录入1.2学生信息D1学生信息学生查询学生信息1.3更新学生信息毕业学生清空 图2.4 细化后数据流图
对顶层DFD图中的一个加工“图书管理”进行展开，如图2.5。
D2图书信息登记图书信息图书基本信息2.23学生借阅图 书借阅信息D3借阅登记借阅结果管理员图书编号2.1编号分类建挡借阅管理归还图书4归还管理归还图书D4归还登记学生归还结果
图2.5 “图书管理”展开
有了系统的数据流图后，还有相当多的数据信息如图书、借阅、归还等信息需要进一步描述，这就是需要定义数据字典，才能把现有的系统描述清楚。图2.7列出了系统的主要数据字典 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图书归还的关系模式
图书归还（归还编号，学生编号，书籍编号，借书时间，实际还书时间） 其中归还编号是本表的主码，图书编号和读者编号是外码 2.2.5图书罚款的关系模式
图书罚款 (罚款编号、学生编号，书籍编号，超期天数，处罚金额) 其中罚款编号是主码，书籍编号和学生编号是外码 2.2.6图书注销的关系模式
图书注销（注销编号、图书编号、注销时间） 其中注销编号是主码，图书编号是外码 2..2.7管理员的关系模式
6
管理员（管理员编号、姓名、年龄，电话） 其中管理员是主码
2.3 数据安全与约束
系统安全性要求体现在数据库安全性、信息安全性和系统平台的安全性等方面。安全性先通过视图机制，不同的用户只能访问系统授权的视图，这样可提供系统数据一定程度上的安全性，再通过分配权限、设置权限级别来区别对待不同操作者对数据库的操作来提高数据库的安全性；系统平台的安全性体现在操作系统的安全性、计算机系统的安全性和网络体系的安全性等方面。
数据是在外部互联网络以及图书馆内部局域网中进行流动和存储的，要保证其在这一过程中的安全稳定。对于图书馆数据安全来说，就是要防止数据在传输和使用的过程中被非法复制、更改、删除和使用等。为了达到这一目的，就需要开发相应的信息管理技术和建立相应的图书馆数据信息管理系统。通过保障图书馆软件系统和硬件系统的安全稳定运行，使得图书馆数据服务系统可以持续工作，不因内部数据错误和外界人为或环境的干扰而出现中断，达到保护数据安全的最终目标。
2.4 功能需求
经过以上详细的用户调查，在现行业务流程和数据分析的基础上，基本可以确定系统设计必须达到的目标。
以下是图书管理系统必须具备的功能：
1.新进图书的登记功能：对于购进的新书，系统必须具备图书信息资料的录入功能。
2.图书的查询修改功能：当图书资料发生变化，如图书丢失或有错误信息输入时，则应能够及时对数据进行修改和补充。
3.借阅的登记，归还的登记功能：系统的主要功能之一，供本校学生借阅图书、归还图书，并进行登记。
4.学生信息的增加、删除和修改功能：系统主要功能之一，建立学生信息，并对其进行维护。
3.系统总体设计
3.1 新的业务流程
根据需求分析的得到的现行业务处理流程，在用户反复研究后，首先确定目标系统的业务流程，其处理流程如图3.1。
7
管理员登录用户验证合法用户图书登记登记后的图书管理员3.2 系统功能模块图
退出登录借阅或归还图书
依据需求分析阶段得到的数据流图，采用软件工程中软件设计的概念和原理，与用户成分协商后，在保证系统基本功能要求的前提下，结合系统新的业务流程确定系统必须具备的所有功能，由此给出图书管理系统的系统功能模块图如图3.2所示
3.3 系统的流程图
3.3.1学生流程图
8
图书管理员流程图：
4详细设计
4.1 数据库的设计
在需求分析阶段已完成该系统所有的数据分析。根据该阶段所建立的概念模型，已经得出满足系统设计要求的几个关系描述，该阶段的主要工作就是把前一阶段的成果转化为具体的数据库。下面给出概念结构设计得E-R图 E-R图：
9
10
表4.1.1 图书信息表的结构 4.1.2学生基本信息表 列名 数据类型 是否为空/性质 说明 stu_id int not null /PK 标明学生唯一学号 stu_name varchar not null 学生姓名 stu_sex varchar not null 学生性别 stu_age int not null 学生年龄 stu_pro varchar not null 学生专业 stu_grade varchar not null 学生年级 stu_integrity int nonull/default=1 学生信用等级 4.1.3书籍信息表
11
4.1.4book_sort:信息表
4.1.5borrow:信息管理表
4.1.6存储学生的归还信息
12
4.1.7存储学生的罚单：
4.1.8manager:信息表
5.数据库代码
5.1数据库表设计
5.1.1设置索引 student：
13
.为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 book(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 affected
Records: 0 Duplicates: 0 Warnings: 0
14
return_table:
.为stu_id和book_id创建多列索引：
sql:create 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 affected
Records: 0 Duplicates: 0 Warnings: 0
manager:
.为manager_id创建索引：
sql:create index index_mid on manager(manager_id);
15
插入索引的操作和结果如下所示：
5.1.2设计视图
给出在各表上建立的视图以及使用的语句。
5.1.2.1.在表student上创建计算机专业(cs)学生的视图stu_cs： sql: create view stu_cs as
select * from student where pro = ‘cs’;
操作和结果：
5.1.2.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;
16
操作和结果：
5.1.2.3.创建类别1的所有图书的视图cs_book： sql: 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);
5.1.2.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
17
from student, book, return_table
where student.stu_id = return_table.stu_id and book.book_id = return_table.book_id;
5.1.3、设计触发器
给出在各表上建立的触发器以及使用的语句。
5.1.3.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
在插入表borrow之前，book_id = 1 的图书还在架上，为1：
学生1借了这本书后，在borrow中插入了一条记录：
18
在borrow中插入这条记录后，book_id =1的图书，不在架上，为0：
5.1.3.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插入表项：
此时图书归还架上：
19
5.1.3.3.定义定时器（事件）eventJob，每天自动触发一次，扫描视图stu_borrow，若发现当前有预期归还时间小于当前时间，则判断为超期，生成处罚记录，这个定时器将每天定时触发存储过程proc_gen_ticket： sql:
create event if not exists eventJob on schedule every 1 DAY /*每天触发*/ on completion PRESERVE
do call proc_gen_ticket(getdate()); /*调用存储过程*/ set global event_scheduler = 1;
alter event eventJob on completion preserve enable; /*开启定时器*/ 操作和结果显示：
1). 学生1借了图书1，生成借书记录stu_borrow视图，如下：
2). 当他在6月15日前还书时，没有生成罚单：
20
3). 当他在6月27日后还书时，生成罚单：
5.1.3.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_id)>30 then
update student set stu_integrity = 0 where stu_id = new.stu_id;
end if;
End
学生201701012超过2次超期归还图书后，
此时触动触发器trigger_credit，将学生的诚信级设置为0：
21
5.2应用程序设计与编码实现
5.2.1、系统实现中存储函数和存储过程的设计
要求给出功能描述和代码。
5.2.1.1. 设计存储过程，产生罚单proc_gen_ticket：
当日期超过预定归还日期时，产生罚单，并将记录写入表ticket中，这个存储过程在定时器eventJob中调用： sql：
create procedure proc_gen_ticket(in currentdate datetime) BEGIN
declare 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,stu_borrow.expect_return_date) from stu_borrow
where cur_date>stu_borrow.expect_return_date;
22
End 1)
. 学生王文龙借了图书数据结构，生成借书记录stu_borrow视图，如下：
2)
2). 当他在7月1日前还书时，没有生成罚单：
3)
. 当他在7月1日后还书时，生成罚单：
5.2.1.2.设计学生注册信息存储过程：学生注册信息stu_register sql:
create 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))
23
begin
insert 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
5.2.1.3. 设计管理员注册信息存储过程：ma_register sql:
create procedure ma_register(in ma_id int, in ma_name varchar(20), in ma_age int, in
ma_phone int)
BEGIN
insert into manager
values(ma_id, ma_name, ma_age, ma_phone);
END
5.2.1.4. 借书过程的实现:
1) 设计存储函数，func_get_credit，返回学生的诚信级：
create function func_get_credit(stu_id int) returns int begin
return(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 begin
24
return(select book_num from book where book.book_id = book_id);
end
3) 设计存储过程proc_borrow，调用func_get_credit和func_get_booknum，判断这个学生
诚信度和书籍是否在架上，若为真，则借书成功，在borrrow表中插入纪录；否则提示失败： create procedure proc_borrow(in stu_id int, in book_id int, in borrow_date datetime) begin if func_get_credit(stu_id) = 1 and func_get_booknum(book_id) = 1 then insert into borrow
values(stu_id, book_id, borrow_date);
else
select 'failed to borrow';
end if;
End
Borrow记录为空
执行函数，学生王文龙借图书数据结构：
call proc_borrow(1,2,now()); 学生王文龙的诚信级为0：
借书失败
25
修改学生201701011诚信级为1：
借书成功
5.2.1.5. 还书存储过程proc_return：
当还书时，查看是否书是否超期，即查询ticket表项，当发现超期，提示交罚单后再次还书，如没有超期，则纪录归还项目到return_table中，并且删除借书纪录（以免还书后定时器仍然扫描这个纪录）： sql：
create procedure proc_return(in 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 /*纪录归还项目到return_table中，并且删除借书纪录*/
set borrowdate = (select borrow_date from borrow where borrow.stu_id = stu_id
and borrow.book_id = book_id);
insert into return_table
values(stu_id, book_id, borrowdate, return_date); delete from borrow
where borrow.stu_id = stu_id and borrow.book_id = book_id;
26
end if;
End
实验操作与结果显示：
学生201701011借了图书“计算机组成原理”：
超期产生了罚单，没有交罚单，payoff=1：
call proc_return(“201701011”, “123457”, now()); 提示交罚单：
交罚单，调用proc_payoff:
call proc_payoff(“201701011”, “123457”,); 交罚单成功，payoff = 0；
5.2.1.6 交罚单存储过程：
27
修改罚单中payoff段为0，表明罚单已交：
create procedure proc_payoff(in stuid int, in bookid int) begin update ticket set payoff = 0
where ticket.stu_id = stuid and ticket.book_id = bookid;
select ‘succeed’;
end
交罚单，调用proc_payoff:
call proc_payoff(“201701011”, “123457”); 交罚单成功，payoff = 0；
5.2.2、功能实现
按各功能模块进行描述。 查询图书信息
按书名查找：
select * from book where book_name = 数据结构’;
按作者查找：
select
*
from
book
where
book_author='
韩
顺
平
28
借书功能: proc_borrow(in stu_id int, in book_id int, in borrow_date datetime)
如果要借的书还在架上，并且学生的诚信级为1，那么可以借书
还书功能: proc_return(in stu_id int, in book_id int, in return_date datetime)
表return_table:
表borrow：
表book：
29
管理员添加图书：
insert into book values(…); 操作与结果：
管理员删除图书：
delete from book where (condition);
操作结果：
管理员注销学生信息：
delete from student where (condition);
30
管理员恢复学生的诚信级：
update student set stu_integrity=1 where (condition);
6.心得体会
为期2个月的数据库设计课程结束了，我通过认真的查阅资料和团队交流的终于完成
了课程设计题目，很感欣慰。回想当初一开始的时候，遇到了很多的困难，感到无从下手，后经自己的学习思考以及和同学的交流探讨终于有了思路。后来的课程设计操作过程让我也感到边学习边实践的乐趣，经过这一段时间的努力我成功的完成了本次课程设计。但是很多的地方考虑的还是不够周全。比如对表和字段的命名以及各表之间的联系命名存在欠缺，有时不能很好的表达自己的思想。课程设计也告诉我还有许多东西需要学习，课程设计当中我意识到自己的理论及实践知识的不足，对数据库设计这方面还是缺乏自主创新的思维能力，有些简单就可实现的东西自己却需好多的步骤才能完成，例如在处理借书和还书时可用事务来处理，但由于自己理论知识的不足没能得到运用。我在以后的学习中要增强自己的自学能力，有的同学在课程设计设中用到了许多没讲到的知识，例如对触发器，事务等的运用，他们的自学精神值得我去学习。这次课程设计使我对所学的数据库知识有了更进一步的理解，更加系统化，理论化。今后，无论对待什么问题，我一定会充满自信的面对，学习更多的知识来充实自己，争取做得更好。
31
32
因篇幅问题不能全部显示，请点此查看更多更全内容