数据库实验报告二

..《数据库原理》实验报告实验三：数据库完整性与安全性控制实验四：视图与索引学号姓名班级日期2013302534杨添文10011303实验三:数据完整性与安全性控制一、实验内容利用图形用户界面对实验一中所创建的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〔2altertablesaddconstraintpk_snoprimarykey<sno>〔3altertablesaddconstraintuk_snameunique<sname>〔4altertablesaddconstraintadefault<'男'>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<'男','女'>绑定：execsp_bindrule'ssex_rule','S.ssex'验证：insertintosvalues<'95111','张伟','男人','1994-12-25','CS','3436330'>结果为：删除ssex_rule规则〔注意：规则已绑定到ssex表的性别字段上,需要先解除原有的绑定sp_unbindrule,droprule。解除绑定：execsp_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@qtyintselect@sno=sno,@pno=pno,@qty=qtyfromdeleteddeletefromSPJwhereSPJ.sno=@snoandSPJ.PNO=@PNOANDSPJ.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<@numberASV