Database Normalization

Design Database Process

The process includes:
- Gathering user or business's requirement
- Develop E-R Model bases user or business's requirements
- Convert E-R Model go to relationship gatherings (table)
- Relationship normalization to remove anomaly
- Implementation goes to database by make table for each relationship already most normalization

Database Normalization
- Normalization process is the establishment of the database structure so that most of the ambiguity can be removed.
- 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-table good quality

Why done by normalization?
- Optimizing table structures
- Increasing speed
- Removing same data inclusion
- More efficient in storage media purpose
- Reducing redundancy
- Avoiding anomaly (insertion anomalies, deletion anomalies, and anomaly's update).
- Increased data integrity

A table saying good (efficient) or if the normal 3 to meet the following criteria:
1. If there is decomposition (decomposition) table, then the decomposition will be guaranteed safe (Lossless-Join Decomposition). That is, after the table is described / in the decomposition into a new table-table, the table-table can generate a new table with the same exact.
2. Maintain the functional dependence on the change data (Dependency preservation)
3. No 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).

Functional Dependency
A functional dependency (FD) is a constraint between two sets of attributes in a relation from a database.
Given a relation R, a set of attributes X in R is said to functionally determine another attribute Y, also in R, (written X --> Y) if and only if each X value is associated with precisely one Y value. Customarily we call X the determinant set and Y the dependent attribute. Thus, given a tuple and the values of the attributes in X, one can determine the corresponding value of the Y attribute. For the purposes of simplicity, given that X and Y are sets of attributes in R, X --> Y denotes that X functionally determines each of the members of Y - in this case Y is known as the dependent set. Thus, a candidate key is a minimal set of attributes that functionally determine all of the attributes in a relation.

Example :



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

Non Functional Dependency:
• Mata_Kuliah --> NRP
• NRP --> Nilai
Functional Dependency from tables of Nilai:
Nrp --> Nama
Because to each Nrp value is same, so Name value is same too.

{Mata_Kuliah, NRP} --> Nilai
Because attribut value depend on Mata_Kuliah and NRP by together. In other meaning for the Mata_Kuliah and NRP is same, so have same Nilai too, because Mata_Kuliah and NRP is key ( having the character is unique).

Mata_Kuliah --> NRP
NRP --> nilai


First Normal Form / 1NF
First normal form (1NF or Minimal Form) is 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.
The concept of a "repeating group" is, however, understood in different ways by different theorists. As a consequence, there is no universal agreement as to which features would disqualify a table from being in 1NF.

Not allowed for :
• Attribute that valuably there are many ( Multi valued attribute )
• Attribute composite or combine of both
So :
• Price of Domain attribute must represent atomic price
For Example

Table "Mahasiswa"

Or


That Tables is not complete 1NF condition.
Decomposition becomes:

Table mahasiswa :

T
able hoby :


Second Normal Form / 2NF
Second normal form (2NF) is a normal form used in database normalization. 2NF was originally defined by E.F. Codd in 1971. A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a 1NF table is in 2NF if and only if, given any candidate key and any attribute that is not a constituent of a candidate key, the non-key attribute depends upon the whole of the candidate key rather than just a part of it.
In slightly more formal terms: a 1NF table is in 2NF if and only if none of its non-prime attributes are functionally dependent on a part (proper subset) of a candidate key. (A non-prime attribute is one that does not belong to any candidate key.)

Functional dependency X --> Y is said full if erase an attribute A of X its mean that Y no longer functional dependent. Functional dependency X --> Y is said partial if erases an A's attribute of X matter Y still functional dependent. Relationship schemer on 2NF form if each attribute non primary key A R full dependent functionally on primary key R.

This table accomplishes 1NF, but not exclude 2NF :


That table is Not accomplishing 2NF form, because (NIM, KodeMk) is regarded as the primary key:

{NIM, KodeMk} -> NamaMhs
{NIM, KodeMk} -> Alamat
{NIM, KodeMk} -> Matakuliah
{NIM, KodeMk} -> SKS
{NIM, KodeMk} -> NilaiHuruf

Table need to decomposition become some table measures 2NF

