MySQL Learning: Three Paradigms for Beginners

1. Paradigm foundation

1.1 The concept of paradigm

There are some specifications that need to be followed when designing a database. Currently, there are six paradigms for relational databases: first paradigm (1NF), second paradigm (2NF), third paradigm (3NF), Buss-Codd Form (BCNF), fourth paradigm (4NF) and fifth paradigm (5NF, also known as perfect paradigm).

Of course, under normal circumstances, we can design a more standardized database if we meet the first three paradigms.

To follow the latter paradigm, you must first follow the requirements of the previous paradigm. For example, the second paradigm must first follow the first paradigm, the third paradigm must first follow the second paradigm, and so on.

2. Three major paradigms

2.1 Three major paradigm concepts

First Normal Form (1NF): Each column cannot be split any further.
Second Normal Form (2NF): Based on the first normal form, non-primary key columns are completely dependent on the primary key, and cannot be part of the primary key.
Third Normal Form (3NF): Based on the second normal form, non-primary key columns only depend on the primary key and do not depend on other non-primary keys.

2.2 Examples of the three paradigms

For example, we have a table. In the following examples, we will transform this table into three paradigms and then turn it into a standard table:

1. Transform the first paradigm

First Normal Form (1NF): Each column cannot be split any more

We can see that there is a column in the table that can be divided, that is, the series, so the transformation of it into the first normal form becomes:

2. Transformation of the Second Paradigm

Second Normal Form (2NF): Based on the first normal form, non-primary key columns are completely dependent on the primary key, and cannot be part of the primary key.

This second paradigm is not easy to understand, so let's first understand a few concepts:

1. Function dependency: If the value of a unique B attribute can be determined from an A attribute (or attribute group), then B depends on A. For example, the name in the picture above is completely dependent on the student number.
2. Complete functional dependency: If A is an attribute group, the determination of the attribute value of B needs to depend on all the attribute values ​​in the attribute group of A. An attribute group refers to multiple fields. For example, if we want to know a score, we must rely on the student number and course name attributes to determine a score. Other attributes cannot determine a score.
3. Some functional dependencies: If A is an attribute group, then the determination of attribute value of B needs to rely on some fields of attribute group A. For example, if student ID and course name are an attribute group, then the student name can be determined only by student ID.
4. Transfer function dependency: If attribute A (attribute group) can determine the unique value of attribute B, then the value of attribute C can be uniquely determined by the value of attribute B. For example, a student ID determines a department name, and a department name corresponds to a department head.
5. Primary Key: In a table, if an attribute or attribute group is completely dependent on all other attributes, then this attribute is called the table of the code, such as the attribute group consisting of student number and course name in the figure above.

In fact, the second paradigm can also be understood as

Eliminate partial dependence of non-primary keys on primary keys based on the first paradigm

The primary key in the above figure is the attribute group consisting of the student number and the course name. For the above figure, we can see that except for the score, all other attributes are partially dependent on the primary key. Then we can correct it as shown below:

After the second normal form transformation, a table is divided into two tables. We find that the second normal form actually eliminates a lot of redundant parts for us. For example, before the transformation, Zhang Wuji's name, department name, and department dean appeared three times in the table, but after the transformation, they only appeared once in the two tables.

3. Transformation to the Third Paradigm

Third Normal Form (3NF): Based on the second normal form, non-primary key columns only depend on the primary key and do not depend on other non-primary keys.

According to the concept we mentioned in the second point, it becomes:

The above is the detailed content of the three major paradigms of MySQL learning for beginners. For more information about the three major paradigms of MySQL, please pay attention to other related articles on 123WORDPRESS.COM!