實驗四 數(shù)據(jù)庫完整性

1、實驗四：數(shù)據(jù)庫的完整性一、實驗?zāi)康?. 熟悉主鍵約束、外鍵約束、空值、惟一約束的使用方法；2. 掌握默認值約束和默認對象的使用方法；3. 掌握check約束及規(guī)則的使用方法；4. 掌握觸發(fā)器的概念、創(chuàng)建、修改和刪除方法；5. 了解inserted和deleted的作用及數(shù)據(jù)的引用；6. 掌握各種觸發(fā)器的工作過程。二、實驗環(huán)境SQL Server 企業(yè)版三、實驗學(xué)時2學(xué)時四、實驗內(nèi)容及步驟（一）創(chuàng)建學(xué)生課程數(shù)據(jù)庫和表Create database stu_triGouse stu_trigocreate table s(sno char(5) not null,sname char(10),ss

2、ex char(2),sage int,sdept char(10),ssum_credit tinyint)gocreate table c(cno char(5) not null,cname char(10),ccredit tinyint)gocreate table sc(sno char(5) not null,cno char(5) not null,grade tinyint)go（二）主鍵約束-1.學(xué)生表-(1)主鍵的定義alter table s add constraint s_pk primary key(sno)go-（2）主鍵約束的違約檢查和處理insert int

3、o s values(95001,李瑩,女,21,計科,0)insert into s values(95001,章張,男,22,計科,0)-修改學(xué)號為，再次插入數(shù)據(jù)insert into s values(95002,章張,男,22,計科,0)-（3）依次輸入多個學(xué)生的記錄insert into s values (95003,陳華,女,22,計科,0),(95004,吳華義,男,23,計科,0),(95005,劉冠章,男,22,計科,0),(95006,蔣婷,女,22,網(wǎng)絡(luò),0),(95007,邱蔚六,男,21,網(wǎng)絡(luò),0),(95008,王德柱,男,22,網(wǎng)絡(luò),0),(95009,秦頻,女

4、,21,網(wǎng)絡(luò),0),(95010,程少根,男,21,網(wǎng)絡(luò),0)-2.課程表c-（1）主鍵的定義alter table c add constraint c_pk primary key(cno)-（2）主鍵的檢查和處理（?。?（3）錄入數(shù)據(jù)insert into cvalues(00001,計算機導(dǎo)論,2),(00002,高級語言,2),(00003,離算數(shù)學(xué),3),(00004,數(shù)據(jù)結(jié)構(gòu),3),(00005,c#,2),(00006,面向?qū)ο?2),(00007,數(shù)據(jù)庫原理,3),(00008,操作系統(tǒng),3)-3.學(xué)生選課表-（1）主鍵的定義alter table sc add constr

5、aint sc_pk primary key(sno,cno)go-（2）主鍵約束的檢查及處理insert into sc values(95001,00001,90)goinsert into sc values(95001,00001,86)Go-（3）錄入數(shù)據(jù)insert into scvalues(95001,00002,86),(95001,00003,92),(95001,00004,76),(95001,00005,86),(95002,00001,86),(95002,00002,80),(95002,00003,81),(95003,00001,88),(95003,0000

6、2,68),(95004,00001,96),(95005,00001,69)（三）參照完整性-1.定義學(xué)生選課表的學(xué)號為外鍵alter table sc add constraint sc_fk foreign key(sno) references s(sno)goalter table sc add constraint sc_fk_1 foreign key(cno) references c(cno)go-2.參照完整性約束的檢查-（1）向?qū)W生選課表中插入數(shù)據(jù)學(xué)號外鍵的檢查insert into sc values(95100,00001,90)go-（2）向?qū)W生選課表中插入數(shù)據(jù)課程

7、號外鍵的檢查insert into sc values(95006,10000,90)go-（3）被參照表數(shù)據(jù)變化：刪除學(xué)生表的某一個學(xué)生-1）刪除學(xué)生選課表中的外鍵sc_fkalter table sc drop sc_fkgo-2)重新定義學(xué)生表的外鍵sc_fk及違約處理機制alter table sc add constraint sc_fk_sno foreign key(sno) references s(sno) on delete cascadeon update cascadego-3)查詢學(xué)生選課表select * from sc-4) 刪除學(xué)生表學(xué)號為95003的學(xué)生del

