Design and understanding of MySQL database's three-paradigm

The general database design needs to meet the three normal forms, which is the most basic requirements, up to 6NF, but in general, 3NF achieved can be

One: 1NF a paradigm of understanding:

1NF is a relational database of the most basic requirements, that is, the requirements of the attributes of the record is atomic, non-divided, is the attribute can not be divided, this is the basic requirements of the relational database, do not meet this can not be called relational database

For example:

Instructor Sex Class classroom substitute time (start, end)
Hanzhongkang Malephp033110230 Day 2013-03-31,2013-05-05
Hanzhongkang Malephp022810630 Day 2013-02-28,2013-03-30
Hanshunping malePhp022810615 Day 2013-03-31,2013-05-05

The design of the substitute time field above does not satisfy the atomicity, because it can be divided, the start time and the end time, you need to design the split according to the following:

Instructor Sex class classroom substitute time begins to end
Hanzhongkang Malephp033110230 Day 2013-03-312013-05-05
Hanzhongkang Malephp022810630 Day 2013-02-282013-03-30
Hanshunping malePhp022810615 Day 2013-03-312013-04-20

The above is satisfied, but sometimes also need to go against the 1NF design: In the design of CMS with Dede in the background is the format, name, resolution of these are rated in a property, but can be regarded as a whole, because this attribute even if separate, then the individual members of the relationship is not equal, But the substitute time is not the same, such as divided into start time and end time, the relationship of two members is basically the same! So there is no contradiction!

II: Understanding of the 2NF two paradigm

2NF is not part-dependent, partial dependence of the precondition is that there is a combination of primary key, that is, each record needs a primary key, the primary key can be a separate property, but also can be a combination of primary key, is a record of multiple properties to uniquely determine a record, so long as the combination of primary key can produce a partial dependency, Partial dependency is the premise of the combination of the primary key, the remaining properties, not entirely dependent on the combination of primary key, but also partially dependent on the combination of primary key, such as the table's n records by the combination of one or a few of the primary key to determine the properties of the remaining properties, it can be said to produce a partial dependency, and in actual development , instead of adding a field ID from the growth itself, as the primary key, such a single attribute primary key is not to produce a partial dependency!

For example:

Lecturer P sex class p classroom substitute time begins to end
Hanzhongkang Malephp033110230 Day 2013-03-312013-05-05
Hanzhongkang Malephp022810630 Day 2013-02-282013-03-30
Hanshunping malePhp022810615 Day 2013-03-312013-04-20

The above design can use the instructor P, class, two fields as the combined primary key, but the problem comes, then the classroom only by the class field (a combination of one of the primary key) can be determined that the so-called classroom part depends on the combination of primary key, then does not meet 2NF

It should be designed according to the following line:

IDP Instructor Sex Class classroom substitute time begins to end
1 Hanzhongkang Malephp033110230 Day 2013-03-312013-05-05
2 Hanzhongkang Malephp022810630 Day 2013-02-282013-03-30

You can see that just adding a primary key to the IDP is not part of the dependency, this is the actual project in the development of common means!

Three: 3NF understanding: Can not appear transfer dependency: is the primary key, non-primary key 1, non-primary key 23 can not appear between the transitive dependency, if the emergence of the primary key could be introduced non-primary key 1, and then by the non-primary key 1 can be introduced non-primary key 2, then the primary key and the non-primary key 2 generated a In layman's terms, in a table, of course, a record as a unit, the primary key and non-primary key can produce a parent-child relationship, but there is no parent-child relationship between the father and son!

For example:

IDP Instructor Sex Class classroom substitute time begins to end
1 Hanzhongkang Malephp033110230 Day 2013-03-312013-05-05
2 Hanzhongkang Malephp022810630 Day 2013-02-282013-03-30
3 Hanshunping malephp022810615 Day 2013-03-312013-04-20

The above design does not meet 3NF:

Primary KEY Introduction---> Class php0331, Class php0331-----launched----> Classroom 102, so give people the feeling that the classroom is not by the primary key IDP1 directly launched, as if by the class passed through the delivery;

Primary KEY IDP----> Instructor------> Gender

The disadvantage is that data redundancy is generated, and the solution is to pull the intermediary agent out as another table:

Should be designed as follows:

IDP Instructor Class substitute time starts to end
1 Hanzhongkang php033130 Day 2013-03-312013-05-05|
2 Hanzhongkang php022830 Day 2013-02-282013-03-30|
3 Hanshunping php022815 Day 2013-03-312013-04-20|

--------------------------------------------------------

Lecturer Sex
Hanzhongkang Male
Hanshunping Male

-----------------

Class Classroom

php0228 106

PHP0331 102

-------------

You can see that the design requires only 22 fields, and the design of the above requires 24 fields, which saves 2 fields, and if the data is massive, then the data saved should be more

This design, in fact, in order to avoid the duplication of non-Cartesian product data, of course, the Cartesian product of data duplication is necessary!

Then the design of the database can be based on their own ideas to design a "big table" out, and then split into three normal forms of the table, of course, the general rules of the entity are separate as a table

such as the instructor entity, class entity, substitute relationship entity, but the biggest problem is to start not to know which is the entity, in fact, in addition to see, in fact, said the clear can describe the relationship can also be as an entity

, this is easy to overlook, but as the experience increases, slowly will understand!

Design and understanding of MySQL database's three-paradigm