Re: MV Keys

In article <1141308035.958520.53950_at_u72g2000cwu.googlegroups.com>, jog_at_cs.nott.ac.uk says...
> Bob Hairgrove wrote:
> > On Wed, 1 Mar 2006 12:05:28 +0100, Jon Heggland <heggland_at_idi.ntnu.no>
> > wrote:
> >[snip]
> > OrderID|CustomerID|Item1 |Item2 |Item3 | Item4 (...)
> > =================================================================
> > 1|foo_123 | apples| pears| bananas| oranges
> > 2|bar_123 | pears| bananas| oranges|
> > 3|bar_123 | bananas| | |
> >
> > etc., and this:
>
> This confuses me. Surely both of these examples are 1NF. In the first
> example item1, item2, etc are different attributes with potentially
> different semantics attached to them. It may well be a shoddy design
> but still 1NF.

Not according to several textbooks and tutorials on normalisation. These sources may be considered wrong---they are certainly far from Codd's definition---but this *is* being taught as non-1NF. Yes, it surprised/confused me too.

> > OrderID|CustomerID|Items
> > ================================================
> > 1|foo_123 | apples;pears;bananas;oranges
> > 2|bar_123 | pears;bananas;oranges
> > 3|bar_123 | bananas
>
> And here the Items attribute is a datatype of varchar

Says who? This is D4 code:

create table Order
{
  OrderID : OrderID,
  CustomerID : CustomerID,
  Fruits : list(Fruit),
  key { OrderID }
};

insert table {
  row { 3 OrderID, "bar_123" CustomerID, list(Fruit) { "bananas" } Fruits },
  row { 2, "bar_123", list(Fruit) { "pears", "bananas", "oranges" } },   row { 1, "foo_123", list(Fruit) { "apples", "pears", "bananas", "oranges" } }
} into Order;

select Order add { Fruits.ToString() Items } remove { Fruits }; // I have to do this extend-project, because list types have no built-in // String representation in D4.

---and the results:

OrderID CustomerID Items

------- ---------- ---------------------------- 
1       foo_123    apples;pears;bananas;oranges 
2       bar_123    pears;bananas;oranges        
3       bar_123    bananas                      

No varchars involved. Another example:

select Order { OrderID, Fruits[0] FirstFruit };

OrderID FirstFruit
------- ----------

1       apples     
2       pears      
3       bananas    

Is my Order table in 1NF? Not the representation of my table, mind you, but the table itself. :)

-- 
Jon