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

Reprint http://blog.csdn.net/famousdt/article/details/6921622 Copyright belongs to the original author all

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). 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 (first paradigm, second paradigm, third paradigm) 2