Database Normalization

Database Normalization

Process Design Database (review)
In Process Design Database there are:
Collect requirement of user / business. Develop E-R Model based requirement of user / business. Convert E-R Model to relationship gathering ( tables). Relationship normalization to eliminated or removed anomaly. Implementation to database by made table to each relationship which normalization.


Database Normalization
Normalization is process forming of database structure so most ambiguity can be eliminated, in other mean normalization is the process of efficiently organizing data in a database. 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 a 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.

Functional Dependency
The concept of functional dependency (also known as normalization was introduced by professor Codd in 1970 when he defined the first three normal forms (first, second and third normal forms).
A table said goodness (efficient) or normal if fulfilling 3 criteria, that is:

• If there is decomposition of table, so it had to be secured (Lossless-Join Decomposition). It's mean, after the tables elaborated become new tables, the new tables can result first tables with same precisely.
• Its preserve dependency functional at the moment data change (Dependency Preservation).
• Don't break Boyce-Code Normal Form (BCNF)
  

If third criterion (BCNF) cannot complete, so at least the tables don't break (3rd Normal Form / 3NF).

Functional Dependency describe a relation of attributes in a relationship. An attribute said by functionally dependant at other if us use the attribute value to determine other attribute value.
Symbol used is --> for representing dependency functional.
--> read functional determine.

Notation: A --> B
A and B are attribute from a table. it's mean functionally A determine B or of B depend on A, if and only if there is 2 data line with A value is same, so B value is same also.

Notation: A --> B or A x--> B
is reverse from previous notation.

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


First Normal Form - 1NF
An table said located in normal form 1 if its do not reside in form of unnormalized table, where happened duplication of field which of a kind and enable there is field null (empty)

Don't permitted existence of:

1. Much valuable attribute (Multi value attribute).
2. Composite attribute or combination from both

Become:
Price of Domain attribute must represent atomic price.

For example table Mahasiswa :










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

















Second Normal Form - 2NF
Normal form 2NF fulfilled in a table if have fulfilled form 1NF, and all attribute beside primary key, and have Functional Dependency at key primary. A table do not fulfill 2NF, if there is attribute (Functional Dependency) only have character partial only ( only depend on some from key primary. If there are attribute which do not have depended to key primary, so the attribute have to be moved or eliminated.
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













Third Normal Form - 3NF
3NF normal form is fulfilled if have fulfilled 2NF 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:






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)


Boyce-Codd Normal Form (BNCF)
Boyce-Codd normal form (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. BCNF was developed in 1974 by Raymond F. Boyce and Edgar F. Codd to address certain types of anomaly not dealt with by 3NF as originally defined.
In the example below there is a relationship of seminar, is the Primary Key NPM + Seminar.
Students may take one or two seminars. Each seminar requires 2 each of the students and led by one of the 2 seminar. Each leader can only take one seminar course. NPM in this example and Seminar show a Pembimbing









Relations Seminar is a form of 3NF, but not BCNF, because Seminar Code still depend the function on the Pembimbing, if any Pembimbing can teach a seminar only. Depending on the seminar is not a super key attributes such as required by BCNF.
So
Seminar relations must be parsed into two tables:









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.

Reference:
Agus Sanjaya ER, S.Kom, M.Kom, slide presentation : Normalization