First Normal Form (1NF) in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Identify 1NF Violation in Table Data

Consider the following table Orders:

OrderID | Customer | Items
------- | -------- | -----------
1       | Alice    | Pen, Notebook
2       | Bob      | Pencil
3       | Carol    | Eraser, Sharpener, Ruler

Which of the following statements best describes why this table violates the First Normal Form (1NF)?

A The <code>Customer</code> column contains NULL values.

B The <code>OrderID</code> column has duplicate values.

C The <code>Items</code> column contains multiple values in a single cell, violating atomicity.

D The table has no primary key defined.

💡 Hint

1NF requires each column to hold atomic (indivisible) values.

1NF requires that each column contains atomic values. Here, the Items column holds multiple items in one cell, which breaks this rule.

❓ query_result

intermediate

Result of Query on 1NF Compliant Table

Given a table Students with columns StudentID, Name, and PhoneNumber, where each student has only one phone number stored per row (1NF compliant). The table data is:

StudentID | Name  | PhoneNumber
--------- | ----- | -----------
1         | John  | 123-4567
2         | Jane  | 234-5678
3         | Mike  | 345-6789

What will be the output of the query:

SELECT Name, PhoneNumber FROM Students WHERE PhoneNumber LIKE '234%';

A Name: Jane, PhoneNumber: 234-5678

B Name: John, PhoneNumber: 123-4567

C No rows returned

D Name: Mike, PhoneNumber: 345-6789

💡 Hint

Look for phone numbers starting with '234'.

The query filters phone numbers starting with '234', which matches Jane's number only.

❓ schema

advanced

Design a 1NF Compliant Table Schema

You need to design a table Books to store book information including multiple authors per book. Which schema design ensures the table complies with First Normal Form (1NF)?

A Books(BookID INT, Title VARCHAR, Authors JSON) storing authors as a JSON array.

B Books(BookID INT, Title VARCHAR), and a separate Authors(BookID INT, AuthorName VARCHAR) table linking authors to books.

C Books(BookID INT, Title VARCHAR, Author1 VARCHAR, Author2 VARCHAR, Author3 VARCHAR) with fixed author columns.

D Books(BookID INT, Title VARCHAR, Authors VARCHAR) where Authors stores comma-separated author names.

💡 Hint

1NF requires atomic values and no repeating groups in a single table.

Option B separates authors into their own table with one author per row, ensuring atomicity and compliance with 1NF.

🔧 Debug

advanced

Identify the 1NF Violation in SQL Table Definition

Review the following SQL table definition:

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  Name VARCHAR(100),
  Skills VARCHAR(255)
);

The Skills column stores multiple skills separated by commas (e.g., 'SQL,Python,Java'). What is the problem with this design regarding First Normal Form (1NF)?

A The <code>Skills</code> column violates 1NF because it stores multiple values in one field.

B The <code>EmployeeID</code> is not unique.

C The <code>Name</code> column allows NULL values.

D The table lacks a foreign key constraint.

💡 Hint

1NF requires each column to hold atomic values.

Storing multiple skills as a comma-separated string violates atomicity, breaking 1NF.

🧠 Conceptual

expert

Why is First Normal Form (1NF) Important in Database Design?

Which of the following best explains the main reason why First Normal Form (1NF) is essential in relational database design?

A It reduces data redundancy by splitting tables into smaller related tables.

B It ensures that each table has a primary key to uniquely identify rows.

C It enforces referential integrity between tables using foreign keys.

D It guarantees that all columns contain atomic values, preventing multi-valued attributes and enabling reliable querying.

💡 Hint

Think about what atomic values mean and why they matter.

1NF requires atomic values in columns to avoid multi-valued attributes, which makes querying and data manipulation consistent and reliable.

Practice

1. What does First Normal Form (1NF) require in a database table?

easy

A. Each table must have a primary key.

B. Tables must have at least two columns.

C. Each column must contain atomic (single) values only.

D. Columns can contain lists or multiple values.

Solution

1. Step 1: Understand 1NF definition

   1NF requires that each column holds only one atomic value per row, no lists or sets.

2. Step 2: Evaluate options

   Each column must contain atomic (single) values only. matches the 1NF rule exactly. Options A, B, and D describe other concepts or violate 1NF.

3. Final Answer:

   Each column must contain atomic (single) values only. -> Option C

4. Quick Check:

   1NF = atomic columns only [OK]

Hint: 1NF means no multiple values in one cell [OK]

Common Mistakes:

• Thinking 1NF requires a primary key
• Allowing lists or sets in columns
• Confusing 1NF with other normal forms

2. Which of the following SQL table definitions violates First Normal Form (1NF)?

easy

A. CREATE TABLE Students (ID INT, Name VARCHAR(50), PhoneNumbers VARCHAR(100));

