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

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

2、验成绩；要求在该课程期末考试前将实验报告交给任课教师。综合性实验中，所涉及的程序，文档等在交实验报告前，拷贝给任课教师。任课教师统一刻录成光盘，与该课程的期末考试成绩一同上交到系里存档。 5、 未尽事宜，请参考该课程的实验大纲和教学大纲。 华北科技学院计算机系综合性实验报告 第 2 页 网络 数据库 课程综合性实验报告 开课实验室： 软件 一室 室 1 年月 日 日 实验题目 图书借阅系统数据库设计 一、实验目的 通过该实验把数据库的理论知识（数据库和数据表的设计理论、数据完整性的实现、存储过程、触发器、数据库安全等）应用到具体的综合实例中，达到数据库知识整合的目的。 二、设备与环境 硬件：多

3、媒体计算机 软件：windowsxp 以上的操作系统、visual basic 或其它可视化语言及 sql server 2021 版本 三、实验内容及要求 1.数据库设计 要求数据库设计要合理，对数据库设计作必要的说明并抓图。 数据库名必须与自己真实姓名有关，所有同学不能同名。 图不要太大，看清即可。 2数据表设计 要求数据表设计要合理，要符合数据库设计的理论范式，对数据表设计作必要的说明并抓图。 数据表名必须与自己真实姓名有关，所有同学不能同名。 3视图设计 要求根据系统需求作必要的视图设计，如在一次查询中涉及到多个表，应该创建视图。不可以只取一个表的几个字段就算创建视图。 4索引设计 要

4、求根据系统需求作必要的索引设计，本系统需要的聚集索引、非聚集索引、唯一索引、全文索引等。 5数据完整性设计 根据系统需求作必要的数据完整性设计，本系统需要的实体完整性体现、域完整性体现、参照完整性体现等。 6存储过程和触发器设计 根据系统需求作必要的存储过程和触发器设计，本系统需要的存储过程和触发器设计。必要的存储过程和触发器设计都要写全说明，图可以是一个表的完整存储 华北科技学院计算机系综合性实验报告 第 3 页 过程或触发器。 7备份与恢复设计 根据系统需求作必要的备份与恢复设计，如需要对那些内容备份，备份策略、由谁来做备份、什么时间做备份等。 8数据库安全设计 根据系统需求作必要的数据库

5、安全设计，如本系统分几级用户、分别是什么角色成员具有什么操作权限等。 四、实验结果及分析 1.数据库设计 （一）设计思想 图书管理系统数据库的主要任务是对读者信息、管理员信息、图书资料信息、借阅归还图书信息、罚款信息的基本信息的操作及处理。此系统功能分为面向读者和面向管理员两部分，其中读者可以进行借阅、续借、归还和查询书籍等操作，管理员可以完成书籍和读者信息的增加，删除和修改，对数据表维护。 根据以上功能分析，图书管理系统应该包含：读者信息表、管理员信息表、图书信息表、借阅信息表。 针对图书管理系统目前的用户量（假设为 15000 人）和图书量（假设为 10 万册）考虑，系统主数据文件初始大小

6、设置为：110m（15000*512byte（读者表一条记录大小）+ 100000*1024byte（图书表一条记录大小）+ 其他表大小），自动增长设置为 20m；日志文件设置为：5m，自动增长设置为 5m。 考虑图书管理系统最经常的操作是查找，所以为了提高查找效率，应为图书表、读者表建立索引。 （二）具体实现 通过 sql 语句来创建图书管理数据库，创建代码如下： create database tsgl_133 on ( name = "tsgl", filename = "j:tsgl.mdf", size = 110 mb, maxsize =

7、unlimited, filegrowth = 20 mb ) log on ( name = "tsgl_log", filename = "j:tslg_log.ldf", size = 5 mb, maxsize = unlimited, filegrowth = 5 mb ); 华北科技学院计算机系综合性实验报告 第 4 页 2数据表设计 （一）设计思想 根据以上对图书管理系统的功能分析，需要设计读者信息表、管理员信息表、图书信息表和借阅信息表。 （1）读者表(学号、姓名、性别、系部、专业班级、借阅数量、欠罚款、联系电话) 读者表主要记录读者信息

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

