1、数据库原理实验报告学号 姓名 班级 日期实验三：数据库完整性与安全性控制实验四：视图与索引2013302534 杨添文 10011303 2015.10.17实验三:数据完整性与安全性控制一、实验内容1 利用图形用户界面对实验一中所创建的 Student 库的 S 表中，增加以下的约束和索引。（18 分，每小题 3 分） (1) 非空约束：为出生日期添加非空约束。(2) 主键约束：将学号(sno)设置为主键，主键名为 pk_sno。(3) 唯一约束：为姓名(sname)添加唯一约束(唯一键)，约束名为 uk_sname。(4) 缺省约束：为性别(ssex)添加默认值，其值为“男”。(5) CH
2、ECK 约束：为 SC 表的成绩(grade)添加 CHECK 约束，约束名为 ck_grade，其检查条件为：成绩应该在 0-100 之间。(6) 外键约束：为 SC 表添加外键约束，将 sno,cno 设置为外键，其引用表为分别是 S表和 C 表，外键名称分别为 fk_sno，fk_cno。2 在图形用户界面中删除上小题中已经创建的各种约束，用 SQL 语言分别重新创建第1 小题中的(2)-(6)小题。（15 分，每小题 3 分，提示：alter table add constraint）（2）alter table sadd constraint pk_sno primary key(s
3、no)（3）alter table sadd constraint uk_sname unique(sname)（4）alter table sadd constraint a default(男) for ssex（5) alter table scadd constraint ck_grade check(grade between 0 and 100)(6) alter table scadd constraint fk_sno foreign key(sno) references s(sno)alter table scadd constraint fk_cno foreign ke
4、y(cno) references c(cno)3 利用图形用户界面根据上述 SC 表中的外键定义画出由 S,C,SC 表构成的数据库关系图。(5 分，提示：选中 student - 数据关系图）4 用 SQL 语言删除 S 表中所创建的缺省约束和外键约束。(6 分，每小题 3 分）(1) 缺省约束：alter table sdrop constraint a(2) 外键约束：alter table scdrop constraint fk_sno,fk_cno5 在图形用户界面中创建新登录名以及用户。（16 分）(3) 根据第四版教材 P148 页第 8 题，创建数据库 company 和其
5、中的基本表，并创建该题中所需要用到的各个用户以及相关的登录名。（10 分）(4) 用图形用户界面完成以下的权限赋予：（每小题 3 分）a)用户王明对两个表均有 Select 和 Insert 的权力。b)用户李勇对职工表具有 Select 权力，对工资字段具有更新权力。6 用 SQL 语句授权和收回权限并对权限加以验证。（40 分,每题 4 分）(1) 第四版教材 P148 页第 8 题 b),c),d),f),g)。（b)grant insert,deleteon 职工to 李勇grant insert,deleteon 部门to 李勇(c) grant selecton 职工to publ
6、ic(d)grant select,update(工资) on 职工to 刘星(f)grant all privilegeson 职工to 周平with grant optiongrant all privilegeson 部门to 周平with grant option(g)create view wage(a,b,c,d)as select 职工.部门号,max(工资),min(工资),avg(工资)from 职工,部门where 职工.部门号=部门.部门号group by 职工.部门号grant select on wageto 杨兰(2) 第四版教材 P148 页第 9 题的 b),c
7、),d),f),g)。（提示：(c)和(g)小题可创建合适的视图，针对视图进行授权，(c)小题用 CURRENT_USER）(b)revoke delete,insert on 部门from 李勇;revoke delete,insert on 职工from 李勇;(c)revoke selecton 职工from public(d)revoke select,update on 职工from 刘星(f)revoke all privilegeson 职工from 周平 cascade(g)revoke select on wagefrom 杨兰;drop view wage; 选做实验（选做
8、第一题）1 利用 SQL 语言创建，验证和删除规则。(1)创建一个 ssex_rule 规则(create rule)，将其绑定(sp_bindrule)到 S 表的 ssex性别字段上（请验证该规则生效，保证了输入的性别只能是“男”或者“女”）。1、 建立规则 ssex_rule：create rule ssex_ruleas ssex in(男,女)2、 绑定：exec sp_bindrule ssex_rule,S.ssex 3、 验证：insert intosvalues(95111,张伟,男人,1994-12-25,CS,3436330) 结果为：(2)删除 ssex_rule 规则
9、（注意：规则已绑定到 ssex 表的性别字段上，需要先解除原有的绑定 sp_unbindrule, drop rule）。1、 解除绑定：exec sp_unbindrule s.ssex2、 删除：drop rule ssex_rule二、实验反思创建合适的视图，可以令查询简单，效率更高，视图对重构数据库提供了一定程度的逻辑独立性。实验四:视图与索引一、实验内容1 在 Student 数据库中，利用图形用户界面，创建一个选修了“数据库原理”课程并且是 1996 年出生的学生的视图，视图中包括学号，性别，成绩三个信息。(5 分)2 用两种不同的 SQL 语句创建第四版教材 128 页第 11
10、题中要求的视图（视图名：V_SPJ）（10 分，每种方法 5 分)。第一种方法：create view V_SPJasselect SNO,PNO,QTYfrom SPJ,Jwhere J.JNO=SPJ.JNOand J.JNAME=三建第二种方法：create view 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) totalf
11、rom V_SPJgroup by PNO(2)select *from V_SPJwhere SNO=S14 用 SQL 语句完成视图的数据更新。(15 分，每题 5 分)(1) 给视图 V_SPJ 中增加一条数据。1、先建立 instead of 触发器 insert_spj：create trigger insert_spjon V_SPJInstead of insert AsBegindeclare sno char(10)declare pno char(10)declare qty int select sno=sno,pno=pno,qty=qtyfrom insertedin
12、sert into spj(sno,pno,jno,qty)values(sno,pno,J1,qty)end2、增加数据：insert into V_SPJvalues(S3,P5,406)3、结果：(2) 修改视图 V_SPJ 中的任意一条数据的供应数量。update V_SPJset QTY=666where PNO=P3 and SNO=S2(3) 删除视图 V_SPJ 中的任意一条数据（注意所创建视图可以视图消解时，才能正常删除，否则会删除失败；也可以考虑用 instead of 触发器实现）。1、 视图连接有多个基表，不能正常删除，建立触发器 delete_spj 如下：creat
13、e trigger delete_spjon V_SPJInstead of delete AsBegindeclare sno char(10)declare pno char(10)declare qty int select sno=sno,pno=pno,qty=qtyfrom deleteddelete from SPJwhere SPJ.sno=sno and SPJ.PNO=PNOAND SPJ.JNO=J1 AND SPJ.QTY=QTY;end2、 删除一条数据：deletefrom V_SPJwhere SNO=S3and PNO=P1and QTY=200结果如下：5 用
14、图形用户界面对 Student 数据库中 C 表的 Cno 字段创建一个降序排列的唯一索引,索引名称 IX_CNo。(5 分)6 使用 SQL 语句对 Student 数据库完成以下的索引操作。（15 分，每题 5 分）(1) 在 C 表的 CName 属性上创建一个非唯一性的聚簇索引-，索引名 IX_CName。create clustered index IX_CNameon c(cname)(2) 在 SC 表上创建一个名为 IX_Cnosno 的非聚簇复合索引，该索引是针对 sno,cno 属性集建立的升序索引。create nonclustered index IX_Cnosnoon
15、 sc(sno asc,cno asc)(3) 删除 C 表的索引 IX_CName。drop index c.IX_CName7 自己设计一个实验验证索引对数据库查询效率的提升作用。（40 分）(提示：需要数据量比较大的情况下才容易进行对比)1、创建表：CREATE TABLE dbo.Article(Id int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,MsId int NOT NULL,Title nvarchar(96) NOT NULL,TitleBak nvarchar(96) NOT NULL,Summary nvarchar(512
16、) NOT NULL,SummaryImageUrl nvarchar(256) NOT NULL,Tag nvarchar(50) NOT NULL,ArticleChannel_Id int NOT NULL,ArticleCategory_Id int NOT NULL,IsApproved bit NOT NULL,Creator_Id int NOT NULL,CreatedDateTime datetime NOT NULL,ModifiedDateTime datetime NOT NULL,ViewCount int NOT NULL,ReplyCount int NOT NU
17、LL,DiggCount int NOT NULL,FavoriteCount int NOT NULL,LastReplyUser_Id int NOT NULL,LastReplyDateTime datetime NOT NULL,RightType int NOT NULL,IsDisplayContent bit NOT NULL,IsSensitive bit NOT NULL,Source int NOT NULL,CONSTRAINT PK_Articles PRIMARY KEY CLUSTERED (Id ASC)WITH (PAD_INDEX = OFF, STATIST
18、ICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARY2、加入测试数据：DECLARE number INTSET number = 200000WHILE number 0BEGININSERT dbo.Article(MsId,Title,TitleBak,Summary,SummaryImageUrl, Tag,ArticleChannel_Id,ArticleCategory_Id,IsApproved,Creator
19、_Id,CreatedDateTime,ModifiedDateTime,ViewCount,ReplyCount,DiggCount,FavoriteCount,LastReplyUser_Id,LastReplyDateTime,RightType,IsDisplayContent,IsSensitive,Source)VALUES(number,Title+cast(number AS VARCHAR(20),TitleBak+cast(number AS VARCHAR(20),Summary+cast(number AS VARCHAR(20),SummaryImageUrl+cas
20、t(number AS VARCHAR(20),Tag+cast(number AS VARCHAR(20),1,2,0,number,GETDATE(),GETDATE(),100,29,123,12,number,GETDATE(),1,0,0,2)SET number=number-1END3、没建立索引前，利用语句，查询开销，看执行计划WITH TEMP AS(SELECT ROW_NUMBER() OVER (ORDER BY CreatedDateTime) AS ROW,CreatedDateTime,ViewCountFROM ArticleWHERE Creator_Id=199996 )SELECT *FROM TEMPWHERE ROW BETWEEN 1 AND 54、建立索引之后，再执行一次查询,执行计划如下：细节内容如下：可以看到，利用索引查询，开销明显减少。（参考文献与博客：http:/
