First Normal Form (1NF) — Detailed Explanation

🔹 What Is First Normal Form (1NF)?

First Normal Form (1NF) is a fundamental rule of the relational model.
It requires that:

Every attribute value must be atomic (indivisible).
Each attribute must contain only a single value from its domain.
No multivalued, composite, or nested attributes are allowed.

In simple terms:

A relation in 1NF cannot contain sets, lists, or relations inside attributes.

🔹 Core Rule of 1NF

A relation is in 1NF if:

Condition	Meaning
Atomic values only	Each cell contains one value
No repeating groups	No sets like {A, B, C}
No composite attributes	No structured attributes inside one column
No nested relations	No relation stored inside another relation

Why Some Relations Violate 1NF

Example 1: Multivalued Attribute

Consider:


DEPARTMENT(Dnumber, Dname, Dmgr_ssn, Dlocations)

Suppose a department has multiple locations:

Dnumber	Dname	Dlocations
5	Research	{Bellaire, Sugarland, Houston}

❌ Problem:

Dlocations contains a set of values, not a single atomic value.

This violates 1NF.

Two Ways to Interpret This Violation

1️⃣ Domain is atomic, but tuples contain sets

Domain = single location names
But tuples contain multiple values
So Dlocations is not functionally dependent on the primary key

2️⃣ Domain contains sets (non-atomic domain)

Domain itself is a set of locations
Now Dnumber → Dlocations
But attribute domain is not atomic → still violates 1NF

In both interpretations → relation is not in 1NF

Techniques to Convert to 1NF

There are three methods:

1️⃣ Best Method: Decomposition (Recommended)

Remove the multivalued attribute and create a new relation.

Original:


DEPARTMENT(Dnumber, Dname, Dmgr_ssn, Dlocations)

After Decomposition:


DEPARTMENT(Dnumber, Dname, Dmgr_ssn)
DEPT_LOCATIONS(Dnumber, Dlocation)

Primary Key of new relation:


(Dnumber, Dlocation)

✔ Advantages:

No redundancy
No NULLs
No artificial limits
Fully normalized

👉 This is the preferred approach

2️⃣ Expand the Primary Key (Not Recommended)

Create separate tuples for each location:

Dnumber	Dname	Dlocation
5	Research	Bellaire
5	Research	Sugarland

New primary key:


(Dnumber, Dlocation)

❌ Disadvantages:

Repetition of department data
Redundancy
Update anomalies

3️⃣ Fixed Number of Attributes (Worst Option)

Replace:


Dlocations

With:


Dlocation1, Dlocation2, Dlocation3

❌ Problems:

NULL values if fewer locations
Artificial limit on number of locations
Introduces ordering semantics
Queries become complex

Example query difficulty:

Find departments located in 'Bellaire'

Now you must check:


Dlocation1 = 'Bellaire'
OR Dlocation2 = 'Bellaire'
OR Dlocation3 = 'Bellaire'

🔹 Nested Relations and 1NF

1NF also disallows nested relations.

Example:


EMP_PROJ(Ssn, Ename, {PROJS(Pnumber, Hours)})

Here:

PROJS is a multivalued composite attribute
Each tuple contains a relation inside it

This violates 1NF.

✔ Converting Nested Relation to 1NF

Decompose into:


EMP_PROJ1(Ssn, Ename)
EMP_PROJ2(Ssn, Pnumber, Hours)

Primary key of EMP_PROJ2:


(Ssn, Pnumber)

This process is called:

Decomposition with primary key propagation

🔹 Multi-Level Nesting Example


CANDIDATE(Ssn, Name,
          {JOB_HIST(Company, Highest_position,
                    {SAL_HIST(Year, Max_sal)})})

Convert step-by-step:


CANDIDATE_1(Ssn, Name)
CANDIDATE_JOB_HIST(Ssn, Company, Highest_position)
CANDIDATE_SAL_HIST(Ssn, Company, Year, Max_sal)

This recursive decomposition converts nested structures into 1NF relations.

🔹 Multiple Multivalued Attributes Problem

Consider:


