Database (first paradigm, second paradigm, third paradigm), Database paradigm

Database (first paradigm, second paradigm, third paradigm), Database paradigm

Database (first, second, and third)

Paradigm: the English name is Normal Form, which is E. f. codd was summarized after the relational database model was proposed in the 1970s S. The paradigm is the basis of the relational database theory, it is also the rule and guiding method we should follow in designing the database structure. There are currently eight traceable paradigms: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, DKNF, and 6NF. Generally, only the first three paradigms are used: 1NF, 2NF, and 3NF ).

When designing a relational database, a rational relational database is designed according to different specifications. These different specifications and requirements are called different paradigms and various paradigm submission specifications, the higher the standard database, the smaller the redundancy. Currently, relational databases have six paradigms: 1NF, 2NF, 3NF, BCNF, and 4NF) and the fifth Paradigm (5NF, also known as perfect paradigm ). The first paradigm (1NF) meets the minimum requirements ). The second Paradigm (2NF) that meets more normative requirements on the basis of the first paradigm, and the other paradigms are similar. Generally, databases only need to satisfy the 3NF. The following describes the three paradigms.

1NF emphasizes that Atomicity of ColumnsThat is, the column cannot be further divided into several other columns.
Consider this table: [contact] (name, gender, phone number)

In actual scenarios, if a contact has a home phone number and a company phone number, the table structure is not 1NF. To comply with 1NF, we only need to split the column (phone number), that is, [contact person] (name, gender, home phone number, company phone number ). 1NF is well identified, but 2NF and 3NF are easy to confuse.

Note: In any relational database, the first paradigm (1NF) is the basic requirement for the design of the relational model. Generally, the first paradigm (1NF) must be met in the design ). However, some relational models break through the 1NF restriction. This is a non-1NF relational model. In other words, whether or not it must meet the minimum 1NF requirement depends mainly on the relational model used.

2NF)

The first is 1NF, and the other two parts are included. One is that the table must have a primary key; the other is that the columns not included in the primary key must be completely dependent on the primary key, instead of relying only on a portion of the primary key.

Consider an order list: [OrderDetail] (OrderID, ProductID, UnitPrice, Discount, Quantity, ProductName ).
Because we know that multiple products can be ordered in an order, an OrderID alone is not enough to become a primary key. The primary key should be (OrderID, ProductID ). Obviously, Discount (Discount) and Quantity (Quantity) depend entirely on the primary key (OderID, ProductID), while UnitPrice and ProductName depend only on ProductID. Therefore, the OrderDetail table does not conform to 2NF. Non-conforming 2NF design is prone to redundant data.

You can split the [OrderDetail] table into [OrderDetail] (OrderID, ProductID, Discount, Quantity) and [Product] (ProductID, UnitPrice, ProductName) to eliminate UnitPrice in the original order table, productName is repeated multiple times.

2NF requires entity attributesFull dependencyKeyword. The so-called full dependency refers to the fact that there cannot be an attribute that only depends on a part of the primary keyword. If so, this attribute and this part of the primary keyword should be separated to form a new entity, the relationship between the new object and the original object is one-to-multiple. To implement differentiation, you usually need to add a column to the table to store the unique identifier of each instance. In short, the second paradigm is based on the first paradigm that attributes fully depend on primary keys.

3NF)

Based on 1NF, any non-MasterAttributeIndependent from other non-primary attributes [eliminating transfer dependencies Based on 2NF].

3NF is a subset of 2NF, that is, 3NF must satisfy 2NF ).

2NF is the first, and non-primary key columns are required.Direct dependencyIn the primary key, there cannot be a transfer dependency. 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 that the primary key of an Order table [Order] (OrderID, OrderDate, CustomerID, CustomerName, CustomerAddr, CustomerCity) is (OrderID ).

Among them, non-primary key columns such as OrderDate, CustomerID, CustomerName, CustomerAddr, and CustomerCity depend entirely on the primary key (OrderID), so they comply with 2NF. However, the problem is that CustomerName, CustomerAddr, and CustomerCity depend directly on CustomerID (non-primary key column) instead of directly relying on the primary key. It depends on the primary key through transmission, so it does not conform to 3NF.

You can split [Order] into [Order] (OrderID, OrderDate, CustomerID) and [Customer] (CustomerID, CustomerName, CustomerAddr, CustomerCity) to 3NF.

The concepts of 2NF and 3NF are confusing. The key to distinguishing them is: 2NF: whether non-primary key columns fully depend on the primary key, it depends on a portion of the primary key. 3NF: whether a non-primary key column depends directly on the primary key or a non-primary key column.