Database Normalization
Normalization is required because of the redundancy relations, not good relations. The main reason is the possibility of "update anomalies" (when the insert, delete, update) because can impact on the data inconsistencies, The reason is other waste storage space (hard disk),Normalization process is the establishment of database structure so the ambiguity can be removed. 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 needed?
- Optimization table structures
- Increase the speed
- Eliminate income data the same
- More efficient use of storage media
- Reduce redundancy
- Avoiding anomalies (insertion anomalies, deletion anomalies, update anomalies).
- Improved data integrity
Criteria database tabel
- If there is decomposition table, it must be guaranteed safe decomposition it. That is, after the table is described / decompositioned a new table-table, the table-table can generate a new table with the same exact.
- Maintain dependence on the functional changes in data (Dependency preservation).
- 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 (FD)
- Functional dependency (abbreviated FD) is a restriction that comes from the meaning of attributes and relationships between attributes.
- 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.
- Symbols used are to represent the functional dependency.
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 ->/ B or A x-> B
It is the opposite of the previous notation
Example
Functional Dependency:
NRP -> Nama
Mata_Kuliah, NRP -> Nilai
Non Functional Dependency:
Mata_Kuliah -> NRP
NRP -> Nilai
- Functional Dependency from the value table :
NRP -> Nama
- Because for each value NRP the same, then the value of the same nama.
{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 ->/ NRP
NRP ->/ value
Normal Form
Normal form is a condition (using the FD and key) that determines whether a scheme relationships meet certain criteria. There are several normal forms based on a number of criteria:
- Primary keys (1NF, 2NF, 3NF)
- All Candidate Keys (2NF, 3NF, BCNF)
- Multivalued dependencies (4NF)
- Join dependencies (5NF)
1NF (First Normal Form)
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) 1NF is not allowed on the:
- Attribute values, many (Multivalued attributes).
- Attribute a composite or a combination of both.
- nested relations.
for more details I give you example
- the tables above does not meet the requirements 1NF.
- Decomposition becomes:
mahasiswa tabel
hobi tabel
2NF (Second Normal Form)
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 said, 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.
- Functional dependence X -> Y , said full if it is said to delete an attribute A from X means that Y is no longer dependent functional.
- Functional dependence X -> Y , said partial if a delete attribute A from X means that Y is functionally dependent.
- Relation scheme R in the form 2NF if every non-primary key attribute A Î R depend on the full functional primary key R.
Example
Does not meet 2NF, because (NIM, CodeMk) is regarded as the primary key whereas:
{NIM, KodeMk} > NamaMhs
{NIM, KodeMk} > Alamat
{NIM, KodeMk} > MataKuliah
{NIM, KodeMk} > Sks
{NIM, KodeMk} > Nilai
The table needs to decomposition to become some table eligible 2NF.
Functional dependency it as follows:
fd1 {NIM, KodeMk} > Nilai
fd2 NIM > {NamaMhs, Alamat}
fd3 KodeMk > {MataKuliah, Sks}
So that :
fd1 (NIM, KodeMk, Nilai) > Table of Nilai
fd2 (NIM, NamaMhs, Alamat) > Table of Mahasiswa
fd3 (KodeMk, MataKuliah, Sks) à Table of MataKuliah
3NF (Third Normal Form)
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).
Example
Table following students eligible 2NF, 3NF, but does not meet
Because the table above there are still non-primary key attribute (ie, Kota and Provinsi), which has a dependence on non-primary key attributes of the other (ie KodePos), namely:
KodePos > {Kota, Provinsi}
So that the table in the decomposition needs to 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 third Normal. To be BNCF, relations must be in the form of first Normal and forced each of the attributes depends on the function in the super key attributes.
In the example below there is a relationship seminar, Primary Key is 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 the Seminar show a Preceptor.
Seminar Relationship
Example
Seminar relations is a Third Normal Form, but not BCNF because Code Seminar is still dependent on the function Pembimbing, if any Pembimbing can only teach a seminar. Depending on the seminar is not a super key attributes such as required by BCNF. But relations Seminar should be parsed into two namely:
Relations Seminar is a form of Third Normal, but not BCNF Seminar Code because the function depends on the preceptor, if every preceptor can only teach a seminar. Depending on the seminar is not a super key attributes such as required by BCNF. But relations Seminar should be parsed into two namely :
4 NF and 5 NF (Normal Form of the 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.
