Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 79 ยท Database Design Theory

1NF, 2NF and 3NF

Learn first, second and third normal form through functional dependencies: atomicity, partial and transitive dependencies, with a step-by-step live SQL decomposition.

A normal form is a named quality level for a table design. Each level forbids one specific kind of redundancy, and each level is defined using the functional dependencies and keys from the last two lessons. The three classic levels, first, second and third normal form, catch the large majority of real-world design mistakes, and many working schemas stop at 3NF.

This lesson is the theory view with a worked decomposition. If you want the practical, tips-and-habits view, read Normalization in the SQL tutorial first or afterwards; here we ask why each rule exists and how to test it with FDs.

Each level removes one problem

1NF Values are atomic. No lists in a cell.
2NF No attribute depends on part of a key.
3NF No attribute depends on a non-key attribute.

A famous summary: the key, the whole key, and nothing but the key.

1 / 2

First normal form: atomic values

A relation is in 1NF when every attribute holds a single, indivisible value per row, and there is a key so each row can be identified. Cells that hold lists ("555-0101, 555-0102"), repeating groups (phone1, phone2, phone3) or nested tables break the rule.

"Atomic" depends on how the data is used. A date is atomic if you never need its year on its own, and a full name is atomic if you never search by surname. The test is whether the database needs to look inside the value. A list of phone numbers fails the test because you want to ask "who owns number X?".

SQL Why a list in one cell hurts, and the 1NF fix

Press Run (or Ctrl + Enter) to see the result here.

The first query returns no rows, and the last one returns Ana. Fixing the list-in-a-cell with LIKE '%555-0102%' would also match 555-01020; only separate rows give exact, indexable lookups and let the database enforce uniqueness per number. This is the same move you will meet again for multivalued attributes in Mapping ER Diagrams to Tables.

A running example

For the next two normal forms we need a table that misbehaves. Here is one wide relation that mixes four facts:

TEXT
Enrollment(student_id, course_id, student_name, course_title,
           instructor, instructor_office, grade)

The business rules, written as FDs, are:

  • student_id -> student_name
  • course_id -> course_title, instructor
  • instructor -> instructor_office
  • student_id, course_id -> grade

Running the closure algorithm from Attribute Closure, Keys and Minimal Cover shows that {student_id, course_id} determines everything, and nothing smaller does. So the only candidate key is (student_id, course_id), and its attributes, student_id and course_id, are the prime attributes. Every other attribute is non-prime.

Second normal form: no partial dependencies

A relation is in 2NF when it is in 1NF and no non-prime attribute depends on only part of a candidate key. A dependency of a non-prime attribute on a proper subset of a key is a partial dependency. Notice the rule only matters when a key has more than one attribute: a table whose keys are all single columns is automatically in 2NF if it is in 1NF.

In Enrollment, three FDs break the rule: student_id -> student_name (part of the key determines a non-prime attribute), and course_id -> course_title, instructor (the same). The cure is to move each partially dependent group into a relation of its own, keyed by the part of the key that determines it:

TEXT
Student(student_id, student_name)
Course(course_id, course_title, instructor, instructor_office)
Enrollment(student_id, course_id, grade)

Only grade needs the whole key, so it stays in Enrollment. The student's name is now stored once per student, not once per course taken.

Third normal form: no transitive dependencies

A relation is in 3NF when it is in 2NF and no non-prime attribute depends on the key through another non-prime attribute. That kind of indirect path (key -> X -> Y) is a transitive dependency. The precise, widely used definition is this: for every non-trivial FD X -> A in the relation, at least one of these is true:

  • X is a superkey, or
  • A is a prime attribute.

Course still violates the rule: course_id -> instructor and instructor -> instructor_office. The determinant instructor is not a superkey of Course, and instructor_office is not prime. So the office of an instructor repeats on every course they teach, and the update anomalies from Redundancy and Update Anomalies return. Split again:

TEXT
Course(course_id, course_title, instructor)
Instructor(instructor, instructor_office)

The final design has four small relations, each of which states one kind of fact, and each of which is in 3NF.

The "or A is prime" clause is the difference between 3NF and the stricter BCNF, introduced in BCNF and Lossless Decomposition. It exists so that a lossless, dependency-preserving 3NF design is always possible.

Doing the decomposition in SQL

Decomposition is only safe if you can rebuild the original table by joining the pieces. This example creates the wide table, projects it into the four relations with SELECT DISTINCT, and then proves the join reproduces exactly the original rows by comparing in both directions with EXCEPT.

SQL Decompose to 3NF and prove nothing was lost

Press Run (or Ctrl + Enter) to see the result here.

Both difference counts come out as 0 and the original has 5 rows, so the join gives back exactly the original table: the decomposition is lossless. Column order in rebuilt matches wide, which is what makes the SELECT * comparison valid. Why this works, and when it does not, is the subject of the next lesson.

Now the payoff. In the wide table, changing Lee's office means updating three rows and hoping you do not miss one. In the decomposed design it is one row.

SQL One fact, one place: the update anomaly is gone

Press Run (or Ctrl + Enter) to see the result here.

Both of Lee's courses now report D-40 after a single-row update. It is also possible to add an instructor who teaches nothing yet, which the wide table could not store without inventing a fake enrollment.

A quick checklist

To test a relation, work through these steps:

  1. List the FDs and compute the candidate keys.
  2. 1NF: are all values atomic, and is there a key?
  3. 2NF: for every FD whose left side is a proper part of a key, is the right side prime? If not, split.
  4. 3NF: for every non-trivial FD, is the left side a superkey or the right side prime? If not, split.

Remember that normal forms are properties of a design given its FDs, and are only as good as those FDs. Normalizing more is not always better; Multivalued Dependencies, 4NF and Denormalization discusses when to stop.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which table is NOT in first normal form?

    A cell holding a list is not atomic. The other three descriptions do not break 1NF.
  2. 2. A table has key (order_id, product_id) and the FD product_id -> product_name. Which normal form is violated?

    product_name is non-prime and depends on only part of the key, which is a partial dependency and breaks 2NF.
  3. 3. In R(emp_id, dept_id, dept_name) with key emp_id and FDs emp_id -> dept_id and dept_id -> dept_name, what is the problem?

    dept_name depends on the key through the non-key attribute dept_id. dept_id is not a superkey and dept_name is not prime, so 3NF fails.
  4. 4. A relation has the single-column candidate key id and is in 1NF. Which statement is true?

    A partial dependency needs a key with at least two attributes. With a one-column key it cannot occur, so 2NF holds. 3NF can still fail through transitive dependencies.

Summary

  • 1NF: atomic values and a key; lists and repeating groups move into their own rows or tables.
  • 2NF: no non-prime attribute depends on part of a candidate key; split off the partially dependent attributes with the part of the key that determines them.
  • 3NF: for every non-trivial FD X -> A, X is a superkey or A is prime; this removes transitive dependencies.
  • Prime attributes are those that belong to at least one candidate key.
  • A decomposition must be lossless: the join of the pieces must rebuild the original, which you can check in SQL with EXCEPT in both directions.
  • The payoff is one fact stored in one place, so updates, inserts and deletes stop causing anomalies.

Next: BCNF and Lossless Decomposition.