Database (first paradigm, second paradigm, third paradigm)

Database (first paradigm, second paradigm, third paradigm)

Paradigm: The English name is the Normal Form, it is the British e.f.codd (relational database ancestor) in the 70 's to put forward a relational database model, the paradigm is the basis of relational database theory, but also we in the design of database structure process to follow the rules and guidance method. There are 8 types of patterns that can be found at present, in order: 1NF,2NF,3NF,BCNF,4NF,5NF,DKNF,6NF. It is usually used in the first three paradigms, namely: primary paradigm (1NF), second Normal (2NF), and third Normal (3NF).

When designing the relational database, we should design a reasonable relational database to conform to different specifications, and the different norm requirements are called different paradigms, and the higher paradigm database redundancy is lower. There are currently six paradigms for relational databases: the first paradigm (1NF), the second paradigm (2NF), the third paradigm (3NF), the bath-cod paradigm (BCNF), the fourth paradigm (4NF) and the fifth paradigm (5NF, also known as the perfect paradigm). The paradigm that satisfies the minimum requirements is the first paradigm (1NF). The second paradigm (2NF) is further satisfied on the basis of the first paradigm, and the rest of the paradigms are referred to by analogy. In general, the database only needs to meet the third normal form (3NF) on the line. The following is a brief introduction to these three paradigms.

First Paradigm (1NF)The emphasis is on the atomicity of the column, that is, the column cannot be divided into several other columns.
Consider a table that says "Contacts" (name, gender, phone)

If a contact has a home phone and a company phone in a real-world scenario, the table structure is not designed to reach 1NF. To comply with 1NF we simply split the column (phone), namely: "Contact" (name, gender, home phone, company phone). 1NF is good to distinguish, 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 pattern, and the first normal form (1NF) must be satisfied in the general design. However, some relational models break the 1NF limit, a relational model called non-1NF. In other words, whether the minimum requirement of 1NF must be met depends primarily on the relational model being used .

second paradigm (2NF)

The first is 1NF, and there are two parts, one is that the table must have a primary key, and the other is that the columns that are not included in the primary key must be completely dependent on the primary key, not just part of the primary key.

consider an order schedule: "OrderDetail" (orderid,productid,unitprice,discount,quantity,productname).
because we know we can order a variety of products in one order, so a single OrderID is not enough to be the primary key, the primary key should be (Orderid,productid). Obviously Discount (discount), Quantity (quantity) depends entirely on (depending on) Yu Si (Oderid,productid), and unitprice,productname only depends on ProductID. Therefore, the OrderDetail table does not conform to 2NF. Designs that do not conform to 2NF tend to produce 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,productname in the original order table.

second paradigm (2NF) requires that the attributes of an entity depend entirely on the primary key. The so-called full dependency is the inability to have a property that depends only on the primary key , and if so, this part of the property and the primary key should be separated to form a new entity, and the new entity is a one-to-many relationship with the original entity. For the implementation of the distinction, it is common to add a column to the table to store unique identities for each instance. In short, the second paradigm is that on the basis of the first paradigm the property is completely dependent on the primary key.

Third paradigm (3NF)

< Span style= "Color:rgb (51,51,51); Font-family:arial, song body, Sans-serif; font-size:14px; line-height:24px; Text-indent:28px "> On the basis of 1NF, Any non-primary Property does not depend on other non-principal properties [to eliminate transitive dependencies on 2NF basis].

< /span> The third paradigm (3NF) is a subset of the second paradigm (2NF) that satisfies the third normal form (3NF) that must satisfy the second normal form (2NF)

The first is 2NF, and the non-primary key column must be directly dependent on the primary key, and there cannot be a transitive dependency.  That cannot exist: non-primary key column A relies 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) primary key is (OrderID).

the non-primary key columns, such as orderdate,customerid,customername,customeraddr,customercity, are completely dependent on the primary key (OrderID), so it conforms to 2NF. However, the problem is that customername,customeraddr,customercity is directly dependent on the CustomerID (non-primary key column), rather than directly relying on the primary key, it is passed through to rely on the primary key, so it does not conform to 3NF.

By splitting "order" to "order" (Orderid,orderdate,customerid) and "Customer" (CUSTOMERID,CUSTOMERNAME,CUSTOMERADDR, customercity) thus reaching 3NF.

the concepts of the second paradigm (2NF) and the third paradigm (3NF) are easy to confuse, and the key to distinguishing them is whether the non-primary key columns are completely dependent on the primary key or part of the primary key; 3NF: The non-primary key column is directly dependent on the primary key, or is directly dependent on the non-primary key column.

Database (first paradigm, second paradigm, third paradigm)