What is the difference between first, second and third database paradigm?

The difference between the first, second and third normal forms of database paradigm is: 1. The first normal form means that there are no duplicate columns; 2. The second normal form means that the attributes completely depend on the primary key; 3. The third normal form means that the attributes do not Depends on other non-primary attributes.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

First normal form (1NF) no duplicate columns

The so-called first normal form (1NF) means that each column of the database table is indivisible Basic data items cannot have multiple values ​​in the same column, that is, an attribute in the entity cannot have multiple values ​​or duplicate attributes. If repeated attributes appear, you may need to define a new entity. The new entity is composed of repeated attributes. There is a one-to-many relationship between the new entity and the original entity. In first normal form (1NF) each row of the table contains information about only one instance. In short, first normal form is a column without duplicates.

1NF is defined as: every attribute in a relationship that conforms to 1NF cannot be subdivided

The situation shown in the following table does not meet the requirements of 1NF:

Note: In any relational database, the first normal form (1NF) is the basic requirement for the relational model. A database that does not meet the first normal form (1NF) is not a relational database.

Second Normal Form (2NF) attributes are completely dependent on the primary key

Second Normal Form (2NF) is established on the basis of First Normal Form (1NF). That is, to satisfy the second normal form (2NF), you must first satisfy the first normal form (1NF). Second Normal Form (2NF) requires that each instance or row in a database table must be uniquely distinguishable. To achieve differentiation, it is usually necessary to add a column to the table to store the unique identification of each instance. For example, an employee number (emp_id) column is added to the employee information table. Because each employee's employee number is unique, each employee can be uniquely distinguished. This unique attribute column is called the primary key or primary key or primary key.
Second Normal Form (2NF) requires that the attributes of an entity completely depend on the primary key. The so-called complete dependence means that there cannot be an attribute that only depends on part of the primary key. If it exists, then this attribute and this part of the primary key should be separated to form a new entity. The new entity and the original entity are one-to-many. relation. To achieve differentiation, it is usually necessary to add a column to the table to store the unique identification of each instance. In short, the second normal form is that the attributes are completely dependent on the primary key.

Third Normal Form (3NF)

To satisfy the third normal form (3NF), you must first satisfy the second normal form (2NF). In short, third normal form (3NF) requires that a database table does not contain non-primary key information that is already contained in other tables.

For example, there is a department information table, in which each department has department number (dept_id), department name, department profile and other information. Then after the department number is listed in the employee information table, department name, department profile and other department-related information cannot be added to the employee information table. If the department information table does not exist, it should be constructed according to the third normal form (3NF), otherwise there will be a lot of data redundancy.

In short, the third normal form is that attributes do not depend on other non-primary attributes. In other words, if there is a transfer function dependency of non-primary attributes on the code, it does not meet the requirements of 3NF.

Explanation of examples:

First normal form

If all attributes of a relational schema R are indivisible basic data items, then R∈1NF (that is, R conforms to the first paradigm).

Two points:

1. Each field can only store a single value

2. Each record must be identified by a unique primary key

Second Normal Form

If the relational schema R∈1NF (that is, R conforms to the first normal form), and each non-primary attribute completely depends on the code of R, then R∈2NF (that is, R conforms to the first normal form) second normal form).

Third Normal Form

If the relational pattern R∈3NF (that is, R conforms to the third normal form), then each non-primary attribute neither partially depends on the code nor transitively depends on the code.

Related learning recommendations: mysql tutorial(video)

The above is the detailed content of What is the difference between first, second and third database paradigm?. For more information, please follow other related articles on the PHP Chinese website!