图书借阅系统数据库设计实验报告Word文档格式.docx

1、图书借阅系统数据库设计一、实验目的通过该实验把数据库的理论知识（数据库和数据表的设计理论、数据完整性的实现、存储过程、触发器、数据库备份、数据库安全等）应用到具体的综合实例中，达到数据库知识整合的目的。二、设备与环境硬件：多媒体计算机软件：WindowsXP以上的操作系统、SQL Server 2008版本三、实验容及要求1.数据库设计要求数据库设计要合理，对数据库设计作必要的说明并抓图。2数据表设计要求数据表设计要合理，要符合数据库设计的理论式，对数据表设计作必要的说明并抓图。3视图设计要求根据系统需求作必要的视图设计，如在一次查询中涉及到多个表，应该创建视图。4索引设计要求根据系统需求作必

2、要的索引设计，本系统需要的聚集索引、非聚集索引、唯一索引、全文索引等。5数据完整性设计根据系统需求作必要的数据完整性设计，本系统需要的实体完整性体现、域完整性体现、参照完整性体现等。6存储过程和触发器设计根据系统需求作必要的存储过程和触发器设计，本系统需要的存储过程和触发器设计。必要的存储过程和触发器设计都要写全说明，图可以是一个表的完整存储过程或触发器。7备份与恢复设计根据系统需求作必要的备份策略。8数据库安全设计根据系统需求作必要的数据库安全设计，如本系统分几级用户、分别是什么角色成员具有什么操作权限等。四、实验结果及分析（1）设计思想：图书借阅系统主要涉及业务为读者借阅图书、读者归还图书

3、，涉及主要实体包括：读者、图书。因此针对图书、读者、借阅、历史借阅设计文件组。在实际实现中，可以为每一个文件组指定一个磁盘驱动器，然后将特定的表、索引等与该文件组相关联，对这些表的存储、查询、修改操作都在该文件组中，使用四个文件组可以提高文件组中表中数据的查询性能。（2）具体实现：设计包含一个主文件组（PRIMARY），四个用户定义文件组（MyDB_FG1、MyDB_FG2、MyDB_FG3、MyDB_FG4）。其中主文件组中存放主数据文件 MBook203.mdf，用户定义文件组1、2、3、4分别存放四个辅助数据文件（MyDB_FG1_Data1.ndf、MyDB_FG2_Data1.ndf

4、、MyDB_FG3_Data1.ndf、MyDB_FG4_Data1.ndf）。在实际系统中，读者、图书、借阅、历史借阅，各个表中数据较大，将其放在不同的辅助数据文件中，每一个辅助数据文件分别属于一个单独的文件组。而每一文件组又分别指定了不同的磁盘驱动器，因此可以提高对这些表中数据的查询性能。 用户定义文件组1、2、3、4中的辅助数据文件分别存放读者表、图书表、借阅表和历史借阅表。将读者表（Reader）放在文件组1、图书表（Book、BookID_ISBN）放在文件组2、借阅表（Lend）放在文件组3、历史借阅表（HistoryLend）放在文件组4。2.1读者表（Reader）： 属于文件

5、组1主键为读者号（ReaderID），因在实际系统中读者号为固定长度，固设置为char（12）；性别（Sex）数据类型设置为bit，约束为0或1（0：女，1：男）；照片（Photo）数据类型设置为 varbinary（MAX），可变长度二进制数据类型，安全性较高，只适合图像容量不是很大的时候；地址（Addr）数据类型设置为 xml 类型，灵活易于扩展，适合于传输和存储数据。 2.2图书表（Book）、图书ID与ISBN对应表（BookID_ISBN）： 属于文件组2图书表主键设置为 ISBN，因为图书有不同复本，所以另外设置了图书ID，在图书ID与ISBN对应表（BookID_ISBN）中存放

