Re: How to normalize this?

On 2013-02-10, hugokornelis_at_gmail.com <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!
>
> 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 think (i) applies, the mistake being not giving the algorithm all the information. Given _only_ the 3 FDs and nothing else, is updating

  (a1, b1, c1, d1)
  (a1, b1, e1)
  (b1, c1, e1)

  (a1, b1, c1, d1)
  (a1, b1, e1)
  (b1, c1, e2)

The other possibility is that the new set of FDs is still wrong, and there is other information that that has not come to light. You may have to go back to the real world and make sure you have the complete set of FDs.

Eric

-- 
ms fnd in a lbry