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

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the core rule of 1NF

Which of the following best describes the main requirement of First Normal Form (1NF) in database design?

A All columns must contain only numeric data types.

B Each column must contain atomic, indivisible values with no repeating groups.

C Each table must have a foreign key referencing another table.

D Tables must be split into multiple databases.

💡 Hint

Think about how data is stored in each cell of a table.

1NF requires that each column holds atomic values, meaning no lists or sets inside a single cell, and no repeating groups of columns.

🚀 Application

intermediate

Identifying violation of 1NF

Given a table where a column 'PhoneNumbers' contains multiple phone numbers separated by commas in a single cell, what is the issue related to 1NF?

A The column violates 1NF because it contains multiple values in one cell.

B The column is correctly designed as it stores all phone numbers together.

C The table violates 1NF because it lacks a primary key.

D There is no violation; 1NF allows multiple values in one cell.

💡 Hint

Recall what atomic values mean in 1NF.

1NF requires each cell to hold only one value. Multiple phone numbers in one cell break this rule.

🔍 Analysis

advanced

Analyzing a table for 1NF compliance

Consider a table with columns: StudentID, Name, and Courses. The 'Courses' column contains a list of course names separated by semicolons. What is the correct way to bring this table into 1NF?

A Split the 'Courses' column so each course is in a separate row linked to the same StudentID.

B Keep the 'Courses' column as is because it shows all courses a student takes.

C Combine 'StudentID' and 'Courses' into one column to avoid lists.

D Remove the 'Courses' column entirely to comply with 1NF.

💡 Hint

Think about how to make each cell atomic without losing information.

To comply with 1NF, each cell must hold a single value. Splitting courses into separate rows keeps data atomic and preserves relationships.

❓ Comparison

advanced

Comparing 1NF with other normal forms

Which statement correctly distinguishes First Normal Form (1NF) from Second Normal Form (2NF)?

A 1NF requires tables to be split into multiple databases; 2NF requires atomic columns.

B 1NF requires no duplicate rows; 2NF requires atomic columns.

C 1NF requires foreign keys; 2NF requires unique keys.

D 1NF requires atomic columns; 2NF requires that all non-key columns depend fully on the primary key.

💡 Hint

Focus on the difference in dependency and atomicity rules.

1NF focuses on atomicity of data, while 2NF focuses on removing partial dependencies on a composite primary key.

❓ Reasoning

expert

Evaluating 1NF compliance in a complex scenario

A table stores customer orders with columns: OrderID, CustomerName, and ItemsOrdered. The 'ItemsOrdered' column contains JSON strings listing multiple items and quantities. Does this table comply with 1NF?

A Yes, because JSON is a single string and counts as atomic data.

B Yes, because 1NF allows complex data types like JSON in columns.

C No, because storing JSON strings means the column contains multiple values, violating atomicity.

D No, because the table lacks a composite primary key.

💡 Hint

Consider what atomic means beyond just data type.

Even if JSON is stored as a string, it represents multiple values inside one cell, violating 1NF's atomicity rule.

Practice

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

easy

A. Tables have multiple primary keys

B. Tables have no foreign keys

C. Each cell contains only one atomic value

D. Data is encrypted for security

Solution

1. Step 1: Understand the definition of 1NF

   1NF requires that each column in a table holds atomic (indivisible) values, meaning no multiple values or lists in a single cell.

2. Step 2: Compare options with 1NF definition

   Only Each cell contains only one atomic value matches this requirement. Other options relate to different concepts like keys or security.

3. Final Answer:

   Each cell contains only one atomic value -> Option C

4. Quick Check:

   1NF = atomic values only [OK]

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

Common Mistakes:

• Confusing 1NF with encryption or keys
• Thinking 1NF allows multiple values in a cell
• Mixing 1NF with higher normal forms

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

easy

A. A table with a column storing multiple phone numbers separated by commas

B. A table where each column contains only single values

C. A table with unique primary keys for each row

D. A table with no repeating rows

Solution

1. Step 1: Identify the 1NF violation

   1NF forbids multiple values in a single cell. Storing multiple phone numbers separated by commas means one cell holds multiple values.

2. Step 2: Check other options

   Options B, C, and D follow 1NF rules because they have atomic values and unique rows.

3. Final Answer:

   A table with a column storing multiple phone numbers separated by commas -> Option A

4. Quick Check:

   Multiple values in one cell violate 1NF [OK]

Hint: Look for columns with multiple values in one cell [OK]

Common Mistakes:

• Assuming commas separate values correctly in 1NF
• Confusing unique rows with atomic values
• Ignoring multi-valued columns

3. Consider this table data:
StudentID | Courses
1 | Math, Science
2 | English
3 | Math, English
Which statement is true about this table regarding 1NF?

medium

A. The table violates 1NF because the Courses column has multiple values

B. The table is in 1NF because Courses are listed as text

C. The table is in 1NF because each row has a unique StudentID

D. The table violates 1NF because StudentID is not unique

Solution

1. Step 1: Analyze the Courses column

   The Courses column contains multiple values separated by commas in some rows, which means cells are not atomic.

2. Step 2: Check uniqueness and 1NF rules

   Although StudentID is unique, 1NF requires atomic values in each cell. Multiple courses in one cell violate 1NF.

3. Final Answer:

   The table violates 1NF because the Courses column has multiple values -> Option A

4. Quick Check:

   Multiple values in one cell break 1NF [OK]

Hint: Check if any cell holds multiple values separated by commas [OK]

Common Mistakes:

• Thinking unique keys alone satisfy 1NF
• Ignoring multi-valued columns
• Assuming text format means atomic values

4. You have a table with columns: OrderID, ProductIDs where ProductIDs stores multiple product IDs separated by semicolons. How would you fix this to comply with 1NF?

medium

A. Remove the ProductIDs column entirely

B. Keep the table as is because semicolons separate values

C. Combine all ProductIDs into one string without separators

D. Split the table so each row has one ProductID per OrderID

Solution

1. Step 1: Identify the 1NF violation

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

2. Step 2: Apply 1NF correction

   Splitting the table so each row has one ProductID per OrderID ensures atomic values and removes repeating groups.

3. Final Answer:

   Split the table so each row has one ProductID per OrderID -> Option D

4. Quick Check:

   One value per cell fixes 1NF violation [OK]

Hint: Make each cell hold only one value by splitting rows [OK]

Common Mistakes:

• Thinking separators alone satisfy 1NF
• Removing columns instead of fixing data structure
• Combining values into one string without atomicity

5. A table stores employee skills as follows:
EmpID | Skills
101 | Java, Python
102 | Python
103 | Java, SQL, Python
How can you transform this table into 1NF compliant form?

hard

A. Keep skills as comma-separated values in one cell

B. Create a new row for each skill per EmpID

C. Remove the Skills column and store skills elsewhere

D. Combine all skills into a single string without commas

Solution

1. Step 1: Identify multi-valued attribute

   The Skills column contains multiple skills separated by commas, violating 1NF.

2. Step 2: Normalize by creating multiple rows

   To comply with 1NF, create a separate row for each skill linked to the same EmpID, making each cell atomic.

3. Final Answer:

   Create a new row for each skill per EmpID -> Option B

4. Quick Check:

   One skill per row ensures 1NF compliance [OK]

Hint: Turn multi-valued cells into multiple rows [OK]

Common Mistakes:

• Leaving multi-valued cells unchanged
• Removing data instead of restructuring
• Combining values without atomicity