A Comprehensive Case Study on Database

Normalization

1. Introduction: The Data Dilemma

This case study provides a practical, step-by-step demonstration of database

normalization, a formal process for organizing data in a relational database. Our
objective is to take a single, poorly structured "flat-file" table and progressively refine it
into a robust, efficient, and stable set of tables.

Why Normalize?

The primary goal of normalization is to reduce and eliminate data redundancy.

Redundancy not only wastes space but, more importantly, leads to data integrity
problems known as anomalies:
● Insertion Anomaly: The inability to add new data about one entity (e.g., a new
employee) without also having data for an unrelated entity (e.g., a project they
haven't been assigned to yet).
● Update Anomaly: A single logical change (e.g., an item's description is updated)
requires updating multiple rows. If any row is missed, the data becomes inconsistent.
● Deletion Anomaly: Deleting a single record (e.g., a customer's final order)
unintentionally removes all information about another entity (e.g., it erases the
existence of that customer from the database).

We will progress from Unnormalized Form (UNF) to Sixth Normal Form (6NF), illustrating
the practical purpose of each step.
2. The Initial Scenario: Unnormalized Form (UNF)

We begin with data gathered into a single spreadsheet or table. This structure is in
Unnormalized Form (UNF) because it violates the most basic rule of relational
databases.

Key Problem: The Repeating_Group column is not atomic. It's a "repeating group" or a
table-within-a-cell, holding multiple distinct facts in one field. This makes it impossible to
query effectively (e.g., "How many entities use 'K2a'?") and is the root of all anomalies.

Table: Data_UNF

Primary Key 1 (e.g., Entity Attribute A (e.g., Name) Repeating Group (Key 2,
ID) Attribute B, Attribute C,
Attribute D)

E101 Value A1 (K2a, B1, C1, D1), (K2b, B2,

C2, D2)

E102 Value A2 (K2a, B1, C1, D1), (K2c, B3,

C3, D3)

E103 Value A3 (K2b, B2, C2, D2)

3. Step 1: Achieving First Normal Form (1NF)

1NF Rule: All data values must be atomic (indivisible), and each row must be uniquely
identifiable by a Primary Key (PK).

To achieve 1NF, we "unroll" the repeating group, creating a new, separate row for each
item in that group.
Table: Data_1NF

Entity ID Attribute A Key 2 Attribute B Attribute C Attribute D

E101 Value A1 K2a B1 C1 D1

E101 Value A1 K2b B2 C2 D2

E102 Value A2 K2a B1 C1 D1

E102 Value A2 K2c B3 C3 D3

E103 Value A3 K2b B2 C2 D2

Analysis of 1NF:
● State: We are now in 1NF. All columns hold single values.
● Primary Key: No single column uniquely identifies a row. The only unique identifier is
the combination (Entity ID, Key 2). This is our composite primary key.
● Problems (Anomalies): The table is riddled with redundancy, leading to...
○ Update Anomaly: If the item K2a's Attribute B changes from B1 to B-new, we
must find every row where K2a appears (for E101 and E102) and update it.
Missing one leads to data inconsistency.
○ Insertion Anomaly: We cannot add a new entity (e.g., E104, Value A4) until it is
associated with at least one Key 2, because the Key 2 part of the primary key
cannot be NULL.
○ Deletion Anomaly: If entity E102 severs its relationship with K2c, we delete that
row. In doing so, we've lost all information that K2c is associated with B3, C3, and
D3.

The root cause of these anomalies is partial dependencies.

4. Step 2: Achieving Second Normal Form (2NF)

2NF Rule: The table must be in 1NF, and all non-key attributes must be fully functionally
dependent on the entire primary key. A partial dependency exists when a non-key
attribute depends on only a part of the composite primary key.

Analysis of Data_1NF (PK: (Entity ID, Key 2))

 ● Functional Dependencies (FDs):
○ Attribute A depends only on Entity ID. (This is a partial dependency.) $\to$ Must
be moved.
○ Attribute B, Attribute C, and Attribute D depend only on Key 2. (This is a partial
dependency.) $\to$ Must be moved.
○ (If we had a Grade or Status column, it would depend on both Entity ID and Key 2,
and would stay in the linking table.)

Action: We decompose the 1NF table by "pulling out" the partial dependencies into new
tables.

Table 1: Entity_A
| Entity ID (PK) | Attribute A |
| :--- | :--- |
| E101 | Value A1 |
| E102 | Value A2 |
| E103 | Value A3 |
Table 2: Entity_B
| Key 2 (PK) | Attribute B | Attribute C | Attribute D |
| :--- | :--- | :--- | :--- |
| K2a | B1 | C1 | D1 |
| K2b | B2 | C2 | D2 |
| K2c | B3 | C3 | D3 |
Table 3: Relationship_AB (This is a "linking" or "junction" table)
| Entity ID (FK, PK) | Key 2 (FK, PK) |
| :--- | :--- |
| E101 | K2a |
| E101 | K2b |
| E102 | K2a |
| E102 | K2c |
| E103 | K2b |
Analysis of 2NF:
● State: We are now in 2NF. All 1NF anomalies are solved. We can add a new entity to
Entity_A. Deleting from Relationship_AB doesn't lose item info.
● New Problem: The Entity_B table still has a problem. Attribute D (e.g., an owner's
location) appears to be dependent on Attribute C (e.g., an owner's name), not on Key
2. If the owner C1 manages 10 items, their location D1 would be repeated 10 times.
● The cause is a transitive dependency.
5. Step 3: Achieving Third Normal Form (3NF)

3NF Rule: The table must be in 2NF, and there must be no transitive dependencies.

A transitive dependency is when a non-key attribute depends on another non-key

attribute, rather than depending directly on the primary key. (i.e., $\text{PK} \to \
text{NonKey\_A} \to \text{NonKey\_B}$).

Analysis of 2NF Tables:

● Entity_A: In 3NF.
● Relationship_AB: In 3NF.
● Entity_B:
○ The Problem: Attribute D (Owner Location) depends on Attribute C (Owner
Name), which in turn depends on Key 2.
○ Transitive Dependency: $\text{Key 2} \to \text{Attribute C} \to \text{Attribute D}
$. The location belongs to the owner, not the item.

Action: We decompose the Entity_B table to remove the transitive dependency. We will
invent a surrogate key Key C to serve as the stable, reliable Primary Key for the new
"Owners" table.
● Why a surrogate key? Using Attribute C (the owner's name) as a primary key is a
bad idea. It might not be unique (two "John Smiths") and it can change (marriage,
typos), which would require a cascading update. A simple ID like C-01 is stable.

Final 3NF Schema:

Table 1: Entity_A (Unchanged)

| Entity ID (PK) | Attribute A |
| :--- | :--- |
| E101 | Value A1 |
| ... | ... |
Table 2: Owners_C (New)
| Key C (PK) | Attribute C (Owner Name) | Attribute D (Owner Location) |
| :--- | :--- | :--- |
| C-01 | Value C1 | Value D1 |
| C-02 | Value C2 | Value D2 |
| C-03 | Value C3 | Value D3 |
Table 3: Entity_B (Modified)
| Key 2 (PK) | Attribute B | Key C (FK) |
| :--- | :--- | :--- |
| K2a | B1 | C-01 |
| K2b | B2 | C-02 |
| K2c | B3 | C-03 |
Table 4: Relationship_AB (Unchanged)
| Entity ID (FK, PK) | Key 2 (FK, PK) |
| :--- | :--- |
| E101 | K2a |
| ... | ... |
Analysis of 3NF:
● State: We are now in 3NF. Each table describes a single entity (Entity A, Owners,
Entity B) or a relationship (Relationship AB). All original anomalies are solved.
● For most transactional (OLTP) databases, 3NF is the practical and desired goal.

6. Step 4: Boyce-Codd Normal Form (BCNF)

BCNF Rule: A stricter version of 3NF. For every non-trivial functional dependency $\
text{X} \to \text{Y}$, the determinant $\text{X}$ must be a superkey.

A 3NF table is not in BCNF only if it has multiple, overlapping candidate keys where one of
those keys is composite.

BCNF Mini-Case:
● Business Rules:
1. An employee can be assigned to multiple projects.
2. A project can have multiple employees.
3. A project has only one "Project Lead."
4. A Project Lead leads only one project.
● Table: Project_Assignments_3NF
| EmployeeID (PK) | ProjectID (PK) | ProjectLead |
| :--- | :--- | :--- |
| E101 | P-1 | L-A |
| E101 | P-2 | L-B |
| E102 | P-1 | L-A |
● Analysis:
○ Candidate Key: (EmployeeID, ProjectID)
○ FDs:
1. (EmployeeID, ProjectID) \to ProjectLead (Good: Determinant is a superkey)
2. ProjectID \to ProjectLead (Good: Per rule #3. But this is a partial dependency!
This table is only in 1NF. Let's try a better example.)

BCNF Mini-Case :
● Business Rules:
1. A user (UserID) can work on multiple specialized Topics.
2. A Specialist is assigned to each (UserID, Topic) pair.
 3. A Specialist handles only one Topic.
● Table: Specialist_Assignments_3NF
| UserID (PK) | Topic (PK) | SpecialistName |
| :--- | :--- | :--- |
| U100 | Physics | Dr. Fagin |
| U100 | CompSci | Dr. Kent |
| U200 | CompSci | Dr. Kent |
| U300 | Physics | Dr. Fagin |
● Analysis:
○ Candidate Key: (UserID, Topic)
○ FDs:
1. (UserID, Topic) \to SpecialistName (Good: Determinant is a superkey)
2. SpecialistName \to Topic (Bad: Per rule #3)
○ The Violation: The FD SpecialistName \to Topic violates BCNF because its
determinant, SpecialistName, is not a superkey (it doesn't uniquely identify a
row).
● Action (Achieving BCNF): Decompose the table.

Table 1: User_Specialists (BCNF)

| UserID (FK, PK) | SpecialistName (FK, PK) |
| :--- | :--- |
| U100 | Dr. Fagin |
| U100 | Dr. Kent |
| ... | ... |
Table 2: Specialist_Topics (BCNF)
| SpecialistName (PK) | Topic |
| :--- | :--- |
| Dr. Fagin | Physics |
| Dr. Kent | CompSci |

7. Step 5: Fourth Normal Form (4NF)

4NF Rule: The table must be in BCNF, and it must have no non-trivial multivalued
dependencies (MVDs).

An MVD ($\text{X} \to \to \text{Y}$) means one value of $\text{X}$ maps to a set of $\
text{Y}$ values, and this relationship is independent of other attributes. 4NF isolates
independent many-to-many relationships.

4NF Mini-Case:
● Business Rules:
1. A Project can have multiple Team_Members.
 2. A Project can require multiple Required_Parts.
3. Crucially: The team members and parts are independent. A part isn't assigned to
a specific team member; the project just has a list of members and a list of parts.
● Table: Project_Resources_BCNF (This table is in BCNF)
| ProjectID (PK) | Team_Member (PK) | Required_Part (PK) |
| :--- | :--- | :--- |
| P-100 | M-01 | Part-A |
| P-100 | M-01 | Part-B |
| P-100 | M-02 | Part-A |
| P-100 | M-02 | Part-B |
● The Problem: Redundancy. To add a new team member (M-03) to P-100, we must
add two new rows (one for Part-A and one for Part-B).
● The Violation: The table has two independent MVDs: ProjectID \to \to Team_Member
and ProjectID \to \to Required_Part.
● Action (Achieving 4NF): Decompose the MVDs into separate tables.

Table 1: Project_Members (4NF)

| ProjectID (FK, PK) | Team_Member (PK) |
| :--- | :--- |
| P-100 | M-01 |
| P-100 | M-02 |
Table 2: Project_Parts (4NF)
| ProjectID (FK, PK) | Required_Part (PK) |
| :--- | :--- |
| P-100 | Part-A |
| P-100 | Part-B |

8. Step 6: Fifth Normal Form (5NF)

5NF Rule (Project-Join Normal Form): The table must be in 4NF, and it must have no
join dependencies that are not implied by its candidate keys.

This is the most complex form, designed to handle 3-way (or n-way) relationships that
must be decomposed to avoid "spurious rows" (false data) on a re-join.

5NF Mini-Case:
● Business Rule (The Constraint): An Agent can be assigned to a Client for a specific
Service if and only if all three of these are true:
1. The Agent is certified for that Service.
2. The Client is subscribed to that Service.
3. The Agent is available for that Client.
● Action (Achieving 5NF): To enforce this complex rule, the single 3-way table (Agent,
 Client, Service) must be decomposed into three 2-way tables, each representing one
part of the rule.

Table 1: Agent_Services (5NF)

| AgentID (FK, PK) | ServiceID (FK, PK) |
| :--- | :--- |
Table 2: Client_Services (5NF)
| ClientID (FK, PK) | ServiceID (FK, PK) |
| :--- | :--- |
Table 3: Agent_Availability (5NF)
| AgentID (FK, PK) | ClientID (FK, PK) |
| :--- | :--- |
To find valid assignments, one must $\text{JOIN}$ all three tables. This ensures no
"spurious tuples" (false combinations) are ever generated, perfectly enforcing the rule.

9. Step 7: Sixth Normal Form (6NF)

6NF Rule: The table must be in 5NF, and it is decomposed to its irreducible minimum: a
primary key and, at most, one non-key attribute.

This is the ultimate level of decomposition, primarily used in temporal (history-tracking)

databases and data warehouses.

Action (Achieving 6NF):

● Let's take our 3NF Owners_C table:
Table: Owners_C (3NF)
| Key C (PK) | Attribute C (Owner Name) | Attribute D (Owner Location) |
| :--- | :--- | :--- |
| C-01 | Value C1 | Value D1 |
● To make this 6NF, we decompose it:

Table 1: Owner_Names (6NF)

| Key C (PK) | Attribute C (Owner Name) |
| :--- | :--- |
| C-01 | Value C1 |
Table 2: Owner_Locations (6NF)
| Key C (PK) | Attribute D (Owner Location) |
| :--- | :--- |
| C-01 | Value D1 |

● Benefit: Ultimate flexibility. If we need to track the history of an owner's location

 changes, we can add StartDate and EndDate columns only to the Owner_Locations
table. This is impossible in the 3NF form without creating complex, messy rows.

10. Conclusion: The Great Trade-off (Integrity vs. Performance)

We have successfully normalized our database, eliminating all data integrity anomalies.
● 1NF solved atomicity.
● 2NF solved partial dependencies.
● 3NF solved transitive dependencies.
● BCNF solved complex key anomalies.1

● 4NF solved independent multivalued dependencies.

● 5NF solved complex join dependencies.
● 6NF provides ultimate temporal flexibility.2

The Final Question: Why not normalize to 6NF always?

Performance.

Look at our 3NF schema. To get a simple report of "Entity Name, Item Name, and Owner
Name," we must perform three $\text{JOIN}$ operations (linking Entity_A,
Relationship_AB, Entity_B, and Owners_C). In a 6NF schema, this would be even worse, as
nearly every attribute requires another $\text{JOIN}$.

In the real world, database administrators often perform Denormalization. This is the
intentional process of re-introducing redundancy (e.g., going from 3NF back to 2NF) to
improve read performance.
● Example: A designer might add Attribute C (the owner's name) back into the Entity_B
table.
● Pro: The common report now only needs two $\text{JOIN}$s. It's much faster.
● Con: The update anomaly is back! Changing an owner's name requires updating it in
Owners_C AND Entity_B.

Golden Rule:
Normalize by default (to 3NF/BCNF) to guarantee data integrity. Denormalize only as a last
resort to fix a specific, measured, and critical performance bottleneck.