Functional dependency as follows:
• {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

The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E.F. Codd in 1971. Codd's definition states that a table is in 3NF if and only if both of the following conditions hold:
1. The relation R (table) is in second normal form (2NF)
2. Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on every key of R.
An example of a 2NF table that fails to meet the requirements of 3NF is:


Because still there are attribute not primary key (namely Kota and Provinsi) own depended to attribute not other primary key (name is Kodepos) :
• Kodepos --> {Town, Provinsi }

So that the table require to decomposition become :
• Mahasiswa ( NIM, Namamhs, Jalan, Kodepos)
• Kodepos ( Kodepos, Provinsi, Town)

Boyce-Codd Normal Form / BCNF

Boyce-Codd normal form (or BCNF) is a normal form used in database normalization. It is a slightly stronger version of the third normal form (3NF). A table is in Boyce-Codd normal form if and only if, for every one of its non-trivial functional dependencies X --> Y, X is a superkey—that is, X is either a candidate key or a superset thereof.

Fourth and Fifth Normal Form

Relationship in fourth normal form (4NF) if relationship in BCNF and not contains multi value dependency. To remove multi value dependency from one relationship, we are divide relationship become two new relationships. Each relationship contains of two attributes have multi value relationship.

Relationship in fifth normal form (5NF) get business with property is calling join without marks sense information loss (lossless join). The fifth normal Form (5 NF) also know as PJNF (projection join normal form). This case is very rare to appearance and hard to detect practically.

DATABASE AND ER-DIAGRAM

Definition Database
The database is a set of data stored in the magnetic disk, optical disk or other secondary storage. Collection of integrated data-related data of an enterprise (company, government or private).
For example:

• Manufacturing business –> Production planning data, recent production data, materials order data, etc
• Hospital –> Patient data, doctor, nurse, etc

Database management system (DBMS)

Database management System is database collection with software application in based of database. This application programs is use for accessing and maintaining the database. The first aim of DBMS is to preparing an easy and efficient environment for using, collecting and storing data and information.

Definition of bit, byte, field

- Bit: is the smaller part of data, which contain of value 1 or 0

- Byte: collection of bits that of a kind

- filed: collection of byte that of a kind, which in database is called "attribute".

ATTRIBUTE OR FIELD
Attribute is the nature or characteristics of an entity that provides provide detail on these entities. A relationship can also have attributes. Example attributes:

1. STUDENTS: NIM, NAME, ADDRESS
2. CAR: NOMOR_PLAT, COLOR, TYPE, CC

Type of attribute

1. Single vs. multivalue

Single: only can fill by one value

multivalue: can fill with more one value with the same of kind

2. Atomic vs. composition

Atomic: can not divided into smaller attribute

Composition: boundless from some smaller attribute

3. Derived attribute

Derived attribute is an attribute which its value can be produced from other attribute

4. Null value attribute

Null value attribute is an attribute with no value for a record

5. Mandatory value attribute is an attribute which must have value

RECORD / TUPLE
It is a line of data in a relationship, and consists of a set of attributes where the attribute-attribute-attribute is to inform each other entity / relationship fully

Entity / FILE
File is a collection of similar records and have the same elements, the same attributes but different data value.
File Type
In processing applications, files can categorized as follows:
- Master File
- Transaction Files
- File Reports
- File History
- File Protection
- File Work

Domain

Domain is collection of values which enabled to stay in one or more attribute. Every attribute in a relational database is defined as a domain

Key of element data

Key is the element of record which used to find the record when accessing or can also used to identify every entity / record / row.

Kind of key:

Super key is one or more attribute from a table which can be used for identifying entity / record from table uniquely

Candidate key is super key with minimum attribute. Candidate key may not contain attribute of other table so that the candidate is super key, but not the contrary

Primary key

One of the attribute of candidate key can be selected / determined into primary key with 3 criteria:

- The key is more natural for use as reference

- The key is simpler

- The key is unique

Alternate key

Alternate key is attribute of candidate key which is not chosen become primary key

Foreign key

Foreign key is any attribute subjecting to primary key at other tables. Foreign key will be happened in a relation which have one to many cardinality or many to many cardinality. Foreign key usually put at the table which direct to many cardinality.

External key

External key is lexical attribute or collection of lexical attribute which values always identify one object instance

ERD (ENTITY RELATIONSHIP DIAGRAM)

• ERD is a network model that use wording that kept in system according to abstract.
• Difference between DFD and ERD :
• DFD is a function network model that would carried out by the system
• ERD is a data network model that emphasized in structure and relationship data

ELEMENTS OF ERD

Entity

In ER Diagram entity described with long square form. Entity is something that there in real system also or abstract where does stored data or where does found data.

Relationship

in ER Diagram relationship can be described with a trapezoid. relationship is a natural connection that happen between entity. in general given name with verb base so that make easy to do the relation reading.

Relationship degree

Is a total entity that participate in one relationship. degree often worn in ERD.

Attribute

Is a character or characteristics from every entity and also relationship

Cardinality

Show optimum tupel that can be related with entity in another entity

Relationship degree

Unary relationship is Relationship model happen between the entity which coming from the same entity set.Binary relationship is relationship model happen between 2 entity. Ternary relationship between instance of 3 entity unilaterally.

Cardinality

There are 3 cardinality relation, that is :

• One-to-one : relationship degree one-to-one expressed by one event in first entity, only have one relation with one event in second entity and on the contrary.
• One-to-many or many-to-one : relationship degree one-to-many is equal many-to-one dependent from where that relationship seen. For one event in first entity can have many relationship with event in second entity, the other way, one event in second entity only have relationship with one event in first entity.
• Many-to-many : happen if each event in an entity having a lot of relationship with event in other entity

Example : cardinality