Re: I have read tons of theory...but still...one question

From: Dawn M. Wolthuis <dwolt_at_tincat-group.comREMOVE>
Date: Fri, 15 Oct 2004 16:21:10 -0500
Message-ID: <ckpf14$4pi$1_at_news.netins.net>


"Tony" <andrewst_at_onetel.com> wrote in message news:ed8a00fa.0410150214.6c42d635_at_posting.google.com...
> "Kostas" <noemail_at_noemail.net> wrote in message
news:<10mult27ildsc26_at_corp.supernews.com>...
> > Hi all,
> >
> > I strived understanding the concepts of all normal forms up to the
> > domain-key normal form but there are some issues I still am unsure of,
and I
> > will describe the first one in this post.
> > (it looks so simple that there has to be an answer)
> >
> > Suppose a relation that holds information about Phone Numbers:
> > PhoneNumber (PhoneID, ActualNumber, PhoneType, Comments)
> >
> > We have 4 fields.
> > 1. PhoneID ==> a system-generated identifier
> > 2. ActualNumber ==> the actual phone number
> > 3. PhoneType ==> determines the type of phone and can have values only 3
> > values which are {Home, Work, Cellular}
> > and
> > 4. Comments ==> comments for a specific phone number.
> >
> > I believe this first relation is in Boyce/Codd Normal Form.
> >
> > Obviously, however, there is some redundancy in having the three values
of
> > the PhoneType attribute repeating for thousands of records containing
phone
> > numbers. So I create another table
> > PhoneType (PhoneTypeID, PhoneType).
> >
> > Now I have the two tables
> > Phone (PhoneID, ActualNumber, PhoneTypeID(foreignkey) Comments)
> > PhoneType (PhoneTypeID, PhoneType)
> >
> > This allows me to get rid of the redundancy of the first design because
I
> > only need to store the 3 phone types once in the second table.
> > My qs is, in doing this what normal form am I enforcing? Is this type of
> > decomposition an example of enforcing the elusive 5th normal form?
>
> You have not reduced any redundancy, you have just replaced a string
> value PhoneType by a numeric (surrogate) value PhoneTypeID. It is not
> redundant (despite what someone who calls himself Neo here may claim)
> to store the same data value in more than 1 row. In other words, your
> original table was just fine.
>
> If you are familiar with C J Date's writings then you will know his
> ubiquitous "suppliers and parts" example, which involves a table SP
> that looks something like this (from memory):
>
> S# P# QTY
> === === ===
> S1 P1 10
> S1 P2 10
> S2 P1 20
> ...
>
> which means e.g. "supplier S1 supplies part P1 in quantities of 10".
> Note that the S# value appears in 2 rows, as does the P# value P1, as
> does the QTY value 10. This is not redundant, because the same FACT
> is never recorded twice.

No, but what do we call it when 10 facts are represented with a single row and only recorded once? This puts us in the possible position of having to later identify which of the 10 parts was actually shipped. So, calling this "normal" doesn't buy us all that much, right? (what's your point, Dawn? I'm sure I have one, sorry -- just had to mention it)

> A tuple like (S1, P1, 10) represents a FACT; an attribute value like
> S1 does not.

or 10 facts, as the case may be ;-)
--dawn Received on Fri Oct 15 2004 - 23:21:10 CEST

Original text of this message