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.