What is Normalization?
Normalization is the process of organizing data in a database to reduce
redundancy and improve data integrity.
It is applied step by step using Normal Forms
Some Database Normal Forms Are :-
1. First Normal Form (1NF): Eliminating Duplicate Records
A table is in 1NF if it satisfies the following conditions:
All columns contain atomic values (i.e., indivisible values).
Each row is unique (i.e., no duplicate rows).
Each column has a unique name.
The order in which data is stored does not matter.
Example of 1NF Violation:
2. Second Normal Form (2NF): Eliminating Partial Dependency
A relation is in 2NF if it satisfies the conditions of 1NF and additionally. No partial dependency
exists, meaning every non-prime attribute (non-key attribute) must depend on the entire primary
key, not just a part of it.
Example:
3. Third Normal Form (3NF): Eliminating Transitive Dependency
A relation is in 3NF if
It satisfies 2NF
Additionally, there are no transitive dependencies . In simpler terms, non-prime attributes
should not depend on other non-prime attributes.
Every non-key attribute must depend only on the primary key.
Example:
4. Boyce-Codd Normal Form (BCNF): The Strongest Form of 3NF
• BCNF does not allow dependencies between attributes that belong to candidate keys.
• BCNF is a refinement of the third normal form in which it drops the restriction of a non-
key attribute from the 3rd normal form.
• Third normal form and BCNF are not same if the following conditions are true:
– The table has two or more candidate keys
– At least two of the candidate keys are composed of more than one attribute
– The keys are not disjoint i.e. The composite candidate keys share some attributes
Example:
Old Scheme {City, Street, ZipCode }
New Scheme1 {ZipCode, Street}
New Scheme2 {City, Street}
• Loss of relation {ZipCode} {City}
Alternate New Scheme1 {ZipCode, Street }
Alternate New Scheme2 {ZipCode, City}
5. Fourth Normal Form (4NF): Removing Multi-Valued Dependencies
A table is in 4NF if it is in BCNF and has no multi-valued dependencies. A multi-valued
dependency occurs when one attribute determines another, and both attributes are independent of
all other attributes in the table.
Example:
(Not in 4NF)
Scheme {Employee, Skill, ForeignLanguage}
1. Primary Key {Employee, Skill, Language }
2. Each employee can speak multiple languages
3. Each employee can have multiple skills
4. Thus violates 4NF
(Convert to 4NF)
Old Scheme {Employee, Skill, ForeignLanguage}
New Scheme {Employee, Skill}
New Scheme {Employee, ForeignLanguage}
6. Fifth Normal Form (5NF): Eliminating Join Dependency
5NF is achieved when a table is in 4NF and all join dependencies are removed. This form ensures
that every table is fully decomposed into smaller tables that are logically connected without
losing information.
Example:
If a table contains (StudentID, Course, Instructor) and there is a dependency where all
combinations of these columns are needed for a specific relationship, we would split them into
smaller tables to remove redundancy.