A Guide to SQL Normalization

1NF (First Normal Form)

First Normal Form, or 1NF, is the most basic level of normalization. Its main goal is to ensure data is stored in a structured, organized way.

The Rules

For a table to be in 1NF, it must satisfy two primary conditions:

1. Atomic Values: Each cell in the table must hold a single, indivisible value. You can't have multiple values stuffed into one cell (like a list of phone numbers).

2. Unique Records: Each record (row) in the table must be unique, which is typically achieved by having a primary key.

Example

Let's look at a table that is not in 1NF:

This table violates the rule of atomicity because the Courses column contains multiple values.

Solution

To bring this table into 1NF, we need to ensure each cell has only one value. We can do this by creating a separate row for each course a student is enrolled in.

Students Table

Now the table is in 1NF! ✅ Every cell holds a single value. The combination of Roll and Course could serve as a composite primary key.

---

2NF (Second Normal Form)

Second Normal Form, or 2NF, builds on 1NF and addresses data redundancy by removing partial dependencies.

The Rules

For a table to be in 2NF, it must meet these conditions:

1. Be in 1NF.

2. Have no partial dependencies. This means that every non-key column must depend on the entire primary key, not just a part of it. This rule is only relevant for tables with a composite primary key (a primary key made of two or more columns).

Example

Let's expand our previous example. Imagine we add the CourseFee and the student's Address. Our primary key here is (Roll, Course).

Enrollments Table

Here, CourseFee depends only on Course, which is just a part of the primary key. Name and Address depend only on Roll, which is also just a part of the primary key. These are partial dependencies. Notice the redundancy? Alice's name and address are repeated.

Solution

To achieve 2NF, we split the table into smaller tables to eliminate these partial dependencies.

Students Table

Courses Table

Enrollments Table

Now, there are no partial dependencies. All tables are in 2NF.

---

3NF (Third Normal Form)

Third Normal Form, or 3NF, goes one step further to reduce data redundancy by removing transitive dependencies.

The Rules

For a table to be in 3NF, it must:

1. Be in 2NF.

2. Have no transitive dependencies. A transitive dependency occurs when a non-key column depends on another non-key column, which in turn depends on the primary key. Think of it as an indirect dependency: (Key -> Non-Key -> Another Non-Key).

Example

Let's modify our Students table from the 2NF example by adding Advisor and AdvisorOffice. The primary key is Roll.

Students Table (Not in 3NF)

Here, Advisor depends on the primary key Roll. However, AdvisorOffice depends on Advisor (which is a non-key column). This is a transitive dependency: Roll -> Advisor -> AdvisorOffice. If Dr. Smith's office changes, we have to update it in multiple places, which can lead to inconsistencies.

Solution

We resolve the transitive dependency by splitting the table again.

Students Table

Advisors Table

Now, the Students table contains only information directly related to the student, and the Advisors table holds information about advisors. There are no more transitive dependencies, and our database is in 3NF.

---

Boyce-Codd Normal Form (BCNF or 3.5NF)

Often referred to as 3.5NF, Boyce-Codd Normal Form is a stricter version of 3NF. A table is in BCNF if, for every non-trivial functional dependency (A -> B), the determining attribute (A) is a superkey. A superkey is a column, or set of columns, that uniquely identifies every row in the table.

The key difference from 3NF is that BCNF eliminates the rare anomaly where a non-prime attribute (not part of any candidate key) could determine a prime attribute (part of a candidate key).

Example: Violation of BCNF

Let's consider a table that tracks which professor teaches which subject to a student.

Student_Professor_Subject Table

StudentID	Subject	ProfessorName
S101	Math	Prof. Ada
S101	Physics	Prof. Turing
S102	Math	Prof. Ada
S103	Chemistry	Prof. Curie
S104	Physics	Prof. Turing

Assumptions:

1. A student can take multiple subjects.

2. Each subject is taught by only one professor.

3. Multiple students can enroll in the same subject.

Functional Dependencies (FDs):

1. {StudentID,Subject}→ProfessorName: If you know the student and the subject, you know which professor is teaching them.

2. ProfessorName→Subject: If you know the professor's name, you know the single subject they teach.

Finding the Candidate Keys: The only candidate key for this table is {StudentID, Subject}. This combination uniquely identifies each row. ProfessorName is not a candidate key because Prof. Ada teaches multiple students (S101, S102).

Why does this violate BCNF? Let's check our FDs against the BCNF rule: "For every FD X→Y, X must be a superkey."

