DATABASE NORMALIZATION

Design Database Process (review)

·Gathering user or business's requirement

· Develop a needs-based ER Model user / business

· Convert E-R Model go to relationship gatherings (table)

· Implementation goes to database by make table for each relationship already most normalization

Database Normalization

Normalization is a database structure making process so most of the ambiguity can be cleared. Normalization phase start at the lowest phase (1NF) till the highest phase (5NF). Why done a normalization?

• Optimizing table structures.
• Improve speed.
• Remove same data.
• More efficient in usage of storage media.
• Lessen redundancy.
• Avoid anomalies ( anomalies insertion, anomalies deletion, update anomalies).
• Improved Data integrity.

Functional Dependency

Functional dependency (FD) is a restriction that comes from the meaning of attributes and relationships between attributes. Functional Dependency attributes describe the relationship in a relationship.

Symbol that is utilized is --> for representing functional dependency.
-->Read functionally determines.

Notation: A -->B

A and B is attribute of one table. It means that functionally A determines B or B depend on A, if and only if available 2 rows data with same value of A, therefore value of B also same.

Notation: A -/-> B or A x -->B are opposite of previous notation.

Functional Dependency:

NRP--> Nama
Mata Kuliah, NRP--> Nilai

Non Functional Dependency :
Mata Kuliah --> NRP
NRP --> Nilai
Functional Dependency from table nilai :

NRP --> Nama
Because for every same NRP value, so Nama value also the same

{Mata_Kuliah, NRP} --> Nilai
Because attribute Nilai depends on Mata_Kuliah and NRP all together. In other meaning, for every same value of Mata_Kuliah and NRP, so Nilai is also the same, because Mata_Kuliah and NRP are the key (unique)

First Normal Form - 1NF

A table is said exist on normal form I if it don't exist on unnormalized form table, where is happening multiplexing a sort field and enabling available field that null (empty).

The example College Student Data as follows:

Or

The tables above are ineligible for 1NF. Decomposition becomes:

- Ccollegian table:

- Hhobby table:

Second Normal Form - 2NF

Second Normal Form 2NF is satisfied in a table if it has satisfies the 1NF, and all attribute except primary key, integratedly have functional dependency in primary key. A table doesn't meet 2NF, if there're attributes which its dependency (Functional Dependency) only being partial (only dependant in half of the primary key). If there're attributes which doesn't have dependency with primary key, so that attribute must be removed. Functional Dependency X --> Y was said to be complete if deleting some attribute A from X means Y not anymore functional dependant. Functional Dependency X -->Y was said to be partial if deleting some attribute A from X means Y still functional dependant. Relation schema R in form of 2NF if every attribute non primary key A є R full dependant functionally in primary key R.

This following table accomplishes 1NF, but excluding 2NF

That table is Not accomplishing in 2NF's form, because {ID, Subject_Code} that is looked on as primary key meanwhile:

{ID, Subject_code} -->Name

{ID, Subject_code}-->Address

{ID, Subject_code} -->Subject

{ID, Subject_code} -->Sks

{ID, Subject_code} -->Grade

That table needs to decomposition becomes many table that measures up 2NF.

The Functional dependency as follows:

{ID, Subject_code} -->Grade (fd1)

ID-->{Name, Address} (fd2)

Subject_code -->{Subject, Sks} (fd3)

So:

fd1 (ID, Subject code, Grade) -->Appreciative table

fd2 (ID, Name, Address) --> Collegian table

fd3 (Subject code, Subject, Sks) -->Subject's table

Third Normal Form 3NF

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 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:

4NF and 5NF

1. Relation in 4NF, if relation in BCNF and not dependence with many value. For losed the dependence with many value from one relation, we must divided the relation become 2 new relation. That relation, contains 2 attribute . They have many value respetively.

2. Relation in 5NF with propertis, that is join without lossless join. 5NF referred as also PJNF (Projection join normal form). This case rarely appear and it is difficult to is detected with practical.

References:
1. Agus Sanjaya ER, S.Kom, M.Kom, slide presentation : Database and ER-Diagram