3NF or Third Normal Form

For a proposed data table to meet 3NF requirements it must already be 2NF. The further requirements for 3NF are very similar to 2NF however we must now compare non-key fields with each other (remember in 2NF we were comparing non-key fields with parts of a combined primary key).

So, in our previous example we ended up with 2 tables.

Table 1: Username,Computer with a Primary Key of (Username,Computer)
Table 2: Username,Fullname with a Primary Key of (Username)

As it is our tables would both meet 3NF requirements however to demonstrate the point lets add some more fields to Table 2. Say, Address, Postcode and Email Address.

Table 2: Username,Fullname,Address,Postcode, Email Address with a Primary Key of (Username)

So,

If we know the Fullname can we find out Address,Postcode, or Email Address ? NO because the person may have multiple addresses
If we know the Address can we find out Fullname,Postcode, or Email Address ? NO because more than one person may live at the address
If we know the Postcode can we find out Fullname,Address, or Email Address ? NO because houses share the same postcode
If we know the Email Address can we find out Fullname, Address, or Postcode ? NO because email addresses are typically assigned to usernames, not full names

We could argue about email address, or indeed the fullname against the address (land registry or council records possibly) and that is part of the fun of data normalisation.

However, in this example it means the table meets Third Normal Form.

Related

• Database Normalisation (Making Sense of Data) – 2NF Second Normal Form
• Database Normalisation (Making Sense of Data) – 1NF First Normal Form
• Making and storing .ISO backups