《数据的库实验的报告材料的二.doc》由会员分享，可在线阅读，更多相关《数据的库实验的报告材料的二.doc（16页珍藏版）》请在三一办公上搜索。
1、实验三:数据完整性与安全性控制一、实验内容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,o设
2、置为外键，其引用表为分别是S表和C表，外键名称分别为fk_sno，fk_o。2 在图形用户界面中删除上小题中已经创建的各种约束，用SQL语言分别重新创建第1小题中的(2)-(6)小题。15分，每一小题3分，提示：alter table add constraint2altertables addconstraintpk_snoprimarykey(sno)3altertables addconstraintuk_snameunique(sname)4altertables addconstraintadefault(男)forssex5) altertablescaddconstraintck
3、_gradecheck(gradebetween 0 and 100) (6) altertablesc addconstraintfk_snoforeignkey(sno)referencess(sno)altertablesc addconstraintfk_oforeignkey(o)referencesc(o)3 利用图形用户界面根据上述SC表中的外键定义画出由S,C,SC表构成的数据库关系图。(5分，提示：选中student - 数据关系图4 用SQL语言删除S表中所创建的缺省约束和外键约束。(6分，每一小题3分(1) 缺省约束： altertables dropconstraint
4、a(2) 外键约束： altertablesc dropconstraintfk_sno,fk_o5 在图形用户界面中创建新登录名以与用户。16分(3) 根据第四版教材P148页第 8题，创建数据库pany和其中的根本表，并创建该题中所需要用到的各个用户以与相关的登录名。10分(4) 用图形用户界面完成以下的权限赋予：每一小题3分a) 用户王明对两个表均有Select和Insert的权力。b) 用户李勇对职工表具有Select权力，对工资字段具有更新权力。6 用SQL语句授权和收回权限并对权限加以验证。40分,每题4分(1) 第四版教材P148页第8题b),c),d),f),g)。b)gran
5、tinsert,delete on职工to李勇 grantinsert,delete on部门to李勇(c) grantselect on 职工 topublic(d)grantselect,update(工资) on职工 toX星(f)grantallprivileges on职工 to周平 withgrantoption grantallprivileges on部门 to周平 withgrantoption(g)createviewwage(a,b,c,d) as select职工.部门号,max(工资),min(工资),avg(工资) from职工,部门 where职工.部门号=部门.
6、部门号 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,updateon职工 fromX星(f)revokeall privileges on 职工 from 周平 cascade(g)
7、revokeselectonwage from杨兰; dropviewwage;l 选做实验选做第一题1 利用SQL语言创建，验证和删除规如此。(1) 创建一个ssex_rule规如此(create rule)，将其绑定(sp_bindrule)到S表的ssex性别字段上请验证该规如此生效，保证了输入的性别只能是“男或者“女。1、 建立规如此ssex_rule：createrule ssex_ruleas ssex in(男,女)2、 绑定：execsp_bindrulessex_rule,S.ssex3、 验证：insertintosvalues(95111,X伟,男人,1994-12-25
8、,CS,3436330)结果为：(2) 删除ssex_rule规如此注意：规如此已绑定到ssex表的性别字段上，需要先解除原有的绑定sp_unbindrule, drop rule。1、 解除绑定：execsp_unbindrules.ssex2、 删除：droprule ssex_rule二、实验反思创建适宜的视图，可以令查询简单，效率更高，视图对重构数据库提供了一定程度的逻辑独立性。实验四:视图与索引一、实验内容1 在Student数据库中，利用图形用户界面，创建一个选修了“数据库原理课程并且是1996年出生的学生的视图，视图中包括学号，性别，成绩三个信息。(5分)2 用两种不同的SQL语
9、句创建第四版教材128页第11题中要求的视图视图名：V_SPJ10分，每种方法5分)。第一种方法：createview V_SPJasselect SNO,PNO,QTYfrom SPJ,Jwhere J.JNO=SPJ.JNOand J.JNAME=三建第二种方法：createview V_SPJasselect SNO,PNO,QTYfrom SPJwhere SPJ.JNO in(select JNOfrom Jwhere J.JNAME=三建)3 用SQL语句完成第四版教材128页第11题中的视图查询10分，每一小题5分)。(1)select PNO,sum(QTY) totalfro
10、m V_SPJgroupby PNO(2)select* from V_SPJ where SNO=S14 用SQL语句完成视图的数据更新。(15分，每题5分)(1) 给视图V_SPJ中增加一条数据。1、先建立insteadof触发器insert_spj：createtriggerinsert_spjonV_SPJInsteadofinsertAsBegindeclaresnochar(10)declarepnochar(10)declareqtyintselectsno=sno,pno=pno,qty=qtyfrominsertedinsertintospj(sno,pno,jno,qty)
11、values(sno,pno,J1,qty)end2、增加数据：insertinto V_SPJvalues(S3,P5,406)3、结果：(2) 修改视图V_SPJ中的任意一条数据的供给数量。update V_SPJset QTY=666where PNO=P3and SNO=S2(3) 删除视图V_SPJ中的任意一条数据注意所创建视图可以视图消解时，才能正常删除，否如此会删除失败；也可以考虑用instead of触发器实现。1、 视图连接有多个基表，不能正常删除，建立触发器delete_spj如下：createtrigger delete_spjon V_SPJInsteadofdelet
12、eAsBegindeclare sno char(10)declare pno char(10)declare qty intselect sno=sno,pno=pno,qty=qtyfrom deleteddeletefrom SPJwhere SPJ.sno=sno and SPJ.PNO=PNOAND SPJ.JNO=J1AND SPJ.QTY=QTY;end2、 删除一条数据：deletefrom V_SPJwhere SNO=S3and PNO=P1and QTY=200结果如下：5 用图形用户界面对Student数据库中C表的o字段创建一个降序排列的唯一索引,索引名称IX_o。(
13、5分)6 使用SQL语句对Student数据库完成以下的索引操作。15分，每题5分(1) 在C表的ame属性上创建一个非唯一性的聚簇索引-，索引名IX_ame。createclusteredindex IX_ame on c(ame)(2) 在SC表上创建一个名为IX_osno的非聚簇复合索引，该索引是针对sno,o属性集建立的升序索引。 createnonclusteredindex IX_osno on sc(sno asc,o asc)(3) 删除C表的索引IX_ame。dropindex c.IX_ame7 自己设计一个实验验证索引对数据库查询效率的提升作用。40分 (提示：需要数据量
14、比拟大的情况下才容易进展比照)1、创建表：CREATETABLEdbo.Article(IdintIDENTITY(1,1)NOTFORREPLICATIONNOTNULL,MsIdintNOTNULL,Titlenvarchar(96)NOTNULL,TitleBaknvarchar(96)NOTNULL,Summarynvarchar(512)NOTNULL,SummaryImageUrlnvarchar(256)NOTNULL,Tagnvarchar(50)NOTNULL,ArticleChannel_IdintNOTNULL,ArticleCategory_IdintNOTNULL,I
15、sApprovedbitNOTNULL,Creator_IdintNOTNULL,CreatedDateTimedatetimeNOTNULL,ModifiedDateTimedatetimeNOTNULL,ViewCountintNOTNULL,ReplyCountintNOTNULL,DiggCountintNOTNULL,FavoriteCountintNOTNULL,LastReplyUser_IdintNOTNULL,LastReplyDateTimedatetimeNOTNULL,RightTypeintNOTNULL,IsDisplayContentbitNOTNULL,IsSe
16、nsitivebitNOTNULL,SourceintNOTNULL,CONSTRAINTPK_ArticlesPRIMARYKEYCLUSTERED(IdASC)WITH(PAD_INDEX=OFF,STATISTICS_NOREPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ONPRIMARY)ONPRIMARY2、参加测试数据：DECLAREnumberINTSETnumber= 200000WHILEnumber 0BEGININSERTdbo.Article(MsId,Title,TitleBak,
17、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+
18、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)SETnumber=number-1END3、没建立索引前，利用语句，查询开销，看执行计划WITHTEMPAS(SELECTROW_NUMBER()OVER (ORDERBYCreatedDateTime)ASROW,CreatedDateTime,ViewCountFROMArticleWHERECreator_Id=199996 )SELECT*FROMTEMPWHEREROWBETWEEN 1 AND 54、建立索引之后，再执行一次查询,执行计划如下：细节内容如下：可以看到，利用索引查询，开销明显减少。参考文献与博客：.blogs./kevinlzf/archive/2009/10/28/1591201.html二、实验反思利用索引查询数据，效率明显提高；触发器可以实现表或者视图中难以删除、更改的数据。
