Database Paradigm (1NF,2NF,3NF) parsing

Source: Internet
Author: User

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). The following is a brief introduction to these three paradigms.
The 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.
The second paradigm (2NF): The first is 1NF, another contains two parts, one is the table must have a primary key, and the other is not included in the primary key column must be completely dependent on the primary key, and not only rely on a 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.
The third paradigm (3NF): First is 2NF, the other non-primary key column must be directly dependent on the primary key, there can be no delivery 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 Paradigm (1NF,2NF,3NF) parsing

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.