8、ete from s where sno=95003go-5) 再次查詢學(xué)生選課表select * from scgo-更新學(xué)生表中學(xué)號為的學(xué)生學(xué)號為-級聯(lián)更新update s set sno=95105 where sno like 95005go-查詢學(xué)生選課表查看學(xué)生的學(xué)號是否改為select * from sc-(四) 用戶定義完整性（省略非空約束）-1. 唯一性約束（學(xué)生姓名惟一）（1）約束的定義alter table s add constraint s_sname_unique unique(sname)（2）約束的檢查和處理insert into s values(96001,

9、李瑩,女,26,信管,0)go2. 檢查約束（1）約束的定義alter table s add constraint s_c1 check(sage=12 and sage=35)（2）約束的檢查及處理insert into s values(96001,金林,男,9,信管,0)go3. 默認約束-（1）約束的定義alter table s add constraint s_def1 default 女 for ssex（2）約束的檢查及處理insert into s(sno,sname,sdept,ssum_credit) values(96002,胡華林,信管,0)這時沒有給性別賦值，但該

10、學(xué)生的性別取了默認值“女”(五)觸發(fā)器-1.insert觸發(fā)器-（1）觸發(fā)器的定義：-當向sc表中插入一個學(xué)生的成績時，將s表中該學(xué)生的總學(xué)分加上添加的課程的學(xué)分。CREATE TRIGGER sc_tri_insON sc AFTER INSERTASBEGINDECLARE sno char(5), cno char(5)DECLARE xf tinyintSELECT sno=sno, cno=cno from insertedSELECT xf=ccredit FROM c WHERE cno=cnoUPDATE s SET ssum_credit= ssum_credit +xf W

11、HERE sno=snoPRINT 修改成功END（2） 觸發(fā)器的驗證insert into sc values(95006,00001,80)2）查詢學(xué)生表select * from s從圖中可以看出，學(xué)生表中學(xué)生李瑩的學(xué)號改為“95001”3）查詢學(xué)生選課表select * from sc-2. delete觸發(fā)器-（）觸發(fā)器的定義-在刪除s表中的一條學(xué)生記錄時將sc表中該學(xué)生的相應(yīng)記錄也刪除。CREATE TRIGGER s_delete_triON s AFTER DELETEASBEGINDELETE FROM sc WHERE sno IN(SELECT sno FROM dele

12、ted)END-1）查詢學(xué)生表sselect * from s-2)查看“劉冠章”的選課記錄,學(xué)號為95105select * from sc-3）在學(xué)生表中刪除學(xué)號為“95105”的學(xué)生記錄delete from s where sno like 95105-4) 查看sc表學(xué)號為“”的選課信息select * from sc where sno like 95105-3.update觸發(fā)器-（1）觸發(fā)器的定義-創(chuàng)建觸發(fā)器，當修改s表中的學(xué)號時，同時也要將sc表中的學(xué)號修改成相應(yīng)的學(xué)號-（假設(shè)s表和sc表之間沒有定義外鍵約束）CREATE TRIGGER s_update_triON s A

13、FTER UPDATEASBEGINDECLARE old_num char(5), new_num char(5)SELECT old_num=sno FROM deletedSELECT new_num=sno FROM insertedUPDATE sc SET sno=new_num WHERE sno=old_numEND-（2）觸發(fā)器的驗證1）UPDATE s SET sno=95100 WHERE sno=950012）查詢學(xué)生表select * from s從圖中可以看出，學(xué)生表中學(xué)生李瑩的學(xué)號改為“95100”3）查詢學(xué)生選課表select * from sc從圖中可以看出，

14、學(xué)生選課表中學(xué)生李瑩的學(xué)號改為“95100”4. Instead of觸發(fā)器在stu_tri數(shù)據(jù)庫中創(chuàng)建視圖stu_view，包含學(xué)生學(xué)號、專業(yè)、課程號、成績。該視圖依賴于表s和sc，是不可更新視圖?？梢栽谝晥D上創(chuàng)建INSTEAD OF觸發(fā)器，當向視圖中插入數(shù)據(jù)時分別向表s和sc插入數(shù)據(jù)，從而實現(xiàn)向視圖插入數(shù)據(jù)的功能。（1）首先創(chuàng)建視圖：create view stu_viewasselect s.sno,sdept,cno,gradefrom s inner join sc on s.sno=sc.sno（2）向視圖中插入數(shù)據(jù)Insert into stu_view values(98100

15、,software,00001,90)這說明，該視圖是不可更新視圖，不能向該視圖中直接插入數(shù)據(jù)。（3）創(chuàng)建視圖CREATE TRIGGER InsteadTrigON stu_view INSTEAD OF INSERTASBEGINDECLARE XH char(5), XM char(10),ZY char(10), KCH char(5), CJ tinyintSET XM=佚名SELECT XH=sno, ZY=sdept, KCH=cno, CJ=gradeFROM insertedINSERT INTO s(sno, sname, sdept) VALUES(XH, XM, ZY)INSERT INTO sc VALUES(XH, KCH, CJ)END（3）再次向視圖中插入數(shù)據(jù)Insert