title: inst.16, a matter of integrity (part3 of 3) date: 28.11.2001 ming-chun chiu page 1...

Ming-Chun Chiu

Title: Inst.16, A Matter of Integrity (Part3 of 3)

Date: 28.11.2001

Installment 16 : A Matter of Integrity (Part 3 of 3)

Student: Ming-Chun Chiu

Class Advisor: Jack, S.M. Huang, Ph.D.

Department of Information Management at National Chung-Cheng University

Reference:

Ming-Chun Chiu


Date: 28.11.2001

OUTLINE

• Terminology Review• Integrity Support

• In SQL/89• In SQL/92• In Object-Oriented• Via Stored Procedures

• Puzzle

Ming-Chun Chiu


Date: 28.11.2001



• Puzzle

Ming-Chun Chiu


Date: 28.11.2001

Terminology Review

• Single-Row

• Multi-Row

CREATE INTEGRITY RULE ER7 IF EMP.JPB=“PGMR” THEN EMP.SAL < 50000;

CREATE INTEGRITY RULE DE20 IF DEPT.BUDGET < 1000000 AND DEPT.DEPT# = EMP.DEPT# THEN EMP.SAL <=100000;

Data-source: Installment 14

Ming-Chun Chiu


Date: 28.11.2001

• State Rule v.s. Transition Rule

• Immediate v.s. Deferred [installment 11, 14]

CREATE INTEGRITY RULE E26 IF EMP’.E# = EMP.E# THEN EMP’.SAL <=EMP.SAL;

