Re: Article claims the following table is not in 1NF

<Srubys_at_gmail.com> wrote in message
news:c4b7b721-9c49-4cb7-bb08-2f2044d277ef_at_e17g2000hsg.googlegroups.com... greetings

> 2) My book claims that if table is not normalized, then primary key
can’t be made out of just one attribute. But how can that ALWAYS be true, since even if a table has multi valued attributes or duplicative columns, we could still have an attribute ( ORDER_ID ) that would uniquely identify the row:

If a table is not in 1NF, then it can't have a primary key at all. So the book's claim is true but meaningless.

> ORDER ( ORDER_ID, ITEM1, ITEM2, ITEM3 )
Now even though the above table has repeating columns ( ITEM1, ITEM2, ITEM3 ), ORDER_NUM column would still be able to uniquely identify the row!

Uniquely identifying a row is not what a primary key is all about. A lot of designers and even books make this mistake. In order for a primary key to really be a primary key, there must be one and only one column in the target table that might contain the value being retrieved. If ITEM1, ITEM2, and ITEM3 all carry the same semantics, then uniquely identifying a row isn't enough information to tell us where to look. We still have to look in all three items to find what we are looknig for.

consider the following:

SELECT ORDER_ID
FROM ORDERS
WHERE ITEM1 = 'LIGHTBULB'
OR ITEM2 = 'LIGHTBULB'
OR ITEM3 = 'LIGHTBULB' BTW, you can substitute "candidate key" for "primary key" in the above with no loss of meaning. A primary key is just a conadidate key that has been chosen by the designer to be "primary". Received on Thu Oct 23 2008 - 08:43:28 CDT