Lecture 11: Normalization of Relations: Boyce-Codd Normal Form

Dependency Preservation

If we decompose a relation R into relations R1 and R2, All dependencies of R either must be a part of R1 or R2 or must be derivable from combination of FD's of R1 and R2. A relation R (A, B, C, D) with FD set{A->BC} is decomposed into R1(ABC) and R2(AD) which is dependency preserving because FD A->BC is a part of R1(ABC).

True

It is always possible to obtain a 2NF and 3NF decomposition without sacrificing a lossless join or dependency preservation

Natural join

Links tables by selecting only the rows with common values in their common attributes

Yes

Lossless Decomposition with 3NF

Not a concern

Lossless decomposition with 1NF

Yes

Lossless decomposition with 2NF

Maybe

Lossless decomposition with BCNF

A candidate key of R has multiple attributes

Violation of BCNF is a concern only when

Violates Boyce codd normal form

X ---> Y and X is not a candidate key alone, and Y is an attribute that is part of a candidate key

True

BCNF guarantees elimination of data anomalies due to functional dependencies

R has more than one multivalued candidate key Any two candidates share a common attribute

BCNF may be violated when

True

BCNF may not be able to achieve both lossless join and dependency preservation

1NF 2NF 3NF

Data anomalies exist in what relations

Exist

Data anomalies with 1NF

Exist

Data anomalies with 2NF

Exist

Data anomalies with 3NF

Absent

Data anomalies with BCNF

Not a concern

Dependency preservation with 1NF

Yes

Dependency preservation with 2NF

Yes

Dependency preservation with 3NF

Maybe

Dependency preservation with BCNF

Lossless Join Property

Ensures no extra records are generated when a natural join operation is applied to the relations in the decomposition. All original data is brought back when a relation is decomposed AKA it ensures that no extra records will be brought back

Boyce Codd normal form

Every determinant is a candidate key