MySQL Learning note (7) Design paradigm

Design Paradigm (NF = Normal Format)

Paradigm: the canonical format.

Paradigms are rules that must be adhered to in designing relational databases.

If we meet the requirements of the design paradigm, the database will be concise and structurally clear.

Conversely, there will be data redundancy, as well as inserting, deleting, modifying data anomalies.

Type of design paradigm:1NF,2NF,3NF,BCNF(Badesco paradigm), 4NF,5NF(Perfect Paradigm).

1NF is the most relaxed, increasing the limit in turn.

The general database only needs to be satisfied to 3NF .

1,1NF(First paradigm): Atomicity of Fields

Requirements: Data is a two-dimensional table, each column can not be split, attributes can no longer be split, the field guarantees atomicity.

Databases that do not meet 1NF are non-relational.

2 examples of 1NF are not satisfied:

Name	Tel	Age
Xiao ming	13988774444, 011 - 15945612	1
Little Red	16812314725	0

Name	Mobile phone, landline	Age
Xiao ming	13988774444,011-15945612	1
Little Red	16812314725, no	0

Workaround:

Name	Cell phone	Landline	Age
Xiao ming	13988774444	011 - 15945612	1
Little Red	16812314725	No	0

2,2NF(second paradigm): eliminate partial (incomplete) Dependencies

Requirements: To satisfy the premise of 1NF , eliminate the partial dependence of the field on the primary key, which completely depends on the primary key.

Dependency: If field A is determined, you can indicate The value of the B field.

Partial (incomplete) dependency: A value that relies on one of the parts.

Examples of not satisfying 2NF:

Students	Course	Teacher's name	Teacher's Degree	Course Materials	Class Room	Class time
Xiao ming	In the second grade language	Tom	Graduate	Second Grade Chinese book	102	8:00
Little Red	Grade five, mathematically.	John doe	Dr	The Grade Five math book	60S	16:00

A course, must appoint a textbook, first grade language It is certainly used in the first year of the Chinese book , then there is a course to introduce teaching materials. The course determines the textbook, which is called partial dependence.

Workaround, split into two or several tables:

teacher TD width= "101" valign= "Top" >

student	course		class classroom	class time
Xiao ming	In the second grade language	Tom	Graduate	102	8:00
Little Red	Grade five, mathematically.	John doe	Dr	60S	16:00

Course	Textbooks
In the second grade language	Second Grade Chinese book
Grade five, mathematically.	The Grade Five math book

3,3NF: Eliminate transmission dependence

Requirements: to meet the premise of 2NF to eliminate transmission dependence.

When there are more than one class of independent entities in the table, data can also be redundant, in the case of insertions, modifications, deletions, and possibly confusion.

↓ Workaround: Split into 3 tables and use the primary key to establish a relationship.

Students ' study number	Student Name	Student Courses	Teachers
110	Xiao ming	001	578
911	Little Red	005	36w

Course ID	Course Name	Textbook name
001	In the second grade language	Second Grade Chinese book
005	Grade five, mathematically.	The Grade Five math book

Teacher ID	Teacher's name	Teacher's Degree
578	Tom	Graduate
561	John doe	Dr

4. BC paradigm (BCNF): 3NF compliant, and the primary attribute is not dependent on the primary attribute .

If the relationship pattern is in the first paradigm, and each attribute is not passed by a key code, then R belongs to the BC paradigm.

usually:the conditions of the BC paradigm have multiple equivalents: each non-trivial dependency must have a key code on the left side, and each determinant must contain a key code.

The BC paradigm checks both non-primary and primary properties. When only the non-primary attribute is checked, it becomes the third paradigm. The relationship of the BC paradigm must satisfy the third paradigm.

It is also possible to say that if a relationship reaches the third paradigm, and it has only one candidate code, or if each of its candidate codes is a single attribute, the relationship naturally reaches the BC paradigm.

Generally, a database design conforms to 3NF or BCNF . In the BC paradigm, there are the fourth paradigm and the five paradigm.

5. Fourth paradigm: requires that many-to-many relationships within the same table be deleted.

6. Fifth paradigm: re-establish the original structure from the final structure.

This article is from the "Learning and Exchange" blog, please be sure to keep this source http://chenhaolinux.blog.51cto.com/9609922/1710798

MySQL Learning note (7) Design paradigm