Databases Reference
In-Depth Information
SHIPMENT { SHIPNO , INVNO , SHIP_DETAILS }
PRIMARY KEY { SHIPNO }
ALTERNATE KEY { INVNO }
FOREIGN KEY { INVNO } REFERENCES INVOICE
So the database structure is as shown in Fig. A.1 (note that the arrows in that figure, in contrast to arrows in
figures elsewhere in this topic, represent foreign key references, not functional dependencies):
┌──────────────────────────────────────┐
┌───▼───┬────────┬──────┐ ┌────────┬───┼───┬─────┐
│ INVNO │ SHIPNO │ ... │ │ SHIPNO │ INVNO │ ... │
└═══════┴────┼───┴──────┘ └════▲═══┴───────┴─────┘
INVOICE └─────────────────────┘ SHIPMENT
Fig. A.1: The invoices-and-shipments database
Now, each relvar in this example actually has two keys, {INVNO} and {SHIPNO}. However, I assume we
can agree for the sake of argument that the “natural” primary key for INVOICE is {INVNO} and the “natural”
primary key for SHIPMENT is {SHIPNO}; then {SHIPNO} in INVOICE and {INVNO} in SHIPMENT are
alternate keys. Furthermore, of course, each of those alternate keys is also a foreign key (as Fig. A.1 indicates),
referring to the primary key of the other relvar.
One problem with the foregoing design is as follows. Clearly, the database is subject to the
constraint─actually it's an equality dependency, and I'll call it
C
─that if the INVOICE relvar shows invoice
i
as
corresponding to shipment
s
, then the SHIPMENT relvar must show shipment
s
as corresponding to invoice
i
(and
vice versa):
7
CONSTRAINT
C
INVOICE { INVNO , SHIPNO } = SHIPMENT { INVNO , SHIPNO } ;
In other words, the tuple (
i
,
s
,...) appears in INVOICE if and only if the tuple (
s
,
i
,...) appears in SHIPMENT. But
the design of Fig. A.1 doesn't capture or enforce this constraint (for example, the configuration of values shown in
Fig. A.2 below is permitted by that design and yet violates the constraint). The constraint thus needs to be
separately stated (as above) and separately enforced.
INVOICE SHIPMENT
┌───────┬────────┬─────┐ ┌────────┬───────┬─────┐
│ INVNO │ SHIPNO │ ... │ │ SHIPNO │ INVNO │ ... │
├═══════┼────────┼─────┤ ├════════┼───────┼─────┤
│
i1
│
s1
│ ... │ │
s1
│
i2
│ │
│
i2
│
s2
│ ... │ │
s2
│
i1
│ │
└───────┴────────┴─────┘ └────────┴───────┴─────┘
Fig. A 2: “Legal” INVOICE and SHIPMENT values that violate constraint C
Aside:
It might be thought that if we pretended the primary key for each relvar was the
combination
{INVNO,SHIPNO}, and if we further defined each of those fake “primary keys” to be a foreign key
referencing the other, then constraint
C
would be taken care of automatically.
8
But the relational model
7
Observe, therefore, that the design violates orthogonality (see Chapter 14).
8
I've actually seen such a subterfuge explicitly recommended, by people who really ought to know better.
