First Normal Form (1NF)0%

Second Normal Form (2NF) & Functional Dependencies

First Normal Form (1NF)

Intermediate12 min read•Updated: 2026-09-12

First Normal Form (1NF): Atomic Values & Repeating Groups

Database Normalization is a systematic theoretical technique developed by Dr. Edgar F. Codd to evaluate and refine relational table structures. Normalization organizes database schemas into progressive levels called Normal Forms (1NF, 2NF, 3NF, BCNF).

The journey to an enterprise-grade schema begins with the First Normal Form (1NF).

1. The Rules of First Normal Form (1NF)

A relational table is in First Normal Form (1NF) if and only if it satisfies three strict criteria:

1
Atomic Values: Every column must contain atomic (indivisible), single values. No cell can contain lists, sets, arrays, or comma-separated values!
2
No Repeating Groups (Positional Columns): A table cannot use multiple numbered columns to store the same attribute (e.g., phone_1, phone_2, phone_3).
3
Unique Rows & Primary Key: Each row must be uniquely identifiable via an explicit Primary Key.

2. Violation 1: Non-Atomic Comma-Separated Values

 VIOLATING TABLE (Not in 1NF):
 +------------+--------------+---------------------------------------+
 | student_id | student_name | phone_numbers                         |  <-- NON-ATOMIC!
 +------------+--------------+---------------------------------------+
 |        101 | Aarav        | 9820011111, 9820022222, 9820033333   |
 |        102 | Diya         | 9820044444                            |
 +------------+--------------+---------------------------------------+

Why this is a Disaster:

How do you query: "Find the student with phone number 9820022222"? You are forced to use slow, unindexed pattern matching (LIKE '%9820022222%').
How do you delete just the second phone number? You must write complex string manipulation code in your application layer!
How do you enforce a UNIQUE constraint on phone numbers? You can't!

3. Violation 2: Repeating Groups (Column Proliferation)

Some developers attempt to fix comma-separated values by adding numbered columns:

 STILL VIOLATING 1NF (Repeating Groups):
 +------------+--------------+------------+------------+------------+
 | student_id | student_name | phone_1    | phone_2    | phone_3    |  <-- REPEATING GROUPS!
 +------------+--------------+------------+------------+------------+
 |        101 | Aarav        | 9820011111 | 9820022222 | 9820033333 |
 |        102 | Diya         | 9820044444 | NULL       | NULL       |
 +------------+--------------+------------+------------+------------+

Why Repeating Groups Are Bad:

Wastes disk space with endless NULL columns for users with only 1 phone number.
What happens when a user has 4 phone numbers? You must execute an expensive ALTER TABLE migration to add phone_4!
Searching for a phone number requires searching across all 3 columns:

WHERE phone_1 = 'x' OR phone_2 = 'x' OR phone_3 = 'x'.

4. The 1NF Solution: Decomposition into a Child Table

To bring the schema into 1NF, separate the repeating attribute into its own dedicated child table where each value occupies its own row:

1-- Table 1: Core Entity
2CREATE TABLE students (
3    student_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
4    student_name VARCHAR(100) NOT NULL
5);
6 
7-- Table 2: Normalized Child Table (1NF Compliant!)
8CREATE TABLE student_phones (
9    phone_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
10    student_id INT UNSIGNED NOT NULL,
11    phone_type ENUM('Mobile', 'Home', 'Work') NOT NULL DEFAULT 'Mobile',
12    phone_number VARCHAR(20) NOT NULL,
13    
14    CONSTRAINT uq_phone UNIQUE (phone_number),
15    CONSTRAINT fk_phone_student FOREIGN KEY (student_id) REFERENCES students(student_id)
16        ON DELETE CASCADE
17);

5. Best Practices & Common Pitfalls

Names and Addresses Atomicity: Be careful with compound fields! If your business needs to sort by last name, storing full_name = 'Aarav Sharma' violates practical atomicity. Split names into first_name and last_name. Similarly, split addresses into street, city, state, and postal_code.
The Modern Exception (Native JSON): While 1NF prohibits unstructured comma-separated strings, modern MySQL 8.0 natively supports JSON columns for semi-structured data that does not require relational foreign keys.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the fundamental requirement regarding column values in First Normal Form (1NF)?

Question 2

Why does a table containing columns skill_1, skill_2, and skill_3 violate First Normal Form?

Question 3

How should a multi-valued attribute (such as multiple email addresses per user) be structured to achieve 1NF?

Question 4

What requirement does 1NF impose on row identification?

Question 5

Why is storing "Mumbai, Maharashtra, 400001" in a single address column considered a practical violation of atomicity?

Next Lesson

