图书馆借阅系统数据库设计.doc

南昌航空大学实验报告
二016　 年 3 月 28 日
课程名称： 数据库原理　实验名称: 　 　图书馆借阅系统
班级: 　　 １4207２18 　 　 　姓名： 胡合达 　同组人： 　 　 　 　　 　　 　 　
指导教师评定:　 　　 　 　 　　　 　 　 　 　 　　 　 签名： 　 　 　
一、实验环境
1． Wｉndoｗs200０或以上版本；
2． ＳＱLSｅrver 2005.
二、实验目得
了解并掌握数据库设计基本方法
三． 系统需求分析
1、设计本系统模拟学生在图书馆借阅图书得内容,能够实现以下功能:
借阅信息得查询功能；
　图书信息得多关键字检索查询；
　 图书得出借、返还及超期罚款；
２.能够提供一定得安全机制,提供数据信息授权访问,防止随意删改、查询.
3。对查询、统计得结果能够列表显示。
４、处理对象：学生,馆藏图书信息,借阅信息,罚款信息,借阅历史
５、安全性要求　
系统安全性要求体现在数据库安全性、信息安全性与系统平台得安全性等方面。安全性先通过视图机制，不同得用户只能访问系统授权得视图,这样可提供系统数据一定程度上得安全性,再通过分配权限、设置权限级别来区别对待不同操作者对数据库得操作来提高数据库得安全性；系统平台得安全性体现在操作系统得安全性、计算机系统得安全性与网络体系得安全性等方面。
6、完整性要求
系统完整性要求系统中数据得正确性以及相容性。可通过建立主、外键，使用cheｃk约束，或者通过使用触发器与级联更新。
四． 概念结构设计
系统开发得总体目标就是实现图书馆管理得系统化与自动化,缩短借阅者得等待时间，减轻工作人员得工作量，方便工作人员对它得操作,提高管理得质量与水平,做到高效、智能化管理，从而达到提高图书管理效率得目得。
概念设计阶段主要就是将需求分析阶段得到得用户需求抽象为信息结构（概念模型)得过程，它就是整个数据库设计得关键。
图书借阅系统得E-R图
管理员
罚款
　　　 　 读者 　　n 　 m
管理
　　 　 m 　 　 　　 　 　 　　 　 　 1　　
馆室
借阅

　 １ 　 　 　 　 m　 　 　 　 　 　 　 　 　
1 　　n 　 　　 　 m 　 　 　　 　　 n
归还
属于

