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.