Second Normal Form (2NF) & Functional Dependencies

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Database Anomalies (Insert, Update, Delete)	Second Normal Form (2NF) & Functional Dependencies

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Second Normal Form (2NF) & Functional Dependencies

First Normal Form (1NF)

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

First Normal Form (1NF): Atomic Values & Repeating Groups

The journey to an enterprise-grade schema begins with the First Normal Form (1NF).

1. The Rules of First Normal Form (1NF)

A relational table is in First Normal Form (1NF) if and only if it satisfies three strict criteria:

1
Atomic Values: Every column must contain atomic (indivisible), single values. No cell can contain lists, sets, arrays, or comma-separated values!
2
No Repeating Groups (Positional Columns): A table cannot use multiple numbered columns to store the same attribute (e.g., phone_1, phone_2, phone_3).
3
Unique Rows & Primary Key: Each row must be uniquely identifiable via an explicit Primary Key.

2. Violation 1: Non-Atomic Comma-Separated Values

 VIOLATING TABLE (Not in 1NF):
 +------------+--------------+---------------------------------------+
 | student_id | student_name | phone_numbers                         |  <-- NON-ATOMIC!
 +------------+--------------+---------------------------------------+
 |        101 | Aarav        | 9820011111, 9820022222, 9820033333   |
 |        102 | Diya         | 9820044444                            |
 +------------+--------------+---------------------------------------+

Why this is a Disaster:

How do you query: "Find the student with phone number 9820022222"? You are forced to use slow, unindexed pattern matching (LIKE '%9820022222%').
How do you delete just the second phone number? You must write complex string manipulation code in your application layer!
How do you enforce a UNIQUE constraint on phone numbers? You can't!

3. Violation 2: Repeating Groups (Column Proliferation)

Some developers attempt to fix comma-separated values by adding numbered columns:

 STILL VIOLATING 1NF (Repeating Groups):
 +------------+--------------+------------+------------+------------+
 | student_id | student_name | phone_1    | phone_2    | phone_3    |  <-- REPEATING GROUPS!
 +------------+--------------+------------+------------+------------+
 |        101 | Aarav        | 9820011111 | 9820022222 | 9820033333 |
 |        102 | Diya         | 9820044444 | NULL       | NULL       |
 +------------+--------------+------------+------------+------------+

Why Repeating Groups Are Bad:

Wastes disk space with endless NULL columns for users with only 1 phone number.
What happens when a user has 4 phone numbers? You must execute an expensive ALTER TABLE migration to add phone_4!
Searching for a phone number requires searching across all 3 columns:

WHERE phone_1 = 'x' OR phone_2 = 'x' OR phone_3 = 'x'.

4. The 1NF Solution: Decomposition into a Child Table

To bring the schema into 1NF, separate the repeating attribute into its own dedicated child table where each value occupies its own row:

1-- Table 1: Core Entity
2CREATE TABLE students (
3    student_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
4    student_name VARCHAR(100) NOT NULL
5);
6 
7-- Table 2: Normalized Child Table (1NF Compliant!)
8CREATE TABLE student_phones (
9    phone_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
10    student_id INT UNSIGNED NOT NULL,
11    phone_type ENUM('Mobile', 'Home', 'Work') NOT NULL DEFAULT 'Mobile',
12    phone_number VARCHAR(20) NOT NULL,
13    
14    CONSTRAINT uq_phone UNIQUE (phone_number),
15    CONSTRAINT fk_phone_student FOREIGN KEY (student_id) REFERENCES students(student_id)
16        ON DELETE CASCADE
17);

5. Best Practices & Common Pitfalls

Names and Addresses Atomicity: Be careful with compound fields! If your business needs to sort by last name, storing full_name = 'Aarav Sharma' violates practical atomicity. Split names into first_name and last_name. Similarly, split addresses into street, city, state, and postal_code.
The Modern Exception (Native JSON): While 1NF prohibits unstructured comma-separated strings, modern MySQL 8.0 natively supports JSON columns for semi-structured data that does not require relational foreign keys.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the fundamental requirement regarding column values in First Normal Form (1NF)?

Question 2

Why does a table containing columns skill_1, skill_2, and skill_3 violate First Normal Form?

Question 3

How should a multi-valued attribute (such as multiple email addresses per user) be structured to achieve 1NF?

Question 4

What requirement does 1NF impose on row identification?

Question 5

Why is storing "Mumbai, Maharashtra, 400001" in a single address column considered a practical violation of atomicity?

Next Lesson

Second Normal Form (2NF) & Functional Dependencies

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Database Anomalies (Insert, Update, Delete)	Second Normal Form (2NF) & Functional Dependencies

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next