Normalization in Database

A. Normalization

Why is normalization in database design? Normal database structure, easy to do query, update database is not a hassle, it can get all the necessary information, and the saving of resources (disk space-tertiary-profit). The essence of normalization is to make an efficient database design (no repetition of data), a database containing all the resources, and the database is the data unit.

Besides the normalization needed to avoid insertion anomalies, deletion anomalies, and anomalies update. On a database that is normal, anomalous insertion can be avoided due to charging the same data on multiple tables just done enough to fill in the table and the rest will follow, as well as update and delete commands. But the tables are not normal, charging the same data in multiple tables is done one by one. This allows for input by human error (human error) so that some records which should have the same data, will have different data.

Form of normalization there are several kinds, ranging from normalization 1, normalization 2, and so on. Some cases reach the stage of normalization to more than 5. But there are also cases that only reached the stage of normalization 2 alone. Normalization of each case varies. It is tailored to the needs. If the database is normalized to a sufficient condition 2 above, it is not necessary next stage of normalization.

B. Normalization 1

First normalization focused to avoid existence of array on an attributes and remove the possibilities of data being redundant in strong entity (table that have primary key). To perform first normalization is easy enough, but must done carefully. We have to collect all entity that have primary key, then check whether there are possibilities of attributes on tables to contain list or array. If there are any possibilities, then create a additional table as shown in the example.

From table above, there is an array of data in child and phone attributes. In the future, this become a problem, as example when we have to know who is the father of Diah Ayu, then system must look for that data in a very long data set. As a result, the search take a long time to complete. It isn’t matter if the data in the table only count dozens, but if record count reached millions, it would be a long time to complete. Thus the above table need a additional table to be normalized.

After we create additional table, 2 field in data_worker table eliminated which is Name of Child and Phone.

Data which in form of array can be divided as table below

Certaintly form shown above isn’t allowed, because there are redundant of data on the same primary key attribute. In addition there is ineffective of the form of the same data over which data is Name, Sex, and address. Thus the solution to break table is the most appropriate solution.

In practice, it isn’t always break table done it more efficient in terms of storage and insertion data is much easier. But also think about the future if because of normalization done, impact on query to be slowly executed? As example, suppose a query to find out the name of the owner of the phone number 687 556. Query processing will exceed the joining process firs, between the pekerja table and alamat_pekerja table. After joining, the new searching process is executed. There are two process involved.

Two case arises from debilitating condition of normalization process. First one is the process of joining table takes a relatively long time if the data is very large. In addition, the process of joining with a large data memory can spend when the query is run. If the condition are like this, why do we need to learn about normalization? The point is, if the database involve large scale data, should be considered normalizing effect on query processing. If the data is small, normalization is an effective solution.

See Also : MySQL Installation for Database

C. Normalization 2

Second form of normalization is it has characteristic that each entity is on normalization 1 form, and each non key attribute of the entitiy has a dependcy on its primary key.

transaction_ID --> transaction date, customer_id, customer name, addreses, phone, total cost, payment status.

item_ID --> item name, production , price, type.

Attribute of transaction date, customer_id, customer name, addreses, phone, total cost, payment status have dependency toward transaction_ID. In other word,  transaction date, customer_id, customer name, addreses, phone, total cost, payment can be known from its transaction_ID. However there is possibility the transaction_ID can occure repeatedly. As example id transaksi today is between 100-5000, while id transaksi for other day is the same as 100-5000 so that is not posible if the transaction_ID is used as the primary key. Table require not only primary key which is only transaction_ID, but also transaction date. Dependence of the result form a non-primary key attribute:

transaction_ID,  transaction date --> customer_id, customer name, addreses, phone, total cost, payment status.

D. Normalization 3

There are two basic requirements for a database to be in third normal form, that is first, every entity is in first and secord normal form and the second is every non-key attribute in a table doesn't have transitif dependency to it's primary key. From the previous example we can see that table transaction has attributes having transitif dependency with its primary key. Such attribute are customer_id, customer name, addreses, phone. Because of it, table transaction will be separated into two different tables by take out attributes having transitif dependency to its primary key. The result will be :

transaction_ID, --> transaction date, total cost, payment status, customer_ID.

customer_ID --> customer name, addreses, phone

The separation result is still in normal form, that is second normal form, because  customer name and addreses, phone constantly depend on appropriate field, customer_ID.

Exercise

1. Create database design with one of these different cases: sales database, library database, database cost, database hospital and database pharmacies.
2. Normalize the created design until normal form reached and explain step by step of the it's normalization process. Give detail reason why some action must be done, for example attribute 'A' deleted because it cause redudancy, etc.