大数据库图书借阅管理系统

大型数据库课程设计设计报告题目：图书借阅管理系统学号：学生姓名:指导教师：提交时间： 2014-11-22目录1.需求分析 (2)1.1需求调查 (2)1.2系统功能分析 (2)2.概念结构设计 (3)2.1概念设计 (3)2.2E-R图建立 (3)3.逻辑结构设计 (8)3.1E-R图向关系模式转换 (8)3.2建立关系模式 (8)3.3关系模式规范化处理 (8)3.4用户子模式建立 (8)3.5关系模式逻辑结构定义 (10)4.物理结构设计 (11)5.数据库实施与测试 (12)5.1数据库实施 (12)5.1.1数据库及数据库对象建立 (12)5.1.2数据存入数据库 (13)5.1.3函数设计 (13)5.2数据库测试 (14)6.数据库完整型设计 (16)6.1主键及唯一性索引 (16)6.2参照完整型 (16)6.3Check约束 (16)6.4触发器设计 (17)6.5规则 (17)7.数据库视图设计 (18)8.数据库存储过程设计 (19)9.总结 (27)10.参考文献 (27)某书店图书借阅管理系统第1章需求分析1.1 需求调查通过对各个书店的市场调查，该图书借阅管理系统的需求如下：1)系统管理：实现系统管理人员对系统的管理，包括添加删除用户，数据备份，数据还原，注销等功能。

2)读者信息管理：添加读者信息，根据借书证编号查看读者信息，根据读者ID删除读者，借阅证到期的读者更改借阅证办证时间。

3)书籍信息管理：添加一条书籍信息，删除一条书籍信息，查看一条图书信息，根据图书名查看图书信息，根据图书类别查看图书信息，根据作者查看图书信息，根据出版社产看图书信息，根据ISBN查看图书库存，根据书籍名查看库存。

4)借阅管理：借书管理，还书管理，续借管理，查看所有借阅信息，根据读者ID查看借阅信息，根据读者ID查看借书记录，根据读者ID查看借书记录，根据读者ID查看罚款信息。

图1-2 系统功能分析图第2章概念结构设计2.1 概念设计概念结构设计阶段的目标是通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型。

即通过对数据流程图的分析归纳和总结，建立E-R图。

概念结构的设计方法有两种：(1)集中式模式设计法：这种方法是根据需求由一个统一机构或人员设计一个综合的全局模式。

这种方法简单方便，适用于小型或不复杂的系统设计，由于该方法很难描述复杂的语义关联，而不适于大型的或复杂的系统设计。

(2)视图集成设计法：这种方法是将一个系统分解成若干个子系统，首先对每一个子系统进行模式设计，建立各个局部视图，然后将这些局部视图进行集成，最终形成整个系统的全局模式。

2.2 E-R图建立局部E-R图1.对‘读者’建立E-R图，其中‘读者ID’为主键图2-1 读者E-R图2.对‘书籍’建立E-R图，其中‘图书ID’为主键图2-2 书籍E-R图3.对‘读者类别’建立E-R图，其中‘读者类别编号’为主键图2-3 读者类别E-R图图2-4 借阅Ｅ－Ｒ图5、对‘还书’建立E-R图图２－５还书Ｅ－Ｒ图图２－６罚款Ｅ－Ｒ图7、对‘罚款类别’建立E-R图图２－７罚款类别Ｅ－Ｒ图全局E-R图第3章逻辑结构设计3.1 E-R图向关系模式转换数据库的逻辑结构设计就是把概念结构设计阶段设计好的基本 E 一R 图转换为与选用的DBMS产品所支持的数据模型相符合的逻辑结构。

逻辑结构是独立于任何一种数据模型的，在实际应用中，一般所用的数据库环境已经给定（如SQL Server或Oracel或MySql)。

由于目前使用的数据库基本上都是关系数据库，因此首先需要将E-R图转换为关系模型，然后根据具体DBMS的特点和限制转换为特定的DBMS支持下的数据模型，最后进行优化。