6、其对应关系；图书表中字段库存量（Invnum）和复本量（Copnum）设置约束非空，默认为0；图书封面照片（Photo）数据类型设置为可变才二进制数据类型，方便存储。图书容概要（Summary）数据类型设置为 xml，适合存储和传输。 BookID_ISBN 表中BookID作为主键。2.3借阅表（Lend）：属于文件组3借阅表中读者号、ISBN、图书ID共同作为主键（说明：不允许同一读者号，借阅同一ISBN号图书）；读者号（ReaderID）作为外键，参照读者表（Reader）中（ReaderID）；图书ISBN号（ISBN）作为外键，参照图书表（Book）中（ISBN）；图书ID号（Boo

7、kID）作为外键，参照图书ISBN号与ID对应表（BookID_ISBN）中（BookID）。借书日期（bbt）数据类型为 date 类型。2.4历史借阅表（HistoryLend）：属于文件组4历史借阅表中读者号、ISBN、图书ID、借书日期、还书日期共同作为主键（说明：不允许同一读者号，借阅同一ISBN号图书），当同一读者两次借同一本书（先还图书后再借），可以依靠借书日期、还书日期来唯一确定读者两次的借阅、还书记录；在历史借阅表中除借书日期（bbt）、还书日期（rbbt）外，其他字段都作为外键，分别参照与其他基本表；在读者还书时，采用存储过程，将读者的借书记录插入到历史借阅表，并将还书日期

8、设置为当前系统日期。管理员可以查看当前读者的借阅情况，可以根据历史借阅表中的数据统计图书的借阅次数；读者可以查看自己的借阅记录和其他读者的借阅记录（部分字段，如读者号、院部、专业、所借图书ISBN、ID、借书日期、归还日期等）。3.1创建视图将读者表、借阅表、图书表连接，根据读者编号、图书ISBN号，可将三个表连接，从而实现查看读者当前借阅记录。在连接时，由于只用到表中部分字段，所以先在基表中筛选部分字段作为新表，进行连接，可以提高连接效率。 以下是视图的代码实现，以及从视图中查询得到的当前读者借阅情况的结果：3.2根据历史借阅表中的数据统计图书的借阅次数： 以下是从视图中查询得到的结果，管理

9、员可以根据历史借阅表图书的外借次数决定增加哪些图书的复本量，以满足读者需求：4索引的建立在图书借阅系统中，为了从数据库的大量数据中迅速找到需要的容，采用类似于书目录这样的索引技术，不必顺序查找，就能迅速查到所需要的容。索引是根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。聚集索引与非聚集索引的不同之处在于：非聚集索引B树的叶子节点不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。读者表按读者号（ReaderID）建立主键索引（PRIMARY KEY），组织方式为聚集索引；图书表按ISBN号建立主键索引，组织方式为聚集索引；借阅表按读者

10、号（ReaderID）、ISBN号、图书ID建立主键索引，组织方式为聚集索引；图书ID与ISBN对应表（BookID_ISBN）按图书ID建立主键索引，组织方式为聚集索引；历史借阅表按读者号（ReaderID）、ISBN号、图书ID、借书日期（bbt）、还书日期（rbbt）建立主键索引、组织方式为聚集索引；为图书表的书名、作者、库存量（Bname,Author,Invnum）创建非聚集索引；为读者表的读者号、借书量（ReaderID,Name,Num）创建非聚集索引；为图书ID与ISBN对应表（BookID_ISBN）中的的图书ISBN 字段创建非聚集索引。实体完整性：实体完整性又称为行的完整

11、性，要求表中有一个主键，其值不能为空且能唯一地标识对应的记录。通过索引、UNIQUE约束、PRIMARY KEY约束或IDENTITY属性可实现数据的实体完整性。域完整性：域完整性又称为列完整性，指给定列输入的有效性。实现域完整性的方法有：限制类型（通过数据类型）、格式（通过CHECK约束和规则）或可能的取值围（通过CHECK约束、DEFAULT定义、NOT NULL定义和规则）等。参照完整性：参照完整性又称为引用完整性。参照完整性保证主表中的数据与从表（被参照表）中的数据的一致性。 实体完整性：图书表中以ISBN号为主键，图书存在不同的复本，在图书ID与ISBN对应表中，同一个ISBN对应不

