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

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

2、為ck_grade，其檢查條件為：成績應(yīng)該在0-100之間。外鍵約束：為SC表添加外鍵約束，將sno,cno設(shè)置為外鍵，其引用表為分別是S表和C表，外鍵名稱分別為fk_sno，fk_cno。在圖形用戶界面中刪除上小題中已經(jīng)創(chuàng)建的各種約束，用SQL語言分別重新創(chuàng)建第1小題中的(2)-(6)小題。（15分，每小題3分，提示：alter table add constraint）（2）altertables addconstraintpk_snoprimarykey(sno)（3）altertables addconstraintuk_snameunique(sname)（4）altertables

3、 addconstraintadefault(男)forssex（5) altertablescaddconstraintck_gradecheck(gradebetween 0 and 100) (6) altertablesc addconstraintfk_snoforeignkey(sno)referencess(sno)altertablesc addconstraintfk_cnoforeignkey(cno)referencesc(cno)利用圖形用戶界面根據(jù)上述SC表中的外鍵定義畫出由S,C,SC表構(gòu)成的數(shù)據(jù)庫關(guān)系圖。(5分，提示：選中student - 數(shù)據(jù)關(guān)系圖）用SQL語

4、言刪除S表中所創(chuàng)建的缺省約束和外鍵約束。(6分，每小題3分）缺省約束： altertables dropconstrainta外鍵約束： altertablesc dropconstraintfk_sno,fk_cno在圖形用戶界面中創(chuàng)建新登錄名以與用戶。（16分）根據(jù)第四版教材P148頁第 8題，創(chuàng)建數(shù)據(jù)庫company和其中的基本表，并創(chuàng)建該題中所需要用到的各個(gè)用戶以與相關(guān)的登錄名。（10分）用圖形用戶界面完成以下的權(quán)限賦予：（每小題3分）用戶王明對兩個(gè)表均有Select和Insert的權(quán)力。用戶勇對職工表具有Select權(quán)力，對工資字段具有更新權(quán)力。用SQL語句授權(quán)和收回權(quán)限并對權(quán)限加以

5、驗(yàn)證。（40分,每題4分）第四版教材P148頁第8題b),c),d),f),g)。（b)grantinsert,delete on職工to勇 grantinsert,delete on部門to勇(c) grantselect on 職工 topublic(d)grantselect,update(工資) on職工 to星(f)grantallprivileges on職工 to周平 withgrantoption grantallprivileges on部門 to周平 withgrantoption(g)createviewwage(a,b,c,d) as select職工.部門號,max(

6、工資),min(工資),avg(工資) from職工,部門 where職工.部門號=部門.部門號 groupby職工.部門號grantselect onwage to蘭第四版教材P148頁第9題的b),c),d),f),g)。（提示：(c)和(g)小題可創(chuàng)建合適的視圖，針對視圖進(jìn)行授權(quán)，(c)小題用CURRENT_USER）(b)revokedelete,inserton部門 from勇; revokedelete,inserton職工 from勇;(c)revokeselect on 職工 frompublic(d)revokeselect,updateon職工 from星(f)revoke

7、all privileges on 職工 from 周平 cascade(g)revokeselectonwage from蘭; dropviewwage;選做實(shí)驗(yàn)（選做第一題）利用SQL語言創(chuàng)建，驗(yàn)證和刪除規(guī)則。創(chuàng)建一個(gè)ssex_rule規(guī)則(create rule)，將其綁定(sp_bindrule)到S表的ssex性別字段上（請驗(yàn)證該規(guī)則生效，保證了輸入的性別只能是“男”或者“女”）。建立規(guī)則ssex_rule：createrule ssex_ruleasssex in(男,女)綁定：execsp_bindrulessex_rule,S.ssex驗(yàn)證：insertintosvalues(

8、95111,偉,男人,1994-12-25,CS,3436330)結(jié)果為：刪除ssex_rule規(guī)則（注意：規(guī)則已綁定到ssex表的性別字段上，需要先解除原有的綁定sp_unbindrule, drop rule）。解除綁定：execsp_unbindrules.ssex刪除：droprule ssex_rule二、實(shí)驗(yàn)反思創(chuàng)建合適的視圖，可以令查詢簡單，效率更高，視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性。實(shí)驗(yàn)四:視圖與索引一、實(shí)驗(yàn)容在Student數(shù)據(jù)庫中，利用圖形用戶界面，創(chuàng)建一個(gè)選修了“數(shù)據(jù)庫原理”課程并且是1996年出生的學(xué)生的視圖，視圖中包括學(xué)號，性別，成績?nèi)齻€(gè)信息。(5分)用兩種

