NORMALIZATION
Design Process of Database ( review)
Collecting requirement of user/business
Developing E-R Model pursuant to requirement of user / business
Converting E-R Model to relationship gathering ( tables)
normalizing the Relation to eliminate the anomaly
Implement to database by making table to each relationship which have normalization.
NORMALIZATION OF DATABASE
Normalization is process forming of data bases structure so that most ambiguity can be eliminated
Normalization Phase started from lightest phase (1NF) tightest till ( 5NF)
Usually only coming up with level 3NF or BCNF because of have enough adequate to make goodness quality tables.
The normalization have to done because of :
Optimalizing Structures of tables
Improving speed
Eliminating inclusion of same data
More efficient in usage of storage media
Lessening redundant ion
Avoiding anomaly ( anomalies insertion, anomalies deletion, update anomalies). improved Data integrity
A tables told by good ( efficient) or normal if fulfilling 3 criterion such as:
If there is decomposition ( elaboration) of tables, hence its decomposition have to be secured ( Lossless-Join Decomposition). Its Meaning, after the tables elaborated / decomposed become new tables, the new tables can yield initially tables with precisely
The looked after of depended functional at the time of change of data ( Dependency Preservation)
not impinge Boyce-Code Normal Form ( BCNF)
If the third criterion ( BCNF) cannot fulfilled, hence at least the tables not impinge Normal Form of third phase ( 3rd of Normal Form / 3NF )
Functional Dependency
Functional Dependency depict relation of attributes in a relationship
An attribute told by functionally dependant at other if us use that attribute value to determine other attribute value
Symbol which is used is for representing dependency functional. read as functionally determine
Notasi: A -> B
A and b is attribute of one table. Matter functionally A determines b or b cling to, if and only if available 2 data row with appreciative A same, therefore point b also with
Notasi: A -> B atau A x -> B
Are opposite of previous notation.
First Normal Form – 1NF
A[N tables told as reside in I normal form if it not reside in unnormalized table form, where duplication of a kind of field happened and enabled there is null ( empty ) field
There are may not existence of :
Many valuable attribute (Multi valued Attribute).
Composite Attribute or combination from both.
so:
Price of Domain attribute have to represent atomic price
For example College Student Data as follows:
can be decomposing become:
collage table
Second Normal Form - 2NF
Normal 2NF form fulfilled in a tables of if have fulfilled 1NF form, and all attribute besides primary key, intactly have Functional Dependency at primary key
A tables not fulfill the 2NF, if there is depended attribute ( Functional Dependency) only having the character just partial ( only depend on some of primary key)
If there are attribute which not have depended to primary key , hence the attribute have to be moved or eliminated
functional Depended of X -> Y told by full if deleting an attribute of A from X its mean Y shall no longer depend on functional
functional Depended of X -> Y told by partial if vanishing a[n attribute of A from X mean Y still depend functional
Relationship scheme of R in form of 2NF if each attribute non primary key A ? R hinge full by functional at primary key R
following Tables is fulfill on 1NF, but not including as 2NF
not fulfill on 2NF, because { NIM, Kodemk} considered to be primary key while:
{ NIM, Kodemk } -> Namamhs
{ NIM, Kodemk } -> Alamat (Address)
{ NIM, Kodemk ) -> Matakuliah
{ NIM, Kodemk } -> Sks
{ NIM, Kodemk } -> Nilaihuruf
The Tables require to decomposed become some tables which is fulfill on 2NF standard.
Its Functional acre dependency:
{ NIM, Kodemk -> Nilaihuruf ( fd1)
NIM -> { Namamhs, Address} ( fd2)
Kodemk -> { Matakuliah, Sks} ( fd3)
So that :
fd1 ( NIM, Kodemk, Nilaihuruf} -> Tables of Value
fd2 ( NIM, Namamhs, Address} -> Tables of Student
fd3 ( Kodemk, Matakuliah, Sks} -> Tables of Matakuliah
Third Normal Form - 3NF
Normal 3NF form fulfilled if have fulfilled 2NF form, and otherwise there is non primary key attribute which is owning depended to other non primary key attribute ( transitive depended).
Example :
following Tables of collage student fulfilled to 2NF standar, but not fulfilled on 3NF
Because of there are non-primary key attribute (Kota and Provinsi), which is has a dependence on the other non-primary key attributes (KodePos):
KodePos -> {Kota,Provinsi}
So that the table need to decompositing 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 and each attributes forced to depends on the function in the super key attributes.
In the following example there is a “seminar” relationship, and the Primary Key is NPM + Seminar.
Students may take one or two seminars. Each seminar requires 2 counselors and each students teach by one of the 2 counselors of seminar. Each counselor can only take one seminar course. In this example NPM and seminar show a Counsellor.
Boyce-Codd Normal Form (BNCF)
Seminar Relationship form is third Normal form, but didn’t BCNF
because seminar Code is still hinge with function on Counselor, if each Counselor can teach just one seminar. Seminar dependent on one attribute which is not super key that likes BCNF standards. Therefore
seminar relationship shall be broken down as two such as:
The Fourth and Fifth of Normal Form
The relations can become fourth normal form (NF 4) if the relations in BCNF and didn’t contain dependence with many 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 has many relationship value.
The relations in fifth normal form (5NF) deal with the property called the join without any loss of information (lossless join). Fifth normal form (5 NF also called the PJNF (projection join normal form). This case is very appear less and difficult to detect practicely.
