Re: Multiple-Attribute Keys and 1NF

JOG wrote:

On Aug 28, 7:41 pm, Bob Badour <bbad...@xxxxxxxxxxxxxxxx> wrote:

JOG wrote:

On Aug 28, 5:43 pm, Bob Badour <bbad...@xxxxxxxxxxxxxxxx> wrote:

JOG wrote:

On Aug 28, 5:05 pm, Bob Badour <bbad...@xxxxxxxxxxxxxxxx> 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.
.

Relevant Pages

  • Re: Multiple-Attribute Keys and 1NF
    ... behest of mrs. JOG, to whom there may only be obeyance). ... Green and yellow -> earth. ... propositions, and that did not require some artificial id. ... where a nested relation was /necessary/, ...
    (comp.databases.theory)
  • Re: Multiple-Attribute Keys and 1NF
    ... Green and yellow -> earth. ... propositions, and that did not require some artificial id. ... requirement that requires a second domain, then one will need a second ... to generate an artificial identifier for the pair. ...
    (comp.databases.theory)
  • Re: Multiple-Attribute Keys and 1NF
    ... behest of mrs. JOG, to whom there may only be obeyance). ... Green and yellow -> earth. ... propositions, and that did not require some artificial id. ... not be numeric or have any external numeric representations. ...
    (comp.databases.theory)
  • Re: Multiple-Attribute Keys and 1NF
    ... Green and yellow -> earth. ... propositions, and that did not require some artificial id. ... requirement that requires a second domain, then one will need a second ... However, if one normalized the base relations, one would already have the identifier, and it would be rather simple to construct the RVA in a derived relation. ...
    (comp.databases.theory)
  • Re: Multiple-Attribute Keys and 1NF
    ... Green and yellow -> earth. ... propositions, and that did not require some artificial id. ... requirement that requires a second domain, then one will need a second ... to generate an artificial identifier for the pair. ...
    (comp.databases.theory)