数据库实验工作报告资料

数据库实验工作报告资料

//《数据库原理》实验报告实验三：数据库完整性与安全性控制实验四：视图与索引学号姓名班级日期2013302534杨添文100113032015.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之间。n//(1)外键约束：为SC表添加外键约束，将sno,cno设置为外键，其引用表为分别是S表和C表，外键名称分别为fk_sno，fk_cno。1．在图形用户界面中删除上小题中已经创建的各种约束，用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)n//1．利用图形用户界面根据上述SC表中的外键定义画出由S,C,SC表构成的数据库关系图。(5分，提示：选中student->数据关系图）2．用SQL语言删除S表中所创建的缺省约束和外键约束。(6分，每小题3分）(1)缺省约束：altertablesdropconstrainta(2)外键约束：altertablescdropconstraintfk_sno,fk_cno3．在图形用户界面中创建新登录名以及用户。（16分）(3)根据第四版教材P148页第8题，创建数据库company和其中的基本表，并创建该题中所需要用到的各个用户以及相关的登录名。（10分）n//(1)用图形用户界面完成以下的权限赋予：（每小题3分）a)用户王明对两个表均有Select和Insert的权力。n//a)用户李勇对职工表具有Select权力，对工资字段具有更新权力。1．用SQL语句授权和收回权限并对权限加以验证。（40分,每题4分）(1)第四版教材P148页第8题b),c),d),f),g)。（b)grantinsert,deleteon职工to李勇grantinsert,deleteon部门to李勇(c)grantselecton职工topublic(d)grantselect,update(工资)on职工to刘星n//(f)grantallprivilegeson职工to周平withgrantoptiongrantallprivilegeson部门to周平withgrantoption(g)createviewwage(a,b,c,d)asselect职工.部门号,max(工资),min(工资),avg(工资)from职工,部门where职工.部门号=部门.部门号groupby职工.部门号grantselectonwageto杨兰(1)第四版教材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)revokeselectonwagen//from杨兰;dropviewwage;l选做实验（选做第一题）1．利用SQL语言创建，验证和删除规则。(1)创建一个ssex_rule规则(createrule)，将其绑定(sp_bindrule)到S表的ssex性别字段上（请验证该规则生效，保证了输入的性别只能是“男”或者“女”）。1、建立规则ssex_rule：createrulessex_ruleas@ssexin('男','女')2、绑定：exec sp_bindrule 'ssex_rule','S.ssex' 3、验证：insertintosvalues('95111','张伟','男人','1994-12-25','CS','3436330')结果为：(2)删除ssex_rule规则（注意：规则已绑定到ssex表的性别字段上，需要先解除原有的绑定sp_unbindrule,droprule）。1、解除绑定：exec sp_unbindrule 's.ssex'2、删除：droprulessex_rule二、实验反思创建合适的视图，可以令查询简单，效率更高，视图对重构数据库提供了一定程度的逻辑独立性。n//实验四:视图与索引一、实验内容1．在Student数据库中，利用图形用户界面，创建一个选修了“数据库原理”课程并且是1996年出生的学生的视图，视图中包括学号，性别，成绩三个信息。(5分)2．用两种不同的SQL语句创建第四版教材128页第11题中要求的视图（视图名：V_SPJ）（10分，每种方法5分)。第一种方法：createviewV_SPJasselectSNO,PNO,QTYfromSPJ,JwhereJ.JNO=SPJ.JNOandJ.JNAME='三建'第二种方法：createviewV_SPJasselectSNO,PNO,QTYfromSPJwhereSPJ.JNOin(n//selectJNOfromJwhereJ.JNAME='三建')1．用SQL语句完成第四版教材128页第11题中的视图查询（10分，每小题5分)。(1)selectPNO,sum(QTY)totalfromV_SPJgroupbyPNO(2)select*fromV_SPJwhereSNO='S1'2．用SQL语句完成视图的数据更新。(15分，每题5分)(1)给视图V_SPJ中增加一条数据。1、先建立insteadof触发器insert_spj：create trigger insert_spjon V_SPJInstead of insert AsBegin  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_SPJn//values('S3','P5',406)3、结果：(1)修改视图V_SPJ中的任意一条数据的供应数量。updateV_SPJsetQTY=666wherePNO='P3'andSNO='S2'(2)删除视图V_SPJ中的任意一条数据（注意所创建视图可以视图消解时，才能正常删除，否则会删除失败；也可以考虑用insteadof触发器实现）。1、视图连接有多个基表，不能正常删除，建立触发器delete_spj如下：createtriggerdelete_spjonV_SPJInsteadofdeleteAsBegindeclare@snochar(10)declare@pnochar(10)n//declare@qtyintselect@sno=sno,@pno=pno,@qty=qtyfromdeleteddeletefromSPJwhereSPJ.sno=@snoandSPJ.PNO=@PNOANDSPJ.JNO='J1'ANDSPJ.QTY=@QTY;end1、删除一条数据：deletefromV_SPJwhereSNO='S3'andPNO='P1'andQTY=200结果如下：1．用图形用户界面对Student数据库中C表的Cno字段创建一个降序排列的唯一索引,索引名称IX_CNo。(5分)n//1．使用SQL语句对Student数据库完成以下的索引操作。（15分，每题5分）(1)在C表的CName属性上创建一个非唯一性的聚簇索引-，索引名IX_CName。createclusteredindexIX_CNameonc(cname)(2)在SC表上创建一个名为IX_Cnosno的非聚簇复合索引，该索引是针对sno,cno属性集建立的升序索引。createnonclusteredindexIX_Cnosnoonsc(snoasc,cnoasc)(3)删除C表的索引IX_CName。dropindexc.IX_CName2．自己设计一个实验验证索引对数据库查询效率的提升作用。（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,n//[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,n//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,n//0,0,2)SET@number=@number-1END3、没建立索引前，利用语句，查询开销，看执行计划WITHTEMPAS(SELECTROW_NUMBER()OVER(ORDERBYCreatedDateTime)ASROW,CreatedDateTime,ViewCountFROMArticleWHERECreator_Id=199996)SELECT*FROMTEMPWHEREROWBETWEEN1AND54、建立索引之后，再执行一次查询,执行计划如下：细节内容如下：n//可以看到，利用索引查询，开销明显减少。（参考文献与博客：http://www.cnblogs.com/kevinlzf/archive/2009/10/28/1591201.html）二、实验反思利用索引查询数据，效率明显提高；触发器可以实现表或者视图中难以删除、更改的数据。