NORMALIZATION-QUIZ SEVENTH

THE ERD:

Normalization of erd:

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

Second Normal Form (2NF)

Normal form 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 said, if there are attributes that 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.

3NF (Third Normal Form)

Normal form 3NF fufilled if have fulfilled form 2NF, and otherwise there is attribute of is non key primary owning depended to attribute of is non the other key primary.

DATABASE NORMALIZATION

Database Design Process

• Gathering user or business requirement
• Developing E-R Model bases user or business requirements
• Convert 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

Database Normalization

Why done by normalization?

Optimizing is table structures

• Increasing speed
• Removing same data inclusion
• More efficient in storage media purpose
• Reducing redundancy
• Avoiding anomaly( insertion anomalies , deletion anomalies , anomalies update ).
• Increased data integrity

One table is said well (efficient) or normal if pock 3 criterions as follows:

• If there is decomposition table, therefore decomposition it shall be secured safe( Lossless Join Decomposition ). Its mean, after that table is untied / at decomposition becomes new tables, that new tables can result tables originally equally a hair breadth.
• It's petted dependable functional at the moment data change (Dependency Preservation).
• Don't breach Boyce Code Form Normal (BCNF).

If the third criterion (BCNF) can't be accomplished, therefore at least that table not breach the third phase Normal Form (3rd is Normal Form or 3NF).

Functional Dependency

• Functional Dependency to figuring attribute connection in a relation.
• A attribute is functionally dependant in other, if we used that attributes value for defined the other attributes value.
• The symbol is "->" for represent the functional dependency.
• Notation : A -> B
  A and B are attribute from a table. The mean is with functional A defined B or B depends on A, if and only if there are 2 line data with the same value A, so value B is same.

Functional Dependency:
NRP -> Nama
Mata_Kuliah, NRP -> Nilai
Non Functional Dependency:
Mata_Kuliah -> NRP
NRP -> Nilai

• Functional Dependency from table value :

NRP -> Nama

Because for every value the same NRP, so the same value is same.

{mata_kuliah,NRP} -> Nilai
Because attribute value depends on mata_kuliah and NRP together.

In the other mean for mata_kuliah and the same NRP, so the value is same, because mata_kuliah and NRP is key (unique).

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

Is not allowed there :

- Many attributes of value (Multivalued attributes).

- Attributes composite or a combination of both.

So :

- Price is the domain attribute must be atomic rates

Example the following Student Data :

Or

Table-the table above doesnt meet the requirements 1NF

Decomposition to be :

Student Table :

Hobby Table :

Second Normal Form (2NF)

Normal form 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 said, if there are attributes that 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.

• Functional dependency X --> Y if it is said of a remove attribute A from X means that Y is no longer dependent functional.
• Functional dependency X --> Y if it is said partial delete an attribute A from X means that Y is functionally dependent.
• Relation scheme R in the form 2NF if every non-primary key attribute A Î R is functionally dependent on the full primary key R.

3NF (Third Normal Form)

Normal form 3NF fufilled if have fulfilled form 2NF, and otherwise there is attribute of is non key primary owning depended to attribute of is non the other key primary.
Tables of up to standard student following 2NF, but do not fulfill 3NF :

Boyce-Codd Normal Form (BNCF)

Boyce-Codd normal form (or BCNF) is a normal form used in database normalization.
It is a slightly stronger version of the third normal form (3NF). A table is in Boyce-Codd normal form if and only if, for every one of its non-trivial functional dependencies X → Y, X is a superkey-that is, X is either a candidate key or a superset thereof.

Fourth and fifth Normal Form

Relationship in fourth normal form (4NF) if relationship in BCNF and not contains multi value dependency. To remove multi value dependency from one relationship, we are divide relationship become two new relationships. Each relationship contains of two attributes have multi value relationship.

Relationship in fifth normal form (5NF) get business with property is calling join without marks sense information loss (lossless join). The fifth normal Form (5 NF) also know as PJNF (projection join normal form). This case is very rare to appearance and hard to detect practically.

Refer:
Agus Sanjaya ER, S.Kom, M.Kom, slide presentation : Normalization