数据库大作业全参考答案Word文档下载推荐.docx
《数据库大作业全参考答案Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《数据库大作业全参考答案Word文档下载推荐.docx（21页珍藏版）》请在冰点文库上搜索。
我们想知道哪些图书最受欢迎。
阅览室是你一个人在管么
阅览室有两个人。
我们俩都有一台计算机，最好两台计算机都可以用上这个软件。
需求分析
1）功能需求
图1-1：
功能需求示意图
教师信息管理：
用于教师基本资料的增删改查。
图书信息管理：
用于图书基本信息的增删改查，分类统计图书册数和价值。
借书登记：
记录借书时间、所借图书、借书人、办理人。
还书登记：
记录还书时间、所还图书、还书人、办理人。
催还：
查询借阅逾期的借书信息，给借书人发电子邮件，给借书人的部门打电话。
2）运行环境要求
图1-2：
运行环境拓扑图
系统采用C/S模式，有两台PC和一台服务器，联成一个局域网。
PC上安装图书管理软件的客户端，服务器上安装DBMS，服务器也可由两台PC中的一台来代替。
第二部分作业1——E-R模型与关系模型设计
（满分8分）
本部分的任务是：
在需求分析的基础上，进行E-R图设计，然后将E-R模型转换为关系模型。
任务：
1）根据需求描述，绘制E-R图。
2）将E-R图转换成关系模型，写出所有的关系模式，并写出每一个关系是按照什么规则转换而成的。
3）在已经形成的关系模型下，举例说明连接运算、投影运算、选择运算。
参考答案：
（属性写在了实体和联系图形内）
1）根据需求描述，分别为“图书管理系统”的不同功能模块绘制局部E-R图。
图书基本信息管理：
借还书登记：
催还书登记：
2）整体E-R图
3）关系模式
类型表（类型名，借阅期限），依据：
实体转换为关系。
图书表（ISBN，书名，作者，出版社，出版时间，单价，类型名，状态），
依据：
实体转换为关系，1：
N的联系合并到N，在N方增加一个外键:
类型名。
注：
状态属性为（库存，借出）
管理员表（管理员名，性别，口令），依据：
教师表（教师工号，姓名,性别，Email，部门名），
部门名。
部门表（部门名，电话），依据：
借还表（借还业务编号，ISBN，教师工号，借办理人，借出日期，还办理人，归还日期），
M:
N联系转换为一个关系，取联系的实体的键加联系属性作为其属性，可以增加一个
列作为主键。
催还表（催还业务编号，ISBN，教师工号，办理人，催还日期），
4）在已经形成的关系模型下，举例说明连接运算、投影运算、选择运算。
连接运算：
查看类型的图书已经超过了有效借出期限。
（需要图书，类型，借还关系）
投影运算：
查看图书的书名、作者。
选择运算：
查看单价高于50元的图书。
3．作业2——用SQL语句建库建表、增删改查数据、创建视图
1）根据关系规范化的要求，在第一部分得到的图书管理系统关系模式需要进行规范化吗如果需要，请写出写出符合3NF的关系模式。
2）在得到3NF的图书管理系统关系模式的基础上，将每个关系转换为SQLServer支持的表结构，用表格形式表示出表结构。
类型表
列名
类型
约束
类型名
NChar（10）
Pk
借阅期限
Tinyint
Notnull
图书表
ISBN
Char（20）
书名
Nchar（10）
作者
Nchar（4）
出版社
出版时间
Datetime
单价
Money
FKreferences类型表（类型名）
状态
NChar
（2）
检查约束
管理员表
管理员名
性别
Nchar
（1）
口令
部门表
部门名
PK
电话
char（11）
教师表
教师工号
Char（10）
姓名
Email
char（20）
FKreferences部门表（部门名）
借还表
借还业务编号
Int
FKreferences图书表（ISBN）
Fkreferences教师表（教师工号）
借办代理人
借出日期
还办代理人
归还日期
催还表
催还业务编号
FKreferences图书表（ISBN）
FKreferences教师表（教师工号）
办代理人
null
催还日期
使用SQL语句，创建数据库，数据库名为BookStore，在数据库中创建表，设定表的主键约束、外键约束、检查约束、默认约束、非空约束、唯一约束。
写出SQL。
createdatabaseBookStore
createtable类型表
（类型名nchar（10）notnullprimarykey,
借阅期限tinyintnotnull
）
createtable图书表
（ISBNchar（20）primarykey,
书名nchar（10）notnull,
作者nchar（4）notnull,
出版社nchar（10）notnull,
出版时间datetime,
单价moneynotnull,
类型名nchar（10）notnullforeignkeyreferences类型表（类型名）ondeletenoactiononupdatecascade,
状态nchar
（2）notnullcheck（状态='
库存'
or状态='
借出'
createtable管理员表
（管理员名nchar（4）primarykey,
性别nchar
（1）check（性别='
男'
or性别='
女'
）,
口令nchar（10）notnull
createtable部门表
（部门名nchar（10）primarykey,
电话char（11）notnull
createtable教师表
（教师工号char（10）primarykey,
姓名nchar（4）notnull,
Emailchar（20）notnull,
部门名nchar（10）notnullforeignkeyreferences部门表（部门名）ondeletenoactiononupdatecascade
createtable借还表
（借还业务编号intprimarykey,
ISBNchar（20）foreignkeyreferences图书表（ISBN）ondeletenoactiononupdatecascade,
教师工号char（10）foreignkeyreferences教师表（教师工号）ondeletenoactiononupdatecascade,
借办代理人nchar（4）notnull,
借出日期datetimenotnull,
还办代理人nchar（4）null,
归还日期datetimenull
createtable催还表
（催还业务编号intprimarykeyidentity（1,1）,
办代理人nchar（4）null,
催还日期datetimenull
写以下增删改查操作的SQL语句
1、输入图书类型数据
insertinto类型表（类型名,借阅期限）
values（'
文学'
15）,
（'
教学参考书'
180）,
（'
音乐'
45）
2、增加三个教师信息
insertinto部门表（部门名,电话）
基础部'
'
139********'
电子系'
136********'
音乐艺术学院'
186********'
insertinto教师表（教师工号,姓名,性别,Email,部门名）
0001'
张三'
'
0002'
李四'
0003'
王五'
3、增加五本书的信息
insertinto图书表（ISBN,书名,作者,出版社,出版时间,单价,类型名,状态）
54109'
夜航船'
'
张岱'
浙江文艺出版社'
,文学'
略
4、某教师今天借了某书，涉及两个操作，一是将书的状态改为“借出”，二是增加一个借书记录
update图书表
set状态='
where书名='
insertinto借还表（借还业务编号,ISBN,教师工号,借办代理人,借出日期）
3123'
5、将某书的作者改为自己的姓名
set作者='
***'
6、查询某教师的借还书记录
select*
from借还表
where教师工号='
7、查询某本书的流转记录
whereISBN='
创建视图，写出SQL语句
8、借出图书视图
createview借出图书
as
from图书表
where状态='
9、借阅逾期视图
createview借阅逾期
select借还表.借办代理人,借还表.教师工号，图书表.书名
from图书表innerjoin借还表on图书表.ISBN=借还表.ISBNinnerjoin类型表on图书表.类型名=类型表.类型名
where图书表.状态='
anddatediff（day,借还表.借出日期,getdate（））>
类型表.借阅期限
10、图书分类统计册数视图
createview分类统计
selectcount（*）as书籍册数,类型名
groupby类型名
4．作业3——存储过程与触发器设计任务
1）为BookStore数据库增加一个用户oper1，允许oper1对图书表和借还表进行修改操作。
useBookStore
go
execsp_addloginoperl,'
（此处为密码）'
BookStore'
execsp_addrolemember'
db_datareader'
operl'
grantupdateon图书表tooperl
grantupdateon借还表tooperl
2）设计存储过程
a）数据初始化过程sp_Init。
包含的操作有：
所有的用于测试图书信息、教师信息、历史记录清空，输入图书分类、输入管理员信息、输入借书规定。
略，界面进行输入或者代码输入作业用数据
b）某教师在某时间借了某本书，由某管理员办理。
createprocedureproc_lendbook
@aidchar（10）,
@atimedatetime,
@ISBNchar（20）,
@anamenchar（4）
begin
whereISBN=@ISBN
insertinto借还表（ISBN,教师工号,借办代理人,借出日期）
values（@ISBN,@aid,@aname,@atime）
end
c）某教师在某时间还了某本书，由某管理员办理
createprocedureproc_returnbook
@bidchar（10）,
@btimedatetime,
@bnamenchar（4）
update借还表
set还办代理人=@bname,归还日期=@btime
where教师工号=@bidandISBN=@ISBN
d）查看某人的全部借还书记录
createprocedureproc_getReaderlog
@cnamenchar（4）,
@cidchar（10）
selectb.姓名,c.书名,a.借出日期,归还日期
from借还表ajoin教师表bona.教师工号=b.教师工号join图书表con=
whereb.姓名=@cnameorb.教师工号=@cid
e）查看某本书的历史流转记录
createprocedureproc_getBooklog
@booknamenchar（10）,
@ISBNchar（20）
select教师表.姓名,图书表.书名,借还表.借出日期,归还日期
where=@ISBNorc.书名=@bookname
f）某管理员在某时间给全部借阅过期者发催还邮件。
部分有错误的需要将修改催还业务编号为自增长（identity（1,1））,还有整个表都要允许空值：
例：
altertable催还表
altercolumn办代理人char（4）null
createprocedureproc_writeEmailLog
@dnamechar（4）,
@dtimedatetime
insertinto催还表（教师工号）
select教师工号
from借还表ajoin图书表bon=join类型表conb.类型名=c.类型名
wheredatediff（day,a.借出日期,getdate（））>
c.借阅期限anda.归还日期isnull
update催还表
set办代理人=@dname,催还日期=@dtime
where催还日期isnull
end
g）对书名进行模糊搜索（搜索条件可以是多个模糊列值的组合，例如：
搜索书名包含“网络”两个字，并且是近3年出版的图书）
createprocedureproc_search
@maxtinyint
wheredatediff（year,出版时间,getdate（））<
@maxand书名like'
%'
+@bookname+'
3）设计触发器
a）当oper1用户对图书状态进行修改时，自动在借还表追加相应的数据。
createtriggertri_a
on图书表afterupdate
declare@statuschar（4）
declare@ISBNchar（20）
select@status=状态frominserted
select@ISBN=ISBNfrominserted
ifuser_name（）='
if@status='
insertinto借还表（ISBN,借办代理人,借出日期）
values（@ISBN,user_name（）,getdate（））
elseif@status='
set还办代理人=user_name（）,归还日期=getdate（）
b）不允许oper1用户对催还表信息进行删除操作，如果删除进行回滚。
createtriggertri_b
on催还表afterdelete
if（selectcount（*）fromdeleted）>
rollback
升级会员 