Normalization Workshop: Decompose to 3NF

The procedure, then the shortcut

This is the highest-yield skill in the course (guaranteed Part B Q1). The full procedure:

1. Find the primary key (often composite after reaching 1NF).
2. List every functional dependency.
3. Remove partial dependencies (→ 2NF): for each determinant that is *part* of the key, pull it and its dependent attributes into a new relation keyed by that part.
4. Remove transitive dependencies (→ 3NF): for each nonkey determinant, pull it and its dependents into a new relation; leave the determinant behind as a foreign key.
5. Write the final relations with their keys and foreign keys.

The shortcut (Table-4-14 trick): *identify every determinant; each determinant becomes the primary key of its own relation, taking the attributes it determines as nonkey columns.* If no two relations share dependent attributes, you land in 3NF directly — no need to march through 1NF and 2NF mechanically.

Worked example: GRADE_REPORT → 3NF

Start with the flat exam relation:

GRADE_REPORT(StudentID, StudentName, CampusAddress, Major,
             CourseID, CourseTitle, InstructorName, InstructorLocation, Grade)
PK = (StudentID, CourseID)

Read the functional dependencies:

StudentID                 -> StudentName, CampusAddress, Major     (partial: part of key)
CourseID                  -> CourseTitle, InstructorName           (partial: part of key)
InstructorName            -> InstructorLocation                    (transitive: nonkey -> nonkey)
(StudentID, CourseID)     -> Grade                                 (full: the whole key)

Apply the shortcut — one relation per determinant:

STUDENT(StudentID, StudentName, CampusAddress, Major)
COURSE(CourseID, CourseTitle, InstructorName)        -- InstructorName FK -> INSTRUCTOR
INSTRUCTOR(InstructorName, InstructorLocation)
REGISTRATION(StudentID, CourseID, Grade)             -- StudentID FK -> STUDENT, CourseID FK -> COURSE

Every determinant became a primary key, the transitive InstructorName → InstructorLocation got its own INSTRUCTOR relation, and Grade (the only attribute fully dependent on the whole key) stayed in REGISTRATION. All four relations are now in 3NF.

A second, smaller drill

STUDENT(StudentID, Major, AdvisorID, AdvisorName, AdvisorPhone) is already in 2NF (single-attribute key) but has the transitive StudentID → AdvisorID → AdvisorName/AdvisorPhone. Decompose:

STUDENT(StudentID, Major, AdvisorID)        -- AdvisorID FK -> ADVISOR
ADVISOR(AdvisorID, AdvisorName, AdvisorPhone)