Databases Reference
In-Depth Information
VIEWS: CONSTRAINTS AND COMPENSATORY ACTIONS
Now I come to the real point of this chapter:
Everything I've said in the previous two sections
applies pretty much unchanged if some or all of the tables concerned are views.
For example,
suppose as we originally did that S is a base table and LS and NLS are views:
CREATE TABLE S ( .............. , UNIQUE ( SNO ) ) ;
CREATE VIEW LS AS ( SELECT ... WHERE CITY = 'London' ) ;
CREATE VIEW NLS AS ( SELECT ... WHERE CITY <> 'London' ) ;
Now consider a user who sees only views LS and NLS, but wants to be able to behave as if
those views were actually base tables. As far as that user is concerned, then, those tables have
semantics as follows:
LS:
Supplier SNO is under contract, is named SNAME, has status STATUS, and is located
in city CITY (which is London).
NLS:
Supplier SNO is under contract, is named SNAME, has status STATUS, and is
located in city CITY (which is not London).
That same user will also be aware of the following constraints (note that these constraints
make no mention of table S, because the user in question doesn't even know table S exists):
{SNO} is a key for both LS and NLS.
Every row in LS has CITY value London, and no row in NLS does.
No supplier number appears in both LS and NLS.
However, that user won't be aware of any compensatory actions as such, precisely because
he or she isn't aware that LS and NLS are actually views of S; indeed, as I've already said, the
user isn't even aware of the existence of S (which is why that user is also unaware of the
constraint to the effect that the union of LS and NLS is equal to S). But updates by that user on
LS and NLS will all work as far as that user is concerned exactly as if LS and NLS really were
base tables. Also, of course, updates by that user on LS and NLS will have the appropriate
effects on S, even though those effects won't be directly visible to that user.