• FD 1: {StudentID,Subject}→ProfessorName.

  • The determinant is {StudentID,Subject}. Is it a superkey? Yes, it's the candidate key. This FD satisfies BCNF. 👍

• FD 2: ProfessorName→Subject.

  • The determinant is ProfessorName. Is it a superkey? No. It cannot uniquely identify a row. This FD violates BCNF. 👎

The table is in 3NF because Subject is a prime attribute (part of the candidate key), but it fails BCNF because ProfessorName is not a superkey. This leads to data redundancy; for instance, "Prof. Ada" is linked to "Math" multiple times.

Solution: Decomposing into BCNF

To fix the violation, we decompose the original table into smaller tables that satisfy BCNF. We use the problematic dependency (ProfessorName→Subject) to create a new table.

Step 1: Create a table for the problematic FD: ProfessorName→Subject.

Professor_Subject Table

ProfessorName	Subject
Prof. Ada	Math
Prof. Turing	Physics
Prof. Curie	Chemistry

• FD: ProfessorName→Subject.

• Candidate Key: ProfessorName.

• BCNF Check: The determinant ProfessorName is the candidate key (a superkey). This table is in BCNF.

Step 2: Create another table with the remaining attributes, ensuring the determinant from the first table (ProfessorName) is included as a foreign key.

Student_Professor Table

StudentID	ProfessorName
S101	Prof. Ada
S101	Prof. Turing
S102	Prof. Ada
S103	Prof. Curie
S104	Prof. Turing

Export to Sheets

• FD: None, except the trivial one where the whole key determines itself.

• Candidate Key: {StudentID, ProfessorName}.

• BCNF Check: Since there are no non-trivial FDs where the determinant isn't a superkey, this table is also in BCNF.

By splitting the original table into Professor_Subject and Student_Professor, we have eliminated the redundancy and now both tables satisfy BCNF. We can still retrieve all the original information by joining these two tables on ProfessorName.

---

Fourth Normal Form (4NF)

Fourth Normal Form takes normalization a step further by addressing multi-valued dependencies. A multi-valued dependency exists when the presence of one row implies the presence of other rows.

For a table to be in 4NF, it must first be in BCNF and must not have any non-trivial multi-valued dependencies. For instance, if a table stores Professor, Course, and Hobby, and a professor can teach multiple courses and have multiple hobbies independently of each other, this creates a multi-valued dependency. 4NF would resolve this by splitting the data into two tables: one linking professors to courses, and another linking professors to their hobbies.

In simple terms, 4NF deals with isolating independent many-to-many relationships. When you store more than one of these independent relationships in a single table, you create problems.

A table is in 4NF if it meets two conditions:

1. It is already in Boyce-Codd Normal Form (BCNF).

2. It has no non-trivial multi-valued dependencies.

Understanding Multi-Valued Dependency (MVD)

A multi-valued dependency (MVD) exists when two or more independent attributes in a table have a many-to-many relationship with a third attribute.

Let's use the classic Professor, Course, and Hobby example.

Assumptions:

• A professor can teach multiple courses.

• A professor can have multiple hobbies.

• Crucially, a professor's courses and hobbies are independent. The courses they teach have nothing to do with their hobbies.

This "independence" is the key. It creates the MVD. We can write it as:

• Professor→→Course (A professor determines a set of courses)

• Professor→→Hobby (The same professor determines a set of hobbies)

Example: A Table Violating 4NF

Imagine we store this data in a single table. To represent all the facts correctly, we are forced to create rows that show every combination of a professor's courses and hobbies.

Professor_Info Table (Violates 4NF)

Professor	Course	Hobby
Prof. Turing	CS101	Chess
Prof. Turing	CS101	Knitting
Prof. Turing	CS202	Chess
Prof. Turing	CS202	Knitting
Prof. Curie	PHY301	Painting
Prof. Curie	PHY301	Tennis

Why is this a problem?

1. Data Redundancy:

   • The fact that "Prof. Turing teaches CS101" is stated twice.

   • The fact that "Prof. Turing's hobby is Chess" is also stated twice.

2. Update Anomalies:

   • Insertion: If Prof. Turing starts teaching a new course, "CS305", we must add two new rows: (Prof. Turing, CS305, Chess) and (Prof. Turing, CS305, Knitting). We have to know all his hobbies to add one new course.

   • Deletion: If Prof. Curie decides to stop her "Tennis" hobby, we would delete the last row. This is fine. But if she only taught one course and had one hobby, deleting that course could accidentally delete the only record of her hobby.

