First Normal Form (1NF)

First Normal Form (1NF)

Introduction

Database normalization is an important technique used in relational database design to reduce data redundancy, improve data consistency, and maintain data integrity. First Normal Form (1NF) is the first stage of the normalization process and provides the foundation for organizing data into a structured and reliable format.

In this tutorial, we will understand what First Normal Form means, examine an unnormalized relation, learn different approaches for converting a table into 1NF, and explore the insertion, deletion, and update anomalies that may still exist after achieving 1NF.

What is First Normal Form (1NF)?

A relation is said to be in First Normal Form (1NF) when every attribute contains only atomic values. An atomic value is a single, indivisible value that cannot be meaningfully divided into multiple values within the same field.

In simple terms, each cell of a relational table should contain exactly one value rather than a list or collection of values.

1NF does not allow:

• Multi-valued attributes
• Repeating groups
• Multiple values stored in a single field
• Non-atomic attribute values

Let us understand this with an example.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

Example of an Unnormalized Employee Table

Consider the following EMPLOYEE table:

EMP_ID	EMP_NAME	EMP_PHONE	EMP_STATE
14	John	7272826385, 9064738238	UP
20	Harry	8574783832	Bihar
12	Sam	7390372389, 8589830302	Punjab

The EMP_PHONE column contains multiple phone numbers in the same cell. Therefore, its values are not atomic.

For example:

7272826385, 9064738238

This violates the basic requirement of 1NF because a single field contains more than one value.

Converting the Table into 1NF

To convert this table into First Normal Form, each phone number should be stored as a separate value. One common approach is to create a separate row for each phone number.

Normalized EMPLOYEE Table

EMP_ID	EMP_NAME	EMP_PHONE	EMP_STATE
14	John	7272826385	UP
14	John	9064738238	UP
20	Harry	8574783832	Bihar
12	Sam	7390372389	Punjab
12	Sam	8589830302	Punjab

Now, every cell contains a single atomic value. Therefore, the relation satisfies the basic requirements of First Normal Form.

Methods to Achieve First Normal Form

There are several ways to organize data so that a relation follows the atomic-value requirement of 1NF. The most appropriate approach depends on the structure and requirements of the database.

Method 1: Flattening the Relation

Flattening involves storing each value of a multi-valued attribute in a separate row while repeating the related non-repeating attributes.

For example, instead of storing two phone numbers in one field:

John | 7272826385, 9064738238

we store them as separate rows:

John | 7272826385
John | 9064738238

This approach is simple and satisfies the atomic-value requirement of 1NF. However, it can introduce repeated information and may not always be the best design for larger databases.

Method 2: Decomposition into Separate Tables

Another approach is to divide the data into separate related tables. This is often a cleaner and more scalable design.

EMP_DETAILS Table

EMP_ID	EMP_NAME
14	John
20	Harry
12	Sam

EMP_CONTACT Table

EMP_ID	EMP_PHONE	EMP_STATE
14	7272826385	UP
14	9064738238	UP
20	8574783832	Bihar
12	7390372389	Punjab
12	8589830302	Punjab

In this design, EMP_ID can serve as the primary key of the EMP_DETAILS table. In the EMP_CONTACT table, a combination of EMP_ID and EMP_PHONE can be used as a composite key to uniquely identify each contact record.

This approach separates employee information from contact information and provides a better foundation for further normalization.

Method 3: Using a Fixed Number of Columns

Another approach sometimes used by database designers is to create separate columns for each possible value.

Consider the following unnormalized table:

EMP_SKILL Table

EMP_ID	Skill
14	DBMS, C, C++
20	JAVA, C
12	DBMS, HTML, VB, MS OFFICE

We could split the skills into individual columns:

EMP_ID	Skill_1	Skill_2	Skill_3	Skill_4	Skill_5
14	DBMS	C	C++	–	–
20	JAVA	C	–	–	–
12	DBMS	HTML	VB	MS OFFICE	–

Each skill is now stored in an individual cell, so the values are atomic. However, this design is generally not recommended for a scalable database.

