I. Paradigms
The English name of the paradigm is Normal Form, which was summarized by the British (the oldest ancestor of relational databases) in the 1970s after proposing the relational database model. Paradigm is the basis of relational database theory, but also in the process of designing the database structure we have to follow the rules and guidelines. Currently there are traces of a total of eight paradigms, in order: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, DKNF, 6NF. usually used only the first three paradigms, that is: the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF).
First Paradigm (1NF)
The first paradigm is actually the basis of relational databases, i.e., any relational database is compliant with the first paradigm. Simply put, the first paradigm is that each row of individual data is indivisible, there can be no more than one value in the same column, and if there is a duplicate attribute you need to define a new corpse entity.
The following database then does not fit the first paradigm:
+------------+-------------------+ | workername | company | +------------+-------------------+ | John | ByteDance,Tencent | | Mike | Tencent | +------------+-------------------+
What the data described above expresses is that Mike works at Tencent and John works at both ByteDance and Tencent (assuming this is possible). But this expression does not satisfy the first paradigm, which states that the data in the columns must be indivisible; to satisfy the first paradigm, it must be of the form below:
+------------+-----------+ | workername | company | +------------+-----------+ | Mike | Tencent | | John | ByteDance | | John | Tencent | +------------+-----------+
Second Paradigm (2NF)
First, for a database to satisfy the second paradigm it must first satisfy the first paradigm.
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 the hiree, the job department, and the leader. The relationships represented by this table are entirely possible in real life. Now let's consider the question, how would we modify the table if Brown took over as head of the Accounting department? This problem will become very troublesome, because we will find that the data are coupled together, you can hardly find a good can uniquely determine the conditions of each row to perform your UPDATE statement. Instead, we make the data that uniquely represents a row of a table in the database the primary key of that table. Therefore, a table without a primary key is not second-paradigm compliant, which means that second-paradigm compliant tables need to specify a primary key.
So we need to split the above table into two tables in order to make it conform to the second paradigm:
+----------+-------------+ | employee | department | +----------+-------------+ | Brown | Accounting | | Green | Engineering | | Jones | Accounting | | Smith | Engineering | +----------+-------------+ +-------------+-------+ | department | head | +-------------+-------+ | Accounting | Jones | | Engineering | Smith | +-------------+-------+
In these two tables, the first table has a primary key of employee and the second table has a primary key of department. in this case, completing the above problem seems very simple.
Third Paradigm (3NF)
For a relational database to satisfy the third paradigm it must first satisfy the second paradigm.
Before putting the third paradigm in place, let's look at two tables likewise:
+-----------+-------------+---------+-------+ | 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, and it is clear that both tables conform to the second paradigm.
But we will find that these two tables have duplicate redundant data score. so the third paradigm is to eliminate redundant data, specifically in the case above, is that only one of the two tables can exist score this column of data. So how to link these two tables, here comes the foreign key. If the two tables have redundant duplicate columns, and this table in a non-primary key column in another table is the primary key, then we can eliminate redundant columns in order to eliminate the non-primary key columns as a bridge to link the two tables, that is, the foreign key. By observation we can see that studentid is primary key in the first table and non-primary key in the second table, so he is the foreign key of the second table. So the above scenario we have the following writeup which conforms to the third paradigm:
+-----------+-------------+---------+ | 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 first table deletes the score column, you can still find the corresponding score value in the second table through studentid, which eliminates data redundancy and does not affect the search, satisfying the third paradigm.
II. Advantages and disadvantages of the paradigm
Advantages of the paradigm
- Paradigmatic update operations are usually faster than counter-paradigms.
- When data is better paradigmatized, there are few or no duplicates, so only less data needs to be modified.
- Paradigm tables are usually smaller and can be better placed in memory, so performing operations will be faster.
- Few redundancies means fewer DISTINCT or GROUP BY statements are needed to retrieve list data.
Disadvantages of the paradigm
- The disadvantage of paradigms is that they usually require correlation. A slightly more complex query statement may require at least one association on a paradigm-compliant database, perhaps more, which is not only expensive but may also invalidate some indexing strategies.
This article on the use of MySQL paradigm is introduced to this article, more related to MySQL paradigm content please search my previous articles or continue to browse the following related articles I hope you will support me more in the future!