Database and database Learning

Database and database Learning
Data Dependency System

Logic Implication definition 6.11 For the relational mode R <U, F> that satisfies a set of function dependencies F, any of its relational r is true if function dependencies X → Y, (that is, any two-element group t, s in r. If tX] = sX], tY] = sY]), the F logic contains X → Y.

Armstrong justice system

The relational model R <U, F> has the following inference rules: A1. self-inverse Law (Reflexivity): If y x u, X → Y is contained in F. A2. augmented Law (Augmentation): If X → Y is contained in F and z u, XZ → YZ is contained in F. A3. Transfer Law: If X → Y and Y → Z are contained in F, X → Z is contained in F.

(L) Self-inverse law: if y x u, then X → Y is the evidence of F: Set y x u to R <U, f> Any two tuples t, s: If t [X] = s [X], due to y x, t [y] = s [y], therefore, X → Y was founded, and self-inverse law was used to prove

(2) augmented law: If X → Y is contained in F and z u, XZ → YZ is contained in F. Certificate: set X → Y to F, and z u. Set any two tuples t, s: If t [XZ] = s [XZ] in any relation R <U, F>. t [X] = s [X] and t [Z] = s [Z]; from X → Y, then t [Y] = s [Y], so t [YZ] = s [YZ], so XZ → YZ is contained in F, and the augmented law is proved.

(3) Transfer Law: If X → Y and Y → Z are contained in F, X → Z is contained in F. Certificate: set X → Y and Y → Z to F. For any two tuples t, s: If t [X] = s [X] in any relation R <U, F>, due to X → Y, there are t [Y] = s [Y]; then from Y → Z, there are t [Z] = s [Z], so X → Z is contained in F, pass the law.

1. Based on the three inference rules A1, A2, and A3, we can obtain the following three inference rules: merging rules: from X → Y, X → Z, with X → YZ. (A2, A3) pseudo transfer rules: from X → Y, WY → Z, with XW → Z. (A2, A3) decomposition rules: X → Y and ZY, with X → Z. (A1, A3)

2. Based on the merging rules and decomposition rules, you can obtain the 6.1 theorem 6.l X → A1 A2... The necessary conditions for Ak establishment are X → Ai establishment (I = l, 2 ,..., K)

The Armstrong justice system is effective and complete.
Validity: The dependencies of each function derived from F according to the Armstrong principle must be in F +;
Completeness: Every function dependency in F + must be derived from F according to the Armstrong principle.

Function dependency Closure

Definition 6. l2 In the relational mode R <U, F> is the closure of F, which is called F +. Define 6.13 to set F as A set of function dependencies on the property set U. x u, XF + = {A | X → A can be exported by F according to the Armstrong principle }, XF + is called the closure of attribute set X on function dependency set F.

6.2 Let F be a set of function dependencies on the property set U, X, y u, the sufficient and necessary condition that X → Y can be exported by F according to the Armstrong principle is that y xf + will determine whether X → Y can be exported by F according to the Armstrong principle, convert to the problem of finding XF + and determining whether Y is a subset of XF +

