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

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

It is in 5NF.

But IMO we could drop the PhoneID attribute.

> 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

No, there is not logical redundancy.

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

You migth save some memory if you are using a DBMS with poor physical independence capabilities, but you are not eliminating any redundancy in the logical level. In fact you are introducing unneeded complexity in the logical design.

If you were using a DBMS that uses the TransRelational Model, you would waste a lot of memory with the second design.

> My qs is, in doing this what normal form am I enforcing?

None.

> Is this type of
> decomposition an example of enforcing the elusive 5th normal form?

No. This does not have anything to do with 5NF.

Regards Received on Fri Oct 15 2004 - 12:36:18 CEST