Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sdd.hp.com!hplabs!pyramid!infmx!dberg
From: dberg@informix.com (David I. Berg)
Newsgroups: comp.databases
Subject: Re: relational theory problem
Summary: Normalize into Fourth Normal Form (Long)
Keywords: 4NF
Message-ID: <1991Feb9.221027.6695@informix.com>
Date: 9 Feb 91 22:10:27 GMT
References: <1991Feb7.120950.23170@actrix.gen.nz>
Sender: news@informix.com (Usenet News)
Organization: Informix Software, Inc.
Lines: 95

> I am working with a table which has the following fields:
> ....
> An example record would be:
> ....
> On the surface, this would seem to normalise as:
> 
>    T1(health_indicator, numerator, denominator)
>    T2(numerator, numr_definition)
>    T3(denominator, denmr_definition)
> ....
> The numerators and denominators are all instances of (descriptions of) 
> DATA ITEMS - so something which is a numerator in one record may be a
> denominar in another.  For example, there may be a record like:
> ....
> (Notice the different roles played by children in the two example records.)
> ....
> Now I really want to normalise this table - the redundancy involved in
> having multiple copies of definitions has caused me *heaps* of problems.
> 
> Has anyone got any suggestion how?

Begin by building relations T1 and T2 like this:

T1(health_indicator, numr_code, numr_value, denr_code, denr_value)
T2(numr_denr_code, numr_denr_definition)

The key to T1 is health_indicator. T1 has foriegn keys numr_code and
   denr_code.  The key to T2 is numr_denr_code.

There is a 1 to 2 relationship from T1 to T2, and a 1 to many relationship
from T2 to T1. This is because T1 contains a multi-valued dependency; 
namely, the values for numr_code and denr_code come from the same
domain of values. However the application works.

Example records would be:

T1:
    health_indicator   Asthma deaths per thousand children
    numerator_code     Asthma deaths of children
    numerator_value    m
    denominator_code   Children
    denomiator_value   n

    health_indicator   Percent of population under 15 years
    numerator_code     Children
    numerator_value    m
    denominator_code   Population
    denomiator_value   n

T2:
    numr_denr_code   Asthma deaths of children
    numr_denr_defn   Deaths coded ICD9-CM 493.00 - 493.99, aged 0-14 yrs

    numr_denr_code   Children
    numr_denr_defn   Population aged 0-14 years

    numr_denr_code   Population
    numr_denr_defn   Total population

To eliminate the multi-valued dependency in T1, we must normalize T1 into
fourth normal form (4NF). This yeilds the relation:

T1(health_indicator, numr_denr_indr, numr_denr_code, numr_denr_value)

The key to T1 is (health_indicator, numr_demr_indr).
It has the foriegn key numr_demr_code.

Now, our example records in T1 are:

    health_indicator   Asthma deaths per thousand children
    numr_demr_indr     N
    numr_denr_code     Asthma deaths of children
    numr_demr_value    m

    health_indicator   Asthma deaths per thousand children
    numr_demr_indr     D
    numr_denr_code     Children
    numr_demr_value    n

    health_indicator   Percent of population under 15 years
    numr_demr_indr     N
    numr_denr_code     Children
    numr_demr_value    m

    health_indicator   Percent of population under 15 years
    numr_demr_indr     D
    numr_denr_code     Population
    numr_demr_value    n

The relations T1 and T2 are now fully normalized to 4NF.

  ___                   ___              dberg@cougar.informix.com
  /  ) __      . __/    /_ ) _       __  Informix Software Inc.  (303) 850-0210
_/__/ (_(_ (/ / (_(_  _/__> (-' -/~ (_-  5299 DTC Blvd #740; Englewood CO 80111
{uunet|pyramid}!infmx!dberg        The opinions expressed herein are mine alone.