Learning outcomes for this page:
    1. Be able to describe what a Determinant is
    2. Be able to describe what a Dependent is
    3. Be able to draw and interpret dependency diagrams
    4. Be able to provide a graphical and narrative definition of the aims of normalisation

We will now consider a more familiar example from the practical sessions you have worked through (See: http://www.robinbt2.free-online.co.uk/virtualclassroom/contents.htm for details of the practical sessions). In the first session you created a “Doctor” table in a database called cons4. To help you remember the table it is reproduced below:

Doc id	doc first name	doc Surname	Gender	Date reg	Addrs title	Addrs st name	Addres st no	city	postcodeA	postcodeB	Phone No
1	john	SMITH	2	01/04/67	The old surgery	12 station rd	Walkergate	Newcastle	Ne23	4rr	0191 009282
2	steve	Jarvis	2	01/05/70	Morpeth HC	High st	Morpeth		Mo2	1ts	0127 485739
3	Mary	Goodall	1	27/11/67	Seeham HC	The harbour	Seaham		Se1	4jp	0191 345867
4	Fiona	black	1	30/01/76	Cragside HC	The East Wing	Cragside castle		Cr3	1ws	0146 389431
5	Anna	Scriabin	1	25/03/61	Uniiverisity MC	Newcastle Univeristy		Newcastle	Ne1	1aa	0191 445776
23	mark	Goodall	2	27/02/55	Seaham HC	The harbour	Seaham		Se1	4jp	0191 345867

We can say that:

'Doc first name' is dependent upon 'Doc id' (Why? because there is a many to one relationship, several values of Doc first name could go to a single Doc id value which uniquely identifes the Doc first name)

'Doc first surname' is dependent upon 'Doc id'

We say that 'Doc id' functionally determines 'Doc first name '.  ‘Doc id’ is an example of a determinant.

I find it easier to talk about something that “functionally determines” something rather than is “dependent upon” it for some unknown reason.  Therefore I prefer to say:

'Doc id' determines 'Doc first name'

'Doc id' determines 'Doc first surname'

It is important to realise that there is no absolute logic in the above argument it is based upon knowledge of the data and how it is used in a particular context.

The 'essence' of the table is embodied in the determinant.  The value of the determinant determines what value the dependent field will take. Related to this aspect it has been said that ‘Each attribute must represent a fact about the key, the whole key, and nothing but the key’ (Date 1995 p316 quoting Kent 1983)

It is important to note that these 'intra-table' dependencies usually only hold in one direction.  A 'Doc id' value of 3 in the Doctor table determines the doc surname 'Goodall' but Goodall does not determine a single Doc id value (rather it is related to doc id values 3 and 23). However there is nothing in stopping the dependency from being in both directions (Date 1999 quoting Codd). However if this is the case one could argue that the two fields are equivalent and therefore one is redundant.

The above paragraph is difficult. To help you try to understand the above look back again at the shipment table example. You may also like to draw a similar diagram for the DOCTOR table to that I did above for the SHIPMENT table.

These are simple arrow drawings the tail being the determinant and the head being the dependent.

Clearly deciding which field is dependent upon which requires an in-depth knowledge of the data. This is why it is vitally important to have people involved in the database design process that actually work on a day to day basis with the data in the clinical situation.

Defining dependencies is part of the process of understanding what the data means, in other words the semantics of the data (Date 1995 p295). In all probability the meaning will be unique to a particular context.

From the above example it would appear moderately easy to define the dependencies between the fields,  however this was principally because we chose an example which is fully normalised (i.e. gone through all the stages of normalisation).  Indeed the basis of normalisation is to obtain a dependency diagram that mimics the one below.

Stating the above in words:

---

Portfolio exercise:  Functional dependency   sect07|07|05
Time:  5 minutes

For: Clinicians | NHS managers | Non healthcare workers

List what you think are the other dependencies in the doctor table in the cons4 database (the table is shown above).

 

 

 

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)

---