Normalization - solved exercises set 2

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.