Normalizing a table with 5 attributes A, B, C, D, and E

0
votes

This is a pretty basic question from early on in the semester, which I got wrong. I am asking this for a better and more accurate answer from others in preparation for the final exam.

Normalizing a table with 5 attributes A, B, C, D, and E. Where A and B is a composite key. With no other prime attributes.

a. If you have a partial dependency B to C, what would you do to normalize the relation?

b. If you have a dependency D to E, what would you do to normalize the relation?

1
databaserelational-databasedatabase-normalization
What have you got so far? What comes to mind? What does "partial FD" bring mind in terms of the definitions or algorithms for the NFs? Or the combination of "FD" and "non-prime attribute" (D & E)? When you look at the NF definitions, do a & b suggest any violations? – philipxy

1 Answers

2
votes

Normalization is something that can be done only trough Functional Dependencies. So let's reformulate you question using them.

Normalizing a table with 5 attributes A, B, C, D, and E. Where A and B is a composite key. With no other prime attributes.

This means that we have a relation schema

R(A, B, C, D, E)

with the only non-trivial functional dependency:

A B → C D E

This relation is in Boyce-Codd Normal Form (BCNF) as well as in Third Normal Form (3NF).

If you have a partial dependency B to C, what would you do to normalize the relation?

Now we add the dependency B → C, which violates both the BCNF (that require that each determinant be a superkey) and the 3NF (that tolerates non-superkyes determinant if the determinate is a prime attribute, that is an attribute which belongs to any key, and C is not a prime attribute since the only key is A B).

In this case the normalization is simple, we decompose the original relation R in two relations, the first, R1(B, C), that represent the information that ties B and C, so that we can know for each value of B which is the only corresponding value of C, the second R2(A, B, D, E) that represents the fact the the values of D and E are uniquely determined by a couple of values A and B. The two relations R1 and R2 are both in BCNF and in 3NF, since the key of R1 is B, while the key or R2 is A B.

It is worth mentioning the fact that this decomposition is loss-less and dependency preserving.

If you have a dependency D to E, what would you do to normalize the relation?

Also in this case the strategy is to decompose R in two relations, this time in R1(A, B, C, D) and R2(D, E). Again, we can note that both relations are in BCNF and in 3NF, and that data and dependencies are preserved.