1 / 30

Installment 16 : A Matter of Integrity (Part 3 of 3) Student: Ming-Chun Chiu

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:. OUTLINE. Terminology Review Integrity Support In SQL/89 In SQL/92 In Object-Oriented

binah
Download Presentation

Installment 16 : A Matter of Integrity (Part 3 of 3) Student: Ming-Chun Chiu

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. OUTLINE • Terminology Review • Integrity Support • In SQL/89 • In SQL/92 • In Object-Oriented • Via Stored Procedures • Puzzle Ming-Chun Chiu

  3. Terminology Review • Integrity Support • In SQL/89 • In SQL/92 • In Object-Oriented • Via Stored Procedures • Puzzle Ming-Chun Chiu

  4. 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

  5. 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

  6. OUTLINE • Terminology Review • Integrity Support • In SQL/89 • In SQL/92 • In Object-Oriented • Via Stored Procedures • Puzzle Ming-Chun Chiu

  7. 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

  8. SQL/89 Support • Integrity Enhancement Feature: • Default Value • CHECK • UNIQUE, PRIMARY KEY, FOREIGN KEY Ming-Chun Chiu

  9. Default Value • Syntax of CREATE in SQL/89 CREATE TABLE tablename (column_name datatype [NULL | NOT NULL] [DEFAULT default_value] [column_constraint_clause] ……) Ming-Chun Chiu

  10. CHECK constraint • Declaration clause • Immediate • State • Single-row • Unnamed • No Violation Response • CREATE TABLE EMP …. • CHECK (JOB <> ‘Pgmr’ or SAL < 50000) CREATE INTEGRITY RULE ER7 IF EMP.JPB=“PGMR” THEN EMP.SAL < 50000; Ming-Chun Chiu

  11. 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

  12. Others in SQL/89 • No any referential support They are OPTIONAL in SQL/89!!! Ming-Chun Chiu

  13. SQL/92 Support • Integrity rule names • Domain rules • General multi-row rules • Deferred Checking CREATE TABLE EMP …. CONSTRAINT EMP_FK1 FOREIGN KEY (DEPT#) REFERENCES DEPT(DEPT#); Ming-Chun Chiu

  14. Shared column • 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? Ming-Chun Chiu

  15. 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

  16. CREATE ASSERTION DE20 CHECK ( NOT EXISTS ( SELECT * FROM DEPT WHERE DEPT.BUDGET < 1000000 AND EXISTS ( SELECT * FROM EMP WHERE EMP.DEPT# = DEPT.DEPT# AND EMP.SAL > 100000 ) ) ( 經費少於1,000,000的部門內, 不能有任何員工其薪水超過100,000 ) CREATETABLE DEPT …. CONSTRAINT DE20 CHECK ( DEPT.BUDGET < 1000000 OR NOT EXISTS ( SELECT * FROM EMP WHERE EMP.DEPT# = DEPT.DEPT# AND EMP.SAL > 100000 ) ) • Multi-row Rules • Two Traps: 1) Redundancy Ming-Chun Chiu

  17. EXISTS ( SELECT * FROM T ) EXISTS ( SELECT * FROM T ) • Multi-row Rules (cont.) • Two Traps: 2) Rule R used in table T’s Declaration, CREATETABLE T … CONSTRAINT TNE CHECK ( R ) CREATE ASSERTION TNE CHECK ( R ) OX • IF R is “Table T must not be empty” 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

  18. Deferred Checking DEFERRABLE / 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

  19. 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 ! Data Source: Installment 11,葛煥元 Ming-Chun Chiu

  20. [Reference: Installment 9] • Others issue in SQL/92: • No support for declaring functional dependencies Intelligent !!! Recommend: 宣告完整性是好的DBMS必要條件 (installment 14),盡量在基表Declaration時就作Integrity constraints,盡量少用Procedure來作Integrity constraints. Ming-Chun Chiu

  21. OUTLINE • Terminology Review • Integrity Support • In SQL/89 • In SQL/92 • In Object-Oriented • Via Stored Procedures • Puzzle Ming-Chun Chiu

  22. Object-Oriented Support • Ex: 經費少於1,000,000的部門, 不能有任何員工薪水超過100,000 IN RDB: IN OODB: (via Procedure, i.e MATHOD) CREATE INTEGRITY RULE DE20 IF DEPT.BUDGET < 1000000 AND 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

  23. Object-Oriented Support (cont.) Problems Arising: • 我們可能會無法明確的掌握住系統何時該執行檢查。 • 我們應如何確保所有必要的方式中都能含有必要的檢查碼? • 我們能避免任何檢查碼被忽略嗎? • 針對所有執行相同限制功能的方法中，我們如何確保其錯誤訊息有統一的格式呢? 此外，對於家族特性的的限制條件中，我們如何確保其錯誤訊息有統一的格式呢? • 如果限制式改變，我們該如何找出所有需要被修改的方法? • 我們應如何確保強制檢查碼都是正確無誤的? Ming-Chun Chiu

  24. Object-Oriented Support (cont.) • 我們應如何做延遲檢查? • 我們應該如何去做查詢，去找出某一特定物件(given object)或結合物件(combination of objects)的所有限制式呢? • 在倒資料(Load)或其他作業(utility)的處理中，限制式都能被強制執行嗎? • 如何做語意(semantic)的最佳化? • 完整性檢查碼可以做最佳化嗎? 而他的優良嗎? • 在建立應用程式撰寫或維護期間，使用者生產力又是如何? • RDB: Declare Once, the all in one • ODB: One Constraint  All Related Method 自動化? 人工? Ming-Chun Chiu

  25. OUTLINE • Terminology Review • Integrity Support • In SQL/89 • In SQL/92 • In Object-Oriented • Via Stored Procedures • Puzzle Ming-Chun Chiu

  26. 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

  27. 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

  28. OUTLINE • Terminology Review • Integrity Support • In SQL/89 • In SQL/92 • In Object-Oriented • Via Stored Procedures • Puzzle Ming-Chun Chiu

  29. 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

  30. 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? Ming-Chun Chiu

More Related