Learning outcomes for this page:
       1. Be able to describe the generic method used to remove non-atomic values from tables (i.e. converting
           tables to first normal form)
       2. Be aware of the advantages of converting tables to first normal form

A much more sensible solution than the one given above to the three examples is to move the multi-valued field(s) to another table.

A generic solution

Generally the solution is to carry out the following steps:

1. Create a new table, in addition to the one you already have, containing:

2. A compound key consisting of:

   1. The primary key field of the original table, in this instance 'Doc id'.

   2. Another field with unique values, either from one of the multivalued  fields or a new field.  For example considering example 1 above we will create a new field called 'visit id'.  We can either make this value unique across all the multivalued fields across doctors or for each doctor.  If we assume this 'Visit id' is the number for a particular doctor is makes sense to begin the value at '1' for each doctor. [In practice, what I call a pragmatic solution, is to allow this field to become the single primary key value for the new table avoiding a composite key and allowing the use of a auto increment field in Access]

3.             Any of the other multivalued fields which vary in the same manner.

4.             Give the new table a name based on a combination of the original table and the multi-valued field(s).

This process may need repeating if you have multi-valued fields which are within or independent of other multi-valued fields.

An example

Carrying out this process for the first example concerning a single repeating group consisting of a single attibute (field) produces the following two tables:

Looking at the above tables it now seems obvious that that is the way the data should have been structured from the beginning and probably if the person designing the database had used some type of object modelling they would have produced the two above tables as part of the modelling process.  Notice that I have in this instance given the new tables names that appear to embody the objects they represent rather than follow the stages listed above for naming the new tables. Basically we have created a one to many relationship.

It is interesting to note that we also end up with a situation were we can have a list of doctors without any patients and no null values in the fields.

What have we achieved by splitting up the original table?  Most importantly we can now query the tables and obtain sensible results.  We can easily find the number of visits made to each doctor and also we do not need to worry about defining the maximum number of visits for each patient.

---

Portfolio exercise:  First normal form   sect7|07|01
Time:  10 minutes

For: Clinicians | NHS managers | Non healthcare workers

Look back at the example of a table containing a single repeating group with multiple attributes and convert it to first normal form. Save the result in your Portfolio.

 

The answer can be found here

Written by Robin Beaumont & Chris Noden e-mail:robin@ieg-net.co.uk or chrisnoden@hotmail.com

Home > Section 7 > Subsection 7 (Normalisation - part 1)

---