Third Normal Form (3NF) in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the main goal of Third Normal Form (3NF) in database design?

The main goal of 3NF is to remove transitive dependencies, ensuring that non-key columns depend only on the primary key, which helps avoid data duplication and update anomalies.

intermediate

Define transitive dependency in the context of 3NF.

A transitive dependency occurs when a non-key column depends on another non-key column, which in turn depends on the primary key. 3NF removes these to keep data consistent.

beginner

Which of the following must a table satisfy to be in Third Normal Form (3NF)?<br>1. Be in Second Normal Form (2NF)<br>2. No transitive dependencies

A table must be in 2NF and have no transitive dependencies to be in 3NF. This means every non-key attribute depends only on the primary key.

intermediate

Given a table with columns: StudentID (PK), StudentName, AdvisorID, AdvisorName. Why is this table not in 3NF?

Because AdvisorName depends on AdvisorID, which is not the primary key. This is a transitive dependency. To reach 3NF, Advisor info should be in a separate table.

beginner

How does 3NF improve database maintenance?

3NF reduces data duplication and inconsistencies by ensuring each fact is stored only once. This makes updates easier and prevents errors.

Which condition must be true for a table to be in Third Normal Form (3NF)?

A It has no transitive dependencies

B It has no partial dependencies

C It has duplicate rows

D It contains only one column

3NF requires that no non-key column depends on another non-key column, meaning no transitive dependencies.

What is a transitive dependency?

A A non-key column depends on another non-key column

B A primary key depends on a foreign key

C A column depends on itself

D A table has duplicate rows

A transitive dependency means a non-key column depends on another non-key column, which violates 3NF.

If a table is in 2NF but has transitive dependencies, what is its normalization status?

A It is in Boyce-Codd Normal Form

B It is in 3NF

C It is in 1NF

D It is not in 3NF

3NF requires no transitive dependencies, so the table is not in 3NF if they exist.

Which action helps achieve 3NF?

A Combining unrelated data into one table

B Adding redundant columns

C Splitting tables to remove transitive dependencies

D Ignoring primary keys

Splitting tables to separate dependent data removes transitive dependencies and achieves 3NF.

Why is 3NF important in databases?

A To increase the number of tables

B To reduce data duplication and improve data integrity

C To make queries slower

D To store data in one big table

3NF reduces duplication and keeps data consistent, making databases easier to maintain.

Explain Third Normal Form (3NF) and why it is important in database design.

Think about how 3NF helps keep data clean and easy to update.

Describe a real-life example where a table violates 3NF and how you would fix it.

Consider a table with people and their managers' names stored together.