24 April 2009

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

No comments:

Post a Comment