Fifth three paradigm of SQL Server database

Analysis:

The database design should follow three main paradigms, respectively:

First paradigm: Ensure the atomicity of each column in the table (non-detachable);

Second paradigm: Make sure that each column in the table is related to the primary key, not just a part of the primary key (mainly for the Federated primary Key), and that the primary key column and the non-primary key column follow the full function dependency (fully dependent);

Third paradigm: There is no transitive function dependency between non-primary key columns (elimination of transitive dependencies);

Detailed:

First Paradigm

Requirements Description: A database system requires an entity table, which is used to store user information, where the attribute "address" requires querying to provinces, cities, and detailed addresses.

Example: The information is as follows:

Name: Zhang Hongxin; Gender: Male, Age: 26 years old, age: 26 years old; tel: 0378-23459876; province: Henan Province; City: Kaifeng; Address: No. 23rd, Xinhualu, Chaoyang District;

Name: Wang Yan; gender: female; Age: 25; tel: 021-2348768; province: Guizhou Province; City: Guiyang; Address: Nanming District Nanming District Shifeng Road, No. 6th ;

Name: Wang Mei; Gender: female; Age: 21; tel: 0571-3876450; province: Zhejiang province; City: Hangzhou; Detailed Address: Bingjiang District No. 352 , Riverside Road;

For the above requirements, two types of tables are designed below:

First table design: Not satisfying the first paradigm

Tb_userinfo

Analysis: Why not meet the first paradigm? Because the region column is not atomic, it can be split into provinces, cities, and specific addresses.

Second table design: satisfying the first paradigm

Tb_userinfo

Second Paradigm

Requirements Description: Design an order Information table, orders have a variety of goods, the order number and the product number as a joint primary key.

First table design: Not satisfying the second paradigm

Analysis: Order number and product number as the joint primary key, because of the product name, unit, price these columns only related to the product number, and the order

No relation to the primary key (Federated primary key), which violates the second principle of the paradigm.

Second table design: satisfying the second paradigm

Analysis: The first design table is split, the product information is separated into another table, the Order Item table is also separated into another table.

Third Paradigm

analysis: In a table, a userid can determine a UserLevel. In this way, theuserid relies on Studentno and Cardno, and userlevel

Depending on the UserID, this leads to a transitive dependency, and3NF is the elimination of that dependency.

split the above table into the following table to meet the third paradigm:

Note, the above content refer to the blog URL:

Http://www.cnblogs.com/springside-example/archive/2011/10/06/2530207.html

http://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html#undefined

Fifth three paradigm of SQL Server database