Kaushik's Notes

1NF - First Normal Form

6 min read

Overview

  • Definition: A relation is in 1NF if every attribute contains only atomic (indivisible) values
  • Purpose: Eliminates repeating groups and multi-valued attributes
  • Foundation: Basic requirement for all higher normal forms
  • Goal: Each cell contains single value, no arrays or lists

Rules for 1NF

Atomic Values

  • Requirement: Each attribute must contain only atomic values
  • No Multi-values: Cannot store multiple values in single cell
  • No Arrays: No comma-separated lists or arrays
  • Single Data Type: Each column has consistent data type

Unique Row Identification

  • Primary Key: Must have a primary key to uniquely identify rows
  • No Duplicate Rows: Each row must be unique
  • Row Order: Order of rows should not matter

Column Properties

  • Single Value: Each intersection of row and column contains exactly one value
  • No Nested Relations: No tables within tables
  • Consistent Domain: All values in column from same domain

Violations of 1NF

Multi-Valued Attributes

❌ NOT IN 1NF:
Student_ID | Name | Courses
1          | John | Math, Physics, Chemistry
2          | Mary | Biology, Chemistry

Repeating Groups

❌ NOT IN 1NF:
Employee_ID | Name | Phone1      | Phone2      | Phone3
1           | John | 123-456-789 | 987-654-321 | 555-123-456
2           | Mary | 111-222-333 | NULL        | NULL

Nested Tables

❌ NOT IN 1NF:
Order_ID | Customer | Items
1        | John     | {Item1: 2, Item2: 3, Item3: 1}
2        | Mary     | {Item1: 1, Item4: 5}

Converting to 1NF

Example 1: Multi-Valued Attributes

Before (Not 1NF):

Student Table:
Student_ID | Name | Courses
1          | John | Math, Physics, Chemistry
2          | Mary | Biology, Chemistry
3          | Bob  | Math, Biology

After (1NF):

Student Table:
Student_ID | Name
1          | John
2          | Mary  
3          | Bob

Student_Course Table:
Student_ID | Course
1          | Math
1          | Physics
1          | Chemistry
2          | Biology
2          | Chemistry
3          | Math
3          | Biology

Example 2: Repeating Groups

Before (Not 1NF):

Employee Table:
Emp_ID | Name | Phone1      | Phone2      | Phone3
1      | John | 123-456-789 | 987-654-321 | 555-123-456
2      | Mary | 111-222-333 | NULL        | NULL
3      | Bob  | 444-555-666 | 777-888-999 | NULL

After (1NF):

Employee Table:
Emp_ID | Name
1      | John
2      | Mary
3      | Bob

Employee_Phone Table:
Emp_ID | Phone_Number | Phone_Type
1      | 123-456-789  | Mobile
1      | 987-654-321  | Home
1      | 555-123-456  | Work
2      | 111-222-333  | Mobile
3      | 444-555-666  | Mobile
3      | 777-888-999  | Work

Example 3: Complex Scenario - Library System

Before (Not 1NF):

Book_Loans Table:
Loan_ID | Member_Name | Books_Borrowed | Authors | Due_Dates
1       | John Smith  | Book1, Book2   | Auth1, Auth2 | 2024-01-15, 2024-01-20
2       | Mary Jones  | Book3          | Auth3        | 2024-01-18

After (1NF):

Members Table:
Member_ID | Member_Name
1         | John Smith
2         | Mary Jones

Books Table:
Book_ID | Book_Title | Author
1       | Book1      | Auth1
2       | Book2      | Auth2
3       | Book3      | Auth3

Loans Table:
Loan_ID | Member_ID | Book_ID | Due_Date
1       | 1         | 1       | 2024-01-15
2       | 1         | 2       | 2024-01-20
3       | 2         | 3       | 2024-01-18

Real-World Examples

E-commerce Order System

Incorrect (Not 1NF):

