Databases Reference
In-Depth Information
Back to the relvar definitions. As you can see, each of those definitions includes a KEY specification, which
means that every relation that might ever be assigned to any of those relvars is required to satisfy the corresponding
key constraint
. (Recall from Chapter 1 that every relvar does have at least one key.) For example, every relation
that might ever be assigned to relvar S is required to satisfy the constraint that no two distinct tuples in that relation
have the same SNO value. What's more, I'm going to assume throughout this topic, barring explicit statements to
the contrary, that the following
functional dependency
(FD) also holds in relvar S:
{ CITY }
→
{ STATUS }
You can read this FD, informally, as
STATUS is functionally dependent on CITY
, or as
CITY functionally
determines STATUS
, or more simply as just
CITY arrow STATUS
. What it means is that every relation that might
ever be assigned to relvar S is required to satisfy the constraint that if two tuples in that relation have the same
CITY value, then they must also have the same STATUS value.
5
Observe that the sample value of relvar S given
in Fig. 1.1 does indeed satisfy this constraint.
Note:
I'll have a great deal more to say about FDs later in Parts II
and III of this topic, but I'm sure you're already familiar with the basic idea anyway.
Now, just as KEY specifications are used to declare key constraints, so we need some kind of syntax in order
to be able to declare FD constraints.
Tutorial D
provides no specific syntax for that purpose, however
6
(nor does
SQL, come to that). It does allow them to be expressed in a somewhat roundabout fashion—for example:
CONSTRAINT XCT
COUNT ( S { CITY } ) = COUNT ( S { CITY , STATUS } ) ;
Explanation:
In
Tutorial D
, an expression of the form
r
{
A1
,...,
An
} denotes the projection of relation
r
on
attributes
A1
, ...,
An
. If the current value of relvar S is
s
(a relation), therefore, (a) the expression S{CITY} denotes
the projection of
s
on CITY; (b) the expression S{CITY,STATUS} denotes the projection of
s
on CITY and
STATUS; and (c) the constraint overall—which I've named, arbitrarily, XCT—requires the cardinalities (COUNT)
of those two projections to be equal. (If it's not obvious that requiring these two counts to be equal is equivalent to
requiring the desired FD constraint to hold, try interpreting it in terms of the sample data in Fig. 1.1.)
Aside:
In case you feel those appeals to COUNT in the formulation of constraint XCT are somehow a little
inelegant, here's an alternative formulation that avoids them:
CONSTRAINT XCT
WITH ( CT := S { CITY , STATUS } ) :
AND ( JOIN { CT , CT RENAME { STATUS AS X } } , STATUS = X ) ;
Explanation:
First, the WITH specification (“WITH (…):”) serves merely to introduce a name, CT, that can
be used repeatedly later in the overall expression to avoid having to write out several times the expression it
stands for. Second, the
Tutorial D
RENAME operator is more or less self-explanatory (but is defined
anyway, in the answer to Exercise 2.15 in Appendix D). Third, the
Tutorial D
expression AND(
rx
,
bx
),
5
This example of what FDs mean also serves to show why such dependencies are called functional. To elaborate: A function in mathematics is a
mapping from one set
A
to some set
B
, not necessarily distinct from
A
, with the property that each element in
A
maps to just one element in
B
(but
any number of distinct elements in
A
can map to the same element in
B
). In the example, therefore, we could say there's a mapping from the set
of CITY values in S to the set of STATUS values in S, and that mapping is indeed a mathematical function.
6
One reason it doesn't is that if the design recommendations discussed in the present topic are followed, there should rarely be a need to declare
FDs explicitly anyway.
