數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告二

1、數(shù)據(jù)庫原理實(shí)驗(yàn)報(bào)告實(shí)驗(yàn)三： 數(shù)據(jù)庫完整性與安全性控制實(shí)驗(yàn)四： 視圖與索引學(xué)號(hào)姓名班級日期2013302534楊添文100113032015.10.17實(shí)驗(yàn)三:數(shù)據(jù)完整性與安全性控制一、實(shí)驗(yàn)內(nèi)容1 利用圖形用戶界面對實(shí)驗(yàn)一中所創(chuàng)建的Student庫的S表中，增加以下的約束和索引。（18分，每小題3分） (1) 非空約束：為出生日期添加非空約束。(2) 主鍵約束：將學(xué)號(hào)(sno)設(shè)置為主鍵，主鍵名為pk_sno。(3) 唯一約束：為姓名(sname)添加唯一約束(唯一鍵)，約束名為uk_sname。(4) 缺省約束：為性別(ssex)添加默認(rèn)值，其值為“男”。(5) CHECK約束：為SC表的成績

2、(grade)添加CHECK約束，約束名為ck_grade，其檢查條件為：成績應(yīng)該在0-100之間。(6) 外鍵約束：為SC表添加外鍵約束，將sno,cno設(shè)置為外鍵，其引用表為分別是S表和C表，外鍵名稱分別為fk_sno，fk_cno。2 在圖形用戶界面中刪除上小題中已經(jīng)創(chuàng)建的各種約束，用SQL語言分別重新創(chuàng)建第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 利用圖形用戶界面根據(jù)上述SC表中的外鍵定義畫出由S,C,SC表構(gòu)成的數(shù)據(jù)庫關(guān)系圖。(5分，提示：選中student -> 數(shù)據(jù)關(guān)系圖）4 用SQL語言刪除S表中所創(chuàng)建的缺省約束和外鍵約束。(6分，每小題3分）(1) 缺省約束： alter table s drop constraint a(2) 外鍵約束： alter table sc drop constraint fk_sno,fk_cno5 在圖形用戶界面中創(chuàng)建新登錄名以及用戶。（16分）(3) 根據(jù)第四版教材P148頁第 8題，創(chuàng)建數(shù)據(jù)庫company和其中的基本表，并創(chuàng)建該題中所需要用到的各個(gè)用戶以及相關(guān)的登錄

5、名。（10分）(4) 用圖形用戶界面完成以下的權(quán)限賦予：（每小題3分）a) 用戶王明對兩個(gè)表均有Select和Insert的權(quán)力。 b) 用戶李勇對職工表具有Select權(quán)力，對工資字段具有更新權(quán)力。6 用SQL語句授權(quán)和收回權(quán)限并對權(quán)限加以驗(yàn)證。（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 職工.部門號(hào),max(工資),min(工資),avg(工資) from 職工,部門 where 職工.部門號(hào)=部門.部門號(hào) group by 職工.部門號(hào) grant select on wage to 楊蘭(2) 第四版教材P148頁第9題的b),c),d),f),g)。（提示：(c)和(g)小題可創(chuàng)建合適的視

7、圖，針對視圖進(jìn)行授權(quán)，(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 選做實(shí)驗(yàn)（選做第一題）1 利用SQL語言創(chuàng)建，驗(yàn)證和刪除

8、規(guī)則。(1) 創(chuàng)建一個(gè)ssex_rule規(guī)則(create rule)，將其綁定(sp_bindrule)到S表的ssex性別字段上（請驗(yàn)證該規(guī)則生效，保證了輸入的性別只能是“男”或者“女”）。1、 建立規(guī)則ssex_rule：create rule ssex_ruleas ssex in('男','女')2、 綁定：exec sp_bindrule 'ssex_rule','S.ssex' 3、 驗(yàn)證：insert intosvalues('95111','張偉',&

9、#39;男人','1994-12-25','CS','3436330') 結(jié)果為：(2) 刪除ssex_rule規(guī)則（注意：規(guī)則已綁定到ssex表的性別字段上，需要先解除原有的綁定sp_unbindrule, drop rule）。1、 解除綁定：exec sp_unbindrule 's.ssex'2、 刪除：drop rule ssex_rule二、實(shí)驗(yàn)反思 創(chuàng)建合適的視圖，可以令查詢簡單，效率更高，視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性。實(shí)驗(yàn)四:視圖與索引一、實(shí)驗(yàn)內(nèi)容1 在Student數(shù)據(jù)庫

10、中，利用圖形用戶界面，創(chuàng)建一個(gè)選修了“數(shù)據(jù)庫原理”課程并且是1996年出生的學(xué)生的視圖，視圖中包括學(xué)號(hào)，性別，成績?nèi)齻€(gè)信息。(5分)2 用兩種不同的SQL語句創(chuàng)建第四版教材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語句完成視圖的數(shù)據(jù)更新。(15分，每題5分)(1) 給視圖V_SPJ中增加一條數(shù)據(jù)。1、先建立instead of觸發(fā)器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、增加數(shù)據(jù)：insert into V_SPJvalues('S3','P5',406)3、結(jié)果： (2) 修改視圖V_SPJ中的任意一條數(shù)據(jù)的供應(yīng)數(shù)量。 update V_SPJset QTY=666where PNO='P3' and SNO='S2'(3) 刪除視圖V_SPJ中的任意一條數(shù)據(jù)（注意所創(chuàng)建視圖可以視圖消解時(shí)，才能正常刪除，否則會(huì)刪除失?。灰部梢钥紤]用instead of觸發(fā)器實(shí)現(xiàn)）。1、 視圖連接有多個(gè)基表，不能正常刪除，建立觸

14、發(fā)器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、 刪除一條數(shù)據(jù)：deletefrom V_SPJwhe

15、re SNO='S3' and PNO='P1' and QTY=200結(jié)果如下：5 用圖形用戶界面對Student數(shù)據(jù)庫中C表的Cno字段創(chuàng)建一個(gè)降序排列的唯一索引,索引名稱IX_CNo。(5分)6 使用SQL語句對Student數(shù)據(jù)庫完成以下的索引操作。（15分，每題5分）(1) 在C表的CName屬性上創(chuàng)建一個(gè)非唯一性的聚簇索引-，索引名IX_CName。 create clustered index IX_CName on c(cname)(2) 在SC表上創(chuàng)建一個(gè)名為IX_Cnosno的非聚簇復(fù)合索引，該索引是針對sno,cno屬性集建立的升序索引。

16、create nonclustered index IX_Cnosno on sc(sno asc,cno asc)(3) 刪除C表的索引IX_CName。 drop index c.IX_CName7 自己設(shè)計(jì)一個(gè)實(shí)驗(yàn)驗(yàn)證索引對數(shù)據(jù)庫查詢效率的提升作用。（40分） (提示：需要數(shù)據(jù)量比較大的情況下才容易進(jìn)行對比)1、創(chuàng)建表： 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、加入測試數(shù)據(jù)： 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