DATABASE NORMALIZATION

DATABASE NORMALIZATION

Database Normalization

Normalization is process of database structure forming so mainly part of ambiguity can be removed. Normalization phase is begun from lightest phase (1NF) until tightest (5NF). Usually, only coming up with level 3NF or BCNF because has adequate enough to yield the tables of which with quality goodness.

target executed with Normalization that is

o Optimizing table structures

o Increasing speed

o Removing same data inclusion

o More efficient in storage media purpose

o Reducing redundancy

o Avoiding anomaly (insertion anomalies, deletion anomalies, and anomaly's update).

o Increased data integrity

Design Process Database ( in evaluating to repeat)

The process includes:

· Gathering user or business's requirement

· Develop E-R Model bases user or business's requirements

· Convert E-R Model go to relationship gatherings (table)

· Relationship normalization to remove anomaly

One tables put in order is ( efficient) or normal if fulfilling 3 this the following size measure condition :

- therefore the decomposition shall be secured safe (Lossless Join Decomposition). It's mean, after that table is untied / at decomposition becomes new tables, that new tables can result original table equally exactly

- Its preserve dependency functional at the moment data change (Dependency Preservation).

Don't breach Boyce-Code Normal Form (BCNF)

- If the third criterion (BCNF) can't be accomplished, therefore at least that table not breach the third Normal Form (3rd Normal Form/ 3NF).

Depended Is Functional :

Functional Depended that is which drawing / calculating a relation of menujukkan one link. A told attribute functionally dependant esteem to determine other attribute value . used device is to delegation depended is functional of it self artribut .

First Normal Format- 1Nf is :

A tables told is on normal format there no at format do not be normalized, where happened multiplex a[n area of sort;jenis available area and enable which is empty cancelation

For example College Student Data as follows:

Or

The tables above are ineligible for 1NF. Decomposition becomes:

- Collegian table:

- Hobby table:

Second Normal Form - 2NF

2NF's normal form is accomplished in one table if have accomplished 1NF's form, and all attribute besides primary key, as whole to have Functional Dependency on primary key. One table will not accomplishes 2NF, if there is attribute which its dependency (Functional Dependency) just have partial character only (just depend on a part of primary key). If anything attribute that have no dependency for primary key, therefore that attribute have to move or is removed. Functional dependency X -->Y is said full if erasing an attribute A of X its mean that Y no longer functional dependent. Functional dependency X -->Y is said partial if erases an A's attribute of X matter Y still functional dependent. Relationship scheme r on 2NF form if each attribute non primary key A ER full dependent functionally on primary key R.

This following table accomplishes 1NF, but excluding 2NF:

Third Normal Form - 3NF

3NF's normal form is accomplished if have accomplished 2NF's form, and if no attribute non primary key that have dependency to attribute non primary key another (transitive dependency).

This Following collegian table measures up 2NF, but doesn't accomplish 3NF:

Boyce-Codd Normal Form (BNCF)

Boyce Codd Normal Form has stronger compulsion of third Normal form. To become BNCF, relationship shall in first Normal form and each attribute is forced dependent on function on attribute super key.

Fourth and fifth Normal Form

Relationship in fourth normal form (4 NF) if relationship in BCNF and not contains of multi value dependency. To remove multi value dependency from one relationship, we are divide relationship become two new relationships. Each relationship contains of two attributes that have multi value relationship.

Relationship in fifth normal form (5NF) get business with property is called join without marks sense information loss (lossless join). The fifth normal Form (5 NF also called PJNF (projection join normal form). This case is very rare to appearance and hard to be detected practically

References:
1. Agus Sanjaya ER, S.Kom, M.Kom, slide presentation : Database and ER-Diagram