Lecture 13Relational Decomposition and Relational Algebra.ppt

Lecture 13:Relational Decomposition and Relational Algebra February 5th, 2003 Summary of Previous Discussion FD’s are given as part of the schema. You derived keys from them. You can also specify keys directly (they’re just another FD) First, compute the keys and the FD’s that hold on the relation you’re considering. Then, look for violations of BCNF. There may be a few. Choose one. Summary (continued) You may need to decompose the decomposed relations: To decide that, you need to project the FD’s on the decomposed relations. The end result may differ, depending on which violation you chose to decompose by. They’re all correct. Relations with 2 attributes are in BCNF. Summary (continued –2) If you have only a single FD representing a key, then the relation is in BCNF. But, you can still have another FD that forces you to decompose. This is all really pretty simple if you think about it long enough. Boyce-Codd Normal Form Example Decomposition Other Example R(A,B,C,D) A B, B C Keys: Violations of BCNF: Correct Decompositions A decomposition is lossless if we can recover: R(A,B,C) R1(A,B) R2(A,C) R’(A,B,C) should be the same as R(A,B,C) Correct Decompositions Given R(A,B,C) s.t. A?B, the decomposition into R1(A,B), R2(A,C) is lossless 3NF: A Problem with BCNF So What’s the Problem? Solution: 3rd Normal Form (3NF) Relational Algebra Formalism for creating new relations from existing ones Its place in the big picture: Relational Algebra Five operators: Union: ? Difference: - Selection: s Projection: P Cartesian Product: ? Derived or auxiliary operators: Intersection, complement Joins (natural,equi-join, theta join, semi-join) Renaming: r 1. Union and 2. Difference R1 ? R2 Example: ActiveEmployees ? RetiredEmployees R1 – R2 Example: AllEmployees -- RetiredEmployees What about Intersection ? It is a derived operator R1 ? R2 = R1 – (R1 – R2) Also e