5. Normalize the Following Relations to 3NF, Using the Set of Functional Dependencies Listed

5. Normalize the following relations to 3NF, using the set of functional dependencies listed:

R1(ABCDEFGH)

FDs: ABC —> D, B —> E, A —> F, C —> GH

R2(ABCDEFGH)

FDs: AB —> CF, C —> DE, F —> G, G —> H

R3(ABCDEFGH)

FDs: ABC —> E, E —> F, C —> DE, B —> GH

You should assume that there are no duplicate rows present and that all values are atomic. Your submission should clearly identify the steps required to normalize the relation to 3NF and demonstrate that the solution is lossless and preserves all functional dependencies

For each relation, the candidate key must be determined. To find the candidate/primary key to the relationship, it is the key that is capable of functionally determining all other attributes in the database. Then the next step is to assume that these relations have rows of information on all separate lines or first normal form (1NF). And then to move to second normal form, 2NF, the partial dependencies are fixed. In order to remove the partial dependencies: if only part of the candidate key is needed to determine an attribute, then that is a partial dependency and must split those tables up. And the final move to third normal form, 3NF, is to remove transitive dependencies. That is any relation that has functional dependencies X ® Y and Y ® Z, where X is a key must be eliminated and split into separate tables. To prove that it is not lossless: Rx Ç Ry = Rx or Ry where Rx and Ry are two relationships that are being compared to each other.

In relation R1, ABC is the candidate key because ABC is needed to determine D and the solo keys can predict everything else: A can predict F, B can predict E, and C can predict GH. It is assumed that R1 starts with 1NF, so to move this to 2NF, the partial dependencies must be eliminated. There is a partial dependency here with B ® E, A ® F, and C ® GH with the candidate key ABC as A/B/C is only a part of ABC and not the whole key. Thus these FDs must split up into 3 separate tables.

R1 = R1 (ABCD) FD : {ABC ® D}

R2 (BE) FD : {B ® E}

R3 (AF) FD : {A ® F}

R4 (CGH) FD : {C ® GH}

Since there is no problems with transitive dependency no X ® Y ® Z, then it is also in 3NF. Last is to check if it preserves lossless properties and functional dependencies:

R1 Ç R2 = (ABCD) Ç (BE) = B. Using B ® E, B ® BE trivial and true because (B ® B).

R1 Ç R3 = (ABCD) Ç (AF) = A. Using A ® F, A ® AF which is trivial (and true).

R1 Ç R4 = (ABCD) Ç (CGH) = C. Using C ® GH, C ® CGH which is trivial (and true).

For relation R2, the candidate key is AB because AB can determine C and F which can determine D, E, and G, and then can determine H (which is all of the attributes). The table is assumed to be in 1NF and it is also in 2NF because there are no partial dependencies on the candidate key AB. There is though transitive dependency on each group of functional dependency, so for each transitive dependency needs to be split up.

R2 = R1 (ABCF) FD : {AB ® CF}

R2 (CDE) FD : {C ® DE}

R3 (FG) FD : {F ® G}

R4 (GH) FD : {G ® H}

And then the last step is to check for lossless properties:

R1 Ç R2 = (ABCF) Ç (CDE) = C. Using C ® DE, C ® CDE which is trivial (and true).

R1 Ç R3 = (ABCF) Ç (FG) = F. Using F ® G, F ® FG which is trivial (and true).

R3 Ç R4 = (FG) Ç (GH) = G. Using F ® G, F ® G which is trivial (and true).

For relation R3, the candidate key is ABC because ABC can determine D, E, G, and H and E can determine F. This table does have partial dependency problems for ABC ® E between C ® DE and B ® GH. Thus these tables have to be split up.

R3 = R1 (ABCEF) FD : {ABC ® E, E ® F}

R2 (CDE) FD : {C ® DE}

R3 (BGH) FD : {B ® GH}

Now it is in 2NF. The last step is to remove the transitive dependency between ABC ® E ® F.

R3 = R1 (ABCE) FD : {ABC ® E}

R2 (EF) FD : {E ® F}

R3 (CDE) FD : {C ® DE}

R4 (BGH) FD : {B ® GH}

And then the last step is to check for lossless properties:

R1 Ç R2 = (ABCE) Ç (EF) = E. Using ABC ® E, ABCE ® E which is trivial (and true).

R1 Ç R3 = (ABCE) Ç (CDE) = CE. Using C ® DE, CE ® CDE which is trivial (and true).

R1 Ç R4 = (ABCE) Ç (BGH) = B. Using B ® GH, B ® BGH which is trivial (and true).