Re: Multiple-Attribute Keys and 1NF
Date: Tue, 28 Aug 2007 13:43:49 -0300
Message-ID: <46d4507b$0$4053$9a566e8b_at_news.aliant.net>
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.