What does the normalization process primarily address in the logical structure of the database?

The normalization process is mainly to overcome the defects of insertion anomalies, deletion anomalies and high redundancy in the logical structure of the database. Database normalization allows database designers to better understand the current data structure within the organization, ultimately resulting in a series of data entities. Database standardization can effectively reduce database redundancy through the design of database tables.

Database normalization process

The standardization of relational databases is simply to of standardization.

Necessity of standardization:

According to the needs of the project, we will create corresponding database tables to complete the data in the project of storage. This has become a fixed process for doing projects, but when you actually start to deal with business needs, you will realize that your table settings are unreasonable, leading to repeated storage of data, insertion exceptions, deletion exceptions, update exceptions and other problems. At this time, it is necessary to re-plan the table, which is a waste of time, manpower and financial resources, and is very uneconomical. Therefore, standardization is very necessary, so today I will teach you how to standardize the table.

Before teaching the standardized database method, let me introduce the knowledge to you first:

Key knowledge points function dependency

##Definition of functional dependency:

Suppose R(U) is a subset of the attribute set, X and Y are subsets of U, If for any possible relation r on R(U), if there cannot be two tuples in r whose attribute values ​​on X are equal but their attribute values ​​on Y are not equal, then it is said that the X function determines If Y or Y is functionally dependent on X, it is denoted as X->Y.

The definition may be a little difficult to understand. Let me explain it briefly here: Functional dependency describes a mapping relationship between two collections. This mapping relationship is the same as a function. For example, y = x^2, here for x, one x corresponds to one y value, but there is no situation where one x corresponds to multiple y values, so it can be said that the y function depends on x. However, for For y, there is a situation where one y value corresponds to multiple x values, so x is not functionally dependent on y. This is functional dependency.

Next we introduce several special functional dependencies:

Complete functional dependency

Definition:

If X-> ;Y, and for any proper subset X' of X does not exist, X'->Y, then we say that the functional dependence of X->Y belongs to complete functional dependence.

A brief explanation: Function z = x y, for z: z function depends on x and y, but z does not depend solely on x or solely on y, which means that z function depends on This dependence between x and y is a complete functional dependence.

Partial functional dependency:

Definition:

If X->Y, but Y does not completely depend on X, then this dependence is called For partial complete dependence. That is to say: the function z = x 0y can be regarded as , that is to say, the z function depends on x and y, but z depends solely on x, then this is partial functional dependence.

Transitive functional dependency:

Definition:

If X->Y, Y -> Z, and not true, Y-> X is not true either. Then the Z transfer function is said to depend on X.

This is relatively simple. The function group z = x^2, x = 2y can be simplified to z = 4y ^2. It is easy to see: z is a function that depends on x, x depends on y, and z ->x does not hold, this is a transfer function dependency.

Key knowledge point two-----Key

Candidate key: an attribute (field) or An attribute group (multiple fields) can be completely determined by other attributes (fields) in the relational schema (table). That is to say, other attributes (fields) are completely dependent on this attribute (field) or attribute group (multiple fields).

Primary key: If there is more than one candidate key, select one of them as the primary key. The value of the attribute or attribute group selected as the primary key in each tuple (row) in the relationship schema (table) is not allowed to be repeated and the value is null.

Main attributes: The attributes in any candidate key are called primary attributes. If the candidate key is composed of multiple attributes, then each attribute in these attribute groups is a primary attribute.

Non-primary attributes: Attributes that are not included in any key are called non-primary attributes.

Foreign key: An attribute or attribute group is not a primary key in the current relational schema (table), but serves as the primary key in another relational schema (table). This attribute or attribute group is called Property groups are foreign keys.

After introducing the above basic knowledge points, let's start learning the standardization process of database tables:

If you want to standardize a table, you first need a standard to measure whether the table has been standardized. This standard is----

paradigm.

There are six paradigms: first normal form (1NF), second normal form (2NF), third normal form (3NF), BC normal form (BCNF), fourth normal form (4NF), and fifth normal form (5NF).

In the above six paradigms, under normal circumstances we need to standardize the table to BCNF, which is perfect. In real projects, it only needs to reach 3NF.

Next, we will focus on the first four paradigms:

First paradigm: All attributes in the relational model R are inseparable data items.

To put it simply, as long as you can create a table, the table already satisfies the first normal form. For example, the student table (student_id, course_id, student_name, age, sex, grade, sdept, sdept_director). In this table, it is obvious that the grade item is jointly determined by student_id, course_id, so these two items should be combined as Primary key.

Second normal form: On the basis of satisfying the first normal form, satisfying non-primary attributes completely depends on the primary key of R.

This requires the use of the previous content to determine whether the non-primary attributes are completely dependent on the primary key. If you are not satisfied, it will be heavy Change the structure of the table. For example, the student table (student_id, course_id, student_name, age, sex, grade, sdept_id, sdept_director) uses the combination of student_id and course_id as the primary key, but for other attributes such as name, age, and sex, they are completely dependent. Rely on the attribute student_id, so they are partially dependent on student_id and course_id as the primary key. This does not meet the definition of the second normal form, so we should take out the grade and split this large table into two small tables: student(student_id, name, age, sex, sdept_id, sdept_director), student_score(student_id, course_id , grade);

Third normal form: Remove transitive dependencies when the second normal form is satisfied.

For example: student table (student_id, student_name, age, sex, sdept, sdept_director), obviously each major determines a professional director, so the sdept_director transfer depends on the student_id, so it should be split into one The tables student (student_id, student_name, age, sex) and sdept (sdept_id, sdept_name, sdept_director) satisfy the third normal form.

BC paradigm: When satisfying the third paradigm, three more points must be met:

1. All main attributes are completely dependent on other Contains its own candidate key;
2. All non-primary attributes are completely dependent on each candidate key;
3. No attribute is completely functionally dependent on any one Group non-primary attribute.

The previous three paradigms all impose various constraints on non-primary attributes. The BC paradigm is based on them and then constrains the primary attributes to solve the partial dependence between the primary attributes. problem, and there is no problem that the main attribute completely depends on the non-primary attribute. Our student table student (student_id, student_name, age, sex), the primary key is student_id, so the main attribute is student_id. Obviously the first two are satisfied, because the student's name may be repeated, so there is no functional dependency between student_id and student_name relationship, so the student table satisfies BC normal form.

The above is the normalization process of the database

Related recommendations: "mysql tutorial"

The above is the detailed content of What does the normalization process primarily address in the logical structure of the database?. For more information, please follow other related articles on the PHP Chinese website!