Installment 15: A matter of integrity (part 2 of 3)

Installment 15:A matter of integrity (part 2 of 3) 資管所碩一 690530017 尤婷靜

Integrity Rules • Domain v.s. Table rules (Table Rule可分為single-row rule, multi-row rule) • State v.s. Transition rules • Immediate v.s. Deferred rules

CREAT INTEGRITY RULE ECK1IF EMP2.EMP#=EMP1.EMP#THEN SAME(EMP1,EMP2);表示員工編號為unique,EMP#為candidate key相當於:ECK1 CONDIDATE KEY (EMP#)

SYNTACTIC SHORTHANDS • SINGLE-SCALAR RULE: CREATE INTEGRITY RULE ER3 FORALL EMP(EMP.SAL>0) ON ATTEMPTED VIOLATION REJECT;  SALARY DECIMAL(8,2)…… ER3 SALARY>0

SYNTACTIC SHORTHANDS • SINGLE-ROW RULE: CREATE INTEGRITY RULE ER7 IF EMP.JOB=‘Pgmr’ THEN EMP.SAL<50000;  CREATE TABLE EMP…… ER7 IF JOB =‘Pgmr’ THEN SAL<50000

KEYS AND FUNCTIONAL DEPENDENCE • Keys and FD relate with Integrity 主鍵以及候選鍵本身即是一種Integrity rule • As for FD’s Perspective CREATE INTEGRITY RULE TFD1 IF T1.A = T2.A THEN T1.B = T2.B 縮寫為 TFD1A -> B

KEYS AND FUNCTIONAL DEPENDENCE • As for Foreign key’s Perspective CREATE INTEGRITY RULE EDFK FORALL EMP (EXISTS DEPT (DEPT.DEPT#=EMP.DEPT#)); 縮寫為 EDFK FOREIGN KEY (DEPT#) REFERENCES DEPT PS:Date ignores “referential action”以及”triggered procedure”

Relation Model通常包含兩個一般的完整性規則如下: • Entity integrity (EI): 任何一個base table,其primary key不可為null • Referential integrity (RI): 在資料庫中,不允許擁有任何不相符合的foreign key值 這兩個規則稱為metarule

metarule • Rules about rules • 在任何的資料庫中都必須擁有這些規則,才能遵照關聯模式的要求 • 另一個metarule: Column Integrity Metarule: 每一個欄位中的值都必須來自相關定義域 e.g. EMP表格中的EMP#欄位中的每一個員工編號都必須來自也稱為EMP#的定義域

就實用主義領域來分析 • Single- V.S. Multi-row rules的劃分純粹是實務性的,因為single-row rules很容易在句法上表達而且比起multi-row rules更容易且有效的實行,但是single-row rules和multi-row rules並沒有一個很根本性的分別 • Immediate V.S. Deferred rules 所有的規則應該是即時的,而不是延遲的!因為延遲性規則能以即時規則來加以模擬,但是反過來則不行.

就實用主義領域來分析 CREATE INTEGRITY RULE DNE AT COMMIT EXISTS EMP(EMP.DEPT#=DEPT.DEPT#) ON ATTEMPTED VIOLATION ROLLBACK;  CREATE INTEGRITY RULE DNE FORALL DEPT(EXIST EMP(EMP.DEPT#=DEPT.DEPT#) OR EXIST TEMP(TRUE)) ON ATTEMPTED VIOLATION ROLLBACK; 每個部門必須至少擁有一個員工

就實用主義領域來分析 • State V.S. Transition rules • Single- and multi-row rules有時候被認為是single- and multi-varible rules e.g. CREATE INTEGRITY RULE EDFK FORALL EMP(EXISTS DEPT (DEPT.DEPT#=EMP.DEPT#) );  multi-variable rule (變數分別是DEPT和EMP)

Puzzle • 給定一個資料表SP{S#,P#}, S#代表供應商,P#代表供應的零件,找出所有成對的供應商編號Sx和Sy其供應的零件正好相同 (1)SQL (2)關聯性代數

The End T.J. 2001

Installment 15: A matter of integrity (part 2 of 3)