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

"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.

A tuple like (S1, P1, 10) represents a FACT; an attribute value like S1 does not. Received on Fri Oct 15 2004 - 05:14:21 CDT