Database Normalization
Normalization process is the establishment of the database structure so that most of the ambiguity can be removed. Normalization stage, starting from the most mild (1NF) to most stringent (5NF) usually only reach the level of 3NF or BCNF because already sufficient to generate the table-a table of good quality.
Why do normalization?
1. Optimizing table structures
2. Increase speed
3. The income data is the same
4. More efficient of storage media
5. Reduce redundancy
6. Avoid anomalies (insertion anomalies, deletion anomalies, update anomalies).
7. Improved data integrity
A table saying good (efficient) or if normal meet 3 criteria:
1. If there is decomposition (decomposition) table, it must be guaranteed safe decomposition (Lossless-Join Decomposition). That is, after the table is described for a new table-table, the table-table can generate a new table with the same exact.
2. Maintain dependence on the functional changes in data (Dependency preservation).
3. Does not violate Boyce-Code Normal Form (BCNF)
If the three criteria (BCNF) can not be met, then at least the table does not violate the Normal Form of the third stage (3rd Normal Form / 3NF).
Functional Dependency
Functional Dependency attributes describe the relationship in a relationship. An attribute said functionally dependant on the other, if we use the value attribute to determine the value of the other attributes.
- The symbols is used to represent the functional dependency is →
→ read determine the functional
- notation: A → B
A and B are attributes of a table. A means of determining the functional B or B depends on A, if and only if there are 2 rows of data with the same value of A, then B is also the same value
- notation: A x→B
It is the opposite of the previous notation.
Example:
Functional Dependency:
1. NRP → Nama
2. Mata_Kuliah, NRP → Nilai
Non Functional Dependency:
1. Mata_Kuliah → NRP
2. NRP → Nilai
Functional Dependency of the table nilai
– Nrp → Nama
Because for each value Nrp the same, then the value of the same name
– (Mata_kuliah, NRP) → Nilai
Because the value of attributes depending on the NRP and Mata_kuliah together. In another sense Mata_kuliah for the NRP and the same, they also rated the same, because Mata_kuliah and the NRP is a key (is unique).
- Mata_kuliah x→ NRP
- NRP x→ Nilai
FIRST NORMAL FORM – 1NF
A table on the form said to be normal if I did not reside in the unnormalized form of a table, where there is a kind of field multiplication and field that allows a null (empty)
What is not the 1NF:
• Attribute values, many (Multivalue attributes).
• Attribute a composite or a combination of both.
So:
• Price is the domain attribute must be atomic rates
• Ex Student Data as follows:
Or
Tables above does not meet 1NF. So decomposition can be:
- Student Table:
- Hobbies Table:
Another example:
SECOND NORMAL FORM – 2NF
Normal form 2NF met in a table if it meets the form of 1NF, and all the attributes than the primary key, have a full Functional Dependency on primary key. A table does not meet 2NF, if there are attributes that Functional Dependency are only partial (only depending on the part of the primary key). If there are attributes that have no dependence on the primary key, then the attributes must be moved or removed. Y is full if it is said to delete anà• functional dependence X attribute A from X means that Y is no longer dependent functional. Y said if deleting a partial attribute A from X means that Y is functionally dependent.à• functional dependence X. The scheme in the form of the relation R 2NF if every non-primary key attribute A Î R depend on the full primary key functional R.
The following table meet 1NF, but not 2NF
Does not meet 2NF, 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 needs to be some table decomposition eligible 2NF. So dependency functional as follows:
1. (NIM, KodeMk) → NilaiHuruf (fd1)
2. NIM → {NamaMhs, Alamat} (fd2)
3. KodeMk → {Matakuliah, Sks} (fd3)
So that:
1. fd1 (NIM, KodeMk, NilaiHuruf) → Nilai Table
2. fd2 (NIM, NamaMhs, Alamat) → Mahasiswa Table
3. fd3 (KodeMk, Matakuliah, SKS) → MataKuliah Table
Another example:
Third NORMAL FORM – 3NF
Normal form 3NF fulfilled if the form meets 2NF, and if there are no non-primary key attribute that has a dependence on non-primary key attributes of the other (transitive dependencies). Table following students eligible 2NF, but not 3NF.
Because there are non-primary key attribute (Kota and Provinsi), which has a dependence on non-primary key attributes of the other (KodePos):
KodePos → (Kota and Provinsi)
So that the table should be decomposition to :
- Mahasiswa (NIM, NamaMhs, Jalan, KodePos)
- KodePos (KodePos, Provinsi, Kota)
Another example:
Boyce-CODD NORMAL FORM (BCNF)
Boyce-Codd Normal Form constraint has a stronger form of the Normal third. To be BNCF, relations must be in the form of 1NF and forced each of the attributes depends on the function in the super key attributes. In the example below there is a relationship 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 show a Seminar Counsellor.
Relations Seminar is a form of Third Normal, but not BCNF because Code Seminar is still dependent on the function Counsellor, if any Counsellor can only teach a seminar. Seminar depend on one key attribute is not as super key. But relations Seminar should be parsed into two namely:
NORMAL FORM fourth and fifth(4NF adn 5Nf)
• Relations in fourth normal form (NF 4) if the relation in BCNF and dependency not contain many 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.
WHAT WE CAN LEARN ?
• 1NF:
- Omission multivalue attribute.
- Atomic domain.
• 2NF:
– All attribute than the primary key must have the functional dependency of whole (not partial) to the primary key.
• 3NF:
– No non-attribute primary key attribute to have the dependency of the non-primary key other.
