First Normal Form (1NF) in MySQL

Course: MySQL Training

Topic: First Normal Form (1NF)

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Database Normalization

Database normalization is a process of organizing data in relational database tables to reduce unnecessary duplication, improve consistency, and make data easier to maintain. It involves arranging tables and relationships according to specific rules called normal forms.

First Normal Form (1NF) is the starting point of normalization. It focuses on storing values in a consistent tabular structure, avoiding repeating groups, and ensuring that each field contains a single value for its intended attribute.

For example, a student record should not store several course names in a single ordinary text column when the application needs to manage each course independently. Instead, the database should represent each student-course association using separate rows or related tables.

Learning Objectives

• Understand the meaning and purpose of First Normal Form.
• Learn the characteristics of a table that satisfies 1NF.
• Identify atomic values and repeating groups.
• Recognize common violations of 1NF.
• Convert an unnormalized table into a 1NF structure.
• Use appropriate primary keys to identify records.
• Understand how 1NF supports data consistency and querying.
• Practice creating and querying normalized tables in MySQL.
• Distinguish 1NF from Second Normal Form and Third Normal Form.

2. What Is First Normal Form (1NF)?

First Normal Form is a relational database normalization rule requiring data to be represented without repeating groups and with a single value in each field for the attribute represented by that column.

In practical database design, a table should have a clear row structure, consistent columns, and a way to distinguish records. A primary key is commonly used to uniquely identify each row.

Main Principles of 1NF

• Atomic values: Each field stores one value for its intended attribute rather than a list of separate values.
• No repeating groups: The same kind of information should not be stored in repeated numbered columns such as phone1, phone2, and phone3 to represent a variable number of values.
• Consistent column meaning: Every value in a column should represent the same kind of attribute.
• Distinct rows: Rows should be distinguishable, normally through a primary key or another suitable candidate key.
• Consistent data structure: Values should be stored in an appropriate format for the column's purpose.

Important: atomicity depends on how an attribute is defined and used. A full name stored in one column may be acceptable if the application treats it as one attribute. If the application needs to search or manage first and last names independently, separate columns may be more suitable.

3. What Are Atomic Values?

An atomic value is a single value for the particular attribute represented by a column. It is not a collection of separate values that the application needs to manage individually.

Example of Non-Atomic Values

The phone_numbers column contains multiple phone numbers in one field. If the application needs to validate, search, update, or associate each number independently, this structure is difficult to manage and does not follow the intended 1NF design.

Example of Atomic Values

Each row now contains one phone number. However, repeating student information in this way can create unnecessary duplication. A separate phone-number table related to the student table is often a better design for a variable number of phone numbers.

4. Understanding Repeating Groups

A repeating group occurs when the same type of information is represented by multiple columns or fields in the same record.

Example of Repeating Groups

CREATE TABLE student_courses (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    course1 VARCHAR(100),
    course2 VARCHAR(100),
    course3 VARCHAR(100)
);

This design stores courses in numbered columns. It creates several problems:

• A student who enrolls in more than three courses requires additional columns.
• Students with fewer courses have unused columns.
• Queries must check multiple columns to find a course.
• Adding or removing courses can require structural changes or complicated updates.
• Course information is not represented consistently as a collection of individual records.

Better Design

Store students and courses in appropriate tables, then represent each enrollment as a separate row in a junction table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
);

This structure allows each student to enroll in any supported number of courses without adding new columns.

5. Why Is First Normal Form Important?

First Normal Form provides a consistent structure for storing and querying data. It is particularly important when individual values must be searched, validated, updated, sorted, or related to other tables.

Benefits of 1NF

• Improved data consistency: Values are stored in a predictable structure.
• Simpler queries: Individual values can be filtered and joined without parsing lists in ordinary text columns.
• Easier updates: A specific value can be changed without rewriting a list of unrelated values.
• Better data validation: Constraints can be applied to individual values.
• Flexible design: The database can handle changing numbers of related records.
• Foundation for further normalization: 1NF is a starting point for evaluating 2NF, 3NF, and other normal forms.

