Re: Nested Relations / RVAs / NFNF

"Kenneth Downs" <firstinit.lastname_at_lastnameplusfam.net> wrote in message news:skdmlc.2b6.ln_at_mercury.downsfam.net...
> Marshall Spight wrote:
>
> > 2) Pseudo nesting. Relation attributes aren't anything "real"
> > but are instead something that arises when another
> > relation has a foreign key into this table. In that case, we have
> > a "virtual" attribute whose value is necessarily a relation,
> > but it's really more a view onto a nested world than
> > anything new and specific. Nested relation are really
> > just other tables, and so require keys, and from the
> > viewpoint of the nesting table, the keys are unique
> > across all rows. (Unlike in 1.)
> >
> > Something weird about 2). In the nested view, the
> > foreign key goes away, okay fine, but the primary
> > key of the nested table now has to be unique across
> > the entire table, so you can have a perfectly good
> > row for the nested table, and fail on insert because
> > of a uniqueness constraint that's hard to mathematically
> > justify.
>
> I am not following #2 at all, can you give an example?

Sure.

What if we have:

create table Orders
(
  OrderId int primary key,

• other attributes );

• other attributes );

Then, if we had "pseudo nesting" or "virtual RVAs" we could see the attributes of Orders as

1) OrderId int
2) ...
3) OrderItems.OrderItemId int
4) OrderItems.Cost money

Note that we *don't* have OrderItems.OrderId, because it's necessarily always the same as Orders.OrderId.)

Now, could we say, perhaps

select OrderId, sum(OrderItems.Cost) from Orders

and have it be the same thing as

select OrderId, sum(Cost) from OrderItems

Or, could we do some select on Orders that would give us something like a left outer join, in which a *set* of OrderItems would be an attribute on the result, empty if a given Order had no OrderItems?

Interesting, no?

Marshall Received on Wed Oct 27 2004 - 03:48:57 CEST