數(shù)據(jù)庫系統(tǒng)概論第5章數(shù)據(jù)庫完整性.ppt

1,第5章 數(shù)據(jù)庫完整性,5.1完整性約束條件 5.2完整性控制,2,數(shù)據(jù)庫的完整性,正確性 相容性,數(shù)據(jù)庫完整性的實現(xiàn): (1)定義完整性約束條件 (2)完整性檢查 (3)違約反應,10.1完整性約束條件,完整性約束條件作用的對象:關系 元組 列,列約束:是指列的類型、取值范圍、精度、排序等約束. 元組約束:是指元組中各個字段間的聯(lián)系約束. 關系約束:是指若干元組間、關系集合上以及關系之間的聯(lián)系約束.,3,完整性約束條件作用對象的狀態(tài): 靜態(tài) 動態(tài),靜態(tài)約束:是指數(shù)據(jù)庫每一確定狀態(tài)時的數(shù)據(jù)對象所應滿 足的約束條件,它是反映數(shù)據(jù)庫狀態(tài)合理性的約束.,動態(tài)約束:是指數(shù)據(jù)庫從一種狀態(tài)轉變?yōu)榱硪环N狀態(tài)時,新、 舊值之間所滿足的約束條件,它是反映數(shù)據(jù)庫狀 態(tài)變遷的約束.,完整性約束條件分類:,1:靜態(tài)列級約束 2:靜態(tài)元組約束 3:靜態(tài)關系約束 4:動態(tài)列級約束 5:動態(tài)元組約束 6:動態(tài)關系約束,4,一、靜態(tài)列級約束,靜態(tài)列級約束：是指對一個列的取值域的說明。,包括以下幾個方面： (1)對數(shù)據(jù)類型的約束(數(shù)據(jù)的類型、長度、單位、精度等) (2)對數(shù)據(jù)格式的約束 (3)對取值范圍或取值集合的約束 (4)對空值的約束 (5)其他約束,二、動態(tài)列級約束,動態(tài)列級約束:是指修改列定義或列值時應滿足的約束條件.,包括以下幾個方面： (1)修改列定義時的約束 (2)修改列值時的約束,5,三、靜態(tài)元組約束,靜態(tài)元組約束:是指元組的各個列之間的約束關系.,四、動態(tài)元組級約束,動態(tài)元組級約束:是指修改元組的值時元組中各個列之間 的約束條件.,五、靜態(tài)關系約束,靜態(tài)關系約束:是指一個關系的各個元組之間或者若干關系 之間存在的約束條件.,包括以下幾個方面： (1)實體完整性約束 (2)參照完整性約束 (3)函數(shù)依賴約束 (4)統(tǒng)計約束,6,六、動態(tài)關系約束,動態(tài)關系約束:是指加在關系變化前后狀態(tài)上的約束條件.,總結:,7,10.2完整性控制,完整性控制機制的功能： (1)定義功能 (2)檢查功能 (3)如果發(fā)現(xiàn)用戶的操作請求使數(shù)據(jù)違背了完整性約束 條件,則采取一定的動作來保證數(shù)據(jù)的完整性.,完整性規(guī)則的表示 :,五元組 (D, O, A, C, P),D(Data):指約束作用的數(shù)據(jù)對象 O(Operation):觸發(fā)完整性檢查的數(shù)據(jù)庫操作 A(Assertion):數(shù)據(jù)對象必須滿足的斷言或語義約束 C(Condition):選擇A作用的數(shù)據(jù)對象值的謂詞 P(Procedure):違反完整性規(guī)則時觸發(fā)的過程,立即執(zhí)行檢查 延遲執(zhí)行檢查,8,例子1:對表Student的“學號不能為空”的約束規(guī)定.,五元組 (D, O, A, C, P),D:約束作用的對象為Sno屬性 O:插入或修改Student元組時 A:Sno不能為空 C:無(A作用于所有記錄的Sno屬性) P:拒絕執(zhí)行該操作,例子2:對教師基本情況表的“教授工資不得低于1000元”的約束規(guī)定.,五元組 (D, O, A, C, P),D:約束作用的對象為工資Sal屬性 O:插入或修改職工元組時 A:Sal不能小于1000 C:職稱=教授 P:拒絕執(zhí)行該操作,9,實現(xiàn)參照完整性要考慮的幾個問題:,1.外碼能否接受空值的問題,學生(學號,姓名,性別,專業(yè)號,年齡) 專業(yè)(專業(yè)號,專業(yè)名),外碼,例子1:,外碼取空值表示該學生尚未分配到任何具體的專業(yè)學習.,與應用環(huán)境的語義相符,因此外碼可取空值.,例子2:,學生(學號,姓名,性別,專業(yè)號,年齡) 課程(課程號,課程名,學分) 選修(學號,課程號,成績),外碼(學號)取空值表示一個不存在的學生選了某門課程,成績記錄在成績列中.,與應用環(huán)境的語義不相符,因此外碼不能取空值.,10,2.在被參照關系中刪除元組的問題,例子:學生(學號,姓名,性別,專業(yè)號,年齡) 選修(學號,課程號,成績),被參照關系,三種策略: (1)級聯(lián)刪除 (2)受限刪除 (3)置空值刪除,在學生關系中刪除元組(95001,李勇,男,CS,20),11,例子： A（A#，AN） S（S#，SN，A#，SA）,在A關系中刪除元組(001,物理)？,三種策略: (1)級聯(lián)刪除 (2)受限刪除 (3)置空值刪除,12,3.在參照關系中插入元組時的問題,例子:學生(學號,姓名,性別,專業(yè)號,年齡) 選修(學號,課程號,成績),參照關系,兩種策略: (1)受限插入 (2)遞歸插入,在選修關系中插入元組(99001,1,90),13,例子： S（S#，SN，A#，SA） A（A#，AN）,在S關系中插入元組(95001,李勇,031,20)？,兩種策略: (1)受限插入 (2)遞歸插入,14,4.修改關系中的主碼,不允許修改主碼 允許修改主碼,(1)修改被參照關系的主碼,例子:學生(學號,姓名,性別,專業(yè)號,年齡) 選修(學號,課程號,成績),被參照關系,在學生關系中修改元組(95001,李勇,男,CS,20)的學號為960123,三種策略: (1)級聯(lián)修改 (2)受限修改 (3)置空值修改,15,例子： S（S#，SN，A#，SA） A（A#，AN）,在A關系中修改元組(001,物理)中的主碼001為005？,三種策略: (1)級聯(lián)修改 (2)受限修改 (3)置空值修改,16,(2)修改參照關系的外碼,例子:學生(學號,姓名,性別,專業(yè)號,年齡) 選修(學號,課程號,成績),參照關系,在選修關系中元組(95001, 1, 90)修改為(99001,1,90),兩種策略: (1)受限修改 (2)遞歸修改,17,例子： S（S#，SN，A#，SA） A（A#，AN）,在S關系中修改元組(95001,李勇,001,20)的A#改為030？,兩種策略: (1)受限修改 (2)遞歸修改,18,10.3 SQL Server的完整性,一、Oracle中的實體完整性,例1：在學生選課數(shù)據(jù)庫中，要定義Student表的Sno屬性為主碼 CREATE TABLE Student (Sno NUMBER(8), Sname VARCHAR(20), Sage NUMBER(20), CONSTRAINT PK_SNO PRIMARY KEY (Sno); 注意: (PK_SNO是此主碼約束名 ) 或: CREATE TABLE Student (Sno NUMBER(8) PRIMARY KEY , Sname VARCHAR(20), Sage NUMBER(20);,PRIMARY KEY子句，供用戶在建表時指定關系的主碼列。,19,例2：要在SC表中定義(Sno, Cno)為主碼 CREATE TABLE SC (Sno NUMBER(8), Cno NUMBER(2), Grade NUMBER(2), CONSTRAINT PK_SC PRIMARY KEY (Sno, Cno);,二、 SQL Server中的參照完整性,定義參照完整性 FOREIGN KEY子句：定義外碼列 REFERENCES子句：外碼相應于哪個表的主碼 ON DELETE|UPDATE CASCADE子語： 在刪除修改被參照關系的元組時，同時刪除修改參照關系中 外碼值等于被參照關系的元組中主碼值的元組,20,例1：建立表EMP表 CREATE TABLE EMP (Empno NUMBER(4), Ename VARCHAR(10), Job VERCHAR2(9), Mgr NUMBER(4), Sal NUMBER(7,2), Deptno NUMBER(2), CONSTRAINT FK_DEPTNO FOREIGN KEY (Deptno) REFERENCES DEPT(Deptno);,Deptno NUMBER(2) CONSTRAINT FK_DEPTNO FOREIGN KEY REFERENCES DEPT(Deptno),CONSTRAINT FK_DEPTNO FOREIGN KEY (Deptno) REFERENCES DEPT(Deptno) ON DELETE CASCADE,21,CREATE TABLE SC ( Sno char(7) , Cno char(10) , Grade tinyint, CHECK (Grade = 0 and Grade = 100), PRIMARY KEY ( Sno, Cno ), FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ), FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) ),例子2:sc表結構,22,三、 SQL Server中用戶定義的完整性,可定義三類完整性約束: 列值非空（NOT NULL短語） 列值唯一（UNIQUE短語） 檢查列值是否滿足一個布爾表達式（CHECK短語）,例1：建立部門表DEPT，要求部門名稱Dname列 取值唯一，部門編號Deptno列為主碼 CREATE TABLE DEPT (Deptno NUMBER, Dname VARCHAR(9) CONSTRAINT U1 UNIQUE, Loc VARCHAR(10), CONSTRAINT PK_DEPT PRIMARY KEY (Deptno);,23,例2： 建立學生登記表Student，要求學號在90000至99999之 間，年齡29，性別只能是男或女，姓名非空. CREATE TABLE Student (Sno NUMBER(5) CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999), Sname VARCHAR(20) CONSTRAINT C2 NOT NULL, Sage NUMBER(3) CONSTRAINT C3 CHECK (Sage 29), Ssex VARCHAR(2) CONSTRAINT C4 CHECK (Ssex IN (男, 女);,24,例3 ：建立職工表EMP，要求每個職工的應發(fā)工資不得超過3000元。 應發(fā)工資實際上就是實發(fā)工資列Sal與扣除項Deduct之和。 CREATE TABLE EMP (Eno NUMBER(4) Ename VARCHAR(10), Job VARCHAR(8), Sal NUMBER(7,2), Deduct NUMBER(7,2) Deptno NUMBER(2), CONSTRAINTS C1 CHECK (Sal + Deduct =3000);,25,四、 SQL Server中的觸發(fā)器。,1.定義觸發(fā)器,Create trigger on for|after|instead of as ,例子1:定義一個for型觸發(fā)器,為教師表teacher定義完整性規(guī)則教授的工資=4000,如果4000,自動改為4000.,Create trigger in-up-sal On teacher for insert,update as if (inserted.zc=教授) and (inserted.sal4000) inserted.sal=4000,26,例2:定義一個for型觸發(fā)器,記錄修改student表sno的用戶和時間信息.,Create table who-change (