The database is to find the closure packet, to find the minimum function dependency set, to seek candidate code, to determine whether the mode decomposition is a lossless connection, 3NF,BCNF

1. White: A closure is a collection of all the attributes deduced directly or indirectly by a property.

For example,:f={a->b,b->c,a->d,e->f}; can be directly obtained from a B and D, indirectly get C, then A's closure is {a,b,c,d}

2.

Solution theory and algorithm of candidate code

For a given relationship R (A1,A2, ... An) and a function dependency set F, you can divide its properties into 4 categories:

The L class appears only on the left-hand property of the function.

The R class appears only on the right-hand property of the function.

The N class does not appear on the left or right side of the function dependency property.

The LR class has properties that appear on both sides of the function dependency.

Theorem: For a given relationship pattern R and its function dependency set F, if X (X∈r) is an L-class attribute, then x must be a member of either candidate of R.

Inference: For a given relationship pattern R and its function dependency set F, if X (X∈r) is an L-class attribute, and X+ contains all the properties of R, then x must be the unique candidate for R.

Example (2): With the relationship mode R (a,b,c,d), its function depends on set f={d→b,b→d,ad→b,ac→d}, all candidate codes for R are obtained.

Solution: Study F found that the A,c two attribute is an L-class attribute, so AC must be a candidate member of R, and because (AC) +=ABCD, AC is the only candidate for R.

Theorem: For a given relationship pattern R and its function dependency set F, if X (X∈r) is an R class attribute, X is not in any candidate code.

Theorem: For a given relationship pattern R and its function dependency set F, if X (X∈r) is an n class attribute, x must be included in any candidate code of R.

Inference: For a given relationship pattern R and its function dependency set F, if X (X∈r) is a set of properties consisting of L and N classes, and x+ contains all the properties of R, then X is the unique candidate for R.

3. The minimum function dependency set is divided into three steps:

1. To the right of all dependencies in F into a single element

This problem fd={abd->e,ab->g,b->f,c->j,cj->i,g->h}; has been satisfied

2. Remove all dependencies in F from the left redundant attribute.

The practice is to remove one of the attributes and see if it is still possible to deduce

This topic: abd->e, remove A, then (BD) + does not contain E, it can not be removed, the same b,d are not redundant properties

Ab->g, there's no

Cj->i, because c+={c,j,i} contains I so j is redundant. Cj->i will become c->i

f={abd->e,ab->g,b->f,c->j,c->i,g->h};

3. Remove all redundant dependencies in F.

The procedure is to remove a relationship from F, such as removing (x->y), then x+ in F, and if Y is in x+, the x-> is superfluous. Need to be removed.

This problem if f minus abd->e,f will be equal to {ab->g,b->f,c->j,c->i,g->h}, and (ABD) +={a,d,b,f,g,h}, which does not contain E. All is not superfluous.

similarly (AB) +={a,b,f} does not contain G, so it is not redundant.

B+={B} not redundant, c+={c,i} not redundant

C->i,g->h can not be removed.

So the minimum function dependency set is f={abd->e,ab->g,b->f,c->j,c->i,g->h};

4. Determine if the mode decomposition is a lossless connection

Method One: Non-destructive connection theorem

A decomposition of the relational pattern R (u,f), Ρ={r1<u1,f1>,r2<u2,f2>} The sufficient and necessary conditions for a lossless connection are:

u1∩u2→u1-u2€f+ or u1∩u2→u2-u1€f+

Method Two: algorithm

Ρ={r1<u1,f1>,r2<u2,f2>,..., Rk<uk,fk>} is a decomposition of the relational pattern r<u,f>, u={a1,a2,..., an},f={fd1,fd2,..., FDp}, and set F is a minimum dependency set, note FDI as XI→ALJ, the steps are as follows:

① creates a table of N-column k rows, each of which corresponds to an attribute, and each row corresponds to a relational pattern in the decomposition. If the attribute AJ Ui, then the J column I line is really on AJ, otherwise filled with bij;

② for each FDI do the following: Find those rows that have the same symbol in the corresponding column of XI. Examine the elements of the Li column in these rows, and if there is AJ, change all to AJ, otherwise all change to Bmli,m is the lowest value of the row number for these rows.

if, after a change, a row becomes: A1,a2,..., An, the algorithm terminates. And decomposition ρ has a non-destructive connectivity , otherwise does not have a non-destructive connectivity.

A one-time process such as P-FD in F is called a scan of F.

③ comparison scan before and after the table has no change, if there is a change, then return to step ②, otherwise the algorithm terminates. If a loop occurs, the previous scan should at least reduce the table by one symbol, and the table has a limited number of symbols, so the loop must terminate.

Example 1: Known r<u,f>,u={a,b,c},f={a→b}, such as the following two decomposition:

①Ρ1={AB,BC}

②Ρ2={AB,AC}

Determine whether the two decomposition has a non-destructive connection.

① because Ab∩bc=b,ab-bc=a,bc-ab=c

So b→a￠f+,b→c￠f+

Therefore ρ1 is a lossy connection.

② because Ab∩ac=a,ab-ac=b,ac-ab=c

So a→b€f+,a→c￠f+

Therefore ρ2 is a lossless connection.

Example 2: One of the known r<u,f>,u={a,b,c,d,e},f={a→c,b→c,c→d,de→c,ce→a},r is decomposed into R1 (AD), R2 (AB), R3 (BE), R4 (CDE), R5 (AE), Determine if the decomposition has a non-destructive connectivity.

① constructs an initial two-dimensional table, if "attribute" belongs to the "mode" attribute, then fill AJ, otherwise fill bij

② according to A→c, the above table is processed, because the property column A on the 1th, 2, 5 row is the same A1, so the attribute column C B13, B23, b53 to the same symbol B13 (take the minimum value of the row number).

③ according to B→c, the above table processing, because the property column B on the 2nd, 3 rows are the same a2, so the attribute column C on the B13, b33 to the same symbol B13 (take the minimum value of the row number).

④ according to C→d, the above table is processed, because the property column C on the 1th, 2, 3, 5 lines are the same as B13, so the value on attribute column D is changed to the same symbol A4.

⑤ according to De→c, the above table is processed, because the property column de on the 3rd, 4, 5 rows are the same a4a5, so the value on the property column C is changed to the same symbol A3.

⑥ according to Ce→a, the above table is processed, because the property column CE on the 3rd, 4, 5 lines are the same as A3A5, so the value on attribute column A is changed to the same symbol A1.

⑦ by the above modification, the third line becomes A1A2A3A4A5, then the algorithm terminates. and decomposition has non-destructive connectivity.

5.3NF

The database is to find the closure packet, to find the minimum function dependency set, to seek candidate code, to determine whether the mode decomposition is a lossless connection, 3NF,BCNF