5.4 A generalized approach to algebraic view maintenance
5.4.6 Join
The join delta rules found in Table 4.1 are repeated here for the readers con- venience. Note that besides the delta relations the base relations are required to incrementally maintain join views.
∆(S ◃▹ T )≡ (So ◃▹ ∆T )∪ (∆S ◃▹ To)∪ (∆S ◃▹ ∆T )
∇(S ◃▹ T ) ≡ (So ◃▹∇T ) ∪ (∇S ◃▹ To)∪ (∇S ◃▹ ∇T )
In the data warehouse environment, source systems are decoupled and base relations are usually available in their new state only. However, the preserved state referenced in the above equations can be reconstructed from the new state and the insert deltas. Given a relation R, the preserved state Ro can
be computed by subtracting the insert delta set from the new state (Ro :=
Rnew− ∆R). Note that both, the Rnew and ∆R are non-partial and thus Ro
is non-partial too. When used in the deletion delta rule, the preserved state relation Ro is hence assigned with a comp type flag.
In this chapter, we focus on the maintenance of so-called dimension views defined in Section 5.4.2. All join predicates used in dimension views follow a common pattern. They are equality predicates and involve the primary key attribute of at least one relation. In the following, we consider the join of two relations S and T with the join predicate (S.a = T.pk ) where S.a is an arbitrary attribute of S and T.pk the primary key attribute of T . It is important to understand that the type of the resulting (joined) delta tuples depends on the type of both input delta tuples. To adapt the join delta rules, all possible combinations of delta types need to be considered. The different combinations are represented by the matrices in Figure 5.7 and Figure 5.8. Consider the
S
pre ins del pup ups tpdel spdel
T
pre - ins del
ups ups spdel spdel
ins ins ins -
del del - del
un un ins -
uo uo - del
pup pup ins -
ups ups ins -
tpdel tpdel - tpdel
spdel spdel - spdel
Figure 5.7: Partial delta type join matrix
matrix in Figure 5.7. The column headings represent the different delta sets of S participating in the join. From left to right, there are preserved tuples, insertions, deletions, partial updates, upserts, true partial deletions, and safe partial deletions. For the sake of clarity, update pairs are shown in a separate matrix (Figure 5.8). The row headings in the matrix represent the different delta sets of T participating in the join. The cells of the matrix indicate the delta type resulting from a join between the corresponding delta sets of S and
T .
Consider the matrix cell at the intersection of the Sins column and the Tpup
row, for instance. The cell indicates that the join result of these delta sets is to be propagated as insertion. This is obvious considering that any tuple added to S has a key that is unique in S. Thus, the key cannot be in the view yet. Hence, the result of the join is an insertions w.r.t. the view.
Consider the four right-most columns in the matrix referring to partial up- dates, upserts, and true and safe partial deletions in S. These deltas lack certain attribute values. They hence cannot be joined to Told, because the
join predicate cannot be evaluated. Consider a partial update in S. Recall, that the initial state of the updated tuple is not available. Hence, it is unclear whether the updated tuple used to find a join partner in Toldbefore the update.
Assuming that the updated tuple joins to a tuple in Tnew, the result tuple is
either an update to the view (if the updated tuple used to find a join partner before) or an insertion (if it did not). Since these cases cannot be distinguished for partial updates, an upsert has to be propagated.
Similarly, it is unclear whether partial deletions in S used to find a join part- ner in Told before the deletion. The propagated partial deletion is thus either
effective (if it used to find a join partner) or ineffective (if it did not). Both true and safe partial deletion, are hence propagated as safe partial deletions.
Consider an upsert in S. For upserts, it is unknown whether the tuple was in fact updated or inserted. If an upsert in S joins to a tuple in Tnew, it is
S Tnew S Told result S Tnew S Told result
un - uo - - un un uo - ins
un - uo pre del un un uo pre un/uo
un - uo uo del un un uo uo un/uo
un - uo del del un un uo del un/uo
un - uo pup tpdel un un uo pup pup
un - uo ups spdel un un uo ups ups
un - uo tpdel tpdel un un uo tpdel pup
un - uo spdel spdel un un uo spdel ups
un pre uo - ins un pup uo - ins
un pre uo pre un/uo un pup uo pre un/uo
un pre uo uo un/uo un pup uo uo un/uo
un pre uo del un/uo un pup uo del un/uo
un pre uo pup pup un pup uo pup pup
un pre uo ups ups un pup uo ups ups
un pre uo tpdel pup un pup uo tpdel pup
un pre uo spdel ups un pup uo spdel ups
un ins uo - ins un ups uo - ins
un ins uo pre un/uo un ups uo pre un/uo
un ins uo uo un/uo un ups uo uo un/uo
un ins uo del un/uo un ups uo del un/uo
un ins uo pup pup un ups uo pup pup
un ins uo ups ups un ups uo ups ups
un ins uo tpdel pup un ups uo tpdel pup
un ins uo spdel ups un ups uo spdel ups
Figure 5.8: Partial delta type join matrix (continued)
view. Hence, upserts in S are again propagated as upserts.
The matrix in Figure 5.8 represents joins involving update pairs in S. Recall that the new state of an updated tuple is joined to the new state of T (Tnew)
while the old state of an updated tuple is joined to the old state of T (Told)
in the delta rules for change propagation. The matrix shows all possible join combinations. Let s be an update pair in Sun/uo, sunthe new state of s, and suo
the old state of s. The first two columns of the matrix indicate where sunfinds
a join partner in Tnew. There are the following possibilities: A join partner may
not exists, it may be a preserved tuple, an inserted tuple, an updated tuple in its new state, a partial update, or an upsert.
The third and fourth column indicate where Suo finds a join partner in Told.
A join partner may not exists, it may be a preserved tuple, an updated tuple in its old state, a deleted tuple, a partial update, an upsert, a true partial deletion, or a safe partial deletion. The fifth column indicates the type of the delta resulting from the joins. As an example, consider the second row of the matrix. It treats the case where sundoes not find a join partner in Tnew, while
suo used to join to a preserved tuple (i.e. the join attribute of s was updated).
Hence, a tuple Suo◃▹ Toused to be in the view and needs to be discarded now.
Thus, the resulting delta is of type deletion.
tuple in Told, namely a preserved tuple, an updated tuple, or a deleted tuple,
the resulting delta tuple is again complete, i.e. an update pair or a deletion. When suojoins to a value-partial delta in Told, i.e. a partial update or a partial
deletion, the resulting delta tuple is again value-partial. Whether the resulting delta tuple is a partial update or a partial deletion is decided based on the existence of a corresponding delta tuple in the insert set ∆(S ◃▹ T ), when the deltas are converted to the explicit model (see Section 5.4.3). When suo joins
to a type-partial delta in Told, i.e. an upsert or a safe partial deletion, the
resulting delta tuple is type-partial again. Based on these considerations and the considerations that lead to the first join matrix, the join delta rules are adapted as follows to handle partial deltas.
∆(S ◃▹ T )≡ (So ◃▹ ∆T )∪ (∆S ◃▹ To)∪ (∆S ◃▹ ∆T )
∇(S ◃▹ T ) ≡ π...,T .flag((So ◃▹∇T ) ∪ (∇S ◃▹ To)∪ (∇S ◃▹ ∇T ))∪
π...,s(flag)(σflag̸=comp(∇S))
Once again, the delta rule for computing the insert delta set remains un- changed. The delta rule for the delete delta set is changed in the following way. Note that the initial three joins are evaluated for complete S tuples only, because partial S tuples have null-padded attribute values. As shown above, in this case the type of the resulting tuples are determined by the type of the join- ing T tuples. Hence the type flag is reused in the result. An additional term is added to the rule to handle partial tuples in∇S that lack the attribute values required to compute the join with Told. In this additional term the function s
(defined in Section 5.4.5) is reused to modify the type flag appropriately. The delta rules implement the delta type transitions suggested by the ma- trices in Figure 5.7 and 5.8. Note that any combination of delta types yields a delta type that can be expressed in the partial delta model proposed in Sec- tion 5.4.1. That is, partial deltas are closed under join operations permissible in dimension view definitions.
Example 5.4 Reconsider the running example. The sample dimension view
was defined asD = C′◃▹A′. The incremental expressions ∆D and ∇D can be derived using the delta rules given above.
∆D ≡ (Co′ ◃▹ ∆A′)∪ (∆C′◃▹ A′o)∪ (∆C′◃▹ ∆A′)
∇D ≡ πCID ,CName,AID ,ACity,ACountry,A′.flag)(Co′ ◃▹∇A′)∪
πCID ,CName,AID ,ACity,ACountry,A′.flag)(∇C′◃▹ A′o)∪
πCID ,CName,AID ,ACity,ACountry,A′.flag)(∇C′◃▹∇A′)∪
∆D
CID CName AID ACity ACountry
1 Adam 1 Essen DE
2 Bob 4 Dresden DE
4 Dave 4 Dresden DE
∇D
CID CName AID ACity ACountry flag
1 Adam 1 - - ptype
2 Bob 2 Berlin DE comp
3 Carl 3 - - ptype
Table 5.4: Result of the sample incremental expressions ∆D and∇D
Note that the preserved relation states referenced above are computed as fol- lows.
Co′ = Cnew′ − ∆C′= πattrC(Cnew)− (πattrC(∆C)− πattrC(∇C)) with attrC ={CID, CName, AID}
A′o= A′new− ∆A′= σp(Anew)− σp(∆A)
Alternatively, these relations may be derived from the preserved states of the base relations Coand Ao instead of the current states Cnewand Anew, whatever
is more appropriate.
Co′ = πattrC(Co∪ ∆C) − (πattrC(∆C)− πattrC(∇C))
= πattrC(Co)∪ (πattrC(∆C)∩ πattrC(∇C))
A′o = σp(Anew− ∆A) = σp(Ao)
The result deltas are depicted in Figure 5.4. When ∆D and∇D are converted back to the explicit model, we obtain an insertion (ID 4), an upsert (ID 1), an update pair (ID 2), and a partial deletion (ID 3).