>collecting requirement of users or business
>developing E-R model based on requirement of users or business
>converting E-R model to collection of relation (table)
>normalization relatio for eliminating anomaly
>implementation of database with creating table for each relation that had normalization
database normalization
>normalization is forming process of database structure so most of ambiguity can be eliminating
>normalization stage started from lighter stage (1NF) to tighter stage(5NF)
>usually, only reaching at level 3NF or BCNF because its quite for producting tables in good quality
>why we do normalization?
>>>optimalizing of table structures
>>>increasing speed
>>>eliminating of the same input data
>>>more efficient in using storage
>>>decreasing redundancy
>>>avoid anomaly (insertion anomalies, deletion anomalies, update anomalies).
>>>increase data integrity
>a table told great (efficient) or normal if fulfilling 3 criteria, that is :
1. if there are decomposition table, so decomposition must guaranteed to be safe (Lossless-Join Decomposition). its mean, after the table was elaborated become new tables, that tale can relsulting the same previously table
2. looking after to depended of functional at data changes (Dependency Preservation).
3. not break the Boyce-Code Normal Form (BCNF)
>if third criteria (BCNF) cannot complete, at least the table ot break normal form third stage (3rd Normal Form/ 3NF)
functional dependency
>described connection of attributes in a relation
>an attribute told as functionally dependant inother if we using attribute value for determine another attribute value
>symsbol that used is for deputizing functional dependency that read as functional to determine.
>notation : A -> B
>>>A and B is attribute from a table. its means as functional A determine B or B depended in A, if and as if there are 2 row data with same A value, so B value can be same
>notation : A-/>B or Ax->B
>>>it is reverse from previously notatioen.
>example:
>>Functional Dependency:
>>>NRP -> Nama
>>>Mata_Kuliah, NRP -> Nilai
>>Non Functional Dependency:
>>>Mata_Kuliah -> NRP
>>>NRP -> Nilai
>functional dependency from value table is
>>>Nrp -> Nama, because for each value of Nrp is same, so name value same too
>>>{Mata_kuliah, NRP} -> Nilai, because value attribute depend on Mata_kuliah and Nrp as at same time. in other mean, for Mata_kuliah and Nrp that same, value will same too, because Mata_kuliah and Nrp was representing key (in unique characther)
>>>Mata_kuliah -> NRP
>>>NRP -> Nilai
first normal form - 1NF
>a table that told as first normal form if it not as unnormalized form table, where happen duplication of same kind field and possible for exist null field
>not be able for:
>>>multivalued attribute
>>>composite attribute or combination of both so price of attribute domain must atomic price.
>for example :
>>>data mahasiswa
>>>this tables not up to standard of 1NF
>>>decomposition become :
second normal form - 2NF
>2NF completed in a table if up to standard of 1NF, and all attribute except primery key, intactly have functional dependency on primary key
>a table not complete as 2NF if there are attribute that only have a partial functional dependency
>if there are attribute that not have dependency of primary key, so that attribute have to moved or eliminated
>functional dependency X -> Y told as complete if deleting a attribute A from X, that's mean Y not depend as functional anymore
>functional dependency X -> Y told as partial if deleting a attribute A from X, that's mean Y still depend as functional
>relation schema R in 2NF form if each attribute non primary key A is member of R depend on as complete as functional in primary key R
>example :this table complete 1NF, but not include as 2NF
>>>not complete as 2NF, because {NIM,KodeMk} assumed as primary key, while
{NIM, KodeMk} -> NamaMhs
{NIM, KodeMk} -> Alamat
{NIM, KodeMk} -> Matakuliah
{NIM, KodeMk} -> Sks
{NIM, KodeMk} -> NilaiHuruf
>>>that table need to decomposition become several table that p to standard 2NF
>>>the Functional dependency is:
{NIM, KodeMk} -> NilaiHuruf (fd1)
NIM -> {NamaMhs, Alamat} (fd2)
KodeMk -> {Matakuliah, Sks} (fd3)
>>>So :
fd1 (NIM, KodeMk, NilaiHuruf) -> Tabel Nilai
fd2 (NIM, NamaMhs, Alamat) -> Tabel Mahasiswa
fd3 (KodeMk, Matakuliah, Sks) -> Tabel MataKuliah
third normal form - 3NF
>3NF complete if have standard of 2NF form, and if there are no attribute non primary key that have dependency with other attribute non primary key (transitive dependensy)
>example :
>>>this table was up to standard of 2NF, but not complete as 3NF
>>>because there are still have an attribute of non primary key (viz, Kota and Provinsi) that have dependency with other attribute non primary key(viz, KodePos)
KodePos -> {Kota,Provinsi}
>>>until that table has to decomposition become Mahasiswa (NIM, NamaMhs, Jalan, KodePos) and KodePos (KodePos, Provinsi, Kota)
Boyce-Codd Normal Form (BCNF)
>BCNF has more powerful force and 3NF. for become BCNF, relation must in form of 1NF and each attribute forced depend on function at super key attribute
>for example : there are seminar relation, primary key is NPM + Seminar.
>case : student is able to take a or two seminar. each seminar need 2 counsellor and each studet guided by one of them. each counsellor only able to take one seminar. in this example, NPM and seminar showing a counsellor.
>sminar relation form is 3NF, but not BCNF because seminar code still depend with function on counsoller. if each counsellor has teach omly one seminar. seminar depend on one attribute that not super key as standardization as BCNF, so seminar relation must divided become two, that is :
4NF and 5NF
>relation is fourth normal form - 4NF, if relation in BCNF and no involved dependency multivalue. for eliminating dependency multivalue from one relation, we divide relation become two new relation. each relation containing two attribute that have multivalue connection.
>relation is fifth normal form - 5NF deal with property that known as join without losing information (lossless join). 5NF as known as PJNF or projection join normal form.this case is very rare emerge and difficult to detected practically
REFERENCE :
- NGURAH AGUS SANJAYA ER, S.KOM, M.KOM. Slide presentasi : Part 6 - DATABASE NORMALISASI