6. Example: An Unnormalized Student Table

Suppose a college stores student information and course enrollments in one table.

This table has multiple courses and phone numbers stored in individual fields as lists. It is inconvenient when the database needs to identify each enrollment or phone number independently.

Problems in the Table

• Finding all students enrolled in Python may require searching inside text values.
• Removing only one course from a student's list requires manipulating the text value.
• Adding another phone number changes the content of an existing field.
• Values cannot be reliably validated as individual course or phone-number records using ordinary column constraints alone.
• Applications may store the same course name with inconsistent spacing or spelling.

7. Converting an Unnormalized Table into 1NF

To convert the example into a 1NF structure, identify attributes that contain multiple values and represent each value separately. The exact table design depends on how the application uses the information.

Step 1: Separate Student Information

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

Step 2: Store Courses Separately

CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL UNIQUE
);

Step 3: Store Each Enrollment as a Separate Row

CREATE TABLE student_courses (
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id)
        REFERENCES students(student_id),
    FOREIGN KEY (course_id)
        REFERENCES courses(course_id)
);

Step 4: Store Phone Numbers in Their Own Table

CREATE TABLE student_phones (
    phone_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    FOREIGN KEY (student_id)
        REFERENCES students(student_id)
);

The separate phone table is useful when a student may have multiple numbers. The phone_id identifies each phone record. If the application has a business rule about whether the same number can be associated with one or multiple students, enforce that rule separately.

8. Inserting Data into the 1NF Tables

8.1 Insert Students

INSERT INTO students (student_id, student_name)
VALUES
    (101, 'Aarav Sharma'),
    (102, 'Priya Mehta'),
    (103, 'Rahul Verma');

8.2 Insert Courses

INSERT INTO courses (course_name)
VALUES
    ('MySQL'),
    ('Python'),
    ('Java'),
    ('HTML');

8.3 Insert Student-Course Associations

First, retrieve the course IDs assigned by MySQL:

SELECT course_id, course_name
FROM courses
ORDER BY course_id;

Assuming the course IDs are 1 for MySQL, 2 for Python, 3 for Java, and 4 for HTML, insert the enrollments as follows:

INSERT INTO student_courses (student_id, course_id)
VALUES
    (101, 1),
    (101, 2),
    (101, 3),
    (102, 1),
    (102, 4),
    (103, 2),
    (103, 3);

For a reusable application or script, retrieve course IDs by their actual values rather than assuming generated IDs will always be the same.

8.4 Insert Phone Numbers

INSERT INTO student_phones (student_id, phone_number)
VALUES
    (101, '9876501111'),
    (101, '9876502222'),
    (102, '9876503333'),
    (103, '9876504444'),
    (103, '9876505555');

Each phone number is now stored in a separate row, and each course enrollment is also represented by a separate row.

9. Retrieving Data from 1NF Tables

9.1 Display All Students

SELECT *
FROM students;

9.2 Display All Courses

SELECT *
FROM courses;

9.3 Display Each Student with Their Courses

SELECT
    s.student_id,
    s.student_name,
    c.course_name
FROM students AS s
JOIN student_courses AS sc
    ON s.student_id = sc.student_id
JOIN courses AS c
    ON sc.course_id = c.course_id
ORDER BY s.student_id, c.course_name;

9.4 Display Each Student with Their Phone Numbers

SELECT
    s.student_id,
    s.student_name,
    sp.phone_number
FROM students AS s
LEFT JOIN student_phones AS sp
    ON s.student_id = sp.student_id
ORDER BY s.student_id, sp.phone_number;

These queries retrieve related information by using relational joins rather than searching inside comma-separated text values.

10. Another Example: Product and Product Categories

Suppose an online store stores several category names in one product field.

Table That Does Not Follow the Intended 1NF Design

