数据库实验报告二

《数据库原理》实验报告实验三：数据库完整性与安全性控制实验四：视图与索引学号姓名班级日期2013302534杨添文100113032015.10.17实验三:数据完整性与安全性控制一、实验内容利用图形用户界面对实验一中所创建的Student库的S表中，增加以下的约束和索引。（18分，每小题3分）非空约束：为出生日期添加非空约束。主键约束：将学号(sno)设置为主键，主键名为pk_sno。唯一约束：为姓名(sname)添加唯一约束(唯一键)，约束名为uk_sname。缺省约束：为性别(ssex)添加默认值，其值为“男”。CHECK约束：为SC表的成绩(grade)添加CHECK约束，约束名为ck_grade，其检查条件为：成绩应该在0-100之间。外键约束：为SC表添加外键约束，将sno,cno设置为外键，其引用表为分别是S表和C表，外键名称分别为fk_sno，fk_cno。在图形用户界面中删除上小题中已经创建的各种约束，用SQL语言分别重新创建第1小题中的(2)-(6)小题。（15分，每小题3分，提示：altertableaddconstraint）（2）altertablesaddconstraintpk_snoprimarykey(sno)（3）altertablesaddconstraintuk_snameunique(sname)（4）altertablesaddconstraintadefault('男')forssex（5)altertablescaddconstraintck_gradecheck(gradebetween0and100)(6)altertablescaddconstraintfk_snoforeignkey(sno)referencess(sno)altertablescaddconstraintfk_cnoforeignkey(cno)referencesc(cno)利用图形用户界面根据上述SC表中的外键定义画出由S,C,SC表构成的数据库关系图。(5分，提示：选中student->数据关系图）用SQL语言删除S表中所创建的缺省约束和外键约束。(6分，每小题3分）缺省约束：altertablesdropconstrainta外键约束：altertablescdropconstraintfk_sno,fk_cno在图形用户界面中创建新登录名以及用户。（16分）根据第四版教材P148页第8题，创建数据库company和其中的基本表，并创建该题中所需要用到的各个用户以及相关的登录名。（10分）用图形用户界面完成以下的权限赋予：（每小题3分）用户王明对两个表均有Select和Insert的权力。用户李勇对职工表具有Select权力，对工资字段具有更新权力。用SQL语句授权和收回权限并对权限加以验证。（40分,每题4分）第四版教材P148页第8题b),c),d),f),g)。（b)grantinsert,deleteon职工to李勇grantinsert,deleteon部门to李勇(c)grantselecton职工topublic(d)grantselect,update(工资)on职工to刘星(f)grantallprivilegeson职工to周平withgrantoptiongrantallprivilegeson部门to周平withgrantoption(g)createviewwage(a,b,c,d)asselect职工.部门号,max(工资),min(工资),avg(工资)from职工,部门where职工.部门号=部门.部门号groupby职工.部门号grantselectonwageto杨兰第四版教材P148页第9题的b),c),d),f),g)。（提示：(c)和(g)小题可创建合适的视图，针对视图进行授权，(c)小题用CURRENT_USER）(b)revokedelete,inserton部门from李勇;revokedelete,inserton职工from李勇;(c)revokeselecton职工frompublic(d)revokeselect,updateon职工from刘星(f)revokeallprivilegeson职工from周平cascade(g)revokeselectonwagefrom杨兰;dropviewwage;选做实验（选做第一题）利用SQL语言创建，验证和删除规则。创建一个ssex_rule规则(createrule)，将其绑定(sp_bindrule)到S表的ssex性别字段上（请验证该规则生效，保证了输入的性别只能是“男”或者“女”）。建立规则ssex_rule：createrulessex_ruleas@ssexin('男','女')绑定：exec

sp_bindrule

'ssex_rule','S.ssex'

验证：insertintosvalues('95111','张伟','男人','1994-12-25','CS','3436330')结果为：删除ssex_rule规则（注意：规则已绑定到ssex表的性别字段上，需要先解除原有的绑定sp_unbindrule,droprule）。解除绑定：exec

sp_unbindrule

's.ssex'删除：droprulessex_rule二、实验反思创建合适的视图，可以令查询简单，效率更高，视图对重构数据库提供了一定程度的逻辑独立性。实验四:视图与索引一、实验内容在Student数据库中，利用图形用户界面，创建一个选修了“数据库原理”课程并且是1996年出生的学生的视图，视图中包括学号，性别，成绩三个信息。(5分)用两种不同的SQL语句创建第四版教材128页第11题中要求的视图（视图名：V_SPJ）（10分，每种方法5分)。第一种方法：createviewV_SPJasselectSNO,PNO,QTYfromSPJ,JwhereJ.JNO=SPJ.JNOandJ.JNAME='三建'第二种方法：createviewV_SPJasselectSNO,PNO,QTYfromSPJwhereSPJ.JNOin(selectJNOfromJwhereJ.JNAME='三建')用SQL语句完成第四版教材128页第11题中的视图查询（10分，每小题5分)。(1)selectPNO,sum(QTY)totalfromV_SPJgroupbyPNO(2)select*fromV_SPJwhereSNO='S1'用SQL语句完成视图的数据更新。(15分，每题5分)给视图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)

end2、增加数据：insertintoV_SPJvalues('S3','P5',406)3、结果：修改视图V_SPJ中的任意一条数据的供应数量。updateV_SPJsetQTY=666wherePNO='P3'andSNO='S2'删除视图V_SPJ中的任意一条数据（注意所创建视图可以视图消解时，才能正常删除，否则会删除失败；也可以考虑用insteadof触发器实现）。视图连接有多个基表，不能正常删除，建立触发器delete_spj如下：createtriggerdelete_spjonV_SPJInsteadofdeleteAsBegindeclare@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删除一条数据：deletefromV_SPJwhereSNO='S3'andPNO='P1' andQTY=200结果如下：用图形用户界面对Student数据库中C表的Cno字段创建一个降序排列的唯一索引,索引名称IX_CNo。(5分)使用SQL语句对Student数据库完成以下的索引操作。（15分，每题5分）在C表的CName属性上创建一个非唯一性的聚簇索引-，索引名IX_CName。createclusteredindexIX_CNameonc(cname)在SC表上创建一个名为IX_Cnosno的非聚簇复合索引，该索引是针对sno,cno属性集建立的升序索引。createnonclusteredindexIX_Cnosnoonsc(snoasc,cnoasc)删除C表的索引IX_CName。dropindexc.IX_CName自己设计一个实验验证索引对数据库查询效率的提升作用。（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@numberINTSET@number=200000WHILE@number>0BEGININSERTdbo.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)),'SummaryImage