Solution:Step 1: Identify the 3NF violationAdvisorOffice depends on Advisor, a non-key column, causing a transitive dependency violating 3NF.Step 2: Fix by separating dependent dataCreating a separate Advisor table with Advisor and AdvisorOffice removes the transitive dependency.Final Answer:Create

[Solved] You have a table:StudentID | StudentName | Advisor | AdvisorOfficeThis table violates 3NF. What is the best way to fix it? | SQL

SQL - Database Design and Normalization

You have a table:

StudentID | StudentName | Advisor | AdvisorOffice

This table violates 3NF. What is the best way to fix it?

ARemove AdvisorOffice column.

BCreate a separate Advisor table with Advisor and AdvisorOffice.

CMake StudentName the primary key.

DAdd AdvisorOffice to StudentID as a foreign key.

Step-by-Step Solution

Solution:

Step 1: Identify the 3NF violation
AdvisorOffice depends on Advisor, a non-key column, causing a transitive dependency violating 3NF.
Step 2: Fix by separating dependent data
Creating a separate Advisor table with Advisor and AdvisorOffice removes the transitive dependency.
Final Answer:
Create a separate Advisor table with Advisor and AdvisorOffice. -> Option B
Quick Check:
Separate dependent data into own table to fix 3NF [OK]

Quick Trick: Fix 3NF by splitting tables for transitive dependencies [OK]

Common Mistakes:

Just removing columns without restructuring
Changing primary keys incorrectly
Adding foreign keys without normalization

Master "Database Design and Normalization" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

You have a table:

Step 1: Identify the 3NF violation

Step 2: Fix by separating dependent data

Final Answer:

Quick Check:

Want More Practice?