Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 33

Achieving First Normal Form (1NF)

Recommended resources

Welcome to the next step in our journey through database normalization. In our previous lesson, we defined First Normal Form (1NF) and learned how to identify tables that violate its rules, primarily by looking for non-atomic values and repeating groups.

Today, we move from theory to practice. This lesson is all about the "how": you will learn the practical techniques to take an unnormalized table and convert it into a clean, 1NF-compliant structure. Mastering this conversion is the foundational skill for all further normalization, ensuring your database design starts on solid ground.

1. The Goal: Eliminating Repeating Groups

The primary target when converting a table to 1NF is the elimination of repeating groups. A repeating group occurs when a single row in a table contains multiple values for a set of related attributes.

For example, look at the Customer_Rental Table below. For a single customer (like CR76), there's space to list multiple properties they've rented. The Property_No, PAddress, Rent_Start, etc., attributes form a repeating group.

UNF to 1NF Conversion Example
This image shows an unnormalized table on the left with repeating groups for property rentals. The 1NF version on the right eliminates these groups by creating a separate row for each rental, ensuring every cell holds a single value.

As the image shows, the most straightforward way to fix this is to create a new row for each individual property rental. This ensures every cell contains only one value, satisfying the core rule of atomicity. While effective, this method can create a lot of redundant data (e.g., the customer's name and number are repeated), an issue we'll learn to solve more elegantly.

2. A Step-by-Step Conversion Process

Let's walk through a structured process for converting a table to 1NF. There are two main types of 1NF violations to address:

  1. Composite Attributes: A column containing values that can be broken down into smaller, meaningful parts (e.g., a full address).
  2. Multi-valued Attributes: A column containing multiple values in a single cell (e.g., a list of subjects).

The following article from Codecademy provides an excellent, detailed walkthrough of converting a table that has both types of violations.

What is First Normal Form (1NF) in DBMS? Explained with Examples

This article breaks down the conversion into a clear, four-step process with a practical example.

Please read the section titled "Steps to convert a table to 1st normal form." Pay close attention to how the initial unnormalized table is transformed in each step: Step 1: Notice how the Address column (a composite attribute) is broken into Street, City, State, and ZIP, and the Marks column is also split to ensure atomicity. Step 2: Observe how the columns for Subject_1 and Subject_2 (a repeating group) are consolidated into a single Subject column by creating new rows. Steps 3 & 4: See how the final table is cleaned up by ensuring data consistency and removing duplicate rows.

This step-by-step "row-splitting" method is a valid way to achieve 1NF. It's intuitive and directly enforces the atomicity rule.

3. A Better Way: Creating a New Table

While creating more rows works, it's not always the most efficient design. As you saw in the Codecademy example, the student's name and address details are repeated for every subject they take. This redundancy can lead to the update, insertion, and deletion anomalies we discussed earlier.

A more robust and common solution is to decompose the unnormalized table. This involves moving the repeating group into its own new table and connecting it back to the original table using a foreign key.

This video provides a fantastic visual explanation of this superior method.

Database Design 36 - 1NF (First Normal Form of Database Normalization)

In this video, Caleb Curry demonstrates how to resolve a multi-valued attribute problem by creating a separate, linked table.

First, watch the segment that explains the problem with having multiple emails stored for a single user. Next, observe the issues this causes, such as data redundancy and update anomalies. Finally, pay close attention to the solution, where he diagrams how to create a separate 'email' table linked back to the 'user' table with a foreign key.

This decomposition approach is central to database design. It achieves 1NF while also minimizing redundancy, creating a more scalable and maintainable structure.

The two main techniques for converting to 1NF are:

  1. Expanding the table: Create new rows for each value in a repeating group. This is simple but can lead to high data redundancy.
  2. Decomposing the table: Create a new table for the repeating group and link it back with a foreign key. This is the preferred method as it reduces redundancy.

4. Practice Exercise

Let's apply what you've learned. The table below shows parking ticket information. As you can see, a person can have multiple tickets, creating a repeating group.

Unnormalized Table Example for Normalization
This unnormalized table contains repeating groups for ticket information associated with each person.

Your task is to describe how you would convert this PARKING TICKETS table into a set of 1NF tables using the decomposition (two-table) method.

Think about:

  • What attributes belong to the person?
  • What attributes belong to the ticket?
  • How will you link the two new tables together? What will be the primary and foreign keys?
Click here to see the solution

To convert this table to 1NF using decomposition, we would create two separate tables: one for people and one for tickets.

1. Create a PERSON Table:
This table will hold the information unique to each person. We can extract the person-specific columns from the original table.

  • PERSON (SSN, LastName, FirstName, PhoneNo)
  • The Primary Key (PK) would be SSN, as it uniquely identifies each person.

2. Create a TICKET Table:
This table will hold the information related to each individual ticket.

  • TICKET (TicketNo, LicPlateSt, LicPlateNo, Date, Code, Fine, PersonSSN)
  • The Primary Key (PK) would be TicketNo.
  • We need to add a Foreign Key (FK) to link each ticket back to the person who received it. We'll add PersonSSN as an FK that references the SSN in the PERSON table.

The final structure would look like this:

PERSON Table:

SSN (PK) LastName FirstName PhoneNo
123-34-5678 Smith John 555-1234
... ... ... ...

TICKET Table:

TicketNo (PK) LicPlateSt LicPlateNo Date Code Fine PersonSSN (FK)
T1001 NY ABC-123 08/15/23 12 50.00 123-34-5678
T1002 NY ABC-123 08/20/23 05 75.00 123-34-5678
T1003 NY ABC-123 09/01/23 12 50.00 123-34-5678
... ... ... ... ... ... ...

This design successfully removes the repeating group, ensures every cell is atomic, and minimizes data redundancy.

Conclusion

In this lesson, we put theory into action by converting unnormalized tables into First Normal Form. You now have two distinct methods for tackling repeating groups and non-atomic data, which are the cornerstones of initial database cleanup.

Key Takeaways:

  • Converting a table to 1NF involves eliminating non-atomic values, especially repeating groups.
  • A simple method is to expand the table by creating a new row for each value from a repeating group.
  • The preferred and more robust method is to decompose the table, moving the repeating group into a new table and linking the two with a primary key-foreign key relationship.
  • This decomposition method is fundamental to good database design as it directly reduces data redundancy.

Now that our tables satisfy the conditions of 1NF, we can move on to the next level of refinement. In our next lesson, we will learn about Second Normal Form (2NF), which addresses a more subtle type of redundancy caused by partial dependencies.