Third Normal Form
Third Normal FormThird 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 | RedThis 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 | RedThis 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:
EditText of this page
(last edited January 6, 2009)
or FindPage with title or text search