Databases Reference
In-Depth Information
The predicates are as follows:
PL′:
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).
PK′:
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 IN_STOCK is TRUE).
ULK′:
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 or IN_STOCK is TRUE).
As for constraints, each of these relvars has {PNO} as sole key, and {PNO} is also a
foreign key, referencing ULK′, in each of PL′ and PK′. The following constraints also hold:
2
CONSTRAINT ... IS_EMPTY ( PL′ WHERE NOT ( ON_SALE ) ) ;
CONSTRAINT ... IS_EMPTY ( PK′ WHERE NOT ( IN_STOCK ) ) ;
CONSTRAINT ... IS_EMPTY ( ULK′ WHERE NOT ( ON_SALE OR IN_STOCK ) ) ;
Here now repeated from Chapter 9 are the update rules connecting 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′ ;
ON INSERT
i
INTO PL′ : INSERT
i
INTO POI ;
ON INSERT
i
INTO PK′ : INSERT
i
INTO POI ;
ON DELETE
d
FROM PL′ : DELETE
d
FROM POI ;
ON DELETE
d
FROM PK′ : DELETE
d
FROM POI ;
And here are the rules connecting relvars PL′ and PK′ and relvar ULK′:
ON INSERT
i
INTO PL′ : INSERT
i
INTO ULK′ ;
ON INSERT
i
INTO PK′ : INSERT
i
INTO ULK′ ;
ON DELETE
d
FROM PL′ : DELETE (
d
WHERE NOT ( IN_STOCK ) ) FROM ULK′ ;
ON DELETE
d
FROM PK′ : DELETE (
d
WHERE NOT ( ON_SALE ) ) FROM ULK′ ;
2
As in Chapter 9, 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
.