Database Normalization

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.

Database Design Process (review)
- Gather user needs / business
- Develop an ER Model based on user / business
- Convert E-R model to the set of relations (tables)
- To Normalized relations, to remove anomalies
- Implemented to create a database with a table for each relationship that have to normalized


Why normalization is needed?
- Optimizing table structures
- Increase speed
- The income data is the same
- More efficient in the use of storage media
- Reduce redundancy
- Avoid anomalies (insertion anomalies, deletion anomalies, update anomalies).
- Improved data integrity

A table saying good (efficient) or if the normal 3 to meet the following criteria:
1. If there is decomposition (decomposition) table, it must be guaranteed safe decomposition (Lossless-Join Decomposition). That is, after the table is described / in the decomposition into a new tables, the tables can generate a new table with the same exact.
2. Maintain dependence on the functional changes in data (Dependency preservation).
Does not violate Boyce-Code Normal Form (BCNF)
3. 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).

Functional Dependency (FD)

• Functional dependency (abbreviated FD) is a restriction that comes from the meaning of attributes and relationships between attributes.
• Functional Dependency attributes describe the relationship in a relationship. 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.

Notation : A -> B

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 ->/ 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 from the value table :

NRP -> Nama

• Because for each value NRP the same, then the value of the same nama.

{Mata_Kuliah, NRP } -> Nilai

• 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

Normal form is a condition (using the FD and key) that determines whether a scheme relationships meet certain criteria. There are several normal forms based on a number of criteria:

1. Primary keys (1NF, 2NF, 3NF)
2. All Candidate Keys (2NF, 3NF, BCNF)
3. Multivalued dependencies (4NF)
4. Join dependencies (5NF)

First Normal Form (1NF)

A table on the form said to be normal if I 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) 1NF is not allowed on the:

1. Attribute values, many ( Multivalued attributes).
2. Attribute a composite or a combination of both.
3. Nested relations.

So, price is the domain attribute must be atomic rates.

Advantage of the 1NF compared Unnormalized relation (UNRs) is a simplification in the form of representation and ease of use in developing a query language

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 element R depend on the full functional primary key R.

EXAMPLE:

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


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).

Table following students eligible 2NF, 3NF, but does not meet

Because the table above there are still non-primary key attribute (ie, Kota and Provinsi), which has a dependence on non-primary key attributes of the other (ie KodePos), namely:

KodePos à {Kota, Provinsi}

So that the table in the decomposition needs to be:

• Mahasiswa (NIM, NamaMhs, Jalan, KodePos)

– KodePos (KodePos, Provinsi, Kota)

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.

Fourth Normal Form and Fifth Normal Form
- 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.

5th Normal Form /5NF

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.

References
1. Agus Sanjaya ER, S.Kom, M.Kom, slide presentation : Normalisasi
2. http://en.wikipedia.org/wiki/Database_normalization
3. http://kuliah.dinus.ac.id/ika/prc4.html
4. http://databases.about.com/od/specificproducts/a/normalization.htm

ER-Diagram

Database definition

A group of on file data in disk magnetic, disk optical or is depository of other sekunder

Inwrought collection of datas which each other is interconnected the than an enterprise ( company, governmental institution or private sector)

· Manufacturing business = production planning data, data produce aktual, data ordering of material, etc

· Hospital = patient data, doctor, nurse, etc

DBMS

Group of database with software of application being based on database.

This application programs used to access and look after database.

Especial target of DBMS to provide an efficient and easy environment for the usage of, to withdrawal and is depository of information and data.

Bit, Byte, Field

Bit = representing part of containing smallest data of value 0 or 1.

Byte = corps of beets which of a kind.

Field = a group of byte-byte which of a kind, in data bases used by attribute term.

Field

Representing the nature of or characteristic from an entity providing to provide clarification of detail about entity.

An relationship also can have attribute.

Attribute example:

STUDENT: NIM, NAME OF, ADDRESS

