Question

Question 1: Functional Dependencies [7 marks Consider a relation R on attributes (A, B, C, D, E, F,G, H) and the following functional dependen- cies. B →G C →D DE →GC → EF DEF → H (a) What is the closure of [F, G, Hy? (b) List all of the candidate keys of R under the dependencies above. (c) List all of the FDs above that are 3NF violations (d) List all of the FDs above that are BCNF violations. This list will include all of the FDs from part (c), since a 3NF violation is automatically also a BCNF violation.

Answer 1

(a) Closure of a relation : Closure of a set of functional dependencies. A Closure is a set of FDs is a set of all possible FDs that can be derived from a given set of FDs. It is also referred as a Complete set of FDs. If F is used to donate the set of FDs for relation R, then a closure of a set of FDs implied by F is denoted by F⁺.

Therefore closure of {F,G,H} = EFGH

(b) Candidate Key: Candidate key is a set of attributes that uniquely determines a relation.

Candidate key of above relation: ACGDEF

(c) A relation is in 3NF under following two conditions:

(i) For FD A->B,

A is a superkey for R.

(ii) B is not a prime attribute, that is B is not a part of candidate key.

Therefore, ACG->B is in 3NF.

C->D is in 3NF.

DE->GC is in 3NF.

3NF violations FD's:

B->G.

G->A.

H->EF.

DEF->H.

(iv) An FD is in BCNF if it satisfies the following condition:

For a relation R: A->B, A is a superkey for R.

BCNF violation:

ACG->B, B->G, C->D, G->A, DE->GC, H->EF, DEF->H.