Database Normalization

I. Process Design Database (review)

In Process Design Database there are:

1. Collect requirement of user / business
2. Develop E-R Model based requirement of user / business
3. Convert E-R Model to relationship gathering ( tables)
4. Relationship normalization to eliminated or removed anomaly
5. Implementation goes to database by make table for each relationship already most normalization
   

II. Database Normalization

In the field of relational database design, normalization is a systematic way of ensuring that a database structure is suitable for general-purpose querying and free of certain undesirable characteristics—insertion, update, and deletion anomalies—that could lead to a loss of data integrity.
Normalization phase started from lightest phase ( 1NF) until tightest ( 5NF). Usually only coming up with level 3NF or BCNF because have adequate enough to yield the tables of which with quality goodness.

Why done by normalization?

• Optimizing table structures.

• Improve speed.

• Remove same data.

• More efficient in usage of storage media.

• Lessen redundancy.

• Avoid anomalies ( anomalies insertion, anomalies deletion, update anomalies).

• Improved Data integrity.

III. Functional Dependency

A functional dependency (FD) is a constraint between two sets of attributes in a relation from a database.
Given a relation R, a set of attributes X in R is said to functionally determine another attribute Y, also in R, (written X --> Y) if and only if each X value is associated with precisely one Y value. Customarily we call X the determinant set and Y the dependent attribute. Thus, given a tuple and the values of the attributes in X, one can determine the corresponding value of the Y attribute. For the purposes of simplicity, given that X and Y are sets of attributes in R, X --> Y denotes that X functionally determines each of the members of Y - in this case Y is known as the dependent set. Thus, a candidate key is a minimal set of attributes that functionally determine all of the attributes in a relation.

Example :



Functional Dependency:

• NRP--> Nama

• Mata_Kuliah, NRP --> Nilai

Non Functional Dependency:

• Mata_Kuliah --> NRP

• NRP --> Nilai

Functional Dependency from tables of Nilai:

Nrp --> Nama
Because to each Nrp value is same, so Name value is same too.

{Mata_Kuliah, NRP} --> Nilai
Because attribut value depend on Mata_Kuliah and NRP by together. In other meaning for the Mata_Kuliah and NRP is same, so have same Nilai too, because Mata_Kuliah and NRP is key ( having the character is unique).

Mata_Kuliah --> NRP
NRP --> nilai


IV. First Normal Form / 1NF

First normal form (1NF or Minimal Form) is a normal form used in database normalization. A relational database table that adheres to 1NF is one that meets a certain minimum set of criteria. These criteria are basically concerned with ensuring that the table is a faithful representation of a relation and that it is free of repeating groups.
The concept of a "repeating group" is, however, understood in different ways by different theorists. As a consequence, there is no universal agreement as to which features would disqualify a table from being in 1NF.

Not allowed for :

• Attribute that valuably there are many ( Multi valued attribute )

• Attribute composite or combine of both

So :

• Price of Domain attribute must represent atomic price

For Example
Table "Mahasiswa"



Or



That Tables is not complete 1NF condition.
Decomposition becomes:

Table mahasiswa :


Table hoby :



V. Second Normal Form / 2NF

Second normal form (2NF) is a normal form used in database normalization. 2NF was originally defined by E.F. Codd in 1971. A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a 1NF table is in 2NF if and only if, given any candidate key and any attribute that is not a constituent of a candidate key, the non-key attribute depends upon the whole of the candidate key rather than just a part of it.
In slightly more formal terms: a 1NF table is in 2NF if and only if none of its non-prime attributes are functionally dependent on a part (proper subset) of a candidate key. (A non-prime attribute is one that does not belong to any candidate key.)

Functional dependency X --> Y is said full if erase 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 schemer on 2NF form if each attribute non primary key A R full dependent functionally on primary key R.

This table accomplishes 1NF, but not exclude 2NF :




That table is Not accomplishing 2NF form, because (NIM, KodeMk) is regarded as the primary key:

{NIM, KodeMk} -> NamaMhs
{NIM, KodeMk} -> Alamat
{NIM, KodeMk} -> Matakuliah
{NIM, KodeMk} -> SKS
{NIM, KodeMk} -> NilaiHuruf

Table need to decomposition become some table measures 2NF

Functional dependency as follows:

• {NIM, KodeMk} --> NilaiHuruf (fd1)

• NIM --> {NamaMhs, Alamat} (fd2)

• KodeMk --> {Matakuliah, Sks} (fd3)

So

• fd1 (NIM, KodeMk, NilaiHuruf) --> Tabel Nilai

• fd2 (NIM, NamaMhs, Alamat) --> Tabel Mahasiswa

• fd3 (KodeMk, Matakuliah, Sks) --> Tabel MataKuliah

VI. Third Normal Form

The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E.F. Codd in 1971. Codd's definition states that a table is in 3NF if and only if both of the following conditions hold:

1. The relation R (table) is in second normal form (2NF)
2. Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on every key of R.
   

An example of a 2NF table that fails to meet the requirements of 3NF is:


Because still there are attribute not primary key (namely Kota and Provinsi) own depended to attribute not other primary key (name is Kodepos) :

• Kodepos --> {Town, Provinsi }

So that the table require to decomposition become :

• Mahasiswa ( NIM, Namamhs, Jalan, Kodepos)

• Kodepos ( Kodepos, Provinsi, Town)

VII. Boyce-Codd Normal Form / BCNF

Boyce-Codd normal form (or BCNF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). A table is in Boyce-Codd normal form if and only if, for every one of its non-trivial functional dependencies X --> Y, X is a superkey—that is, X is either a candidate key or a superset thereof.


VIII. Fourth and Fifth Normal Form

Relationship in fourth normal form (4NF) if relationship in BCNF and not contains 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 have multi value relationship.

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

Artikel Terkait