《数据库原理及应用》课程设计报告Word文档格式.docx

1、 存放位置 State Not null 当前状态表1-2Reader读者信息表格RID读者借书证号Name读者姓名Tel读者电话Email读者邮箱BNumberInt可借书数表1-3 Manager管理员信息表格MID工号姓名RoomID馆室号表1-4CIP书目信息表VarcharNot null（主键）booknameNot null书籍名称AuthorNull书籍作者PyearChar出版年月Publisher出版者Number册数表1-5Borrow 借阅记录信息表Not null（外主键）读者借阅证编号书籍编号BDateDatetime读者借书时间SRDate Datetime 应还

2、日期 Varcher Not null（外键） 索书号表1-6History 借阅历史信息表RDatedatetime读者还书时间 BDate Notnull读者借书日期表1-7fine 罚款记录信息表读者借书证编号ClStatenull是否缴纳罚金Fine罚款金额Not Null借阅时间 MID 工号 RDate 还书日期表1-8room 馆室信息表馆室编号RoomName馆室名表1-9 Appiontmnet 预约信息表读者编号借阅日期Not null（外键）Applimit预约时间限制 Null 归还日期二、概念结构设计可以用E/R模型表述该模型的设计，E/R图如图所示。三、逻辑结构设计

3、通过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、 借阅历史：Hi

4、story（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端对应的关系模式合并

5、。如果转换为一个独立的关系模式，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为n端实体的码。 一个1：1联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。三个或三个以上实体间的一个多元联系可以转换为一个关系模式。与该多元联系相连的各实体的码以及联系本身的属性均转换为关系的属性，而关系的码为各实体码的组合 具有相同码的关系模式可合并。由于图书与CIP、资料室与图书的联系方式是1:n（一对多），可以将其之间的联系与n端实体CIP、图书合并，管理员与图书之间的维护联系也是1:n（一对多），同样也将其之间的联系与n端实体合并，而读者与图书之间的借阅和归

6、还联系方式则是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

7、，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，但是借阅关系模式、借阅历史关系模式、罚款关系

8、模式及预约信息模式（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

9、、 建立Reader表CREATE TABLE ReaderRID 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 CIPISBN varchar（10） PRIMARY KEY， BookName varchar（20） NOT NULLAuthor varchar（20） Publisher varc

10、har（50）， Pyear char（8）Number int check（Number=0）4、 建立Manager表CREATE TABLE ManagerMID varchar（8） PRIMARY KEY，RoomID varchar（18）CONSTRAINT FK_Manager_RoomIDFOREIGN KEY（RoomID） REFERENCES RoomID（RoomID）,5、建立Room表CREATE TABLE RoomRoomID varchar（8） PRIMARY KEY，RoomName varchar（18） NOT NULL，6、 建立Borrow表CR

11、EATE TABLE BorrowBookID 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

12、（ISBN）,CONSTRAINT FK_ Borrow_RID FOREIGN KEY（RID） REFERENCES Reader（RID）7、 建立History表CREATE TABLE HistoryBookID varchar（20）NOT NULL，RDate datetime NOT NULL,CONSTRAINT FK_History_BookID CONSTRAINT FK_History_RID 8、 建立Fine表CREATE TABLE FineRID varchar（20） NOT NULL,MID varchar（8） NOT NULL,BookID varcha

13、r（20）NOT NULL,BDate datetime NOT NULL,Fine varchar（20） CLState char（8）,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 Ap

14、piontmnetRID 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 CONSTRAINT FK_ RETURN _ ISBN FOREI

15、GN 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 （读者姓名,编号,电话,邮箱）select Name,RID,Tel,Em

16、ailfrom Reader （3）用于显示当前借阅基本信息的视图定义如下：create view Borrowview （读者编号,书名,作者,借阅日期,到期日期）select RID,BookName,Author,BDate,SRDatefrom Borrow,CIPwhere Borrow.ISBN=CIP.ISBN （4）用于借阅历史信息查询的视图定义如下：create view Historyview （读者编号,书名,借阅日期,应还日期）select Borrow.RID,BookName,BDate,SRDatefrom Borrow,CIP,Readerwhere Borro

17、w.RID=Reader.RID（5）用于查询罚款信息的视图定义如下：create view Fineview （读者编号,书名,借阅日期,归还日期,罚款,处理状态）select 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,App

18、iontment.ISBN ,BDate,ApplimitFrom Appiontment,CIPWhere Appiontment.ISBN=CIP.ISBN and CIP.Number is null /*当某书目可借书数量为零时*/4.1.3建立触发器本系统创建了三个触发器，它们分别实现删除图书、统计图书可借数量、借阅图书信息的功能。其代码如下。1创建删除图书的触发器create trigger deletebookon Book instead of deletedelete from Borrow where RID in （select RID from deleted）dele

19、te from Book where id in （select BookID from deleted）2创建统计图书可借数量的触发器create trigger Readercountable on Borrow after insertupdate Reader set BNumber= BNumber -1 where RID in （select RID from inserted）-借书后用户的可借数量减13创建借阅图书信息功能的触发器/*借书管理，当在中增加一条借阅记录时，使该图书的状态由“未借”变为“已借出*/create trigger Borrow_insert1on Bo

20、rrowfor insertas declare BookID char（9）select BookID=BookIDfrom insertedupdate Bookset State=已/* 已借出*/where BookID=BookID/*验证*/insert into Borrow values （07,048062012-2-42012-4-4）4还书管理 /*还书管理（举实例论证,预约功能在上一触发器中实现）*/BEGIN TRANSACTIONINSERT INTO History（BookID,RID,BDate,RDate）VALUES（11022012-4-62012-4-

21、8UPDATE BOOK SET State=0 WHERE BookID=COMMIT;5自动计算罚金公式/*若超过时间限制，计算罚金公式*/*1.把没归还日期的归还日期改为系统时间*/UPDATE History SET RDate=getdate（） where RDate is nulll/*2.假设有个可借天数字段天*/update Fine set fine=（datediff（day,2012-3-12012-6-3）-60）*0.2/*没有可借天数的话把-可借天数以及外面的一对括号删除*/4.1.4管理员操作1） 入库管理：/*入库管理，新书入库*/insert into CI

22、P（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

23、=# 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 =

24、#Pyear, Number =#Number WHERE（BookID=#BookID）;8） 学生借阅图书：INSERT INTO Borrow（RID, BookID, SRDate,BDate）VALUES（#RID, #BookID,#SRDate, #BDate）;UPDATE BOOK SET State=2 WHERE BookID=#BookID9） 学生归还图书：INSERT INTO History（RID,BookID, RDate,BDate）VALUES（#RID, #BookID, #RDate,# BDate）;UPDATE BOOK SET State=0 WHERE BookID=#BookID（说明：要知道图书当前的状态，是图书馆存放，还是被借阅等，需要再Book模式中增加对应项用以表示图书当前的状态。比如我们增加State,并且约定取值和状态的对应关系如下：0：在图书馆中并且没有被