Re: How to normalize this?

On Sunday, 10 February 2013 14:14:42 UTC+1, hugoko..._at_gmail.com wrote:
> Op zaterdag 9 februari 2013 12:50:46 UTC+1 schreef Norbert_Paul het volgende:
>
> > There is an algorithm thad can do this for you:
>
> >
>
> > Google the terms "synthesis algorithm third normal form".
>
>
>
> Hi Norbert,
>
>
>
> Thanks for the pointer. Very useful!
>
>
>
> I used the algorithm on my set of FDs and this resulted in three relations:
>
>
>
> R1: ABCD (ABC --> D)
>
> R2: ABE (AB --> E)
>
> R3: BCE (BC --> E)
>
>
>
> Interestingly, that was the first thing I came up with when thinking about this problem. I thought it would be incorrect because
>
> (a) is has an update anomaly (changing an E value in R2 without making the accompanying change in R3 would result in inconsistent data); and
>
> (b) R3 is redundant, it can be reconstructed by joining R1 and R2.

Hello Hugo,

This is a very nice point, and one that is AFAIK indeed overlooked by many if not all textbooks. But it is also somewhat academic. In some sense you are adding an extra constraint to your database schema that could be formulated as:

"If I take the natural joins of all my relations R1, ..., Rn, and then project the result on a header Hi of Ri, I obtain again Ri."

or equivalently:

"all my relations in my database schema are projections of what was once a single relation"

This holds of course trivially if you have only one relation. Under this constraint your observations are right. But it is usually assumed that this constraint does not really apply and that it is an artifact of the schema that you (perhaps mistakenly) chose as your starting point. So the fact that the normalized schema is in some sense more liberal, because it does not have that constraint, is usually considered a feature and not a bug.

• Jan Hidders