Definition:Non-destructive join decomposition is to decompose a relational pattern into several relational patterns, which can still be restored to the original relational pattern through natural joins and projections, which is called decomposition of lossless join.can be restored
Example 1:Relationship mode: Score (student number, name, course number, course name, score) function dependent: Student number--name, course number, class name, (student number, course number)--and fractions if it is decomposed into the following three relationship modes: grades (school number, course number, score) students (school number, name) course (Course number , the course name) asks, is this decomposition a non-destructive decomposition? ----because: the name of the study number, so: score (School Number, course number, score,nameBecause: Course number--course name, so: Score (school number,Course Number, score, name,Course NameSo this example is a lossless decomposition
Example 2:Set R=ABCDE, R1=AD,R2=BC,R3=BE,R4=CDE, r5=ae, function dependent: a->c, B->c, C->d, De->c, ce->a. Judging R decomposition into Ρ={r1, R2, R3, R 4, R5} non-destructive join decomposition? Solution: Such a problem to be solved by drawing a table, first, the original table:
|
A |
B |
C |
D |
E |
| AD |
A1 |
B12 |
B13 |
A4 |
B15 |
| Bc |
B21 |
A2 |
A3 |
B24 |
B25 |
| Be |
B31 |
A2 |
B33 |
B34 |
A5 |
| Cde |
B41 |
B42 |
A3 |
A4 |
A5 |
| Ae |
A1 |
B52 |
B53 |
B54 |
A5 |
Table 1 (a B C D E is the property of the relationship R, AD, BC, be, CDE, AE is the attribute set corresponding to each relationship after decomposition) the process of filling in the form: when there is a single attribute of the corresponding column in the decomposition relationship (for example, the first row of ad with a cell that intersects with a, the ad contains a Write A1), then fill in a subscript, the subscript is the column number where the cell corresponds. Otherwise fill in the B subscript, subscript is the cell corresponding to the row number. The initial table after completion is shown in table 12. Modify the original table according to the dependency relationship:for dependency A->c, there are two rows in column A A1 are equal (the first and fifth rows), so the corresponding two rows in column C should also be equal, but see that both lines are B (b13,b53), so change this B to B13 (the smaller subscript above)
|
A |
B |
C |
D |
E |
| AD |
A1 |
B12 |
B13 |
A4 |
B15 |
| Bc |
B21 |
A2 |
A3 |
B24 |
B25 |
| Be |
B31 |
A2 |
B33 |
B34 |
A5 |
| Cde |
B41 |
B42 |
A3 |
A4 |
A5 |
| Ae |
A1 |
B52 |
B53àb13 |
B54 |
A5 |
for dependent bàc, the same reason, see B in this column, the second row and the third row are A2, then the C column of the same operation, but see c this column in the second row is A3, then change the third row to A3, the priority is higher than B.
|
A |
B |
C |
D |
E |
| AD |
A1 |
B12 |
B13 |
A4 |
B15 |
| Bc |
B21 |
A2 |
A3 |
B24 |
B25 |
| Be |
B31 |
A2 |
B33àa3 |
B34 |
A5 |
| Cde |
B41 |
B42 |
A3 |
A4 |
A5 |
| Ae |
A1 |
B52 |
B13 |
B54 |
A5 |
for the Dependent càd,c column of 1, 5 lines are equal, D of 1, 5 lines should also be equal, the 1th row of D has a, so b54 to A4, and the 2,3,4 row of the C column is equal, the 2,3,4 row of D should be equal, the 4th line of D has a, so the corresponding line is replaced by A4
|
A |
B |
C |
D |
E |
| AD |
A1 |
B12 |
B13 |
A4 |
B15 |
| Bc |
B21 |
A2 |
A3 |
B24àa4 |
B25 |
| Be |
B31 |
A2 |
A3 |
B34àa4 |
A5 |
| Cde |
B41 |
B42 |
A3 |
A4 |
A5 |
| Ae |
A1 |
B52 |
B13 |
B54àa4 |
A5 |
for Deàc, the equivalent line of the de public is 3,4,5 row, corresponding to the 3,4,5 line of C should also be equal, so the C column of the two b13 into A3, so the table through this function dependency, is:
|
A |
B |
C |
D |
E |
| AD |
A1 |
B12 |
B13àa3 |
A4 |
B15 |
| Bc |
B21 |
A2 |
A3 |
A4 |
B25 |
| Be |
B31 |
A2 |
A3 |
A4 |
A5 |
| Cde |
B41 |
B42 |
A3 |
A4 |
A5 |
| Ae |
A1 |
B52 |
B13àa3 |
A4 |
A5 |
for ceàa, the common line of the CE is 3,4,5 row, so the 3,4,5 line of a is also equal, because the fifth row of column A contains A1, so the b31,b41 of 3 and 4 rows is replaced by A1The final table is: Finally, we see from the table for the De line, is a, so concluded that the decomposition of the problem is a lossless join decomposition********************a simple discriminant method for non-destructive decomposition (for the case of decomposition into 2 relationships) For Example:there is a relationship between R=abc, dependency {a-->b} then which of the following is lossless decomposition:A. {R1 (AB), R2 (AC)}b.{r1 (AB), R3 (BC)}First look at the option a,r1∩r2=a,r1-r2=b,r1u r2--> (R1-R2). So it's a lossless decomposition.option B, R1∩r2=b, R1-r2=a, R2-r1=c,so it's not a lossless decomposition .So here's a quick way to judge non-destructive decompositionThe ∩ of the set is first evaluated on two sets, and then the difference of the set is obtained (2 sets have two difference results)If the ∩--> set of the collection is the difference (whichever one gets the result), then it's a lossless decomposition .other questions please add my: x7993794, can join my soft test database group to discuss together(This article is from "Li Yu ping" blog)
Soft test database-lossless connection decomposition