If categories must be managed individually, storing multiple category names in one ordinary field makes it difficult to maintain category details and relationships.

Normalized Structure

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL
);

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE product_categories (
    product_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (product_id, category_id),
    FOREIGN KEY (product_id)
        REFERENCES products(product_id),
    FOREIGN KEY (category_id)
        REFERENCES categories(category_id)
);

This design supports multiple categories per product and multiple products per category without storing category lists in a single field.

11. First Normal Form and Primary Keys

A primary key uniquely identifies each row in a table. Using a suitable primary key makes records easier to update, reference, and maintain.

Example

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100) NOT NULL
);

Here, employee_id uniquely identifies each employee. Two employees may share the same name or department, but they cannot share the same primary-key value.

Composite Primary Keys

Some tables represent relationships rather than standalone entities. A composite primary key can identify each relationship uniquely.

CREATE TABLE employee_projects (
    employee_id INT NOT NULL,
    project_id INT NOT NULL,
    PRIMARY KEY (employee_id, project_id)
);

In this example, the combination of employee and project IDs identifies an assignment. In a complete schema, both columns should normally reference their respective parent tables through foreign keys.

12. Does Every Column Need to Be Individually Unique?

No. First Normal Form does not mean that every column must contain unique values. It means that fields should represent single values for their intended attributes and that rows should be distinguishable.

Uniqueness is a property of candidate keys and constraints, not a requirement that every individual column contain different values.

13. First Normal Form and NULL Values

NULL represents missing, unknown, or inapplicable information, depending on the application's data model. A nullable column can contain NULL without necessarily violating 1NF. However, NULL is not the same as an empty string, zero, or a list of values.

Example

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL
);

A student who has no recorded middle name can have NULL in the middle_name column. This is different from placing several middle names or other values in one field.

Whether a field should allow NULL depends on the business rules. Use NOT NULL when a value is required.

14. First Normal Form and Data Types

Choosing an appropriate data type helps store each value consistently and makes filtering, sorting, validation, and calculations more reliable.

A suitable data type does not by itself guarantee 1NF. For example, a VARCHAR column can still contain a comma-separated list of values. The structure and meaning of the stored data must also be considered.

15. Common Violations of First Normal Form

Not every compound-looking value is automatically a violation. A postal address, full name, or JSON document may be intentionally treated as one attribute in a particular application. Split values when their components must be independently represented or manipulated in the relational model.

16. Advantages and Limitations of 1NF

Advantages

• Provides a clear structure for individual values.
• Makes filtering and sorting individual values easier.
• Supports reliable relationships between tables.
• Allows database constraints to operate on clearly defined columns.
• Helps prepare a schema for additional normalization.
• Reduces the need to parse lists stored in ordinary text fields.

Limitations

• 1NF alone does not eliminate all data duplication.
• A table can satisfy 1NF while still having update, insertion, or deletion anomalies.
• It does not automatically resolve partial dependencies or transitive dependencies.
• Splitting data into multiple tables may require JOIN operations.
• Good table design still requires analysis of functional dependencies and business rules.

17. First Normal Form vs. Second Normal Form vs. Third Normal Form

First Normal Form addresses the structure of values and repeating groups. Higher normal forms examine how attributes depend on keys and on other attributes.

18. Practical Example: Employee Skills Database

Suppose a company wants to store employees and the skills they possess.

Unnormalized Design

The skills column contains multiple values. This is inconvenient if the company needs to search for employees with a particular skill or maintain skill names consistently.

Step 1: Create Employees

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL
);

Step 2: Create Skills

CREATE TABLE skills (
    skill_id INT AUTO_INCREMENT PRIMARY KEY,
    skill_name VARCHAR(100) NOT NULL UNIQUE
);

Step 3: Create Employee-Skill Associations

CREATE TABLE employee_skills (
    employee_id INT NOT NULL,
    skill_id INT NOT NULL,
    PRIMARY KEY (employee_id, skill_id),
    FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id),
    FOREIGN KEY (skill_id)
        REFERENCES skills(skill_id)
);

