Normalization
Normalization is improving a table in a better way.
Most of the normalization steps consist of dividing a table. The purpose of dividing is to allocate one information(data) in one table.
Although there are 5 types of normalization, but 3 types are the most widely used.
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
First Normal Form
In relational database, one cell can store one value. Therefore, ‘주문상품’ column of the example below would look better if it is changed.
As the example above, increasing rows by separating duplicated data is called ‘the first step of first normal form’.
Table split is also conducted in first normal form. The purpose of splitting is to prevent the duplicated information. In the example, the table can be divided into two table, one for order info and the other for ordered product info.
After splitting tables, you can set a primary key. As ‘주문번호’ in ‘주문’ table is unique, you can set the column as a primary key. In ‘주문상품’ table, combining ‘주문번호’ and ‘상품코드’ will make it possible to set two columns as a primary key.
Second Normal Form
In second normal form, splitting table is a basic theme also. In here, you will split table by columns whether they are specified by a primary key or not.
In ‘주문상품’ table, ‘상품명’ can be specified by ‘상품코드’ only, without using a primary key(‘주문번호’ and ‘상품코드’). However, a primary key is essential to specify ‘개수’ column. Therefore, table can be split into two table, one for a product info and the other for ordered product info.
Second normal form is finding partial functional dependency and splitting table.
Third Normal Form
In third normal form, check whether there are duplicated part except a primary key.
Duplicated customer information can be split as above.
The following is a final result and its ER diagram.
Through normalization, you can store one information in only one table. It will make it easy to update and manage database.
All images, except those with separate source indications, are excerpted from lecture materials.
댓글남기기