NORMALIZATION

Normalization


Database design process (review)
• Gathering user needs / business
• Develop the ER model based on user needs / business
• Convert ER Model to set the relation (table)
• Normalisasikan relations, to remove anomalies
• to implement the database for each table to create relationships that are ternormalisasi

Database normalization
• normalization process is the establishment of the database structure so that most of the ambiguity can be removed.
• The normalization stage starting from the light (1NF) to most stringent (5NF)
• Usually only up to the level of 3NF or BCNF because already sufficient to generate the table-a table of good quality.

Advanced
• Why do normalization?
- 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

More
• A table said good (efficient) or if the normal 3 to meet the following criteria:
- If there is decomposition (decomposition) table, it must be guaranteed safe dekomposisinya (Lossless-Join Decomposition). That is, after the table is described / didekomposisi a new table-table, the table-table can generate a new table with the same exact.
- Maintain the functional dependence on the change data (Dependency preservation).
- No 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 symbol that is used to represent → functional dependency. → reads determine the functional

Continue
• 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.

Functional DEPENDENCY (FD)

Example

Functional Dependency:
• NRP → Name
• subject_lecture, NRP → Value

Non Functional Dependency:
• subject_lecture → NRP
• NRP → Value Functional Dependency
• Functional Dependency of the table value
- Nrp → Name Because for each value Nrp the same, then the value of the same name
- (subject_lecture, NRP) → Value Because the value of attributes depending on the NRP and subject_lecture together. In another sense subject_lecture for the NRP and the same, they also rated the same, because subject_lecture and the NRP is a key (is unique).
- subject_lecture → NRP - NRP → Value

FIRST NORMAL FORM (First Normal Form - 1NF)
A table said to be normal in the form of identity if it is not in the unnormalized form of a table, where there is a kind of field multiplication and field that allows a null (empty) Not the came by:
• Attribute values, many (Multivalued attributes).
• Attribute a composite or a combination of both.

So:
• Price is the domain attribute must be atomic rates Eg student data as follows:

Or

Table
-the table above does not meet the requirements Inf. Decomposition becomes:


Student table:

Table hobbies:

Second NORMAL FORM (Second Normal Form - 2NF)
• The form of normal 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 dependence if (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 Second NORMAL FORM (Second Normal Form - 2NF
• functional dependence X → Y said if fully remove an attribute A from X means that Y is no longer dependent functional.
• functional dependence X → Y if said partial delete an attribute A from X means that Y is functionally dependent.
• The scheme in the form of the relation R 2NF if every non-primary key attribute A € R depend on the full primary key fungsioanl R.

Example:
Table following this meet Inf, including tetafi not 2NF.
• Not meet 2NF, because (NIM, code lecture of subject) is regarded as the primary key:
(NIM, codelecture of subject) → Name Mhs (NIM, codelecture of subject) → Address (NIM, codelecture of subject) → lecture of subject (NIM, code lecture of subject) → SKS (NIM, code lecture of subject) → alfabeth of value

• The table should be a few tables compositioned eligible 2NF
• Functional if dependency as follows:
• (NIM, KodeMk) → NilaiHuruf (fd1)
• NIM → (NamaMhs, Address) (fd2)
• KodeMk → (Matakuliah, SKS) (fd3)
• So that:
• fd1 (NIM, KodeMk, NilaiHuruf) → Table Values
• fd2 (NIM, NamaMhs, Address) → Table Student
• fd3 (KodeMk, Matakuliah, SKS) → Table MataKuliah NORMAL FORM Third (Third Normal Form - 3NF)
• The form of normal 3NF has been met 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 the following students qualified 2NF, 3NF, but does not meet
• Because there are non-primary key attribute (ie, City and Provincial), which has a dependence on non-primary key attributes of the other (ie zip code):
• postcode → (City, Province)
• So that the table should be didekomposisi:
• Students (NIM, NamaMhs, Road, zip code)
- postcode(zip code, Province, City)

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 Normal first 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 of guidancers

Boyce-Codd Normal Form (BNCF) Relations Seminar is a form of Third Normal, but not BCNF Seminar Code because the function depends on the guidancers, if any guidances can only teach a seminar. Seminar depend on one key attribute is not as super disayaratakan by BCNF. But relations Seminar should be parsed into two namely:

Normal form of the fourth and fifth
• Relations in fourth normal form (4NF ) 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.