Re: Enforcing functional dependecy constraints

"x" <x_at_not-exists.org> wrote in message news:dnjksn$7dk$1_at_domitilla.aioe.org...
>
> "David Cressey" <david.cressey_at_earthlink.net> wrote in message
> news:wpClf.507$nm.373_at_newsread2.news.atl.earthlink.net...
> >
> > "x" <x_at_not-exists.org> wrote in message
> > news:dn1j2m$fpo$1_at_domitilla.aioe.org...
> > > Hi !
> > >
> > > Since this is a database theory group and I'm bored by the null and
3vl
> > > threads I ask yet another elementary question:
> > >
> > > Suppose you have this relation R(A,B,C) with the following functional
> > > dependencies AB->C and C->B.
> > > What is the best way to implement this in available SQL DBMS in your
> > opinion
> > > ?
> > >
> > > Regards,
> > > x
>
> > I'm not sure what you mean by "best". If it's my opinion of "what's
> best",
> > I'm going to dodge the question by giving the universal answer: "It
> > depends".
>
> Best as in "best implementation" of the constraints in SQL with some
> particular products.
> There could be many answers and tradeoffs.

I completely missed the point behind your question.

> 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?

> 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.

> > 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.

> 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.

> 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. Received on Tue Dec 13 2005 - 14:10:07 CET