Re: Multiple-Attribute Keys and 1NF
From: Brian Selzer <brian_at_selzer-software.com>
Date: Tue, 28 Aug 2007 22:05:37 GMT
Message-ID: <R_0Bi.47966$Um6.1000_at_newssvr12.news.prodigy.net>
>> "JOG" <j..._at_cs.nott.ac.uk> wrote in message
>>
>> news:1188303981.637381.32500_at_r29g2000hsg.googlegroups.com...
>>
>>
>>
>> >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.
>>
>> It may seem artificial, but it isn't. A wire is not identified by an
>> element of the Colour domain, but rather by a subset of the Colour
>> domain.
>> Even if a wire has only one colour, a wire is not identified by that
>> colour,
>> but by the set of colours which just happens to consist of the single
>> colour. If you want to avoid a second-order predicate (one that ranges
>> over
>> subsets of the Colour domain), you can assign a name to each colour
>> combination. (That's all that a surrogate is: a name.)
>> In this way all of
>> the domains remain simple, and the queries remain simple, but at the same
>> time all of the relationships amongst Wires and Colours and Types are
>> retained.
>>
>> > 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.
>
> Received on Wed Aug 29 2007 - 00:05:37 CEST
Date: Tue, 28 Aug 2007 22:05:37 GMT
Message-ID: <R_0Bi.47966$Um6.1000_at_newssvr12.news.prodigy.net>
"JOG" <jog_at_cs.nott.ac.uk> wrote in message news:1188321193.340654.29490_at_r34g2000hsd.googlegroups.com...
> On Aug 28, 5:33 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
>> "JOG" <j..._at_cs.nott.ac.uk> wrote in message
>>
>> news:1188303981.637381.32500_at_r29g2000hsg.googlegroups.com...
>>
>>
>>
>> >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.
>>
>> It may seem artificial, but it isn't. A wire is not identified by an
>> element of the Colour domain, but rather by a subset of the Colour
>> domain.
>> Even if a wire has only one colour, a wire is not identified by that
>> colour,
>> but by the set of colours which just happens to consist of the single
>> colour. If you want to avoid a second-order predicate (one that ranges
>> over
>> subsets of the Colour domain), you can assign a name to each colour
>> combination. (That's all that a surrogate is: a name.)
>
> Yup, I see where your coming from there, but I'm still unconvinced (as
> of yet) that it isn't a bit of a fudge. The reason I'm still uneasy is
> that when I refer to a green and yellow wire I _don't_ name it
> collectively ("Grellow" perhaps). Rather I just state that it
> hasColour(Yellow) and hasColour(Green), as distinct separate values,
> and this allows (without any deconstruction) me to quickly answer the
> question does it have "Green" in it. Given:
>
> [P1] hasColour(green) ^ hasColour(yellow) <-> isType(earth)
>
Actually, hasColour(green) /\ hasColour(yellow) --> isType(earth), since hasColour(brown) --> isType(live) and hasColour(red) --> isType(live)
The only reason that <--> applies is due to the fact that there is only one tuple that satisfies isType(earth). Here in the US, earth is sometimes bare and sometimes green, and sometimes grellow.
> I can immediately deduce:
>
> isType(earth) -> hasColour(green)
>
> However if I had:
>
> [P2] hasColour( {green, yellow} ) <-> isType(earth)
>
> I am unclear as to the logical process that allows me to make that
> jump.
>
> Similarly if I use a surrogate, then I am stating two propositions:
> [P3] hasPattern(grellow) <-> isType(earth)
> [P4] contains(grellow, green) ^ contains(grellow, yellow)
>
> I then have to make a double hop skip and jump to answer my question,
> and also must be armed with the extra knowledge that a pattern
> contains individual colours itself. This seems more convoluted
> compared to representing the information as P1.
>
I think it's six of one, a half-dozen of the other. In one case, you need to count the number of tuples that satisfy isType(earth), in the other, you have to count the number of tuples that satisfy hasPattern(grellow).
>
>> In this way all of
>> the domains remain simple, and the queries remain simple, but at the same
>> time all of the relationships amongst Wires and Colours and Types are
>> retained.
>>
>> > 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.
>
> Received on Wed Aug 29 2007 - 00:05:37 CEST