1. Definition
Set F is a function dependency set of relationship mode R
Ρ={r 1<u 1,f 1>, R 2<u 2,f 2>,..., R k<u k,f K>} is a decomposition of R,
If R satisfies any of the relationship R of F: r=mρ (R),
It is said that decomposition ρ has a non-destructive connectivity.
2. lemma
Set Ρ={r1<u1,f1>, r2<u2,f2>,..., Rk<uk,fk>}
For a decomposition of the relational mode R, R is any relationship of R, Ri=πui (R),
The
①r≤mρ (r) (table R included in mρ (R))
② if s=mρ (r), then Πui (s) =ri
③mρ (mρ (R)) =mρ (R)
Certificate
① sets T as any tuple of relation R, Ti=t[ui]∈ri (i=1,2,..., k),
According to the definition of natural connection, T1,t2,..., Ti∈πui (R), that is, t∈mρ (R),
So there is r≤mρ (r) (the table is contained in mρ (R)).
②, ③ 's proof see P121.
Conclusion: The relationship between decomposition and natural connection must contain the relationship before decomposition,
That decomposition does not lose information, but may increase information,
Only r=mρ (R), decomposition has a non-destructive connectivity.
Example 4.4.2: Set ρ (R), which can be concluded.
Solution:Conclusion: Decomposition does not have non-destructive connectivity.
3, why the relationship decomposition
After a relational pattern is decomposed, it can store information that could not be stored.
Usually called "hanging" tuples, which is actually required, is the advantage of decomposition.
In natural joins, such hanging tuples are naturally lost,
But not the loss of information, but reasonable. 4. Algorithm for testing decomposition and non-destructive connectivity
With the relationship mode R (A1,a2,..., an), F is its function dependency set,
Ρ={R1,R2,..., Rk} is a decomposition of R.
Algorithm:
(1) Construct the initial table:
Constructs an initial table of K row n columns, where each column corresponds to an attribute of R.
Each row is used to represent a pattern composition after decomposition.
If the attribute AJ belongs to a relational mode RI, then the first I row of Table J is listed in the symbol AJ,
Otherwise, the symbol BIJ.
(2) Modify the table contents according to the function dependency in F:
Examine each function in F that relies on x→y, on those columns where attribute group X resides
Look for lines with the same symbol, if two or more rows are found,
The rows are modified so that the same elements are on the same column as the property group Y on those rows.
The rule to modify is: if Y is in a row to be modified with a AJ,
These elements all become AJ, otherwise the change is BMJ (where M is the minimum line number for these lines).
Note: If a bij is altered, the same symbol in that column is changed for the same bij.
Loop through the function dependencies in F until a row is found in the table
becomes A1,A2,... an or can no longer be modified.
(3) Determine if decomposition is a lossless join:
If modified, a row in the Discovery table becomes A1,A2,... an,
The decomposition is a lossless join, otherwise the decomposition does not have a non-destructive connectivity.
Algorithm implementation:
Input: Attribute set on relationship R u={a1,a2,..., Ak}, function dependency set F on R,
Decomposition of R ρ={r1,r2,..., Rk}.
Output: If ρ is non-destructive decomposition is true, otherwise false.
Lossless (r,f,ρ)
{Constructing the initial table rρ
Change= true;
while (change)
{for (each function in F is dependent on x→y)
{if (rρ in ti1[x]=ti2[x]=...=tim[x])
{change ti1[y],ti2[y]=,..., tim[y] to the same}
If (there is a behavior in rρ a1,a2,... an)
{return true;}
}
if (modified table rρ= Pre-modified table rρ)
{chang= false;}
}
If (there is a behavior in rρ a1,a2,... an) {return true;}
else {return false;}
}
Example 4.4.3: Relationship mode R (SAIP), F={S→A,SI→P},Ρ={R1 (SA), R2 (SIP)},
Tests whether decomposition is a lossless join.
By modifying the second row of elements in the discovery table into A1,a2,..., An, the decomposition is a lossless join.
Example 4.4.4: Known relationship mode R (ABCDE) and function dependency set F={a→c,b→c,c→d,de→c,ce→a}
Verify that the decomposition Ρ={r1 (AD), R2 (AB), R3 (BE), R4 (CDE), and R5 (AE)} are non-destructive joins.
By modifying the third row of elements in the discovery table into A1,a2,..., An, the decomposition is a lossless join.
Exercise 4.4.1:
Known relational mode R (U,F),
U={sno,cno,grade,tname,tage,office},
f={(SNO,CNO) →grade,cno→tname,tname→ (Tage,office)},
and two decomposition ρ1={sc,ct,to},ρ2={sc,gto} on R,
Where Sc={sno,cno,grade},ct={cno,tname},to={tname,tage,office},
Gto={grade,tname,tage,office}.
Test the non-destructive connection of ρ1,ρ2.
Answer: Ρ1 is non-destructive decomposition, ρ2 is not lossless decomposition.
5. Theorem 4.4.1
The algorithm of testing decomposition non-destructive connectivity can correctly determine a decomposition
Whether it has a non-destructive connectivity. (Proof: See textbook P124)
6. Theorem 4.4.2
Set Ρ={R1, R2} is a decomposition of the relationship mode R, F is a function dependent set of R,
Then ρ is the sufficient and necessary condition for the non-destructive decomposition of R (about F):
(R1∩R2) →r1-r2∈f+ or (R1∩R2) →r2-r1∈f+
(Proof: See textbook P124)
Example 4.4.5: Relationship mode R (SAIP), F={S→A,SI→P},Ρ={R1 (SA), R2 (SIP)}
Tests whether decomposition is a lossless join.
Solution: R1∩r2=sa∩sip=s r1-r2=sa-sip=a,s→a∈f, so ρ is lossless decomposition.
7. Theorem 4.4.3 (stepwise decomposition theorem--relationship mode can be decomposed gradually)
Set F is a function dependency set of relationship mode R, Ρ={r1,r2,..., Rk} is a lossless join of R about F.
Decomposition:
(1) If Σ={s1,s2,..., Sm} is a non-destructive join decomposition of ri about FI, then
Ε={R1,..., ri-1,s1,s2,..., sm,ri+1,..., Rk} is a lossless join decomposition of R about F.
where Fi=πri (F).
(2) Set Τ={R1,..., rk,rk+1,..., Rn} is a decomposition of R, wherein τ≥ρ (table tau contains ρ),
Then Tau is also r about F's lossless join decomposition.
has a relational pattern R (a,b,c), ρ={r1,r2} for one of its decomposition,
Where R1=ab,r2=bc,r is a relationship of R, R1=πr1 (R), R2=ΠR2 (R),
Beg R1,r2,m |
|
1. Definition
Set F is the function dependency set of the relationship mode R,
Ρ={r1<u1,f1>, r2<u2,f2>,..., Rk<uk,fk>} is a decomposition of R,
If Fi=πri (F) is the same as the set (F1∪f2∪ ... ∪FK) ≡f (i=1,2,..., k)
The decomposition ρ is called a function-dependent persistence.
2. Examples and Exercises
Example 4.4.6: r= (Abcd,{a→b,b→c,b→d,c→a}) is decomposed into
About U1=AB,U2=ACD two relations, seeking R1, R2,
And the decomposition of the non-destructive connectivity and decomposition of function-dependent persistence.
Solution: F1=πr1 (F) ={a→b,b→a},
F2=ΠR2 (F) ={a→c,c→a,a→d}
R1= (Ab,{a→b,b→a})
R2= (Acd,{a→c,c→a,a→d})
U1∩u2=ab∩acd=a,
U1-u2=ab-acd=b,a→b∈f,
So ρ is non-destructive decomposition;
F1uf2={a→b,b→a,a→c,c→a,a→d}≡{a→b,b→c,b→d,c→a}=f
So ρ is the function of dependency retention.
Example 4.4.7: Relationship mode R (a,b,c,d) function dependency set F={A→B,C→D},Ρ={R1 (AB), R2 (CD)}
The R1,R2, and the decomposition of the non-destructive connectivity and decomposition of functional dependency retention.
Solution: F1=πr1 (F) ={a→b},
F2=ΠR2 (F) ={c→d}
R1 (Ab,{a→b}),
R2 (Cd,{c→d})
U1∩u2=ab∩cd=φ,
U1-u2=ab,
U2-U1=CD,
Φ→ABF,
Φ→CDF,
So ρ is not a lossless decomposition.
Exercise 4.4.2: Known relational mode R (City,st,zip), f={(city,st) →zip,zip→city}
and a decomposition ρ={r1 on R, R2}, R1 ={st,zip}, R2 ={city,zip}
The R1,R2, and the decomposition of the non-destructive connectivity and decomposition of functional dependency retention.
Answer: r1= ({st,zip},{φ}) r2= (City,zip,{zip→city})
ρ is a lossless decomposition, but does not have the function of dependency retention.
|
