网络数据库课程综合性实验报告图书借阅系统数据库设计

1、华北科技学院计算机系综合性实验报告华北科技学院计算机系综合性实验实 验 报 告 课程名称 网络数据库 实验学期 至 学年 第 学期学生所在系部 年级 专业班级 学生姓名 学号 任课教师 实验成绩 计算机系制实验报告须知1、 学生上交实验报告时，必须为打印稿（a4纸）。页面空间不够，可以顺延。2、 学生应该填写的内容包括：封面相关栏目、实验地点、时间、目的、设备环境、内容、结果及分析等。3、 教师应该填写的内容包括：实验成绩、教师评价等。4、 教师根据本课程的综合性实验指导单中实验内容的要求，评定学生的综合性实验成绩；要求在该课程期末考试前将实验报告交给任课教师。综合性实验中，所涉及的程序，文档

2、等在交实验报告前，拷贝给任课教师。任课教师统一刻录成光盘，与该课程的期末考试成绩一同上交到系里存档。5、 未尽事宜，请参考该课程的实验大纲和教学大纲。网络数据库课程综合性实验报告开课实验室：软件一室 1 年月 日实验题目图书借阅系统数据库设计一、实验目的通过该实验把数据库的理论知识（数据库和数据表的设计理论、数据完整性的实现、存储过程、触发器、数据库安全等）应用到具体的综合实例中，达到数据库知识整合的目的。二、设备与环境硬件：多媒体计算机软件：windowsxp以上的操作系统、visual basic或其它可视化语言及sql server 2008版本三、实验内容及要求1.数据库设计要求数据库

3、设计要合理，对数据库设计作必要的说明并抓图。数据库名必须与自己真实姓名有关，所有同学不能同名。图不要太大，看清即可。2数据表设计要求数据表设计要合理，要符合数据库设计的理论范式，对数据表设计作必要的说明并抓图。数据表名必须与自己真实姓名有关，所有同学不能同名。3视图设计要求根据系统需求作必要的视图设计，如在一次查询中涉及到多个表，应该创建视图。不可以只取一个表的几个字段就算创建视图。4索引设计要求根据系统需求作必要的索引设计，本系统需要的聚集索引、非聚集索引、唯一索引、全文索引等。5数据完整性设计根据系统需求作必要的数据完整性设计，本系统需要的实体完整性体现、域完整性体现、参照完整性体现等。6

4、存储过程和触发器设计根据系统需求作必要的存储过程和触发器设计，本系统需要的存储过程和触发器设计。必要的存储过程和触发器设计都要写全说明，图可以是一个表的完整存储过程或触发器。7备份与恢复设计根据系统需求作必要的备份与恢复设计，如需要对那些内容备份，备份策略、由谁来做备份、什么时间做备份等。8数据库安全设计根据系统需求作必要的数据库安全设计，如本系统分几级用户、分别是什么角色成员具有什么操作权限等。四、实验结果及分析1.数据库设计（一）设计思想图书管理系统数据库的主要任务是对读者信息、管理员信息、图书资料信息、借阅归还图书信息、罚款信息的基本信息的操作及处理。此系统功能分为面向读者和面向管理员两

5、部分，其中读者可以进行借阅、续借、归还和查询书籍等操作，管理员可以完成书籍和读者信息的增加，删除和修改，对数据表维护。根据以上功能分析，图书管理系统应该包含：读者信息表、管理员信息表、图书信息表、借阅信息表。针对图书管理系统目前的用户量（假设为15000人）和图书量（假设为10万册）考虑，系统主数据文件初始大小设置为：110m（15000*512byte（读者表一条记录大小）+ 100000*1024byte（图书表一条记录大小）+ 其他表大小），自动增长设置为20m；日志文件设置为：5m，自动增长设置为5m。考虑图书管理系统最经常的操作是查找，所以为了提高查找效率，应为图书表、读者表建立索引

