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

On Oct 23, 5:38 am, Sru..._at_gmail.com wrote:
> greetings
>
> 1) One site claims the following table is not in first normal form –
> but the definition for 1NF just says that data shouldn’t  contain
> repeating groups of fields. And it’s quite obvious that there are no
> repeating columns in the following table, since I wouldn’t consider
> Last_name column being same as Cust_lastname or Address same as
> Cust_address ( but I would consider ORDER ( ORDER_ID, ITEM1, ITEM2,
> ITEM3 )
> to have repeating columns ) …
>
> COMPANY_DATABASE ( Emp_id#, Last_name, First_name, Address, City,
> State, Position, Cust_id#, Cust_lastname, Cust_firstname,
> Cust_address, Cust_city, Ord_num, Ord_date, Prod_id, Cost )
>

Yes you are correct on that point! Data is UNF if it is unstructured. We structure it into relations (represented in the system as tables). Particular chunks of data are called relvars (represented in the tables as rows). A relvar is in 1NF iff every legal value of that relvar contains exactly one value for each attribute. SO by the time you've organised your data into table rows, it's in 1NF. It looks like it's in 1NF, not 2NF though :(

Seems to be a link between employee and customer. Does an employee have 0..many customers assigned to them? Does a customer get served by more than one employee?

If an employee deals with several customers, will the Emp_id# half of the row be repeated on each row? If a customer is dealt with by more than one employee, will the customer information need to be repeated on each row? Presumably an employee with no customers has nulls in thos columns. Yuk.

> 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:
>
> ORDER ( ORDER_ID, ITEM1, ITEM2, ITEM3 )
>

Ahh... only if ORDER_ID is unique. If so, then it's probably a dataless, unique, unchanging, unambiguous sequence number you made up to go with the items, in which case it is called a surrogate key and a primary key. If so, your example is not 1NF any more because every non-key attribute is completely dependent on the PK.

If ORDER_ID is not unique, you'll need more columns to uniquely identify the row you want won't you?

> Now even though the above table has repeating columns ( ITEM1, ITEM2,
> ITEM3 ), ORDER_NUM column would still be able to uniquely identify the
> row!
>
> Similarly, if ITEM column is multi valued, we could still have
> ORDER_ID as unique key:
>
> ORDER ( ORDER_ID, ITEM )
>
> thank you

We believe you should not feel afraid to ask questions in this forum, so welcome to the forum and good on you for asking! Don't be upset if someone comes along in a minute and jumps up and down on you. Good luck! Received on Thu Oct 23 2008 - 07:41:19 CEST