. Given the following table definition and constraints: R (A, B, C, D, E, F, G,

Question

. Given the following table definition and constraints:

R (A, B, C, D, E, F, G, H, I, J)

            FD = { A,B,C,D -> E, F, G, H, I J

                        B, C, D -> E, F

                        A -> G, H, I, J

                        E -> F

                        H, I -> J }

4a. Based on the FDs above, what is the primary key of the table?

4b. Is the table definition in 1NF? Why or why not? If not, convert the table to 1NF table(s).

4c. Is/are the table(s) in 2NF? Why or why not? If not, convert the table(s) to table(s) that are in 2NF. Explain why they are now in 2NF?

4d. Are the tables in 3NF? Why or why not? If not, convert the table to 3NF. Explain why they are now in 3NF?

4e. List all of the foreign keys in question 4d. Identify them by the table name and state which table(s) and corresponding attribute(s) they relate to.

Explanation / Answer

R (A, B, C, D, E, F, G, H, I, J)

FD = { A,B,C,D -> E, F, G, H, I J

                        B, C, D -> E, F

                        A -> G, H, I, J

                        E -> F

                        H, I -> J }

4A. Based on the FDs above, what is the primary key of the table {ABCD}

4b. Yes ,table definition in 1NF. As per 1NF defination saying all attribute values are atomic.Here E,F,G,H,I,J depend on A,B,C,D

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

4C. No, above tables are not 2NF because there are partial dependencies on repeating groups so they are not 2NF

R = {A, B, C, D, E, F, G, H, I, J} includes partial dependencies

If we convert tables on 2NF.

G,H,I and J depend on A,F depend on E and E depend on B,C,D, and J,F and G don't depend on the key (directly) at all.

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

R1 = {A,G,H,I,J}

R2 = {B,C,D,E,F}

R0, R1, and R2 contain no partial dependencies (or repeating groups) so they are 2NF.

4d. Yes, These tables are 3NF because It satisfies the following conditions:

By transitive functional dependency, we mean we have the following relationships in the table: A is functionally dependent on B, and B is functionally dependent on C. In this case, C is transitively dependent on A via B.

4e.

R1 = {A,G,H,I,J}

R2 = {B,C,D,E,F}

J depend on H,I and F depend on E

R1 = {A,G,H,I,J}

R1a = {A,G,H,I}

R2 = {B,C,D,E,F}

R2a = {B,C,D}

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

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