Step 4: Insert Sample Data

INSERT INTO employees (employee_name)
VALUES ('Amit Shah'), ('Neha Patel');

INSERT INTO skills (skill_name)
VALUES ('SQL'), ('Excel'), ('Python'), ('Power BI');

Retrieve the skill IDs before creating associations:

SELECT skill_id, skill_name
FROM skills;

Assuming SQL is 1, Excel is 2, Python is 3, and Power BI is 4, the associations can be inserted as follows:

INSERT INTO employee_skills (employee_id, skill_id)
VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 2),
    (2, 4);

Step 5: Display Employees and Their Skills

SELECT
    e.employee_name,
    s.skill_name
FROM employees AS e
JOIN employee_skills AS es
    ON e.employee_id = es.employee_id
JOIN skills AS s
    ON es.skill_id = s.skill_id
ORDER BY e.employee_name, s.skill_name;

This design stores each employee-skill association separately and makes it easier to query skills across the company.

19. Troubleshooting Common Design Problems

Problem 1: Searching for Values Inside a Comma-Separated Column

Problem: The application needs to find students enrolled in Python, but course names are stored in a comma-separated field.

Why it is difficult: Queries may need string matching, which can produce false matches and makes validation harder.

Solution: Store courses and student-course associations in separate tables and use JOIN operations.

Problem 2: Too Many Repeating Columns

Problem: A table has phone1, phone2, phone3, and additional numbered columns.

Solution: Move phone numbers to a related table so each number occupies a separate row.

Problem 3: Duplicate Records After Restructuring

Problem: Converting a list into separate rows may produce duplicate associations.

Solution: Check for duplicates and define an appropriate key. For a student-course relationship, a composite primary key on (student_id, course_id) may be suitable if each student can enroll in a course only once.

Problem 4: Incorrectly Splitting Every Text Field

Problem: A designer splits every text attribute into smaller fields even when the application does not need the components independently.

Solution: Determine the meaning and usage of each attribute before splitting it. 1NF is about representing values appropriately, not mechanically splitting every string.

20. Checking a Table's Structure in MySQL

MySQL does not provide a single command that automatically certifies a table as satisfying every conceptual requirement of 1NF. Review the table structure and data based on the application's attribute definitions.

20.1 Describe a Table

DESCRIBE students;

20.2 View the Table Definition

SHOW CREATE TABLE students;

20.3 Check for Duplicate Student-Course Associations

SELECT
    student_id,
    course_id,
    COUNT(*) AS total_records
FROM student_courses
GROUP BY student_id, course_id
HAVING COUNT(*) > 1;

If a composite primary key already exists on these columns, duplicate pairs cannot normally be stored. The query is still useful when auditing a legacy table before adding the constraint.

20.4 Review Column Values

SELECT
    student_id,
    student_name
FROM students
ORDER BY student_id;

Check whether columns consistently represent their intended attributes and whether multi-valued fields should be represented using related rows.

21. Best Practices for Applying First Normal Form

• Give each column one clearly defined meaning.
• Store a single value per field for the attribute being modeled.
• Avoid numbered columns for variable-length collections of similar values.
• Use separate tables for independently managed repeating values.
• Define suitable primary keys or candidate keys.
• Use foreign keys to maintain relationships between tables.
• Choose data types that match the values being stored.
• Use NOT NULL, UNIQUE, and CHECK constraints when they reflect business rules.
• Do not confuse NULL with an empty string or a collection of values.
• Do not assume that satisfying 1NF automatically means the schema is fully normalized.
• Test queries and updates after restructuring a table.
• Back up important data and validate migration results when converting existing tables.

22. Practice Exercises