3.2 建立关系模式读者信息:读者ID，密码，读者姓名，读者性别，读者类别，读者单位，办证时间，联系方式；读者类别信息:读者类别编号，读者类别名，允许借阅图书最大数，持有图书最长期限，借阅证期限；书籍信息：图书ID，ISBN，书籍名称，书籍类别，书籍作者，出版社，定价，出版日期，登记日期，状态；借书信息：借书证编号，读者姓名，书籍编号，ISBN，书籍名，读者借书时间；还书信息：借书证编号，书籍编号，ISBN，图书名,读者姓名，还书时间；罚款信息：借书证编号，读者姓名，书籍编号，ISBN，书籍名，借阅日期，归还日期，应还日期，罚款类别编号，处理状态；罚款类别：罚款类别编号，类别名，罚款原因，罚款金额3.3 关系模式规范化处理根据F，分析每一个关系模式是否满足3NF，对不满足3NF的关系模式要进行规范化处理。

优化处理，消除不必要的数据冗余3.4 用户子模式建立根据需求分析，研究建立满足不同需求的用户子模式，子模式表示形式为：子模式名（属性列表）3.5 关系模式逻辑结构定义1.读者详细信息字段名数据类型长度约束读者ID Char 10 主键，Not null密码char 20 Not null姓名char 15 Not null性别char 2 男/女，not null 类别编号char 2 外键，not null单位char 30 null办证日期date Not null联系方式char 20 Null表3-12、读者类别详细信息字段名数据类型长度约束读者类别编号char 2 主键，'01'/'02'读者类别名char 15 '普通读者'/'高级读者' 可借阅最大数目smallint 3/6持有最长期限smallint 30/60借阅证期限smallint 1表3-23、书籍详细信息图书ID char 10 主键ISBN char 20 Not null 书籍名char 30 Not null 书籍类别Char 10 Not null 作者char 15 Not null 出版社char 30 Not null 定价money Not null 出版日期date Not null 登记日期date Not null 状态bit Not null表3-34、罚款类别详细信息字段名数据类型长度约束罚款类别编号char 5 主键罚款类别名char 15 Not null 罚款原因char 30 Not null 罚款金额money null表3-45、借阅详细信息字段名数据类型长度约束ID int 主键，自增图书ID char 10 Not null 读者ID char 10 Not null ISBN char 20 Not null 借走日期date Not null 归还日期date null应还日期date Not null 罚款类别编号char 5 null处理状态bit null表3-5第4章数据库物理结构设计数据库物理设计：设计数据库的物理结构，根据数据库的逻辑结构来选定RDBMS（如Oracle、Sybase等），并设计和实施数据库的存储结构、存取方式等。

数据库物理设计是后半段。

将一个给定逻辑结构实施到具体的环境中时，逻辑数据模型要选取一个具体的工作环境，这个工作环境提供了数据存储结构与存取方法，这个过程就是数据库的物理设计。

物理结构依赖于给定的DBMS和和硬件系统，因此设计人员必须充分了解所用RDBMS 的内部特征、存储结构、存取方法。

数据库的物理设计通常分为两步，第一，确定数据库的物理结构，第二，评价实施空间效率和时间效率确定数据库的物理结构包含下面四方面的内容：1、确定数据的存储结构2、设计数据的存取路径3、确定数据的存放位置4、确定系统配置数据库物理设计过程中需要对时间效率、空间效率、维护代价和各种用户要求进行权衡，选择一个优化方案作为数据库物理结构。

在数据库物理设计中，最有效的方式是集中地存储和检索对象。

