Database Normalization

Normalization

There are several processes that must be done to design a database, namely:

1. Gathering user needs / business

2. Develop the ER model based on user needs / business

3. Converting E-R Model to relationship gathering ( of tables )

4. Relationship normalization to eliminate the anomaly

5. Implementation to database by making table to each; 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 tables which good with quality.

Normalization should be performed for the optimal structure-structure of the table, Increase speed, remove the same income data, more efficient use of storage media, Reducing redundancy, Avoiding anomalies (insertion anomalies, deletion anomalies, update anomalies), improved data integrity.

A tables told by goodness if fulfilling 3 criterion :

· If there is decomposition of is tables of, hence decomposition have to be secured ( Lossless-Join Decomposition

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

· Don’t impinge the Normal Boyce-Code of Form ( BCNF)

If the three criteria (BCNF) can’t 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.

· 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)

·

NRP -> Name

· Mata Kuliah, NRP-> Value

Non Functional Dependency:

· Mata Kuliah -> NRP

· NRP -> Value

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 (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) not allowed:

• Attribute values, many (Multivalued attributes).
• Attribute a composite or a combination of both.

So:

• Price is the domain attribute must be atomic rates

Example:

• Ex Student Data as follows:

Ex Student Data as follows:

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 l

ed 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. So seminar relations must be parsed into two namely:

Fifth and fourth Normal form

• Relations in fourth normal form (NF 4) 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.

References:

Er Ngurah Agus Sanjaya. Slide Part 6 - Normalisasi.