Detailed explanation of the use of MySQL paradigm

1. Paradigm

The English name of the paradigm is Normal Form, which was summarized by the British EF Codd (the ancestor of relational database) after he proposed the relational database model in the 1970s. Paradigm is the foundation of relational database theory, and it is also the rules and guiding methods we must follow in the process of designing database structure. There are currently 8 paradigms that can be found, namely: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, DKNF, and 6NF. Usually only the first three paradigms are used, namely: the first paradigm (1NF), the second paradigm (2NF), and the third paradigm (3NF).

First Normal Form (1NF)

The first normal form is actually the foundation of relational databases, that is, any relational database conforms to the first normal form. Simply put, the first normal form means that the data in each row are inseparable, there cannot be multiple values ​​in the same column, and if there are repeated attributes, a new entity needs to be defined.
The following database does not conform to the first normal form:

+------------+-------------------+
| workername | company |
+------------+-------------------+
| John | ByteDance,Tencent |
| Mike | Tencent |
+------------+-------------------+

What the data described above means is that Mike works at Tencent, and John works at both ByteDance and Tencent (assuming this is possible). However, this expression does not conform to the first normal form, that is, the data in the column must be inseparable. To meet the first normal form, it must be in the following form:

+------------+-----------+
| workername | company |
+------------+-----------+
| Mike | Tencent |
| John | ByteDance |
| John | Tencent |
+------------+-----------+

Second Normal Form (2NF)

First of all, a database must satisfy the first normal form before it can satisfy the second normal form.
Let's look at a table first:

+----------+-------------+-------+
| employee | department | head |
+----------+-------------+-------+
| Jones | Accountint | Jones |
| Smith | Engineering | Smith |
| Brown | Accounting | Jones |
| Green | Engineering | Smith |
+----------+-------------+-------+

This table describes the relationship between employees, work departments and leaders. The relationship represented by this table is entirely possible in real life. Now let's consider a question: if Brown takes over as the leader of the Accounting department, how do we need to modify the table? This problem will become very troublesome, because we will find that the data is coupled together, and it is difficult to find a good judgment condition that can uniquely determine each row to execute your UPDATE statement. We make the data that can uniquely represent a row of a table in the database the primary key of this table. Therefore, a table without a primary key does not conform to the second paradigm, which means that a table that conforms to the second paradigm needs to specify a primary key.

Therefore, in order to make the above table conform to the second normal form, we need to split it into two tables:

+----------+-------------+
| employee | department |
+----------+-------------+
| Brown | Accounting |
| Green | Engineering |
| Jones | Accounting |
| Smith | Engineering |
+----------+-------------+

+-------------+-------+
| department | head |
+-------------+-------+
| Accounting | Jones |
| Engineering | Smith |
+-------------+-------+

In these two tables, the primary key of the first table is employee and the primary key of the second table is department. In this case, it is very simple to complete the above problem.

Third Normal Form (3NF)

A relational database must satisfy the second normal form before it can satisfy the third normal form.
Before the third normal form, we also look at two tables:

+-----------+-------------+---------+-------+
| studentid | studentname | subject | score |
+-----------+-------------+---------+-------+
| 1 | Mike | Math | 96 |
| 2 | John | Chinese | 85 |
| 3 | Kate | History | 100 |
+-----------+-------------+---------+-------+

+-----------+-----------+-------+
| subjectid | studentid | score |
+-----------+-----------+-------+
| 101 | 1 | 96 |
| 111 | 3 | 100 |
| 201 | 2 | 85 |
+-----------+-----------+-------+

The primary keys of the two tables above are studentid and subjectid respectively. Obviously, both tables conform to the second normal form.

However, we will find that these two tables have duplicate and redundant data scores. Therefore, the third normal form is to eliminate redundant data. Specifically in the above situation, only one of the two tables can contain the score column data. So how do we connect these two tables? Here comes the foreign key. If there are redundant and repeated columns in two tables, and a non-primary key column in one table is the primary key in the other table, then in order to eliminate the redundant columns, we can use this non-primary key column as a bridge connecting the two tables, that is, a foreign key. By observation, we can find that studentid is the primary key in the first table and a non-primary key in the second table, so it is the foreign key of the second table. Therefore, in the above situation, we have the following writing that conforms to the third normal form:

+-----------+-------------+---------+
| studentid | studentname | subject |
+-----------+-------------+---------+
| 1 | Mike | Math |
| 2 | John | Chinese |
| 3 | Kate | History |
+-----------+-------------+---------+

+-----------+-----------+-------+
| subjectid | studentid | score |
+-----------+-----------+-------+
| 101 | 1 | 96 |
| 111 | 3 | 100 |
| 201 | 2 | 85 |
+-----------+-----------+-------+

It can be found that after setting the foreign key, even if the score column is deleted in the first table, the corresponding score value can be found in the second table through studentid. This eliminates data redundancy without affecting the search, satisfying the third normal form.

2. Advantages and Disadvantages of Paradigms

Advantages of the paradigm

• Normalized update operations are generally faster than denormalized ones.
• When data is well normalized, there is little or no duplication of data, so less data needs to be modified.
• Normalized tables are usually smaller and fit better in memory, so operations are faster.
• Less redundant data means less need for DISTINCT or GROUP BY statements when retrieving tabular data.

Disadvantages of the paradigm

• The downside of normalization is that it usually requires association. Any query that is slightly more complex may require at least one join on a normalized database, perhaps more, which is not only expensive but may also invalidate some indexing strategies.
  

This is the end of this article on the detailed use of MySQL paradigm. For more relevant MySQL paradigm content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!