NORMALIZATION
NORMALIZATION
• The process of producing a simpler and more reliable database
structure is called normalization.
• It is used to create a set of relations for storing data without
redundancy and inconsistency.
TYPES OF NORMALIZATION
• There are three types of normalization:
• First normal form(1NF)
• Second normal form(2NF)
• Third normal form(3NF)
• Each normal form has certain requirements or condition. These
conditions must be fulfilled to bring the database in that particular
normal form.
TYPES OF NORMALIZATION
FIRST NORMAL FORM(1NF)
• A relation is in first normal form if every intersection of row and
column contains atomic values only.
• It means that the relation does not contain any repeating group.
• A repeating group is a set of one or more data items that may occur a
variable number of times in a record.
EXAMPLE
DeptID DeptName EmpID EmpName
10 Management E01 Usman Khalil
E02 Abdullah
20 Finance E10 Ali Ahmed
E11 Mahmood Abbas
30 IT E25 Hamid Ali
DeptID DeptName DeptID EmpID EmpName
10 Management 10 E01 Usman Khalil
20 Finance 10 E02 Abdullah
30 IT 20 E10 Ali Ahmed
20 E11 Mahmood Abbas
30 E25 Hamid Ali
SECOND NORMAL FORM(2NF)
• A relation is in second normal form if it is in 1NF.
• And every non-key attribute is fully dependent on the primary key.
• It means that all partial functional dependencies must be removed.
• A partial dependency occur when a relation has composite key and
only part of the key can be used to determine one or more other
attributes.
• All attributes of partial dependency are placed in a seprate relation to
convert it from 1NF to 2NF.
EXAMPLE
EmpID Name DeptName Salary CourseTitle DateCompleted
100 Ahmad Marketing 25000 Advertising 19/06/2006
100 Ahmad Marketing 25000 Surveys 10/09/2006
140 Nazir Accounting 19000 MS Excel 12/08/2007
110 Hamid IT 24000 Oracle 14/07/2006
110 Hamid IT 24000 Java 22/09/2006
190 Rashid Finance 30000 Investment 20/06/2007
150 Hussain Marketing 25000 Advertising 19/06/2007
150 Hussain Marketing 25000 Ecommerce 20/09/2007
CONTINUE…
EmpID Name DeptName Salary EmpID CourseTitle DateCompleted
100 Ahmad Marketing 25000 100 Advertising 19/06/2006
140 Nazir Accounting 19000 100 Surveys 10/09/2006
110 Hamid IT 24000 140 MS Excel 12/08/2007
190 Rashid Finance 30000 110 Oracle 14/07/2006
150 Hussain Marketing 25000 110 Java 22/09/2006
190 Investment 20/06/2007
150 Advertising 19/06/2007
150 Ecommerce 20/09/2007
THRID NORMAL FORM(3NF)
• A relation is in third normal from if it is in 2NF.
• And no transitive dependency exists.
• The transitive dependency occur when a non-key attribute can
determine any other non-key attribute.
• All attributes of transitive dependency are placed in a separate
relation to convert it from 2NF to 3NF.
• A relationship is created between the existing relation and the new
relation.
EXAMPLE
CustomerID Name SalesmanID Salesman
10 Ahsan S10 Ahmad
20 Babar S20 Bashir
30 Ali S10 Ahmad
40 Daood S30 Khalid
50 Raza S20 Bashir
60 Farooq S40 Munir
CONTINUE…
CustomerID Name SalesmanID SalesmanID Salesman
10 Ahsan S10 S10 Ahmad
20 Babar S20 S20 Bashir
30 Ali S10 S30 Khalid
40 Daood S30 S40 Munir
50 Raza S20
60 Farooq S40