Re: How to normalize this?

hugokornelis_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!
You are welcome.

> 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.
>
> I am not really surprised at (b), because this does not follow from just the FDs, but from a combination of the FDs and the original design, a single relation ABCDE, which implies that there will be no (AB) or (BC) value combinations in R2 or R3 that are not in R1. That fact is not an input in Bernsteins algorithm, so it makes sense that all three relations are produced.
>
> I am surprised at (a). I was under the impression that a fully normalized is free of modification anomalies. I checked the result of Bernsteins algorithm against the higher normal forms, but found no violation.
>
> So either:
> (i) I made a mistake when applying Bernsteins algorithm (improbable, it's very straightforward and I had only three FDs to begin with), or
> (ii) I overlooked a violation of one of the higher normal forms (possible, I learned normalization from one of the idiots who think that 3NF is enough an don't bother with the higer forms, so I had to study that later, without help), or
> (iii) I was misinformed about full normalization eliminating ALL modification anomalies, and found a case where it doesn't. And where it's maybe not even possible to achieve a relational design without modification anomalies.
>
> Do you see any flaws in my reasoning?

I must admit that I didn't have a closer look at your FDs yet. So still without going deeper into them I guess that (ii) holds because Bernsteins algorithm only guarantees 3NF and does not guarantee BCNF, for example.
Try to avoid words like "idiot". It is often possible to write statements in a friendly manner that make the reader think these words.

Cheers
Norbert Received on Mon Feb 11 2013 - 10:09:55 CET