Normalization ERD

Normalization ERD

There is some awkwardness that can be seen from the picture on the ERD that was attached to the Quiz question, there is no cardinality between PART and Ternary & PROJECT and the relationship between dependent & PROJECT

ERD has a new improved

In the ERD that was diperbahaui, can be added 1-N cardinality on the PROJECT and the relationship, this is because, because, in the part 1 and can handle many PROJECT, PROJECT, and many can be handled by the Section 1

Tedapat goal ERD is making improvements; this is done on the table from the ERD mapping, the mapping of the table as much as possible in the form of mapping 2NF or 3NF this can occur if the process is done correctly.

1. First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database. A normal form used in database normalization. A relational database table that adheres to 1NF is one that meets a certain minimum set of criteria. These criteria are basically concerned with ensuring that the table is a faithful representation of a relation and that it is free of repeating groups.

• Eliminate duplicative columns from the same table.
• Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
  

2. NF normalization form(2NF)
1 NF form from above and with Functional Dependency happens then obtained a second 2NF normalization form as follows

3. NF normalization for (3NF)

2NF form from above and with Functional Dependency happens then obtained a second form of 3NF normalization as follows

4. Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:

• Meet all the requirements of the third normal form.
• A relation is in 4NF if it has no multi-valued dependencies.

Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database

Normalization Database

Normalization Database

In the field of relational database design, normalization is a systematic way of ensuring that a database structure is suitable for general-purpose querying and free of certain undesirable characteristics insertion, update, and deletion anomalies that could lead to a loss of data integrity.

The reason is other waste storage space (hard disk),Normalization process is the establishment of database structure so the ambiguity can be removed. Normalization stage, starting from the most mild (1NF) to most stringent (5NF). Normalization is usually only up to the level of 3NF or BCNF because already sufficient to generate the table-a table of good quality.

A table told goodness (efficient) or normal if fulfilling 3 criterions follow this

• If there is decomposition of tables, hence it’s of him having to be secured (Lossless-Join Decomposition). Its Meaning, after the tables elaborated / decomposition become new tables, the new tables can yield tables initially 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 depicts relation of attributes in a relationship. An attribute told dependant functionally at other if we use the attribute value to determine other attribute value. Symbol the used à for representing dependency functional à read functionally determine

Notation: Aà B

A and B is attribute from a tables. That Mean functionally A determine B or B depend on A, if and only if there is 2 data line with value of A is same, hence value of B is same also

Notation: A x--> B

Is reverse of previous notation.

FUNCTIONAL DEPENDENCY (FD)

NRP à Name

Mata_Kuliah, NRP à Valueà

Non Functional Dependency:

Mata_Kuliah NRP

NRP à Value

Functional Dependency from value of tables

Nrp à Name

Because to each value of Nrp is same, hence value of Name is same also

{ Mata_Kuliah, NRP } à Value

Because attribute Assess depend on Mata_Kuliah and NRP by together. In other meaning for the Mata_Kuliah and NRP is same, hence is same Value also, because Mata_Kuliah and of NRP represent key (having the unique of character).

Mata_Kuliah à NRP

NRP --> Value

FIRST NORMAL FORM (First Normal Form - 1NF)

A tables told to reside at normal form of I if it do not reside at form of normalized table, where happened duplication of field which a kind and enable there is field which null (empty)

Do not be enabled the existence of:

• valuable attribute many ( Attribute Multivalent )
• Composite Attribute or combination from both.

Becoming:

Price of Domain attribute have to represent price of atomic

Example:

For example Data Student follows this:

Or

Tables of above ineligibility 1NF

Table Hobby:

NORMAL FORM BOTH (Second Normal Form - 2NF )

• Normal Form 2NF fulfilled in a tables, if have fulfilled form 1NF, and all attribute besides primary key, intact have Functional Dependency [at] primary key
• A tables do not fulfill 2NF, if there is attribute which depended ( Functional Dependency) only having just partial ( only depend on some primary key )
• If there are attribute do not have depended to primary key, hence the attribute have to be moved or eliminated
• Depended functional X àY told [by] full (of) if vanishing a[n attribute of A of X mean Y [shall] no longger hinge functionally.
• Depended functional X à Y told [by] partial if vanishing a[n attribute of A of X mean Y still hinge functionally.
• Scheme relationship R in the form of 2NF if every attribute is non A element R primary key hinge full of by functional at R primary key.

Tables following fulfill 1NF, but do not including 2NF

·

Do not fulfill 2NF, because { NIM, KodeMk } considered to be primary key while:

{ NIM, Kodemk} à Namamhs

{ NIM, Kodemk}à Address

{ NIM, Kodemk} à Matakuliah

{ NIM, Kodemk} à Sks

{ NIM, Kodemk}à Nilaihuruf

The Tables require to decomposition become some tables of up to standard 2NF. Its Functional follow this:

• { 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 ( Third Normal Form - 3NF )

Normal form 3NF fulfilled if have fulfilled form 2NF, and otherwise there is attribute is non primary key owning depended to attribute non the other primary key ( depended transitife).

Example :

Tables of up to standard student following 2NF, but do not fulfill 3NF

Because still there are attribute of non primary key (Town and of Province) owning depended to attribute non other primary key ( namely Kodepos :

Kodepos à { Town, Provinsi}

So that the tables require to decomposition become :

• Student ( NIM, Namamhs, Street, Kodepos
• Kodepos ( Kodepos, Province, Town)

Boyce-Codd Normal Form ( BNCF)

Boyce-Codd Normal Form have constraint which stronger than third Normal form. To become BNCF, relationship have to in the first normal form and every attribute forced to base function at super key attribute.

At hereunder example there are Seminar relationship, Primary key is NPM + Seminar

Student may take one or two seminar.;Every seminar require 2 counsellor and every student guided by one among 2 counsellor of seminar. Every counsellor only take just one seminar. At this example NPM and Seminar show a Counsellor.

Form Relationship Seminar is Third Normal form, but do not BCNF. Because Seminar Code still hinge function Counsellor, if every Counsellor can teach only one seminar. Seminar base on one attribute is non super key such as those which required by BCNF. Hence Seminar relationship have to be broken to become two, that is :

Fourth normal form and to five
• Relationship in shaped fourth normal (4 NF) if relationship in BCNF and no contains dependency a lot of point. To remove dependency there are many point of one relationship, we divide relationship become two new relationships. Each relationship contains two attributes that have relationship a lot of points.
• Relationship in shaped normal to five (5NF) get business with property is join the so called without marks sense information loss (lossless join). Form normaling to five (5 NF also so-called PJNF (projection join is form's normal). This case very rare appearance and hard to be detected practical ala.