Re: Enforcing functional dependecy constraints

"David Cressey" <dcressey_at_verizon.net> wrote in message news:PKznf.1173$Y2.1015_at_trndny04...

> > Elementary as in "elementary in theory" because this is the standard
> problem
> > for BCNF.

> If it's elementary, and there's a standard answer, and the standard answer
> is not the best, in your view, then what is wrong, in your view?

It is elementary in theory. Practice is something else :-).

> > Have you encountered this problem in practice ?
> No.

> > This is the standard theoretical decomposition.
> > How the AB->C is enforced ?

> I've got to think this one through. DEC Rdb VMS had inter-table
constraints
> way back when.
> I never had to use them, though, so it's going to take a little thought.
> And I can't say in advance whether I'm going to come up with a good
answer.

I saw something about functional dependecies in the SQL standard. I don't know if there is a special syntax for stating them or if inter-table functional dependecies are allowed.

> > > Do you have a different answer?
> > Yes.
> > a)R(A,B,C) with (A,B) primary key, (B,C) foreign key into T and T(B,C)
> with
> > C primary key.

> Interesting. Incidentally, "T(B,C) with C Primary key" says what I was
> hinting at with "T(C,B)".
> But saying it, as you did, is better than hinting at it, as I did.

Have you noticed the redundancy for attribute B ?

> > b)S(A,C) and T(B,C) with some triggers
> > c)R(A,B,C) with some triggers

> Triggers are ok with me. But then, I tend to tolerate a lot of things
that
> others find anathema.

Some DBMS implement the declarative constraints with triggers anyway :-)

> > d) S(A,C) and T(B,C) with R(A,B,C) a view with some unique index on it

> This would be cool if you could declare "inter-table indexes" (and make
them
> unique). I know of no product that offers
> such a feature, but I figure it would be a really neat way of speeding
up
> a join without materializing the join in a denormalized table. Then you
> could enforce the constraint via the index, and guarantee that the view
> R(A,B,C) (or actually the SQL table that represents it) actually
represents
> a relation.

I know MS SQL Server offer something called view indexing, but I don't know the details. Received on Tue Dec 13 2005 - 15:04:58 CET