Normalization of databases

First, the basic concept

Entity: something that exists in the real world and can be distinguished. such as " one student "," one book "," one course " " and so on.

Attribute: The textbook is interpreted as:" an entity has a certain characteristic ", thus, the attribute is initially a logical concept, for example," gender " is a property of " man " . In a relational database, a property is a physical concept, and attributes can be thought of as " a column in a table ."

Tuples: A row in a table is a tuple.

Component: A property value of a tuple.

Code: The table can uniquely determine a tuple of a property (or attribute group), if such a code has more than one, then everyone is called candidate code, we choose a candidate code out to do the boss, it is called the main code.

Full code: If a code contains all the attributes, the code is full code.

Main attribute: An attribute is the primary attribute as long as it appears in any candidate code.

Non-primary attribute: In contrast to the above, there is no candidate code, this property is a non-primary attribute.

External code: A property (or attribute group), it is not a code, but its other table code, it is outside the code.

Second, function dependence

1. Function dependency

Set X, Y is a collection of two attributes of the relationship R, and when the X attribute values in any two tuples in R are the same, their Y attribute values are the same, then they are said to determine Y, or the Y function depends on X as x→y.

2. Trivial function dependence

When the attribute set Y in a relationship is a subset of the attribute set X, there is a function dependency x→y, a set of property functions that determine all of its subsets, a function dependency called trivial function dependency.

3. Non-trivial function dependence

When the attribute set Y in a relationship is not a subset of the attribute set X, there is a function dependency x→y, which is called a non-trivial function dependency.

4. Full function dependency

Set X, Y is a set of two attributes of the relationship R, and the X→Y is the true subset of X, but there is X '!→y for each x ', then the Y full function is dependent on X.

5. Partial function dependency

Set X, Y is a set of two attributes of the relationship R, there is x→y, if X ' is a true subset of x, there is X ' →y, then the Y function is called X.

6. Transfer function Dependency

X, Y, Z is a different set of attributes in the relationship R, there is x→y (Y!→x), y→z, then the function is said to be dependent on X.

Three,5 major paradigms and their characteristics

1NF: The atomic field is not re -divided, otherwise it is not a relational database (so it is not possible to create a table in a normal relational database that does not conform to 1NF);

2NF: uniqueness A table only shows one thing, 1NF elimination of the non-main property of the code part of the function depends on the 2NF;

3NF: Each column has a direct relationship with the primary key, and 2NF eliminates the transfer function of the non-principal- dependent code is 3NF;

BCNF:3NF the partial function dependency and transfer function dependency of the main attribute pair code is BCNF (in other words, the BCNF paradigm checks both the non-primary property and the main attribute, and 3NF checks only Non-primary attribute);

4NF: Non-trivial and non-function dependent multivalued dependencies are not allowed between the attributes of the restricted relational schema (only the function dependencies are considered, up to BCNF, if the multi-valued dependency is considered to be up to 4NF).

About 4NF is still not clear.

Normalization of databases