Second Normal Form (2NF) in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding the Definition of 2NF

Which of the following best describes the Second Normal Form (2NF) in database normalization?

A A table is in 2NF if it is in 1NF and all non-key attributes are fully functionally dependent on the entire primary key.

B A table is in 2NF if it contains only atomic values and no nulls.

C A table is in 2NF if it has no transitive dependencies between non-key attributes.

D A table is in 2NF if it has no repeating groups and all attributes depend on the primary key.

💡 Hint

Recall that 2NF builds on 1NF by focusing on how attributes depend on the primary key.

2NF requires that the table is already in 1NF and that every non-key attribute depends on the whole primary key, not just part of it. This eliminates partial dependency.

🚀 Application

intermediate

Identifying Partial Dependency

Consider a table with a composite primary key (StudentID, CourseID) and attributes: StudentName, CourseName, and Grade. Which attribute violates 2NF?

A CourseID

B Grade

C StudentName

D CourseName

💡 Hint

Think about which attributes depend on only part of the composite key.

StudentName depends only on StudentID, not on CourseID, so it is partially dependent and violates 2NF.

🔍 Analysis

advanced

Evaluating Table Normalization Status

Given a table with primary key (OrderID, ProductID) and attributes: OrderDate, ProductName, Quantity, and SupplierName. Which attribute(s) cause the table to not be in 2NF?

A OrderDate and SupplierName

B ProductName and SupplierName

C Quantity only

D OrderDate only

💡 Hint

Consider which attributes depend on only part of the composite key.

ProductName and SupplierName depend only on ProductID, not on the full key, causing partial dependency and violating 2NF.

❓ Comparison

advanced

Difference Between 1NF and 2NF

Which statement correctly contrasts First Normal Form (1NF) and Second Normal Form (2NF)?

A 1NF requires atomic values; 2NF requires no partial dependency on a composite key.

B 1NF requires no partial dependency; 2NF requires atomic values.

C 1NF requires no transitive dependency; 2NF requires no repeating groups.

D 1NF requires full functional dependency; 2NF requires no null values.

💡 Hint

Think about what each normal form focuses on.

1NF ensures atomicity of data (no repeating groups), while 2NF eliminates partial dependencies on parts of a composite key.

❓ Reasoning

expert

Impact of Partial Dependency on Data Integrity

Why can partial dependency in a table with a composite primary key lead to data anomalies?

A Because transitive dependencies cause redundant data storage.

B Because atomic values are not enforced, leading to duplicate rows.

C Because the table cannot have a primary key defined, causing errors in queries.

D Because changes to a non-key attribute dependent on part of the key may require multiple updates, causing inconsistency.

💡 Hint

Consider how partial dependency affects update operations.

Partial dependency means some attributes depend only on part of the key, so updating those attributes may require multiple rows to be changed, risking inconsistency.