Algorithm 6.1 evaluate attribute set X (x u) closure XF + input: X, F output: XF + step: (1) X (0) = X, I = 0 (2) Evaluate B, here B = {A | (V) (W) (V → WF 1_v X (I) 1_a W )}; (3) X (I + 1) = B then X (I) (4) Judge X (I + 1) = X (I? (5) If it is equal or X (I) = U, X (I) is XF +, and the algorithm is terminated. (6) If no, I = I + l and return step (2. For algorithm 6.1, make ai = | X (I) |, {ai} form a strictly incrementing sequence with step size greater than 1. The upper bound of the sequence is | U |, therefore, this algorithm can terminate a maximum of | U |-| X | times in a loop.

[Example 1] known relational mode R <U, F>, where U = {A, B, C, D, E}; F = {AB → C, B → D, C → E, EC → B, AC → B }. Evaluate (AB) F +. Returns X (0) = AB; (1) X (1) = AB ∪ CD = ABCD. (2) X (0) =x (1) X (2) = X (1) must BE = ABCDE. (3) X (2) = U, algorithm termination (AB) F + = ABCDE.

Effectiveness and completeness of Armstrong Systems

Theorem 6.2 The Armstrong justice system is an effective and complete proof: 1. validity can be obtained from theorem 6.1. 2. completeness only needs to prove the inverse negative proposition: If the function depends on X → Y, it cannot be derived from the Armstrong principle by F, then it must not be included in F.

Function dependency set equivalence

Define 6.14 if G + = F +, it means that the function dependency set F overwrites G (F is the coverage of G, or G is the coverage of F), or F is equivalent to G. The sufficient and necessary condition for the theorem 6.3 F + = G + is f g +, and g f +: The necessity is obvious, but only the adequacy is proved. (1) If FG +, XF + XG ++. (2) If any X → YF + is used, y xf + XG ++ is used. Therefore, X → Y (G +) + = G +. That is, F + G +. (3) Similarly, it can prove that G + F +, so F + = G +.

Minimum dependency set

Definition 6.15 if function dependency set F meets the following conditions, it is called F as a very small function dependency set. It is also known as the minimum dependency set or the minimum coverage.
(1) F contains only one attribute on the right of any function dependency.
(2) F does not depend on X → A, which makes F equivalent to F-{X →.
(3) F does not depend on X → A. X has A real subset Z, which makes F-{X → A} else {Z → A} equivalent to F.

[Example 2] relational mode S <U, F>, where: U = {Sno, Sdept, Mname, Cno, Grade}, F = {Sno → Sdept, Sdept → Mname, (Sno, Cno) → Grade} set F' = {Sno → Sdept, Sno → Mname, Sdept → Mname, (Sno, Cno) → Grade, (Sno, Sdept) → Sdept} F is the minimum overwrite, whereas f' is not. Because: f'-{Sno → Mname} and F' are equivalent to F'-{(Sno, Sdept) → Sdept} and F'

Theorem 6.3 Each function dependency set F is equivalent to a very small function dependency
Set Fm. This Fm is called the minimum dependency set of F.
Proof: constructive Proof: Find a minimum dependency set of F.

(1) Check the dependency of each function in F on FDi: X → Y one by one. If Y = A1A2... Ak, k> 2, {X → Aj | j = 1, 2 ,..., K} to replace X → Y. (2) Check the dependency of each function in F on FDi: X → A, so that G = F-{X → A}. If AXG + is used, remove this function dependency from F. (3) retrieve the dependency of each function in F on FDi: X → A, and set X = B1B2... Bm, examine Bi one by one (I = l, 2 ,..., M). If A (X-Bi) F +, X-Bi replaces X. [Example 3] F = {A → B, B → A, B → C, A → C, C → A} Fm1 and Fm2 are the minimum dependency sets of F: fm1 = {A → B, B → C, C → A} Fm2 = {A → B, B → A, A → C, C → A} F's minimal dependent set Fm is not unique minimization process (proof of Theorem 6.3) is also an algorithm used to check whether F is an extremely small dependent set

Mode Decomposition

It is not the only method to break down a lower-level relational model into several higher-level relational models.
The decomposition method is meaningful only when the decomposed link mode is equivalent to the original link mode.

Three modes are equivalent to decomposition:

Crash decomposition has lossless connectivity. Crash decomposition must maintain functional dependency. Crash decomposition must maintain both functional dependency and lossless connectivity.

Define a decomposition of the 6.16 relational mode R <U, F>: P = {R1 <U1, F1>, R2 <U2, F2> ,..., Rn <Un, Fn >}u = Ui, and Ui Uj does not exist, fi is the projection definition of F on the Ui. A coverage Fi of the 6.17 function dependency set {X → Y | X → y f + ∧ XY Ui} is called the projection of F on the property Ui.

Example: S-L (Sno, Sdept, Sloc) F = {Sno → Sdept, Sdept → Sloc, Sno → Sloc} S-L in 2NF decomposition methods can have a variety of: 1. the S-L is divided into three relational modes: SN (Sno) SD (Sdept) SO (Sloc) 2. SL is divided into the following two relational modes: NL (Sno, Sloc) DL (Sdept, Sloc) 3. the SL is divided into the following two relational modes: ND (Sno, Sdept) NL (Sno, Sloc)

Relational mode R

Decomposition Algorithm

Algorithm 6.2 identifies the lossless connectivity of A Decomposition
The decomposition of the persistence function dependency of algorithm 6.3 (synthesis) converted to 3NF.
Algorithm 6.4 converts to 3NF, which has both lossless connectivity and functional dependency decomposition.
Lossless link decomposition for converting algorithm 6.5 (decomposition method) to BCNF
Lossless connectivity decomposition for algorithm 6.6 reaching 4 NF
If the decomposition is required to have lossless connectivity, the pattern decomposition must be 4 NF
If decomposition is required to maintain functional dependency, the mode decomposition must reach 3NF, but not necessarily BCNF.
If the decomposition requires both lossless connectivity and functional dependency, the mode decomposition must be 3NF, but not BCNF.

The Standardization Theory provides a theoretical guide and tool for database design.
It's just a guide and Tool

The higher the degree of standardization, the better the mode.
The database mode must be properly selected based on the application environment and the actual world.