Question

Section B. For each question in this section, you are required to list all possible candidate keys for the given
schema based on the functional dependencies provided. You may wish to compute the closure of
your key(s) to confirms they are valid.

Question 1
R [A, B, C, D, E, F, G, H, I, J]
{A} -> {B}
{C} -> {B}
{B} -> {D, E, F, G, H}
{D, F} -> {I, J, A}

Question 2
R [A, B, C, D, E, F, G, H]
{A, B} -> {C, D, E}
{C} -> {A, B, D, E}
{D} -> {G}
{E} -> {F}

Section C. For each question in this section, you are required state and justify the highest normal form of the
relation given a schema and functional dependencies. Your explanations should be brief and concise.
Hint: It may be useful to identify the candidate keys for each relation.

Question 1
R [A, B, C, D, E, F]
{B, C} -> {D, E, F}
{A} -> {D, E, F}

Highest Normal Form: ?
Explanation: ?

Question 2
R [A, B, C, D, E, F, G, H]
{A, B, C} -> {D, E, F, G, H}
{G, H} -> {A, B, C}

Highest Normal Form: ?
Explanation: ?

Answer 1

Section B:

Candidate key(s) is superkey with minimum attributes where superkey is a set of one of one or more attribute that can uniquely identify a tuple in a relation.

Question 1:

Given relation: R [A, B, C, D, E, F, G, H, I, J]
Functional dependencies:
{A} -> {B}
{C} -> {B}
{B} -> {D, E, F, G, H}
{D, F} -> {I, J, A}

Answer: In the given functional dependencies, attribute C is not present in any of the dependencies in the right hand side. Thus, let check the closure of C, i.e. whether attribute C itself can be the candidate key or not.

{C}⁺ = closure of {C} = { C, B, D, E, F, G, H, I, J, A}

Since all the attributes can be derived using the closure of C, C is a candidate key.

Now, in any other combination of keys, if C is not considered in the key, C cannot be derived on the right hand side. Thus, all other possible keys should contain C, which will become superkey. Thus, only one candidate key is possible, which is C.

Candidate key(s): {C}

Question 2:

Given relation: R [A, B, C, D, E, F, G, H]
Functional dependencies:
{A, B} -> {C, D, E}
{C} -> {A, B, D, E}
{D} -> {G}
{E} -> {F}

Answer: In the given functional dependencies, H is not present in any of the functional dependencies. Thus, attribute H must be present in the candidate key.

Now, closure of {A,B,H} = {A,B,H}⁺ = { A, B, H, C, D, E, G, F}

Since all the attributes of R can be derived, {A,B,H} is a candidate key.

On the other hand, closure of {C, H} = {C, H}⁺ = {C, H, A, B, D, E, G, F}

So, {C, H} is also a candidate key.

Any other combination of keys to generate all the attributes will overlap the existing candidate keys. Thus, two candidate keys are possible.

Candidate keys: {A, B, H}, {C, H}

Section C:

The highest normal form of a relation can be determined by checking the functional dependencies along with the candidate key(s).

Question 1:

Given relation: R [A, B, C, D, E, F]
Functional dependencies:
{B, C} -> {D, E, F}
{A} -> {D, E, F}

Explanation: At first, the candidate key(s) of the relation has to be checked. Here, attribtues A,B,C are not present in the right hand side of the functional dependencies. Thus, closure of {A, B, C} = {A, B, C}⁺ = {A, B, C, D, E, F}

So, {A, B, C} is the candidate key.

Now, in both of the functional dependencies, the part of the key is determining something, i.e. part of the key {B,C} is defining some other attribtues, as well as part of the key {A} is determining some other attributes. Thus, partial dependency is present here. (in 2NF, partial dependency is removed)

So, the given relation is in 1NF.
Highest Normal Form: 1NF

Question 2:

Given relation: R [A, B, C, D, E, F, G, H]
Functional dependencies:
{A, B, C} -> {D, E, F, G, H}
{G, H} -> {A, B, C}

Explanation: Here, the keys are, {A,B,C} and {G,H} , which can determine all the attributes of the relation R. The keys can be considered to be superkey too.

Now, in both the functional dependencies, the left hand side is the superkey. According to the definitions of normal forms, if functional dependency X->Y is defined where X is a superkey, then the relation is said to be in BCNF.

Here, in both the functional dependencies, superkeys are determining other attributes. So, the relation R should be in BCNF.

Highest Normal Form: BCNF.