DATABASE NORMALIZATION

NORMALIZATION

Process Device Database (review)

- Gather user needs / business

- Develop a needs-based ER Model user / business

- Convert E-R model to the set of relations (table)

- Normalize the relations to remove anomalies

- Implemented to create a database with a table for every relationship that has been normalize

Normalization Bases Data

Normalization is process forming of data bases structure so that most ambiguity can be eliminated. Phase Normalization started from lightest phase ( 1NF) tightest till ( 5NF). Usually only up to the level of 3NF or BCNF because already sufficient to generate the table-a table of good quality.

Normalization is done to :

- Optimizing table structures

- Improving speed

- The income data is the same

- More efficient in usage of storage media

- Reduce redudancy

- Avoiding anomaly (insertion anomalies, deletion anomalies, update anomalies)

- Improved Data integrity

A table saying good (efficient) or if the normal 3 to meet the following criteria :

- If there is decomposition (decomposition) table, it must be guaranteed safe the decomposition (Lossless-Join Decomposition). That is, after the table is described a new table-table, the table-table can generate a new table with the same exact.

- The looking after of depended functional at the (time) of change of data (Dependency Preservation).

- Does not violate Boyce-Code Normal Form (BCNF).

If the three criteria (BCNF) can’t be meet, then at least the table doesnt violate the Normal Form of the third stage (3rd Normal Form / 3NF).

Functional Dependency (1)

Functional Dependency depict relation of attributes 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 the used is,for representing dependency functional, read functionally determine.

Notation : A -> B

A and B are attributes of a table. A means of determining the functional B or B depends on A, if and only if there are 2 rows of data with the same grade of A, then B is also the same grade.

Notation : A -> B or A x-> B

It is the opposite of the previous notation.

EXAMPLE :

Functional Dependency:

NRP -> Name

(Course, NRP) -> Grade

Non Functional Dependency:

Course -> NRP

NRP -> Grade

Functional Dependency From Table Grade :

NRP -> Name

Because for each value Nrp the same, then the value of the same name

(Course, NRP) -> Grade

Because the value of attributes depending on the NRP and Course together. In another sense Course for the NRP and the same, they also rated the same, because Course and the NRP is a key (a unique).

Course -> NRP

NRP -> Grade

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 meet 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, if there are attributes that dependence (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 is full if it is said to delete an attribute A from X means that Y is no longer dependent functional.

- Functional dependency X -> Y said if deleting a partial 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 depend on the full primary key functional R.

Example 1

The following table meet 1NF, 2NF, including but not

Doesnt meet 2NF, because (NIM, Course Code) is regarded as the primary key:

(NIM, Course Code) -> Student Name

{NIM, Course Code} -> Address

{NIM, Course Code} -> Course

{NIM, Course Code } -> Sks

{NIM, Course Code } -> Value of the letter

Table needs to be some table decomposition eligible 2NF

Functional dependency as follow:

– {NIM, Course Code } ->Value of the letter (fd1)

– NIM -> (Student Name, Address) (fd2)

– Course Code -> (Course, Sks) (fd3)

So :

– fd1 (NIM, Course Code, Value of the letter) -> Value Table

– fd2 (NIM, Student Name, Address) -> Student Table

– fd3 (Course Code, Course, Sks) -> Course Table

Third Normal Form (3NF) (1)

Normal form 3NF fulfilled if the form meets 2NF, and if there are no non-primary key attribute that has a dependence on non-primary key attributes of the other (transitive dependencies).

Table following students eligible 2NF, 3NF, but doent meet

Because there are non-primary key attribute (City and Provincial), which has a dependence on non-primary key attributes of the other (postal code)

Postal Code -> (City,Province)

Example 2

So that the table should be decomposition:

Student (NIM,Student Name, Road, Postal Code)

- Postal Code (Postal Code, Province, City)

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 first normalize type 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 a Seminar preceptor.

Relations Seminar is a form of Third Normal, but not BCNF Seminar Code because the function depends on the Preceptor, if any Preceptor can only teach a seminar. Seminar depend on one key attribute is not as super requirement by BCNF. So Seminar relations must be parsed into two namely:

Normal form of the fourth and fifth

- Relations in the fourth normal form (NF 4) if the relation in BCNF and dependency not contain many 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.

Refference :

Agus Sanjaya ER, S.Kom, M.Kom, Slide Part 6 - Database and ER-Diagram