Converting to Second Normal Form (2NF)
We have solved the problem of the inability to store the document in database tables but have introduced a new problem. Consider the table we produced on the previous page. Click on the following link to see it again:
Note that the student's details (Student No, Name, Date of Birth, Gender and Last Attendance Date) are stored in this table. But if the student enrolled on another course then the same information would be stored again. We only need to store a student's details once in our database. This situation occurs because some of the non-key attributes in this table are referenced by part of the table's key - not the whole key. This problem can be dealt with by applying the following rules:
- Examine tables with a composite key (a key made up of two parts)
- For each non-key attribute, determine if its key is the first part, or the second part, or if neither then the answer is both parts
- Remove the partial key and its dependents to form a new table
In our example you will note that Student Name, Date of Birth and Gender are referenced by Student No while Last Attendance Date is referenced by Course Code and Student Number (because it is the date for that student on that particular course). The document below shows this stage of Normalisation:
We now have three tables in our database. Note that the first table is already in Second Normal Form because is only has a single-part key.