Database normalization and classic three-paradigm

Database Normalization and Classic Three Paradigm

Redundancy causes multiple update exceptions, which are difficult to insert, update, and delete rows.

　　 Normalization (normalization) is a series of steps to reduce redundancy and contradiction by modifying tables.

after each step, the database reaches a specific paradigm (normal form) .

The relational model defines three paradigms, named after the famous ordinal number.

First paradigm (1NF)

Second paradigm (2NF)

Third paradigm (3NF)

Each paradigm is more robust than the previous one. databases that meet 3NF are also 2NF and 1NF compliant. The higher the normalization level, the more the number of tables.

　　 Non-destructive decomposition (lossless decomposition) ensures that the partitioning of the table does not cause information loss.

　　 Dependency -Hold decomposition (dependency-preserving decomposition) to ensure that the contact is not lost.

When a table is split, the existence of a matching primary key and foreign key column should not be considered redundant data.

　　Normalization is not systematic, it is an iterative process involving the splitting, re-coupling, and refining of repeating tables.

First paradigm (1NF)

　　Table satisfying the first paradigm:

　　column contains only atomic values.

　　There are no duplicate groups.

　　Atomic Value (also known as scalar value ) is a single value that can no longer be subdivided.

　　Duplicate Groups is a collection of two or more logically associated columns.

Second paradigm (2NF)

The table in the first normal form automatically satisfies the second paradigm when the following conditions are met:

The primary key is a column (that is, the keyword is not a combination).

All columns in the table are part of the primary key (single or combined).

　　Tables satisfying the second paradigm:

　　satisfies the first paradigm.

　　Non-partial function dependency.

if some of the key combinations (but not all) values in the table determine the value of a non-key column, the table contains Partial function Dependency .

The second Paradigm table is Full function Dependency , meaning that if any of the column values in the key combination changes, the value of the Nonkey column will need to be updated.

Third paradigm (3NF)

　　Table satisfying the third paradigm:

　　satisfies the second paradigm.

　　There is no delivery dependency.

if the value of one non-key column determines the value of another non-key column, the table contains transitive Dependency .

In the third Paradigm table, nonkey columns are independent of each other and depend only on the primary key column.

Database normalization and classic three-paradigm