for Microsoft Access
Create complex MS Access databases without being an expert in relational database design! Designer for Microsoft Access asks you plain-language questions about what you want to manage with your database, and creates the tables and relationships automatically. Free trial available
Now we've looked at normalising a database to 1NF (First Normal Form), we will continue to investigate normalising to Second Normal Form.
A table is in first normal form and each non-key field is functionally dependent on the entire primary key.
Look for values that occur multiple times in a non-key field. This tells you that you have too many fields in a single table.
A Practical Approach
In the example below, see all the repeating values in the name and ProjectTitle fields. This is an inefficient way to store and maintain data. In a well-designed database, the only data that is duplicated is in key fields used to connect tables. The presumption is that the data in key fields will rarely change (so it's OK if it's repeated) while the data in non-key fields may change frequently (so it's not OK to repeat it).
If a ProjectTitle changed, you would have to edit it in several records. And what would happen in this table if the EmployeeID was part of the primary key and you wanted to add a new ProjectNum and ProjectTitle even though no employees had yet been assigned?
The primary key cannot contain a null value so you couldn't add the new project. Additionally, if a project ended and you wanted to delete it, you would have to delete the individual values because, if you deleted the records containing the titles and an employee was assigned to only that project, you would also delete that employee's record - something that you may not want to do.
In the above example, the asterisks indicate the fields that make up the primary key of this table as it now stands. A multi-field primary key is necessary because neither the EmployeeID nor the ProjectNum fields contain unique values.
The reason there are repeated values in LastName, FirstName, and ProjectTitle is that these fields are dependent on only part of the primary key. The value in EmployeeID determines what the value in LastName will be but the value in ProjectNum has nothing to do with it. Similarly, the value in ProjectNum determines the value in ProjectTitle but EmployeeID does not. These non-key fields relate to only part of the primary key. They are not functionally dependent on the entire primary key.
The solution to this lies in breaking the table into smaller tables that do meet second normal form. You will find that more tables is the solution to most problems encountered during data normalisation.
Now we'll take the table above and design new tables that will eliminate the repeated data in the non-key fields.
|*EmployeeID||Last Name||First Name|
Examine the tables to make sure there are no repeating values in non-key fields and that the value in each non-key field is determined by the value(s) in the key field(s). This removes the modification anomaly of having the repeated values.
That's all for the second article on normal forms. Check the following article to view details of normalising your database to 3NF (3rd normal form)