Second-Normal Form (2NF)
Now our data is grouped into sets of related data we still need to check we are not keeping more data than we need to in our relation. We know we don’t have any repeating groups as we removed these with First Normal Form. But if we look at our example we can see for every UnitCode we are also storing the UnitName.
Would it not seem more sensible to have a different relation we could use to look up UG45783 and find the unit name ‘Advanced Database’? This way we wouldn’t have to store lots of additional duplicate information in our Student/Unit relation.
This is exactly what we aim to achieve with Second Normal Form and its purpose is to remove partial dependancies.
We can consider a relation to be in Second Normal Form when: The relation is in First Normal Form and all partial key dependencies are removed so that all non key domains are functionally dependant on all of the domains that make up the primary key.
Before we start with the steps, if we have a table with only a single simple key this can’t have any partial dependencies as there is only one domain that is a key therfroe these relations can be moved directly to 2nd normal form.
For the rest the steps from 2NF to 3NF are:
- Take each non-key domain in turn and check if it is only dependant on part of the key?
- If yes
- Remove the non-key domain along with a copy of the part of the key it is dependent upon to a new relation.
- Underline the copied key as the primary key of the new relation.
- Move down the relation to each of the domains repeating steps 1 and 2 till you have covered the whole relation.
- Once completed with all partial dependencies removed, the table is in 2nd normal form.
In our example above, UnitName is only dependant on unitCode and has no dependency on studentId. Applying the steps above we move the unitName to a new relation with a copy of the part of the key it is dependent upon. Our table in second normal form would subsequently look like this: