Re: Multiple-Attribute Keys and 1NF

On Aug 29, 6:39 am, Jan Hidders <hidd..._at_gmail.com> wrote:
> On 29 aug, 02:05, JOG <j..._at_cs.nott.ac.uk> wrote:
>
>
>
> > On Aug 29, 12:42 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
> > > JOG wrote:
> > > > On Aug 29, 12:12 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
> > > >>JOG wrote:
>
> > > >>>On Aug 28, 8:23 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>
> > > >>>>JOG wrote:
>
> > > >>>>>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).
>
> > > >>>>Assuming one has a need to query for green separately, I suppose one can
> > > >>>>define an operator on the domain to that effect. If one invents a
> > > >>>>requirement that requires a second domain, then one will need a second
> > > >>>>domain regardless.
>
> > > >>>Well that sort of brings us full circle back to to my query as to
> > > >>>whether a structure that doesn't require that second domain,
>
> > > >>Let me be clear: Unless you invent a requirement that requires a second
> > > >>domain, no second domain is required. If one invents such a requirement,
> > > >>one is.
>
> > > >>such as a
>
> > > >>>set where elements themselves are pure mathematical relations
> > > >>>containing attribute/value pairs:
>
> > > >>>Wires = { {(Color, Yellow), (Color, Green), (Type, earth)}} {(Color,
> > > >>>blue), (Type, live)} }
>
> > > >>But a set is a second domain. You have 1) colors and 2) sets of colors.
> > > >>Actually, you have sets of some supertype of color and type?!? Yuck!
>
> > > > I'm worrying that you have misinterpreted what I have sketched out
> > > > there. I haven't specified any domain sets at all in the above - it
> > > > is just a set of propositions, and as with RM, each element is a
> > > > mapping from attribute names onto values, that's all. I have no idea
> > > > why you think I have supertypes, etc, in there. (which I agree would
> > > > be yuck)
>
> > > Based on this set: {(Color, Yellow), (Color, Green), (Type, earth)}
>
> > Still not seeing where you get supertypes from. I just see a mapping
> > of roles in a proposition to corresponding values.
>
> > > That is not a tuple. A tuple would be:
>
> > > {(Color, {Yellow, Green}), (Type, earth)}
>
> > Yes, I realize it is not a db-tuple, because if one relaxes 1NF then
> > one doesn't have a db-relation at all.
>
> That is irrelevant. In a NFNF setting tuples are still defined as a
> certain kind of function, and what you gave is not a function.

Aye, I am aware I have dropped the correspondence between a tuple and a finite partial function, and left a unrestricted mathematical relation in its place. What I really wanted to explore were the connotations of doing this.

>
> > That set-valued element still
> > represents a proposition however, and is in fact a relation in the
> > true mathematical sense. I find this representation interesting
> > because a JOIN becomes a union of these elements, and a natural join
> > is generated by default as one would expect.
>
> That depends a little on what one would expect. ;-) One elegant
> definition of the natural join of two relations R and S is for example
> { t1 + t2 | t1 in R, t2 in S, t1 + t2 is a tuple }. If you change the
> definiton of tuple as you propose this doesn't work anymore.

Well hey, the definition of a tuple in terms of RM is a bit quirky anyhow (nevermind cross-product). Why let Codd have all the fun ;)

> Of course it is not hard to come up with a definition that does generalize the
> natural join correctly.
>
> -- Jan Hidders
Received on Wed Aug 29 2007 - 13:05:28 CEST