Re: foundations of relational theory?

Lauri Pietarinen <lauri.pietarinen_at_atbusiness.com> wrote in message news:<bnmpj1$l1t$1_at_nyytiset.pp.htv.fi>...
> cmurthi wrote:
>
> > Lauri Pietarinen wrote:
> >
> >> Jonathan Leffler wrote:
> >>
> >>> cmurthi wrote:
> >>>
> >>>> Marshall Spight wrote:
> >>>>
> >>>>> I'd still argue that a declarative integrity enforcement
> >>>>> system is better than a procedural one.
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> Can a declaration enforce a really complex oonstraint? And if so,
> >>>> does the declaration begin to look like a procedural statement? For
> >>>> example, even on a simple level, how would you declare that Field1
> >>>> has to be conditionally based on Field2 and Field3, eg,
> >>>> procedurally, if(field3=='') field1=field2 else field1=field3.
> >>>
> >>>
> >>> Using the Informix dialect of SQL, and ignoring SQL NULL values
> >>> (since they make a mess of everything), you could do:
> >>>
> >>> ALTER TABLE WhatNot ADD CONSTRAINT ((field3 = '' AND field1 =
> >>> field2) OR (field3 != '' AND field1 = field3));
> >>>
> >>> You could give the constraint a name - though ISO SQL and Informx
> >>> manage to do that differently. You could also include that
> >>> declarative constraint in the CREATE TABLE statement.
> >>>
> >>> I don't regard that as a particularly complex constraint. I'm not
> >>> sure that it is part of a good database design, but that is probably
> >>> simply because it is illustrative rather than anything else.
> >>>
> >>> The big advantage of the declarative constraint is that the DBMS
> >>> enforces it. No application can violate it, whether accidentally or
> >>> on purpose. If the constraint becomes obsolete, then you drop it.
> >>> Altering it is a two-stage drop and add operation (which Informix
> >>> permits you to do in a single ALTER TABLE statement, so there is no
> >>> window of vulnerability while you are changing the constraint).
> >>
> >
> > Thanks, Jonathon. Firstly, the constraint syntax is reminiscent of old
> > scripting languages; field = '' [then] field != '' [then]... I am
> > assuming this is a common syntax. Procedural syntax is simply more
> > elegant, but that's not a functional comment, it's an aesthetic one.
> >
> > More to the point, the more complex the constraint (and I was giving
> > only the simplest above,) won't it become, simply, unreadable? And
> > what if you wanted the constraint to be be dependent on "foreign" data
> > (I think you call it), ie: if(sum(values_from_another_table if present
> > for this record key) < 0) [ok] elseif (something else..) etc. I see
> > these as valid "constraints" in that, while maybe infringing on
> > business rule territory, they should still be enforced at the lower
> > level as part of the db set.

Lauri,

You need to question the need for the complex constraint in the first place. The quality of the logical design will generally determine the complexity of the constraints. A well-designed base database will require few or no complex constraints. Full normalization reduces the need for complex constraints, and one chooses base relations to minimize the complexity of constraints.

I suggest the logic involving sums etc. is more appropriate in the definition of a derived relation. Received on Wed Oct 29 2003 - 15:11:59 CET