Database Normalization
Database normalization is a process used in relational database design to organize
data efficiently.
The main goal is to:
Reduce data redundancy (duplicate data)
Improve data integrity
Avoid insertion, update, and deletion anomalies
Make the database easier to maintain
Normalization divides large tables into smaller related tables and connects them
using keys.
Why Normalization is Important
Without normalization:
Same data may be repeated many times
Updating data becomes difficult
Incorrect or inconsistent data may appear
Storage space is wasted
With normalization:
Data becomes organized
Redundancy is minimized
Queries become more reliable
Database maintenance becomes easier
Key Terms Before Learning Normalization
Term Meaning
Attribute Column in a table
Tuple Row in a table
Primary Key Unique identifier of a row
Foreign Key Column used to connect tables
Functional Dependency Relationship between attributes
Types of Normalization
The common normal forms are:
1. First Normal Form (1NF)
2. Second Normal Form (2NF)
3. Third Normal Form (3NF)
4. Boyce-Codd Normal Form (BCNF)
5. Fourth Normal Form (4NF)
6. Fifth Normal Form (5NF)
First Normal Form (1NF)
Rule of 1NF
A table is in 1NF if:
Each column contains atomic (single) values
No repeating groups or multiple values in one column
Each row is unique
Example Before 1NF
StudentID StudentName Courses
1 Ali DBMS, Java
2 Ahmed Networking
Problem:
Multiple values exist in the Courses column.
Convert to 1NF
StudentID StudentName Course
1 Ali DBMS
1 Ali Java
2 Ahmed Networking
Now:
Each field contains only one value.
Second Normal Form (2NF)
Rule of 2NF
A table is in 2NF if:
It is already in 1NF
No partial dependency exists
Partial dependency means:
A non-key attribute depends on only part of a composite primary key.
Example Before 2NF
StudentID CourseID StudentName CourseName
1 C1 Ali DBMS
1 C2 Ali Java
Primary Key = (StudentID, CourseID)
Problem:
StudentName depends only on StudentID
CourseName depends only on CourseID
This creates redundancy.
Convert to 2NF
STUDENT Table
StudentID StudentName
1 Ali
COURSE Table
CourseID CourseName
C1 DBMS
C2 Java
ENROLLMENT Table
StudentID CourseID
1 C1
1 C2
Now: Every non-key attribute fully depends on the whole key.
Third Normal Form (3NF)
Rule of 3NF
A table is in 3NF if:
It is already in 2NF
No transitive dependency exists
Transitive dependency means:
A non-key attribute depends on another non-key attribute.
Example Before 3NF
EmpID EmpName DeptID DeptName
1 Hassan D1 IT
Problem:
DeptName depends on DeptID
DeptID depends on EmpID
So:
EmpID → DeptID → DeptName
This is transitive dependency.
Convert to 3NF
EMPLOYEE Table
EmpID EmpName DeptID
1 Hassan D1
DEPARTMENT Table
DeptID DeptName
D1 IT
Now:
Non-key attributes depend only on the primary key.
Boyce-Codd Normal Form (BCNF)
BCNF is an advanced version of 3NF.
Rule of BCNF
For every dependency:
X→Y
X must be a super key.
Example
Teacher Subject Room
Ahmed DBMS R1
Ali Java R2
If:
One teacher teaches one subject
One room is assigned to one subject
Then redundancy may occur.
BCNF separates such dependencies into multiple tables.
Fourth Normal Form (4NF)
Rule of 4NF
A table must:
Already be in BCNF
Have no multi-valued dependencies
Example
Student Hobby Language
Ali Football English
Student Hobby Language
Ali Reading Arabic
Problem:
Hobbies and languages are independent multi-valued attributes.
Convert to 4NF
STUDENT_HOBBY
Student Hobby
Ali Football
Ali Reading
STUDENT_LANGUAGE
Student Language
Ali English
Ali Arabic
Fifth Normal Form (5NF)
Rule of 5NF
A table should not have join dependencies.
5NF removes redundancy caused by complex many-to-many relationships.
Used mainly in advanced database systems.
Normalization Flow Summary
UNF → 1NF → 2NF → 3NF → BCNF → 4NF → 5NF
Functional Dependency
A functional dependency means:
A→B
Meaning:
Attribute A determines attribute B.
Example:
StudentID → StudentName
If we know StudentID, we can identify StudentName.
Types of Dependencies
1. Full Dependency
Attribute depends on the entire primary key.
Example:
(StudentID, CourseID) → Grade
2. Partial Dependency
Attribute depends on part of a composite key.
Example:
StudentID → StudentName
3. Transitive Dependency
Non-key attribute depends on another non-key attribute.
Example:
EmpID → DeptID → DeptName
Advantages of Normalization
Advantage Description
Reduces redundancy Avoids repeated data
Improves integrity Data becomes accurate
Advantage Description
Easier updates Change data in one place
Better storage Saves space
Easier maintenance Database becomes organized
Disadvantages of Normalization
Disadvantage Description
Too many tables Complex structure
More joins required Queries may become slower
Difficult for beginners Requires deeper understanding
Real-World Example
Unnormalized Table
OrderID CustomerName Products
1 Ali Mouse, Keyboard
Problems:
Multiple products in one field
Repeated customer data
After Normalization
CUSTOMER
CustomerID CustomerName
C1 Ali
PRODUCT
ProductID ProductName
P1 Mouse
P2 Keyboard
ORDER
OrderID CustomerID
1 C1
ORDER_PRODUCT
OrderID ProductID
1 P1
1 P2
Oracle SQL Example
Create Student Table
CREATE TABLE STUDENT (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(50)
);
Create Course Table
CREATE TABLE COURSE (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50)
);
Create Enrollment Table
CREATE TABLE ENROLLMENT (
StudentID NUMBER,
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID),
FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID)
);
Quick Comparison of Normal Forms
Normal Form Removes
1NF Repeating groups
2NF Partial dependency
3NF Transitive dependency
BCNF Advanced dependency issues
4NF Multi-valued dependency
5NF Join dependency
Conclusion
Normalization is one of the most important concepts in database systems. It helps
design efficient, organized, and reliable databases by reducing redundancy and
improving integrity.
The most commonly used normal forms in real-world systems are:
1NF
2NF
3NF
Advanced systems may also use:
BCNF
4NF
5NF