數(shù)據(jù)庫實驗報告二

..《數(shù)據(jù)庫原理》實驗報告實驗三：數(shù)據(jù)庫完整性與安全性控制實驗四：視圖與索引學號姓名班級日期2013302534楊添文10011303實驗三:數(shù)據(jù)完整性與安全性控制一、實驗內(nèi)容利用圖形用戶界面對實驗一中所創(chuàng)建的Student庫的S表中,增加以下的約束和索引。〔18分,每小題3分非空約束：為出生日期添加非空約束。主鍵約束：將學號<sno>設置為主鍵,主鍵名為pk_sno。唯一約束：為姓名<sname>添加唯一約束<唯一鍵>,約束名為uk_sname。缺省約束：為性別<ssex>添加默認值,其值為"男"。CHECK約束：為SC表的成績<grade>添加CHECK約束,約束名為ck_grade,其檢查條件為：成績應該在0-100之間。外鍵約束：為SC表添加外鍵約束,將sno,cno設置為外鍵,其引用表為分別是S表和C表,外鍵名稱分別為fk_sno,fk_cno。在圖形用戶界面中刪除上小題中已經(jīng)創(chuàng)建的各種約束,用SQL語言分別重新創(chuàng)建第1小題中的<2>-<6>小題。〔15分,每小題3分,提示：altertableaddconstraint〔2altertablesaddconstraintpk_snoprimarykey<sno>〔3altertablesaddconstraintuk_snameunique<sname>〔4altertablesaddconstraintadefault<'男'>forssex〔5>altertablescaddconstraintck_gradecheck<gradebetween0and100><6>altertablescaddconstraintfk_snoforeignkey<sno>referencess<sno>altertablescaddconstraintfk_cnoforeignkey<cno>referencesc<cno>利用圖形用戶界面根據(jù)上述SC表中的外鍵定義畫出由S,C,SC表構(gòu)成的數(shù)據(jù)庫關(guān)系圖。<5分,提示：選中student->數(shù)據(jù)關(guān)系圖用SQL語言刪除S表中所創(chuàng)建的缺省約束和外鍵約束。<6分,每小題3分缺省約束：altertablesdropconstrainta外鍵約束：altertablescdropconstraintfk_sno,fk_cno在圖形用戶界面中創(chuàng)建新登錄名以及用戶?！?6分根據(jù)第四版教材P148頁第8題,創(chuàng)建數(shù)據(jù)庫company和其中的基本表,并創(chuàng)建該題中所需要用到的各個用戶以及相關(guān)的登錄名?！?0分用圖形用戶界面完成以下的權(quán)限賦予：〔每小題3分用戶王明對兩個表均有Select和Insert的權(quán)力。用戶李勇對職工表具有Select權(quán)力,對工資字段具有更新權(quán)力。用SQL語句授權(quán)和收回權(quán)限并對權(quán)限加以驗證?！?0分,每題4分第四版教材P148頁第8題b>,c>,d>,f>,g>?！瞓>grantinsert,deleteon職工to李勇grantinsert,deleteon部門to李勇<c>grantselecton職工topublic<d>grantselect,update<工資>on職工to劉星<f>grantallprivilegeson職工to周平withgrantoptiongrantallprivilegeson部門to周平withgrantoption<g>createviewwage<a,b,c,d>asselect職工.部門號,max<工資>,min<工資>,avg<工資>from職工,部門where職工.部門號=部門.部門號groupby職工.部門號grantselectonwageto楊蘭第四版教材P148頁第9題的b>,c>,d>,f>,g>?！蔡崾荆?lt;c>和<g>小題可創(chuàng)建合適的視圖,針對視圖進行授權(quán),<c>小題用CURRENT_USER<b>revokedelete,inserton部門from李勇;revokedelete,inserton職工from李勇;<c>revokeselecton職工frompublic<d>revokeselect,updateon職工from劉星<f>revokeallprivilegeson職工from周平cascade<g>revokeselectonwagefrom楊蘭;dropviewwage;選做實驗〔選做第一題利用SQL語言創(chuàng)建,驗證和刪除規(guī)則。創(chuàng)建一個ssex_rule規(guī)則<createrule>,將其綁定<sp_bindrule>到S表的ssex性別字段上〔請驗證該規(guī)則生效,保證了輸入的性別只能是"男"或者"女"。建立規(guī)則ssex_rule：createrulessex_ruleas@ssexin<'男','女'>綁定：execsp_bindrule'ssex_rule','S.ssex'驗證：insertintosvalues<'95111','張偉','男人','1994-12-25','CS','3436330'>結(jié)果為：刪除ssex_rule規(guī)則〔注意：規(guī)則已綁定到ssex表的性別字段上,需要先解除原有的綁定sp_unbindrule,droprule。解除綁定：execsp_unbindrule's.ssex'刪除：droprulessex_rule二、實驗反思創(chuàng)建合適的視圖,可以令查詢簡單,效率更高,視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨立性。實驗四:視圖與索引一、實驗內(nèi)容在Student數(shù)據(jù)庫中,利用圖形用戶界面,創(chuàng)建一個選修了"數(shù)據(jù)庫原理"課程并且是1996年出生的學生的視圖,視圖中包括學號,性別,成績?nèi)齻€信息。<5分>用兩種不同的SQL語句創(chuàng)建第四版教材128頁第11題中要求的視圖〔視圖名：V_SPJ〔10分,每種方法5分>。第一種方法：createviewV_SPJasselectSNO,PNO,QTYfromSPJ,JwhereJ.JNO=SPJ.JNOandJ.JNAME='三建'第二種方法：createviewV_SPJasselectSNO,PNO,QTYfromSPJwhereSPJ.JNOin<selectJNOfromJwhereJ.JNAME='三建'>用SQL語句完成第四版教材128頁第11題中的視圖查詢〔10分,每小題5分>。<1>selectPNO,sum<QTY>totalfromV_SPJgroupbyPNO<2>select*fromV_SPJwhereSNO='S1'用SQL語句完成視圖的數(shù)據(jù)更新。<15分,每題5分>給視圖V_SPJ中增加一條數(shù)據(jù)。1、先建立insteadof觸發(fā)器insert_spj：create

