Database Normalization

• Database Normalization

Database normalization is a database design principle for organizing data in an organized and consistent way.

It helps you avoid redundancy and maintain the integrity of the database. It also helps you eliminate undesirable characteristics associated with insertion, deletion, and updating.

 

• Purpose of Normalization

The main purpose of database normalization is to avoid complexities, eliminate duplicates, and organize data in a consistent way. In normalization, the data is divided into several tables linked together with relationships.

 

• Types of database normalization

1NF, 2NF, and 3NF are the first three types of database normalization. They stand for first normal form, second normal form, and third normal form, respectively.

There are also 4NF (fourth normal form) and 5NF (fifth normal form). There’s even 6NF (sixth normal form), but the commonest normal form you’ll see out there is 3NF (third normal form).

All the types of database normalization are cumulative – meaning each one builds on top of those beneath it. So all the concepts in 1NF also carry over to 2NF, and so on.

 

• The First Normal Form – 1NF

For a table to be in the first normal form, it must meet the following criteria:

- a single cell must not hold more than one value (atomicity)

- there must be a primary key for identification

- no duplicated rows or columns

- each column must have only one value for each row in the table

 

For example, the left table voilates the 1NF that one attriute should have only one attribute value.

철수 has two attribute values of mathmatics and science.

Splitting is the first normalization.

↓

 

• The Second Normal Form – 2NF

The 1NF only eliminates repeating groups, not redundancy. That’s why there is 2NF.

A table is said to be in 2NF if it meets the following criteria:

- it’s already in 1NF

- has no partial dependency. That is, all non-key attributes are fully dependent on a primary key.

 

For example, 

due to the overlapping data, row cannot be distinguished by student name, and age cannot be distinguished by row.

Each row can be distinguished only by  [Student Name, Course] that combined.

The problem here is that age is subordinate to the student name, so you can know the age just by knowing the student name. So, If there is a column dependent on only a specific column, it violates the secondary normal type.

 

[Student] table

[Course] table

 

• The Third Normal Form – 3NF

When a table is in 2NF, it eliminates repeating groups and redundancy, but it does not eliminate transitive partial dependency.

This means a non-prime attribute (an attribute that is not part of the candidate’s key) is dependent on another non-prime attribute. This is what the third normal form (3NF) eliminates.

So, for a table to be in 3NF, it must:

- be in 2NF

- have no transitive partial dependency.

 

For example, 

the rating is determined by the customer number and the discount rate is determined by the grade. 

As in this situation, if X->Y and Y->Z are satisfied, it called the transition function dependence.

 

reference : https://www.freecodecamp.org/news/database-normalization-1nf-2nf-3nf-table-examples/#:~:text=Database%20normalization%20is%20a%20database,insertion%2C%20deletion%2C%20and%20updating.

Database Normalization – Normal Forms 1nf 2nf 3nf Table Examples

https://mjn5027.tistory.com/46