1 / 17

Installment Number 11 Expression Transformation ( part 2 of 2 )

Installment Number 11 Expression Transformation ( part 2 of 2 ). 690530004 碩一葛煥元. Index. Other Types of Expression Conjunctive Normal Form Expression Semantic Transformations Optimization Inhibitors Duplicate rows 3 VL Dynamically deferred constraints. Other Types of Expression.

clinton-gomez
Download Presentation

Installment Number 11 Expression Transformation ( part 2 of 2 )

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 Number 11Expression Transformation ( part 2 of 2 ) 690530004 碩一葛煥元 Expression Transformation ( 2 / 2 )

  2. Index • Other Types of Expression • Conjunctive Normal Form Expression • Semantic Transformations • Optimization Inhibitors • Duplicate rows • 3VL • Dynamically deferred constraints Expression Transformation ( 2 / 2 )

  3. Other Types of Expression • A * B + A * Ctransform A *（ B + C ） • true-valued expressionA > B AND B > 3transformA > B AND B > 3 AND A > 3(‘>’ is transitive) Expression Transformation ( 2 / 2 )

  4. Conjunctive Normal Form Expression • A > B OR （C = D AND E < F）transform（A > B OR C = D ） AND ( A > B OR E < F) • C1ANDC2AND … ANDCnis true only if every conjunct is trueis false if any conjunct is false • evaluate all of the conjuncts in parallel Expression Transformation ( 2 / 2 )

  5. Semantic Transformations ex.1 • foreign-to-primary-key join Expression Transformation ( 2 / 2 )

  6. Semantic Transformations ex2 (1/3) • constraints：All red parts must be stored in London • Find suppliers who supply only red parts and are located in the same city as at least one of the parts the supply Expression Transformation ( 2 / 2 )

  7. Semantic Transformations ex2 (2/3) • Find suppliers who supply only red parts and are located in the same city as at least one of the parts the supply • CREATE VIEW SC ASSELECT S# , CITYFROM SP WHERE NOT IN (SELECT S# FROM SP WHERE P#<>’red’)ORDER BY CITY; • SELECT S# FROM SC WHERE city=‘???? ’; Expression Transformation ( 2 / 2 )

  8. Semantic Transformations ex2 (3/3) • transformFind London suppliers who supply only red parts. • SELECT S# FROM SP WHERE NOT IN (SELECT S# FROM SP WHERE P#<>’red’)AND city=‘London’ ; Expression Transformation ( 2 / 2 )

  9. Optimization Inhibitors • Duplicate rows • 3-Valued logic • Dynamically deferred constraints Expression Transformation ( 2 / 2 )

  10. Duplicate rows (1/2) • List part numbers for parts that either are screws or are supplied by supplier S1 ,or both. Expression Transformation ( 2 / 2 )

  11. Duplicate rows (2/2) Expression Transformation ( 2 / 2 )

  12. 3VL (3-Valued logic) • p OR NOT p • 3VL Expression Transformation ( 2 / 2 )

  13. Dynamically deferred constraints • DEPT (Dept#, DeptName, Emp#,…)EMP (Emp#, EmpName,…) • SELECT * FORM DEPT,EMP; • SELECT * FORM DEPT,EMPWHERE DEPT.Emp#=EMP.Emp#AND … ; (if each employee is only belong to one department) Expression Transformation ( 2 / 2 )

  14. Puzzle • SELECT E#FROM DEPT,EMPWHERE NOT ( DEPT.D# = EMP.D# AND EMP.D# = ‘D1’ ); • (DEPT , EMP satisfy the primary-to-foreign key) • real-world • delivered by the query as stated • apply predicate transitive closure Expression Transformation ( 2 / 2 )

  15. Answer a NOT (DEPT.D# = EMP.D# AND EMP.D# = ‘D1’); Answer:E1 Expression Transformation ( 2 / 2 )

  16. Answer b • 3VL NOT (DEPT.D# = EMP.D# AND EMP.D# = ‘D1’); NOT (DEPT.D# = unknown) OR NOT (EMP.D#=‘D1’) Answer:Empty Expression Transformation ( 2 / 2 )

  17. Answer c NOT (DEPT.D# = EMP.D# AND EMP.D# = ‘D1’); NOT DEPT.D# = ‘D1’ OR NOT EMP.P# =‘D1’ Answer:E1 Expression Transformation ( 2 / 2 )

More Related