Re: How to normalize this?
Date: Wed, 1 May 2013 18:35:15 -0700 (PDT)
Message-ID: <36bb212d-47ae-4cb0-bf2b-a926ff2173d4_at_googlegroups.com>
On Tuesday, April 30, 2013 3:17:44 PM UTC-7, Erwin wrote:
> Op dinsdag 30 april 2013 23:39:58 UTC+2 schreef Jan Hidders het volgende:
>
> > On 2013-04-30 14:21:47 +0000, Erwin said:
> > >> R1(a, b, c, d)
> > >> R2(a, b, e)
> > >> R3(b, c, e)
> > > In particular, that at all times R1 JOIN R2 === R1 JOIN R3.
> > But on the other hand, this type of restriction is often an
> > artifact of the initial table design
No, it is not an artifact. The fact that it was a single-table schema is irrelevant to normalization. The normalized design will have such a constraint exactly when the original does. The original schema, involving a single table, is capable of representing any predicate on its attributes including the one with the constraint and the one without.
> > And the getting rid of it is actually a feature and not a
I agree that people come to understand that they had the wrong design. But the feature-not-bug situation is that it is noticed that the mistaken predicates and their conjunction are actually the correct design. Likely it is never noticed that the mistaken predicates are in fact mistaken, and the discrepancy between their conjunction and the original predicate is attributed, as by you and Erwin, to starting normalization from a single table. Normalization didn't have anything to do with the fact that the user chose an original predicate that was wrong for the design and that they misinterpreted the normalization-produced predicates as ones that were right for the design.
> It's often bothered me that normalization theory/procedure seems to quietly ignore the notion of "nullability or not" of any of the attributes in the "initial table design" ...
Eg: Given predicate R(a,b,c,d,e) the normalized relations have predicates
R1: EXISTS e R(a,b,c,d,e)
R2: EXISTS c,d R(a,b,c,d,e)
R3: EXISTS a,d R(a,b,c,d,e)
where R1(a,b,c,d) AND R2(a,b,e) AND R3(b,c,e) iff R(a,b,c,d,e). But people erroneously think the Ri can hold any old values. They come up with different predicates that instead of being of the form "...a...b...c...d...some e..." are of the form "...a...b...c...d...". When these mistaken predicates are conjoined they give a predicate that R(a,b,c,d,e) implies but might not be logically equivalent. It will be non-equivalent if the mistaken meanings differ from the correct meanings. Ie if it is possible for the "...a...b...c...d..." version to hold when there isn't some appropriate e value.
> > bug.
>
> Iow, that normalization theory per se doesn't actually allow to determine when and when not the phenomenon is "an artifact of the iniital table design".