Normalization

Normalization

Database Design process

Gathering user / business's requirement. Develop e r Model bases user / business's requirements. Conversion e r Model goes to relationship gatherings (table). Normalization is relationship to remove anomaly. Implementation goes to database by make table for each relationship already most 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)

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

Normal Form Fourth And Fifth

Relation in the form of normal fourth (4NF) if relation in BCNF and not full multivalue dependence . To cause the loss of multivalue dependence from one relation, we divide relation be two new relations. Each full relation has two attributes that has multivalue connection.

Relation in the form of normal fifth (5NF) deal with property that called join without existence loses information (lossless join). Normal form fifth (5NF also called PJNF (Projection Join Normal Form). This case is very rare appears and difficult to detected according to practise.

References:

Er Ngurah Agus Sanjaya. Slide Part 6 - Normalisasi.