数据库第五次实验

南昌航空大学实验报告

2018年6月1 日

课程名称：数据库原理实验名称：数据库应用系统设计与开发

学号： 16206127 姓名：周良

指导教师评定：签名：

一、实验目的：

1.通过实验进一步学习和掌握数据库原理的基础知识和理论。

2. 学习SQL Server数据库管理系统的基本功能和建立数据库各种对象的方法。

3. 熟练掌握和使用SQL语言操纵数据库。

4. 理解和掌握利用数据库所学的理论知识来设计数据库的基本方法和步骤。

5. 通过上机操作和调试程序，提高学生理论联系实际和动手操作能力。

6.通过完成从用户需求分析、数据库设计到上机编程、调试和应用等全过程，进一步理解和掌握教材中的相关内容。

二、实验内容及步骤：

一个简单的图书管理系统包括图书馆内书籍信息、学校在校学生的信息以及学生的借阅信息。此系统功能分为面向学生和面向管理员两部分，其中面向学生部分可以进行查询和续借书籍等操作，面向管理员部分可以完成书籍和学生的增加、删除和修改以及对学生借阅、归还的确认。

1、需求分析：

学生操作流程如图：

登录

预定书籍

注销续借书籍

查询书籍

管理员

管理员可以完成书籍和学生的增加、删除和修改以及对学生借阅、续借、

归还的确认，其操作流程如图：

登录

书籍信息维护学生信息维护借阅图书确认归还图书确认

注销

2、概念模型设计：

数据库需要表达的信息有以下几种：

图书信息其数据项有图书编号、图书名称、作者、出版社等

学生信息其数据项有学生学号、学生姓名、电话等

管理员信息

图书借阅登记，其数据项有借阅序号、借阅图书编号、借阅人编号等。

为了实现图书信息录入的方便性与规范性以及相关的统计功能，还应

增加出版社信息与图书分类信息：

出版社信息，其数据项有出版社编号、出版社名称、地址、电话、传

真等。

图书分类信息，其数据项有分类编号、分类名称、同一类型图书数目。

同时针对于本系统的多用户使用特点，增加用户信息表：

用户信息表，其数据项有用户名、密码、用户权限

为了实现图书借阅超期罚款制度，还应增设罚金规则表：

罚金规则表，其数据项包括免费使用天数、罚金费率、规则启用开关。

学生借阅归还图书信息

模型的E--R图如图示

3、逻辑设计：

通过ER 模型到关系模型的转化，可以得到如下关系模式：

姓名

学号

性别

学生

预订

续借

借阅 归还

图书

作者

语种

出版社

出版年

管理员

编号 姓名

书号

书名

Book（BookID，Title，Author，Publisher，Pyear，Language）

Student（ID，Name，Dept）

Assistent（ID,Name）

Bbook(BookID,StdID,BDate)

Rbook(BookID,StdID,RDate)

Lend(StdID,AstID,BookID,LDate)

Return(SdID,AstID,BookID,RDate)

说明

（1）书号是图书的键码，每本书有唯一的书号，一个学生可同时借阅多本书，一个管理员可处理多个同学的借阅等事宜。

(2)一般情况下，学生、管理员和图书之间的联系为1:1:n,借书关系Lend作为连接关系，其键码为n端实体集的键码，即书号作为借书关系的键码。这反映了如果还书时也把当出的借书记录删除，则书号就能唯一识别一个元组。

如果还书时不同时删除借书记录，则意味着同一本书前后可借给不同的学生于是学生、管理员和图书之间的联系变为m：1：n，这时借书关系的键码为书号和学生的组合。

如果在不删除借书记录的情况下，同一学生再次借同一本书，这时，学生、管理员和图书之间的联系变为m：p：n，于是，借书关系键码为书号、学号和管理员号的组合。但其中包含隐含信息，即同一学生前后两次借同一本书所遇到的管理员不同，而这种不同可能仅仅是“日期”不同。因此，借书日期成了必不可少的成分，也就是说，在这种情况下，属性全集才是借书关系的键码。

（3）要知道图书当前的状态，是在图书馆存放还是被借阅等，需要在Book的模式中增加对应项用以表示图书当前状态。比如我们增加state，并约定取值和状态的对应关系如下：

?在图书馆中并且没有被预订

?在图书馆中并且已被预订

?被借出并且没有被预订

?被借出并且已被预订

4、物理设计

为了提高在表中搜索远元祖的速度，在实际实现的时候应该基于键码建立索引。下面是各表中建立索引的表项：

Book(bookID)

Student(ID)

三、数据库表的建立及操作

1、表的建立

1)建立book表

CREATE TABLE Book（

BookID varchar(20)

PRIMARY KEY,

Tille varchar(50)

NOT NULL,

Author varchar(50), Publisher varchar(50), Pyear char(4),

Language char(1),

DEFAULT’c’,

State char(1)

DEFAULT’0’

);

2)建立student表

CREATE TABLE Student(

ID char(6)

PRIMARY KEY,

Name varchar(20)

NOT NULL,

Dept varchar(20)

NOT NULL

);

3）、建立assistent表

CREATE TABLE assistent(

ID char(6)

PRIMARY KEY,

Name varchar(20)

NOT NULL,

);

4）、建立BBook表

CREATE TABLE BBook（

BID varchar(20)

NOT NULL,

StdID char(6)

NOT NULL,

Bdate datetime

