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.
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
. 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...
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.
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:
- Five Normal Forms
- Tutorial on the First Three Normal Forms
- Sixth Normal Form (for temporal databases)