Cornell Virtual Workshop > Relational Databases > Designing/creating Relational Databases

Second Normal Form (2NF)

2NF imposes two constraints on the database

Database is in 1NF
Any non-PK columns are dependent on the whole primary key

The first condition illustrates that normalization is hierarchical; being normalized to a later level requires normalization at all of the lower levels, true of the current state of our database, now with two tables. The second condition means that in cases with a compound primary key, any columns which are not part of the primary key must depend on the entire primary key, not just part of it. It is this condition which our database fails to achieve; the weatherstation_location column in Measurements clearly only depends on the identity of the weatherstation itself, as given by weatherstation_name and which is only part of the primary key. This will drive us to create a Weatherstations table.

Note that as the Instruments table has a single-column primary key, it will be 2NF if it's 1NF because the other fields all depend on the entirety of the primary key by construction. So, our new database layout looks like this:

An Instruments table, a Measurements
table and a Weatherstations table; Instruments and Measurements are joined by a foreign key, as are Weatherstations and Measurements. — We have broken down our two 1NF tables into three tables, our previous Instruments table with instrument_id as the primary key, a Measurements table with a compound primary key made up of weatherstation_name, datetime, and instrument_id (where instrument_id is a foreign key to the instruments table), but now we also have a Weatherstations table with weatherstation_name as primary key and also a foreign key to Measurements.

Our course of normalization will end at 3NF; we have only one more step to complete!

Back