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

From: Laconic2 <laconic2_at_comcast.net>
Date: Fri, 15 Oct 2004 09:02:41 -0400
Message-ID: <g_mdnQz9vbgcV_LcRVn-uw_at_comcast.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.

I'm sorry to make a simple example complicated. But here goes.

In the case of land lines (Work, Home phones) the "telephone number" does not identify a telephone. If I unplug the telephone in my house, take it over to your house and plug it in, it no longer has any relationship to my telephone number.
In other words, a telephone does not "know its own number" to anthropomophize a bit.

In the case of cellulars, a phone knows it's "contract number" in America. In Europe, think there's a card inside to phone that knows its contract number. The contract number determines the phone number.

Next,

I take it that PhoneID and ActualNumber are both candidate keys. If ActualNumber is NOT a candidate key, then I think you have transitive dependencies and therefore the relation is not in 3NF.

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

I'm not sure I really get what you are saying. It sounds like you are concerned about physical redunancy
(too many bytes in the value), rather than logical redundancy (the same
fact in more than one place.)

If that's what you are thinking, then you are vulnerable to what I'll call a "Neo Con" (being conned by Neo). Received on Fri Oct 15 2004 - 15:02:41 CEST

Original text of this message