DATABASE NORMALIZATION
Design Database Process (review)
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
· Implementation goes to database by make table for each relationship already most 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.
Why done by normalization?
• Optimizing table structures
• Increasing speed
• Removing same data inclusion
• More efficient in storage media purpose
• Reducing redundancy
• Avoiding anomaly (insertion anomalies, deletion anomalies, and anomaly's update).
• Increased data integrity
One table is said well (efficient) or normal if accomplish 3 criterions as follows:
- If there is decomposition (parsing) table, 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).
Functional Dependency
Functional Dependency figuring relationship attributes in one relationship. An attribute is said functionally dependant on the other if we utilize the attributes value to determine the other attribute value. Symbol that is utilized is 
for representing functional dependency.
Read functionally determines.
Notation: A B
A and B is attribute of one table. It means that functionally A determines B or B depend on A, if and only if available 2 rows data with same value of A, therefore value of B also same.
Notation : A
B or A x --> B are opposite of previous notation.
From the table above, we can explain that:
Functional Dependency:
• NRP name
• Subject, NRP Grade
Non Functional Dependency:
• Subject NRP
• NRP Grade
Functional Dependency from the table value
NRP Name
Because of each value of NRP that same, then the value of Name also same.
{Subject, NRP} Grade
Because of attribute Grade is depend on Subject and NRP jointly. In other mean for Subject and same NRP, therefore Grade also same, because Subject and NRP are key (get unique character)
– Subject NRP
– NRP Grade
First Normal Form - 1NF
A table is said exist on normal form I if it don't exist on unnormalized form table, where is happening multiplexing a sort field and enabling available field that null (empty).
For example College Student Data as follows:
| ID | NAME | HOBBY |
| 12020001 | Feri Wijantara | Football, reading comics, swimming |
| 12020013 | Leoni WS | Cooking, singing |
| 12020015 | Rion Saputra | tailor, making bread |
Or
| ID | NAME | HOBBY1 | HOBBY2 | HOBBY3 |
| 12020001 | Feri Wijantara | Football | reading comics | swimming |
| 12020013 | Leoni WS | Cooking | singing |
|
| 12020015 | Rion Saputra | Tailor | making bread |
|
The tables above are ineligible for 1NF. Decomposition becomes:
- Collegian table:
| ID | NAME |
| 12020001 | Feri Wijantara |
| 12020013 | Leoni WS |
| 12020015 | Rion Saputra |
- Hobby table:
| ID | HOBBY |
| 12020001 | Football |
| 12020001 | reading comics |
| 12020001 | swimming |
| 12020013 | Cooking, |
| 12020013 | singing |
| 12020015 | tailor |
| 12020015 | making bread |
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 R full dependent functionally on primary key R.
This following table accomplishes 1NF, but excluding 2NF:
| ID | Name | Address | Subject_code | Subject | Sks | Grade |
| 0608605087 | Leoni WS | Jl. Celagi Basur | DM | Data Mining | 3 | A |
| 0608605013 | Feri Wijantara | Jl. Sriwijaya | GIS | Sistem Informasi Geografis | 3 | B |
| 0608605087 | Leoni WS | Jl. Celagi Basur | GIS | Sistem Informasi Geografis | 3 | A |
| 0608605200 | Rion Saputra | Jl. Penguin | GIS | Sistem Informasi Geografis | 3 | C |
| 0608605087 | Leoni WS | Jl. Celagi Basur | MP | Metodologi Penelitian | 2 | A |
| 0608605200 | Rion Saputra | Jl. Penguin | MP | Metodologi Penelitian | 2 | B |
That table is Not accomplishing in 2NF's form, because {ID, Subject_Code} that is looked on as primary key meanwhile:
{ID, Subject_code} Name
{ID, Subject_code}Address
{ID, Subject_code} Subject
{ID, Subject_code} Sks
{ID, Subject_code} Grade
That table needs to decomposition becomes many table that measures up 2NF.
The Functional dependency as follows:
{ID, Subject_code} Grade (fd1)
ID{Name, Address} (fd2)
Subject_code {Subject, Sks} (fd3)
So:
fd1 (ID, Subject code, Grade) Appreciative table
fd2 (ID, Name, Address) Collegian table
fd3 (Subject code, Subject, Sks) Subject's table
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:
| ID | Name | Address | City | Province | PostalCode |
| 0608605087 | Leoni WS | Jl. Celagi Basur | Badung | Bali | 80978 |
| 0608605013 | Feri Wijantara | Jl. Sriwijaya | Gianyar | Bali | 80999 |
| 0608605139 | Fernindya Putri | Jl. Kura-kura | Denpasar | Bali | 81233 |
| 0608605200 | Rion Saputra | Jl. Penguin | Bangli | Bali | 80612 |
Since is still exist the attribute non primary key (namely City and Province) that have dependency to attribute non primary key another one (namely PostalCode):
PostalCode {City, Province}
So that table need to be decomposition becomes:
Collegian (ID, Name, Street, PostalCode)
PostalCode( PostalCode, Province, City)
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.
On example hereunder is exists Seminar relationship, Prime key is NPM + seminar.
Student may take one or two seminar. Each seminar needs 2 counselors and each student led by one of between 2 that seminar counselor. Each counselor just may take one seminar only. On this example NPM and seminar points out a Counselor.
Seminar Relationship
| NPM | Seminar | Counselor |
| 1000 | S100 | Leoni |
| 1001 | S100 | Feri |
| 1002 | S101 | Rion |
| 1001 | S101 | Rion |
| 1003 | S101 | Nelly |
Seminar Relationship form is third Normal Form, but not BCNF since Seminar Code is still dependent function on Counselor, if each Counselor gets to teach just one seminar. Dependent seminar on one attribute is not super key as presupposed by BCNF. Therefore Seminar relationship shall be broken down as two which is:
Counselor Relationship
| Counselor | Seminar |
| Leoni | S100 |
| Feri | S100 |
| Rion | S101 |
| Nelly | S101 |
Seminar-Counselor Relationship
| NPM | Counselor |
| 1000 | Leoni |
| 1001 | Feri |
| 1002 | Rion |
| 1001 | Rion |
| 1003 | Nelly |
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
