Database Design and Normalisation

First Normal Form (1NF)

1. Remove all repeating columns (Fields)

e.g. Subject1, Subject2, Subject3

2. Remove data with multiple values

e.g. Name and Surname in same field (Robyn Smith)

3. Ensure that each table has a unique primary key field.

Try to avoid using a composite primary key – that is two fields that jointly function as primary key.

 Study the following table:

Table: School_Sports :

 

 

 

Problems: (1) There are two John Smiths. They are actually two different learners – 
                        and we can’t tell them apart!
                  (2) There are repeating columns (fields) namely Sport1,Sport2 etc.

Solution:  (1)Create a new table just for the learners names and surnames add a key

                     field called ID that allows us to distinguish between people with the same
                    name and surname.
               (2)Create a second table for all the sporting activities done by these people
                    and link the two tables using the values in the ID field of the Names table.
                   Also add a key field (ID2) to the Activities table so that each record has a
                  unique  value.

 

 

Second Normal Form (2NF)

1. Make sure the database meets the requirements of (1NF)

2. Make sure there are no partial dependencies every field must depend
    on a whole primary field (not a composite one).
3. Also check for repeating values that do not have to be repeated (if the
    tables were designed correctly - redundancy).

 Problems: (1) Although the above split has resulted every field depending a whole primary key field, there is the  problem of repeating values in a field with the potential for errors.

The above arrangement therefore does not meet the criteria of the second normal form.

Solution: Split the tables in the way shown:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Third Normal Form (3NF)

1. Make sure the database meets the requirements of (2NF)
2. Make sure that no value depends on field that is NOT part of primary  key.