Database Normalization:
Step-by-Step Guide
An ICT Project Management Case Study from Unnormalized Form to 3NF
Prepared for: Database System Students (BRIM, BECS, BCS, BCSe, BIT, BA-IT)
Date: March 16, 2026
Introduction
Database normalization is a systematic process of organizing data in a relational
database to minimize redundancy and improve data integrity. This guide
presents a practical case study that demonstrates the transformation of an
unnormalized table through First Normal Form (1NF), Second Normal Form
(2NF), and Third Normal Form (3NF).
The scenario presented here involves an ICT company project management
system---a relatable example that illustrates common database design challenges
and their solutions in the software development industry.
The Scenario: ICT Project
Management System
Imagine an ICT company in Dar es Salaam that maintains software development
project records in a spreadsheet. Initially, the system was designed without
proper database principles, resulting in an unnormalized structure with several
data management issues.
Unnormalized Table (UNF)
Before normalization, the company uses a single table to track all project
assignment information:
Develope Develope Develope ProjectLi ClientLis StartDat
rID rName rPhone st t es
WebApp, TanzaniaB
Amina 0755- 10-Jan,
D001 MobileAp ank,
Kibwana 334455 15-Jan
p Vodacom
John 0756- TanzaniaB
D002 WebApp 10-Jan
Mpemba 667788 ank
MobileAp
Sarah 0757- p, Vodacom, 15-Jan,
D003
Komba 990011 CloudSyst TTCL 20-Jan
em
Hassan 0758- CloudSyst
D004 TTCL 20-Jan
Ally 223344 em
Table 1: Unnormalized ProjectAssignments table showing multiple values in
single cells
Problems with the Unnormalized
Table
This table structure presents several critical problems:
1. Multi-valued attributes: The ProjectList, ClientList, and StartDates
columns contain multiple values separated by commas, violating the
atomic value principle.
2. Update anomalies: If TanzaniaBank changes its contact information, it
must be updated in multiple rows, increasing the risk of inconsistency.
3. Insertion anomalies: Cannot add a new project to the company's catalog
without assigning a developer, or add a developer without assigning them
to a project.
4. Deletion anomalies: If Hassan Ally leaves the company and we delete
his record, we lose all information about the CloudSystem project if he's
the only developer.
5. Data redundancy: Developer names, phone numbers, project names, and
client names are repeated unnecessarily.
6. Query complexity: Searching for all developers working on a specific
project requires parsing comma-separated values.
Step 1: First Normal Form (1NF)
Rule for 1NF
A table is in First Normal Form if:
1. All columns contain atomic (indivisible) values---no multi-valued attributes
2. Each column contains values of a single type
3. Each column has a unique name
4. The order in which data is stored does not matter
5. There are no repeating groups
Transformation to 1NF
To achieve 1NF, we eliminate repeating groups by creating separate rows for
each project assignment. Each cell now contains only a single value.
Develope Develope Develope ProjectN ClientNa StartDat
rID rName rPhone ame me e
Amina 0755- TanzaniaB
D001 WebApp 10-Jan
Kibwana 334455 ank
Amina 0755- MobileAp
D001 Vodacom 15-Jan
Kibwana 334455 p
John 0756- TanzaniaB
D002 WebApp 10-Jan
Mpemba 667788 ank
Sarah 0757- MobileAp
D003 Vodacom 15-Jan
Komba 990011 p
Sarah 0757- CloudSyst
D003 TTCL 20-Jan
Komba 990011 em
Hassan 0758- CloudSyst
D004 TTCL 20-Jan
Ally 223344 em
Table 2: ProjectAssignments table in First Normal Form (1NF)
Primary Key in 1NF
The composite primary key for this table is: (DeveloperID, ProjectName,
StartDate)
This combination uniquely identifies each row---one record per developer per
project per start date.
Achievements and Remaining Issues
Achievements:
• All cells contain atomic values
• No repeating groups
• Each row is uniquely identifiable
Remaining Issues:
• Significant data redundancy (developer names, phone numbers, client
names are repeated)
• Partial dependencies exist (some attributes depend on only part of the
primary key)
Step 2: Second Normal Form (2NF)
Rule for 2NF
A table is in Second Normal Form if:
1. It is already in First Normal Form (1NF)
2. All non-key attributes are fully functionally dependent on the entire
primary key (no partial dependencies)
Partial dependency occurs when a non-key attribute depends on only part of a
composite primary key, rather than the whole key.
Identifying Partial Dependencies
Let's examine the functional dependencies in our 1NF table with composite key
(DeveloperID, ProjectName, StartDate):
1. DeveloperName, DeveloperPhone depend only on DeveloperID
(partial dependency)
2. ClientName depends only on ProjectName (partial dependency)
3. The project assignment itself depends on the complete key (DeveloperID
+ ProjectName + StartDate)
Transformation to 2NF
To eliminate partial dependencies, we decompose the table into three separate
tables:
Table 1: Developers
DeveloperID DeveloperName DeveloperPhone
D001 Amina Kibwana 0755-334455
D002 John Mpemba 0756-667788
D003 Sarah Komba 0757-990011
D004 Hassan Ally 0758-223344
Table 3: Developers table - Primary Key: DeveloperID
Table 2: Projects
ProjectName ClientName
WebApp TanzaniaBank
MobileApp Vodacom
CloudSystem TTCL
Table 4: Projects table - Primary Key: ProjectName
Table 3: Assignments
DeveloperID ProjectName StartDate
D001 WebApp 10-Jan
D001 MobileApp 15-Jan
D002 WebApp 10-Jan
D003 MobileApp 15-Jan
D003 CloudSystem 20-Jan
D004 CloudSystem 20-Jan
Table 5: Assignments table - Primary Key: (DeveloperID, ProjectName,
StartDate); Foreign Keys: DeveloperID references Developers, ProjectName
references Projects
Achievements and Remaining Issues
Achievements:
• Eliminated partial dependencies
• Reduced data redundancy significantly
• Developer information stored once
• Project information stored once
• Easier to update developer or project details
Remaining Issues:
• Transitive dependencies may exist (non-key attributes depending on other
non-key attributes)
Step 3: Third Normal Form (3NF)
Rule for 3NF
A table is in Third Normal Form if:
1. It is already in Second Normal Form (2NF)
2. There are no transitive dependencies (non-key attributes must not depend
on other non-key attributes)
Transitive dependency occurs when a non-key attribute depends on another
non-key attribute, rather than directly on the primary key.
Identifying Transitive Dependencies
Let's examine the Projects table:
ProjectName ClientName
WebApp TanzaniaBank
MobileApp Vodacom
CloudSystem TTCL
Functional dependencies:
1. ProjectName → ClientName (appears to be direct, but...)
In reality, client information (such as email, address, contact person, phone
number) depends on the client themselves, not the project. This creates a
transitive dependency:
ProjectName →ClientName →ClientEmail, ClientAddress, etc.
If TanzaniaBank changes their email address, we would need to update every
project they commission. This indicates a transitive dependency.
Transformation to 3NF
To eliminate transitive dependencies, we separate client information into its own
table:
Table 1: Developers (unchanged)
DeveloperID DeveloperName DeveloperPhone
D001 Amina Kibwana 0755-334455
D002 John Mpemba 0756-667788
D003 Sarah Komba 0757-990011
D004 Hassan Ally 0758-223344
Table 6: Developers table - Primary Key: DeveloperID
Table 2: Clients (new table)
ClientID ClientName ClientEmail
contact@tanzaniabank.
C001 TanzaniaBank
[Link]
C002 Vodacom info@[Link]
C003 TTCL support@[Link]
Table 7: Clients table - Primary Key: ClientID
Table 3: Projects (modified)
ProjectName ClientID
WebApp C001
MobileApp C002
CloudSystem C003
Table 8: Projects table - Primary Key: ProjectName; Foreign Key: ClientID
references Clients
Table 4: Assignments (unchanged)
DeveloperID ProjectName StartDate
D001 WebApp 10-Jan
D001 MobileApp 15-Jan
D002 WebApp 10-Jan
D003 MobileApp 15-Jan
D003 CloudSystem 20-Jan
D004 CloudSystem 20-Jan
Table 9: Assignments table - Primary Key: (DeveloperID, ProjectName,
StartDate); Foreign Keys: DeveloperID references Developers, ProjectName
references Projects
Final Achievements
The database is now in Third Normal Form:
1. No multi-valued attributes - All cells contain atomic values
2. No partial dependencies - All non-key attributes depend on the entire
primary key
3. No transitive dependencies - Non-key attributes depend only on the
primary key, not on other non-key attributes
4. Minimal redundancy - Each piece of information is stored in exactly one
place
5. Data integrity - Updates, insertions, and deletions no longer cause
anomalies
6. Scalability - Easy to add new developers, projects, clients, or
assignments
Summary: Transformation Journey
Visual Summary
Unnormalized Form (UNF)
↓ Eliminate repeating groups, ensure atomic values
First Normal Form (1NF)
↓ Eliminate partial dependencies
Second Normal Form (2NF)
↓ Eliminate transitive dependencies
Third Normal Form (3NF)
Key Principles to Remember
1. 1NF: Each cell contains one value, no repeating groups
2. 2NF: Every non-key attribute depends on the whole key
3. 3NF: Every non-key attribute depends on nothing but the key
Benefits of Normalization
1. Eliminates redundancy: Each fact is stored once
2. Prevents update anomalies: Changing data in one place updates
everywhere
3. Prevents insertion anomalies: Can add entities independently
4. Prevents deletion anomalies: Deleting one entity doesn't delete
unrelated data
5. Improves data integrity: Constraints and relationships enforce
consistency
6. Enhances query efficiency: Smaller, focused tables are faster to search
Understanding Data Anomalies
Update Anomaly
Problem in unnormalized/2NF design: If we wanted to update a client's
contact information (e.g., TanzaniaBank changes its email address), we would
need to update multiple rows in the Projects table for every project
commissioned by that client.
Solution in 3NF: Client information is stored in a separate Clients table, so we
only need to update one row in the Clients table, and the change automatically
applies to all projects for that client through the foreign key relationship.
Insertion Anomaly
Problem in unnormalized design: We cannot add a new project to the
company's project catalog without assigning a developer to it first, because
project information is embedded within assignment records.
Solution in 3NF: The Projects table exists independently of the Assignments
table, so we can insert a new project with its client information even if no
developer has been assigned yet. The project simply won't appear in the
Assignments table until a developer starts working on it.
Deletion Anomaly
Problem in unnormalized design: If a developer leaves the company and we
delete all their assignment records, we lose all information about that developer
(name, phone number).
Solution in 3NF: Developer information is stored independently in the
Developers table. When we delete assignment records from the Assignments
table (when a developer leaves), the developer's information can remain in the
Developers table for historical records. The company can maintain a complete
record of past employees even after all their project assignments are removed.
Classroom Discussion Questions
1. What would happen in the unnormalized table if TanzaniaBank changed
their contact information? How many rows would need updating?
2. Why is it important that developer phone numbers are stored in a
separate Developers table?
3. Can you think of a scenario where you might want to store developer
skills or certifications? Which table would be most appropriate?
4. What new table would you create if projects could have multiple clients
(joint ventures)? Hint: Think many-to-many relationships.
5. How would you modify the database to track project milestones,
deadlines, or completion percentages?
6. What would happen if Ms. Amina teaches both Physics and Chemistry?
Does our current 3NF design handle this?
Practical Exercise
Try normalizing this unnormalized table for a hospital appointment system:
AppointmentDa
PatientID PatientName DoctorList
tes
Dr. Mwangi, Dr.
P001 Ahmed Said 10-Mar, 15-Mar
Komba
P002 Fatma Ali Dr. Mwangi 12-Mar
Follow the same process:
1. Convert to 1NF by eliminating multi-valued attributes
2. Convert to 2NF by eliminating partial dependencies
3. Convert to 3NF by eliminating transitive dependencies
Advanced Topic: Many-to-Many
Relationships
Question: If one project can have multiple clients (e.g., a joint project for both
TanzaniaBank and CRDB), how would you modify the 3NF design?
Answer:
To accommodate multiple clients per project, we need to create a many-to-
many relationship between Projects and Clients using a junction table.
Modified 3NF Design:
Remove ClientID from Projects table, so Projects contains only ProjectName.
New Table: ProjectClients (junction table)
ProjectName ClientID
WebApp C001
MobileApp C002
CloudSystem C003
EnterpriseSuite C001
EnterpriseSuite C004
Table 10: ProjectClients junction table - Composite Primary Key: (ProjectName,
ClientID); Foreign Keys: ProjectName references Projects, ClientID references
Clients
Changes needed:
1. Remove the ClientID column from the Projects table
2. Projects table now contains only ProjectName (PK)
3. Create the ProjectClients junction table with composite primary key
(ProjectName, ClientID)
4. [Link] references [Link]
5. [Link] references [Link]
6. This allows one project to serve many clients, and one client to have many
projects
Example showing joint project:
The EnterpriseSuite project serves both TanzaniaBank (C001) and CRDB (C004),
represented by two rows in the ProjectClients table.
Conclusion
Database normalization is a fundamental skill for database designers and
developers. By systematically applying normalization rules (1NF, 2NF, 3NF), you
can transform poorly structured data into a well-organized, efficient, and
maintainable database system.
Remember: normalization is about organizing data to minimize redundancy
while ensuring data integrity and supporting efficient queries. While higher
normal forms exist (BCNF, 4NF, 5NF), Third Normal Form is sufficient for most
practical applications.
The golden rule: Each fact should be stored in one place, and every non-key
attribute should depend on the key, the whole key, and nothing but the key.
Real-World Applications in ICT
Industry
This normalization structure relates directly to actual software systems:
• Project Management Tools: Jira, Asana, Trello all use normalized
databases similar to this structure
• Resource Allocation Systems: Companies like Microsoft, Google track
developer assignments using these principles
• Time Tracking Software: Harvest, Toggl Track use normalized schemas
for project-client-developer relationships
• CRM Systems: Salesforce and similar platforms normalize client, project,
and team member data
• Freelance Platforms: Upwork, Fiverr manage complex many-to-many
relationships between developers, projects, and clients
Career relevance for BIT/BCS/BECS students:
• Database design is a core skill for software developers and IT consultants
• Understanding normalization prevents costly redesigns in production
systems
• These concepts appear in technical interviews at major tech companies
• Well-normalized databases improve system performance and
maintainability
Additional Resources
For further study, explore these topics:
1. Boyce-Codd Normal Form (BCNF)
2. Fourth Normal Form (4NF) - multi-valued dependencies
3. Fifth Normal Form (5NF) - join dependencies
4. Denormalization - when and why to intentionally violate normal forms for
performance
5. Database design patterns and best practices
6. Entity-Relationship Diagrams (ERD) for visual database modeling