Third-Normal Form (3NF)
Third Normal Form deals with something called ‘transitive’ dependencies. This means if we have a primary key A and a non-key domain B and C where C is more dependent on B than A and B is directly dependent on A, then C can be considered transitively dependant on A.
Another way to look at it is a bit like a stepping stone across a river. If we consider the primary key A to be the far bank of the river and our non-key domain C to be our current location, in order to get to A, our primary key, we need to step on a stepping stone B, another non-key domain, to help us get there. Of course we could jump directly from C to A, but it is easier, and we are less likely to fall in, if we use our stepping stone B. Therefore current location C is transitively dependent on A through our stepping stone B.
Before we start with the steps, if we have any relations with zero or only one non-key domain we can’t have a transitive dependency so these move straight to 3rd Normal Form
For the rest the steps from 2NF to 3NF are:
- Take each non-key domain in turn and check it is more dependent on another non-key domain than the primary key.
- If yes
- Move the dependent domain, together with a copy of the non-key attribute upon which it is dependent, to a new relation.
- Make the non-key domain, upon which it is dependent, the key in the new relation.
- Underline the key in this new relation as the primary key.
- Leave the non-key domain, upon which it was dependent, in the original relation and mark it a foreign key (*).
- 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 transitive dependencies removed, the table is in 3rd normal form.
In our example above, we have unitCode as our primary key, we also have a courseName that is dependent on courseCode and courseCode, dependent on unitCode. Though couseName could be dependent on unitCode it more dependent on courseCode, therefore it is transitively dependent on unitCode.
So following the steps, remove courseName with a copy of course code to another relation and make courseCode the primary key of the new relation. In the original table mark courseCode as our foreign key.