Re: Multiple-Attribute Keys and 1NF

On Aug 28, 5:43 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> JOG wrote:
> > On Aug 28, 5:05 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
> >>JOG wrote:
>
> >>>I am still fighting with the theoretical underpinning for 1NF. As
> >>>such, any comments would be greatfully accepted. The reason for my
> >>>concern is that there /seems/ instances where 1NF is insufficient. An
> >>>example occurred to me while I was wiring up a dimmer switch (at the
> >>>behest of mrs. JOG, to whom there may only be obeyance). Now I don't
> >>>know the situation in the US, but in the UK a while back the colour
> >>>codes for domestic main circuit wiring changed. Naturally the two
> >>>schemes exist in tandem, as exhibited in every house I've had the joy
> >>>of doing some DIY in:
>
> >>>Brown -> live.
> >>>Red -> live
> >>>Blue -> neutral.
> >>>Black -> neutral.
> >>>Green and yellow -> earth.
>
> >>>The issue with encoding these propositions is that the candidate key
> >>>for each proposition may consist of one _or_ two colours. Now I have a
> >>>couple of options, none of which seem satisfactory. I could leave
> >>>green & yellow as some sort of set-value composite, but obviously this
> >>>would affect my querying capabilities, so thats out straight off the
> >>>bat. Similarly adding attributes Colour1 and a nullable Colour2 is
> >>>simply so hideous it isn't worth consideration. So, I could ungroup to
> >>>give me:
>
> >>>Colour Type
> >>>-----------------
> >>>Brown live
> >>>Red live
> >>>Black neutral
> >>>Blue neutral
> >>>Green earth
> >>>Yellow earth
> >>>-----------------
>
> >>>But again this is unsatisfactory as I have lost the information that
> >>>one wire is green and yellow, but none is brown /and/ red.
>
> >>>I could introduce a surrogate to give me:
>
> >>>Id Colour Type
> >>>-----------------
> >>>1 Brown live
> >>>2 Red live
> >>>3 Black neutral
> >>>4 Blue neutral
> >>>5 Green earth
> >>>5 Yellow earth
> >>>-----------------
>
> >>>But this seems wholly artificial given that all the information I
> >>>required for identification was available in the original
> >>>propositions, and that did not require some artificial id. A [shudder]
> >>>non 1NF variation such as:
>
> >>>Id Colour Type
> >>>-----------------
> >>>1 Brown live
> >>>2 Red live
> >>>3 Black neutral
> >>>4 Blue neutral
> >>>5 Green, earth
> >>> Yellow
> >>>-----------------
>
> >>>is clearly hideous as it denies the fundamental mathematical principle
> >>>that that one attribute should take one value from one domain,
> >>>nevermind the fact that it introduces query bias.
>
> >>>I could of course introduce nested relations, but I am uncertain as to
> >>>the theoretical consequences of having nested relation as a key (I
> >>>guess it would be fine, if adding seemingly unnessecary complexity to
> >>>subsequent queries). But moreover it again seems unintuitive, given
> >>>that in this case it would indicating that the original propositions
> >>>contained, as a value for one of their attributes, a further
> >>>proposition, and this was not the case.
>
> >>>I am having a crisis of faith with the way 1NF is currently viewed.
> >>>Any ideas to solve my dilemma? Am I on my own in being perturbed?
>
> >>>Regards, Jim.
>
> >>There is one obvious way to represent that in 1NF:
>
> >>Create a color domain where a single value represents green and yellow,
> >>another value represents green, and a third represents yellow etc. The
> >>domain could even represent thick green/thin yellow as a separate value
> >>from thick yellow/thin green if one chooses.
>
> >>Regardless whether one creates only the domain or also uses it as a
> >>candidate key for some sort of lookup table, the resulting relation is
> >>simply:
>
> >>Colour Type
> >>======= -------
> >>...
>
> >>Your ID above is one example of such a domain. However, the domain need
> >>not be numeric or have any external numeric representations. It need
> >>only exist with a distinct value for green and yellow.
>
> > Well, practically, the surrogate key is the way that I would go. My
> > question is rather whether this corresponds naturally to the original
> > propositions, which don't require a new domain in order to be
> > manipulated in FOL.
>
> You assume a color domain so imagining a different color domain changes
> the design without adding anything new.

Okay, you're right - not a new domain, just a different one. If I had started with domain of all colours C (clearly containing the colour "grey" given the presence of the u there), I read you as proposing that it be replaced with a labelled powerset of C. Howwwever, would occams razor not suggest that we should prefer a domain made up of atomic individuals, as opposed to aliased sets, which will require an extra step to decompose?

I guess my question is heading towards what is theoretically wrong about having:

wires = {
{ (Colour, green), (Colour,yellow), (Type, earth) }, { (Colour, black), (Type, neutral) }
}

as opposed to:

wires = {
{ (Pattern, greenAndYellow), (Type, earth) }, { (Pattern, solidBlack), (Type, neutral) } }

patterns = {

{ (Pattern, greenAndYellow), (Contains, green) },
{ (Pattern, greenAndYellow), (Contains, yellow) },
{ (Pattern, solidBlack), (Contains, black) }

The first version seems so much impler, and while it doesn't accord to the traditional view of 1NF, I am unclear as to how it would harm manipulation.

>
> I seem to remember a while back there was a
>
> > discussion involving Marshall and a few others considering situations
> > where a nested relation was /necessary/ (I need to have a dig for it),
> > and it didn't sit comfortably then.
>
> I would argue that nested relations are never necessary; although, they
> are certainly handy at times. I would choose the discipline of base
> relations having no nested relations. In fact, the princicple of
> cautious design suggests--as tools evolve toward nested relations--to
> allow them only in derived relations.

I would agree with this. Received on Tue Aug 28 2007 - 19:34:35 CEST