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

On Oct 23, 4:41 pm, xyzzy <goo..._at_m-streeter.demon.co.uk> wrote:
> 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
>
In addition to that...

It's very important to get this across to people studying database design: as well as showing the tables, attributes and keys, it is ESSENTIAL for you to WRITE DOWN THE RULES that govern the data. The reasons for doing this are 1. the rules contain the information about Whether or not there is a FD 2. helps stop you making mistakes yourself.

Examples of rules: Say your employee-customer relation was for recording current assignments about butlers who are assigned to men... or PAs contracted to managers in third-party companies if you prefer. In that case, R1: an emp# cannot possibly be associated with more than one customer and R2: a customer never has more than one emp. Also (because you are recording associations) you will R3: never have an emp that isn't associated with a customer and R4: never have a customer without a corresponding emp. So every relvar will be unique.

On the other hand, if the same information is to be used as a little black book listing extractor-fan salesmen and the regular clients on their list for the geographic area they cover, every single rule is flipped around.

The same thing applies to your list of items. Are you modelling a queue of people who are allowed to take up to three uniquely-numbered plates off a converor belt? If an item is 'claimed' by the fact is appears in another row, that has a big impact on what your FDs are.

It will create an enormous amount of confusion if you don't write down the rules. I consider that to be documentation and I normally stick them in the table and column comments for posterity.

Thanks Received on Fri Oct 24 2008 - 00:23:28 CEST