Databases Reference
In-Depth Information
However, I
also said in
Chapter 2 that FOREIGN KEY specifications like the ones shown here
are essentially just shorthand for constraints that can also be expressed, possibly more
longwindedly, using explicit CONSTRAINT syntax. More specifically, I showed in Chapter 3
how the fact that, e.g., {SNO} in relvar SP is a foreign key referencing the key {SNO} in relvar
S could be expressed in relational calculus as a multivariable constraint, like this:
FORALL
x
∈
SP ( UNIQUE
y
∈
S (
x
.SNO =
y
.SNO ) )
(I remind you that the UNIQUE quantifier can be read as “there exists exactly one … such that.”)
So you might be wondering what the
Tutorial D
“longhand” equivalent of this relational
calculus expression would look like.
Well, we could certainly write the following in
Tutorial D
:
CONSTRAINT ... IS_EMPTY ( SP NOT MATCHING S ) ;
But this statement doesn't quite do the job (at least, not by itself). What it says is that every
shipment has
at least
one corresponding supplier. What we want to say, however, is that each
shipment has
exactly
one corresponding supplier.
2
Well, we can do that too by making use of
another convenient shorthand called
image relations
(see
SQL and Relational Theory
for further
discussion of this construct). To be specific, we can write:
CONSTRAINT ... IS_EMPTY ( SP WHERE COUNT ( ‼S )
≠
1 ) ;
Explanation:
For a given SP tuple, the expression ‼S—pronounced “bang bang S” or “double
bang S”—denotes the set of S tuples with the same supplier number as that SP tuple. Thus, the
constraint overall requires every SP tuple to be such that the count of corresponding S tuples is
exactly one.
Still on the subject of
Tutorial D
syntax, you might also be wondering how functional
dependencies (FDs) can be formulated in
Tutorial D
. (You might have noticed that I didn't give
any such formulations in connection with the examples in which I was making use of such FDs.)
In fact there are several ways to do it, of which the most elegant is probably the one illustrated
here:
CONSTRAINT ... SSP { SNO , CITY } KEY { SNO } ;
You can read this CONSTRAINT statement as saying “If we were to define a relvar consisting
of the projection of SSP on SNO and CITY, then that relvar would have {SNO} as a key”—
which effectively does what we want, since it states implicitly that the FD
2
Actually the statement does do the job in a sense, thanks to the fact that there's an additional constraint in effect that requires
supplier numbers in S to be unique. But having to deduce that some constraint—a rather important constraint at that—holds
implicitly from the fact that two other constraints have been declared explicitly does seem, from the user's point of view at least.
a trifle unfriendly.