12、同的图书ID，以图书ID作为主键；读者表中以读者号作为主键可以唯一确定一个读者；借阅表中以读者号、ISBN号、图书ID作为主键（不允许读者同一ISBN号图书同时借两本，但是可以还了该书后再接，保证了借阅表中借阅记录的实体完整性）；历史借阅表中，以读者编号、ISBN、图书ID、借书日期、还书日期作为主键来保证还书记录的实体完整性。 域完整性：读者表中读者号设置为char（12）固定长度，性别约束为CHECK（Sex = 0 OR Sex = 1），0：男，出生日期设置为 date 类型，借书数量，设置默认为 0；图书表中ISBN号设置为varchar（16），库存量和复本量设置为 int 类型，

13、默认为0；借阅表中借书日期字段设置为date类型，且不允许为空；历史借阅表中借书日期、还书日期数据类型也都设置为date 类型。 参照完整性：图书ID与ISBN对应表中，ISBN号参照图书表中ISBN号；借阅表中读者号参照读者表中读者号，ISBN号参照图书表中ISBN号，图书ID参照图书ID与ISBN对应表中的图书ID；历史借阅表中读者号、ISBN号、图书ID也都分别参照于基表中对应字段；当读者借书时，采用借书存储过程，将当前日期作为借书日期字段的值插入到借阅表中；当读者还书时，采用还书存储过程，将读者的借书记录（在借阅表中）插入到历史借阅表中（表明读者已经还书），将当前日期作为还书日期字段的

14、值插入到历史借阅表中。 采用两个借书、还书的存储过程可以很好的保证了借阅表、历史借阅表和读者表、图书表之间的参照完整性。在图书借阅系统中，主要涉及业务为读者借书、读者还书、书籍检索。所以分别针对三个业务创建了存储过程。此外还创建了在当前借阅表中查询超期未归还图书的借书记录、在历史借阅表中超期未归还图书的还书记录。存储过程在服务器端运行，执行速度块；存储过程在执行一次后就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能；使用存储过程可以完成所有的数据库操作，并通过编程方式控制对数据库信息访问的权限，确保数据库的安全；可以自动完成需要预先执行

15、的任务。存储过程可以在SQL Server启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以完成一些需要预先执行的任务。6.1在数据库 MBook203 中创建加密的读者借书存储过程，当向 Lend 表插入一条记录时： 检查该记录的借书证号 ReaderID 在 Reader 表中是否存在； 检查该记录的图书 ISBN 号是否在 Book 表中是否存在； 检查该记录的图书库存量 Invnum 是否 等于 0； 检查该记录是否借阅过 该图书； 检查该记录的 图书 BookID 是否在 借阅表 Lend 中已经存在； 若有一项为否，则不允许插入，并回滚事务，否则，插入记录

16、并将读者表中该读者借阅数量 加1 ，图书表中库存量 减1 ，并返回借书成功信息。满足上述条件，则开始一个事务，将借书记录插入到借阅表中：读者表中借书数量（Num）加1，在上面语句中已经执行，接着执行：图书表中的图书库存量（Invnum） 减1；如果所有语句都不出错，则提交事务并返回借书成功信息，否则，回滚事务，并返回错误信息。6.2在数据库 MBook203 中创建加密的读者还书存储过程，当删除 Lend 表中一条记录时： 检查借阅表中是否存在该借阅记录，如果不存在则回滚事务； 将该记录插入到历史借阅表； 将该记录的还书日期 rbbt 获取当前系统时间，并插入到对应记录的还书日期字段； 将该记

