Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe
Achieving the BCNF by Decomposition (2)
Three possible decompositions for relation TEACH
D1: {student, instructor} and {student, course}
D2: {course, instructor } and {course, student}
D3: {instructor, course } and {instructor, student}
All three decompositions will lose fd1.
We have to settle for sacrificing the functional dependency
preservation. But we cannot sacrifice the non-additivity property
after decomposition.
Out of the above three, only the 3rd decomposition will not generate
spurious tuples after join.(and hence has the non-additivity property).
A test to determine whether a binary decomposition (decomposition
into two relations) is non-additive (lossless) is discussed under
Property NJB on the next slide. We then show how the third
decomposition above meets the property.
Slide 14- 52