Detailed explanation of MySQL database paradigm

Preface:

I have often heard about database paradigms, but I have never understood them in detail. General database books or database courses will introduce paradigm-related content, and paradigms often appear in database exam questions. I wonder if you have a clear understanding of the paradigm? In this article, let’s learn about database paradigms.

1. Introduction to database paradigm

In order to establish a database with less redundancy and a reasonable structure, certain rules must be followed when designing the database. In a relational database, this rule is called a paradigm. A paradigm is a summary that meets a certain design requirement. In order to design a well-structured relational database, certain paradigms must be met.

The English name of the paradigm is Normal Form, abbreviated as NF. It was summarized by British EF Codd 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 six common paradigms for relational databases: first normal form (1NF), second normal form (2NF), third normal form (3NF), Buss-Codd normal form (BCNF), fourth normal form (4NF) and fifth normal form (5NF, also known as perfect normal form). The minimum normal form is the first normal form (1NF). The first normal form that further meets more specification requirements is called the second normal form (2NF), and the remaining normal forms are similar.

2. Detailed explanation of common paradigms

When designing a database, we will refer to the paradigm requirements, but it does not mean that the higher the level of the paradigm followed, the better. Although a paradigm that is too high has better constraints on data relationships, it will also make the relationships between tables more complicated, resulting in more tables being operated each time and reduced database performance. Usually, in relational database design, the highest level is to follow BCNF, and 3NF is the most common. That is, in general, the first three paradigms are sufficient for us. Let’s take a closer look at the first three commonly used paradigms.

First Normal Form (1NF)

The first paradigm is the most basic paradigm. If all field values ​​in a database table are indecomposable atomic values, it means that the database table satisfies 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 duplicate attributes appear, a new entity needs to be defined.

Example: Suppose a company wants to store the names and contact details of its employees. It creates a table like this:

Two employees (Jon & Lester) have two mobile numbers, so the company stores them in the same table as shown in the table above. Then the table does not conform to 1NF because the rule says "Each attribute of a table must have an atomic (single) value", and the emp_mobile value for Jon & Lester employees violates that rule. In order to make the table conform to 1NF, we should have the following table data:

Second Normal Form (2NF)

The second paradigm goes one step further than the first paradigm. The second paradigm requires ensuring that every column in the database table is related to the primary key, rather than just part of the primary key (mainly for composite primary keys). That is to say, in a database table, only one type of data can be stored in a table, and multiple types of data cannot be stored in the same database table.

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

The above table describes the relationship between employees, work departments and leaders. We call the data that can uniquely represent a row of a table in the database the primary key of this table. The head column in the table is not related to the primary key. Therefore, the table does not conform to the second normal form. In order to make the above table conform to the second normal form, it needs to be split into two tables:

-- employee is the primary key +----------+-------------+
| employee | department |
+----------+-------------+
| Brown | Accounting |
| Green | Engineering |
| Jones | Accounting |
| Smith | Engineering |
+----------+-------------+

-- department is the primary key+-------------+-------+
| department | head |
+-------------+-------+
| Accounting | Jones |
| Engineering | Smith |
+-------------+-------+

Third Normal Form (3NF)

Under the premise of meeting 2NF, all fields other than the primary key must be independent of each other, that is, it is necessary to ensure that each column of data in the data table is directly related to the primary key, and not indirectly related.

In short, the third normal form (3NF) requires that a relation does not contain non-primary key information that is already contained in other relations. For example, there is a department information table, in which each department has information such as department number (dept_id), department name, and department profile. Then, after listing the department number in the employee information table, you can no longer add department name, department profile, and other department-related information to the employee information table. If the department information table does not exist, it should also be constructed according to the third normal form (3NF), otherwise there will be a lot of data redundancy.

3. About Anti-Paradigm

The advantages of the paradigm are obvious. It avoids a lot of data redundancy, saves storage space, and maintains data consistency. Normalized tables are generally smaller and fit better in memory, so operations will be faster. So does it mean that as long as all tables are standardized to 3NF, the database design will be optimal? That's not necessarily the case. A higher paradigm means finer division of tables, more tables are needed in a database, and users have to distribute originally related data into multiple tables. 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.

Therefore, when we design a database, we do not follow the paradigm requirements completely, and sometimes we also perform anti-paradigm design. Improve the database read performance by adding redundant or duplicate data and reduce the number of join tables during associated queries.

The above is a detailed explanation of the MySQL database paradigm. For more information about the MySQL database paradigm, please pay attention to other related articles on 123WORDPRESS.COM!