Databases Reference
In-Depth Information
in particular that (a) supplier S3 has no status, (b) supplier S4 has no status and no city, and (c) supplier S5 has no
name, no status, and no city.
S SN ST SC
┌─────┐ ┌─────┬───────┐ ┌─────┬────────┐ ┌─────┬────────┐
│ SNO │ │ SNO │ SNAME │ │ SNO │ STATUS │ │ SNO │ CITY │
├═════┤ ├═════┼───────┤ ├═════┼────────┤ ├─────┼────────┤
│ S1 │ │ S1 │ Smith │ │ S1 │ 20 │ │ S1 │ London │
│ S2 │ │ S2 │ Jones │ │ S2 │ 30 │ │ S2 │ Paris │
│ S3 │ │ S3 │ Blake │ └─────┴────────┘ │ S3 │ Paris │
│ S4 │ │ S4 │ Clark │ └─────┴────────┘
│ S5 │ └─────┴───────┘
└─────┘
Fig. 15.3: An “RM/T design” for suppliers─sample values
As I indicated in Chapter 13, this kind of design actually has quite a lot to recommend it (at least, it would do
so given a well architected DBMS). For present purposes, however, all I want to do is call your attention to the
following: So long as every entity of type
E
has at least one of the
n
properties, then the design certainly involves
some redundancy─arguably, in fact,
strong
redundancy as defined by Codd himself in his 1970 paper─because, at
any given time, the value of the E-relvar will be equal to the union of the projections of the P-relvars over the
identifier attribute:
CONSTRAINT ... S { SNO } =
UNION { SN { SNO } , ST { SNO } , SC { SNO } } ;
This kind of redundancy would apply to Fig. 15.3, for example, if we deleted supplier S5 (that is, if every supplier
had at least one of the three properties name, status, and city).
Exercise for the reader:
How does the redundancy
here differ from that discussed under Example 4?
Does
it differ? Would it make any difference if the employees in
Example 4 had additional properties (for example, salaries)?
Observe further that the design becomes “even more redundant,” as it were, in the (common?) special case in
which every entity of type
E
in fact has all of the
n
properties. Fig. 15.4 is a revised version of Fig. 15.3 that
illustrates this situation. Note in that figure that─speaking a trifle loosely─{SNO} is now a foreign key in each of
the relvars that references the sole key {SNO} in each of the others; equivalently, the projection on {SNO} of any
of the relvars is equal to the projection on {SNO} of any of the others. Well ... to be more precise about the matter,
there's actually an equality dependency interrelating every pair of the four relvars:
CONSTRAINT ...
IDENTICAL { S { SNO } , SN { SNO } , ST { SNO } , SC { SNO } } ;
Note:
IDENTICAL is an operator proposed by Hugh Darwen and myself
12
as an addition to
Tutorial D
, with
semantics as follows: The expression
IDENTICAL {
r1
, ... ,
rn
}
returns TRUE if the relations
r1
, ...,
rn
are all equal and FALSE otherwise (you can think of it as a kind of
n-
adic
“=” operator).
12
In
Database Explorations: Essays on The Third Manifesto and Related Topics
(Trafford, 2010) and elsewhere.
