《数据库原理及应用》课程设计报告.doc

数据库原理及应用课程设计报告 专 业： 软件学院 班 级： 12级软工java2班姓 名： 张振华 学 号： 1215115054 设计题目： 图书管理系统 一、 系统需求分析1.1从读者角度考虑的数据流程图图2.1 业务流程图1.2图书管理系统数据流程图图 2.2 顶层数据流顶层数据流图：第2层数据流图：（读者借阅，读者还书，读者查询，管理员查询，管理员修改）第3层数据流图：（读者借阅）第3层数据流图：（读者还书）第3层数据流图：（查询图书信息，查询读者信息，查询借阅历史，查询罚款信息）第3层数据流图：（管理员添加、删除、修改图书信息）第3层数据流图：（管理员添加、删除、修改读者信息）1.3 图书管理系统数据字典表1-1 Book书籍信息表表中列名数据类型可否为空说明BookIDvarcharnot null(主键)图书编号ISBNvarcharnot null索书号SPlace varchar not null 存放位置 State varchar Not null 当前状态表1-2Reader读者信息表格表中列名数据类型可否为空说明RIDvarcharnot null(主键)读者借书证号Namevarcharnot null读者姓名Telvarcharnot null读者电话Emailvarcharnot null读者邮箱BNumberIntnot null可借书数表1-3 Manager管理员信息表格表中列名数据类型可否为空说明MIDvarcharnot null(主键)工号Namevarcharnot null姓名RoomIDvarchar馆室号表1-4CIP书目信息表表中列名数据类型可否为空说明ISBNVarcharNot null(主键)索书号booknameVarcharNot null书籍名称AuthorVarcharNull书籍作者PyearCharNull出版年月PublisherVarcharNull出版者NumberIntNull册数表1-5Borrow 借阅记录信息表表中列名数据类型可否为空说明RIDVarcharNot null(外主键)读者借阅证编号BookIDVarcharNot null(外主键)书籍编号BDateDatetimeNot null读者借书时间SRDate Datetime Not null 应还日期ISBN Varcher Not null（外键） 索书号表1-6History 借阅历史信息表表中列名数据类型可否为空说明BookIDVarcharNot null（外主键）读者借阅证编号RIDVarcharNot null(外主键)书籍编号RDatedatetimeNot null读者还书时间 BDate Datetime Notnull读者借书日期表1-7fine 罚款记录信息表RIDvarcharNot null（外主键）读者借书证编号ClStateDatetimenull是否缴纳罚金BookIDvarcharNot null(外主键)书籍编号SRDateDatetimeNot null书籍名称FinevarcharNull罚款金额BDatedatetimeNot Null借阅时间 MID Varcher Not null（外键） 工号 RDateDatetime Not null 还书日期表1-8room 馆室信息表表中列名数据类型可否为空说明RoomIDVarcharNot null（主键）馆室编号RoomNameVarcharNot null馆室名表1-9 Appiontmnet 预约信息表表中列名数据类型可否为空说明ISBNVarcharNot null(外主键)索书号RIDVarcharNot null（外主键）读者编号BDateDatetimeNull借阅日期MIDDatetimeNot null（外键）工号ApplimitVarcharNull预约时间限制RDate Datetime Null 归还日期二、概念结构设计可以用E/R模型表述该模型的设计，E/R图如图所示。读者姓名电话IDEmail罚款借阅历史管理员工号姓名属于借阅预约可借书数图书IDISBN存放位置State属于对应管理归还读者CIP册数ISBN书名作者出版商出版年月资料室拥有RoomIDRoomNamemnn11nmmnnnmn11n1n三、逻辑结构设计通过E/R模型到关系模型的转化，可以得到如下关系模式：(一)各ER图各实体的属性如下所示1、 图书：Book（BookID,ISBN,SPlace,State）2、 读者：Reader（RID,Name,Tel，Email,BNumeber）3、 管理员：Manager（MID,Name，RoomID）4、 书目：CIP(ISBN,BookName,Author,Publisher,Pyear,Number)5、 馆室：Room(RoomID,RoomName)(二)各ER图中联系的属性如下所示：6、 借阅信息：Borrow（BookID,RID,BDate,SRDate）7、 借阅历史：History（BookID,RID,BDate,RDate）8、 罚款信息：Fine（RID,BookID,BDate,RDate,Fine,CLState,MID）9、 预约信息：Appiontmnet（RID,ISBN,RDate,AppLimit,MID）3.1.1将E-R图转换为关系模型实体型转换为关系模式。实体的属性就是关系的属性，实体的码就是关系的码。对于实体间的联系则有以下不同的情况：一个m：n联系转换为一个关系模式。与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合。一个1：n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为n端实体的码。 一个1：1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。三个或三个以上实体间的一个多元联系可以转换为一个关系模式。与该多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合 具有相同码的关系模式可合并。由于图书与CIP、资料室与图书的联系方式是1:n（一对多），可以将其之间的联系与n端实体CIP、图书合并，管理员与图书之间的维护联系也是1:n（一对多），同样也将其之间的联系与n端实体合并，而读者与图书之间的借阅和归还联系方式则是n:m（多对多），这样要把它们之间的联系转化为独立的关系模式，读者与管理员之间的罚款联系是m:n（多对多），将其联系也转化成独立的关系模式，具体的基本E-R图向关系模型的转化如下：图书：Book（BookID，ISBN，SPlace,State）读者：Reader（RID，Name，Tel，Email，BNumeber）管理员：Manager（MID，Name,RoomID）书目：CIP(ISBN，BookName,Author,Publisher,Pyear,Number)馆室：Room(RoomID,RoomName,)借阅信息：Borrow（BookID，RID，BDate，SRDate）借阅历史：History（BookID，RID，BDate，RDate）罚款信息：Fine（RID，BookID，BDate，SRDate,RDate，Fine，CLState，MID）预约信息：Appiontmnet（RID，ISBN，RDate，AppLimit，MID）（注：标有直线下划线的为主属性，标有波浪线下划线的是外键属性，主属性与外键属性一起构成主码）3.2.1模型优化关系模式Book,Reader,Room ,Maneger，CIP，Room不存在非主属性对主属性的部分函数依赖，也不存在传递函数依赖，已经达到了3NF，但是借阅关系模式、借阅历史关系模式、罚款关系模式及预约信息模式（Borrow,History,Fine，Appontment）中存在着一些数据冗余，现将四个关系模型进行合并，消除冗余，优化为：借阅信息:Borrow(BookID,RID,ISBN,BDate,RDate,SRDate,Fine,AppLimit CLStaete,MID)四、系统实现（SQL编程）4.1.1 建立视图1、建立Book表CREATE TABLE Book（BookID varchar（20） PRIMARY KEY，ISBN varchar（50） NOT NULL，SPlace varchar（50） NOT NULL，State char（2） ）；2、 建立Reader表CREATE TABLE Reader（RID varchar（10） PRIMARY KEY，Name varchar（18） NOT NULL，Tel varchar（18） NOT NULL，Email varchar（18） NOT NULL，BNumber int check(BNumber=0) default 10,-可借书的数量）；3、 建立CIP表CREATE TABLE CIP(ISBN varchar（10） PRIMARY KEY， BookName varchar（20） NOT NULLAuthor varchar（20） Publisher varchar（50）， Pyear char（8）Number int check(Number=0)）；4、 建立Manager表CREATE TABLE Manager（MID varchar（8） PRIMARY KEY，Name varchar（18） NOT NULL，RoomID varchar(18)CONSTRAINT FK_Manager_RoomIDFOREIGN KEY(RoomID) REFERENCES RoomID(RoomID),）；5、建立Room表CREATE TABLE Room（RoomID varchar（8） PRIMARY KEY，RoomName varchar（18） NOT NULL，）；6、 建立Borrow表CREATE TABLE Borrow（BookID varchar（20） NOT NULL，RID varchar（10） NOT NULL，ISBN varchar（50） NOT NULL，BDate datetime NOT NULL, SRDate datetime NOT NULL,primary key(BookID, RID),CONSTRAINT FK_Borrow_BookID FOREIGN KEY(BookID) REFERENCES Book(BookID),CONSTRAINT FK_Borrow_ISBNFOREIGN KEY(ISBN) REFERENCES CIP(ISBN),CONSTRAINT FK_ Borrow_RID FOREIGN KEY(RID) REFERENCES Reader(RID)）；7、 建立History表CREATE TABLE History（BookID varchar（20）NOT NULL，RID varchar（10） NOT NULL，BDate datetime NOT NULL, RDate datetime NOT NULL,primary key(BookID, RID),CONSTRAINT FK_History_BookID FOREIGN KEY(BookID) REFERENCES Book(BookID),CONSTRAINT FK_History_RID FOREIGN KEY(RID) REFERENCES Reader(RID)）；8、 建立Fine表CREATE TABLE Fine（RID varchar（20） NOT NULL,MID varchar（8） NOT NULL,BookID varchar（20）NOT NULL,BDate datetime NOT NULL,SRDate datetime NOT NULL,RDate datetime NOT NULL,Fine varchar(20) CLState char(8),primary key(BookID, RID),CONSTRAINT FK_Fine_RID FOREIGN KEY(RID) REFERENCES Reader(RID),CONSTRAINT FK_ Fine_MID FOREIGN KEY(MID) REFERENCES Manager(ID),CONSTRAINT FK_ Fine_BookID FOREIGN KEY(BookID) REFERENCES Book(BookID)）；9、 建立Appiontmnet表CREATE TABLE Appiontmnet（RID varchar（8） NOT NULL，MID varchar（6） NOT NULL，ISBN varchar（20）NOT NULL，RDate datetime BDate datetime AppLimit varchar（20） default 10primary key(ISBN, RID),CONSTRAINT FK_Appiontment_RID FOREIGN KEY(RID) REFERENCES Reader(ID),CONSTRAINT FK_ Appiontment_MID FOREIGN KEY(MID) REFERENCES Manager(ID),CONSTRAINT FK_ RETURN _ ISBN FOREIGN KEY(ISBN) REFERENCES CIP(ISBN)）；4.1.2 建立视图（1）用于查询图书基本信息的视图定义如下： create view Bookview (索书号, 书号,书名, 出版社, 册数,存放位置,当前状态)asselect CIP.ISBN, BookID,BookName,Publisher,Number,Splace,Statefrom CIP,Bookwhere CIP.ISBN=Book.ISBN（2）用于读者基本信息查询的视图定义如下：create view Readerview (读者姓名,编号,电话,邮箱)asselect Name,RID,Tel,Emailfrom Reader（3）用于显示当前借阅基本信息的视图定义如下：create view Borrowview (读者编号,书名,作者,借阅日期,到期日期)asselect RID,BookName,Author,BDate,SRDatefrom Borrow,CIPwhere Borrow.ISBN=CIP.ISBN （4）用于借阅历史信息查询的视图定义如下：create view Historyview (读者编号,书名,借阅日期,应还日期)asselect Borrow.RID,BookName,BDate,SRDatefrom Borrow,CIP,Readerwhere Borrow.RID=Reader.RID（5）用于查询罚款信息的视图定义如下：create view Fineview (读者编号,书名,借阅日期,归还日期,罚款,处理状态)asselect Borrow.RID,BookName,Borrow.BDate,RDate,Fine,CLStatefrom Borrow,CIP,Finewhere Borrow.ISBN=CIP.ISBN and Fine is not null (6)用于预定信息的视图如下:Create view Appiontmentview(读者编号,索书号,预约时间,预约期限天)AsSelect Appiontment.RID,Appiontment.ISBN ,BDate,ApplimitFrom Appiontment,CIPWhere Appiontment.ISBN=CIP.ISBN and CIP.Number is null /*当某书目可借书数量为零时*/4.1.3建立触发器本系统创建了三个触发器，它们分别实现删除图书、统计图书可借数量、借阅图书信息的功能。其代码如下。1创建删除图书的触发器create trigger deletebookon Book instead of deleteasdelete from Borrow where RID in (select RID from deleted)delete from Book where id in (select BookID from deleted)2创建统计图书可借数量的触发器create trigger Readercountable on Borrow after insertasupdate Reader set BNumber= BNumber -1 where RID in (select RID from inserted)-借书后用户的可借数量减13创建借阅图书信息功能的触发器/*借书管理，当在中增加一条借阅记录时，使该图书的状态由“未借”变为“已借出*/create trigger Borrow_insert1on Borrowfor insertas declare BookID char(9)select BookID=BookIDfrom insertedupdate Bookset State=已/* 已借出*/where BookID=BookID/*验证*/insert into Borrow values (07,04,806,2012-2-4,2012-4-4)4还书管理 /*还书管理(举实例论证,预约功能在上一触发器中实现)*/BEGIN TRANSACTIONINSERT INTO History(BookID,RID,BDate,RDate)VALUES(11,02,2012-4-6,2012-4-8)UPDATE BOOK SET State=0 WHERE BookID=11COMMIT;5自动计算罚金公式/*若超过时间限制，计算罚金公式*/*1.把没归还日期的归还日期改为系统时间*/UPDATE History SET RDate=getdate() where RDate is nulll/*2.假设有个可借天数字段天*/update Fine set fine=(datediff(day,2012-3-1,2012-6-3)-60)*0.2/*没有可借天数的话把-可借天数以及外面的一对括号删除*/4.1.4管理员操作1） 入库管理：/*入库管理，新书入库*/insert into CIP(ISBN,BookName,Number,Author,Publisher,Pyear)values(813,中外通史,5,方严,高教,2007.5)/*旧书入库*/update CIPset Number=Number+1 where ISBN=8052） 增加读者：INSERT INTO Reader(RID,Name,Tel,Email,BNumber)VALUES(#RID,#Name, #Tel,#BNumber);3） 删除读者：DELETE FROM Reader WHERE(RID=#RID);4） 修改读者信息：UPDATE Reader SET Name=#Name, Tel=# Tel WHERE(RID=#RID);5） 增加书籍：INSERT INTO CIP(ISBN, BookName,Author, Publisher, Pyear, Number) VALUES(#ISBN, #BookName, #Author, #Publisher, #Pyear, #Number);6） 删除书籍：DELETE FROM Book WHERE(BookID=#BookID);7） 修改书籍信息：UPDATE Book SET BookName=# BookName, Author =#Author, Publisher =#Publisher, Pyear =#Pyear, Number =#Number WHERE(BookID=#BookID);8） 学生借阅图书：BEGIN TRANSACTIONINSERT INTO Borrow(RID, BookID, SRDate,BDate)VALUES(#RID, #BookID,#SRDate, #BDate);UPDATE BOOK SET State=2 WHERE BookID=#BookIDCOMMIT;9） 学生归还图书：BEGIN TRANSACTIONINSERT INTO History(RID,BookID, RDate,BDate)VALUES(#RID, #BookID, #RDate,# BDate);UPDATE BOOK SET State=0 WHERE BookID=#BookIDCOMMIT;(说明：要知道图书当前的状态，是图书馆存放，还是被借阅等，需要再Book模式中增加对应项用以表示图书当前的状态。比如我们增加State,并且约定取值和状态的对应关系如下：0：在图书馆中并且没有被预定1：在图书馆中并且已被预定2：被借出并且没有被预定3：被借出并且已被预定)4.1.5读者操作/*预约管理图书（存储过程触发器实现）*/Create proc Appiontment_BookBookID varchar(20),RID char(6),BDate datetime,SRDate datetimeAsDECLARE TransName VARCHAR(20)SELECT TransName=Appiontment_BookBEGIN TRANSACTION TransNameDECLARE Appiontmented int,book_state_before char(1),book_state_after char(1),Applimit nchar(10)SELECT Appiontmented =count(*) from Borrow WHERE BookID=BookIDIF Appiontmented0 ROLLBACK TRANSACTION TransNameELSE BEGINSELECT book_state_before=state FROM Book WHERE BookID=BookIDI