SQL first paradigm, second paradigm, third paradigm, BCNF

Author: Dolphin

Original address: http://blog.csdn.net/qingdujun/article/details/27365979

One, the first paradigm 1NF

Requirements: Each component must be a non-divided data item.

Characteristics:

1) has a primary key, and the primary key cannot be empty.

2) field can no longer be divided.

Example: (The following example does not satisfy the first paradigm)

/* School Number      age        Information  *       /Sno Sage        sinfo  1001          Shaanxi Xian, tel: 10086  1001      20          Baoji, Shaanxi

Primary key: Sno

Reason not satisfied: 1) Primary key is duplicated. 2) The Sinfo field can be divided again.

Ii. Second Paradigm 2NF

Requirements: On the basis of paradigm one, and each non-principal attribute full function depends on the code.

Characteristics:

1) Meet the first paradigm.

2) Each non-primary attribute in the table must be fully dependent on this table code.

3) Only when a table, the main code is composed of two or more attributes, will appear in the case of non-conforming to the second paradigm.

Example: (The following example does not conform to the second paradigm)

/* Study number Course    No.     */  Sno      Cno      score    Sname  1001     001      Database  

Primary key: Sno,cno

Not satisfied with the reason: Sname is not completely dependent on the code, the course name part depends on the code CNO.

Iii. Third Paradigm 3NF

Requirements: On the basis of satisfying the second paradigm, and each non-principal attribute is neither dependent on the code nor the pass-through code.

Characteristics:

1) Meet the second paradigm.

2) The non-primary attribute cannot be passed by code.

Example: (The following example does not conform to the third paradigm)

/* School Number     Department     head */  Sno     sdept     shead  1001   Computer Department    Zhang San

Primary key: Sno

Not satisfied with the reason: Shead transmission depends on the code, Sno->sdept->shead, can get sno->shead.

Iv. BCNF

Requirements: On the basis of satisfying the third paradigm, and not allowing part of the primary key to be determined by another part or other part.

Specific:

1) Meet the third paradigm.

2) All non-primary properties are fully functional dependent on each code.

3) All the main attributes for each code that does not contain it are also fully functional dependent.

4) None of the properties of the full function depend on any set of properties of the fly code.

Example: (The following example does not conform to section bcnf paradigm)

/* Student       Teacher        Course * *  Stu      Teacher      Course  Zhang San       lee Kai-fu       database

Suppose: Each teacher teaches only one course.

Candidate Code:

(Stu,teacher)->course

(Stu,course)->teacher

These two codes consist of two attributes, and they intersect each other, so there is no transitive dependency, so it is 3NF.

However, because: Teacher->course, (part of the primary key is determined by another part), so it does not conform to BCNF.

Reference: Wang Shan, Shaman Xuan Munsu. Introduction to Database Systems (4th edition) [M]. Beijing: Higher Education Press, 2006.170-178.

Giseryang Blog Park. The first paradigm, the second paradigm, and the third paradigm [Cp/ol]. Http://www.cnblogs.com/GISerYang/archive/2012/05/09/2491996.html, 2012-05-09/2014-05-28.

Ruthless Blog Park. Three main paradigms of database design [Cp/ol]. Http://www.cnblogs.com/linjiqin/archive/2012/04/01/2428695.html, 2012-04-01/2014-05-28.
Allenlsy Lonely and Silent Csdn blog. First paradigm, second paradigm, third paradigm, bcnf[cp/ol].http://blog.csdn.net/allenlsy/article/details/ 5356899,2010-03-08/2014-05-28.