PERSON(Ss#, {Car_lic#}, {Phone#})

If expanded improperly:


PERSON_IN_1NF(Ss#, Car_lic#, Phone#)

This produces:

All combinations of cars × phones for each person.

❌ Leads to:

Redundancy
Spurious relationships
Future normalization problems (handled by 4NF)

✔ Correct solution:


P1(Ss#, Car_lic#)
P2(Ss#, Phone#)

Separate relations for each multivalued attribute.

🔹 BLOBs and CLOBs in 1NF

Modern databases store:

Images
Videos
Documents
Audio files

These are stored as:

BLOB (Binary Large Object)
CLOB (Character Large Object)

Even though they are large objects:

✔ They are treated as atomic values
✔ Therefore, they do not violate 1NF

🔹 Why 1NF Is Important

Without 1NF:

Data redundancy increases
Update anomalies occur
Deletion anomalies occur
Functional dependencies become unclear
Querying becomes complex

1NF ensures:

Clean relational structure
Proper functional dependency analysis
Foundation for higher normal forms (2NF, 3NF, BCNF, etc.)

Summary

Violates 1NF	Why
{A, B, C} in a cell	Multivalued attribute
(Street, City, Zip) in one column	Composite attribute
Relation inside tuple	Nested relation
Multiple repeating columns	Repeating groups

Final Definition

A relation is in First Normal Form (1NF) if and only if every attribute contains only atomic, single-valued elements from its domain, and there are no repeating groups, multivalued attributes, composite attributes, or nested relations.

Practice Examples on First Normal Form (1NF)

Below are structured practice problems to help you identify 1NF violations and convert relations into 1NF.

Example 1: Multivalued Attribute

Given Relation:


STUDENT(SID, Name, PhoneNumbers)

Sample Data:

SID	Name	PhoneNumbers
1	Ali	{9876, 9123}
2	Sara	{9001}

Question:

Is this relation in 1NF?
If not, convert it into 1NF.

Solution:

❌ Not in 1NF

Because PhoneNumbers contains a set of values.

✔ Convert Using Decomposition (Best Method)


STUDENT(SID, Name)
STUDENT_PHONE(SID, PhoneNumber)

Primary Key of STUDENT_PHONE:


(SID, PhoneNumber)

Example 2: Composite Attribute

Given Relation:


EMPLOYEE(EID, Name, Address)

Where:


Address = (Street, City, Zip)

❓ Question:

Is this in 1NF? Normalize it.

Solution:

❌ Not in 1NF

Because Address is composite (not atomic).

✔ Convert to 1NF


EMPLOYEE(EID, Name, Street, City, Zip)

Now all attributes are atomic.

Example 3: Nested Relation

Given Relation:


ORDER(OrderID, CustomerName, {ITEM(ItemID, Quantity)})

Each order contains multiple items.

❓ Question:

Normalize to 1NF.

Solution:

❌ Not in 1NF

Because {ITEM(...)} is a nested relation.

✔ Decompose


ORDER(OrderID, CustomerName)
ORDER_ITEM(OrderID, ItemID, Quantity)

Primary Key of ORDER_ITEM:


(OrderID, ItemID)

Example 4: Multiple Multivalued Attributes

Given Relation:


PERSON(ID, {Email}, {Skill})

A person can have multiple emails and multiple skills.

❓ Question:

Convert to 1NF correctly.

❌ Wrong Approach:


PERSON_1NF(ID, Email, Skill)

This creates all combinations of emails × skills → redundancy.

✅ Correct Approach:


PERSON_EMAIL(ID, Email)
PERSON_SKILL(ID, Skill)

Two separate relations.

Example 5: Fixed Repeating Columns

Given Relation:


PRODUCT(ProductID, Name, Supplier1, Supplier2, Supplier3)

❓ Question:

Is this good 1NF design?

❌ Problem:

Repeating groups (Supplier1, Supplier2, Supplier3)
Artificial limit on number of suppliers
NULL values possible

✅ Convert to 1NF


PRODUCT(ProductID, Name)
PRODUCT_SUPPLIER(ProductID, SupplierName)

Example 6: Identify Whether in 1NF

Determine if the following are in 1NF.

(A)


CAR(CarID, Model, Color)

✔ Yes — all atomic values.

(B)


COURSE(CourseID, Title, {Instructor})

❌ Not in 1NF — multivalued attribute.

(C)


LIBRARY(BookID, Title, Authors)

If Authors = "Smith, John; Clark, Bob"

❌ Not in 1NF — multiple values stored in one field.

Example 7: Advanced Practice

Given Non-1NF Relation:


CANDIDATE(Ssn, Name, {PreviousCompany}, {Phone})

❓ Normalize into 1NF.

✅ Solution:


CANDIDATE(Ssn, Name)
CANDIDATE_COMPANY(Ssn, PreviousCompany)
CANDIDATE_PHONE(Ssn, Phone)

Practice Questions (Try Yourself)

1️⃣ Normalize:


DOCTOR(DID, Name, {Specialization})

2️⃣ Normalize:


BOOK(ISBN, Title, {Author(Name, Country)})

(Hint: Nested composite multivalued attribute)

3️⃣ Normalize:


STUDENT(SID, Name, Course1, Course2, Course3)

Answers to the “Try Yourself” Questions (1NF)

1️⃣ Normalize:

DOCTOR(DID, Name, {Specialization})

Problem

Specialization is a multivalued attribute (a set of values).
Violates 1NF because attributes must contain atomic values only.

Convert to 1NF

Decompose into two relations:


DOCTOR(DID, Name)
DOCTOR_SPECIALIZATION(DID, Specialization)

🔑 Primary Keys

DOCTOR → DID
DOCTOR_SPECIALIZATION → (DID, Specialization)

✔ Why This Works

Each tuple now contains only one specialization
No redundancy
No artificial limits

2️⃣ Normalize:

BOOK(ISBN, Title, {Author(Name, Country)})

Problem

Author is:
- Multivalued
- Composite (Name, Country)
This is a nested multivalued composite attribute
Violates 1NF

Step-by-Step Normalization

Step 1: Remove nested attribute

Create separate relations:


BOOK(ISBN, Title)
BOOK_AUTHOR(ISBN, AuthorName, Country)

🔑 Primary Keys

BOOK → ISBN
BOOK_AUTHOR → (ISBN, AuthorName)

✔ Why This Is Correct

No nested relations
No composite attributes
All attributes atomic
Supports multiple authors per book

3️⃣ Normalize:

STUDENT(SID, Name, Course1, Course2, Course3)

Problem

Repeating groups (Course1, Course2, Course3)
Artificial limit of 3 courses
NULL values possible
Violates good 1NF design principles

Convert to 1NF

Decompose into:


STUDENT(SID, Name)
STUDENT_COURSE(SID, Course)

🔑 Primary Keys

STUDENT → SID
STUDENT_COURSE → (SID, Course)

✔ Why This Is Better

No fixed number of courses
No NULL values
Clean relational structure
Flexible and scalable

Summary Table

Original Problem	Violation Type	1NF Solution
{Specialization}	Multivalued attribute	Separate relation
{Author(Name, Country)}	Nested + composite + multivalued	Decomposition
Course1, Course2, Course3	Repeating groups	Separate relation

Key Rule to Remember for 1NF

If you see:

{ } → multivalued → decompose
( ) inside attribute → composite → split into atomic attributes
Numbered repeating fields → separate relation
Relation inside relation → unnest recursively