This table is in BCNF because the only candidate key is {Professor, Course, Hobby}. There are no functional dependencies to violate BCNF rules. However, the multi-valued dependencies on Professor create the problems listed above.

Solution: Decomposing into 4NF

The solution is to separate the independent many-to-many relationships into their own tables. We break down the original table based on the MVDs.

Step 1: Create a table for the professor-course relationship.

Professor_Course Table (4NF Compliant)

Professor	Course
Prof. Turing	CS101
Prof. Turing	CS202
Prof. Curie	PHY301

Export to Sheets

Step 2: Create a separate table for the professor-hobby relationship.

Professor_Hobby Table (4NF Compliant)

Professor	Hobby
Prof. Turing	Chess
Prof. Turing	Knitting
Prof. Curie	Painting
Prof. Curie	Tennis

Export to Sheets

Why is this better?

• No Redundancy: Each fact is stored only once.

• Easy Updates: If Prof. Turing adds a new course, we add only one row to the Professor_Course table. If he picks up a new hobby, we add only one row to the Professor_Hobby table. The two actions are now correctly isolated.

• No Deletion Anomalies: Deleting a course from the Professor_Course table will never affect the data in the Professor_Hobby table.

By decomposing the original table, we have eliminated the multi-valued dependencies within a single table, and both new tables are now in 4NF.

---

Fifth Normal Form (5NF)

Fifth Normal Form, also known as Project-Join Normal Form (PJNF), is concerned with join dependencies. 5NF is mainly of academic interest.

It's designed to reduce redundancy in relational databases by isolating semantically related, many-to-many relationships. A table is in 5NF if and only if it is in 4NF and every join dependency in it is implied by the candidate keys.

That sounds complex, so let's simplify. 5NF deals with a very specific and rare type of problem where a table can be split into three or more smaller tables, which can then be joined back together to form the original table without losing any information. If a table can be decomposed this way, it should be.

Understanding Join Dependency

A join dependency exists if a table can be losslessly decomposed into three or more smaller tables. It means the original table is essentially a combination (a join) of these smaller, more fundamental relationships.

The main idea of 5NF is: Don't store data that can be inferred by joining other tables.

Example: A Table Violating 5NF

Let's consider a classic example involving sales agents, the companies they work for, and the products they are authorized to sell.

Assumptions & Business Rules:

1. An agent can work for multiple companies.

2. An agent can sell multiple products.

3. A company can have multiple agents.

4. A company can offer multiple products.

5. Crucially, if an agent works for a company AND that company makes a certain product AND the agent is authorized to sell that product, then the agent can sell that specific product for that specific company.

This last rule is the source of the join dependency. Let's look at a table that stores these relationships.

Agent_Company_Product Table (Violates 5NF)

Agent	Company	Product
Agent Smith	Acme Corp	Widget
Agent Smith	Acme Corp	Gizmo
Agent Smith	Globex Inc	Widget
Agent Jones	Acme Corp	Widget
Agent Jones	Zenon	Gadget
Agent Smith	Globex Inc	Gadget

Let's add one more crucial row to illustrate the problem:
| Agent Jones | Acme Corp | Gadget |

