Three paradigms of database design

The paradigm is translated from the English normal form, which is mainly about the first 3 paradigms. These 3 paradigms step by step, the latter paradigm must be based on the former paradigm, if not to achieve the first paradigm, it is impossible to achieve the second paradigm, let alone the third paradigm. All of the 3 paradigms are designed to avoid data redundancy and operation anomalies such as interpolation, modification, and deletion.

1, the first paradigm (1NF): Emphasis on the atomicity of columns, requires that each column can no longer be subdivided. For example, there is a student information table: School number, name, class. "Class" is not atomic because it can be broken down into "grade" and "class". If you do not split it will result in data redundancy, such as:

School Number Name Class

1 three high 31 classes

2 John Doe High 32 classes

3 Harry High 11 classes

From the above can see "class" In the grade is repeated, this is OK, the problem is if we need to revise the grade, such as "Senior" to "graduating class", then the trouble, only the whole table to "class" in a change.

2, the second paradigm (2NF): On the basis of 1NF, two requirements, one must have a primary key, and the second is that the non-primary key must be completely dependent on the primary key. The first requires a good understanding, and the second one is not so easy to understand. In fact, it is very simple, because it is required that the other columns cannot rely on only part of the primary key, which means that there must be two or more than two to have a partial primary key. So only the federated primary Key will appear to meet the situation of 2NF, it is plain that two primary keys split into two tables. Let me give you an example:

School Number name Grade class course highest score

1 Three high 31 classes Chinese 100 80

2 John Doe High 32 classes Chinese 100 78

3 Harry High 11 class English 150 123

1 three high 31 classes math 120 49

2 John Doe High 32 classes Math 120 113

We have met the 1NF, but now we have more courses and the highest score in the table, we have two primary keys: study number and course. The name, grade, class and grades are dependent on the student's number, and the highest score depends on the course rather than the number. This is referred to as part of the dependent primary key. From the above, it is obvious that the data is redundant, and if you want to modify the "language" of the "highest score" of 150, then the whole table can only be one modification; If the school cancels the "English" course, the deletion of "English" will delete the "Harry", poor Harry has no record; If you add a "physics" course "180", then sorry, can not be inserted, because there is no one to apply for, there is no "study number" primary key and dependent on its columns.

3, the third paradigm (3NF): On the basis of 2NF, the non-primary key must be directly dependent on the primary key. 2NF is to be completely dependent, here is to be directly dependent, can not have dependent on the delivery of the situation. For example, column C relies on column B, column B depends on column A, and a is the primary key, then column C is passed on primary key A. This situation must also be split into two tables. Example:

Student number name grade class teacher in charge of the class head cell phone number

1 three High 31 class Zhang Lao 313,811,111,111

2 John Doe High 32 class Zhang Lao 313,811,111,111

3 Harry high 11 class king old 513,922,222,222

At present there is only one primary key "study number", Name, Grade, class, head teacher, class teacher mobile phone number are dependent on the primary key, so 2NF. But "the class teacher mobile phone number" exists the transmission dependence, because it relies on "the class teacher", "the class teacher" relies on "the study number". Data is still redundant, the teacher in charge of mobile phone number or change, add a class teacher also there is no place to put the situation, delete a mobile phone number may also be the head teacher to delete.

Here 2NF and 3NF are a bit bad to distinguish, the key is to see whether the joint primary key. A single primary key does not have a problem with the primary key partial dependency.

Three paradigms of database design