Re: rookie to gurus: transitive?

Jan Hidders wrote:
> Arne Jonsson wrote:
> > I´m trying to get hold of normalization forms. Anyone who can explain
> > "transitive dependencies" at street level (with some real life
> > examples)?
>
> First let me say that I really dislike the term "transitive
> dependency". [...] that's not your fault. :-)
>
> Ok. Enough ranting, I'll get on with the explanation now. Suppose you
> have the relation
>
> Order-line(order#, line#, article#, amount, article-price)
>
> that stores the information of the lines of orders. (The information
> that goes with the order itself is stored in another table.) The
> primary key of this relation is going to be {order#, line#}.
>
> The definition of a transitive dependency is as follows:
>
> An FD X->Y is transitive if there is a set of attributes Z that is
> not a subset of the key such that X->Z and Z->Y.
>
> To understand this look at the example relation I gave. The FD that
> satisfies the definition is:
>
> FD1: order#, line# -> article-price
>
> because we have also
>
> FD2: order#, line# -> article#
> FD3: article# -> article-price.
>
> Note that when normalizing to 3NF we do not factor out the transitive
> dependency FD1 but the dependency that *makes* it a transitive
> dependency but does not follow from the key, which in this case is FD3.

I'm not going to dispute Jan's theory -- it looks impeccable to me.

Taken at face value, Jan's example is OK; if the article# determines the price, then there is indeed a transitive dependency.

In practice, though, there is often the possibility for an item to have a list price and the actual price at which it is sold -- software and cars are two categories that spring to mind. In a database modelling such a system, there may not be a functional dependency between the price on the order line and the price in price book, in which case there would no longer be a transitive dependency in the order line. Even if you assume that article#->price but you don't have a temporal database, you may need to record the price of the item at the time when the order was placed, or fulfilled, or invoiced, so that you can reconstruct the billing. You don't want to have to come back 90 days after the invoice is sent and find that the pricing has changed so you have no idea what was the correct price at the time when the price of the (item in the) order was fixed.

-- 
Yours,
Jonathan Leffler (Jonathan.Leffler_at_Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
     "I don't suffer from insanity; I enjoy every minute of it!"