Databases Reference
In-Depth Information
or more xyz 's in which each pair of adjacent xyz 's is separated by a comma (as well as, optionally, one or more
spaces before or after the comma or both).
Next, as I'm sure you also know, a primary key is a candidate key that's been singled out in some way for
some kind of special treatment. Now, if the relvar in question has just one candidate key, then it doesn't make any
real difference if we call that key primary. But if the relvar has two or more candidate keys, then it's usual to choose
one of them to be primary, meaning it's somehow “more equal than the others.” Suppose, for example, that
suppliers always have both a unique supplier number and a unique supplier name, so that {SNO} and {SNAME} are
both candidate keys. Then we might choose {SNO}, say, to be the primary key.
Observe now that I said it's usual to choose a primary key. Indeed it is usual—but it's not 100 percent
necessary. If there's just one candidate key, then there's no choice and no problem; but if there are two or more,
then having to choose one and make it primary smacks a little bit of arbitrariness, at least to me. (Certainly there are
situations where there don't seem to be any good reasons for making such a choice. There might even be good
reasons for not doing so. Appendix A elaborates on such matters.) For reasons of familiarity, I'll usually follow the
primary key discipline myself in this topic—and in pictures like Fig. 1.1 I'll indicate primary key attributes by
double underlining—but I want to stress the fact that it's really candidate keys, not primary keys, that are significant
from a relational point of view, and indeed from a design theory point of view as well. Partly for such reasons, from
this point forward I'll use the term key , unqualified, to mean any candidate key, regardless of whether the candidate
key in question has additionally been designated as primary. (In case you were wondering, the special treatment
enjoyed by primary keys over other candidate keys is mainly syntactic in nature, anyway; it isn't fundamental, and it
isn't very important.)
More terminology: First, a key involving two or more attributes is said to be composite (and a noncomposite
key is sometimes said to be simple ). Second, if a given relvar has two or more keys and one is chosen as primary,
then the others are sometimes said to be alternate keys (see Appendix A). Third, a foreign key is a combination, or
set, of attributes FK in some relvar R2 such that each FK value is required to be equal to some value of some key K
in some relvar R1 ( R1 and R2 not necessarily distinct). 5 With reference to Fig. 1.1, for example, {SNO} and {PNO}
are both foreign keys in relvar SP, corresponding to keys {SNO} and {PNO} in relvars S and P, respectively.
To repeat something I said in the preface, by the term design I mean logical design, not physical design. Logical
design is concerned with what the database looks like to the user (which means, loosely, what relvars exist and what
constraints apply to those relvars); physical design, by contrast, is concerned with how a given logical design maps
to physical storage. 6 And the term design theory refers specifically to logical design, not physical design—the point
being that physical design is necessarily dependent on aspects (performance aspects in particular) of the target
DBMS, whereas logical design is, or should be, DBMS independent. Throughout this topic, then, the unqualified
term design should be understood to mean logical design specifically, barring explicit statements to the contrary.
Now, design theory as such isn't part of the relational model; rather, it's a separate theory that builds on top
of that model. (It's appropriate to think of it as part of relational theory in general, but it's not, to repeat, part of the
relational model per se.) Thus, design concepts such as further normalization are themselves based on more
fundamental notions—e.g., the projection and join operators of the relational algebra—that are part of the relational
model. (All of that being said, it could certainly be argued that design theory is a logical consequence of the
5 This definition is deliberately a little simplified (though it's good enough for present purposes). A better one can be found in SQL and
Relational Theory .
6 Be warned, however, that other writers (a) use the terms logical design and physical design to mean something else and (b) use other terms to
mean what I mean by them. Caveat lector.
Search WWH ::

Custom Search