Chapter -3 Normalization
Normalization is a database design technique, which begins by examining the relationships
also called functional dependencies between attributes. Its main objective is to create an
accurate representation of the data, relationships between the data, and constraints on the data
that is pertinent to the enterprise.
As per the given project requirements, normalization up to 3rd level has to be done.
There are some basic rules needed to be followed while switching over from un-normalized
table to the 3rd Normal Form:-
Form:-
Data Gathering related to Un-normalized Table
entity, Requirements with repeating groups
Removing Repeating
Group
1st Normal Form with
partial dependency
Removing Partial
dependency
2nd Normal Form with
full functional
Removing Transitivity
3rd Normalization
without any transitive
Package Shipment
The following information about the shipment is needed to be stored in the database. After the research work, following attributes are defined for
the package table.
3.1. Un- normalized Table
Sender_ID S_Name S_Address S_State S_Pincod S_Count S_Phone S_Fax S_Email ConsignNo Type Content
e ry
CS001 Sachine sec-15, Haryana 132103 India 9999999999 2345677 sa@gmail.com CN001 Dox ------------
115B 8
CN002 Dox --------------
CS002 John Sec-12, 234 Maharas 334862 India 9922223333 244234 j@gmail.com CN003 Non Dox -------------
B htra
CS003 Ramesh Sec-22, 24 B Jharkhan 829301 India 34567345666 4345345 r@gmail.com CN004 Dox --------------
d CN005 Dox --------------
Weight Scity Dcity Mode Express Cost CDate RDate R_Name R_Address R_Pincode R_State
100gm Panipat Kanpur Urjent Air 50 25-03-2010 26-03-10 Sehwag Sec-25, 222D, 183567 UP
150gm Bangalore Normal Rail 60
27-03-10 Ramesh Sec-12, 145B 233267 Karnataka
200gm Mumbai Ambala Urjent Air 70 28-03-2010 29-03-10 Rahul Sec-12, 165B 344234 Punjab
100gm Bokaro Ambala MostUrjent Air 100 02-04-2010 03-04-10 John Sec-1, 265B 344455 Punjab
300gm Kanpur Urjent Rail 50 04-04-10 Rajesh Sec-2, 65B 238965 UP
A R_Countr R_Fax R_Email HandlerID H_Name H_Add H_Phone H_Email
y
India 31313 sw@gmail.com H001 HN1 HA1 HP1 HE1
54325 ra@gmail.com H002 HN2 HA2 HP2 HE2
India 434342 ra@gmail.com H001 HN1 HA1 HP1 HE1
India 324432 jo@gmail.com H003 HN3 HA3 HP3 HE3
545455 raj@gmail.com H004 HN4 HA4 HP4 HE4
Anomalies Present:
1. Insertion Anomaly- In the above table, the handler and recipient details cannot be
inserted without having a packet and sender because in the above table, ConsignNo
(PacketID) seems to be the primary key and which cannot be kept null.
2. Deletion Anomaly- If one want to delete the Packet info details then recipient info,
sender info will be deleted automatically.
3. Modification Anomaly- If one wants to change the Customer (sender), recipient
address and some info for then every related tuples has to be modified.
a. Moreover there are many redundancies possible.
3.2. 1st Normal Form
Here there can be more than one packet corresponding to (Customer) sender for the same
sender, so a repeating group is present corresponding to that sender values which has to be
removed.
Sender_ID S_Name S_Address S_State S_Pincode S_Country S_Phone S_Fax S_Email ConsignNo Type Content
CS001 Sachine sec-15, Haryana 132103 India 9999999 2345677 sa@gmail.com CN001 DOX --------------
115B 999 8
CS001 Sachine sec-15, Haryana 132103 India 9999999 2345677 sa@gmail.com CN002 DOX --------------
115B 999 8
CS002 John Sec-12, 234 Maharas 334862 India 9922223 244234 j@gmail.com CN003 Non -------------
B htra 333 Dox
CS003 Ramesh Sec-22, 24 B Jharkhan 829301 India 34567345 4345345 r@gmail.com CN004 Dox --------------
d 666
CS003 Ramesh Sec-22, 24 B Jharkhan 829301 India 34567345 4345345 r@gmail.com CN005 Dox --------------
d 666
Weight Scity Dcity Mode Express Cost CDate RDate R_Name R_Address R_Pincod R_State
e
100gm Panipat Kanpur Urgent Air 50 25-03-2010 25-03-10 Sehwag sec-25, 222D, 183567 UP
150gm Panipat Bangalore Normal Rail 60 25-03-2010 27-03-10 Ramesh Sec-12, 145B 233267 Karnataka
200gm Mumbai Ambala Urgent Air 70 28-03-2010 29-03-10 Rahul Sec-12, 165B 344234 Punjab
100gm Bokaro Ambala Most Urgent Air 100 02-04-2010 03-04-10 John Sec-1, 265B 344455 Punjab
300gm Bokaro Kanpur Urgent Rail 50 02-04-2010 04-04-10 Rajesh Sec-2, 65B 238965 UP
R_Countr R_Fax R_Email HandlerI H_Name H_Add H_Phone H_Email
y D
India 31313 sw@gmail.com H001 HN1 HA1 HP1 HE1
India 54325 ra@gmail.com H002 HN2 HA2 HP2 HE2
India 434342 ra@gmail.com H001 HN1 HA1 HP1 HE1
India 324432 jo@gmail.com H003 HN3 HA3 HP3 HE3
India 545455 raj@gmail.com H004 HN4 HA4 HP4 HE4
Justification
First identify the repeating groups in the un normalized table that is in above according to the property of shipment details.
Now Package shipment table is considered to be first normal form (1NF).
Now package shipment table states atomicity values.
1st NF of this table disallows having set of values, a tuple of values or combination of both as an attribute value.
As a sequence, there are multiple values at the intersection of certain rows and column.
For Example:
There are two value of packet Id (CN001, CN002) that is assign corresponding to sender id.
So transfer an normalized table into 1st NF, it is insure that there is a single value at the interaction of each row and column. This is done
by removing repeating group.
3.3. Dependency
Partial Dependency on
SenderID
Full Functional Dependency
on ConsignNo
Transitive among Type,
Weight, Express
Partial Dependency on
HandlerID
Justification
The above dependency sketch shows about different functional dependency to their related “key attribute” and “non key attribute”
Note:
Determinant
Dependent
Determinant Dependent Attributes Dependency Type
ConsignNo, SenderID Sender_ID, Type, Content, Weight, SCity, DCity, Mode, Express, Cost, Full Functional Dependency
CDtae, RDate, HandlerID
(Primary Key)
Sender_ID S_Name, S_Address, S_State, S_Pincode, S_Country, S_Phone, S_Fax, Partial Dependency
S_Email
HandlerID H_Name, H_Add, H_Phone, H_Email Partial Dependency
Express Cost Transitive Dependency
Weight Cost Transitive Dependency
Type Cost Transitive Dependency
3.4. 2nd Normalization Form
Table 1: Shipment
ConsignN Type Content Weight Scity Dcity Mode Express Cost CDate RDate Sender_ID HandlerID
o
CN001 DOX --------- 100gm Panipat Kanpur Urgent Air 50 25-03-2010 25-03-10 CS001 H001
CN002 DOX --------- 150gm Panipat Bangalore Normal Rail 60 25-03-2010 27-03-10 CS001 H002
CN003 Non ---------- 200gm Mumbai Ambala Urgent Air 70 28-03-2010 29-03-10 CS002 H001
Dox
CN004 Dox -------------- 100gm Bokaro Ambala Most Air 100 02-04-2010 03-04-10 CS003 H003
Urgent
CN005 Dox -------------- 300gm Bokaro Kanpur Urgent Rail 50 02-04-2010 04-04-10 CS003 H004
Table 2: Sender
Sender_I S_Name S_Address S_State S_Pincode S_Country S_Phone S_Fax S_Email
D
CS001 Sachine sec-15, Haryana 132103 India 9999999999 23456778 sa@gmail.com
115B
CS002 John Sec-12, 234 Maharashtra 334862 India 9922223333 244234 j@gmail.com
B
CS003 Ramesh Sec-22, 24 Jharkhand 829301 India 34567345666 4345345 r@gmail.com
B
Table 3: Handler
HandlerID H_Name H_Add H_Phone H_Email
H001 HN1 HA1 HP1 HE1
H002 HN2 HA2 HP2 HE2
H003 HN3 HA3 HP3 HE3
H004 HN4 HA4 HP4 HE4
Table 4: Recipient
ConsignNo R_Name R_Address R_Pincode R_State R_Country R_Fax R_Email
CN001 Sehwag sec-25, 222D, 183567 UP India 31313 sw@gmail.com
CN002 Ramesh Sec-12, 145B 233267 Karnataka India 54325 ra@gmail.com
CN003 Rahul Sec-12, 165B 344234 Punjab India 434342 ra@gmail.com
CN004 John Sec-1, 265B 344455 Punjab India 324432 jo@gmail.com
CN005 Rajesh Sec-2, 65B 238965 UP India 545455 raj@gmail.com
Justification
The above tables are in 2nd NF and it is output of 1st NF.
It is based on the concept of full functional dependency.
Second normal form applies to relations with composite keys that is relations with
primary key composed of and more attributes.
In the above table, SenderID + ConsignNo is acting as a primary key.
Here it has to remove all the partial dependencies present.
1. [fd1] >> {SenderID, ConsignNo }- Uniquely determine other attributes (Primary
Key)
2. [fd2] >> {SenderID, DispatchDate, DeliveryDate }- ConsignNo, Cost, Type, Receiver
Name, Add ( Candidate Key )
3. [fd3] >> {Consign, DispatchDate, DeliveryDate }- SenderID, Sender Name (more
info) ( Candidate Key )
4. [fd4] >> {Send erID}- S_Name, S_Address, S_State, S_Pincode, S_Country,
S_Phone, S_Fax, S_Email ( Partial Dependency)
5. [fd5] >> {HandlerID}- H_Name, H_Add, H_Phone, H_Email ( Partial Dependency)
6. [fd6] >> {Type, Weight, Express} – Cost (Transitive Dependency)
3.5. 3rd Normalization Form
Table 1: Shipment
ConsignN Type Content Weight Scity Dcity Mode Express Cost CDate RDate Sender_ID HandlerID
o
CN001 DOX --------- 100gm Panipat Kanpur Urgent Air 50 25-03-2010 25-03-10 CS001 H001
CNd002 DOX --------- 150gm Panipat Bangalore Normal Rail 60 25-03-2010 27-03-10 CS001 H002
CN003 Non ---------- 200gm Mumbai Ambala Urgent Air 70 28-03-2010 29-03-10 CS002 H001
Dox
CN004 Dox -------------- 100gm Bokaro Ambala Most Air 100 02-04-2010 03-04-10 CS003 H003
Urgent
CN005 Dox -------------- 300gm Bokaro Kanpur Urgent Rail 50 02-04-2010 04-04-10 CS003 H004
Table 2: Weight Table 3: Express Table 4: Type
Weight Cost Express Cost Type Cost
50 gm 10 Road 10 Dox 10
100 gm 20 Rail 20 Non Dox 20
200 gm 25 Air 30
250 gm 30 Ocean 30
Table 5: Sender
Sender_ID S_Name S_Address S_State S_Pincode S_Country S_Phone S_Fax S_Email
CS001 Sachine sec-15, 115B Haryana 132103 India 9999999999 23456778 sa@gmail.com
CS002 John Sec-12, 234 Maharashtra 334862 India 9922223333 244234 j@gmail.com
B
CS003 Ramesh Sec-22, 24 B Jharkhand 829301 India 34567345666 4345345 r@gmail.com
Table 6: Handler
HandlerID H_Name H_Add H_Phone H_Email
H001 HN1 HA1 HP1 HE1
H002 HN2 HA2 HP2 HE2
H003 HN3 HA3 HP3 HE3
H004 HN4 HA4 HP4 HE4
Table 7: Recipient
ConsignNo R_Name R_Address R_Pincode R_State R_Country R_Fax R_Email
CN001 Sehwag sec-25, 222D, 183567 UP India 31313 sw@gmail.com
CN002 Ramesh Sec-12, 145B 233267 Karnataka India 54325 ra@gmail.com
CN003 Rahul Sec-12, 165B 344234 Punjab India 434342 ra@gmail.com
CN004 John Sec-1, 265B 344455 Punjab India 324432 jo@gmail.com
CN005 Rajesh Sec-2, 65B 238965 UP India 545455 raj@gmail.com
Justification
The above tables are in 3rd NF and it is output of 2nd NF and in which no non-primary key is
transitively dependent on the non key attributes.
Although 2nd relation have less redundancy than 1 NF, but it still contain some update
anomalies.
