TOPICS (Click to Navigate)
Monday, 1 February 2016
Normalization solved exercise 5
Consider a relation R(A, B, C, D) with FD's AB → C, AC → B, BC → A, B → D.
Determine all the keys of relation R. Is the relation R in BCNF?
We can derive the values of C and D uniquely from the FDs AB → C, and B → D. And the closure of AB, i.e., (AB)+ = ABCD. Hence, AB is one of the keys.
We can derive the values of B and D uniquely from the FDs AC → B, and B → D (Transitive FDs). (AC)+ = ABCD. Hence, AC is one of the keys.
We can derive the values of A and D uniquely from the FDs BC → A, and B → D. and the closure (BC)+ = ABCD. Hence, BC is one of the keys.
The keys are AB, AC, and BC.
Is R in BCNF?
Requirements: R should be in 2NF, 3NF, and every determinant must be a candidate key.
Partial key dependency is present – In the functional dependency B → D, the determinant B is not a key. But it is a part of the candidate keys AB and BC. If part of any candidate keys can uniquely identify another (or set of) non-key attribute, we call that as partial key dependency.
Hence, R is not in 2NF. We need to decompose R into the following relations so that we can make 2NF relations out of R;
R1(A, B, C) and R2(B, D).
R1 and R2 do not have partial key dependencies, and transitive dependencies. Hence, both are in 2NF and 3NF.
The determinants are the keys in both the relations. Hence, R1 and R2 are in BCNF.
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...