Re: Multiple-Attribute Keys and 1NF

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. 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. Received on Tue Aug 28 2007 - 18:22:57 CEST