Hello! In our last lesson, we established why normalization is essential for creating robust and reliable databases. We learned that by minimizing data redundancy, we can prevent a host of data integrity problems called modification anomalies.
Today, we'll move from the "why" to the "how." We will tackle the first and most fundamental step in the normalization process. Your learning outcome for this lesson is to apply First Normal Form (1NF) to eliminate repeating groups.
Think of this as establishing the foundational safety level for our database design. 1NF provides the bare minimum rules a table must follow to be considered "normalized." It directly addresses one of the most common and problematic design flaws: storing multiple pieces of information in a single field.
What is First Normal Form (1NF)?
First Normal Form (1NF) sets the basic rules for an organized, queryable table. The core principle behind 1NF is atomicity, which means that every cell in your table should hold one, and only one, value. It should be "atomic" or indivisible.
To get a comprehensive look at the rules of 1NF, please watch the following video.
Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF
This video from the Decomplexify channel offers a detailed explanation of First Normal Form. It breaks down the four key requirements for a table to be in 1NF and uses clear examples to illustrate common violations.
Watch the segment from 03:48 to 10:25. Pay close attention to the four rules mentioned and especially the concept of 'repeating groups', as this is the most common violation of 1NF you'll encounter.
The Rules of 1NF Summarized
As the video explained, for a table to be in First Normal Form, it must satisfy four conditions:
- Values must be atomic: Each cell must contain a single, indivisible value. No lists, arrays, or comma-separated strings.
- No repeating groups: You cannot have multiple columns representing the same type of data (e.g.,
Item1,Item2,Item3). The data itself should determine the number of rows, not the table's structure. - Each row must be unique: This is typically enforced by defining a primary key—a column or set of columns that uniquely identifies each row.
- No mixing of data types in a column: Every value in a single column must be of the same data type (e.g., all integers, all strings). Modern relational database systems (RDBMS) strictly enforce this, so it's a rule you get "for free" when you define your table structure.
The most critical and common problem that 1NF solves is the issue of non-atomic values and repeating groups. Let's see how to fix this in practice.
Transforming a Table to 1NF
Imagine a simple table designed to track customer orders. An initial, unnormalized design might look like this:
| Customer ID | Customer Name | Purchased Products |
|---|---|---|
| 101 | John Doe | Laptop, Mouse |
| 102 | Jane Smith | Tablet |
| 103 | Alice Brown | Keyboard, Monitor, Pen |
This design violates 1NF because the Purchased Products column contains multiple values. This makes simple questions like "Who bought a Mouse?" surprisingly difficult to answer and update operations risky.
Let's read through a clear, step-by-step example of how to fix this.
Database Normalization: 1NF, 2NF, 3NF & BCNF Examples
This article from DigitalOcean provides a practical example of transforming a non-compliant table into 1NF. It clearly demonstrates how to handle a column that contains a list of values.
Please read the section titled 'First Normal Form (1NF)' up to (but not including) the SQL code block. Focus on the 'Before' and 'After' tables showing the transformation for customer purchases. Make sure you understand why the initial table is not in 1NF and how the transformed table solves the problem.
As the article demonstrates, the solution is to decompose the table. Instead of cramming multiple products into one cell, we create a new row for each product a customer purchased.
The transformed table looks like this:
| Customer ID | Customer Name | Product |
|---|---|---|
| 101 | John Doe | Laptop |
| 101 | John Doe | Mouse |
| 102 | Jane Smith | Tablet |
| 103 | Alice Brown | Keyboard |
| 103 | Alice Brown | Monitor |
| 103 | Alice Brown | Pen |
This new structure is in 1NF. Every cell holds a single value. Querying for all "Mouse" purchasers is now a simple WHERE clause.
This process of taking a repeating group and breaking it out into its own set of rows is the essence of applying 1NF. The image below provides another visual example of this transformation.
Test Your Understanding
Now, let's see if you can spot a 1NF violation.
Test your understanding!
Based on the definition provided in the image and what you've learned, is the table shown in the image in First Normal Form? Why or why not?
Show answer
No, the table is not in 1NF. For a single project (e.g., PNo 'p1'), the details of multiple employees (ENo, EName, etc.) are listed across several rows that repeat the project information. This is a classic example of a repeating group. To bring this table into 1NF, you would need to ensure each row represents a single, unique fact, which typically means creating separate tables for projects and employees and a third table to link them. The structure shown leads to the exact redundancy problems we discussed in the last lesson.
The "How": Creating New Tables with Foreign Keys
In many cases, the best way to achieve 1NF is not just to create more rows in the same table, but to create a new, related table. This is especially true when you are resolving issues that could lead to other anomalies down the line.
The following video gives a very clear walkthrough of this process, showing how a foreign key is used to link the new table back to the original data. This pattern is fundamental to relational database design.
Database Design 36 - 1NF (First Normal Form of Database Normalization)
We've seen that the solution to repeating groups is to create separate rows. This video shows how to implement that solution by creating a new table and linking it with a foreign key.
Watch the segment from 03:03 to 06:42. The speaker explains how to solve the problem of a user having multiple emails by creating a separate email table linked by a user_id foreign key.
This approach of creating a separate table is the most robust way to apply 1NF, as it lays the groundwork for further normalization, which we'll cover in the next lessons.
Apply your knowledge!
You are given a table to track movies and their genres. The table is called Movies and has the following columns and data:
| MovieID | Title | Director | Genres |
|---|---|---|---|
| 1 | The Matrix | Wachowskis | Sci-Fi, Action |
| 2 | Inception | Christopher Nolan | Sci-Fi, Thriller, Action |
| 3 | The Godfather | Francis Ford Coppola | Crime, Drama |
This table is not in 1NF because the Genres column contains multiple values. How would you redesign this structure to comply with 1NF? Describe the new table(s) you would create and their columns.
Show answer
To bring this structure into 1NF, you should decompose it into two tables:
-
MoviesTable: This table holds information specific to each movie.MovieID (Primary Key) Title Director 1 The Matrix Wachowskis 2 Inception Christopher Nolan 3 The Godfather Francis Ford Coppola -
MovieGenresTable: This table stores the many-to-many relationship between movies and genres, eliminating the repeating group.MovieID (Foreign Key) Genre 1 Sci-Fi 1 Action 2 Sci-Fi 2 Thriller 2 Action 3 Crime 3 Drama
In the MovieGenres table, the combination of MovieID and Genre would form a composite primary key. This design ensures each column holds a single, atomic value and cleanly separates the concepts of "movies" and "genres".
Conclusion
In this lesson, you learned the first rule of database normalization, 1NF. By ensuring that our tables adhere to these foundational rules, we make our data easier to query, maintain, and trust.
Key Takeaways:
- A table is in First Normal Form (1NF) if its values are atomic, it has a primary key, and it contains no repeating groups.
- The core principle of 1NF is atomicity: each cell must contain only a single value.
- Violations like comma-separated lists or multiple columns for the same data (
Item1,Item2) are called repeating groups. - To fix a 1NF violation, you decompose the table by creating a new row for each value in the repeating group, often by creating a new table linked by a foreign key.
Preview of the Next Lesson:
Achieving 1NF is a critical first step, but it doesn't solve all redundancy problems. Our 1NF Customer Purchase table from the example still repeats the Customer Name for every product purchased by that customer. In the next lesson, we will learn about Second Normal Form (2NF), which addresses this type of redundancy, known as a partial dependency.