DATABASE NORMALIZATION

NORMALIZATION

Process The Database Device ( Review )

· Collecting requirement user / business

· Developing E-R Model pursuant to requirement user / business

· Converting E-R Model to relationship gathering ( tables )

· Relationship normalization to eliminate the anomali

· Implementation to database by making table to every relationship is normalization

Normalization of Data Bases

· Normalization is process of forming of structure of data bases so that most ambiguity can be eliminated.

· Normalization Phase started from lightest phase ( 1NF) tightest till ( 5NF)

· Usually only come up with the storey level 3NF or BCNF of because have adequate enough to yield the tabless which good with quality.

Why is conducted normalization ?

Normalization Conducted bcause :

• Optimalisasi Structure of is tables of
• Improving speed
• Eliminating same data inclusion
• More efficient in storage media use
• Lessening redundansi
• Avoiding anomali ( insertion anomalies, deletion anomalies, update the anomalies).
• improved Data integrity

A tables told by goodness ( efisien) or normal if fulfilling 3 criterion :

· If there is decomposition of is tables of, hence decomposition have to be secured ( Lossless-Join Decomposition). Its Meaning, after the tables elaborated / decompositioni become the new tabless, the new tabless can yield the tables from the beginning with is of equal precisely.

· The looking after of functional depending at the (time) of data change ( Dependency Preservation).

· Do not impinge the Normal Boyce-Code of Form ( BCNF)

If third criterion ( BCNF) cannot be fullfiled, hence at least the tables do not impinge the Normal Form of third phase ( 3rd Normal of Form / 3NF).

Functional Dependency

Functional Dependency depict the relation attributes in a relationship

A attribute told by functionally dependant of at other if we use the the attribute value to determine the other attribute value.

Symbol used is to deputize the functional dependency read functionally determine

• 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

Example :

Functional Dependency from tables of value

• Nrp → Name

Because to each;every same value Nrp, hence assess the Name also is of equal

• { Mata_Kuliah, NRP } → Value

Because attribut Assess depend on Mata_Kuliah and NRP by together. In other dissimilar meaning for the Mata_Kuliah of same and NRP, hence Assess also is of equal, because Mata_Kuliah and NRP represent the key ( having the character of unique ).

• Mata_Kuliah ∕→ NRP
• NRP ∕→ Assess

First Normal Form - 1NF

A tables told to be at the normal form of I of if he do not be at the form of unnormalized table, where happened by the field duplication which of a kind and enable there is field which null ( empty )

Do not can is existence of :

· valuable attribute a lot of ( Multivalued Attribut).

· Composite Attribut or combination from both.

Becoming :
Price of Domain attribute have to represent the price atomic

EXAMPLE (1)

Or

• Tabless of is above ineligibility 1NF

EXAMPLE (2)

• Tables of student

• Tables of Hobby

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 ( Functional Dependency) 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 an 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 every attribute of is non primary key A Î R hinge full (of) by fungsioanl 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) (1)

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)

Normal Boyce-Codd of Form have the constraint which is stronger than Normal form third. To become the BNCF, relationship have to in the form of Normal of first and every attribute forced to base on the function of at super attribute of key.

At example of hereunder there are Seminar relationship, Lock the Primary is NPM + Seminar

Student may take one or two seminar. Every seminar require 2 counsellor and every student guided by one of among 2 the seminar counsellor. Every counsellor only may take one just just seminar. at this example of is NPM And Seminar show a Counsellor.

Form the Seminar Relationship is Normal form Third, but do] not BCNF. Because Seminar Code still hinge the function of at Counsellor, if every Counsellor can teach only one seminar. Seminar base on one attribute of non super of key such as those which disayaratakan by BCNF. Hence

Seminar relationship have to be broken to become two that is :

Fifth and fourth Normal form

Relationship in the form of fourth normal ( 4 NF) if relationship in BCNF and tdak contain the depending of a lot of value. To eliminate the depending of a lot of value from one relationship, we divide the relationship become two new relationship. Each relationship contain two attribute having relation of a lot of value.
Relationship in the form of fifth normal ( 5NF) dealing by properti is so-called join without existence of information loss ( lossless join). fifth Normal form ( 5 NF is also referred as by PJNF ( normal projection join of form). This case very rare emerge and difficult to be detected practically