MySQL three main paradigms

First paradigm: Ensure the atomicity of each column.

The first paradigm is the most basic paradigm.

A field in a database table is a single attribute and cannot be divided.

As long as the relational database satisfies the first paradigm

If each column (or each attribute) is the smallest data unit (also known as the smallest atomic unit) that is not re-divided, the first normal form is satisfied.

Example: Customer table (name, number, address 、......) Where the "address" column can also be subdivided into countries, provinces, municipalities, districts and so on.

2 . The second paradigm (make sure that each column in the table is related to the primary key).

If a relationship satisfies the first normal form and the columns other than the primary key are dependent on the primary key, the second normal form is satisfied.

For example: the order form (order number, product number, order date, Price 、......), "order number" as the primary key, "product number" and primary key column do not have a direct relationship, that is, the "Product number" column does not depend on the primary key column, you should delete the column.

***********************************************************

Does not satisfy the second normal form, the combination of a and field C repeats in a composite primary key
+------------+-----------+-------------------+
PK PK Row
+------------+-----------+-------------------+
A B C
+------------+-----------+-------------------+
A D C
+------------+-----------+-------------------+
A E C
+------------+-----------+-------------------+

Instead of satisfying the second paradigm (but not satisfying the third paradigm, field A and field C are combined duplicates):
+---------+------------+-----------+-------------------+
PK Row, Row row
+---------+------------+-----------+-------------------+
1 A B C
+---------+------------+-----------+-------------------+
2 A D C
+---------+------------+-----------+-------------------+
3 A E C
+---------+------------+-----------+-------------------+

****************************************************************

3 . The third paradigm (ensure that each column is directly related to the primary key column, not indirectly).

If a relationship satisfies the second normal form and the columns other than the primary key are not dependent on the primary key column, the third normal form is satisfied.

In order to understand the third paradigm, it is necessary to define transitive dependencies according to one of Armstrong km. Suppose that A, B, and C are the three attributes of a relationship R, and if A-〉b and B-〉c, then from these function dependencies, you can derive a-〉c, as described above, that dependency a-〉c is a transitive dependency.

For example: Order form (order number, date of purchase, customer number, customer name, ...), first look at the table is not a problem, to meet the second paradigm, each column and the primary key column "order number" related, and then you will find "Customer name" and "customer number" related, "customer number" and "Order Number" is also related Finally pass-through dependency, "Customer name" and "Order Number" is also related. In order to satisfy the third paradigm, the "Customer name" column should be removed and placed in the Customer table.

**********************************************************************

Does not satisfy the third paradigm, field A and field C are combined repeatedly

+---------+------------+-----------+-------------------+---------------+
PK Row row, row row
+---------+------------+-----------+-------------------+---------------+
1 A B C F
+---------+------------+-----------+-------------------+---------------+
2 A D C G
+---------+------------+-----------+-------------------+---------------+
3 A E C K
+---------+------------+-----------+-------------------+---------------+

Instead, this satisfies the third paradigm:
Table 1
+---------+------------+-----------+
PK Row Row
+---------+------------+-----------+
1 A B
+---------+------------+-----------+
2 A D
+---------+------------+-----------+
3 A E
+---------+------------+-----------+
and table 2
+---------+-------------------+------------+
PK Row Row
+---------+-------------------+------------+
1 C F
+---------+-------------------+------------+
2 C G
+---------+-------------------+------------+
3 C K
+---------+-------------------+------------+

Principle: ************************************

Principle: When the combination of fields and fields repeats, such as the combination of a and C above, the first thing to consider is to split them into 2 tables, whether C is split to table 1, or A to table 1, depending on the situation

The key to understand is that the main purpose of defining this paradigm is to reduce data redundancy, which is essentially the existence of a one-to-many, or many-to-many relationship between fields and fields in a table. To solve this problem, we can easily realize the design of database that satisfies the third paradigm.

Summary ***************************

It boils down to 3 words:

1NF: field is not divided;
2NF: Primary key, non-primary key field depends on primary key;
3NF: Non-primary key fields cannot depend on each other;

Explanation:
1NF: Atomic field can not be re-divided, otherwise it is not a relational database;
2NF: Uniqueness A table shows only one thing;
3NF: Each column is directly related to the primary key, there is no transitive dependency;

examples that do not conform to the first paradigm (create a table in a relational database):

table: Field 1, Field 2 (Field 2.1, Field 2.2), Field 3 ...

the problem: Because the design does not have such a table, so there is no problem;

examples that do not conform to the second paradigm:

Table: School number, name, age, course name, grade, credit;

This table clearly illustrates two transactions: Student information, course information;

*********************** Existing problem: *********************

data redundancy, each record contains the same information;
Delete exception: Delete all student scores, the course information is completely deleted;
Insert Exception: The student does not choose the class, cannot record into the database;
Update Exception: Adjust course credits and all lines are adjusted.

*********************** FIX: ******************************

Student: Student (school number, name, age);

Course: Course (course name, credits);

Elective Relationship: Selectcourse (School number, course name, score).

satisfying the 2nd paradigm only eliminates the insertion exception.


&&&&???????? Examples that do not conform to the third paradigm:???????? &&&&&&&&&&

School number, name, age, school, college contact number, key word for a single keyword "study number";

presence Dependent Delivery: (school number) → (school) → (college location, college phone)

A problem exists:

data redundancy: there are duplicate values;

Update Exception: Duplicate redundant information, modify the need to modify multiple records at the same time, otherwise there will be inconsistent data situation

Delete Exception

FIX:
Student: (School number, name, age, school);
College: (College, location, telephone).

MySQL three main paradigms