Database 1
Database Design
- Prof. dr. Paul M.E. De Bra
- Dept. of Computer Science
- Eindhoven Univ. of Technology
Lossless Join Decomposition
- Lossless Join means:
- Let { R1 , R2 } be a decomposition of R (meaning that R1 R2 = R); the decomposition is lossless if for every legal instance r of R: r = R1(r) R2(r)
- What is wrong with the following decomposition?
- R = {A,B,C} and F = { A B, C B} and we replace R by { R1 , R2 } where R1 {A,B} and R2 = {C,B}.
Boyce-Codd Normaalvorm
- A relation scheme R is in BCNF if (and only if) for every non-trivial fd X Y F+ X is a superkey (for R).
- A database scheme D = {R1,..., Rn} is in BCNF if (and only if) i {1,...,n}: Ri is in BCNF.
- Let R = {A,B,C} and F = { A B, C B} and let us decompose R into by { R1 , R2 } where R1 {A,B} and R2 = {C,B}. Is this decomposition in BCNF? Is this the “best” decomposition in BCNF? (Can you find a better one?)
Sufficient Condition for Lossless Join
- Lossless Join means:
- Let { R1 , R2 } be a decomposition of R (meaning that R1 R2 = R);
- Prove that for all legal instances r: r R1(r) R2(r)
- Prove that this decomposition is lossless if R1 R2 R1 or R1 R2 R2
- Can you give an example of a lossless join decomposition (instance) when neither R1 R2 R1 nor R1 R2 R2 hold?
Dependencies in a decomposition
- Which dependencies hold in the R1 and R2?
- R = {A,B,C} and F = { A B, B C} and we replace R by { R1 , R2 } where R1 {A,B} and R2 = {B,C}.
- R = {A,B,C} and F = { A B, C B} and we replace R by { R1 , R2 } where R1 {A,B} and R2 = {A,C}.
- R = {A,B,C} and F = { A B, B C} and we replace R by { R1 , R2 } where R1 {A,B} and R2 = {A,C}
Preparation for colstruction 7
- Study chapter 7 up to and including section 7.7
- This deals with third normal form and dependency preserving decomposition