Bird
0
0

A table has columns (StudentID, CourseID, InstructorName, Grade) with composite key (StudentID, CourseID). InstructorName depends only on CourseID. How to redesign to satisfy 2NF?

hard📝 Application Q9 of 15
SQL - Database Design and Normalization
A table has columns (StudentID, CourseID, InstructorName, Grade) with composite key (StudentID, CourseID). InstructorName depends only on CourseID. How to redesign to satisfy 2NF?
ARemove InstructorName from the table.
BAdd InstructorName to the primary key.
CMake StudentID the only primary key.
DCreate two tables: Courses(CourseID, InstructorName) and Enrollment(StudentID, CourseID, Grade).
Step-by-Step Solution
Solution:

  1. Step 1: Identify partial dependency

    InstructorName depends only on CourseID, part of composite key.

  2. Step 2: Normalize by splitting

    Split into Courses and Enrollment tables to remove partial dependency.

  3. Final Answer:

    Create two tables: Courses(CourseID, InstructorName) and Enrollment(StudentID, CourseID, Grade). -> Option D

  4. Quick Check:

    Splitting tables removes partial dependency [OK]
Quick Trick: Separate dependent data into new tables [OK]
Common Mistakes:
  • Adding non-key columns to primary key
  • Removing important columns
  • Changing primary key incorrectly

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More SQL Quizzes
Advanced Query Patterns - Why query patterns matter - Quiz 14medium Advanced Query Patterns - Top-N per group query - Quiz 15hard Advanced Window Functions - Running totals with SUM OVER - Quiz 6medium CASE Expressions - CASE with aggregate functions - Quiz 12easy Common Table Expressions (CTEs) - WITH clause syntax - Quiz 14medium Common Table Expressions (CTEs) - CTE vs subquery vs view decision - Quiz 8hard Indexes and Query Performance - How an index works (B-tree mental model) - Quiz 15hard SQL Security Basics - Parameter binding mental model - Quiz 15hard SQL Security Basics - How string concatenation creates vulnerabilities - Quiz 9hard Window Functions Fundamentals - OVER clause with ORDER BY - Quiz 15hard