Normalization Theory of database review

Disclaimer: This article is for the author to review the database course with a simple record of notes, if there are errors, please point out, thank you.

6.1 The question is raised

1. A relational pattern is a five-tuple, shaped like R (u,d,dom,f). D, Dom and pattern design is not very related, can be regarded as ternary group r<u,f>.

• The relationship name R is a symbolic tuple definition;
• U is a set of attributes;
• D is the domain from which the attributes in the attribute group u are derived;
• Dom is a property-to-domain mapping;
• F is a set of data dependencies on the attribute group U.

2. Data dependency: A constraint relationship between attributes and attributes within a relationship. The most important are function-dependent (FD) and multivalued-dependent (MVD), and one called connection dependency .

3. Analyze the Relationship Pattern FAQ:

• Data redundancy: Repeated occurrences, wasted space. (as few as possible)
• Update exception: Update cost (preferably not)
• Insert exception: Unable to insert part of information (preferably not)
• Delete exception: May delete other desired data (preferably not)

6.2 Normalization

1. Function dependency: (concept omitted, x, Y is a subset of the attribute group U) The X function determines that Y or Y function depends on X, which is recorded as X→y. For example: Department number → Department name, study number → name.

(1) The function dependence does not refer to the constraint conditions that some relationships in the relationship pattern R satisfy, but the constraints that all relations on R must satisfy . The existence of a function dependency is irrelevant to time, but only to the semantic provisions between data. The existence of a function dependency is independent of time and is only related to the semantic definition between data.

(2) Basic properties of function dependence: extensibility, projection, merging, decomposition,

2. The non-trivial function relies on x→y:x→y, but Y is not included in X. By default, I'm talking about non-trivial function dependencies.

3. Trivial functions rely on x→y:x→y, but Y is included in X. Must be set up (as if it were nonsense).

4. If x→y, it is said that X is the determinant attribute group for this function dependency, also called the determinant , and Y is the dependent factor .

5. Full function Dependency : in R (U), if x→y, and for any true subset of X ' X ', there is X '/→y, which is called Y to the X full function dependency. Recorded as X f→y.

Inference: A single determinant must be a complete function dependency.

Example: (School number, course number) → Results

6. Partial function Dependency : in R (U), if x→y, and Y incomplete function depends on X, it is called Y to the X part function depends. Recorded as X p→y.

Example: (School number, course number) → Course name (because the course number → course name, and the course number is a true subset of (school number, course number)

7. Transfer function Dependency : in R (U), if X→y (Y is not included in X), y/→x,y→z (Z is not included in Y), then z is called to be dependent on the X pass function. The X Pass (t) →z is recorded.

Note: The condition must have y/→x, because if y→x, then y←→x, then X direct →z, belong to direct function dependence, not indirect.

Example: Department number → Department name, department name → Department director name.

8. Candidate Code : set K as the attribute or attribute combination in r<u,f>, if K f→u, then K is the candidate Code of R (candidate key). (That is, U is completely dependent on K).

9. Super Code : If U part depends on K, that is K p→u, then the K is super code (super Key). Candidate code is the smallest super code

10. Candidate code may be more than one, optionally one as the main code . The attribute contained in any candidate code is called the primary attribute ; A property that is not contained in any candidate code is called a non-primary attribute (non-code attribute). In the simplest case, a single attribute is a code (master code or candidate code); The most extreme case, the entire attribute group U is a code, called a full code . (main code and candidate Code are abbreviated code)

11. The attribute or attribute group x in the relationship mode R is not the code of r, but X is the code of another relational pattern, which is called an external code (outside code)of R.

The first paradigm (1NF): Each component must be an irreducible data item ( each attribute in a relationship is a simple item that cannot be re-divided).

second Normal form (2NF): If R satisfies the first normal form, and each non-principal attribute full function relies on any one candidate code.

Inference: The candidate code is a single attribute or full code, then belongs to 2NF.

Features: There is no partial function dependency of the non-primary attribute on the candidate code .

1NF→2NF: Eliminate the partial function dependency of the non-main attribute on the candidate code, and cast the partial function dependency into the table separately. (a list of things)

The third paradigm (3NF): If R satisfies the second paradigm, and each of its non-primary properties is not passed , it relies on any candidate code.

Definition: The relationship pattern r<u,f> belongs to the first paradigm, if there is no such code X in R, the attribute group Y and the non-primary attribute Z (y not included in Z) Yes X→Y,Y→Z is established, Y/→x, is called R belongs to 3NF.

Definition understanding: The definition of 3NF is pushed over by 1NF, not very good understanding, the decision words with the line 2NF deduction can be, this definition also can prove that if R belongs to 3NF, then R will belong to 2NF.

Features: each non-primary attribute has no partial function dependency on the candidate code, and no transitive dependency.

Disadvantage: 3NF restricts the dependency of the non-primary property on the key, without restricting the primary property's dependency on the key.

2NF→3NF: Eliminate the dependency of the non-primary attribute transfer, and cast the transitive dependency into a separate table. (a list of things)

BCNF: In relational mode r<u,f>, each determinant contains a code for R (candidate key), R belongs to BCNF.

Definition: The relationship pattern r<u,f> belongs to the first paradigm, if X→y (Y is not included in X) is x must contain code, then r belongs to BCNF.

Features: Excludes any attributes from passing dependencies and partial dependencies on the candidate code. Achieve a thorough separation within the functional dependency category, eliminating inserts and delete exceptions.

Inference: If R belongs to Bcnf, then

• All non-primary attributes in R are fully functional dependent on each code;
• All the main attributes in R are fully functional dependent on each code that does not contain it;
• There is no property in R that the full function relies on any set of properties that are not code.

Theorem: If R belongs to Bcnf, then R belongs to 3NF must be established.　　The reverse is not necessarily true, because 3NF is not exhaustive (there may be partial dependency and transitive dependency of the main attribute on the code). Example: (S,j) →t, (s,t) →j,t→j. The candidate code is (S,J), (s,t), T is the determinant, but T does not contain the code. 16. Fourth Paradigm 4NF: Non-trivial and non-function dependent multivalued dependencies are not allowed between attributes that restrict relational schemas. (not required) 17. A low-level paradigm relationship model can be Pattern DecompositionA set of relational patterns that are converted to several high-level paradigms, a process called Normalization。 Summary: In the practical application, the most valuable is 3NF and bcnf, in the design of the relationship model, usually decomposed to 3NF can be.

Alvinzh

Source: http://www.cnblogs.com/AlvinZH/

I Github:https://github.com/pacsiy/jobdu

The copyright belongs to the author Alvinzh and blog Park all, welcome reprint and Commercial, but without the consent of the author must retain this paragraph statement, and in the article page obvious location to the original link, otherwise reserves the right to pursue legal responsibility.

Normalization Theory of database review