Consider a relation R with five attributes A, B, C, D, and E. You are given the
ID: 3852348 • Letter: C
Question
Consider a relation R with five attributes A, B, C, D, and E. You are given the following functional
dependencies: A --> B, BC --> E, and ED --> A.
Is R in BCNF? If it is not, decompose it into a collection of BCNF relations.
For this, R is not in BCNF because none of A, BC, and ED contain a key. It cannot decompose into BCNF as other violations like AC, BD, or CD do not satisfy BCNF. If we further decompose it like ABC, or ABD, these relations also are not in BCNF.
So how is it decomposed into a collection of BCNF?
Explanation / Answer
First step: Identify the candidate keys.
CANDIDATE KEYS ARE: EDC,ACD,BCD
Now, just check for the conditions of each normal form.
We can see A->B.
ACD is the candidate key and A is a partial key. But B is a prime-attribute Hence, this is not a partial FD.
Similarly in BC->E and ED->A, E and A are prime-attributes and hence both are not partial functional dependencies. Hence R is in 2NF.
3NF states that every non-prime attribute must be dependent on the candidate key. In the given functional dependencies, all dependent attributes are prime-attributes and hence it is trivially in 3NF.
Now BCNF requires that for every FD >>, must be a super key (candidate key or its superset). This condition is violated by all the 3 FDs given and hence R is not in BCNF.
schema R IN 3NF