9、不同的SQL語句創(chuàng)建第四版教材128頁第11題中要求的視圖（視圖名：V_SPJ）（10分，每種方法5分)。第一種方法：createview V_SPJasselect SNO,PNO,QTYfrom SPJ,Jwhere J.JNO=SPJ.JNOand J.JNAME=第二種方法：createview V_SPJasselect SNO,PNO,QTYfrom SPJwhereSPJ.JNO in(select JNOfrom Jwhere J.JNAME=)用SQL語句完成第四版教材128頁第11題中的視圖查詢（10分，每小題5分)。(1)select PNO,sum(QTY) total

10、from V_SPJgroupby PNO(2)select* from V_SPJ where SNO=S1用SQL語句完成視圖的數(shù)據(jù)更新。(15分，每題5分)給視圖V_SPJ中增加一條數(shù)據(jù)。1、先建立insteadof觸發(fā)器insert_spj：createtriggerinsert_spjonV_SPJInsteadofinsertAsBegindeclaresnochar(10)declarepnochar(10)declareqtyintselectsno=sno,pno=pno,qty=qtyfrominsertedinsertintospj(sno,pno,jno,qty)val

11、ues(sno,pno,J1,qty)end2、增加數(shù)據(jù)：insertinto V_SPJvalues(S3,P5,406)3、結(jié)果：修改視圖V_SPJ中的任意一條數(shù)據(jù)的供應(yīng)數(shù)量。update V_SPJset QTY=666where PNO=P3and SNO=S2刪除視圖V_SPJ中的任意一條數(shù)據(jù)（注意所創(chuàng)建視圖可以視圖消解時(shí)，才能正常刪除，否則會刪除失??；也可以考慮用instead of觸發(fā)器實(shí)現(xiàn)）。視圖連接有多個(gè)基表，不能正常刪除，建立觸發(fā)器delete_spj如下：createtrigger delete_spjon V_SPJInsteadofdeleteAsBegindecla

12、resno char(10)declarepno char(10)declareqty intselectsno=sno,pno=pno,qty=qtyfrom deleteddeletefrom SPJwhere SPJ.sno=sno and SPJ.PNO=PNOAND SPJ.JNO=J1AND SPJ.QTY=QTY;end刪除一條數(shù)據(jù)：deletefrom V_SPJwhere SNO=S3and PNO=P1and QTY=200結(jié)果如下：用圖形用戶界面對Student數(shù)據(jù)庫中C表的Cno字段創(chuàng)建一個(gè)降序排列的唯一索引,索引名稱IX_CNo。(5分)使用SQL語句對Student

13、數(shù)據(jù)庫完成以下的索引操作。（15分，每題5分）在C表的CName屬性上創(chuàng)建一個(gè)非唯一性的聚簇索引-，索引名IX_CName。createclusteredindex IX_CName on c(cname)在SC表上創(chuàng)建一個(gè)名為IX_Cnosno的非聚簇復(fù)合索引，該索引是針對sno,cno屬性集建立的升序索引。 createnonclusteredindex IX_Cnosno on sc(sno asc,cno asc)刪除C表的索引IX_CName。dropindex c.IX_CName自己設(shè)計(jì)一個(gè)實(shí)驗(yàn)驗(yàn)證索引對數(shù)據(jù)庫查詢效率的提升作用。（40分） (提示：需要數(shù)據(jù)量比較大的情況下才容易

14、進(jìn)行對比)1、創(chuàng)建表：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,IsApprovedb

15、itNOTNULL,Creator_IdintNOTNULL,CreatedDateTimedatetimeNOTNULL,ModifiedDateTimedatetimeNOTNULL,ViewCountintNOTNULL,ReplyCountintNOTNULL,DiggCountintNOTNULL,FavoriteCountintNOTNULL,LastReplyUser_IdintNOTNULL,LastReplyDateTimedatetimeNOTNULL,RightTypeintNOTNULL,IsDisplayContentbitNOTNULL,IsSensitivebit

16、NOTNULL,SourceintNOTNULL,CONSTRAINTPK_ArticlesPRIMARYKEYCLUSTERED(IdASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ONPRIMARY)ONPRIMARY2、加入測試數(shù)據(jù)：DECLAREnumberINTSETnumber= 200000WHILEnumber 0BEGININSERTdbo.Article(MsId,Title,TitleBak,Summary

17、,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+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、沒建立索引前，利用語句，查詢開銷，看執(zhí)行計(jì)劃WITHTEMPAS(SELECTROW_NUMBER()OVER