TOPICS (Click to Navigate)
Sunday, 31 January 2016
Normalization solved exercise 3
Consider a relation R(A, B, C, D, E) with FD's AB → C, CD → E, C → A, C → D, D → B.
The possible candidate keys for R are AB, AD, C
(a) List all the functional dependencies that violate 3NF. If any, then decompose R accordingly.
(b) List all the FD's that violate BCNF. If any, then decompose R accordingly.
To be in 3NF, the following points should not be violated by any of the FDs;
(i) R should be in 2NF;
According to the properties that are to be held for 2NF (No partial functional dependencies), all the non-prime attributes (E is the only non-prime attribute in our case) depend on prime attributes (A, B, C, and D in our case) or keys as a whole. No partial dependencies found. Hence, we would say that R is in 2NF.
(ii) Transitive functional dependencies of non-prime attributes on candidate keys are prohibited;
In the list of functional dependencies, we found no non-key dependencies. That is, no non-prime attribute is functionally dependent on another non-prime attribute.
From points (i) and (ii), it is clear that there are no FDs that violate 3NF. Hence, we would say that R is in 3NF.
R can be in BCNF if and only if R is in 3NF and every determinant is a candidate key.
From the solution (a), you would understand that R is in 3NF. But every determinant is not a candidate key. In the given set of FDs, D → B is the only functional dependency that violates this rule. That is, D is not a candidate key on its own. Hence, the relation R is not in BCNF.
To convert R into a BCNF relation, we need to decompose R using the FD D → B. Then we shall get the following relations R1 and R2;
R1 (D, B) and R2 (A, C, D, E)
Go back to Normalization - Solved Exercises page
SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...
Advanced concepts in DBMS Advanced Database Topics (Click on the links to navigate) Advanced Concepts in D...
Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? /...
Query Processing in DBMS / Steps involved in Query Processing in DBMS / How is a query gets processed in a Database Management System? / Q...