Normalization
Normalization:
1.) To reduce redundant table data to the very minimum.
2.) The redundant data cannot be reduced to zero in any database management system.
Advantages:
1.) To structure the data between tables so that data manipulation is simplified.
2.) To allow data retrieval at optimal speed.
3.) To simplify data manipulation through updates, inserts and deletes.
Before Normalization � Employee table
R - Rate
PNoo Pnamee Epid Ename R Hourly rate
P001 Target E100 Peter A 7000
P001 Target E101 Alexx B 6500
P001 Target E102 Johan C 6000
P001 Target E101 Alexx B 6500
P002 Carexx E100 Peter A 7000
P002 Carexx E101 Alexx B 6500
P002 Carexx E105 Priya C 6000
P002 Carexx E100 Peter A 7000
First Normal Form
1.) When a table is decomposed into two-dimensional tables with all repeating groups of data eliminated.
2.) There is no repeating groups.
3.) All the key attributes are defined.
4.) All attributes are dependent on a primary Key.
After First Normal Form �
Employee Table
R - Rate
PNoo Pnamee Epid Ename R Hourly rate
P001 Target E100 Peter A 7000
P001 Target E102 Johan C 6000
P001 Target E101 Alexx B 6500
P002 Carexx E100 Peter A 7000
P002 Carexx E101 Alexx B 6500
P002 Carexx E105 Priya C 6000
Second Normal Form:
1.) Each record in the table is in the first normal form and each column in the record is fully dependent on its primary key.
2.) It includes no partial dependencies.
3.) Find and remove fields that are related to the only part of the key.
4.) Group the removed items in the another table.
After Second Normal Form
Project
PNoo EmpID
P001 E100
P001 E101
P001 E102
P002 E103
P002 E104
P002 E105
Employee
R - Rate
Epid Ename R Hourly
E100 Peter A 7000
E101 Johan C 6000
E102 Alexx B 6500
E103 Sudip A 7000
E104 Rajaa B 6500
E105 Priya C 6000
After Second Normal Form
EmpProj
PNoo EmpID
P001 E100
P001 E101
P001 E102
P002 E103
P002 E104
P002 E105
Third Normal Form:
1.) All transitive dependencies are to be removed.
2.) Where non-key attributes is dependent on another non-key attributes.
After Third Normal Form
R - Rate
Rate
R Hourly Rate
A 7000
B 6500
C 6000
D 5000
E 4500
F 4000
EmpProj
PNoo EpID
P001 E100
P001 E101
P001 E102
P002 E103
P002 E104
P002 E105
After Third Normal Form
Project
PNoo Pname
P001 Target
P002 CareXX
P003 SelTel
P004 RFID
P005 Order
P006 XXXXXXX
Employee
Epid Ename Rate
E100 Peter A
E101 Johan C
E102 Alexx B
E103 Sudip A
E104 Rajaa B
E105 Priya C
