Databases Reference
In-Depth Information
XLK′:
Part PNO is on sale if and only if ON_SALE is TRUE and in stock if and only if
IN_STOCK is TRUE (and ON_SALE is TRUE and IN_STOCK is TRUE).
As for constraints, note first that each of the three relvars has {PNO} as sole key, and
{PNO} in XLK′ is a foreign key, referencing both PL′ and PK′. The following constraints also
obviously hold:
CONSTRAINT ... IS_EMPTY ( PL′ WHERE NOT ( ON_SALE ) ) ;
CONSTRAINT ... IS_EMPTY ( PK′ WHERE NOT ( IN_STOCK ) ) ;
CONSTRAINT ... IS_EMPTY ( XLK′ WHERE NOT ( ON_SALE AND IN_STOCK ) ) ;
We also need a constraint to the effect that if some part is represented in both PL′ and PK′,
then the two tuples representing that part are in fact one and the same:
CONSTRAINT ... UNION { PL' , PK' } KEY { PNO } ;
Of course, this constraint will automatically be enforced if relvars PL′ and PK′ are indeed, as
stated, views of POI.
Aside:
By the way, we also have the following:
CONSTRAINT ... ( PL' WHERE IN_STOCK ) = ( PK' WHERE ON_SALE ) ;
As a consequence (of the fact that this constraint holds, that is), relvars PL′ and PK′, like
relvars NLS and NPS in the previous section, clearly violate
The Principle of Orthogonal
Design
. In other words, I seem to be contravening one of my own design recommendations
in this example! To be specific, in
Database Design and Relational Theory
, I suggested
rather strongly that orthogonality should never be violated. But of course the violation here
won't hurt, because the redundancy it causes will certainly be controlled.
End of aside
.
Be that as it may, what happens to the update rules? Well, first we have to consider how
updates on relvar POI itself affect relvars PL′ and PK′:
ON INSERT
i
INTO POI :
INSERT (
i
WHERE ON_SALE ) INTO PL′ ,
INSERT (
i
WHERE IN_STOCK ) INTO PK′ ;
ON DELETE
d
FROM POI :
DELETE (
d
WHERE ON SALE ) FROM PL′ ,
DELETE (
d
WHERE IN_STOCK ) FROM PK′ ;
Next we have to consider how updates on relvars PL′ and PK′ affect relvar POI:
ON INSERT
i
INTO PL′ : INSERT
i
INTO POI ;
