數(shù)據(jù)庫原理 數(shù)據(jù)的完整性 實驗報告.doc

*大學計算機科學與信息學院軟件工程系上機實驗報告數(shù)據(jù)庫原理實驗報告題目實驗8 數(shù)據(jù)的完整性姓名*班級*日期*實驗環(huán)境：SQL Server 2000一、實驗?zāi)康氖煜ねㄟ^SQL對數(shù)據(jù)進行完整性控制，通過實驗加深對數(shù)據(jù)完整性的理解。n 相關(guān)知識觸發(fā)器是實施復雜完整性的一種機制。觸發(fā)器不需要專門的語句調(diào)用，當對它所保護數(shù)據(jù)進行修改時由DBMS自動激活，以防止對數(shù)據(jù)進行不正確，未授權(quán)或不一致的修改。創(chuàng)建觸發(fā)器的語法為：CREATE TRIGGER ON WITH ENCRYPTIONFOR DELETE，INSERT，UPDATEWITH APPENDNOT FOR REPLICATIONAS 其中：1) WITH ENCRYPTION 為加密選項。2) DELETE 選項為創(chuàng)建DELETE 觸發(fā)器。DELETE觸發(fā)器的作用是當對表執(zhí)行DELETE操作時觸發(fā)器被激活，并從指定表中刪除元組，同時將刪除的元組放入一個特殊的邏輯表（delete表）中。觸發(fā)器的動作可以檢查delete表中的數(shù)據(jù)，以確定下一步該如何處理。3) INSERT選項為創(chuàng)建INSERT觸發(fā)器。INSERT觸發(fā)器在對指定表中執(zhí)行插入數(shù)據(jù)操作時激活，激活后將插入表中的數(shù)據(jù)拷貝并送入一個特殊的邏輯表（inserted 表）中，觸發(fā)器會根據(jù)INSERT表中的值決定如何處理。4) UPDATE選項為創(chuàng)建UPDATE觸發(fā)器。UPDATE觸發(fā)器僅在對指定表中進行更新數(shù)據(jù)操作時激活。UPDATE觸發(fā)器激活后把將要被更新的原數(shù)據(jù)移入delete表中,再將要被更新后的新數(shù)據(jù)的備份送入inserted表中，UPDATE觸發(fā)器對delete和inserted表進行檢查，并決定如何處理。5) NOT FOR REPLICATION 選項說明當一個復制過程在修改一個觸發(fā)器表時，與該表相關(guān)聯(lián)的觸發(fā)器不能被執(zhí)行。一個觸發(fā)器只適用于一個表，每個表最多只能有三個觸發(fā)器，它們分別是INSERT、UPDATE和DELETE觸發(fā)器。觸發(fā)器僅在實施數(shù)據(jù)完整性和處理業(yè)務(wù)規(guī)則時使用。二、實驗內(nèi)容本實驗的內(nèi)容為使用SQL對數(shù)據(jù)進行完整性控制（關(guān)系數(shù)據(jù)庫的三類完整性、CHECK短語、CONSTRAINT子句、觸發(fā)器）。用實驗證實，當操作違反了完整性約束條件時，系統(tǒng)如何處理的。認真完成以下題目，按要求填寫實驗報告，記錄所有實驗用例。（1）打開SQL Server 2000的查詢分析器，用DDL語句建立如下三個表。表student1：字段名類型長度含義約束SnoChar10學號主碼(約束名：STU_PK)SnameChar20姓名非空(約束名：STU_U1)SsexChar2性別男或女 (約束名：STU_C1)SageInt年齡18 到 30(約束名：STU_C2)SdeptChar10所在系表course1：字段名類型長度含義約束CnoChar10課程號主碼(約束名：COU_PK)CnameChar20課程名唯一(約束名：COU_C1)CpnoChar2先行課CcreditInt學分表SC1：字段名類型長度含義約束SnoChar10學號主碼(約束名：SC_PK)外碼(參照student1的Sno，約束名：SC_FK1)CnoChar20課程號主碼(約束名：SC_PK)外碼(參照course1的Cno，約束名：SC_FK2)GradeChar2成績create table student1(Sno char(10),Sname char(20)constraint STU_U1 NOT NULL,Ssex char(2)constraint STU_C1 CHECK(Ssex in(男,女),Sage intconstraint STU_C2 check(Sage between 18 and 30),Sdept char(10)constraint STU_PK PRIMARY KEY(Sno);create table course1(Cno char(10),Cname char(20)constraint COU_C1 UNIQUE,Cpno char(2),Ccredit intconstraint COU_PK PRIMARY KEY(Cno);create table SC1(Sno char(10),Cno char(10),Grade CHAR(2),constraint SC_PK PRIMARY KEY(Sno,Cno),constraint SC_FK1 FOREIGN KEY(Sno) REFERENCES student1(Sno),constraint SC_FK2 FOREIGN KEY(Cno) REFERENCES course1(Cno),);（2）插入如下數(shù)據(jù)，什么原因?qū)е履承?shù)據(jù)不能正確插入？SnoSnameSsexSageSdept95001黎小明男16CS95002劉小華男45CS95003王小菲女26MA95004王小凌女20IS95004劉小燁男27ISinsertinto student1(Sno,Sname,Ssex,Sage,Sdept)VALUES(95001,黎小明,男,16,CS);insertinto student1(Sno,Sname,Ssex,Sage,Sdept)VALUES(95002,劉小華,男,45,CS);insertinto student1(Sno,Sname,Ssex,Sage,Sdept)VALUES(95003,王小菲,女,26,MA);insertinto student1(Sno,Sname,Ssex,Sage,Sdept)VALUES(95003,王小凌,女,20,IS);insertinto student1(Sno,Sname,Ssex,Sage,Sdept)VALUES(95003,劉小燁,男,27,IS);原因：年齡不在約束條件內(nèi)，Sno不能重復插入。學號重復。CnoCnameCcredit1高等數(shù)學42大學英語43計算機導論34計算機網(wǎng)絡(luò)35計算機網(wǎng)絡(luò)3insertinto course1(Cno,Cname,Ccredit)VALUES(1,高等數(shù)學,4);insertinto course1(Cno,Cname,Ccredit)VALUES(2,大學英語,4);insertinto course1(Cno,Cname,Ccredit)VALUES(3,計算機導論,3);insertinto course1(Cno,Cname,Ccredit)VALUES(4,計算機網(wǎng)絡(luò),3);insertinto course1(Cno,Cname,Ccredit)VALUES(5,計算機網(wǎng)絡(luò),3);原因：Cname唯一，不能重復插入。SnoCnoGrade9500118095001290950031959500417595004295insertinto SC1(Sno,Cno,Grade)VALUES(95001,1,80);insertinto SC1(Sno,Cno,Grade)VALUES(95001,2,90);insertinto SC1(Sno,Cno,Grade)VALUES(95003,1,95);insertinto SC1(Sno,Cno,Grade)VALUES(95004,1,75);insertinto SC1(Sno,Cno,Grade)VALUES(95004,2,95);（3） 給SC1表添加一個約束，約束名為SC1_C1，該約束限制Grade的取值范圍是0到100。alter table SC1add constraint SC1_C1 check(Grade between 0 and 100);（4） 更改約束STU_C2，使得年齡的范圍是15到50。alter table student1drop constraint STU_C2 ;ALTER TABLE student1add constraint STU_C2 CHECK(Sage between 0 and 100);（5） 對SC1表設(shè)計一個觸發(fā)器，該觸發(fā)器保證每個學生所選課程的總學分不超過12分。create trigger Insert_SC1 on dbo.SC1for insert,updateas if(12=(select sum(Ccredit)from SC1,Course1,Inserted where SC1.Cno=Course1.Cno and SC1.Sno=(select Sno from inserted)beginraiserror(不能選！,0,0)rollback transactionend return;（6）驗證觸發(fā)器。insertinto SC1values(200215121,1,92)insertinto SC1values(200215121,2,92)insertinto SC1values(200215121,3,92)三、實驗步驟1.在查詢分析器中完成（1）到（4）小題。2.在企業(yè)管理器中創(chuàng)建觸發(fā)器：（1）在企業(yè)管理器中，由服務(wù)器開始逐步擴展到觸發(fā)器所屬表的數(shù)據(jù)庫，打開表文件夾，在表窗口中用鼠標右擊觸發(fā)器所屬的表（本例為選課表即SC1）。（2）在彈出菜單上選擇“所有任務(wù)”“管理觸發(fā)器”項，則彈出如圖7-1所示的觸發(fā)器屬性對話框。 圖7-1 觸發(fā)器屬性對話框如果要新建觸發(fā)器，在文本T的文本框中輸入創(chuàng)建觸發(fā)器的SQL語句（見實驗內(nèi)容），單擊“檢查語法”按鈕進行語法的檢查，檢查無誤后，單擊確定按鈕。如圖7-2所示，于是執(zhí)行對基本表SC1的插入和更新操作的時候，都會自動顯示表SC1中的內(nèi)容。請在查詢分析器中進行驗證該觸發(fā)器。圖7-2 觸發(fā)器語法檢查對話框（3）如果要修改觸發(fā)器，則在“名稱”彈出項中選擇要修改的觸發(fā)器名（本例為SC_inserted）,然后在文本T的文本框中對已有的內(nèi)容進行修改，檢查無誤后單擊“確定”按鈕。如圖7-3所示，將該觸發(fā)器代碼修改為檢查插入、更新、刪除操作時觸發(fā)。請在查詢分析器中進行驗證該觸發(fā)器。圖7-3 修改觸發(fā)器語法檢查對話框（4）如果要刪除觸發(fā)器，則在“名稱”彈出項中選擇要刪除的觸發(fā)器名（本例為SC