17、录的读者借阅数量 减1； 将该记录的图书库存量 加1； 返回还书成功信息；若有一项为执行不成功，则不允许删除，并回滚事务。 满足上述条件，则开始一个事务，将还书记录插入到历史借阅表中：接着执行：删除借阅表中的借书记录；更新读者表中的借书数量（Num） 减 1；图书表中的图书库存量（Invnum） 加 1；如果所有语句都不出错，则提交事务并返回还书成功信息，否则，回滚事务，并返回错误信息。6.3超期未归还图书的借书记录（当前借阅表中）、超期未归还图书的还书记录（历史借阅表中），读者的借书证号、院部、专业、图书ISBN号、书名以及借书日期、当前日期（或还书日期）。列出当前借阅表中超期未归还图书的借

18、书记录（Book_Current_Overdue） 读者的借阅期为90天。计算应还书日期用DATEADD或DATEDIFF函数。getdate（）函数返回当前数据库系统日期，返回值的类型为 datetime。列出历史借阅表中有超期未归还图书的还书记录（Book_History_Overdue）。在图书借阅系统中，分为四个用户定义文件组和一个主文件组，其中操作最为频繁的是文件组4（存放历史借阅表）、文件组3（存放借阅表）、其次为，文件组2（存放图书表）、文件组1（存放读者表）。但是主文件组又是整个数据库可以启动、运行的基础，所以不可忽视。备份操作由授予固定服务器角色 db backupopera

19、tor（允许进行数据库备份的用户）来操作，完全备份时间，在每周二上午，图书馆务整理时进行，文件组的差异备份与事务日志备份在每天闭馆后进行。综合以上考虑，在本地计算机上，指定一个单独的磁盘，存放每周二进行的数据库完全备份（包括主文件组、用户定义文件组、事务日志），在本地局域网另设一台主机专门用来存放完全备份，该主机权限只能由系统管理员登录。由属于固定服务器角色db backupoperator用户进行完全备份操作。文件组差异备份与事务日志备份在每天闭馆后进行。主要针对四个用户自定义文件组、主文件组、事务日志进行差异备份，指定备份在本地计算机一个单独的磁盘。在图书借阅系统中，在服务器级别，可以分为

20、系统管理员（sysadmin）、安全管理员（securityadmin）、服务器管理员（serveradmin）、设置管理员（setupadmin）、进程管理员（processadmin）、管理磁盘文件（diskadmin）、数据库创建者（dbcreator）、指定格式复制数据文件（bulkadmin）。在数据库级别，可以分为数据库所有者（db_owner）、数据库访问权限管理者（db_accessadmin）、数据库安全管理员（db_securityadmin）、数据库DDL管理员（db_ddladmin）、数据库备份操作员（db_backupoperator）、数据库数据读取者（db_da

21、tareader）、数据库数据写入者（db_datawriter）。此外还包括自定义数据库角色（学生、教师、图书馆普通员工、图书馆借阅处工作人员、图书馆采编部工作人员）、应用程序角色（图书馆）。自定义数据库角色都具有对图书表的查询权限、具有对图书ID与ISBN对应表的查询权限。学生对与自己的借书记录、还书记录有查询权限、读者信息中的地址（Addr）字段有更新权利、其他字段只允许查询。学生借阅数量设置为（0=Num=10），借书期限为 90天。教师对与自己的借书记录、还书记录有查询权限、读者信息中的地址（Addr）字段有更新权利、其他字段只允许查询。教师借阅数量设置为（0=15），借书期限为 1

22、20天。图书馆普通员工对与自己的借书记录、还书记录有查询权限、读者信息中的地址（Addr）字段有更新权利、其他字段只允许查询。普通员工借阅数量设置为（0=图书馆借阅处工作人员同时属于普通员工、借阅处工作人员两个角色，新增权限当读者借书、还书时，借阅处工作人员可以提交借书、还书记录。对读者表的借阅量字段、图书表的库存量字段，通过应用程序角色可以进行更新。图书馆采编部工作人员同时属于普通员工、采编部工作人员两个角色，新增权限对历史借阅表所有行具有查询权限，可以根据统计数据，决定新增上架图书、下架某些图书，具有对图书表中数据的更新、删除。 测试：以SQL Server 用户：8，登录服务器：ADMI