9、置等信息。 （4）借阅表（图书编号、学号、借阅日期、应还日期、归还日期、罚款金额、缴纳状态） 读者通过借阅表查询借书情况，例如：借阅日期、应还日期、归还日期等信息。 （二）具体实现 通过 sql 语句来创建图书管理数据表，创建代码如下： use tsgl_133 go create table t_reader ( 学号 char(12) not null primary key, 姓名 nchar(4) not null, 性别 bit null default 1, 系部 nchar(10) null, 专业班级 nchar(8) null, 借阅数量 int null default 0

10、, 欠罚款 float null, 联系电话 char(11) null ) go create 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, ) go 华北科技学院计算机系综合性实验报告 第 5 页 create table t_book ( 图书编号 int id

11、entity 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, ) go create table t_lend ( 图书编号 int not null, 学号 char(12) not null, 借阅日期 date not null, 应还日期 date not null,

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

13、金额。其中，罚款金额通过计算得出。 （4）读者需要查看图书信息，所以建立图书信息视图，其字段包括：isbn 条码、书名、图书总量、可外借数量、馆藏位置。其中图书总量和可外借数量通过计算得出。 （5）为数据库所有表创建单独的视图，如图书表视图、管理员表视图、借阅表视图和读者表视图。 （二）具体实现 （1）读者借阅情况视图实现代码： use tsgl_133 go create view reader_lended_view as 华北科技学院计算机系综合性实验报告 第 6 页 select t_reader.学号,姓名,性别=( case when 性别=1 then "男"

14、 else "女" end),系部,专业,班级, 图书编号,借阅日期,应还日期,归还日期,罚款金额,缴纳状态 from t_lend,t_reader where t_lend.学号 = t_reader.学号 （2）图书借阅情况视图实现代码： use tsgl_133 go create view book_lended_view as select t_book.图书编号,isbn 条码,书名,借阅状态="被借阅", 借阅日期,应还日期 from t_book,t_lend where t_book.图书编号=t_lend.图书编号 and 借阅状态

15、 = 1 （3）逾期未还图书信息视图使用 getdate()函数获取系统时间；使用借阅表中"应还日期'字段信息与系统当前日期对比判断读者借阅书籍是否超期未还；使用 datediff()函数计算读者借阅书籍的超期天数并求出相应的罚款金额。（注：罚款金额以超出一天罚款 0.1 元计算） 逾期未还图书信息视图具体实现代码如下： use tsgl_133 go create view not_return_view as select t_lend.学号,姓名,系部,专业班级,书名,借阅日期,应还日期,罚款金额 = convert(float,datediff(day,应还日期,ca

16、st(left(getdate(),10) as date)/10 from t_lend,t_book,t_reader where t_lend.学号 = t_reader.学号 and t_lend.图书编号 = t_book.图书编号 and 归还日期 is null and cast(left(getdate(),10) as date) gt; 应还日期 上述代码运行结果如下图"图 1 超期未还读者'。 图 图 1 超期未还读者 （4）读者查阅图书信息视图实现代码如下： use tsgl_133 go create view bookinfo_reader_vie

17、w as select distinct t_book.isbn 条码,书名,图书总量,可借阅数量,馆藏位置 from t_book, (select isbn 条码,图书总量 = count(isbn 条码) from t_book 华北科技学院计算机系综合性实验报告 第 7 页 group by isbn 条码) as book1, (select isbn 条码,可借阅数量 = count(isbn 条码) from t_book where 借阅状态 = 0 group by isbn 条码) as book2 where t_book.isbn 条码 = book1.isbn 条码

18、and t_book.isbn 条码 = book2.isbn 条码 上述代码运行结果： 图 图 2 读者查阅图书信息视图 （5）读者表视图实现代码： use tsgl_133 go create view reader_view as select * from t_reader 管理员表视图、图书表视图和借阅表视图与上述代码类似，此处不重复列出。 4索引的建立 （一）设计思想 为了提高搜索效率，应该为表建立相关索引。 （1） 对读者表建立索引 在建立读者表时，已经对读者表的"学号'字段建立了主键索引，且其为聚集索引，为了提高对读者表的检索效率，还需要为经常查询的字段增加非

19、聚集索引，例如为"姓名' 华北科技学院计算机系综合性实验报告 第 8 页 字段建立非聚集索引。 （2）对图书表建立索引 在建立图书表时，已经对图书表的"图书编号'字段建立了主键索引，且其为聚集，为了提高对图书表的检索效率，还需要为该表添加"isbn 条码'字段和"书名'字段建立非聚集索引。 （二）具体实现 （1）为读者表建立"姓名'字段非聚集索引 图 图 3 新建 读者表"姓名'字段索引 5数据完整性设计 （一）设计思想 图书管理系统包括：图书表、读者表、借阅表和管理员表。下面分别就实体

20、完整性、域完整性、参照完整性分析设计数据库完整性。 （1）实体完整性 图书表根据"图书编号'字段唯一表示一条记录，所以定义"图书编号'为主键来实现图书表的实体完整性。 读者表定义"学号'为主键实现实体完整性；借阅表定义"学号'和"图书编号'两 华北科技学院计算机系综合性实验报告 第 9 页 个组合字段为主键实现实体完整性；管理员表定义"工号'实现实体完整性。 （2）域完整性 设定图书表 "isbn 条码'、"书名'字段不为空，实现图书表域完整性；设定读

21、者表"姓名'字段设定不为空，实现读者表域完整性；设置借阅表中"借阅日期'、"应还日期'不为空实现借阅表域完整性；设置管理员信息表中"姓名'、"管理员身份'、"登陆口令'、"登陆密码'字段设置不为空实现管理员表域完整性。 （3）参照完整性 对读者表、借阅表和图书表建立表关系。其中读者表与借阅表通过"学号'关联，借阅表"学号'字段参照读者表"学号'字段，是读者表的外键；图书表与借阅表通过"图书编号'关联

22、，借阅表的"图书编号'字段参照图书表"图书编号'字段，是图书表的外键。 （二）具体实现 图 图 4 管理员表完整性设计 图 图 5 借阅表完整性设计 华北科技学院计算机系综合性实验报告 第 10 页 图 图 6 图书表完整性设计 图 图 7 读者表完整性 图 图 8 数据表关系图 华北科技学院计算机系综合性实验报告 第 11 页 6存储过程和触发器设计 （一）设计思想 （1）存储过程设计 读者需要查询自己的借阅情况，需要建立根据学号查询该读者借阅情况；读者需要检索图书信息，需要建立根据图书名查询图书信息。 （2）触发器设计 读者借书时，触发借阅表插入记录的触

23、发器，执行操作包括：填补借阅表"应还时间'字段信息，即将"应还时间'设置为"借阅时间'开始计算的后 2 个月的时间；设置读者表"借阅数量字段'增加 1；设置图书表"借阅状态'字段为"true'。 读者还书时，设置读者表 "借阅数量'减 1；设置该图书"借阅状态'为"false'；判断读者"还书时间'与"应还时间'关系，如果"还书时间'超出了"应还时间'，计算罚款金

24、额，更新到该条借阅记录的"罚款金额'字段，并将罚款金额累加到读者表该读者的"欠罚款'字段。 （二）具体实现 （1）存储过程实现 根据学号查询该读者借阅情况的存储过程实现代码： use tsgl_133 go create procedure stulendinfo_byxuehao xuehao char(12) as select t_reader.学号,姓名,(case when 性别 = 1 then "男" else "女" end) as 性别,系部, 专业班级,图书编号,借阅日期,应还日期,归还日期,罚款金

25、额,缴纳状态 from t_lend inner join t_reader on t_lend.学号 = t_reader.学号 where t_reader.学号=xuehao go 根据图书名查询图书信息的实现代码： use tsgl_133 go create procedure bookinfo_byboonname bookname nchar(30) as select distinct t_book.isbn 条码,书名,图书总量,可借阅数量,馆藏位置 from t_book, (select isbn 条码,图书总量 = count(isbn 条码) from t_book

26、group by isbn 条码) as book1, (select isbn 条码,可借阅数量 = count(isbn 条码) from t_book where 借阅状态 = 0 group by isbn 条码) as book2 where t_book.isbn 条码 = book1.isbn 条码 and t_book.isbn 条码 = book2.isbn 条码 and 书名 like bookname go 华北科技学院计算机系综合性实验报告 第 12 页 （2）触发器实现 借阅表插入记录的触发器实现代码： use tsgl_133 go create trigger l

27、end_insert on t_lend after insert as begin declare booknum int,readernum nchar(12),lendcount int,lenddate date select booknum=图书编号,readernum=学号,lenddate=借阅日期 from inserted select lendcount=借阅数量 from t_reader where 学号=readernum update t_book set 借阅状态=1 where 图书编号=booknum update t_lend set 应还日期=datead

28、d(m,2,借阅日期) update t_reader set 借阅数量=lendcount+1 where 学号=readernum print "图书借阅状态已修改！" print "读者借阅数量已修改！" end 借阅表"归还日期'字段的触发器实现代码： use tsgl_133 go create trigger lend_update on t_lend after update as begin if(update(归还日期) begin declare booknum int,readernum nchar(12), le

29、ndcount int,returndate date,money float select booknum=图书编号,readernum=学号,returndate=应还日期 from deleted select lendcount=借阅数量,money=欠罚款 from t_reader where 学号=readernum update t_book set 借阅状态=0 where 图书编号=booknum update t_reader set 借阅数量=lendcount-1 where 学号=readernum print "图书借阅状态已修改！" print "读者借阅数量已修改！" end if(cast(left(getdate(),10) as date) gt; returndate) begin update t_lend set 罚款金额=conve