Second Normal Form (2NF) in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is Second Normal Form (2NF) in database design?

2NF means a table is in First Normal Form (1NF) and all non-key columns depend on the whole primary key, not just part of it.

beginner

Why do we use Second Normal Form (2NF)?

To remove partial dependencies and avoid duplicate data, making the database easier to maintain and update.

intermediate

What is a partial dependency in the context of 2NF?

A partial dependency happens when a non-key column depends on only part of a composite primary key, not the whole key.

intermediate

Given a table with a composite key (StudentID, CourseID) and a column 'StudentName', why is this a problem for 2NF?

'StudentName' depends only on 'StudentID', part of the key, causing a partial dependency. This violates 2NF.

intermediate

How do you fix a table that violates 2NF?

Split the table into two: one with the full key and dependent columns, and another with the partial key and its dependent columns.

What must a table already satisfy before it can be in Second Normal Form (2NF)?

A It must be in First Normal Form (1NF)

B It must have no null values

C It must have only one column

D It must have no foreign keys

2NF requires the table to be in 1NF first, meaning no repeating groups and atomic columns.

What does 2NF primarily aim to eliminate?

A Partial dependencies

B Transitive dependencies

C Duplicate rows

D Null values

2NF removes partial dependencies where columns depend on part of a composite key.

If a table has a single-column primary key, is it automatically in 2NF?

A No, it must be in 3NF first

B No, it must have multiple keys

C Yes, but only if it has no nulls

D Yes, because no partial dependency is possible

With a single-column key, partial dependencies cannot exist, so 2NF is automatically satisfied.

Which of these is an example of a partial dependency?

A A column depends on the entire primary key

B A column depends on only part of a composite key

C A column depends on a non-key column

D A column has null values

Partial dependency means a column depends on only part of a composite key.

How can you fix a partial dependency in a table?

A Remove the primary key

B Add more columns to the table

C Split the table into two tables to separate dependencies

D Add null values to dependent columns

Splitting the table removes partial dependencies by isolating columns that depend on part of the key.

Explain Second Normal Form (2NF) and why it is important in database design.

Think about how columns relate to the whole primary key.

Describe how you would identify and fix a partial dependency in a table with a composite primary key.

Focus on which columns depend on which parts of the key.