数据库原理与应用大作业Word格式文档下载.docx
- 文档编号:18896694
- 上传时间:2023-01-02
- 格式:DOCX
- 页数:17
- 大小:123.70KB
数据库原理与应用大作业Word格式文档下载.docx
《数据库原理与应用大作业Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《数据库原理与应用大作业Word格式文档下载.docx(17页珍藏版)》请在冰豆网上搜索。
1.2需求分析
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,教师工号,办理人,催还日期),依据,M:
4)在已经形成的关系模型下,举例说明连接运算、投影运算、选择运算。
连接运算:
查看类型的图书已经超过了有效借出期限。
(需要图书,类型,借还关系)
投影运算:
查看图书的书名、作者。
选择运算:
查看单价高于50元的图书。
第三部分作业2——用SQL语句建库、查询、更新数据、创建视图
(满分9分)
在关系模型的基础上,设计出数据库的逻辑结构,然后在SQLServer中用SQL语句创建数据库、数据表、并输入一些原始数据,写SQL语句,进行数据增删改,创建视图。
1)在第一次大作业的参考答案基础上,将每个关系转换为SQLServer支持的表结构,写出表结构。
2)使用SQL语句,创建数据库,数据库名为BookStore
3)使用SQL语句,在数据库中创建表,设定表的主键约束、外键约束、检查约束、非空约束。
4)写以下增删改查操作的SQL语句
1、增加三条类型表数据
2、增加三条教师表数据
3、增加五条图书表数据
4、某教师今天借了某书,涉及两个操作,一是图书表的状态改为“借出”,二是增加一个借还表记录
5、将某书的单价改为32元。
5)写出创建以下视图的SQL语句
1.查询某教师的借还书记录
2.查询某本书的流转记录
3.借出但未归还的图书视图
4.借阅逾期视图
5.图书分类统计册数和总价值的视图
参照完成:
1)表结构
管理员表
列名
类型
约束
用户名
Char(10)
pk
性别
Char
(2)
NOTNULL,且默认为男
口令
Nchar(20)
NOTNULL
类型表
类型名
NChar(10)
借阅期限
tinyint
图书表
ISBN
Char(20)
书名
作者
Nchar(20)
出版社
出版时间
DateTime
单价
money
Nchar(10)
FKreferences类型表.图书类型
状态
Nchar
(2)
状态in(库存,借出)
部门表
部门名
电话
char(11)
教师表
工号
Char(4)
姓名
Nchar(4)
Char(30)
Fkreferences部门表.部门名
借还记录表
业务编号
int
Pk自动标示
char(20)
Fkreferences图书表.isbn
Fkreferences教师表.工号
借办理人
借出日期
datetime
还办理人
归还日期
催还记录表
办理人
催还日期
1)使用SQL语句,创建数据库,数据库名为BookStore
语句:
CreateDatabseBookstore
2)使用SQL语句,在数据库中创建表,设定表的主键约束、外键约束、检查约束、非空约束。
CREATETABLE管理员表(
管理员名nchar(10)NOTNULL,
性别Char
(2)notnullDEFAULT'
男'
口令nchar(20)NOTNULL,
CONSTRAINTPK_管理员PRIMARYKEYCLUSTERED(管理员名ASC)
)ON[PRIMARY]
GO
CREATETABLE类型表(
类型名nchar(10)NOTNULL,
借阅期限tinyintNOTNULL,
CONSTRAINTPK_类型PRIMARYKEYCLUSTERED(类型名ASC)
CREATETABLE图书表(
ISBNchar(20)NOTNULL,
书名nchar(20)NOTNULL,
作者nchar(20)NOTNULL,
出版社nchar(20)NOTNULL,
出版时间datetimeNOTNULL,
单价moneyNOTNULL,
类型名nchar(10)NOTNULL,
状态nchar
(2)NOTNULLCHECK(状态in('
库存'
'
借出'
)),
CONSTRAINTPK_图书PRIMARYKEYCLUSTERED(ISBNASC),
CONSTRAINTFK_图书_类型FOREIGNKEY(类型名)REFERENCES类型表(类型名)
ONUPDATECASCADEONDELETECASCADE
CREATETABLE部门表(
部门名nchar(10)NOTNULL,
电话char(11),
CONSTRAINTPK_部门表PRIMARYKEYCLUSTERED(部门名ASC)
)ON[PRIMARY]
CREATETABLE教师表(
工号char(4)NOTNULL,
姓名nchar(4)NOTNULL,
性别Char
(2)notnullDEFAULT'
Emailchar(30)NOTNULL,
CONSTRAINTPK_教师表PRIMARYKEYCLUSTERED(工号ASC),
CONSTRAINTFK_部门表_教师表FOREIGNKEY(部门名)REFERENCES部门表(部门名)
ONUPDATECASCADEONDELETECASCADE,
CREATETABLE借还记录表(
业务编号intidentity(0,1)NOTNULL,
代办理人char(10)NOTNULL,
借出日期datetimeNOTNULL,
还办理人char(10)NULL,
归还日期datetimeNULL,
CONSTRAINTPK_借还记录表PRIMARYKEYCLUSTERED(业务编号ASC),
CONSTRAINTFK_借还记录表_教师表FOREIGNKEY(工号)REFERENCES教师表(工号)
CONSTRAINTFK_借还记录表_借还记录表FOREIGNKEY(ISBN)REFERENCES图书表(ISBN)
CREATETABLE催还记录表(
业务编号intidentity(0,1)NOTNULL,
办理人char(10)NOTNULL,
催还日期datetime,
CONSTRAINTPK_催还记录表PRIMARYKEYCLUSTERED(业务编号ASC),
CONSTRAINTFK_催还记录表_教师表FOREIGNKEY(工号)REFERENCES教师表(工号)
CONSTRAINTFK_催还记录表_图书FOREIGNKEY(ISBN)REFERENCES图书表(ISBN)
3)写以下增删改查操作的SQL语句
1.增加三条类型表数据
INSERTINTO类型表(类型名,借阅期限)VALUES('
计算机'
150)
INSERTINTO类型表(类型名,借阅期限)VALUES('
文学'
180)
经济'
30)
--delete类型表
--select*from类型表
2.增加三条部门表数据
INSERTINTO部门表(部门名,电话)VALUES('
网络系'
020-8188483'
)
外语系'
020-8188482'
管理系'
020-8188481'
--select*from部门表
3.增加三条教师表数据
INSERTINTO教师表(工号,姓名,性别,Email,部门名)VALUES('
J001'
张三'
女'
**********'
INSERTINTO教师表(工号,姓名,性别,Email,部门名)VALUES('
J002'
李四'
J003'
王五'
--select*from教师表
4.增加五条图书表数据
INSERTINTO图书表(ISBN,书名,作者,出版社,出版时间,单价,类型名,状态)VALUES('
9789302218661'
C#'
江红'
清华大学出版社'
2012.2.10'
45,'
9787802229761'
人生博弈密码'
吕叔春'
中国华侨出版社'
2009.2.1'
36.8,'
9787301150894'
经济学原理'
梁小民'
北京大学出版社'
2009.4.1'
54,'
--select*from图书表
5.增加三条管理员表数据
INSERTINTO管理员表(管理员名,性别,口令)VALUES('
吴管'
wg001'
李管'
Lg002'
徐管'
Xg003'
--select*from管理员表
6.某教师今天借了某书,涉及两个操作,一是图书表的状态改为“借出”,二是增加一个借还表记录
insert借还记录表(ISBN,工号,代办理人,借出日期,还办理人,归还日期)values('
2012.3.20'
'
--select*from借还记录表
--delete借还记录表
update图书表
set状态='
whereISBN='
7.将某书的单价改为32元。
set单价=32
8.查询某教师的借还书记录
selecta.工号,姓名,部门名,书名
from教师表ajoin借还记录表cona.工号=c.工号
join图书表bonc.ISBN=b.ISBN
wherea.工号='
go
9.查询某本书的流转记录
selecta.工号,姓名,部门名,书名,借出日期,归还日期
from教师表a,图书表b,借还记录表c
wherea.工号=c.工号andb.ISBN=c.ISBNandb.ISBN='
4)写出创建以下视图的SQL语句
1.借出图书视图
Createview借出图书视图
As
select*from图书表where状态='
2.借阅逾期视图
CreateView借阅逾期视图
SELECTa.ISBN,a.书名,b.工号,b.借出日期
FROM图书表aJOIN借还记录表bONa.ISBN=b.ISBN
JOIN类型表CONa.类型名=c.类型名
WHEREa.状态='
anddatediff(day,b.借出日期,getdate())>
c.借阅期限
--select*from借阅逾期视图
3.图书分类统计册数视图
CreateView图书统计视图
SELECT类型名,count(*)各类图书册数,sum(单价)各类图书总价
FROM图书表
GROUPBy类型名
--select*from图书统计视图
第四部分作业3——存储过程与触发器设计(满分8分)
1)为BookStore数据库增加一个用户oper1,该用户可以进行系统所需的应用操作,权限尽可能精简。
请描述该用户的权限。
2)设计存储过程
a)数据初始化过程sp_Init。
包含的操作有:
所有的用于测试图书信息、教师信息、历史记录清空,输入图书分类、输入管理员信息、输入借书规定。
b)某教师在某时间借了某本书,由某管理员办理。
c)某教师在某时间还了某本书,由某管理员办理
d)查看某人的全部借还书记录
e)查看某本书的历史流转记录
f)某管理员在某时间给全部借阅过期者发催还邮件。
g)对书名进行模糊搜索(搜索条件可以是多个模糊列值的组合,例如:
搜索书名包含“网络”两个字,并且是近3年出版的图书)
3)设计触发器
a)拒绝超级用户sa对所有的数据的修改,凡是SA进行的修改全部回退。
b)Oper1用户对图书状态进行修改时,在历史记录表中追加相应的数据。
usebookstore
execsp_addloginoper1,'
123456'
bookstore'
--sl,'
:
依次为用户名,密码,默认数据库
EXECsp_addrolemember'
db_datareader'
'
oper1'
—授权oper1用户对所有表具有查询权
GRANTUPDATEON图书表TOoper1--允许用户oper1对数据表直接更新
a)略
b)createprocproc_lendbook
@jsIDchar(4),
@timedatetime,
@shuISBNchar(20),
@glynchar(20)
as
selecta.姓名,借出日期,d.书名,b.管理员名
from教师表ajoin借还记录表cona.工号=c.工号
join管理员表bonb.口令=c.代办理人
join图书表donc.ISBN=d.ISBN
wherea.工号=@jsIDandc.借出日期=@timeandc.ISBN=@shuISBN
andc.代办理人=@gly
EXECproc_lendbook'
2012-3-20'
WG001'
c)略
d)略
e)略
f)略
Createprocgetbook
@tsnamenchar(20),
@nxtinyint
Select*from图书表
Where图书名LIKEC
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 原理 应用 作业