NORMALIZATION
Database Plan Process (Review)
- Gather user/business need
- Develop ER model based on user/business need
- Conversion ER model to relation collection (table)
- Relation normalization to cause the loss of anomaly
- Implementation to database with make table for every relation that normalization
Data Base Normalization
Normalization process is the establishment of the database structure so that most of the ambiguity can be removed. Normalization process of decomposition is also a relationship that is still “bad” with break the attributes to build some relationships. Normalization stage, starting from the most mild (1NF) to most stringent (5NF). Normalization is usually only up to the level of 3NF or BCNF because already sufficient to generate the table-a table of good quality.
Why normalization done?
o Table structures optimalization
o Increase speed
o Cause the loss of the same data entering
o Efficienter in storage media use
o Decrease redundancy
o Avoid anomaly (insertion anomalies, deletion anomalies, update anomalies).
o Data integrity that increased
- A table is said good (efficient) or normal if fulfil 3 criterias as follows:
- If there is decomposition (decomposition) table, then the decomposition will be guaranteed safe (Lossless-Join Decomposition). That is, after the table is described / in the decomposition into a new table-table, the table-table can generate a new table with the same exact.
- Functional dependence when data change (dependency preservation) was kept.
- No violate Boyce-Code Normal Form (BCNF)
- If the third criteria (BCNF) can not be fulfilled, then at least the table does not violate the Normal Form of the third stage (3rd Normal Form / 3NF).
Functional Dependency
- Functional Dependency describe a attributes connection in a relation
- A attribute said functionally dependant in other attribute if we use attribute value to determine other attribute value.
- Symbol that used is à for representing functional dependency.
§ Read : functionally to determine
Notation: A à B
A and B attribute from a table. That’s mean functionally A determine B or B depend on A, if and only if there 2 data lines which have a same value with A value , so B value also same.
Notation: A
B or A à B
Is the contrary from previous notation.
Example :
Functional Dependency:
- NRP à Nama
- Mata_Kuliah, NRPà Nilai
Non Functional Dependency:
- Mata_Kuliah à NRP
- NRP à Nilai
- Functional Dependency From Value Table
- Nrp à Nama
- because for every same Nrp value , so also same Nama value
- {Mata_kuliah, Nrp } à Value
- because Value attribute depend on Mata_kuliah and Nrp according to together. In other meaning for Mata_kuliah and same Nrp , so Value also same , because Mata_kuliah and Nrp is key (has unique).
- Mata_kuliah Nrp
- Nrp Value
First Normal Form – 1NF
- A table is said present in normal form I if it doesn’t present in form unnormalized table, where happen multiplication of a kind field and there possible field that null (empty)
- Forbidden of existence:
- Attribute that have multivalue (multivalued attribute).
- Composite attribute or combination of both.
- So:
- Value of domain attribute must be atomic value
Example 1:
Student university data example as follows:
Or
- Tables above doesn’t up to standard in 1NF
Example 2 :
dekomposition be:
student university table:
Hobby table :
Second Normal Form – 2NF
- Normal form 2NF has been fulfilled in a table if form 1NF has been fulfilled, and all attributes besides primary key, according to intact has functional dependency in primary key
- A table doesn’t fulfil 2nf, if there is an attribute the dependence (functional dependency) has only partial (only depend on some of primary key)
- If found attribute doesn’t has dependence towards primary key, so the attribute must moved or caused
- Functional dependence X à Y is said full if wipe off a A attribute from X that’s mean Y not again depend on functional.
- Functional dependence X à Y is said partial if wipe off a A attribute from X that’s mean Y still to depend on functional.
- R relation scheme in the form of 2NF if every attribute non primary key A Î R depend on full according to fungsional in R primary key.
Example 1 :
Table follows to fulfil 1NF, but doesn’t belong 2NF :
Example 2 :
- Doesn’t fulfil 2NF, because {NIM, KodeMk that assumed as primary key but:
- {NIM, KodeMk}à NamaMhs
- {NIM, KodeMk} à Alamat
- {NIM, KodeMk} à Matakuliah
- {NIM, KodeMk} à Sks
- {NIM, KodeMk} à NilaiHuruf
- That’s table necessary decompositioned be several tables that up to standard 2NF
Example 3 :
- That’s Functional dependency as follows:
- {NIM, KodeMk}à NilaiHuruf (fd1)
- NIM à {NamaMhs, Alamat} (fd2)
- KodeMk à {Matakuliah, Sks} (fd3)
- Then :
- fd1 (NIM, KodeMk, NilaiHuruf) à Tabel Nilai
- fd2 (NIM, NamaMhs, Alamat) à Tabel Mahasiswa
- fd3 (KodeMk, Matakuliah, Sks) à Tabel MataKuliah
Third Normal Form – 3NF
Normal form 3NF has been fulfilled if fulfil form 2NF, and if there is no non primary key attribute that has dependence towards non primary key attribute the other ( transitive dependence)
Example 1:
- Student university table follows up to standard 2NF, but doesn’t fulfil 3NF
- Because still found non primary key attribute (that is Kota and Provinsi) has dependence towards other non primary key attribute (that is KodePos):
kodepos à {Kota, Provinsi}
Example 2 :
- so that the table necessary decompositioned be :
- Mahasiswa (NIM, NamaMhs, Jalan, KodePos)
- KodePos (Kodepos, Provinsi, Kota)
Boyce-Codd Normal Form (BNCF)
Boyce-Codd Normal Form constraint has a stronger form of the Normal third. To be BNCF, relations must be in the form of First Normal form and forced each of the attributes depends on the function in the super key attributes.
In this example : found Seminar relation, primary key is NPM + Seminar.
Student may take one or two seminars. Every seminar need 2 guides and every student is guided by one of between 2 seminar guides. Every guide only may take one seminar. In this example is NPM and seminar shows a guide.
Seminar Relation
Seminar relation form is third normal form, but not BCNF Because seminar code stills to depend on function in guide, if every guide can teach only one seminar. Seminar depend on one attribute not super key like condition in bcnf. So
Seminar relation must devided be two that is:
Guide Relation Seminar-Guide Relation
Normal Form Fourth And Fifth
Relations in fourth normal form (4NF) if the relation in BCNF and does not contain a lot of dependence values. To remove the dependency of many values from a relation, we divide the relationship into two new relations. Each relation contains two attributes that have a lot of relationship value.
Relations in fifth normal form (5NF) deal with the property called the join without any loss of information (lossless join). Fifth normal form also called the 5 NF PJNF (projection join normal form). The case is very rare and appear difficult to detect in practice.
