database normalization

Mei 6, 2009 at 1:14 pm (Tidak terkategori)

Process Device Database ( review )

  • Collect requirement of user / business.
  • Developing E-R Model pursuant to requirement of user / E-R business.
  • Converting E-R Model to relationship gathering.
  • Relationship normalization to eliminate anomaly.
  • Implementation to database by making table to each relationship which have normalization

Normalization Database

Normalization is process forming of database structure so that most ambiguity can be eliminated. Phase Normalization started from lightest phase ( 1NF) till tightest ( 5NF). Usually only coming up with level 3NF or BCNF because have adequate enough to yield the tables which goodness quality.

why done normalization ?

  • Optimalisation Structures of tables
  • Improving speed
  • Eliminating inclusion of same data
  • More efficient in usage of storage media
  • Lessening redudansi
  • Avoiding anomaly (insertion anomalies, deletion anomalies, update anomalies).
  • improved Data integrity

A tables told goodness ( efficient) or normal if fulfilling 3 criterion follow this

  • If there is decomposition of tables, hence its of him have to be secured ( Lossless-Join Decomposition). Its Meaning, after the tables elaborated / decomposition become new tables, the new tables can yield tables initialy same precisely
  • The looking after of depended functional at the (time) of change of data ( Dependency Preservation
  • Do not impinge Boyce-Code Normal Form ( BCNF)

If third criterion ( BCNF) cannot fulfilled, hence at least the tables do not impinge Normal Form of third phase ( 3rd is Normal Form / 3NF).

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.

gaya jak…

1

Functional Depedency ~ 4
Functional Dependency of the table value :
– Nrp -> Name
Because for each value Nrp the same, then the value of the same name
– (Mata_kuliah, NRP) -> Value
Because the value of attributes depending on the NRP and Mata_kuliah together. In another sense Mata_kuliah for the NRP and the same, they also rated the same, because Mata_kuliah and the NRP is a key (is unique).
– Mata_kuliah -//-> NRP
– NRP -//-> Value

Normal Form
1. The normal forms (abbrev. NF) of relational database theory provide criteria for determining a table’s degree of vulnerability to logical inconsistencies and anomalies. (from http://en.wikipedia.org/wiki/Database_normalization)

* First Normal Form (1NF)
A table on the form said to be normal I if it’s 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

Eg Student Data as follows:
21
or
3
the tables above does not meet the requirements 1NF

both tables are decomposition into:
Student Table:
4
Table Hobbies:
5

* Second Normal Form (2NF) ~ 1 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, if there are attributes that it’s 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

* Second Normal Form (2NF) ~ 2 – 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 e R depend on the full functional primary key R.

EXAMPLE:

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

6

the tables above does not meet 2NF, because (NIM, KodeMk) is regarded as the primary key:
{ NIM, KodeMk } -> NamaMhs
{ NIM, KodeMk } -> Address
{ NIM, KodeMk } -> Matakuliah
{ NIM, KodeMk } -> SKS
{ NIM, KodeMk } -> NilaiHuruf
Table in the decomposition needs to be some of the table is eligible 2NF

their functional dependency as follows:
– {NIM, KodeMk} -> NilaiHuruf (fd1)
– NIM -> {NamaMhs, Address} (fd2)
– KodeMk -> {Matakuliah, SKS} (fd3)
So that:
– fd1 (NIM, KodeMk, NilaiHuruf) -> Value Table
– fd2 (NIM, NamaMhs, Address) -> Table Student
– fd3 (KodeMk, Matakuliah, SKS) -> Table MataKuliah

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)

EXAMPLE:
The table following students eligible 2NF, but does not meet 3NF

7
Because there are non-primary key attribute (ie, City and Provincial), which has a dependence on non-primary key attributes of the other (ie KodePos):
KodePos -> { City, Province }

So that the table in the decomposition needs to be:
– Student (NIM, NamaMhs, Road, KodePos)
– KodePos (KodePos, 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 the form of First Normal form and forced each of the attributes depends on the function in the super key attributes.

In the example below there is a relationship of 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 Pembimbing

81
Relations Seminar is a form of Third Normal, but not BCNF, because Seminar Code still depends the function on the Pembimbing, if any Pembimbing can only teach a seminar. Depending on the seminar is not a super key attributes such as required by BCNF. So Seminar relations must be parsed into two tables:

91
Fourth Normal Form (4NF) and Fifth Normal Form (5NF)
– Relations in the fourth normal form (4NF) if the relation in BCNF and does not contain a lot of dependence 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.

Refrences
1. Agus Sanjaya ER, S.Kom, M.Kom ,presentation slide : Normalization
2. http://en.wikipedia.org/wiki/Database_normalization

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: