Re: Multiple-Attribute Keys and 1NF

On Aug 28, 7:41 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> JOG wrote:
> > 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 don't recall suggesting anything about sets--just a domain that has a
> distinct value that means "green and yellow".

Okay, sure. But then to be able to query for green and yellow individually one must employ a further relation encoding two more propositions that state "'Green and yellow' contains 'Green'" and that "'Green and yellow' contains 'Yellow'" respectively. One then has a schema with two domains - one for the composites and one for individual colours (which is what I was inferring when I initially said a new one was being added). Received on Tue Aug 28 2007 - 20:53:46 CEST