Information Systems Analysis and Design [Part-6]

DATABASE NORMALIZATION

Design Database Process (review)

The process includes:

· Gathering user or business's requirement

· Develop E-R Model bases user or business's requirements

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

· Relationship normalization to remove anomaly

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

Database Normalization

Normalization is process of database structure forming so mainly part of ambiguity can be removed. Normalization phase is begun from lightest phase (1NF) until tightest (5NF). Usually, only coming up with level 3NF or BCNF because has adequate enough to yield the tables of which with quality goodness.

Why done by normalization?

• Optimizing table structures

• Increasing speed

• Removing same data inclusion

• More efficient in storage media purpose

• Reducing redundancy

• Avoiding anomaly (insertion anomalies, deletion anomalies, and anomaly's update).

• Increased data integrity

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

- If there is decomposition (parsing) table, therefore the decomposition shall be secured safe (Lossless Join Decomposition). It’s mean, after that table is untied / at decomposition becomes new tables, that new tables can result original table equally exactly

- Its preserve dependency functional at the moment data change (Dependency Preservation).

- Don't breach Boyce-Code Normal Form (BCNF)

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

Functional Dependency

Functional Dependency figuring relationship attributes in one relationship. An attribute is said functionally dependant on the other if we utilize the attributes value to determine the other attribute value. 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 : AB or A x --> B are opposite of previous notation.

From the table above, we can explain that:

Functional Dependency:

• NRP name

• Subject, NRP Grade

Non Functional Dependency:

• Subject NRP

• NRP Grade

Functional Dependency from the table value

NRP Name

Because of each value of NRP that same, then the value of Name also same.

{Subject, NRP} Grade

Because of attribute Grade is depend on Subject and NRP jointly. In other mean for Subject and same NRP, therefore Grade also same, because Subject and NRP are key (get unique character)

– Subject NRP

– NRP Grade

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

For example College Student Data as follows:

ID	NAME	HOBBY
12020001	Feri Wijantara	Football, reading comics, swimming
12020013	Leoni WS	Cooking, singing
12020015	Rion Saputra	tailor, making bread

Or

ID	NAME	HOBBY1	HOBBY2	HOBBY3
12020001	Feri Wijantara	Football	reading comics	swimming
12020013	Leoni WS	Cooking	singing
12020015	Rion Saputra	Tailor	making bread

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

- Collegian table:

ID	NAME
12020001	Feri Wijantara
12020013	Leoni WS
12020015	Rion Saputra

- Hobby table:

ID	HOBBY
12020001	Football
12020001	reading comics
12020001	swimming
12020013	Cooking,
12020013	singing
12020015	tailor
12020015	making bread

Second Normal Form - 2NF

2NF's normal form is accomplished in one table if have accomplished 1NF's form, and all attribute besides primary key, as whole to have Functional Dependency on primary key. One table will not accomplishes 2NF, if there is attribute which its dependency (Functional Dependency) just have partial character only (just depend on a part of primary key). If anything attribute that have no dependency for primary key, therefore that attribute have to move or is removed. Functional dependency X Y is said full if erasing an attribute A of X its mean that Y no longer functional dependent. Functional dependency X Y is said partial if erases an A's attribute of X matter Y still functional dependent. Relationship scheme r on 2NF form if each attribute non primary key A R full dependent functionally on primary key R.

This following table accomplishes 1NF, but excluding 2NF:

ID	Name	Address	Subject_code	Subject	Sks	Grade
0608605087	Leoni WS	Jl. Celagi Basur	DM	Data Mining	3	A
0608605013	Feri Wijantara	Jl. Sriwijaya	GIS	Sistem Informasi Geografis	3	B
0608605087	Leoni WS	Jl. Celagi Basur	GIS	Sistem Informasi Geografis	3	A
0608605200	Rion Saputra	Jl. Penguin	GIS	Sistem Informasi Geografis	3	C
0608605087	Leoni WS	Jl. Celagi Basur	MP	Metodologi Penelitian	2	A
0608605200	Rion Saputra	Jl. Penguin	MP	Metodologi Penelitian	2	B

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

3NF's normal form is accomplished if have accomplished 2NF's form, and if no attribute non primary key that have dependency to attribute non primary key another (transitive dependency).

This Following collegian table measures up 2NF, but doesn't accomplish 3NF:

ID	Name	Address	City	Province	PostalCode
0608605087	Leoni WS	Jl. Celagi Basur	Badung	Bali	80978
0608605013	Feri Wijantara	Jl. Sriwijaya	Gianyar	Bali	80999
0608605139	Fernindya Putri	Jl. Kura-kura	Denpasar	Bali	81233
0608605200	Rion Saputra	Jl. Penguin	Bangli	Bali	80612

Since is still exist the attribute non primary key (namely City and Province) that have dependency to attribute non primary key another one (namely PostalCode):

PostalCode {City, Province}

So that table need to be decomposition becomes:

Collegian (ID, Name, Street, PostalCode)

PostalCode( PostalCode, Province, City)

Boyce-Codd Normal Form (BNCF)

Boyce Codd Normal Form has stronger compulsion of third Normal form. To become BNCF, relationship shall in first Normal form and each attribute is forced dependent on function on attribute super key.

On example hereunder is exists Seminar relationship, Prime key is NPM + seminar.

Student may take one or two seminar. Each seminar needs 2 counselors and each student led by one of between 2 that seminar counselor. Each counselor just may take one seminar only. On this example NPM and seminar points out a Counselor.

Seminar Relationship

NPM	Seminar	Counselor
1000	S100	Leoni
1001	S100	Feri
1002	S101	Rion
1001	S101	Rion
1003	S101	Nelly

Seminar Relationship form is third Normal Form, but not BCNF since Seminar Code is still dependent function on Counselor, if each Counselor gets to teach just one seminar. Dependent seminar on one attribute is not super key as presupposed by BCNF. Therefore Seminar relationship shall be broken down as two which is:

Counselor Relationship

Counselor	Seminar
Leoni	S100
Feri	S100
Rion	S101
Nelly	S101

Seminar-Counselor Relationship

NPM	Counselor
1000	Leoni
1001	Feri
1002	Rion
1001	Rion
1003	Nelly

Fourth and fifth Normal Form

Relationship in fourth normal form (4 NF) if relationship in BCNF and not contains of 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 that have multi value relationship.

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

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