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

From: Kostas <noemail_at_noemail.net>
Date: Fri, 15 Oct 2004 00:52:18 -0400
Message-ID: <10mult27ildsc26@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?

Many thanks to anyone who can clue me in,

Kostas Received on Thu Oct 14 2004 - 23:52:18 CDT