6、。（二）具体实现 通过sql语句来创建图书管理数据库，创建代码如下：create database tsgl_133on(name = tsgl,filename = j:tsgl.mdf,size = 110 mb,maxsize = unlimited,filegrowth = 20 mb)log on(name = tsgl_log,filename = j:tslg_log.ldf,size = 5 mb,maxsize = unlimited,filegrowth = 5 mb);2数据表设计（一）设计思想根据以上对图书管理系统的功能分析，需要设计读者信息表、管理员信息表、图书信息表

7、和借阅信息表。（1）读者表(学号、姓名、性别、系部、专业班级、借阅数量、欠罚款、联系电话)读者表主要记录读者信息，通过读者表可以查看读者的具体信息，如：学号、姓名、性别、系部、专业班级、借阅数量、欠罚款等。（2）管理员表(工号、姓名、性别、管理员身份、登陆口令、登陆密码、联系电话)管理员表主要记录管理员信息，通过管理员表可以查询管理员信息以及用于验证管理员身份及其管理权限。（3）图书表（图书编号、isbn条码、书名、作者、出版社、出版日期、单价、分类、借阅状态、管藏位置）图书表主要记录图书的各种信息，包括：isbn条码、书名、作者、出版社、出版日期、单价、分类、借阅状态、管藏位置。读者可以通过