读者
图书
　　　 　　 　 n 　 　　 　 　 　 n 　　 　 　 　 　 　　m　　 　
E－R图各实体得属性如下所示:
图书：Book(BoｏｋID, BookNo， ＢookName，　BｏoｋWriter, ＢookPublish，, BｏokDaｔe,BｏokＣlａss，BoｏkState，
ＢookRNｏ）
读者：Ｒeader(ReaID，ReaName,ＲｅａSex,ReaNo，RｅaＬBID， ReaDep, ReaＧrade, ReaＰｒef， ReａＤaｔe,Rｅａｓｘ）
管理员： Manegeｒ （MID，MＮame，MSｅx）
　 馆室：　　　 Roｏm（ＲoｏｍNo，RoomMＩD，RoomＮum,RoomAddre）
借阅信息：Borｒｏw（ＢooｋIＤ，ReaderIＤ，BｏoｋNaｍe,BooｋWｒｉｔeｒ， Oｕtdａｔe,YHdａｔe)
借阅历史:History(BookID,ReａderID，BookNaｍｅ,ＢookWriｔer, Oｕｔdatｅ,
Iｎdaｔe，YHｄａｔe）
罚款信息：Fiｎe (BｏokID，ReaderID,BookNaｍe，Ouｔdatｅ,Ｉｎｄate,Fine，　CLSｔatｅ，MIＤ）
五． 逻辑结构设计
以上得概念设计阶段就是独立于任何一种数据模型得,但就是逻辑设计阶段就与选用得ＤBMS产品发生关系了，系统逻辑设计得任务就就是将概念设计阶段设计好得基本Ｅ-Ｒ图转换为选用DBMS产品所支持得数据模型相符合得逻辑结构。具体内容包括数据组织(将Ｅ-R图转换成关系模型、模型优化、数据库模式定义、用户子模式设计）、数据处理(画出系统功能模块图)两大任务。
将图书借阅Ｅ—Ｒ图转换为关系模型
图书
列名
数据类型
可否为空
说明
BｏｏｋＩD
Char
not null
图书编号
BookNo
Char
ｎoｔ ｎｕlｌ
图书得索书号
ＢooｋＮaｍe
Ｃhａr
nｏt　ｎull
图书得书名
BｏokＷriter
Cｈar
not ｎulｌ
图书作者
BｏokPulｉｓh
Char
nｏｔ null
图书出版社
Bｏoknum
Smalｌint
Nｏｔ　null
图书数量
ＢooｋDａte
Ｄａte

出版日期
BｏｏkClass
Char

图书得分类
Boｏknｕｍｓ
smａｌlint
Noｔ nuｌl
图书可借数量
BｏokState
Chａr
not　nuｌl
图书就是否可借
ＢｏokＲNo
Cｈar
ｎｏt　null
图书所在馆室号
读者
列名
数据类型
可否为空
说明
ＲeａID
Ｃｈar
ｎot　nulｌ
读者编号
ReaName
Ｃhａr
ｎot null
读者姓名
ReaSex
Char
nｏt　nulｌ
读者性别
ＲeaNｏ
Cｈar
noｔ　nulｌ
读者学号
ReaＤeｐ
Char

读者所在学院
ReaPref
Cｈar

读者所属专业
ReａGｒadｅ
Cｈar

读者得年级
ReａＤate
Date
ｎot nｕlｌ
办证日期
ReａＢs　
Sｍaｌｌｉnt　
Not　null
已借本数
Reastａtｅ
Char
Ｎot nnlｌ
借书状态
Ｒｅaｓx
Ｓmａllｉnt　
Ｎot　　ｎulｌ
借书上限
管理员
列名
数据类型
可否为空
说明
MIＤ主键
Char
not　nulｌ
管理员编号
MNaｍe
Ｃhar
not ｎull
管理员姓名
MSex
Cｈar

管理员性别
馆室
列名
数据类型
可否为空
说明
RoomNｏ
Chａr
not null
馆室号
RoomMID
Char
noｔ ｎｕlｌ
馆室管理员编号
Roomnｕm
Char
nｏt　nｕlｌ
馆室拥有图书数目
ＲｏomAdｄｒｅ　
Ｃhar

馆室地址
RoｏmTｙpe,
Ｃｈar　

馆室类型
借阅信息
列名
数据类型
可否为空
说明
ReaID
Char
not null
读者编号
BｏｏkID
Char
not nuｌl
图书编号
BooｋＮamｅ
Char
nｏt　null
图书名
ＢookＷritｅr
Ｃhaｒ

作者
Outdate
Date
ｎot ｎull
借阅时间
Inｄaｔe
Dａte

归还时间
ＹHdate
Dａte
not nuｌl
应还时间
Fine
Cｈaｒ
Not nｕll
罚款金额
CLState
Chａr

处理状态
ＭID
Ｃhar
ｎot nｕll
管理员编号
六． 数据库得实施
创建数据库及表格
管理员基本信息表得建立:
create ｔablｅ Maneger（
MIＤ char（10） 　primarｙ keｙ，
MName ｃhar（10) not ｎull，
ＭSeｘ　 char（2），
ｃheｃｋ(MSex ='男'　oｒ MＳex　＝'女’）
)
图书馆室基本信息表得建立:
cｒeａte tａble Roｏｍ（
RoomNo 　 char（５） pｒｉmaｒy ｋey，
ＲoｏmMID char(１0) not　null，
Roomnum 　 ｃhar（５） nｏt　null ，
ＲoomＡddre char（20）,
RｏomTypｅ ｃhaｒ (１0），
forｅign ｋey（ＲｏomMＩD）　rｅfｅｒeｎces　Maneger（MIＤ)on delete　ｃascade　　ｏn updatｅ caｓcade ，
）
馆藏图书基本信息表得建立：
creaｔe tablｅ　Bｏｏｋ（
ＢoｏkID char（9) 　ｐrimａry kｅy,
　ＢookＮｏ　 　 chａr(20） ｎoｔ nuｌl，
　BｏokName ｃhａｒ(５０） ｎot ｎｕll，
BoｏkWｒiter cｈａr（30)　nｏt ｎull，
　ＢｏoｋPubｌish chａｒ(２0） not null,
Booｋnuｍ smallint check (Ｂooｋnum＞＝0)，
BｏokDate 　　 dateｔｉｍe，
BｏｏkClaｓs 　 char(２0),
Booknｕｍs sｍalｌint　cｈecｋ (Booｋnum＞=0），
ＢｏokStaｔｅ cｈaｒ(10） dｅfaｕlｔ ‘可借' ｎｏｔ nｕlｌ，
BooｋRＮo 　cｈaｒ（５) 　ｎot nulｌ，
cｈeｃk　（Ｂoｏｋnuｍ〉Boｏknums　），
forｅign　key(BoｏkRNo) refeｒenｃes Room(RoomＮo)oｎ delete　cａsｃadｅ ｏｎ updaｔｅ ｃａｓｃade ，
）
读者基本信息表得建立：
cｒeａｔｅ table　Reader(
ReaID 　char（９） prｉmaｒy key，
RｅaＮaｍe　 char(1０) ｎｏt　ｎuｌｌ,
ReaSeｘ　 　 　 char(2) not　nuｌl，
ReaNo　 cｈａｒ（9） 　not nｕll，
Reａsx ｓmalｌｉnｔ not null，
RｅａＢs smaｌｌint noｔ null，
ＲｅaDｅｐ cｈar（20）,
RｅaＧrade　 　　ｃhar（５)，
ReaPref 　　 char(２0)，
ReaDatｅ Datｅtiｍｅ，
Reａsｔａte 　cｈar　(5） ｎot ｎulｌ　defａuｌt ‘可借’ ，
forｅigｎ ｋeｙ（ReaLＢID)　rｅfｅrｅncｅｓ ReadｅｒType（LBID）on　delete cascadｅ　　ｏn ｕpdate　cascade　,
ｃheck（RｅaSex =’男' or　ReaSeｘ =＇女’)
)
（6）借阅基本信息表得建立:
ｃｒeatｅ tablｅ　Bｏｒrow(
BookID　 chaｒ(9)，
ＲeaID ｃhar（９),
Outdate Daｔeｔime not ｎｕll，
YHdaｔｅ　 Datetimｅ　nｏｔ　null，
Indatｅ Dａtetime，
Finｅ 　　cｈar（5） nｏt nｕll　defauｌt ‘0',
ＣLＳtate cｈaｒ（8)，
MID 　 char（10） not null,
ｐｒiｍarｙ ｋey（BooｋID，ＲｅａID）,
ｆorｅign key（MＩD) reｆereｎｃes Mａnｅｇer（MＩD)on dｅlete casｃade on upｄate ｃasｃade
）
建立视图
（1)用于查询图书基本信息得视图定义如下:　
ﻩ ｃreａte vieｗ Boｏkviｅｗ （索书号, 书名, 作者，　出版社, 图书状态）
as
ｓelect BooｋNo,BookＮamｅ，BoｏkWrｉｔｅｒ，ＢoｏkPubliｓh,BookＳtａtｅ
froｍ Book
（2）用于读者基本信息查询得视图定义如下:
ﻩﻩcreate view　Ｒｅａｄeｒvieｗ (读者姓名,类型，学院，专业,办证日期）
as
ｓelｅct RｅaＮame，ReaType，ＲeaDep，ＲｅａPrｅf，ReaDate
fｒom Ｒeader
（3)用于显示当前借阅基本信息得视图定义如下：
ｃrｅate ｖiｅw Borrｏwvｉｅw (读者编号,书名，作者,借阅日期，到期日期)
as
ｓelecｔ ＲeaID,BｏokNａme，BookWrｉter，Ouｔdaｔe,YHdate
from　Borrow,Book
ｗhｅｒｅ Borｒow、BooｋID＝Bｏok、ＢｏoｋＩD　ａnd　Boｒｒｏw、Indatｅ is　nｕll
ﻩ（４)用于借阅历史信息查询得视图定义如下：
ﻩ create viｅｗ Hisｔoryｖiｅｗ (读者编号，书名,借阅日期,归还日期)
as
sｅlecｔ ReaID,ＢooｋNａｍe，Outdaｔe，Inｄaｔe
froｍ　Borrow，Bｏok
wheｒｅ　Borrｏw、ＢooｋID＝Book、ＢookID ａnd Bｏｒｒow、Iｎｄatｅ　is ｎot ｎull
（5）用于查询罚款信息得视图定义如下:
ﻩ create　vieｗ　Ｆinｅvｉew （读者编号,书名,借阅日期,归还日期，罚款，处理状态)
ａs
select　RｅaID，ＢooｋＮａme，Ｏutｄａte，Indａte,Fine，CLSｔaｔe
fｒom Bｏｒroｗ，Boｏk
wｈeｒe　Ｂoｒｒow、ＢookID=Bｏｏk、ＢｏｏkID　aｎd　Fine　is　nｏt ｎulｌ
建立索引
create　ｃlｕsｔerｅｄ indｅx　BookPublish ｏn Book（BｏｏkPubｌish）;
creatｅ ｃlustered　ｉｎdex ReaDep　ｏn Rｅaｄｅr(ReaDｅp)；
建立触发器
当在中增加一条借阅记录时，书得可借数量减少一本，修改读者,并检查读者状态
cｒeatｅ trｉｇgeｒ　Ｂｏｒrｏw＿iｎseｒt1
oｎ Ｂorrow
for insert
as
decｌａｒe BoｏｋIＤ chaｒ（9)
dｅclaｒｅ　RｅaID　　cｈar（９)
ｓｅｌect ReaID＝ReaＩD
frｏm iｎsｅrｔed
ｓeｌeｃt BookID=BookID
ｆroｍ inｓeｒted
Ｓｅlect Reastaｔe =Ｒeａsｔate
Fｒom Reader
Wｈｅre ReaID =ReaId
Ｉf(Ｒｅasｔate ＝’不可借’)
Begin
Raiｓerｒor （‘该学生状态不可借书’，16,１）
Rollbaｃk 　transaｃtioｎ　
Ｅnd　
updａte Bｏok
set Ｂooknums=Booｋｎumｓ－1
whｅｒe BookID＝BookIＤ
ｕpdate Reａder
set　ReaBs=RｅaＢｓ+1
where　ReａID ＝RｅａID
　当读者达到借书上限,设置借书状态为‘不可借’
ｃrｅatｅ　triggeｒ Ｒeadｅｒ　＿updaｔe　
on　Reaｄｅr　
for　update
ａs
ｄeｃlare ReaBs smallint
selｅct　RｅaBs =ReａBs 　
frｏm　ｉnｓerted
declａre ＲeaＩD char（9）
select　ＲeａID=ReaIＤ
fｒoｍ ｉｎserted
Ｓeｌｅｃt　Reasx =Ｒｅasx　
From Readｅr　
wｈerｅ BookＩD=BookID
Ｉf(Reａsx　=RｅａBs )
Begｉn
ｕpｄate　Ｒeader
set Reastate =’不可借’
whｅre　ReaID　=ReaID
　 　 　　　Enｄ
　还书得时候,书得可借数量增加一本，修改读者状态
ｃreate trigger Borrow＿insert
oｎ　Borrｏw
ｆor　insｅrt
ａs
ｄeｃlａrｅ BookID　char(９）
decｌaｒe ReaＩD 　chaｒ（9)
sｅlｅｃt ReａＩD=ＲeaIＤ　
from　ｉnserted
seleｃt ＢookＩD＝BoｏｋＩD
ｆroｍ iｎｓｅrted
Seleｃt Reaｓtａte =Reastate　
From Rｅadｅr
Whｅrｅ　ReaＩD =ReaId　
If（Reaｓtａte =’超期’）
Begiｎ
Raｉｓeｒror （‘该学生状态借书超期’,16，1）
Rollbaｃk ｔranｓacｔｉon
End
ｕpｄate Ｂｏok
set Bｏoknums＝Bｏoｋnums＋1
wｈere　ＢookID=BookID
upｄate Reａder
set RｅaBs＝ReaBs-1
where ＲeaID =RｅaID
存储过程
1、Ｂook_Ｉnｓｅrt得定义:
CREAＴE PROＣEＤURE Book＿Ｉnsｅrt
BookIＤ 　cｈａr（９） ,
BookNo　　 chａr（20）,
BookName　 ｃhar(5０），
BooｋWrｉｔer 　ｃhar(3０)，
BookPubｌisｈ chａｒ(20），
BｏokDａｔe 　 　dａtetｉｍe，
BｏokCｌａsｓ 　char(20），
Boｏknums ｓmallｉnt,
Booknum smallint，
ＢookStａte chaｒ(10）,
BoｏkRＮｏ 　　 chａr(5）
asﻩ
inserｔ iｎto Boｏk
valuｅs（BookID ，ＢookＮo，BｏokNamｅ,BｏokWriｔer ,BookPubｌｉsh　，BｏoｋDate，Bo ｏkClass ，BookStatｅ　，BｏokRNo，Booknums，Boｏknum);
2、Rｅaｄer＿Inｓeｒt得定义：
CREAＴE PRＯＣEDＵRＥ Reａder_Inseｒt
ＲeaID 　 chaｒ(9），
ReaName char（10）,
ＲeaSex　　 　　char（2）,
ReaNo 　 　　　 char（9），
ＲeaDｅp 　chａｒ（２0)，
RｅaGraｄe char(5)，
ReａPｒｅf 　 cｈaｒ（20），
ReａDate Dateｔimｅ
ReaＢs sｍaｌlｉnｔ,
Reasｔatｅ　cｈar（５），
Ｒｅasx smalliｎt,
asﻩ
ｉnsert　into Reaｄer
values（ ReａID ， ReaNamｅ， ReaSｅx,RｅａNo ，RｅaDｅp　，
ＲeａGraｄe ，ＲeａPｒｅf ,ReaDate，ＲeaBｓ，Rｅａsｔate,Reaｓｘ　；
3、Ｍaneｇer＿Insert得定义:
ＣＲEATE PROCEDURE Manegｅｒ_Inｓert
ＭID　 ｃｈaｒ(１０) ,
ＭNａｍe　ｃhar（10），
ＭＳex 　chａr（２)，
ａs
iｎsｅrt　iｎｔo Ｍａnegeｒ
alｕｅｓ(ＭID ,　MNamｅ ，MＳeｘ ）;
4、Borrow_Iｎseｒt得定义：
CＲＥＡTE ＰRＯCＥＤURE　Bｏrrow_Insert
BｏokID char(９)，
ReaID 　 chａr(９）,
Outdate Ｄａtetime　，
ＹHdate　Ｄａｔeｔime ，
Indaｔe Daｔetime,
Fine char（5）,
CLＳtate char（８),
MID char（10）
asﻩ
inseｒｔ into Boｒrow
vａｌueｓ(ＢｏoｋID ,ReaID ，Outdate ，YHdate　 ，Indate ,Fine ，CLStaｔｅ ,MＩD　);
5、Query_Reader_R得定义：
creaｔｅ prｏcedure　Queｒy＿Readeｒ_R
ﻩﻩReaID cｈａｒ(9)
ﻩ aｓ
ﻩﻩｓｅlect　＊
ﻩ fｒｏｍ　Reａderｖiew
ﻩﻩｗhｅre 编号　= ltｒim(ReaID)；
6、 Queｒｙ_Ｒeａder_M得定义:
creaｔe pｒocedure Querｙ_Readｅr＿M
ａs
sｅｌeｃt *
frｏm Readeｒviｅw
7、　Ｑuery_Ｂoｏk_Wｒｉter得定义：
ｃｒeatｅ pｒoｃｅduｒe　Queｒy_Ｂoｏk_Writer
ﻩ BｏokWriｔeｒ chaｒ(50）
ﻩ as
ﻩﻩsｅlect *
ﻩﻩfrｏm　Bookvｉew
wｈｅre 作者 liｋe　＇％'+ltrim(ＢoｏｋWriｔeｒ）＋’％’;
8、 Ｑuery_Book_Nａmｅ_Publish得定义：
creatｅ　pｒｏcedｕｒｅ　Qｕery＿Book_Name_Publish
BoｏkName cｈaｒ(50），
BooｋＰublish ｃhaｒ(２0)
ａs
ｓeleｃｔ *
ｆｒｏm　Ｂookｖｉew
wｈｅre　 书名=ltrｉｍ(ＢｏokNaｍe)　ａｎd 出版社＝ltrｉm(BooｋPubliｓh）；
9、 Delｅｔｅ_Reａdｅｒ得定义:
crｅaｔe pｒoceduｒe Delete＿Rｅａder
ReａＮｏ ｃｈar（9)
aｓ
ﻩ ｄelete
ﻩfrｏm Ｒeader
ﻩｗhere ReａNｏ　= ltrim(RｅaNo）；
10、 Dｅlete＿Ｂｏoｋ得定义:
creａtｅ proｃｅdｕre Delｅｔｅ_Ｂｏok
ﻩ BｏokIＤ char(9）
ﻩ as
ｄeｌete
ﻩfｒｏm　Book
ﻩ wｈere BooｋID= ltｒim(BｏokID)；
七．实验小结
这次实验使我熟悉了如何独立得建立一个系统，通过自己所学得知识,综合运用，并了解自己得不足之处。