Databases Reference
In-Depth Information
Note:
In order to guarantee that this design is formally equivalent to the original one, I
should really state, and have the DBMS enforce, certain integrity constraints—including in
particular constraints to the effect that every CITY value in LS is London and no CITY value in
NLS is—but I want to ignore such details for the moment. I'll have a lot more to say about such
matters in a little while, I promise you.
Anyway, the message of the example is that, in general, which tables are base ones and
which ones are views is arbitrary (at least from a formal point of view). In other words, in the
case at hand, we could design the database in at least two different ways—ways, that is, that are
logically distinct but information equivalent. (By
information equivalent
here, I mean the two
designs represent the same information, implying among other things that for any query on one,
there's a logically equivalent query on the other. Chapter 3 elaborates on this concept.) And
The
Principle of Interchangeability
is a logical consequence of such considerations:
Definition:
The Principle of Interchangeability
states that there must be no arbitrary and
unnecessary distinctions between base tables and views; in other words, views should—as
far as possible—“look and feel” just like base tables so far as users are concerned.
Here are some implications of this principle:
As I've already suggested, views are subject to integrity constraints, just like base tables.
(We usually think of integrity constraints as applying to base tables specifically, but
The
Principle of Interchangeability
shows this position isn't really tenable.)
In particular, views have keys (and so I ought really to have included some key
specifications in my view definitions; unfortunately, however, SQL doesn't permit such
specifications).
2
They might also have foreign keys, and foreign keys might refer to them.
Many SQL products, and the SQL standard, provide some kind of “row ID” feature (in the
standard, that feature goes by the name of
REF types
and
reference values
). If that feature
is available for base tables but not for views—which in practice is quite likely—then it
clearly violates
The Principle of Interchangeability
.
Perhaps most important of all,
we must be able to update views
—because if not, then that
fact in itself would constitute the clearest possible violation of
The Principle of
Interchangeability
.
2
Throughout this topic I use the term
key
, unqualified, to mean a candidate key, not necessarily a primary key specifically. In
fact,
Tutorial D
—see Chapter 2—has no syntax for distinguishing between primary and other keys. For reasons of familiarity,
however, I use double underlining in figures like Fig. 1.1 to suggest that the attributes so underlined can be thought of as primary
key attributes, if you like.
