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
1.
Suppose that you are given a relation R = (A,B,C,D,E) with the following
functional dependencies: {CE → D,D → B,C → A}.
Find
all candidate keys.
Answer: From the given set of functional
dependencies, we can observe that only the attributes C and E are present only
on LHS. Hence, we can try with C and E attributes to find candidate keys.
C
alone cannot determine all the other attributes.
Hence,
C → C
E
alone cannot determine all the other attributes.
Hence,
E → E
C
and E together can form a candidate key.
CE → ABCDE. Hence, CE is the only candidate key for the given relation R.
2.
Suppose that you are given a relation R = (A,B,C,D,E) with the following
functional dependencies:
{BC → ADE, D → B}.
Find
all candidate keys.
Answer: Let us start with LHS of given functional
dependencies;
- From D → B, D cannot uniquely determine the values of all the other attributes. Hence, D alone cannot be a candidate key.
- From BC → ADE, it is very clear that if you know values of B and C, you can determine the values of attributes A, D, and E. Hence, BC → ABCDE is holding. So, BC is one candidate key.
- From D → B, if you know D then you know B. If you know C also, then you can determine all the other attributes. Hence, CD is another candidate key.
3.
You are given the following set of functional dependencies for a relation
R(A,B,C,D,E,F),
F
= {AB → C, DC → AE, E → F}.
What
are the keys of this relation?
Answer:
From the given set of FDs F, it is very evident that we cannot have any one
attribute as the key for R. Hence, we need to check with the different
combination of attributes.
- Let us try this example with the algorithm that is used for finding Attribute Closure. Click in the above link to visit the page.
- Let us start with AB. Assume that the result is AB.
Result
= AB;
From
AB → C, (if you know A and B, then you would
know C) Result = ABC.
We
cannot move further. That is, AB can determine only A, B, and C. Hence, AB cannot be a key.
- Let us try with the other combination ABD.
Result
= ABD;
From
AB → C, Result = ABCD
From
DC → AE, Result = ABCDE
From
E → F, Result = ABCDEF.
At
last, Result includes all the attributes of the relation R. Hence, ABD is one
of the keys for the relation R.
- Let us try with the other combination BCD.
Result
= BCD;
From
DC → AE, Result = ABCDE
From
E → F, Result = ABCDEF.
At
last, Result includes all the attributes of the relation R. Hence, BCD is also
one of the keys for the relation R.