Orders Table:
Order_ID | Customer | Products | Quantities | Prices
1001     | John     | Laptop, Mouse, Keyboard | 1, 2, 1 | 1000, 25, 50
1002     | Mary     | Phone, Case | 1, 1 | 800, 20

Correct (1NF):

Customers Table:
Customer_ID | Customer_Name
1           | John
2           | Mary

Products Table:
Product_ID | Product_Name | Unit_Price
1          | Laptop       | 1000
2          | Mouse        | 25
3          | Keyboard     | 50
4          | Phone        | 800
5          | Case         | 20

Orders Table:
Order_ID | Customer_ID | Order_Date
1001     | 1           | 2024-01-15
1002     | 2           | 2024-01-16

Order_Items Table:
Order_ID | Product_ID | Quantity
1001     | 1          | 1
1001     | 2          | 2
1001     | 3          | 1
1002     | 4          | 1
1002     | 5          | 1

Employee Skills Management

Incorrect (Not 1NF):

Employee_Skills Table:
Emp_ID | Name | Skills | Skill_Levels | Certifications
101    | John | Java, Python, SQL | Expert, Intermediate, Advanced | Oracle_Java, None, MySQL
102    | Mary | C++, JavaScript | Advanced, Expert | None, W3C_JS

Correct (1NF):

Employees Table:
Emp_ID | Name
101    | John
102    | Mary

Skills Table:
Skill_ID | Skill_Name
1        | Java
2        | Python
3        | SQL
4        | C++
5        | JavaScript

Employee_Skills Table:
Emp_ID | Skill_ID | Skill_Level | Certification
101    | 1        | Expert      | Oracle_Java
101    | 2        | Intermediate| NULL
101    | 3        | Advanced    | MySQL
102    | 4        | Advanced    | NULL
102    | 5        | Expert      | W3C_JS

Benefits of 1NF

Data Integrity

  • Consistent Structure: Each attribute has predictable data type
  • No Ambiguity: Clear meaning for each data value
  • Easier Validation: Simple to validate atomic values

Query Simplicity

  • Standard SQL: Can use standard SQL operations
  • Indexing: Easier to create and use indexes
  • Searching: Simple search operations on atomic values

Maintenance

  • Updates: Easier to update individual values
  • Inserts: Straightforward insertion of new records
  • Deletes: Clear deletion without affecting other data

Common Mistakes

Storing JSON/XML

❌ AVOID:
Product_ID | Product_Info
1          | {"name": "Laptop", "specs": {"cpu": "Intel i7", "ram": "16GB"}}

Concatenated Values

❌ AVOID:
Customer_ID | Full_Address
1           | "123 Main St, City, State, 12345"

✅ CORRECT:
Customer_ID | Street      | City | State | Zip_Code
1           | 123 Main St | City | State | 12345

Multiple Meanings in Single Column

❌ AVOID:
Contact_ID | Contact_Info
1          | john@email.com
2          | 123-456-7890

✅ CORRECT:
Contact_ID | Contact_Type | Contact_Value
1          | Email        | john@email.com
2          | Phone        | 123-456-7890

Implementation Steps

Step 1: Identify Violations

  • Look for comma-separated values
  • Find repeating column patterns (col1, col2, col3)
  • Identify complex data types (JSON, arrays)

Step 2: Decompose Multi-valued Attributes

  • Create separate table for multi-valued attributes
  • Use foreign key to link back to original table
  • Ensure each value gets its own row

Step 3: Handle Repeating Groups

  • Identify repeating patterns in column names
  • Create new table with meaningful relationship
  • Move repeating data to new table

Step 4: Validate Results

  • Ensure each cell contains atomic value
  • Verify primary key exists and is unique
  • Check that no information is lost

Summary

  • Foundation: 1NF is the foundation for all database normalization
  • Atomic Values: Each attribute must contain only indivisible values
  • No Repetition: Eliminates repeating groups and multi-valued attributes
  • Enables Further Normalization: Required before applying 2NF, 3NF, etc.
  • Practical Benefit: Makes database queries simpler and more reliable

Graph View

Backlinks