数据库实验报告二.docx
- 文档编号:5906804
- 上传时间:2023-01-02
- 格式:DOCX
- 页数:18
- 大小:238.69KB
数据库实验报告二.docx
《数据库实验报告二.docx》由会员分享,可在线阅读,更多相关《数据库实验报告二.docx(18页珍藏版)》请在冰豆网上搜索。
数据库实验报告二
《数据库原理》实验报告
实验三:
数据库完整性与安全性控制
实验四:
视图与索引
学号
姓名
班级
日期
2013302534
杨添文
10011303
2015.10.17
实验三:
数据完整性与安全性控制
一、实验内容
1.利用图形用户界面对实验一中所创建的Student库的S表中,增加以下的约束和索引。
(18分,每小题3分)
(1)非空约束:
为出生日期添加非空约束。
(2)主键约束:
将学号(sno)设置为主键,主键名为pk_sno。
(3)唯一约束:
为姓名(sname)添加唯一约束(唯一键),约束名为uk_sname。
(4)缺省约束:
为性别(ssex)添加默认值,其值为“男”。
(5)CHECK约束:
为SC表的成绩(grade)添加CHECK约束,约束名为ck_grade,其检查条件为:
成绩应该在0-100之间。
(6)外键约束:
为SC表添加外键约束,将sno,cno设置为外键,其引用表为分别是S表和C表,外键名称分别为fk_sno,fk_cno。
2.在图形用户界面中删除上小题中已经创建的各种约束,用SQL语言分别重新创建第1小题中的
(2)-(6)小题。
(15分,每小题3分,提示:
altertableaddconstraint)
(2)altertables
addconstraintpk_snoprimarykey(sno)
(3)altertables
addconstraintuk_snameunique(sname)
(4)altertables
addconstraintadefault('男')forssex
(5)altertablesc
addconstraintck_gradecheck(gradebetween0and100)
(6)altertablesc
addconstraintfk_snoforeignkey(sno)referencess(sno)
altertablesc
addconstraintfk_cnoforeignkey(cno)referencesc(cno)
3.利用图形用户界面根据上述SC表中的外键定义画出由S,C,SC表构成的数据库关系图。
(5分,提示:
选中student->数据关系图)
4.用SQL语言删除S表中所创建的缺省约束和外键约束。
(6分,每小题3分)
(1)缺省约束:
altertables
dropconstrainta
(2)外键约束:
altertablesc
dropconstraintfk_sno,fk_cno
5.在图形用户界面中创建新登录名以及用户。
(16分)
(3)根据第四版教材P148页第8题,创建数据库company和其中的基本表,并创建该题中所需要用到的各个用户以及相关的登录名。
(10分)
(4)用图形用户界面完成以下的权限赋予:
(每小题3分)
a)用户王明对两个表均有Select和Insert的权力。
b)用户李勇对职工表具有Select权力,对工资字段具有更新权力。
6.用SQL语句授权和收回权限并对权限加以验证。
(40分,每题4分)
(1)第四版教材P148页第8题b),c),d),f),g)。
(b)grantinsert,delete
on职工
to李勇
grantinsert,delete
on部门
to李勇
(c)grantselect
on职工
topublic
(d)grantselect,update(工资)
on职工
to刘星
(f)grantallprivileges
on职工
to周平
withgrantoption
grantallprivileges
on部门
to周平
withgrantoption
(g)createviewwage(a,b,c,d)
as
select职工.部门号,max(工资),min(工资),avg(工资)
from职工,部门
where职工.部门号=部门.部门号
groupby职工.部门号
grantselect
onwage
to杨兰
(2)第四版教材P148页第9题的b),c),d),f),g)。
(提示:
(c)和(g)小题可创建合适的视图,针对视图进行授权,
(c)小题用CURRENT_USER)
(b)revokedelete,inserton部门
from李勇;
revokedelete,inserton职工
from李勇;
(c)revokeselect
on职工
frompublic
(d)revokeselect,update
on职工
from刘星
(f)revokeallprivileges
on职工
from周平cascade
(g)revokeselectonwage
from杨兰;
dropviewwage;
●选做实验(选做第一题)
1.利用SQL语言创建,验证和删除规则。
(1)创建一个ssex_rule规则(createrule),将其绑定(sp_bindrule)到S表的ssex性别字段上(请验证该规则生效,保证了输入的性别只能是“男”或者“女”)。
1、建立规则ssex_rule:
createrulessex_rule
as@ssexin('男','女')
2、绑定:
exec sp_bindrule 'ssex_rule','S.ssex'
3、验证:
insertinto
s
values('95111','张伟','男人','1994-12-25','CS','3436330')
结果为:
(2)删除ssex_rule规则(注意:
规则已绑定到ssex表的性别字段上,需要先解除原有的绑定sp_unbindrule,droprule)。
1、解除绑定:
exec
sp_unbindrule 's.ssex'
2、删除:
droprulessex_rule
二、实验反思
创建合适的视图,可以令查询简单,效率更高,视图对重构数据库提供了一定程度的逻辑独立性。
实验四:
视图与索引
一、实验内容
1.在Student数据库中,利用图形用户界面,创建一个选修了“数据库原理”课程并且是1996年出生的学生的视图,视图中包括学号,性别,成绩三个信息。
(5分)
2.用两种不同的SQL语句创建第四版教材128页第11题中要求的视图(视图名:
V_SPJ)(10分,每种方法5分)。
第一种方法:
createviewV_SPJ
as
selectSNO,PNO,QTY
fromSPJ,J
whereJ.JNO=SPJ.JNO
andJ.JNAME='三建'
第二种方法:
createviewV_SPJ
as
selectSNO,PNO,QTY
fromSPJ
whereSPJ.JNOin
(
selectJNO
fromJ
whereJ.JNAME='三建'
)
3.用SQL语句完成第四版教材128页第11题中的视图查询(10分,每小题5分)。
(1)
selectPNO,sum(QTY)total
fromV_SPJ
groupbyPNO
(2)select*
fromV_SPJ
whereSNO='S1'
4.用SQL语句完成视图的数据更新。
(15分,每题5分)
(1)给视图V_SPJ中增加一条数据。
1、先建立insteadof触发器insert_spj:
create trigger insert_spj
on V_SPJ
Instead of insert
As
Begin
declare @sno char(10)
declare @pno char(10)
declare @qty int
select @sno=sno,@pno=pno,@qty=qty
from inserted
insert into spj(sno,pno,jno,qty)
values(@sno,@pno,'J1',@qty)
end
2、增加数据:
insert
intoV_SPJ
values('S3','P5',406)
3、结果:
(2)修改视图V_SPJ中的任意一条数据的供应数量。
updateV_SPJ
setQTY=666
wherePNO='P3'andSNO='S2'
(3)删除视图V_SPJ中的任意一条数据(注意所创建视图可以视图消解时,才能正常删除,否则会删除失败;也可以考虑用insteadof触发器实现)。
1、视图连接有多个基表,不能正常删除,建立触发器delete_spj如下:
createtriggerdelete_spj
onV_SPJ
Insteadofdelete
As
Begin
declare@snochar(10)
declare@pnochar(10)
declare@qtyint
select@sno=sno,@pno=pno,@qty=qty
fromdeleted
deletefromSPJ
whereSPJ.sno=@sno
andSPJ.PNO=@PNO
ANDSPJ.JNO='J1'
ANDSPJ.QTY=@QTY;
end
2、删除一条数据:
delete
fromV_SPJ
whereSNO='S3'
andPNO='P1'
andQTY=200
结果如下:
5.用图形用户界面对Student数据库中C表的Cno字段创建一个降序排列的唯一索引,索引名称IX_CNo。
(5分)
6.使用SQL语句对Student数据库完成以下的索引操作。
(15分,每题5分)
(1)在C表的CName属性上创建一个非唯一性的聚簇索引-,索引名IX_CName。
createclusteredindexIX_CName
onc(cname)
(2)在SC表上创建一个名为IX_Cnosno的非聚簇复合索引,该索引是针对sno,cno属性集建立的升序索引。
createnonclusteredindexIX_Cnosno
onsc(snoasc,cnoasc)
(3)删除C表的索引IX_CName。
dropindexc.IX_CName
7.自己设计一个实验验证索引对数据库查询效率的提升作用。
(40分)
(提示:
需要数据量比较大的情况下才容易进行对比)
1、创建表:
CREATETABLE[dbo].[Article](
[Id][int]IDENTITY(1,1)NOTFORREPLICATIONNOTNULL,
[MsId][int]NOTNULL,
[Title][nvarchar](96)NOTNULL,
[TitleBak][nvarchar](96)NOTNULL,
[Summary][nvarchar](512)NOTNULL,
[SummaryImageUrl][nvarchar](256)NOTNULL,
[Tag][nvarchar](50)NOTNULL,
[ArticleChannel_Id][int]NOTNULL,
[ArticleCategory_Id][int]NOTNULL,
[IsApproved][bit]NOTNULL,
[Creator_Id][int]NOTNULL,
[CreatedDateTime][datetime]NOTNULL,
[ModifiedDateTime][datetime]NOTNULL,
[ViewCount][int]NOTNULL,
[ReplyCount][int]NOTNULL,
[DiggCount][int]NOTNULL,
[FavoriteCount][int]NOTNULL,
[LastReplyUser_Id][int]NOTNULL,
[LastReplyDateTime][datetime]NOTNULL,
[RightType][int]NOTNULL,
[IsDisplayContent][bit]NOTNULL,
[IsSensitive][bit]NOTNULL,
[Source][int]NOTNULL,
CONSTRAINT[PK_Articles]PRIMARYKEYCLUSTERED
(
[Id]ASC
)
WITH
(
PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)
ON[PRIMARY]
)
ON[PRIMARY]
2、加入测试数据:
DECLARE@numberINT
SET@number=200000
WHILE@number>0
BEGIN
INSERTdbo.Article
(
MsId,
Title,
TitleBak,
Summary,
SummaryImageUrl,
Tag,
ArticleChannel_Id,
ArticleCategory_Id,
IsApproved,
Creator_Id,
CreatedDateTime,
ModifiedDateTime,
ViewCount,
ReplyCount,
DiggCount,
FavoriteCount,
LastReplyUser_Id,
LastReplyDateTime,
RightType,
IsDisplayContent,
IsSensitive,
Source
)
VALUES
(
@number,
'Title'+cast(@numberASVARCHAR(20)),
'TitleBak'+cast(@numberASVARCHAR(20)),
'Summary'+cast(@numberASVARCHAR(20)),
'SummaryImageUrl'+cast(@numberASVARCHAR(20)),
'Tag'+cast(@numberASVARCHAR(20)),
1,
2,
0,
@number,
GETDATE(),
GETDATE(),
100,
29,
123,
12,
@number,
GETDATE(),
1,
0,
0,
2
)
SET@number=@number-1
END
3、没建立索引前,利用语句,查询开销,看执行计划
WITHTEMPAS
(
SELECTROW_NUMBER()OVER(ORDERBYCreatedDateTime)ASROW,CreatedDateTime,ViewCount
FROMArticle
WHERECreator_Id=199996
)
SELECT*
FROMTEMP
WHEREROWBETWEEN1AND5
4、建立索引之后,再执行一次查询,执行计划如下:
细节内容如下:
可以看到,利用索引查询,开销明显减少。
(参考文献与博客:
二、实验反思
利用索引查询数据,效率明显提高;触发器可以实现表或者视图中难以删除、更改的数据。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验 报告