VisaSpark
Sign in
intermediate21 min

Normalization: First and Second Normal Form

Functional dependencies as the underlying idea behind normalization, and the first two normal forms — eliminating repeating groups, then eliminating partial dependency on a composite key.

What you'll learn

  • Identify a functional dependency between columns in a table
  • Determine whether a table satisfies First Normal Form (1NF)
  • Determine whether a table satisfies Second Normal Form (2NF), given a composite primary key

Prerequisites

Explanation

A functional dependency exists between two columns when one column's value fully determines another's — written A -> B ("A determines B"). In a Learner table, learner_id -> email holds: knowing the ID tells you exactly one email, since each learner has exactly one. Functional dependencies are the underlying idea every normal form is really testing for; each normal form is a progressively stricter rule about which dependencies are allowed to exist in a single table.

First Normal Form (1NF) requires every column to hold a single, atomic value — no repeating groups, and no comma-separated lists or arrays stuffed into one column pretending to be multiple values. A table with a column literally named phone_numbers holding "555-1234, 555-5678" violates 1NF: that column isn't atomic, and the database has no way to query, index, or enforce constraints on the individual phone numbers hidden inside that string. The fix is a separate table (PhoneNumber, with a foreign key back to Learner) — exactly the one-to-many pattern from the modeling lesson, applied specifically to fix a 1NF violation.

Second Normal Form (2NF) applies only to tables with a composite primary key (a primary key made of more than one column) and requires every non-key column to depend on the entire composite key, not just part of it — this is called eliminating partial dependency. Consider an Enrollment table with the composite key (learner_id, course_id), plus a course_title column: course_title depends only on course_id (part of the key), not on the combination of both — a 2NF violation, because it means course_title is redundantly repeated on every single enrollment row for that course, and an update to the course's title now has to correctly find and change every one of those repeated copies or the data becomes inconsistent. The fix: move course_title to the Course table, where it depends on the entire (single-column) key there, and reference it via course_id from Enrollment instead of duplicating it.

Example

Detecting a 1NF violation (a non-atomic column) and a 2NF violation (partial dependency on a composite key) programmatically.

function violates1NF(row) {
  // A column value containing a comma is a strong signal of a hidden repeating group.
  return Object.values(row).some(v => typeof v === "string" && v.includes(","));
}

console.log(violates1NF({ id: 1, phone_numbers: "555-1234, 555-5678" })); // true -- not atomic
console.log(violates1NF({ id: 1, phone_number: "555-1234" }));            // false -- atomic

function violates2NF(compositeKeyColumns, nonKeyColumn, dependsOnColumns) {
  // A 2NF violation exists if the non-key column depends on a STRICT SUBSET of the composite key,
  // not the entire key.
  const isProperSubset =
    dependsOnColumns.every(c => compositeKeyColumns.includes(c)) &&
    dependsOnColumns.length < compositeKeyColumns.length;
  return isProperSubset;
}

// course_title depends only on course_id, which is part of, but not all of, (learner_id, course_id):
console.log(violates2NF(["learner_id", "course_id"], "course_title", ["course_id"])); // true -- violation

Try it yourself

Check whether a column depending on the FULL composite key (both learner_id and course_id) correctly reports NOT a violation.

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Guided exercise

Guided exercise

Write isAtomic(value) modeling the 1NF atomicity check: false if value is a string containing a comma OR is an array, true otherwise (numbers, booleans, and comma-free strings are atomic).

Checks: a single value string is atomic · a comma-separated string is not atomic · an array is not atomic · a number is atomic

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Independent exercise

Independent exercise

Write find2NFViolations(compositeKey, columnDependencies) where columnDependencies is an object mapping each non-key column name to the array of key columns it actually depends on. Return an array of column names that violate 2NF (depend on a PROPER SUBSET of compositeKey, not the whole thing).

Checks: correctly identifies multiple partial-dependency violations · does not flag a column that depends on the entire composite key · a single-column key has no possible 2NF violation

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Common mistakes

  • Storing a comma-separated or JSON-array-in-a-text-column list of values to 'avoid creating another table' -- this violates 1NF and makes it impossible to query, index, or constrain individual values without fragile string parsing.
  • Not noticing a 2NF violation because the redundant column 'seems fine' with only a few rows -- the real cost (inconsistent copies after an update, wasted storage) only becomes visible at scale, but the structural problem exists from the very first duplicated row.
  • Applying 2NF reasoning to a table with a single-column primary key -- 2NF specifically concerns composite keys; a single-column key has no 'partial' subset to depend on, so this check is meaningless there (though other normal forms, like 3NF in the next lesson, still apply).

Knowledge check

Knowledge check

1. Why does storing "555-1234, 555-5678" in a single phone_numbers column violate First Normal Form?
2. In an Enrollment table with composite key (learner_id, course_id), why does a course_title column violate 2NF?
3. What does a functional dependency A -> B mean?

Takeaway

1NF requires every column to hold one atomic value, eliminating hidden repeating groups; 2NF (relevant only for composite keys) requires every non-key column to depend on the entire key, eliminating redundant, partially-dependent data that update anomalies can silently make inconsistent.

Summary

A functional dependency (A -> B) means A's value determines B's value. 1NF requires atomic column values — no hidden lists. 2NF, for composite primary keys, requires every non-key column to depend on the full key, not a subset — a violation means data is redundantly duplicated and can become inconsistent after an update.

References

Your notes

Notes save automatically.

Finished this lesson?

Mark it complete to track your progress and schedule a future review.

Next: Third Normal Form and Denormalization Tradeoffs →