Tested by system architecture designers-decomposition of Relationship Models

http://wsjx.mnu.cn/learning/web_course/dataxitong/ 5.2.5 link mode decomposition: in the previous discussion, we all achieve this by breaking down the lower-level relational patterns into several higher-level relational patterns. Such a decomposition splits the relationship patterns to some extent, and allows a relational pattern to describe a kind of association between a type of entities or entities. however, how can we break down the link mode? There may be multiple decomposition schemes for the same link mode. For example, in link Mode S (SNO, classno, deptno), the student ID SnO, the class number classno, and the department number deptno. function dependency set f = {SnO → classno, classno → deptno} on the property set S 3nf is obvious. We have at least three decomposition schemes for Link Mode S. decomposition 1: S11 (SNO, deptno), S12 (classno, deptno) decomposition 2: S21 (SNO, classno), s22 (SNO, deptno) decomposition 3: s31 (SNO, classno), s32 (classno, deptno) the two relational modes obtained by each Decomposition Scheme belong to 3nf. In fact, both belong to the bcnf and 4nf paradigms. How can we compare the advantages and disadvantages of these three decomposition solutions? When dividing a relational model into multiple relational models, what else do you need to consider in addition to improving the degree of standardization? 1. Equivalent Standard of pattern decomposition 2. Several facts about pattern decomposition

	1. Equivalent Standard of pattern Decomposition In the normalization process, a relational mode is divided into several relational modes, and the resulting mode should be equivalent to the original mode. Common equivalence standards are required: ● Decomposition has lossless connectivity; ● Decomposition maintains function dependencies; ● Decomposition requires both lossless connections and functional dependencies. A relational model R (u, f) is divided into several Relational Models r1 (U1, F1), R2 (U2, F2 )... RN (UN, FN) (where u = U1 U2... UN, R1 is the projection of F on U1), which means that data stored in the r of a two-dimensional table is distributed to several two partition tables R1, R2 ,..., RN (R1 is the projection of R on the Property Group U1 ). We certainly hope that such decomposition will not lose information, that is to say, we hope that through... The natural Join Operation of Rn re-obtains all information in the relational R. In fact, the relationship R is projected as R1, R2 ,..., Rn does not lose information, the key is to R1, R2 ,..., Rn as a natural connection may generate some tuples that are not in the original R, so it is impossible to distinguish the tuples that are in the original R, that is, the data that should exist in the database, in this sense, information is lost. For example, set the relationship between S (SNO, classno, deptno) and R at a certain time point. Table 5-14 Table 5-14 SnO Classno Deptno S1 C1 D1 S2 C2 D2 S3 C2 D2 S4 C3 D1 If the Relationship Mode S is divided: S11 (SNO, deptno) and S12 (classno, deptno ), Then, Project R to the attributes of S11 and S12, and obtain the relationship R11, such as table 5-15, and R12, such as table 5-16. Table 5-15 SnO Deptno S1 D1 S2 D2 S3 D2 S4 D1 Table 5-16 Classno Deptno C1 D1 C2 D2 C3 D1 Perform a natural connection to the decomposed relationship R11 * R12, and obtain R', as shown in table 5-17: Table 5-17 SnO classno deptno S1 C1 d1 S1 C3 d1 S2 C2 D2 S3 C2 D2 S4 C1 d1 S4 C3 d1 R' neither s1c3d1 nor s4c1d1 is the tuples in the original R. That is to say, we do not want to know which tuples exist in the original R. Definition 1 : Set the relational mode R (u, F) to the relational mode r1 (U1, F1), R2 (U2, F2 ), ..., RN (UN, FN). For any possible r relationship, r = R1 * R2... * Rn, that is, R is on R1, R2 ,..., If the natural connection of the projection on RN is equal to R, the decomposition of the relational mode R is non-destructive. decomposition 1 does not have lossless connectivity, which is a bad decomposition solution. when a link mode is divided into three or more link modes, algorithms are complex to determine whether the decomposition has lossless connectivity. However, if a link mode is divided into two link modes, it is easy to determine whether the decomposition has lossless connectivity. the relational mode R (u, f) is divided into relational modes r1 (U1, F1), R2 (U2, F2) is a lossless connectivity decomposition of the necessary conditions is (U1 ∩ U2 → U1-U2) in F +, or (U2 ∩ U1 → U2-U1) in F +. let's look at the second Decomposition Scheme. The link Mode S is divided into: S21 (SNO, classno), s22 (SNO, deptno) because U1 ready U2 = SnO, U1-U2 = classno, apparently U1 U2 → U1-U2, so decomposition 2 has lossless connectivity. However, decomposition 2 is not a good decomposition solution. Projection the relational R in the previous example to the attributes of S21 and s22 to obtain the relational R21, such as tables 5-18 and R22, such as tables 5-19: Table 5-18 SnO Classno S1 C1 S2 C2 S3 C2 S4 C3 Table 5-19 SnO deptno S1 d1 S2 D2 S3 D2 S4 d1 In this decomposition, assume that the student S3 is transferred from Class C2 to Class C3, so we need to change the tuples s3c2 to s3c3 in R21, and change the tuples s3d2 to s3d1 in R22. If these two modifications are not completed at the same time, inconsistency will exist in the database. This is because the two relationship modes obtained by decomposition are not independent of each other. The function in F depends on classno → deptno, which is not projected into the relational model R22, but across two relational models. Function dependencies are integrity constraints in databases. In R, if the X values of the two tuples are equal, the y values must be equal. The X value and Y value in one of R are in two different relationships. To maintain Database Consistency, when changing the X value in a link, you need to modify the Y value in another link. This is of course troublesome and error-prone, therefore, we require pattern decomposition to maintain the equivalent standard of function dependency. Definition 2: Set the relational mode R (u, F) to the relational mode r1 (U1, F1), R2 (U2, F2 ),..., RN (UN, FN), if f = (f1f2... FN), that is, the functional dependencies in the f logic must also be defined by the functional dependencies in the corresponding relations obtained from the decomposition. This decomposition of the relational mode R maintains the function dependency. Solution 2 does not maintain the function dependency, because only functions depend on SnO → classno In the decomposed relational mode, and the function dependency classno → deptno is lost. Not a good decomposition. Solution 3 is to maintain the function dependency.

	2. several facts about pattern decomposition (1) decomposition with lossless connectivity and decomposition to maintain functional dependency are two independent standards. The decomposition with lossless connectivity does not necessarily maintain function dependencies, for example, solution 2. The decomposition with lossless connectivity is not necessarily. for example, there are student ID SnO, class number classno, course number courseno, and credit, which constitute a relational model: SC (SNO, classno, courseno, credit), the function dependency set on the property set is: F = {SnO → classno, courseno → credit }, there are two relationship modes: SC1 (SNO, classno) and SC2 (courseno, credit). This decomposition maintains function dependencies, but it does not have lossless connectivity. therefore, a break-down of the relational model may maintain the function dependency, which may be non-destructive or non-destructive. (2) if the decomposition is required to have lossless connectivity, the mode decomposition can reach 4nf. (3) if Division is required to maintain functional dependencies, the mode decomposition can reach 3nf, but not necessarily bcnf. (4) if the decomposition requires both lossless connectivity and functional dependency, the mode decomposition can reach 3nf, but not necessarily bcnf. for more information about the pattern decomposition algorithms, see references.