Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? / Sets of examples to find the keys of a tables / Process of Key finding in a database – Examples / Normalization to BCNF.
- The relation R is in 1NF.
- R is not in 2NF. Why?
- 2NF says “no partial functional dependencies”. In other words, all the non-key (non-prime) attributes must fully functionally dependent on the key or whole key.
- This is violated here. For example, if we say ABD as the key (because ABD determines all the attributes of R uniquely), D determines the value of E which is a non-key attribute. This means, D alone would be enough to uniquely determine E. This is called as partial functional dependencies (or partial key dependency).
- This happens with other candidate keys also.
- The solution is to decompose R into two or more relations.
- Create a separate relation for each partial dependency.
- Remove the right hand side (RHS)
attributes of partial dependency from the relation that is to be decomposed.
Key for R21 are AB, BC, and AC. R21 has no partial dependency, hence in 2NF, no transitive dependency of non-key attributes hence in 3NF and the LHS of all FDs are keys hence in BCNF.
Key for R22 is (ABD). As all attributes have formed the key, R22 is in 2NF, 3NF and BCNF.