CREATE INTEGRITY RULE DEN AT COMMIT EXIST EMP (EMP.DEPT#=DEPT.DEPT#) ON ATTEMPED VIOLATION ROLLBACK;

Data-source: Installment 14

Ming-Chun Chiu


Date: 28.11.2001

OUTLINE



• Puzzle

Ming-Chun Chiu


Date: 28.11.2001

Integrity Support

• SQL Version?• SQL/86 (SQL)

• Queries, Basic definitions & Manipulation

• SQL/89 • Referential integrity

• SQL/92 (SQL2)• Revised & Expanded

• SQL/99 (SQL3)• Archive rules & triggers, some recursive operation,

Object-Oriented features

Date Source: http://www.cis.ohio-state.edu/~srini/670/

Ming-Chun Chiu


Date: 28.11.2001

SQL/89 Support

• Integrity Enhancement Feature:• Default Value• CHECK• UNIQUE, PRIMARY KEY, FOREIGN KEY

Ming-Chun Chiu


Date: 28.11.2001

CREATE TABLE tablename (column_name datatype [NULL | NOT NULL] [DEFAULT default_value] [column_constraint_clause] ……)

• Default Value• Syntax of CREATE in SQL/89

Ming-Chun Chiu


Date: 28.11.2001

• CHECK constraint• Declaration clause

• Immediate

• State

CREATE TABLE EMP ….

CHECK (JOB <> ‘Pgmr’ or SAL < 50000)

•Single-row

•Unnamed

•No Violation Response

CREATE INTEGRITY RULE ER7 IF EMP.JPB=“PGMR” THEN EMP.SAL < 50000;

Ming-Chun Chiu


Date: 28.11.2001

• UNIQUE, PRIMARY KEY, FOREIGN KEY Constraints• Declaration Clause• Unnamed• Immediate • State• No violation response

UNIQUE (EMP #)PRIMARY KEY (EMP#)FOREIGN KEY (DEPT#) REFERENCES DEPT

Ming-Chun Chiu


Date: 28.11.2001

• Others in SQL/89• No any referential support

They are OPTIONAL in SQL/89!!!

Ming-Chun Chiu


Date: 28.11.2001

CREATE TABLE EMP …. CONSTRAINT EMP_FK1EMP_FK1 FOREIGN KEY (DEPT#) REFERENCES DEPT(DEPT#);

SQL/92 Support

• Integrity rule names• Domain rules• General multi-row rules• Deferred Checking

Ming-Chun Chiu


Date: 28.11.2001

• Domain Rules• to enumerate the values in that domain.

ex: from a to b , picture (99-999-99999)

• Problems: (1st Problem)

• If used in Truth-valued expression of arbitrary complexity?

Ex: Domain D draws its values from Column C of table T, then What is the domain of column T.C?

Employee

EMP_ID Name TEL

1 xxx xxx

2 xxx xxx

3 xxx xxx

4 xxx xxx

5 xxx xxx

Hourly

EMP_ID Hourly_Rate

2 xxx

4 xxx

Salary

EMP_ID Annual_Salary

1 xxx

3 xxx

5 xxx

Shared column

Ming-Chun Chiu


Date: 28.11.2001

• Domain Rules(cont.)

• Two Problems: (2nd Problem)

• If a domain integrity rule is dropped ?

Ex: DROP DOMAIN, ALTER DOMAIN

Domain Rule is important: What operator is illegal (i.e. type check)

Ming-Chun Chiu


Date: 28.11.2001

• Multi-row Rules• Two Traps:

1) Redundancy

CREATE TABLE DEPT …. CONSTRAINT DE20 CHECK ( DEPT.BUDGET < 1000000 OR NOT EXISTS

( SELECT * FROM EMP WHERE EMP.DEPT# = DEPT.DEPT# AND EMP.SAL > 100000 ) )

CREATE ASSERTION DE20 CHECK ( NOT EXISTS

( SELECT * FROM DEPT WHERE DEPT.BUDGET < 1000000 AND EXISTS ( SELECT *

FROM EMPWHERE EMP.DEPT# = DEPT.DEPT#AND EMP.SAL > 100000 ) )

( 經費少於 1,000,000的部門內 , 不能有任何員工其薪水超過 100,000 )

Ming-Chun Chiu


Date: 28.11.2001

• Multi-row Rules (cont.)

• Two Traps:2) Rule R used in table T’s Declaration,

CREATE TABLE T … CONSTRAINT TNE CHECK ( R )

CREATE ASSERTION TNE CHECK

( R )

• IF R is “Table T must not be empty”

O

XEXISTS ( SELECT * FROM T )

EXISTS ( SELECT * FROM T )

If T is empty? SQL/92 allows base table constraint to be of arbitrary

complexity, and not limit them used only meaningful way!!

Ming-Chun Chiu


Date: 28.11.2001

• Deferred CheckingDEFERRABLE / NOT DEFERRABLE

INITIALLY DEFERRED / INITIALLY IMMEDIATE

( Reference Installment 14, installment 11)

SET CONSTRAINTS constraints [IMMEDIATE | DEFERRED)

CREATE INTEGRITY RULE DEN AT COMMIT EXIST EMP (EMP.DEPT#=DEPT.DEPT#) ON ATTEMPED VIOLATION ROLLBACK;

( 每一個部門至少有一個職員 )

Ming-Chun Chiu


Date: 28.11.2001

Dynamically deferred constraints (1/3)

• Why use deferred constraints ? (use Oracle)

• CREATE TABLE chicken (cID INT PRIMARY KEY, eID INT REFERENCES egg(eID));

• CREATE TABLE egg(eID INT PRIMARY KEY, cID INT

REFERENCES chicken(cID));

error !

egg eID cIDchicken cID eID

Data Source: Installment 11,葛煥元

Ming-Chun Chiu


Date: 28.11.2001

• Others issue in SQL/92:• No support for declaring functional dependencies

Recommend: 宣告完整性是好的 DBMS必要條件 (installment 14), 盡量在基表 DeclarationDeclaration 時時就作 Integrity constraints, 盡量少用 Procedure來作 Integrity constraints.

Quantity Unit_Price Amount

10 10 10020 10 200

Intelligent !!!

[Reference: Installment 9]

Ming-Chun Chiu


Date: 28.11.2001

OUTLINE



• Puzzle

Ming-Chun Chiu


Date: 28.11.2001

Object-Oriented Support

• Ex: 經費少於 1,000,000 的部門 , 不能有任何員工薪水超過 100,000

IN RDB:

IN OODB: (via Procedure, i.e MATHOD)

CREATE INTEGRITY RULE DE20IF DEPT.BUDGET < 1000000AND DEPT.DEPT# = EMP.DEPT #THEN EMP.SAL <= 100000

Method for hiring an employeeMethod for updating an employee’s salaryMethod for updating a department’s budgetMethod for moving an employee to a new department

Ming-Chun Chiu


Date: 28.11.2001

Object-Oriented Support (cont.)

Problems Arising: 1. 我們可能會無法明確的掌握住系統何時該執行檢查。2. 我們應如何確保所有必要的方式中都能含有必要的檢查碼 ? 3. 我們能避免任何檢查碼被忽略嗎 ?4. 針對所有執行相同限制功能的方法中，我們如何確保其錯誤訊息有

統一的格式呢 ? 此外，對於家族特性的的限制條件中，我們如何確保其錯誤訊息有統一的格式呢 ?

5. 如果限制式改變，我們該如何找出所有需要被修改的方法 ?6. 我們應如何確保強制檢查碼都是正確無誤的 ?

Ming-Chun Chiu


Date: 28.11.2001

Object-Oriented Support (cont.)

7. 我們應如何做延遲檢查 ?8. 我們應該如何去做查詢，去找出某一特定物件 (given object)或

結合物件 (combination of objects)的所有限制式呢 ?9. 在倒資料 (Load)或其他作業 (utility)的處理中，限制式都能被

強制執行嗎 ?10.如何做語意 (semantic)的最佳化 ? 11.完整性檢查碼可以做最佳化嗎 ? 而他的優良嗎 ?12.在建立應用程式撰寫或維護期間，使用者生產力又是如何 ?

•RDB: Declare Once, the all in one

•ODB: One Constraint All Related Method

自動化 ?

人工 ?

Ming-Chun Chiu


Date: 28.11.2001

OUTLINE



• Puzzle

Ming-Chun Chiu


Date: 28.11.2001

Via Stored Procedures

• Stored Procedure / Trigger

Create proc Addnew_Emp (@emp_name , @emp_dep, @emp_salary) as if @emp_salary <=0

return ERROR! if ( (select budget from dep where dep.name = @emp_dep) < 1000000 and (@emp_Salary > 100000) ) return ERROR! Else Insert into EMP (@emp.name, @emp_dep, @emp_salary) return;

Who knows what you do !!!Somewhat Alike the Method in OO !!!

Ming-Chun Chiu


Date: 28.11.2001

Via Stored Procedures(cont.)

• Are Stored Procedure / Triggered play a good roll of doing the integrity constraint ?

It has the same drawback with OO!!!

Ming-Chun Chiu


Date: 28.11.2001

OUTLINE



• Puzzle

Ming-Chun Chiu


Date: 28.11.2001

Puzzle

• Puzzle:• CK be some candidate key for table T• User add ck2 into table T• Failed, if ck2 = ck1 and ck1 existed in table T

• Explanation of following:

Ck1 and ck2 are “THE SAME”, If for the purpose of

1. Comparison condition?

2. Candidate key uniqueness?

3. Duplicate elimination?

Puzzle source: “A Guide to SQL Standard”, C.J. Date

Ming-Chun Chiu


Date: 28.11.2001

Reference

• Installment 9, 11, 14, 15, 16• An Introduction to Database systems, Volume I, C. J. Date• Modern Database Management, Fifth, McFadden• http://www-db.stanford.edu/~ullman/• http://www.odmg.org• http://www.odbmsfacts.com/• 資料庫應用系統實務 , 曾守正

THE END!!!

QUESTION?

title: inst.16, a matter of integrity (part3 of 3) date: 28.11.2001 ming-chun chiu page 1...

Documents