Physical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 234

a b c

ProdID ProdName ShipID
p1

ShipID ShipName
s1

ProdID ProdName ShipID
p3 TV s1 Fedex MP3 s2

p2 Tablet NULL s2 DHL p4 PC NULL

d e

ProdID ProdName ShipID ShipName
p1

ProdID ProdName ShipID ShipName
p1 TV s1 Fedex TV s1 Fedex

p2 Tablet NULL NULL p2 Tablet NULL NULL

NULL NULL s2 DHL p3 MP3 s2 DHL

p4 PC NULL NULL

Fig. 7.2 An example of maintenance of a full outer join view. ( a )Table Product .( b )
Table Shipper .( c ) Δ + ( Product ). ( d )View ProductShipper .( e ) Resulting view after
the insertions

also delete ( NULL , NULL , s2 , DHL ) (because ( s2 , DHL ) now has a matching
tuple), together with adding ( p3 , MP3 , s2 , DHL ). If the tuple ( p4 , PC , NULL )is
inserted into Product , the left outer join between ( p4 , PC , NULL )and Shipper
yields ( p4 , PC , NULL , NULL ), which is inserted into the view. Figure 7.2 e
shows the final state of the view.

7.2.2 Algorithms Using Partial Information

It is not always possible to maintain a view using only partial information. A
view is called self-maintainable if it can be maintained using only the view
and key constraints. This is important for data warehouses because we do
not want to access base data to update summary tables. Further, we say that
a view is self-maintainable with respect to a modification type T to a base
relation R if the view can be self-maintained for all instances of the database
in response to all modifications of type T over R .
As an example, consider again the view FoodCustomers defined above:

FoodCustomers = π CustomerKey ( σ CategoryName= ' Food ' ( Product ) ∗ Sales )

Suppose that c3 is in the view and we delete the tuple ( p1 , c3 , 100 )from
the relation Sales . We could not delete c3 from the view without checking if
this customer ordered another food product. If in the base relations we find
that there is another tuple in Sales of the form ( p , c3 , q ), such that p is in the
food category, then c3 will remain in the view. Thus, the view FoodCustomers
is not self-maintainable with respect to deletions on Sales . Analogously, this
view is not self-maintainable with respect to insertions into any of the two
base relations, because for any tuple inserted, for example, into Sales ,we