Normalization

The design database is required, so that we can have a database of compact and efficient in the use of storage space, quick and easy in the initialization manipulation (add, change, delete) data.

Database design process

• Gather user needs / business
• Develop a needs-based ER Model user / business
• Convert E-R model to the set of relations (tables)
• Normalization relations, to remove anomalies
• Implemented to create a database with a table for each relationship that is normal

Database normalization

Normalization process is the establishment of the database structure so that most of the ambiguity can be removed.
The process of normalization is the process of grouping data elements into table-table that entity and show relationship. In the process of normalization is always tested in some cases, if there are difficulties at the time to add / insert, delete / delete, change / update, and read / retrieve on a Database .Normalization stage, starting from the most mild (1NF) to most stringent (5NF) .Usually only up to the level of 3NF or BCNF because already sufficient to generate the table-a table of good quality.

Normalization must be done because:

1. Optimizing table structures
2. Increase speed
3. The income data is the same
4. More efficient in the use of storage media
5. Reduce redundancy
6. Avoid anomalies (insertion anomalies, deletion anomalies, update anomalies).
7. Improved data integrity

A table saying good (efficient) or if the normal 3 to meet the following criteria:

• If there is decomposition (decomposition) table, it must be guaranteed safe decomposes (Lossless-Join Decomposition). That is, after the table is described / decomposed a new table-table, the table-table can generate a new table with the same exact.
• Maintain dependence on the functional changes in data (Dependency preservation).Does not violate Boyce-Code Normal Form (BCNF)
• If the three criteria (BCNF) can not be met, then at least the table does not violate the Normal Form of the third stage (3rd Normal Form / 3NF).

Normalization Stage

The process of normalization

• Data described in the form of a table, then analyzed based on specific requirements to some level.
• If the table has not been tested to meet specific requirements, the need to table some of the table into a more simple form to meet the optimal.

Functional Dependency

Functional Dependency describes a relationship attributes in relation
An attribute said functionally dependant on the other, if we use the value attribute to determine the value of the other attributes.
Symbols used are
to represent the functional dependency.
reads determine the functional
notation : B --> A
A and B are attributes of a table. A means of determining the functional B or B depends on A, if and only if there are 2 rows of data with the same value of A, then B is also the same value

notation : A not B or A x--> B
It is the opposite of the previous notation.

Example :
Functional Dependency:
NRP --> Nama
Mata_Kuliah, NRP --> Nilai

Non Functional Dependency:
Mata_Kuliah --> NRP
NRP --> Nilai

Functional Dependency of the table value
Name-->Nrp
Because for each value Nrp the same, then the value of the same name
(Mata_kuliah, NRP) -->
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).
NRP not Mata_kuliah
Value not NRP
FIRST NORMAL FORM (First Normal Form - 1NF)

A table on the form said to be normal if I did not reside in the un-normalized form of a table, where there is a kind of field multiplication and field that allows a null (empty)
Not allowed:

• Many attributes of value (Multi valued attributes).
• Attributes composite or a combination of both.

So:
Price is the domain attribute must be atomic rates

Example (1)
E.g. Student Data as follows:

Or

Table-the table above does not meet the requirements 1NF
Example (2)

Decomposition becomes:
Student Table:


Table Hobbies:
Second NORMAL FORM (Second Normal Form - 2NF)

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 dependence (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

Y is full if it is said to delete anàFunctional dependency X attribute A from X means that Y is no longer dependent functional.
Y said if deleting a partial attribute A from X means that Y is functionally dependent.àFunctional dependency X
R depend on the full primary key fungsioanl R.ÎRelation scheme R in the form 2NF if every non-primary key attribute A

Example (1)
The following table meet 1NF, 2NF, including but not



Example (2)
Does not meet 2NF, because (NIM, KodeMk) is regarded as the primary key:
NamaMhs --> (NIM, KodeMk)
Address --> (NIM, KodeMk)
Matakuliah --> (NIM, KodeMk)
SKS --> (NIM, KodeMk)
NilaiHuruf -->(NIM, KodeMk)
Table needs to be some table decomposition eligible 2NF

Example (3)
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) --> Student Table
fd3 (KodeMk, Matakuliah, SKS) --> MataKuliah Table

NORMAL FORM Third (Third Normal Form - 3NF)

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 (1)
Table following students eligible 2NF, 3NF, but does not meet


Because there are non-primary key attribute (i.e., City and Provincial), which has a dependence on non-primary key attributes of the other (i.e. KodePos):
(City, Province)àKodePos

Example (2)
So that the table should be decomposition:
Student (NIM, NamaMhs, Road, KodePos) r>KodePos (KodePos, Province, City)

Example 3
A model data meets said third normal form if it meets the form of both normal and does not have the attributes of non-identifying (non-unique identifier) that depending on the attributes of other non-identifying. If there is, separate one of these attributes a new entity, and attributes that depend on the attributes to become the new entity.
In a simple data model that we use here, none of non-identifying attributes (such as Task Mata kuliah description, or name Lecturer), which depends on the attributes non identifying other. But for the sake of example, we have Lecturer e.g entity attributes information Home Address and Phone Number House. Both can not be unique identify the object of a certain entity Lecturer, but both are mutually dependent.
As in the previous two-step normalization, as this type of dependences
can be removed by creating a new entity (which will not be created because the three
entities have been enough for one article).
Last model that we can meet this third normal form (third normal form)
and ready to convert into a table. But before, we need to discuss the various types of
relations, which are often found in data modeling, including those we met in the example model
data this time.

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 Normal once and forced each of the attributes depends on the function in the super key attributes.
In the example below there is a relationship 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.


Relations Seminar is a form of Third Normal, but not BCNF
Seminar Code because the function depends on the Pembimbing, if any Pembimbing can only teach a seminar. Seminar depend on one key attribute is not as super disayaratakan by BCNF. So
Seminar relations must be parsed into two namely:



Normal form of the fourth and fifth

Relations in the fourth normal form (NF 4) if the relation in BCNF and dependency tdak contain 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 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.