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

suppose that the only products of the category food are p1 and p2 and the
tuples shown are the only ones concerning these products.
Suppose that we delete tuple ( p1 , c2 , 100 )from Sales . Although c2 in
FoodCustomers is derived from the deleted tuple, it has also an alter-
native derivation, through ( p2 , c2 , 50 ). Thus, deleting ( p1 , c2 , 100 )does
not prevent c2 to be in the view. The counting algorithm computes a
relation Δ − ( FoodCustomers ) which contains the tuples that can be derived
from ( p1 , c2 , 100 ), and therefore affected by the deletion of such tuple,
and adds a 1 to each tuple. In this example, Δ − ( FoodCustomers ) will

contain the tuples

−

. Analogously, for dealing with insertions,
Δ + ( FoodCustomers ) extends the tuples with a 1. The updated view (shown
in Fig. 7.1 c) is obtained by joining Δ − ( FoodCustomers ) with the materialized
view FoodCustomers (using the attribute CustomerKey ) and subtracting
Δ − ( FoodCustomers ) . Count from FoodCustomers.Count .Wecanseethat,since
c2 has two possible derivations (Fig. 7.1 b), it will not be removed from
the view; we will only eliminate one possible derivation. If later the tuple
( p2 , c2 , 50 ) gets deleted, c2 will be also eliminated from the view. On the
contrary, c1 would be deleted together with ( p1 , c1 , 20 ).
We analyze now views defined with an outer join. Let us consider two
relations Product ( ProdID , ProdName , ShipID )and Shipper ( ShipID , ShipName )
as depicted, respectively, in Fig. 7.2 a,b. An example of outer join view is as
follows:

{ ( c2 , − 1 ) }

CREATE VIEW ProductShipper AS (
SELECT P.ProdID, P.ProdName, S.ShipID, S.ShipName
FROM Product P FULL OUTER JOIN Shipper S ON
P.ShipID = S.ShipID )

This view is depicted in Fig. 7.2 d. A modification Δ ( Product )toarelation
Product consists in insertions Δ + ( Product ) and deletions Δ − ( Product ). As
usual, updates are considered as deletions followed by insertions. View
maintenance is tackled by rewriting the full outer join as either left or right
outer joins as indicated below, depending on whether we tackle the updates
of the left or the right table of the full outer join. Then, we merge the result
with the view to be updated:

SELECT P.ProdID, P.ProdName, S.ShipID, S.ShipName
FROM Δ ( Product ) P LEFT OUTER JOIN Shipper S ON P.ShipID = S.ShipID

SELECT P.ProdID, P.ProdName, S.ShipID, S.ShipName
FROM Product P RIGHT OUTER JOIN Δ ( Shipper ) S ON P.ShipID = S.ShipID

The first query computes the effect on the view of the changes to Product ,
and the second one does the same with the changes to Shipper .Consider
the two relations Product and Shipper in Fig. 7.2 a,b, as well as Δ + ( Product )
in Fig. 7.2 c containing the tuples inserted in Product . When we insert a
matching tuple like ( p3 , MP3 , s2 ), the projection of the left outer join with
Shipper would be ( p3 , MP3 , s2 , DHL ). In this case, the algorithm should