Given the relation R and the following functional dependencies, answer the follo

Question

Given the relation R and the following functional dependencies, answer the following questions. R(A, B, C, D, E) All attributes contain only atomic values.

FD1: A ? BC FD2: CD ? E FD3: B ? D FD4: A ? E

(a) Compute A+, the attribute closure of attribute A.

(b) List the candidate keys of R.

(c) What’s the highest normal form that R satisfies and why?

(d) If R is not already at least in 3NF, then normalize R into 3NF and show the resulting relation(s) and their candidate keys. Your decomposition should be both join-lossless and dependency-preserving. If R is already in 3NF, just list the candidate keys of R.

(e) Is your answer to (d) also in BCNF? Why or why not?

Explanation / Answer

a) Attribute closure of A is Attributes which can be derived from any attribute A. In this case, B , C and E can be derived from A. So these are also a part of closure. Therefore A+={A,B,C,D,E}

b) First we create closure of all the attributes

Closure(A)={A,B,C,E,D}  [Reason: A can self derive A and B,C, B can derive D and D can derive E hence by transitivity rule A can derive D also]

Closure (B)={B,D,E}

Closure(C)={C,E}

Closure (D)={D,E}

Closure(E)={E}

As A can derive all the attributes of the relation, here A is the candidate key.

c) Since A is the candidate key so it is a prime attribute and rest B,C,D,E are non prime attributes. The relation R is in 1st normal form as a relational DBMS does not allow multi-valued or composite attribute. The relation R is in second normal form because no non-prime attributes have part-key dependencies on candidate key. But the relation is not in third normal form because all the attributes in a table are not determined only by the candidate key of that relation. So the highest normal form is Second normal form.

d) Check each FD in the set F1 for violation of 3NF, and split table accordingly.
Checking FD a --> b,c
FD does not violate 3NF
Checking FD c,d --> e
The FD violates 3NF as its LHS is not a superkey (and RHS is a set of non-key attributes).
The following 3NF table is obtained:
c,d,e
with FDs
c,d --> e
Checking FD b --> d
The FD violates 3NF as its LHS is not a superkey (and RHS is a set of non-key attributes).
The following 3NF table is obtained:
b,d
with FDs
b --> d

Finally, add the following table into normalized 3NF table set (obtained by removing RHS attributes of FDs using which we produced a table):
a,b,c
with FDs
a --> b,c

e) Yes this relation is also in BCNF because does not have multiple overlapping candidate key.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.