Databases Reference
In-Depth Information
PL′ DLK′
┌─────┬─────────┬──────────┐ ┌─────┬─────────┬──────────┐
│ PNO │ ON_SALE │ IN_STOCK │ │ PNO │ ON_SALE │ IN_STOCK │
├═════┼─────────┼──────────┤ ├═════┼─────────┼──────────┤
│ P1 │ TRUE │ FALSE │ │ P1 │ TRUE │ FALSE │
│ P2 │ TRUE │ TRUE │ │ P3 │ TRUE │ FALSE │
│ P3 │ TRUE │ FALSE │ │ P6 │ TRUE │ FALSE │
│ P6 │ TRUE │ FALSE │ └─────┴─────────┴──────────┘
└─────┴─────────┴──────────┘
PK′
┌─────┬─────────┬──────────┐
│ PNO │ ON_SALE │ IN_STOCK │
├═════┼─────────┼──────────┤
│ P2 │ TRUE │ TRUE │
│ P5 │ FALSE │ TRUE │
└─────┴─────────┴──────────┘
Fig. 11.3: Relvars PL′, PK′, and DLK′—sample values
Each of these relvars has {PNO} as sole key, and {PNO} is also a foreign key, referencing
PL′, in DLK′. The following constraints also obviously hold:
5
CONSTRAINT ... IS_EMPTY ( PL′ WHERE NOT ( ON_SALE ) ) ;
CONSTRAINT ... IS_EMPTY ( PK′ WHERE NOT ( IN_STOCK ) ) ;
CONSTRAINT ... IS_EMPTY ( DLK′ WHERE NOT ( ON_SALE ) OR IN_STOCK ) ) ;
As in Chapter 9 (though not Chapter 10), we need an additional 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 be enforced automatically if relvars PL′ and PK′ are indeed, as
stated, views of POI.
Here now repeated from Chapters 9 and 10 are the update rules involving relvar POI and
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′ ;
5
As in Chapters 9 and 10, there's also a constraint to the effect that the restrictions PL′ WHERE IN_STOCK and PK′ WHERE
ON_SALE must be equal, as a consequence of which relvars PL′ and PK′ violate
The Principle of Orthogonal Design
.
