NORMALIZATION

Database Design Process

There are some processes that must be done to design a database, that is:

Ø Gathering user needs / business

Ø Develop the E-R model based on user needs / business

Ø Convert E-R Model to set the relation (table)

Ø Normalization of relations, for the anomaly

Ø To implement the database for each table to create relationships that have been in the normalization

Database Normalization

Normalization process is the establishment of the database structure so that most of the ambiguity can be removed. Normalization is done at the design stage, before the database is implemented. Purpose to avoid update anomalies. To find out how to do normalization, we must first understand the concept of functional dependency and key.

The purposes normalization :

Ø Optimization table structures

Ø Increase the speed

Ø Eliminate income data the same

Ø More efficient use of storage media

Ø Reduce redundancy

Ø Avoiding anomalies (insertion anomalies, deletion anomalies, update anomalies).

Ø Improved data integrity

A table is good (efficient) if following the criteria:

Ø If there is decomposition (decomposition) table, then the decomposition will be guaranteed safe (Lossless-Join Decomposition). That is, after the table is described / in the decomposition into a new table-table, the table-table can generate a new table with the same exact.

Ø Maintain the functional dependence on the change data (Dependency preservation)

Ø No violate Boyce-Code Normal Form (BCNF)

If the three criteria (BCNF) can not be connected, at least the table does not violate the Normal Form of the third stage (3rd Normal Form / 3NF).

Why normalization is required because of the redundancy relations, not relations of good ?

That because possibility of "update anomalies" (when the insert, delete, update) because can impact on the data inconsistencies

The way to Handle anomaly are :

Ø Anomaly in the handle by programming language used to create the application database. Designer should note this anomaly and to tell a programmer.

Ø Anomaly does not handle the system, but submitted to the operator to be careful in making modifications, inserts and deletes. It is human error risk.

Ø Anomalies be avoided, with ways to secure the normalization.

Denormalization

Denormalization is a reverse process of normalization, ie, combine several relations, brought to normal form of a lower.

Functional Dependency (FD)

Functional dependency (abbreviated FD) is a restriction that comes from the meaning of attributes and relationships between attributes. Functional Dependency attributes describe the relationship in a relationship. An attribute said functionally dependant on the other, if we use the value attribute to determine the value of the other attributes.

First Normal Form - 1NF

Ø Functional Dependency dari tabel nilai

– Nrp à Nama

Karena untuk setiap nilai Nrp yang sama, maka nilai Nama juga sama

– {Mata_kuliah, NRP} à Nilai

Karena attribut Nilai tergantung pada Mata_kuliah dan NRP secara bersama-sama. Dalam arti lain untuk Mata_kuliah dan NRP yang sama, maka Nilai juga sama, karena Mata_kuliah dan NRP merupakan key (bersifat unik).

– Mata_kuliah à NRP

– NRP à Nilai

BENTUK NORMAL PERTAMA (First Normal Form - 1NF)

A table said to be normal I if it not in the unnormalizet table form, in that procces ther is same field duplication and able to happen the null field.

There is will be not :

Ø Multivalued attribute

Ø Composite attribute or combination of both.

So

Ø Domain value must be atomic value.

For example :

Or

Ø The tables above aren’t eligable 1NF

Second Normal Form - 2NF

Ø Normal form 2NF eliged in a table after to elig primary 1NF form, and all attributes beside primary key, and have Functional Dependency at primary key.

Ø A table is not eliged 2NF if there is Functional Dependency attribute.

Ø If there is a attribute that have not dependency to the primary key, so that attribute must be deleted.

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

Ø Meet all the requirements of the second normal form.

Ø Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

Ø Meet all the requirements of the third normal form.

Ø A relation is in 4NF if it has no multi-valued dependencies.
Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.

References:

1. ER Ngurah Agus Sanjaya. Slide Part 6 - NORMALISASI.
2. Siti Aminah. Slide Review_Normalisasi_untuk_Vcon_7_des.