The main problems are:

• The number of skills is limited by the number of columns.
• Adding more skills may require altering the table structure.
• Queries become more complicated.
• Searching for employees with a particular skill requires checking multiple columns.
• The design becomes difficult to maintain as the application grows.

A separate employee-skill relation is usually a more flexible design.

Why Should We Not Stop at 1NF?

Achieving 1NF ensures that each field contains an atomic value, but it does not eliminate every type of redundancy or dependency problem.

A table can satisfy 1NF and still suffer from several types of data anomalies.

The three major anomalies are:

• Insertion Anomaly
• Deletion Anomaly
• Update Anomaly

Let us understand these anomalies using an ORDER_BOOK relation.

ORDER_BOOK Relation

Order_No	B_Name	Quantity	Price
4253	C	15	175
4253	Database	20	225
4154	IT	30	200
4256	C	50	175
4186	Database	15	225

Assume that Order_No and B_Name together form the composite primary key.

1. Insertion Anomaly

An insertion anomaly occurs when we cannot add new information to a table without also adding unrelated information.

Suppose we want to add a new book called Operating Systems, but no customer has ordered it yet.

Because the relation requires an Order_No for the record, we cannot easily store information about the book independently of an order.

This means the current table structure makes it difficult to store a book that has not yet been ordered.

2. Deletion Anomaly

A deletion anomaly occurs when deleting one piece of information unintentionally removes another important piece of information.

For example, suppose order number 4154 is canceled:

4154 | IT | 30 | 200

If this is the only record containing information about the book IT, deleting the order also removes the only stored information about that book.

Therefore, the database loses book information even though we only intended to remove an order.

3. Update Anomaly

An update anomaly occurs when the same piece of information is stored in multiple rows and all occurrences must be updated consistently.

For example, the price of the book C is currently stored as 175 in multiple records.

If its price changes to 200, every corresponding row must be updated. If even one row is missed, the database will contain inconsistent prices for the same book.

This repeated information increases the possibility of data inconsistency.

1NF vs Unnormalized Relation

Unnormalized Relation	First Normal Form (1NF)
May contain multiple values in a field	Each field contains a single atomic value
May contain repeating groups	Repeating groups are eliminated
Data structure can be difficult to query	Data is organized into individual values
May contain lists inside cells	Lists inside cells are not allowed

Advantages of First Normal Form

• Ensures that attributes contain atomic values.
• Eliminates repeating groups and multi-valued fields.
• Makes data easier to query and process.
• Provides a structured foundation for further normalization.
• Improves consistency in the representation of data.

YT:- DecodeIT

Limitations of First Normal Form

Although 1NF is an important first step, it does not solve all database design problems.

• Data redundancy can still exist.
• Insertion anomalies may still occur.
• Deletion anomalies may still occur.
• Update anomalies may still occur.
• Functional dependencies are not fully addressed.

These limitations are addressed by higher normal forms such as Second Normal Form (2NF) and Third Normal Form (3NF).

Conclusion

First Normal Form (1NF) is the foundation of relational database normalization. Its primary goal is to ensure that every attribute contains atomic values and that no field contains multiple values or repeating groups.

There are different ways to achieve 1NF, including flattening a relation, decomposing data into separate tables, and separating multi-valued information into individual attributes. However, decomposition into logically related tables is generally more scalable than creating a fixed number of columns for repeated values.

It is important to remember that achieving 1NF does not completely eliminate redundancy or data anomalies. Problems such as insertion, deletion, and update anomalies can still occur. Therefore, database designers generally continue the normalization process toward 2NF, 3NF, and higher normal forms when appropriate.

Understanding 1NF is essential for anyone learning DBMS, SQL, relational database design, and database normalization.

Keywords

First Normal Form (1NF), 1NF in DBMS, First Normal Form in DBMS, DBMS normalization, database normalization, 1NF normalization, atomic values in DBMS, normalization in DBMS, database anomalies, insertion anomaly, deletion anomaly, update anomaly, relational database design, 1NF example, unnormalized relation, SQL normalization