1. Create a student table with a column containing several course names and explain why that structure is inconvenient.
2. Redesign the student-course example using a students table, a courses table, and a junction table.
3. Create an employee table with phone1, phone2, and phone3 columns, then redesign it using a separate phone table.
4. Insert five students and at least two courses for each student.
5. Write a query to display every student and their courses.
6. Write a query to find all students enrolled in a specific course.
7. Create a product table and category table connected through a junction table.
8. Explain why a comma-separated list is harder to validate than separate rows.
9. Identify whether a nullable middle-name column automatically violates 1NF and explain your answer.
10. Compare the objectives of 1NF, 2NF, and 3NF.
11. Inspect a table with DESCRIBE and SHOW CREATE TABLE.
12. Explain why 1NF does not require every column to contain unique values.

23. Interview Questions and Answers

Q1. What is First Normal Form?

Answer: First Normal Form is a relational database normalization rule that requires values to be represented in a consistent tabular structure without repeating groups and with single values for the attributes represented by each field.

Q2. What is an atomic value?

Answer: An atomic value is one value for the attribute being represented by a field, rather than a collection of separate values that need to be managed individually.

Q3. What is a repeating group?

Answer: A repeating group occurs when the same type of information is stored in repeated columns or fields, such as phone1, phone2, and phone3.

Q4. Does 1NF require every column to be unique?

Answer: No. Individual columns can contain repeated values. Uniqueness is enforced by suitable candidate keys, primary keys, or UNIQUE constraints.

Q5. How can a table with multiple phone numbers in one column be redesigned?

Answer: Store the phone numbers as separate rows in a related table, with a foreign key referencing the person or student.

Q6. Does NULL violate 1NF?

Answer: A nullable column does not automatically violate 1NF. NULL represents missing, unknown, or inapplicable information and is distinct from storing a list of values.

Q7. Why is a primary key useful?

Answer: A primary key uniquely identifies each row and supports reliable references and updates.

Q8. What is the difference between 1NF and 2NF?

Answer: 1NF focuses on atomic values and repeating groups. 2NF additionally addresses partial dependencies of non-key attributes on a proper subset of a candidate key.

Q9. Can a table satisfy 1NF and still contain duplicated data?

Answer: Yes. A table can satisfy 1NF while still having redundancy or anomalies that require further normalization.

Q10. Why use a junction table?

Answer: A junction table represents associations between two entities, especially when the relationship is many-to-many. Each association is stored as a separate row.

Q11. Does using VARCHAR guarantee atomic values?

Answer: No. A VARCHAR column can still contain a comma-separated list. Whether the value is atomic depends on the intended meaning of the attribute.

Q12. How does 1NF improve database querying?

Answer: It makes individual values easier to filter, sort, validate, join, and update without parsing lists stored inside ordinary text columns.

24. Quick Revision Checklist

• 1NF is the first level of relational database normalization.
• Each field should contain a single value for its intended attribute.
• Repeating groups should be removed from the table design.
• Variable numbers of related values are often best represented in separate rows.
• Primary keys help uniquely identify records.
• Foreign keys connect related tables.
• A junction table is useful for many-to-many relationships.
• Not every column needs unique values.
• NULL does not automatically violate 1NF.
• A table in 1NF can still have redundancy and anomalies.
• 2NF and 3NF address additional dependency-related design issues.
• MySQL table definitions and queries can help audit a schema, but 1NF requires conceptual review of the data model.

25. Conclusion

First Normal Form is a fundamental concept in MySQL database design. It encourages developers to store data in a consistent tabular structure, avoid repeating groups, and represent independently managed values as separate fields or rows.

When a column contains a list of courses, phone numbers, or other values that need to be managed separately, consider redesigning the schema with related tables and appropriate keys. This improves the clarity of the data model and makes querying, validation, and updates easier.

Remember that 1NF is the starting point, not the end of normalization. After ensuring that a table follows 1NF, evaluate its keys and dependencies to determine whether additional normalization is needed.

Explore the course: MySQL Training Course at JustAcademy

Register for a demo: Book a MySQL Course Demo