8、查询此表得到需要的与图书相关的信息。例如：读者可以通过查询图书的isbn号，查看该图书当前的借阅状态和馆藏位置等信息。（4）借阅表（图书编号、学号、借阅日期、应还日期、归还日期、罚款金额、缴纳状态）读者通过借阅表查询借书情况，例如：借阅日期、应还日期、归还日期等信息。（二）具体实现通过sql语句来创建图书管理数据表，创建代码如下：use tsgl_133gocreate table t_reader(学号 char(12) not null primary key,姓名 nchar(4) not null,性别 bit null default 1,系部 nchar(10) null,专业班级

9、 nchar(8) null,借阅数量 int null default 0,欠罚款 float null,联系电话 char(11) null)gocreate table t_admin(工号 char(12) not null primary key,姓名 nchar(4) not null,性别 bit null default 1,管理员身份 nchar(10) not null,登陆口令 char(12) not null,登陆密码 char(16) not null,联系电话 char(11) null,)gocreate table t_book(图书编号 int identi

10、ty primary key,isbn条码 char(20) not null,书名 nchar(30) not null,作者 nchar(20) null,出版社 nchar(20) null,出版日期 date null,单价 float null,分类 nchar(10) null,借阅状态 bit null,馆藏位置 nchar(20) null,)gocreate table t_lend(图书编号 int not null,学号 char(12) not null,借阅日期 date not null,应还日期 date not null,归还日期 date not null,罚

11、款金额 float null,缴纳状态 bit null,primary key(图书编号,学号)go3视图设计（一）设计思想（1）为了查询读者借阅情况时方便，建立读者借阅情况视图。其字段信息包括：学号、姓名、性别、系部、专业,班级、图书编号、借阅日期、应还日期、归还日期、罚款金额、缴纳状态（2）建立图书借阅情况视图。其字段信息包括：图书编号、isbn、书名、借阅状态，借阅日期，应还日期。（3）工作人员需要查看当前逾期未还的图书，所以需要建立逾期未还图书信息视图，其字段包括：学号、姓名、系部、专业班级、书名、借阅日期、应还日期、罚款金额。其中，罚款金额通过计算得出。（4）读者需要查看图书信息，

12、所以建立图书信息视图，其字段包括：isbn条码、书名、图书总量、可外借数量、馆藏位置。其中图书总量和可外借数量通过计算得出。（5）为数据库所有表创建单独的视图，如图书表视图、管理员表视图、借阅表视图和读者表视图。（二）具体实现（1）读者借阅情况视图实现代码：use tsgl_133gocreate view reader_lended_viewasselect t_reader.学号,姓名,性别=(case when 性别=1 then 男 else 女 end),系部,专业,班级,图书编号,借阅日期,应还日期,归还日期,罚款金额,缴纳状态from t_lend,t_readerwhere t

13、_lend.学号 = t_reader.学号（2）图书借阅情况视图实现代码：use tsgl_133gocreate view book_lended_view as select t_book.图书编号,isbn条码,书名,借阅状态=被借阅,借阅日期,应还日期 from t_book,t_lend where t_book.图书编号=t_lend.图书编号 and 借阅状态 = 1（3）逾期未还图书信息视图使用getdate()函数获取系统时间；使用借阅表中“应还日期”字段信息与系统当前日期对比判断读者借阅书籍是否超期未还；使用 datediff()函数计算读者借阅书籍的超期天数并求出相应的

14、罚款金额。（注：罚款金额以超出一天罚款0.1元计算）逾期未还图书信息视图具体实现代码如下：use tsgl_133gocreate view not_return_viewas select t_lend.学号,姓名,系部,专业班级,书名,借阅日期,应还日期,罚款金额 = convert(float,datediff(day,应还日期,cast(left(getdate(),10) as date)/10 from t_lend,t_book,t_reader where t_lend.学号 = t_reader.学号 and t_lend.图书编号 = t_book.图书编号 and 归还日

15、期 is null and cast(left(getdate(),10) as date) 应还日期 上述代码运行结果如下图“图1 超期未还读者”。图1 超期未还读者（4）读者查阅图书信息视图实现代码如下：use tsgl_133gocreate view bookinfo_reader_view asselect distinct t_book.isbn条码,书名,图书总量,可借阅数量,馆藏位置from t_book, (select isbn条码,图书总量 = count(isbn条码)from t_bookgroup by isbn条码) as book1, (select isbn条

16、码,可借阅数量 = count(isbn条码)from t_bookwhere 借阅状态 = 0group by isbn条码) as book2where t_book.isbn条码 = book1.isbn条码 and t_book.isbn条码 = book2.isbn条码上述代码运行结果：图2 读者查阅图书信息视图（5）读者表视图实现代码：use tsgl_133gocreate view reader_viewas select *from t_reader管理员表视图、图书表视图和借阅表视图与上述代码类似，此处不重复列出。4索引的建立（一）设计思想为了提高搜索效率，应该为表建立相关

17、索引。（1） 对读者表建立索引在建立读者表时，已经对读者表的“学号”字段建立了主键索引，且其为聚集索引，为了提高对读者表的检索效率，还需要为经常查询的字段增加非聚集索引，例如为“姓名”字段建立非聚集索引。（2）对图书表建立索引在建立图书表时，已经对图书表的“图书编号”字段建立了主键索引，且其为聚集，为了提高对图书表的检索效率，还需要为该表添加“isbn条码”字段和“书名”字段建立非聚集索引。（二）具体实现（1）为读者表建立“姓名”字段非聚集索引图3 新建读者表“姓名”字段索引5数据完整性设计（一）设计思想图书管理系统包括：图书表、读者表、借阅表和管理员表。下面分别就实体完整性、域完整性、参照完

18、整性分析设计数据库完整性。（1）实体完整性图书表根据“图书编号”字段唯一表示一条记录，所以定义“图书编号”为主键来实现图书表的实体完整性。读者表定义“学号”为主键实现实体完整性；借阅表定义“学号”和“图书编号”两个组合字段为主键实现实体完整性；管理员表定义“工号”实现实体完整性。（2）域完整性设定图书表 “isbn条码”、“书名”字段不为空，实现图书表域完整性；设定读者表“姓名”字段设定不为空，实现读者表域完整性；设置借阅表中“借阅日期”、“应还日期”不为空实现借阅表域完整性；设置管理员信息表中“姓名”、“管理员身份”、“登陆口令”、“登陆密码”字段设置不为空实现管理员表域完整性。（3）参照完

19、整性对读者表、借阅表和图书表建立表关系。其中读者表与借阅表通过“学号”关联，借阅表“学号”字段参照读者表“学号”字段，是读者表的外键；图书表与借阅表通过“图书编号”关联，借阅表的“图书编号”字段参照图书表“图书编号”字段，是图书表的外键。（二）具体实现图4 管理员表完整性设计图5 借阅表完整性设计图6 图书表完整性设计图7 读者表完整性图8 数据表关系图6存储过程和触发器设计（一）设计思想（1）存储过程设计读者需要查询自己的借阅情况，需要建立根据学号查询该读者借阅情况；读者需要检索图书信息，需要建立根据图书名查询图书信息。（2）触发器设计读者借书时，触发借阅表插入记录的触发器，执行操作包括：填

20、补借阅表“应还时间”字段信息，即将“应还时间”设置为“借阅时间”开始计算的后2个月的时间；设置读者表“借阅数量字段”增加1；设置图书表“借阅状态”字段为“true”。读者还书时，设置读者表 “借阅数量”减1；设置该图书“借阅状态”为“false”；判断读者“还书时间”与“应还时间”关系，如果“还书时间”超出了“应还时间”，计算罚款金额，更新到该条借阅记录的“罚款金额”字段，并将罚款金额累加到读者表该读者的“欠罚款”字段。（二）具体实现（1）存储过程实现根据学号查询该读者借阅情况的存储过程实现代码：use tsgl_133gocreate procedure stulendinfo_byxueh

21、ao xuehao char(12)as select t_reader.学号,姓名,(case when 性别 = 1 then 男 else 女 end) as 性别,系部,专业班级,图书编号,借阅日期,应还日期,归还日期,罚款金额,缴纳状态from t_lend inner join t_reader on t_lend.学号 = t_reader.学号where t_reader.学号=xuehaogo根据图书名查询图书信息的实现代码：use tsgl_133gocreate procedure bookinfo_byboonname bookname nchar(30)as sele

22、ct distinct t_book.isbn条码,书名,图书总量,可借阅数量,馆藏位置from t_book, (select isbn条码,图书总量 = count(isbn条码)from t_bookgroup by isbn条码) as book1, (select isbn条码,可借阅数量 = count(isbn条码)from t_bookwhere 借阅状态 = 0group by isbn条码) as book2where t_book.isbn条码 = book1.isbn条码 and t_book.isbn条码 = book2.isbn条码 and 书名 like book

23、namego（2）触发器实现借阅表插入记录的触发器实现代码：use tsgl_133gocreate trigger lend_inserton t_lend after insertasbegin declare booknum int,readernum nchar(12),lendcount int,lenddate dateselect booknum=图书编号,readernum=学号,lenddate=借阅日期 from insertedselect lendcount=借阅数量 from t_reader where 学号=readernumupdate t_book set 借

24、阅状态=1 where 图书编号=booknumupdate t_lend set 应还日期=dateadd(m,2,借阅日期)update t_reader set 借阅数量=lendcount+1 where 学号=readernumprint 图书借阅状态已修改！print 读者借阅数量已修改！end借阅表“归还日期”字段的触发器实现代码：use tsgl_133gocreate trigger lend_updateon t_lend after update asbegin if(update(归还日期)begindeclare booknum int,readernum nchar

25、(12),lendcount int,returndate date,money floatselect booknum=图书编号,readernum=学号,returndate=应还日期 from deletedselect lendcount=借阅数量,money=欠罚款 from t_reader where 学号=readernumupdate t_book set 借阅状态=0 where 图书编号=booknumupdate t_reader set 借阅数量=lendcount-1 where 学号=readernumprint 图书借阅状态已修改！print 读者借阅数量已修改！endif(cast(left(getdate(),10) as date) returndate)beginupdate t_lend set 罚款金额=convert(float,datediff(day,returndate,cast(left(getdate(),10) as date)/10where 图书编号=booknumupdate t_reader set 欠罚款=欠罚款+convert(float,datediff(day,returndate,