Databases Reference

In-Depth Information

So predicates are informal. By contrast, constraints are
formal
. In essence, a constraint is a boolean

expression, expressed in some formal language like SQL or
Tutorial D
and usually containing references to relvars

in the database, that's required to evaluate to TRUE at all times. Let
R
be a relvar. Then it's convenient to think of

the logical AND of all constraints that mention
R
, either directly or indirectly, as
the
relvar constraint for
R
. Note,

therefore, that whereas the relvar predicate for
R
is understood only by the user, the relvar constraint for
R
is

“understood” by both the user
and the system
. In fact, the relvar constraint for
R
can be regarded as the system's

approximation to the relvar predicate for
R
. Ideally, of course, we would like
R
to be such that it always satisfies its

predicate; the best we can hope for, however, is that it always satisfies its constraint.
5

Given now that a database is supposed to be a faithful representation of the semantics of what might be called

“the microworld of interest,” it follows that predicates and constraints are highly relevant to the business of database

design. We could say that predicates are the informal, and constraints the formal, representation of those semantics.

Thus, the database design process as I see it goes like this:

1.

First we pin down the relvar predicates (and other business rules) as carefully as possible.

2.

Then we map those predicates and rules into relvars and constraints.

As a consequence of the foregoing, we can see that another way to think about design theory─normalization

and so forth─is as follows:
It's a set of principles and techniques for helping with the business of pinning down

predicates
(and hence constraints). This perspective underpins much of what follows in this chapter.

As an aside, I note that the foregoing discussion goes a long way toward explaining why I'm not much of a

fan of E/R (“entity/relationship”) modeling and similar pictorial methodologies. (You might have noticed the total

absence of E/R diagrams and the like in previous chapters!) The problem with E/R modeling and suchlike schemes

is that they're less powerful─much less powerful─than formal logic. In particular, they don't include anything like

adequate support for the
quantifiers
(EXISTS and FORALL),
6
which is a serious omission because the formulation

of all but the simplest constraints requires such support, or something equivalent to such support.
7
As a

consequence, those schemes and those diagrams are completely incapable of representing all but a few admittedly

important, but limited, constraints. Thus, while it might be acceptable to use such diagrams to explicate the overall

design at a high level of abstraction, it's misleading, and in some respects quite dangerous, to think of such diagrams

as actually
being
the design in its entirety.
Au contraire:
The design is the relvars, which the diagrams do show,

plus the constraints, which they don't.
8

5
As an aside, I remark that
The Closed World Assumption
applies to predicates, not constraints. That is, (a) if tuple
t
appears in relvar
R
at time

T
, then
t
certainly satisfies both the relvar predicate and the relvar constraint for
R
at time
T
; (b) if tuple
t
could plausibly appear in relvar
R
but

doesn't, then
t
certainly doesn't satisfy the predicate for
R
at time
T
, but it still has to satisfy the constraint for
R
(because if it doesn't, then it

couldn't “plausibly appear” in the first place).

6
Since the quantifiers were invented by Frege in 1879, this omission makes E/R diagrams and the like (as a friend of mine once put it) “a pre

1879 kind of logic”!
Note:
A tutorial on quantifiers and related matters can be found in
SQL and Relational Theory
and many other places.

7
Tutorial D
has no explicit quantifier support either, but anything expressible in terms of the quantifiers can nevertheless be expressed in

Tutorial D
; that is,
Tutorial D
does at least have “something equivalent to” quantifier support.

8
Two qualifications here: First, the diagrams do show
some
constraints (basically key and foreign key constraints), as already noted. Second,

they might not in fact show all of the relvars─some E/R modeling schemes don't include in their diagrams relvars like SP in our running example

that correspond to many to many relationships.