CAR: NO_PLAT, COLOUR, TYPE, CC

Attribute type:

Multivalue Vs Single

Single = only can fill at most one value

Multivalue = can fill with interest from one value with same type

Composition Vs Atomic

Atomic = indiscrete into smaller attribute

Composition = representing aliance from some smaller attribute

Derived Attribute.

attribute which [is] its value can be yielded from other attribute value, for example = yielded age of attribute of[is date of delivering birth

Null Value Attribute

Attribute which [do] not have value to a[n record

Mandatory Value Attribute

Attribute which must have value

Record/ Tuple

Representing an data line in a[n relationship

Consist of attributes corps where attributes of interaction to inform entitas / relationship completely

Entity/File

File represent corps of record which of a kind and have [is] same element, [is] same attribute but different each other its data [of] him.

Type File

In application process:

File Mains

File Transaction

File Report

File History

File Protector

File Job/Activity

Domain

Domain represent corps of values enabled to stay in one or more attribute. Each;Every attribute in data bases of relasional defined by as a domain.

Data Element key

Key is element of record weared to find the the record when accessing or can is also used to identify each;every entity/record

Key type:

· Superkey represent one or more attribute from tables of able to be used to identify entityty / record of tables uniquely ( not all attribute can become superkey)

· Cadidate Key is super of key with minimum attribute. Key Candidate may not contain attribute of other tables so that key candidate beyond question superkey but not yet of course on the contrary.

· Primary Key

One of the attribute of key candidate can be selected / to be determined to become key primary with three criterion:

1. The Key more natural to be used as reference

2. The Key more simple

3. The Key well guaranted it

· Alternate Key is attribute of key candidate which is not chosen become key primary.

· Foreign Key represent any attribute subjecting to key primary at other tables. Key Foreign will happened at one particular relationship owning many to one kardinalitas ( one to many) or many to many ( many to many). Key Foreign usually always put down by tables of which is flange to many is.

· External [of] Key represent a[n attribute lexical ( or gathering of lexical attribute) which [is] its values always identify one object of instance.

ERD (Entity Relationship Diagram)

ERD is a network model using wording which is kept in system abstractionly.

Differencebetween] DFD and of ERD:

DFD represent an function network model to be executed by system

ERD represent data network model emphasizing at and structure of relationship data

ERD Elements

ENTITY

At ER Diagram of Entity depicted with square form of length. Entity is something that there is in real system and also abstraction where on file data or where there are data

Relationship

At ER Diagram of relationship can be depicted with a is lozenged. Relationship is natural relation that happened between entity. In general called with elementary verb. so that facilitate to conduct read of its relationship.

Relationship Degree

is the amount of entitas participating in one relationship. Degree of which often weared in ERD

Attribute

is the nature of or characteristic of each and also entitas of relationship

Cardinality

showing optimum of tupel able to berelasi with entitas at other entity.

Relationship Degree

Unary Relationship

is model of relationship that happened between entity coming from entity set is same.

Binary Relationship

is model of relationship that happened between 2 entity.

Ternary Relationship

representing relationship between instance from 3 type of entity unilaterally.

Cardinality

There are 3 relationship kardinalitas that is

To One One :

Storey;Level Relation one to one expressed with one occurence at first entity, only having one relation with one occurence at second entitas conversely.

To Many One or of Many One to :

Storey;Level Relation one to many is equal to many to one depended from which direction of relation that see. For one occurence at first entitas can have many relation with occurence at second entity, if on the contrary one occurence at second entity can only have relation with one occurence at first entity.

Many To Many :

happened if each;every occurence at a entity have many relation with occurence other entity.

Cardinality for example:

Notation ERD:

Symbolic notation in diagram of ER

1. Square of length express gathering of entity

2. Radian express attribute

3. Rhombic of relationship gathering

4. Line as link between relationship gathering with gathering of entitas Gathering and of Entitay with its attribute