trigger

insert_spj

on

V_SPJ

Instead

of

insert

As

Begin

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>

values<@sno,@pno,'J1',@qty>

end2、增加數(shù)據(jù)：insertintoV_SPJvalues<'S3','P5',406>3、結(jié)果：修改視圖V_SPJ中的任意一條數(shù)據(jù)的供應數(shù)量。updateV_SPJsetQTY=666wherePNO='P3'andSNO='S2'刪除視圖V_SPJ中的任意一條數(shù)據(jù)〔注意所創(chuàng)建視圖可以視圖消解時,才能正常刪除,否則會刪除失?。灰部梢钥紤]用insteadof觸發(fā)器實現(xiàn)。視圖連接有多個基表,不能正常刪除,建立觸發(fā)器delete_spj如下：createtriggerdelete_spjonV_SPJInsteadofdeleteAsBegindeclare@snochar<10>declare@pnochar<10>declare@qtyintselect@sno=sno,@pno=pno,@qty=qtyfromdeleteddeletefromSPJwhereSPJ.sno=@snoandSPJ.PNO=@PNOANDSPJ.JNO='J1'ANDSPJ.QTY=@QTY;end刪除一條數(shù)據(jù)：deletefromV_SPJwhereSNO='S3'andPNO='P1'andQTY=200結(jié)果如下：用圖形用戶界面對Student數(shù)據(jù)庫中C表的Cno字段創(chuàng)建一個降序排列的唯一索引,索引名稱IX_CNo。<5分>使用SQL語句對Student數(shù)據(jù)庫完成以下的索引操作。〔15分,每題5分在C表的CName屬性上創(chuàng)建一個非唯一性的聚簇索引-,索引名IX_CName。createclusteredindexIX_CNameonc<cname>在SC表上創(chuàng)建一個名為IX_Cnosno的非聚簇復合索引,該索引是針對sno,cno屬性集建立的升序索引。createnonclusteredindexIX_Cnosnoonsc<snoasc,cnoasc>刪除C表的索引IX_CName。dropindexc.IX_CName自己設計一個實驗驗證索引對數(shù)據(jù)庫查詢效率的提升作用?！?0分<提示：需要數(shù)據(jù)量比較大的情況下才容易進行對比>1、創(chuàng)建表：CREATETABLE[dbo].[Article]<[Id][int]IDENTITY<1,1>NOTFORREPLICATIONNOTNULL,[MsId][int]NOTNULL,[Title][nvarchar]<96>NOTNULL,[TitleBak][nvarchar]<96>NOTNULL,[Summary][nvarchar]<512>NOTNULL,[SummaryImageUrl][nvarchar]<256>NOTNULL,[Tag][nvarchar]<50>NOTNULL,[ArticleChannel_Id][int]NOTNULL,[ArticleCategory_Id][int]NOTNULL,[IsApproved][bit]NOTNULL,[Creator_Id][int]NOTNULL,[CreatedDateTime][datetime]NOTNULL,[ModifiedDateTime][datetime]NOTNULL,[ViewCount][int]NOTNULL,[ReplyCount][int]NOTNULL,[DiggCount][int]NOTNULL,[FavoriteCount][int]NOTNULL,[LastReplyUser_Id][int]NOTNULL,[LastReplyDateTime][datetime]NOTNULL,[RightType][int]NOTNULL,[IsDisplayContent][bit]NOTNULL,[IsSensitive][bit]NOTNULL,[Source][int]NOTNULL,CONSTRAINT[PK_Articles]PRIMARYKEYCLUSTERED<[Id]ASC>WITH<PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON>ON[PRIMARY]>ON[PRIMARY]2、加入測試數(shù)據(jù)：DECLARE@numberINTSET@number=200000WHILE@number>0BEGININSERTdbo.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>VALUES<@number,'Title'+cast<@numberASVARCHAR<20>>,'TitleBak'+cast<@numberASV