6. Second Normal Form 2NF

Most tables tend to have a single-attribute (i.e. simple) primary key. Like this

CUSTOMER

But sometimes a table has a primary key made up of more than one attribute i.e. it has a compound primary key.

CONCERT

The table above is using both the venue and artist as the compound primary key.

It is in this situation that the extra rule for second normal form comes in handy. The rule states

• Non-key attributes must depend on every part of the primary key
• The table must already be in first normal form

So inherently, any table that is already in 1NF and has a simple primary key is automatically in second normal form as well.

Consider the Concert example above - this is NOT in second normal form. Notice the attribute called Style. This is describing the style of artist - it has nothing to do with where the concert took place! And so its value does not depend on EVERY part of the primary key, so the rule for second normal form is not being met.

The reason for this rule is to ensure there is no redundant data being stored.

For example, let's add another Girls Aloud concert to the table

Notice that the 'girl band' value is being repeated and so is causing the database to be bigger than it needs to be.