What is the first normal form, second normal form and third normal form of database?

Paradigm: The English name is Normal Form, which was summed up by the British E.F. Codd (the ancestor of relational database) after he proposed the relational database model in the 1970s. Paradigm is the basis of relational database theory and also the basis of our work in relational database. The rules and guidelines to be followed when designing a database structure. There are currently 8 paradigms that can be traced, in order: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, DKNF, and 6NF. Usually only the first three normal forms are used, namely: first normal form (1NF), second normal form (2NF), and third normal form (3NF). The following is a brief introduction to these three paradigms.
◆ First normal form (1NF): It emphasizes the atomicity of columns, that is, columns cannot be divided into other columns.
Consider such a table: [Contact] (name, gender, phone number)
If in an actual scenario, a contact has a home phone number and a company phone number, then this table structure design does not reach 1NF. To comply with 1NF we only need to split the column (phone), namely: [Contact Person] (name, gender, home phone number, company phone number). 1NF is easy to distinguish, but 2NF and 3NF are easily confused.
◆ Second normal form (2NF): First, it is 1NF, and it also contains two parts. First, the table must have a primary key; second, the columns not included in the primary key must be completely dependent on the primary key, and cannot only rely on the primary key. a part of.
Consider an order detail table: [OrderDetail] (OrderID, ProductID, UnitPrice, Discount, Quantity, ProductName).
Because we know that multiple products can be ordered in one order, a single OrderID is not enough to be the primary key. The primary key should be (OrderID, ProductID). It is obvious that Discount and Quantity are completely dependent on the primary key (OderID, ProductID), while UnitPrice and ProductName only depend on ProductID. So the OrderDetail table does not conform to 2NF. Designs that do not comply with 2NF are prone to redundant data.
You can split the [OrderDetail] table into [OrderDetail] (OrderID, ProductID, Discount, Quantity) and [Product] (ProductID, UnitPrice, ProductName) to eliminate multiple repetitions of UnitPrice and ProductName in the original order table.
◆ Third normal form (3NF): First, it is 2NF. In addition, non-primary key columns must directly depend on the primary key, and there cannot be transitive dependencies. That is, it cannot exist: non-primary key column A depends on non-primary key column B, and non-primary key column B depends on the primary key.
Consider an order table [Order] (OrderID, OrderDate, CustomerID, CustomerName, CustomerAddr, CustomerCity) where the primary key is (OrderID).
Among them, OrderDate, CustomerID, CustomerName, CustomerAddr, CustomerCity and other non-primary key columns are completely dependent on the primary key (OrderID), so they comply with 2NF. However, the problem is that CustomerName, CustomerAddr, and CustomerCity directly depend on CustomerID (non-primary key column) instead of directly relying on the primary key. They rely on the primary key through transmission, so they do not comply with 3NF.
Achieve 3NF by splitting [Order] into [Order] (OrderID, OrderDate, CustomerID) and [Customer] (CustomerID, CustomerName, CustomerAddr, CustomerCity).
The concepts of second normal form (2NF) and third normal form (3NF) are easily confused. The key point to distinguish them is, 2NF: whether the non-primary key column completely depends on the primary key, or depends on part of the primary key; 3NF: non-primary key Whether the primary key column directly depends on the primary key, or directly depends on the non-primary key column.

Paradigm: The English name is Normal Form, which was summed up by the British E.F. Codd (the ancestor of relational database) after he proposed the relational database model in the 1970s. Paradigm is the basis of relational database theory and also the basis of our work in relational database. Rules and guidelines to be followed when designing a database structure. There are currently 8 paradigms that can be traced, in order: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, DKNF, and 6NF. Usually only the first three normal forms are used, namely: first normal form (1NF), second normal form (2NF), and third normal form (3NF). The following is a brief introduction to these three paradigms.
◆ First Normal Form (1NF): Emphasis is on the atomicity of the column, that is, the column cannot be divided into other columns.
Consider such a table: [Contact] (name, gender, phone number)
If in an actual scenario, a contact has a home phone number and a company phone number, then this table structure design does not reach 1NF. To comply with 1NF we only need to split the column (phone), namely: [Contact Person] (name, gender, home phone number, company phone number). 1NF is easy to distinguish, but 2NF and 3NF are easily confused.
◆ Second normal form (2NF): First, it is 1NF, and it also contains two parts. First, the table must have a primary key; second, the columns not included in the primary key must be completely dependent on the primary key, and cannot only rely on the primary key. a part of.
Consider an order detail table: [OrderDetail] (OrderID, ProductID, UnitPrice, Discount, Quantity, ProductName).
Because we know that multiple products can be ordered in one order, a single OrderID is not enough to be the primary key. The primary key should be (OrderID, ProductID). It is obvious that Discount and Quantity are completely dependent on the primary key (OderID, ProductID), while UnitPrice and ProductName only depend on ProductID. So the OrderDetail table does not conform to 2NF. Designs that do not comply with 2NF are prone to redundant data.
You can split the [OrderDetail] table into [OrderDetail] (OrderID, ProductID, Discount, Quantity) and [Product] (ProductID, UnitPrice, ProductName) to eliminate multiple repetitions of UnitPrice and ProductName in the original order table.
◆ Third normal form (3NF): First, it is 2NF. In addition, non-primary key columns must directly depend on the primary key, and there cannot be transitive dependencies. That is, it cannot exist: non-primary key column A depends on non-primary key column B, and non-primary key column B depends on the primary key.
Consider an order table [Order] (OrderID, OrderDate, CustomerID, CustomerName, CustomerAddr, CustomerCity) where the primary key is (OrderID).
Among them, OrderDate, CustomerID, CustomerName, CustomerAddr, CustomerCity and other non-primary key columns are completely dependent on the primary key (OrderID), so they comply with 2NF. However, the problem is that CustomerName, CustomerAddr, and CustomerCity directly depend on CustomerID (non-primary key column) instead of directly relying on the primary key. They rely on the primary key through transmission, so they do not comply with 3NF.
Achieve 3NF by splitting [Order] into [Order] (OrderID, OrderDate, CustomerID) and [Customer] (CustomerID, CustomerName, CustomerAddr, CustomerCity).
The concepts of second normal form (2NF) and third normal form (3NF) are easily confused. The key point to distinguish them is, 2NF: whether the non-primary key column completely depends on the primary key, or depends on part of the primary key; 3NF: non-primary key Whether the primary key column directly depends on the primary key, or directly depends on the non-primary key column.

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