First Normal Form (1NF) in SQL - Step-by-Step Execution

Concept Flow - First Normal Form (1NF)

Start with a Table

↓

Check for Atomicity

Yes / No?↓

Table in 1NF

↩[Split Multi- [Check for Repeating

Start with a table, check if each column has atomic (single) values and no repeating groups. If not, split or remove them until the table meets 1NF.

Execution Sample

SQL

CREATE TABLE Orders (
  OrderID INT,
  CustomerName VARCHAR(50),
  Items VARCHAR(100) -- comma separated
);

This table has a column 'Items' with multiple values in one field, which violates 1NF.

Execution Table

Step	Action	Table State	Reason
1	Initial table created	OrderID | CustomerName | Items 1 | Alice | 'Pen, Notebook' 2 | Bob | 'Pencil'	Items column has multiple values in one field (not atomic)
2	Check atomicity of 'Items'	Same as above	Items column violates 1NF because it stores multiple items in one field
3	Split 'Items' into separate rows	OrderID | CustomerName | Item 1 | Alice | Pen 1 | Alice | Notebook 2 | Bob | Pencil	Each field now atomic, no repeating groups
4	Table now in 1NF	Same as above	All columns have atomic values, no repeating groups

💡 Table meets 1NF after splitting multi-valued fields into atomic values in separate rows

Variable Tracker

Variable	Start	After Step 3	Final
Table Rows	2 rows with multi-valued Items	3 rows with atomic Item values	3 rows with atomic Item values
Items Column	'Pen, Notebook', 'Pencil'	'Pen', 'Notebook', 'Pencil'	'Pen', 'Notebook', 'Pencil'

Key Moments - 2 Insights

Why can't we keep multiple items in one 'Items' column field?

Because 1NF requires each column to hold atomic (single) values only. The execution_table row 2 shows that having 'Pen, Notebook' in one field violates this rule.

How do we fix a table that violates 1NF due to multi-valued fields?

We split the multi-valued field into multiple rows so each row has one atomic value, as shown in execution_table row 3.

Visual Quiz - 3 Questions

Look at the execution_table at Step 2. What is the problem with the 'Items' column?

A It contains atomic values

B It has missing values

C It contains multiple values in one field

D It is the primary key

💡 Hint

Check the 'Reason' column in Step 2 of execution_table

At which step does the table first meet First Normal Form (1NF)?

A Step 1

B Step 4

C Step 3

D Step 2

💡 Hint

Look at the 'Reason' column in execution_table for when the table is in 1NF

If we did not split the 'Items' column, what would happen to the number of rows?

A It would stay the same

B It would decrease

C It would increase

D It would become zero

💡 Hint

Check variable_tracker for 'Table Rows' before and after splitting

Concept Snapshot

First Normal Form (1NF):
- Each table column must hold atomic (single) values.
- No repeating groups or arrays in columns.
- Multi-valued fields must be split into separate rows.
- Ensures data is organized for easy querying and updates.

Full Transcript

First Normal Form (1NF) means every column in a database table must have single, atomic values. If a column has multiple values in one field, like a list of items separated by commas, it breaks 1NF. To fix this, we split those multi-valued fields into separate rows so each field holds only one value. This process removes repeating groups and makes the table easier to work with. The example shows a table with an 'Items' column holding multiple items per row. After splitting, each item gets its own row, and the table meets 1NF. This helps keep data clean and consistent.