23、NISTRATOR 默认数据库：MBook203，数据库用户：8，属于角色：学生 具有权限：对图书表（Book）的查询权限。执行图书检索存储过程，执行结果如下：五、体会 在图书借阅系统数据设计的库综合实验中，由于需求分析不够深入，对图书借阅系统的业务流程，以及所涉及到的角色没有做到深入的了解。比如读者，图书馆工作人员、管理员，他们应该具有详细的角色划分，由此设计数据表、以及对应数据表中的各个字段，比如借阅数量，可以根据角色设置不同的约束条件。对于不同的角色也可以分配不同的权限，也方便了管理员对用户权限的管理。在设计表结构时存在一些不足，比如，在历史借阅表中，为了保证实体完整性，而将读者号、图书

24、ISBN、图书ID、借书日期、还书日期都设置为了主键，可以考虑加入一个字段作为标志列，用标识列作为主键，可以保证还书记录在历史借阅表中的唯一性，标志列可以唯一的标志一行。另外一个是图书ID与ISBN的对应表，在该表中同一个ISBN对应了不同的ID，表中用图书ID作为主键不太合理。在图书表中，图书应该是分类存放，所以应该考虑添加分类标号，这样在图书检索的时候可以提高检索效率，同时图书按类存放，也使得图书表中数据便于维护。对于服务器固定角色、数据库固定角色、用户自定义角色、应用程序角色的理解也不够，对于他们应有权限以及权限的分配不清楚，在为图书借阅数据库创建了学生角色后，指定学生角色所有者为dbo

25、，为学生角色授权可以查询图书表，将创建的SQL Server登录用户对应的数据库用户添加到学生角色，使用该用户登录后，对其他表中数据仍然具有查询权限，还可以操作借书、还书存储过程。对于读者登录验证，可以单独创建一个表，包含读者号、读者登录密码，对于登录密码在数据库中可以采用加密存储，保证读者个人信息的安全性。管理员的权限也要有明确的分配，对管理员的操作也要具有审计功能。 总体而言，对于数据设计，应该遵循数据库设计的理论指导，需求分析、概念结构设计、逻辑结构设计、物理设计，结合实际系统在数据库设计的每一步都应该认真分析、设计，是后期投入运行的重中之重。六、参考文献 SQL Server实用教程（

26、第3版）阿奇 主编 七、SQL 代码/*1.使用命令方式创建图书管理数据库 MBook203 */CREATE DATABASE MBook203 ON PRIMARY （ NAME = MBook203_DATA, FILENAME = D:dbMBook203.mdf SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ）, FILEGROUP MyDB_FG1MyDB_FG1_Data1dbMyDB_FG1_Data1.ndf FILEGROWTH = 10% FILEGROUP MyDB_FG2MyDB_FG2_Data1dbMyDB_

27、FG2_Data1.ndf FILEGROUP MyDB_FG3MyDB_FG3_Data1dbMyDB_FG3_Data1.ndf FILEGROUP MyDB_FG4MyDB_FG4_Data1dbMyDB_FG4_Data1.ndf ） LOG ONMBook203_LOG filename = dbMBook203.ldf size = 10MB, maxsize = unlimited, filegrowth = 10% ）;/*2.创建数据表,读者表、（图书表、BookID_ISBN）、借阅表、历史借阅表 分别放在 文件组 MyDB_FG1、 MyDB_FG2、 MyDB_FG3、 MyDB_FG4 读者表（Reader）、图书表（Book）、图书ID与ISBN对应表（BookID_ISBN）、借阅表（Lend）和借阅历史表（HistoryLend）*/USE MBook203GOCREATE TABLE Reader（ ReaderID char（12） NOT NULL PRIMARY KEY, Name varchar（8） NOT NULL, Sex bit CHECK（Sex = 0 OR Sex =