Non-destructive connection determination of decomposition of relational normalization

Definition: Lossless join decomposition is the decomposition of a relational pattern into a number of relational patterns, through the natural joins and projections and other operations can still be restored to the original relational mode, it is said that the decomposition of non-destructive join decomposition.

A judgement algorithm for nondestructive decomposition

Input: A relational mode R (A1,a2,a3,...,an), a function on R is dependent on set F and a decomposition P{{r1,f1},{r2,f2},..., {RK,FK]}

Output: Determine if P is a connection without losing true decomposition

Method:

(1) Establish a KXN table for verifying the distortion of the connection

	A1	A2	...	Aj
R1
R2
...
Rk

Line I corresponds to RI, column J corresponds to attribute AJ

(2) Fill in the form: If AJ is an RI, fill in the section J of row I as AJ, otherwise fill in the bij.

(3) Modification of the table: the function-dependent x->y,x in F may contain one or more properties, and if these properties correspond to the same values for the columns in the table, the values for the Y property of the row corresponding to the equal value are also equal. (for example, X->y,x is the property of A1A2, and Y is the property A4.) From the first step, if the values of the first row and the second row in the table are equal, the values in the table A4 corresponding to the same line and the second row are also modified to be the same. If there is AJ, change the bij to AJ, and if you do not have AJ, change them to bij, in general I is the smallest line number in the row with the same value.

(4) Repeated (refers to on the basis of the previous modification, repeated, until the data in the table is no longer changed) (3), if a row is found to become a1,a2,..., AJ, you can conclude that the decomposition P{{r1,f1},{r2,f2},..., {RK,FK]} has a connection fidelity.

Example://This example is from the "Li Yu ping" blog http://fsjoy.blog.51cto.com/318484/137130:
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,   R4, R5} is not a lossless join decomposition. Solution: Such a problem to be solved by drawing a table, first, the original table:

	A	B	C	D	E
AD	A1	b all	b all	A4	b
BC	b all	A2	A3	B (	b)
be	B to	A2	b all	b a5
CDE	b all	b all	A3	A4	A5
AE	A1	b all	b all	b a5	the

Table 1 (a B C D E is the property of the relationship R,  ad, BC, be, CDE, ae  is the set of attributes corresponding to each relationship after decomposition)   fill in the process of filling in: When the intersection is the same, if there is a single attribute of the corresponding column in the decomposition relationship (such as the first column The first line of ad with a cell intersection, AD contains a, just fill in A1), then fill in a subscript  ,  subscript is the column number corresponding to the cell. Otherwise fill in the B subscript,  subscript is the row number corresponding to the cell. The initial table after completion is shown in table 1.2. Modify the original table based on dependencies: 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 superscript above)  

	A	B	C	D	E
AD	A1	b 12	B13	A4	b 15
Bc	b 21	A2	A3	B 24	B 25
Be	B 31	A2	B 33	B 34	A5
Cde	B 41	B 42	A3	A4	A5
Ae	A1	B 52	B53àb13	B 54	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	b 12	b 13	A4	b 15
Bc	b 21	A2	A3	B 24	B 25
Be	B 31	A2	B33àa 3	B 34	A5
Cde	B 41	B 42	A3	A4	A5
Ae	A1	B 52	b 13	B 54	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	b 12	b 13	A4	b 15
Bc	b 21	A2	A3	B24àa 4	B 25
Be	B 31	A2	A3	B34àa 4	A5
Cde	B 41	B 42	A3	A4	A5
Ae	A1	B 52	b 13	B54àa 4	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 after this function dependency, is