databasedatabase-normalization3nf

Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J}. What is the key for R?

Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J}. What is the key for R? Decompose R into 2NF and then 3NF relations?

Solution

  • Attributes appearing on the left of FDs are {A, B, D, H}. From these all but {H} seem plausibly part of keys. Calculating the closures of likely candidates gives:

    {A, B}
+ = {A, B, C, I}
{B, D}
+ = {B, D, E, F}
{A, D}
+ = {A, D, G, H, I, J}
{A, B, D}
+ = {A, B, C, D, E, F, G, H, I, J}

    So {A, B, D} is the only candidate key

    Decomposing attributes based on relations partially dependent on the key gives:

    R1 = {A, B, C}
R2 = {B, D, E, F}
R3 = {A, D, G, H, J}
R4 = {A, I}
R5 = {A, B, D}

    Relation R5 is kept to preserve the original primary key

    Further decomposing attributes base on transitive dependencies keeps R1, R2, R4, and R5 from above but splits R3 into:

    R3a = {A, D, G, H}
R3b = {H, J}