Without this last row, our business rule (#5) is violated. Why?

• Agent Jones works for Acme Corp (row 4).

• Acme Corp makes Gadgets (implied if Agent Smith sells Gadgets for them, but let's assume it's a known fact).

• Agent Jones is authorized to sell Gadgets (row 5, for Zenon).

Because all three conditions are true, Agent Jones must be able to sell Gadgets for Acme Corp. Storing this fact explicitly in the table creates redundancy and potential anomalies. This table is in 4NF because there are no independent multi-valued dependencies, but it violates 5NF due to this join dependency.

Solution: Decomposing into 5NF

To satisfy 5NF, we must decompose the table into smaller tables that represent the fundamental relationships. We break it into the smallest pieces that cannot be broken down further without losing information.

1. Agent_Company Table (Agent Assignments)

Agent	Company
Agent Smith	Acme Corp
Agent Smith	Globex Inc
Agent Jones	Acme Corp
Agent Jones	Zenon

2. Company_Product Table (Company Catalogs)

Company	Product
Acme Corp	Widget
Acme Corp	Gizmo
Globex Inc	Widget
Globex Inc	Gadget
Zenon	Gadget

3. Agent_Product Table (Agent Skills)

Agent	Product
Agent Smith	Widget
Agent Smith	Gizmo
Agent Smith	Gadget
Agent Jones	Widget
Agent Jones	Gadget

Why is this better?

Now, the "fact" that Agent Jones sells Gadgets for Acme Corp is no longer stored directly. Instead, it can be derived by joining the three tables.

• We can see from Agent_Company that Agent Jones works for Acme Corp.

• We can see from Company_Product that Acme Corp makes Gadgets.

• We can see from Agent_Product that Agent Jones can sell Gadgets.

Because we can reconstruct the original, valid data by joining these tables, we don't need to store the combined information. This eliminates redundancy and prevents update anomalies. For example, if a company stops making a product, we only have to delete one row from the Company_Product table, and it correctly removes that product as an option for all associated agents without causing data integrity issues.

In summary, 5NF ensures that you don't store redundant facts that can be logically inferred from other, more fundamental facts stored in separate tables. It's rare to encounter join dependencies that aren't covered by candidate keys, making 5NF violations uncommon in practice.

---

Sixth Normal Form (6NF)

Sixth Normal Form is currently the highest level of normalization and is primarily used in temporal (time-based) databases and data warehousing. It aims to eliminate all non-trivial join dependencies.

A table is in 6NF if it satisfies two conditions:

1. It is already in Fifth Normal Form (5NF).

2. It has been decomposed to its most irreducible form. This practically means that each table consists of a primary key and, at most, one other attribute.

The core idea of 6NF is to eliminate all non-trivial join dependencies, ensuring that every piece of information is stored independently.

Why is 6NF important for temporal data?

In many real-world scenarios, different attributes of a record change at different times. For instance, an employee's job title might change on one date, while their salary might change on another. Storing this in a single, wide table can lead to redundancy and complexity when tracking history. 6NF solves this by isolating each attribute's history.

Example: A table violating 6NF

Let's consider a table that tracks a doctor's status and their assigned department over time. This table is in 5NF.

Doctor_History Table (Violates 6NF)

DoctorID	Status	Department	ValidFrom	ValidTo
D101	Resident	Cardiology	2022-01-01	2023-06-30
D101	Attending	Cardiology	2023-07-01	2024-08-31
D101	Attending	Neurology	2024-09-01	Present

Problems with this structure: 🧐

• Redundancy: The fact that Dr. D101 was in the Cardiology department is stored twice.

• Update Anomalies: When the doctor's status changed to "Attending" on July 1, 2023, the Department ("Cardiology") had to be repeated in the new row, even though it didn't change on that date. This creates a dependency where a change in one attribute forces the repetition of another.

This structure implies that the Status and Department are a single fact that changes together, which isn't true. They are independent facts with their own histories.

Solution: Decomposing into 6NF

To achieve 6NF, we decompose the table so that each non-key attribute is in its own table along with the primary key and the temporal columns.

1. Doctor_Status_History Table (6NF Compliant)

This table tracks only the history of the doctor's status.

DoctorID	Status	ValidFrom	ValidTo
D101	Resident	2022-01-01	2023-06-30
D101	Attending	2023-07-01	Present

2. Doctor_Department_History Table (6NF Compliant)

This table tracks only the history of the doctor's department assignment.

DoctorID	Department	ValidFrom	ValidTo
D101	Cardiology	2022-01-01	2024-08-31
D101	Neurology	2024-09-01	Present

Why is this better?

• No Redundancy: Each historical fact is stored exactly once. The fact that Dr. D101 was in Cardiology from 2022 to 2024 is now a single, unambiguous record.

• Simplified Updates:

  • If the doctor's status changes, only one row is added to the Doctor_Status_History table.

  • If their department changes, only one row is added to the Doctor_Department_History table.

• Temporal Purity: Each row represents a single, indivisible fact that was true for a specific period. This makes querying for historical states much cleaner and more efficient.

Benefits and Drawbacks of 6NF

Benefits:

• Maximum data integrity and elimination of redundancy.

• Ideal for tracking historical data and auditing changes (a core requirement in data warehousing).

• Increases flexibility; adding a new attribute to be tracked over time simply means adding a new table, not altering existing ones.

Drawbacks:

• Explosion of tables: A single table with many attributes will be decomposed into many smaller tables.

• Increased query complexity: Retrieving a complete record for a specific point in time requires joining multiple tables, which can be complex and may impact performance if not handled by a modern, optimized database.

Due to these drawbacks, 6NF is generally considered overkill for typical transactional databases (OLTP systems) but is highly valuable in specialized applications like data warehouses.