DATABASE NORMALIZATION

DATABASE NORMALIZATION

Database Plan Process (Review)
• Gather user/business need
• Develop e-r model based on user/business need
• Conversion e-r model to relation collection (table)
• Relation normalization to cause the loss of anomaly
• Implementation to database with make table for every relation that normalization

Data Basis normalization

Normalization are process basis structure forming data so a considerable part ambiguity can be removed. Normalization phase is begun of lightest phase (1NF) until tightest (5NF) Just usually comes up 3NF's zoom or BCNF because was enough is equal to result qualified table good.
The importance for normalization:
- Since marks sense database structure that insufficiently lovely
- Saved same data at severally place (file or record)
- Disability to result particular information
- Information’s forfeit happening
- Happening marks sense redundancy (repeat) or data duplication so wastes storage room and wanting hard with while updating process data
- Mark sense NULL VALUE
Normalization process:

- Untied data in shaped table, hereafter analysis bases particular situating go to many zooms.
- If table examinee has qualified particular, therefore table that needs to be broken down becomes many table that simpler until optimal form pock.

To the effect Normalization:

- to remove duplicate data
- to reduce complexity
- to water down modification data

Normalization?

- Optimal is table's structures
- Increasing speed
- Removing same data inclusion
- More efficient in storage media purpose
- Reducing redundancy

- Avoiding anomaly ( insertion anomalies , deletion anomalies , anomaly’s update ).
- Increased data integrity

One table is said well (efficient) or normal if pock 3 criterions as follows:
- If there is decomposition (decomposition) table, therefore decomposition it shall be secured safe( Lossless Join Decomposition ). Its mean, after that table is untied or at decomposition becomes new tables, that new tables can result tables originally equally a hair's breadth.
- Its petted dependable functional at the moment data change (Dependency Preservation).
- Don't breach Boyce Code Form's Normal (BCNF)


Functional Dependency

To do normalization, shall can determine earlier Functional Dependency (FD) or Functional dependency, notably deep do database design decomposition.
Functional Dependency (FD) can symbol with:
A -> B: its mean b have dependency with A
Matter a. functionally determine B or b functionally depend on A.


• Functional Dependency figuring relationship attributes in one relationship
• An attribute is said functionally dependant on the other if we utilize to assess that attribute to determine the other attribute point.
• Symbol that is utilized is -> for representing functional dependency.
> read functionally determines
• Notation: 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
• Notation: A. > B or a. x > B
Are opposite of previous notation.
Normalization forms

While design database utilizes relational model, we often find severally alternative in definition relationship scheme gathering. Severally selection more comfortable disbanding option – other option for medley motive. Normalization order is declared for in normal shaped terminology. Normal form are a ruling one be put on on relationships in data basis and has to be accomplished by that relationship on normalization levels. A relationship is said in given normal form if given condition pocks.


Functional Dependency:
• NRP > Nama
• Mata_Kuliah, NRP> Nilai
Non Functional Dependency:
• Mata_Kuliah > NRP
• NRP > Nilai
• Functional Dependency From Value Table
• Nrp > Nama
• because for every same Nrp value , so also same Nama value
• {Mata_kuliah, Nrp } > Value
• because Value attribute depend on Mata_kuliah and Nrp according to together. In other meaning for Mata_kuliah and same Nrp , so Value also same , because Mata_kuliah and Nrp is key (has unique).
• Mata_kuliah Nrp
• Nrp Value

First Normal Form - 1NF
• A table is said present in normal form I if it doesn't present in form unnormalized table, where happen multiplication of a kind field and there possible field that null (empty)
• Forbidden of existence:
o Attribute that have multivalue (multivalued attribute).
o Composite attribute or combination of both.
• So: Value of domain attribute must be atomic value.

SECOND NORMAL FORM (second is Form's Normal 2NF) (1 )

Both of Normal Shaped definition (2 NF) are:
1 ). Shaped pock 1 NF (first normal).
2 ). Attribute doesn't key to have logistic ala dependent on main key /
primary goes to y. .
So to form normal both of every table / must file is determined keys its attribute. Attribute key has unique and get to represent attribute any other that as its member. On College Student table example that accomplishes first normal (1 NF) , visually that NIM constitute Primary Goes To y. (PK). NIM -> Name, Guardian lecturer .
Its mean is that Name attribute and Sponsor Lecturer hinges on NIM.
But NIM -> Lesson. Its mean is bahqa attribute Lesson not depends
on NIM.

For meeting normal both of, therefore on that college student table is broken down

DRD NORMAL FORM (Third is Form's Normal 3NF)

Normal Shaped definition drd (3 NF) are:
1 ). Shaped pock 2 NF (second normal).
2 ). Attribute doesn't key have no dependensi transitiving to keys
main / primary goes to y..
Following Samples relationship that accomplishes to form 2 NF, but doesn't accomplish to form 3
NF.



Attribute No. Order and No. Massage constitutes prime key, well code item and name
item has dependensi functional to that prime key.
On table upon, each code item with, therefore item's name point also with,
so pointing out marks sense dependensi two that attribute, but which that
determine, what is item's code hinges on item's name, or contrariwise? So
item's name has dependensi functionaling to item's Code.
On this relationship points out that item's name have no dependensi ala
directly to prime key (No. order and No. Massage). In other words Name
Item has dependensi transitive to prime key.
So for meeting form 3 NF, therefore relationship upon at decomposition becomes
two relationships as follows:




Boyce Codd's form Form's Normal (BNCF)

BCNF'S Shaped definition is:
1 ). Shaped pock 3 NF (drd normal).
2 ). All conditioner (determinant) are key candidate (attribute that gets unique character).
Each attribute shall hinge function on super key's attribute.
BCNF constitutes to form normal as remedial as to 3 NF. A relationship
that BCNF'S pock always accomplishes 3 NF, but don't for on the contrary. A
relationship that accomplishes 3 NF was obviously accomplishes BCNF. Since form 3 NF
still enable anomali's happening.
On this following example exists seminar table, prime key is number student +
seminar, with that savvy:
_ Student can take a or two seminar.
_ Each seminar needs 2 instructors.
_ Each student led by either one 2 seminar instructors.
_ Each instructor just may take one seminar just.

On this example, number student and seminar points out an instructor.




Tabular seminar is accomplish forms DRD normal (3 NF), but don't
BCNF because seminar number is still hinge function on instructor, if each
instructor can teach at only one seminar. Logistic dependent seminar
on one attribute is not super key as one presupposed by BCNF.
Therefore seminar table shall at decomposition becomes two tables, which is instructor table
and seminar_instructor, as following as this:



Fourth normal form and to five
• Relationship in shaped fourth normal (4 NF) if relationship in BCNF and tdak 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.