Third Normal Form

Third Normal Form (3NF) is a property of database tables.

A relation is in third normal form if it is in SecondNormalForm and there are no functional (transitive) dependencies between two (or more) non-primary key attributes.

-Siddharth Mahajan

The following table is in 3NF:

  Plate#  | Make   | Model | Color
 ---------|--------|-------|-------
  DGY1344 | Ford   | Focus | White 
  CWZ9193 | Ford   | Focus | Blue  
  BGY3199 | Toyota | Camry | White 
  CNG2239 | Toyota | Camry | Red   

This table has no multivalued records. It is in ThirdNormalForm, as it has nothing in it that is independent of the primary key (Plate#) and it has no columns whose values are dependent on a non-key field.

  Plate#  | VIN        | Make   | Model | Color
 ---------|------------|--------|-------|-------
  DGY1344 | 1FMKP33... | Ford   | Focus | White 
  CWZ9193 | 1FMKP38... | Ford   | Focus | Blue  
  BGY3199 | JT...      | Toyota | Camry | White 
  CNG2239 | JT...      | Toyota | Camry | Red   

This table is also in ThirdNormalForm. Both Plate# and VIN are candidate keys and qualify as 'prime' attributes, and (at any given point in time) possess an 'at most one VIN' <-> 'at most one plate' relationship (in real life, there are plates without cars and cars without plates). Given this is a non-temporal database, either Plate# or VIN can be used to determine the other. However, there are no transitive dependencies; i.e. Make, Model, and Color are directly dependent on VIN and directly dependent on Plate#.


I don't think that's true. Model is most certainly dependent on Make in this case. However, Model itself isn't sufficient to identify the data (for example, in the U.S., the "Neon" model was produced under both the "Plymouth" and "Dodge" makes). To normalize this table, you really want a "Model ID", which is a foreign key into a table that associates makes and models with model IDs. The same holds for color too. You might need a list of colors (palette) to select from...

The OnceAndOnlyOnce of a relational database?

Use unique identifiers (keys) to find data. Do not duplicate data across tables, instead, refer a dataset's key from within the calling table. Duplicate data tends to drift apart and the database's integrity is lost.

Not surprisingly, OnceAndOnlyOnce applies to schema design also.

Ack. EditHint. -- TerryLorber

Except that (relational) databases have constraints (on columns, tables, or the whole DB) which can be used to control that redundancy declaratively, so that integrity is not lost. Is there an equivalent outside relational systems? (apart from "wrap the whole model/db up as an encapsulated object and implement the constraints procedurally in the access methods" which isn't scalable to large schemas with constraints involving multiple tables)


For more on Database Normalization:
CategoryDataStructure CategoryDatabase

EditText of this page (last edited January 6, 2009) or FindPage with title or text search