NOT NULL, CONSTRAINT FK_BBOOK_BID

FOREIGN KEY(BID) REFERENCES Book(BookID), CONSTRAINT FK_BBOOK_StdBID

FOREIGN KEY(StdBID) REFERENCES Student(ID),

);

5）、建立RBook表

CREATE TABLE RBook(

BookID varchar(20)

NOT NULL,

StdID char(6)

NOT NULL,

RDate datetime

NOT NULL,

CONSTRAINT FK_RBOOK_BookID

FOREIGN KEY(BookID)

REFERENCES Book(BookID),

CONSTRAINT FK_RBOOK_StdBID

FOREIGN KEY(StdID)

REFERENCES Student(ID) );

6）、建立lend表

CREATE TABLE Lend(

StdID char(6)

NOT NULL,

AstID char(6)

NOT NULL,

BookID varchar(20)

NOT NULL,

LDate datetime

NOT NULL,

CONSTRAINT FK_LEND_StdID

FOREIGN KEY(StdID)

REFERENCES Student(ID),

CONSTRAINT FK_LEND_AstID

FOREIGN KEY(AstID)

REFERENCES Assistent(ID)

CONSTRAINT FK_LEND_BookID

FOREIGN KEY(BookID)

REFERENCES Book(BookID) );

7）、建立Return表

CREATE TABLE return(

StdID char(6)

NOT NULL,

AstID char(6)

NOT NULL,

BookID varchar(20)

NOT NULL,

LDate datetime

NOT NULL,

CONSTRAINT FK_RETURN_StdID

FOREIGN KEY(StdID)

REFERENCES Student(ID),

CONSTRAINT FK_RETURN_AstID

FOREIGN KEY(AstID)

REFERENCES Assistent(ID)

CONSTRAINT FK_RETURN_BookID

FOREIGN KEY(BookID)

REFERENCES Book(BookID) );

2、管理员操作

①增加学生：

INSERT INTO Student(ID,Name,Dept)

VALUES(#StdNo,#Name,#Dept);

②删除学生；

DELETE FROM Student

Where(ID=#ID);

③修改学生信息；

UPDATE Student

SET Name=#Name，Dept=#Dept

WHERE(ID=#ID);

④增加书籍：

INSERT INTO Book(bookID,title,author,publisher,pyear,language) VALUES(#bookID,#title,#author,#publisher,#pyear,#language)

⑤删除书籍：

DELETE PROM Book

WHERE(Book=#IDbook)

⑥修改书籍信息

UPDATE BOOK

WHERE(Book=#BookID)；

⑦学生借阅图书；

BEGIN TRANSACTION

INSRET INTO Lend（StdID,AstID,BookID,LDate）

VALUES(#StdID,#AstID,#BookID,#LDate);

UPDARE BOOK

SET STATE=’2’

WHERE BOOKID=#BOOKID

COMMIT;

⑧学生归还图书；

BEGIN TRANSACTION

INSRET INTO Return (StdID,AstID,BookID,LDate）

VALUES(#StdID,#AstID,#BookID,#LDate);

UPDARE BOOK

SET STATE=’0’

WHERE BOOKID=#BOOKID

COMMIT;

3、学生操作

预定图书：

Create proc book_book

@bookID varchar(20),

@stdID char(6),

@BDate datetime

AS

DECLARE @transname varchar(20)

Select @transname='book__book'

Begin transaction@transname

DECLARE@book int,@book_state_beforechar (1),@book,@book_state_after char(1)

Select @booked =count(*)from bbook where BID=@bookID

IF @booked>0

ROOLBACK TRANSACTION @TRANSNAMO

ELSE BEGIN

Select @book_state_before =state from book where bookID=@bookID

If @book_state_before='0'

Select @book_state_after ='1'

ELSE IF @book_state_betore ='2'

Select @book_state_after ='3'

Update book set state =@book_state_after where bookID Insert into BBook (bid,stdid ,bdate)

Values(@bookID,@stdid,@bdate)

Commit transaction @transname

END

GO

学生预定图书，假设图书已经憋预定了，则不允许继续预定。否则的话应根据图书是在馆内还是被借出去两种情况，修改图书当前的状态。最后在预定表中插入一条记录。修改记录和插入新纪录应该都发生或都不发生，所以将这个动作封装成一个事务，保证这个操作的原子性。

②续借图书：

Create proc renew_book

@bookID varchar(20),

@stdID char(6),

@BDate datetime

AS

DECLARE @transname varchar(20)

Select @transname='renew__book'

Begin transaction@transname

DECLARE@book int

Select @booked =count(*)from bbook where BID=@bookID

IF @booked=0

Insert into RBook (bookid,stdid ,bdate)

Commit transaction @transname

Values(@bookID,@stdid,@bdate)

Commit transaction @transname

GO

学生续借图书时，假定图书已经被预定了，则不允许继续续借。否则，在续借记录中插入一条记录就可以了。把这个动作封装成一个存储过程是为了使用方便明了。

四、实验总结

通过本次实了从用户需求分析，数据库设计到上机编程等过

程进一步理解和掌握了数据库的各方面的知识，通过上机操作和

调试程序，提高理论联系实际和动手实践操作能力。掌握了sQL

Server数据库管理系统的基本功能和建立数据库各种对象的方

法，通过此次上机知道了自己的不足并加以学习和改正，让知识

更加融汇与实践相结合。