数据库实验报告二

1、数据库原理实验报告实验三： 数据库完整性与安全性控制实验四： 视图与索引学号姓名班级日期2013302534杨添文100113032015.10.17实验三:数据完整性与安全性控制一、实验内容1 利用图形用户界面对实验一中所创建的Student库的S表中，增加以下的约束和索引。（18分，每小题3分） (1) 非空约束：为出生日期添加非空约束。(2) 主键约束：将学号(sno)设置为主键，主键名为pk_sno。(3) 唯一约束：为姓名(sname)添加唯一约束(唯一键)，约束名为uk_sname。(4) 缺省约束：为性别(ssex)添加默认值，其值为“男”。(5) CHECK约束：为SC表的成绩

2、(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 s add constraint pk_sno primary key(sno)（3）alter table s add constraint

3、 uk_sname unique(sname)（4）alter table s add constraint a default('男') for ssex（5) alter table scadd constraint ck_grade check(grade between 0 and 100) (6) alter table sc add constraint fk_sno foreign key(sno) references s(sno) alter table sc add constraint fk_cno foreign key(cno) references

4、c(cno)3 利用图形用户界面根据上述SC表中的外键定义画出由S,C,SC表构成的数据库关系图。(5分，提示：选中student -> 数据关系图）4 用SQL语言删除S表中所创建的缺省约束和外键约束。(6分，每小题3分）(1) 缺省约束： alter table s drop constraint a(2) 外键约束： alter table sc drop 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,delete on 职工to 李勇 grant insert,delete on 部门to 李勇(c) grant select on 职工 to public(d)grant select,update(工资) on 职工 to 刘星

6、(f)grant all privileges on 职工 to 周平 with grant option grant all privileges on 部门 to 周平 with grant option(g)create view wage(a,b,c,d) as select 职工.部门号,max(工资),min(工资),avg(工资) from 职工,部门 where 职工.部门号=部门.部门号 group by 职工.部门号 grant select on wage to 杨兰(2) 第四版教材P148页第9题的b),c),d),f),g)。（提示：(c)和(g)小题可创建合适的视

7、图，针对视图进行授权，(c)小题用CURRENT_USER）(b)revoke delete,insert on 部门 from 李勇; revoke delete,insert on 职工 from 李勇;(c)revoke select on 职工 from public(d)revoke select,update on 职工 from 刘星(f)revoke all privileges on 职工 from 周平 cascade(g)revoke select on wage from 杨兰; drop view wage;l 选做实验（选做第一题）1 利用SQL语言创建，验证和删除

8、规则。(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','张伟',&

9、#39;男人','1994-12-25','CS','3436330') 结果为：(2) 删除ssex_rule规则（注意：规则已绑定到ssex表的性别字段上，需要先解除原有的绑定sp_unbindrule, drop rule）。1、 解除绑定：exec sp_unbindrule 's.ssex'2、 删除：drop rule ssex_rule二、实验反思 创建合适的视图，可以令查询简单，效率更高，视图对重构数据库提供了一定程度的逻辑独立性。实验四:视图与索引一、实验内容1 在Student数据库

10、中，利用图形用户界面，创建一个选修了“数据库原理”课程并且是1996年出生的学生的视图，视图中包括学号，性别，成绩三个信息。(5分)2 用两种不同的SQL语句创建第四版教材128页第11题中要求的视图（视图名：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 JNO

11、from J where J.JNAME='三建' )3 用SQL语句完成第四版教材128页第11题中的视图查询（10分，每小题5分)。(1)select PNO,sum(QTY) totalfrom V_SPJgroup by PNO(2)select * from V_SPJ where SNO='S1'4 用SQL语句完成视图的数据更新。(15分，每题5分)(1) 给视图V_SPJ中增加一条数据。1、先建立instead of触发器insert_spj：create trigger insert_spjon V_SPJInst

12、ead 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) 

13、 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、 视图连接有多个基表，不能正常删除，建立触

14、发器delete_spj如下： create trigger delete_spjon V_SPJInstead of delete AsBegin declare sno char(10) declare pno char(10) declare qty int select sno=sno,pno=pno,qty=qty from deleted delete from SPJ where SPJ.sno=sno and SPJ.PNO=PNO AND SPJ.JNO='J1' AND SPJ.QTY=QTY;end2、 删除一条数据：deletefrom V_SPJwhe

15、re SNO='S3' and PNO='P1' and QTY=200结果如下：5 用图形用户界面对Student数据库中C表的Cno字段创建一个降序排列的唯一索引,索引名称IX_CNo。(5分)6 使用SQL语句对Student数据库完成以下的索引操作。（15分，每题5分）(1) 在C表的CName属性上创建一个非唯一性的聚簇索引-，索引名IX_CName。 create clustered index IX_CName on c(cname)(2) 在SC表上创建一个名为IX_Cnosno的非聚簇复合索引，该索引是针对sno,cno属性集建立的升序索引。

16、create nonclustered index IX_Cnosno on 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 n

17、varchar(96) NOT NULL, Summary nvarchar(512) 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

18、 NULL, ViewCount int NOT NULL, ReplyCount int NOT NULL, 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

19、 PRIMARY KEY CLUSTERED ( Id ASC)WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARY2、加入测试数据： DECLARE number INT SET number = 200000 WHILE number > 0 BEGIN INSERT dbo.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 ) VALUE