B. CREATE TABLE Students (ID INT PRIMARY KEY, Name VARCHAR(50), PhoneNumber VARCHAR(15));

C. CREATE TABLE Students (ID INT, Name VARCHAR(50), Email VARCHAR(50));

D. CREATE TABLE Students (ID INT, Name VARCHAR(50), Address VARCHAR(100));

Solution

1. Step 1: Identify 1NF violation in table definitions

   CREATE TABLE Students (ID INT, Name VARCHAR(50), PhoneNumbers VARCHAR(100)); uses PhoneNumbers as VARCHAR(100), likely storing multiple numbers in one field, violating 1NF.

2. Step 2: Check other options

   Options A, B, and C have single atomic values per column, so they follow 1NF.

3. Final Answer:

   CREATE TABLE Students (ID INT, Name VARCHAR(50), PhoneNumbers VARCHAR(100)); -> Option A

4. Quick Check:

   Multiple values in one column break 1NF [OK]

Hint: Multiple values in one column break 1NF [OK]

Common Mistakes:

• Assuming VARCHAR can hold multiple values without issue
• Confusing primary key with 1NF rules
• Ignoring column data types when checking 1NF

3. Given the table below, which row violates First Normal Form (1NF)?

Orders(OrderID, Customer, Items)
Where Items column contains comma-separated product IDs.

Rows:
1. (101, 'Alice', 'P1')
2. (102, 'Bob', 'P2,P3')
3. (103, 'Carol', 'P4')
4. (104, 'Dave', 'P5')

medium

A. Row 1

B. Row 2

C. Row 3

D. Row 4

Solution

1. Step 1: Understand 1NF violation in data rows

   Row 2 has 'P2,P3' in Items, which is multiple values in one cell, violating 1NF.

2. Step 2: Check other rows

   Rows 1, 3, and 4 have single product IDs, so they follow 1NF.

3. Final Answer:

   Row 2 -> Option B

4. Quick Check:

   Multiple values in one cell break 1NF [OK]

Hint: Comma-separated values in one cell break 1NF [OK]

Common Mistakes:

• Ignoring multiple values separated by commas
• Assuming string with commas is atomic
• Not checking all rows carefully

4. You have this table definition violating 1NF:

CREATE TABLE Employees (ID INT, Name VARCHAR(50), Skills VARCHAR(100));

Skills column stores multiple skills separated by commas.
Which SQL change fixes the 1NF violation?

medium

A. Store skills as a single string with semicolons instead of commas.

B. Change Skills to TEXT and store JSON arrays.

C. Keep Skills as VARCHAR but limit length to 50.

D. Create a separate Skills table with EmployeeID and Skill columns.

Solution

1. Step 1: Identify 1NF violation and fix approach

   Storing multiple skills in one column violates 1NF. The fix is to separate repeating data into another table.

2. Step 2: Evaluate options for 1NF compliance

   Create a separate Skills table with EmployeeID and Skill columns. creates a separate Skills table with one skill per row, fixing 1NF. Options B, C, and D still store multiple values in one column.

3. Final Answer:

   Create a separate Skills table with EmployeeID and Skill columns. -> Option D

4. Quick Check:

   Separate repeating data into new table for 1NF [OK]

Hint: Use separate table for repeating groups to fix 1NF [OK]

Common Mistakes:

• Trying to store multiple values in one column anyway
• Using JSON or delimiters inside one column
• Not normalizing repeating data into separate tables

5. You have a table storing orders with multiple products in one column:

Orders(OrderID INT, Customer VARCHAR(50), Products VARCHAR(255))
Products column stores product IDs separated by commas.

How do you redesign the schema to fully comply with First Normal Form (1NF)?

hard

A. Create a new table OrderProducts(OrderID, ProductID) with one product per row.

B. Split Products into multiple columns like Product1, Product2, Product3.

C. Store Products as JSON array in the same column.

D. Keep Products column but restrict to one product ID only.

Solution

1. Step 1: Identify 1NF violation in current design

   Storing multiple product IDs in one column violates 1NF because values are not atomic.

2. Step 2: Choose redesign that enforces atomicity

   Create a new table OrderProducts(OrderID, ProductID) with one product per row. creates a new table with one product per row, fully normalizing the data and satisfying 1NF. Split Products into multiple columns like Product1, Product2, Product3. uses multiple columns but is limited and not scalable. Options C and D still violate 1NF.

3. Final Answer:

   Create a new table OrderProducts(OrderID, ProductID) with one product per row. -> Option A

4. Quick Check:

   Separate repeating data into rows for 1NF compliance [OK]

Hint: Use separate table with one value per row for 1NF [OK]

Common Mistakes:

• Adding multiple columns instead of rows
• Using JSON or arrays inside one column
• Restricting to one product but losing data