第5章数据库实施与测试5.1 数据库实施5.1.1数据库及数据库对象建立（1）--建立读者类别表create table读者类别(读者类别编号char(2)primary key not null check(读者类别编号in('01','02')),读者类别名char(15)not null check(读者类别名in('普通读者','高级读者')),可借阅最大数目smallint not null check(可借阅最大数目in(3,6)),持有最长期限smallint not null check(持有最长期限in(30,60)),借阅证期限smallint not null check(借阅证期限in(1)))（2）--建立读者表create table读者(读者ID char(10)not null primary key,密码char(20)not null,姓名char(15)not null,性别char(2)not null check(性别in('男','女')),类别编号char(2)not null foreign key(类别编号)references读者类别(读者类别编号), 单位char(30)null,办证日期date not null,联系方式char(20)null)（3）--建立书籍表create table书籍(图书ID char(10)not null primary key,ISBN char(20)not null,书籍名char(30)not null,书籍类别char(10)not null,作者char(15)not null,出版社char(30)not null,定价money not null,出版日期date not null,登记日期date not null,状态bit not null)（4）--建立罚款类别表create table罚款类别(罚款类别编号char(5)not null primary key,罚款类别名char(15)not null,罚款原因char(30)not null,罚款金额money null)（5）--建立借阅信息表create table借阅信息(id int primary key identity(1,1),--id 自增图书ID char(10)not null,读者ID char(10)not null,ISBN char(20)not null,借走日期date not null,归还日期date null,应还日期date not null,罚款类别编号char(5)null,处理状态bit nullforeign key(图书ID)references书籍(图书ID),foreign key(读者ID)references读者(读者ID),foreign key(罚款类别编号)references罚款类别(罚款类别编号))5.1.2数据存入数据库读者信息由存储过程随机产生100条数据，代码略书籍信息由存储过程随机产生100条数据，代码略5.1.3自定义函数--根据读者ID判断读者证是否过期,判断依据:将读者办证日期加上一年,再和当前系统时间比较alter function check_reader_date(@reader_id char(10))returns bitasbegindeclare@reader_register_date date,@result bitset@reader_register_date=(select办证日期from读者where读者ID=@reader_id)if(Dateadd(year,1,@reader_register_date)<CAST(GETDATE()as date))--如果过期，返回bit=0set@result=0elseset@result=1return@resultend--测试check_reader_date()set@result=dbo.check_reader_date('1000002')if(@result=1)print'没过期'elseprint'过期'--判断读者已借的书籍数量是否超出限制create function check_reader_books(@reader_id char(10))returns bitasbegindeclare@result bit--记录返回值declare@up_book_num smallint--记录读者最高可借的书籍数量declare@borrow_num smallint--记录读者已借的书籍数量set@up_book_num=(select可借阅最大数目from dbo.读者类别where读者类别编号=(select类别编号from dbo.读者where读者ID=@reader_id))set@borrow_num=(select count(*)from dbo.借阅信息where读者ID=@reader_id and处理状态=0)if(@borrow_num<@up_book_num)--可以再借set@result=1else--所借数目达到上限set@result=0return@resultend--判断读者是否有罚款没有交alter function check_ticket(@reader_id char(10))returns bitasbegindeclare@result bitif((select count(*)from dbo.借阅信息where读者ID=@reader_id and罚款类别编号!=null)=0)--如果借阅信息里读者的罚款记录为空,返回set@result=1elsereturn@resultend--select dbo.check_ticket('1000043')--根据读者ID获得图书应该归还的日期create function lend_date(@reader_id char(10))returns dateasbegindeclare@date datedeclare@up_borrow_date smallintset@up_borrow_date=(select持有最长期限from dbo.读者类别where读者类别编号=(select类别编号from dbo.读者where读者ID=@reader_id)) set@date=Dateadd(day,@up_borrow_date,cast(getdate()as date))return@dateend第6章数据库完整性设计6.1 主键及唯一性索引表6-16.2 参照完整性设计1、读者表中将属性类别编号设为外键类别编号char(2)not null foreign key(类别编号)references读者类别(读者类别编号), 2、借阅信息表中的图书ID属性、读者ID属性、罚款类别编号属性设为外键foreign key(图书ID)references书籍(图书ID)foreign key(读者ID)references读者(读者ID)foreign key(罚款类别编号)references罚款类别(罚款类别编号)6.3 Check约束1、读者类别表的读者类别编号进行check约束：check(读者类别编号in('01','02'))2、读者类别表的读者类别名进行check约束：check(读者类别名in('普通读者','高级读者'))3、读者类别表的可借阅最大数目进行check约束：check(可借阅最大数目in(3,6))4、读者类别表的持有最长期限进行check约束：check(持有最长期限in(30,60))5、读者类别表的借阅证期限进行check约束:check(借阅证期限in(1))6、读者表的性别属性进行check约束：check(性别in('男','女'))6.4 触发器设计--创建触发器，当删除借阅信息表中一条数据时，根据书籍ID将书籍表中对应的书籍的状态改为turecreate trigger jieyue_delete on借阅信息after deleteasbegindeclare@book_id char(10)select@book_id=图书ID from deletedupdate书籍set状态=1 where图书ID=@book_idend6.5 规则--创建规则，读者手机号必须为数字create rule phonenum_ruleas@phonenum like'[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'goexec sp_bindrule'phonenum_rule','dbo.读者.联系方式'第7章数据库视图设计--创建书籍库存视图create view book_countasselect ISBN,COUNT(ISBN)as库存from书籍group by ISBN--创建书籍库存create view book_stockasselect书籍.ISBN,书籍名,书籍类别,作者,出版社,定价,出版日期,库存from dbo.book_count,书籍where书籍.ISBN=dbo.book_count.ISBN--创建借书视图create view book_borrowasselect dbo.借阅信息.图书ID,读者ID,dbo.借阅信息.ISBN,dbo.书籍.书籍名,借走日期from dbo.借阅信息,dbo.书籍where借阅信息.图书ID=书籍.图书ID--创建还书视图create view book_lendasselect借阅信息.图书ID,借阅信息.读者ID,借阅信息.ISBN,书籍.书籍名,归还日期from dbo.借阅信息,dbo.书籍where处理状态=1 and借阅信息.图书ID=书籍.图书ID--创建罚款视图alter view ticketasselect借阅信息.读者ID,读者.姓名,罚款类别.罚款原因,罚款类别.罚款金额from借阅信息,罚款类别,读者where借阅信息.罚款类别编号!='00'and借阅信息.罚款类别编号=罚款类别.罚款类别编号and借阅信息.读者ID=读者.读者ID第8章数据库存储过程设计--创建存储过程添加读者信息create procedure add_reader@reader_password char(20),@reader_name char(15),@reader_sex char(2),@reader_kinds char(2),@reader_company char(30),@reader_phone char(20)asbegin--产生编号declare@n intset@n=(select count(*)from读者)set@n=@n+1000000--获得当前日期作为办证日期declare@reader_date dateset@reader_date=Getdate()insert into读者values(@n,@reader_password,@reader_name,@reader_sex,@reader_kinds,@reader_company,@reade r_date,@reader_phone)if@@ERROR>0 --如果上面一条语句出错，则回滚事物，并返回beginrollback tranprint'执行过程中遇到错误'return 0endselect*from读者where读者ID=@n--print '增加成功'end--管理员根据借书证编号查看读者信息的存储过程create procedure reader_info@reader_no char(10)asbeginif((select COUNT(*)from读者where读者ID=@reader_no)=1)select*from读者where读者ID=@reader_noelseprint'没有该读者'end--根据读者ID删除读者的存储过程create procedure delete_reader@reader_no char(10)asbeginif((select COUNT(*)from读者where读者ID=@reader_no)=1)begindelete from读者where读者ID=@reader_noprint'删除成功'endelseprint'没有该读者'end--管理员删除读者证到期的读者的信息create procedure delet_reader_by_dateasbegindeclare@now_date dateset@now_date=GETDATE()delete from读者where Dateadd(year,1,办证日期)<CAST(GETDATE()as date)end--管理员借阅证到期的读者延长借阅证时间create procedure alter_reader_register_date@reader_id char(10)asbeginupdate读者set办证日期=cast(GETDATE()as DATE)where读者ID=@reader_id print'修改成功'end--书籍信息管理--存储过程添加一条书籍信息alter procedure add_book@ISBN char(20),@book_name char(30),@book_kinds char(10),@book_author char(15),@book_publish char(30),@book_price smallint,@book_publish_date dateasbegin--产生编号declare@book_id char(10),@m int,@n intset@m=(select count(*)from书籍)set@n=100000000+@mset@book_id=cast(@n as char(10))--产生登记日期declare@book_rigste_date dateset@book_rigste_date=GETDATE()--产生书籍状态declare@book_state bitset@book_state=1insert into书籍values(@book_id,@ISBN,@book_name,@book_kinds,@book_author,@book_publish,@book_price, @book_publish_date,@book_rigste_date,@book_state)select*from书籍where图书ID=@book_idprint'插入成功'end--根据图示ID删除一条书籍信息create procedure delete_book@book_id char(10)asbegindelete from书籍where图书ID=@book_idend--根据图书ISBN查看一条图书信息create procedure find_book_by_id@book_id char(10)asbeginselect*from书籍where图书ID=@book_idend--根据图书名查看图书信息create procedure find_book_by_name@book_name char(30)asbeginselect*from书籍where书籍名=@book_nameend--根据图书类别查看图书信息create procedure find_book_by_kinds@book_kinds char(10)asbeginselect*from书籍where书籍类别=@book_kindsend--根据作者查看图书信息create procedure find_book_by_author@book_author char(15)asbeginselect*from书籍where作者=@book_authorend--根据出版社产看图书信息create procedure find_book_by_publishment@book_publishment char(30)asbeginselect*from书籍where出版社=@book_publishmentend--根据ISBN查看图书库存create procedure find_book_stock_by_ISBN@ISBN char(20)asbeginselect*from dbo.book_stock where ISBN=@ISBNend--根据书籍名查看库存create procedure find_book_stock_by_name@book_name char(30)asbeginselect*from dbo.book_stock where书籍名=@book_nameEnd--书籍借阅管理--借书管理alter procedure reader_borrow_book@reader_id char(10),@book_id char(10)asbegindeclare@state bitset@state=dbo.check_reader_date(@reader_id)if((select COUNT(*)from dbo.读者where读者ID=@reader_id)=1)--读者存在--判断读者证是否过期if(@state=1)--没有过期--判断读者已借的书籍数量是否超出限制if(dbo.check_reader_books(@reader_id)=1)--已借的书籍数量没有超出限制--判断读者是否有付款没有交if(dbo.check_ticket(@reader_id)=1)--没有罚款未交if((select状态from dbo.书籍where图书ID=@book_id)=1)--图书状态为可借begin--插入借阅信息declare@ISBN char(20),@reader_borrow_date date,@up_date dateset@ISBN=(select ISBN from dbo.书籍where图书ID=@book_id)set@reader_borrow_date=GETDATE()set@up_date=dbo.lend_date(@reader_id)insert into借阅信息(图书ID,读者ID,ISBN,借走日期,应还日期,处理状态)values(@book_id,@reader_id,@ISBN,@reader_borrow_date,@up_date,0)update dbo.书籍set状态=0 where图书ID=@book_idselect*from dbo.借阅信息where图书ID=@book_id and读者ID=@reader_idendelseprint'图书不可借'elseprint'有罚款未交，不能借书，请交罚款'elseprint'已借的书籍数量达到限制，不能再借'else--过期print'借阅证过期'elseprint'读者不存在'end--还书管理alter procedure reader_lend_book@reader_id char(10),@book_id char(10),@tickte_kinds char(5)asbegindeclare@reader_lend_date date,@state bitset@state=1set@reader_lend_date=cast(GETDATE()as date)if((select COUNT(*)from dbo.借阅信息where图书ID=@book_id and读者ID=@reader_id)=0)print'没有该读者或该书籍的借阅信息'elseif((select COUNT(*)from dbo.借阅信息where图书ID=@book_id and处理状态=0)=0) print'该图书已经归还'else--判断是否丢失书籍if(@tickte_kinds='02')--丢失书籍,更新借阅信息beginupdate dbo.借阅信息set归还日期=@reader_lend_date,罚款类别编号=@tickte_kinds,处理状态=@state where图书ID=@book_id and处理状态=0select*from dbo.借阅信息where图书ID=@book_id--delete from 书籍where 图书ID=@book_idendelse--判断是否损坏书籍if(@tickte_kinds='01')beginupdate dbo.借阅信息set归还日期=@reader_lend_date,罚款类别编号=@tickte_kinds,处理状态=@state where图书ID=@book_id and处理状态=0select*from dbo.借阅信息where图书ID=@book_idupdate dbo.书籍set状态=1 where图书ID=@book_idendelse--判断是否超期if(@tickte_kinds='03')beginupdate dbo.借阅信息set归还日期=@reader_lend_date,罚款类别编号=@tickte_kinds,处理状态=@state where图书ID=@book_id and处理状态=0select*from dbo.借阅信息where图书ID=@book_idupdate dbo.书籍set状态=1 where图书ID=@book_idendelse--判断是否超期并损坏if(@tickte_kinds='04')beginupdate dbo.借阅信息set归还日期=@reader_lend_date,罚款类别编号=@tickte_kinds,处理状态=@state where图书ID=@book_id and处理状态=0select*from dbo.借阅信息where图书ID=@book_idupdate dbo.书籍set状态=1 where图书ID=@book_idendelsebeginupdate dbo.借阅信息set归还日期=@reader_lend_date,罚款类别编号=@tickte_kinds,处理状态=@state where图书ID=@book_id and处理状态=0select*from dbo.借阅信息where图书ID=@book_idupdate dbo.书籍set状态=1 where图书ID=@book_idendend--续借管理create procedure renew_book@reader_id char(10),@book_id char(10)asbegindeclare@should_lend_date date--判断是否有该读者借阅该图书的借阅信息if((select COUNT(*)from借阅信息where图书ID=@book_id and读者ID=@reader_id and处理状态=0)=0)print'该读者没有借阅该书籍或图书已经归还'else--判断是否超期set@should_lend_date=(select应还日期from借阅信息where图书ID=@book_id and读者ID=@reader_id and处理状态=0)if(CAST(GETDATE()as date)>@should_lend_date)print'已超期借阅，不能续借，请归还书籍'else--符合续借条件，可以续借begindeclare@up_date datedeclare@up_borrow_date smallintset@up_borrow_date=(select持有最长期限from dbo.读者类别where读者类别编号=(select类别编号from dbo.读者where读者ID=@reader_id)) set@up_date=(select应还日期from借阅信息where图书ID=@book_id and读者ID=@reader_id and处理状态=0)set@up_date=Dateadd(day,@up_borrow_date,cast(getdate()as date))update借阅信息set应还日期=@up_date where图书ID=@book_id and读者ID=@reader_id and处理状态=0select*from借阅信息where图书ID=@book_id and读者ID=@reader_id and处理状态=0endend--查看所有借阅信息alter procedure find_lb_infoasbeginselect*from dbo.借阅信息end--根据读者ID查看借阅信息create procedure find_lb_info_by_readerID@reader_id char(10)asbeginselect*from dbo.借阅信息where读者ID=@reader_idend--根据读者ID查看罚款信息create procedure find_ticket_by_readerID@reader_id char(10)asbeginselect*from ticket where读者ID=@reader_idend--查看借书记录create procedure find_book_borrowasbeginselect*from dbo.book_borrowend--根据读者ID查看借书记录create procedure find_book_borrow_by_readerID@reader_id char(10) asbeginselect*from book_borrow where读者ID=@reader_idend--根据读者ID查看还书记录create procedure find_book_lend_by_readerID@reader_id char(10) asbeginselect*from book_lend where读者ID=@reader_idEnd总结理论联系实际才能做好一件事，学习一门课程同样是这样。