Normalization

Normalization

Database Plan Process (Review)
• Gather user/business need
• Develop e-r model based on user/business need
• Conversion e-r 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 is data base structure formation process so that a large part of ambiguity can be caused.
• Normalization step is begun from lightest step (1nf) up to tightest (5nf)
• Usually only until 3nf level or bcnf because that is enough proper to produce tables that have a 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:
o If there table decomposition, so decomposition must be guaranteed safe (lossless-join decomposition). That's mean, after table elaborated / decomposited be the new tables,the new tables can produce the first table with same exactly.
o Functional dependence when data change (dependency preservation) was kept.
o Doesn't break Boyce-Code Normal Form (BCNF)
• If third criteria (BCNF) can not be fulfilled, so at least that table doesn't break normal form 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.
• Symbol used is (→) to deputize the functional dependency
• Notation: A → B
“A” And “ B” is attribute from a tables. Mean functionally A determine the B or B of depend on A, if and only if there is 2 data line with the same value A, hence assess the B also is of equal
• Notation: A /→ B Or A x→ B
Is reverse from previous notation.

Functional Dependency (FD)
• 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.
• Simbol that used is  for representing functional dependency.
 Read : functionally to determine
Functional Dependency from tables of value
• Nrp -> Name
Because for each value Nrp the same, then the value of the same name
• (Mata_kuliah, NRP) -> Value
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

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:
o Attribute that have multivalue (multivalued attribute).
o Composite attribute or combination of both.
• So:
o Value of domain attribute must be atomic value
Second Normal Form - 2NF
Normal form 2NF fullfiled in a tables of if have fulfilled the form 1NF, and all attribute of besides primary key, intactly own the Functional Dependency of at primary key. A tables do not fulfill 2NF, if there is attribute which its depending only have the character of the just just parsial ( only depend on some of primary key) . If there are attribute which do not own the depending to primary key, hence the attribute have to be moved or eliminated.
- Functional depending of X → Y told by full of if vanishing a attribute A from X mean the Y shall no longger hinge functional.
- Functional depending of X →Y told by partial of if vanishing a attribute A from X mean the Y still hinge functional.
- Scheme of Relationship R in the form of 2NF if each every attribute of is non primary key A € R hinge full of by fungsional at primary key R.
- Tables in the following is fulfilling 1NF, but [do] not the inclusive of 2NF :
{NIM, KodeMk} → NameMhs
{NIM, KodeMk} → Address
{NIM, KodeMk} → Matakuliah
{NIM, KodeMk} → Sks
{NIM, KodeMk} → LetterValue
- The tables require to decomposition become some up to standard tables 2NF

• Functional dependency:
{NIM, KodeMk} → LetterValue (fd1)
NIM → {NameMhs, Address} (fd2)
KodeMk → {Matakuliah, Sks} (fd3)
• becoming :
fd1 (NIM, KodeMk, LetterValue) → Tables Value
fd2 (NIM, NameMhs, Address) → Tables of student
fd3 (KodeMk, Matakuliah, Sks) → Tables MataKuliah
Third Normal Form - 3NF
Normal form 3NF fullfiled by if have fulfilled the form 2NF, and otherwise there is attribute of is non primary key owning depending to attribute of is non primary key the other ( depending transitif)
• So that the tables require to didekomposisi become.:
• Student (NIM, NameMhs, Jalan, KodePos)
KodePos (KodePos, Provinsi, Town)

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 To one 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 the seminar.

Relations Seminar is a form of Third Normal, but not BCNF. Seminar Code because the function depends on the teach, if any teach can only teach a seminar. Depending on the seminar isn’t a super key attributes such as required by BCNF.
Fourth normal form and to five
• Relationship in shaped fourth normal (4 NF) if relationship in BCNF and tdak contains dependency a lot of point. To remove dependency there are many point of one relationship, we divide relationship become two new relationships. Each relationship contains two attributes that have relationship a lot of points.
• Relationship in shaped normal to five (5NF) get business with property is join the so called without marks sense information loss (lossless join). Form normaling to five (5 NF also so-called PJNF (projection join is form's normal). This case very rare appearance and hard to be detected practical ala.

References:
Er Ngurah Agus Sanjaya. Slide Part 6 - Normalisasi.