Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set o

Question

Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of
functional dependencies
a) F = { {A, B}->{C}, {A}->{D, E }, {B}?{F}, {F}->{G, H}, {D}->{I, J} }
b) F = {{A, B}->{C}, {B, D}->{E, F }, {A, D}->{G, H}, {A}->{I}, {H}->{J} }.
What is the key for R? Decompose R into 2NF and then 3NF relations.
(Please do not use DBD system and Prolog)

Explanation / Answer

A minimal set of attributes whose closure includes all the attributes in R is a key. Since the closure of {A, B}, {A, B}+ = R, one key of R is {A, B}. {A}+ = {A, D, E, I, J}. Hence {A} -> {D, E, I, J} ({A} -> {A} is a trivial dependency {B}+ = {B, F, G, H}, hence {A} -> {F, G, H} ({B} -> {B} is a trivial dependency) To normalize into 2NF (remove the attributes that are functionally dependent on part of the key (A or B) from R and place them in separate relations R1 and R2) and part of the key they depend on (A or B). R1 = {A, D, E, I, J}, R2 = {B, F, G, H}, R3 = {A, B, C} To normalize into 3NF (the transitive dependencies in R1, R2, R3) are : R1 has the transitive dependency {A} -> {D} -> {I, J}, so we remove the transitively dependent attributes {I, J} from R1 into a relation R11 and copy the attribute D they are dependent on into R11. The remaining attributes are kept in a relation R12. Hence, R1 is decomposed into R11 and R12 as follows: R11 = {D, I, J}, R12 = {A, D, E} The relation R2 is similarly decomposed into R21 and R22 based on the transitive dependency {B} -> {F} -> {G, H}: R21 = {F, G, H}, R22 = {B, F} The final set of relations in 3NF are {{ D, I, J}, {A, D, E}, {F, G, H},{B, F}, {A, B, C}}

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---