Path: utzoo!attcan!uunet!cs.utexas.edu!usc!ucsd!helios.ee.lbl.gov!nosc!cod!dberg
From: dberg@cod.NOSC.MIL (David I. Berg)
Newsgroups: comp.databases
Subject: Re: Tricky database definition problem
Summary: Normalize into Observation and Observation_Data
Message-ID: <1752@cod.NOSC.MIL>
Date: 13 Jan 90 21:58:02 GMT
References: <49@arkham.enet.dec.com> <CIMSHOP!DAVIDM.90Jan11152210@uunet.UU.NET>
Organization: Naval Ocean Systems Center, San Diego
Lines: 104

In article <CIMSHOP!DAVIDM.90Jan11152210@uunet.UU.NET>, cimshop!davidm@uunet.UU.NET (David S. Masterson) writes:
> I have a database consisting of observations.  Each observation consists of a
> <DataType, DataValue> pair (the observation might be water height or machine
> temperature or any number of other things).  Associated with each pair is a
> list of <LocationType, LocationValue> pairs that help identify where the
> observation occurred.  This can be defined simply (and rather redundantly) in
> one table like (with some example data):
> 
>     Observation
>     DataType    DataValue    LocType        LocValue
>     WaterHeight    10        Shore        South
>         "           "        Time        12:30
>         "           "        Observer    Bill
>     WaterHeight    15        Time        12:30
>         "           "        Observer    Fred
>     WaterTemp      60        Time        1:30
>         "           "        Observer    Tom
>     WaterTemp      50        Time        2:30
>         "           "        Observer    Gerry
> 
> With a table like this (or more normalized), can a query be written like to
> find <DataType, DataValue> that are constrained by some <LocType, LocValue>
> pairs.  
--
To take the Information Modelling appoach, what you have is an object type
"Observation".  Each observation can have a number of attributes, each
with its associated value.  From the schema you presented in your posting,
I conclude that you wish to keep your data base as generic as possible,
i.e. allow for a variable number of attributes for each observation, including
those which are yet to be defined.  If this isn't so, then your
schema should look like:

    Observation(Time, WaterTemp, WaterHeight, Shore, Obersver),

end of discussion..

If this is your goal, however, (and it is not an unreasonable one) then
you've started down the right track.  When you normalize the schema 
you have presented, you will be left with those attributes which are
ALWAYS a transitive function of Observation, and those atrtributes which
are a function of the particular data items recorded during the observation.
For each observation, you need an identifier.  Date/Time of the observation
would probably be your identifier. The only other attribute which is 
a transitive function of Observation is Observer (i.e. there will always
be one and only one observer for each observation). Now you create
another object type called Observation_Data. The attributes of this 
object type are <Data_Type, Data_Value>. There is a one to many
relationship between the object types Observation and Observation_Data.
To establish the logical access path between them, you must attribute
Observation_Data with the identifier of Observation.  I recommend that 
you create an Obervation Number for each observation, particularly to answer 
the queries you have posed.  The implementation of the information model, 
then, results in the following data base
schema:
    Observation(Date/Time, Observer, Observation_No)
    Observation_Data(Observation_No, Data_Type, Data_Value).


Your data base now looks like:

    Observation(12:30,Bill,1)
    Observation(12:30,Fred,2)
    Observation(1:30,Tom,3)
    Observation(2:30,Gerry,4)

    Observation_Data(1,WaterHeight,10)
    Observation_Data(1,Shore,South)
    Observation_Data(2,WaterHeight,15)
    Observation_Data(3,WaterTemp,60)
    Observation_Data(4,WaterTemp,50)

Now, to answer the following questions:

1.  Find the WaterHeight when Time was 12:30 and Observer was Bill.
2.  Find the WaterHeight for Observer Fred and Shore is South.
3.  Find the WaterTemp for Observer (Tom or Fred) and Time was 1:30.

you would:

1. SELECT Data_Value FROM Observation_Data, Observation
    WHERE Data_Type = "WaterHeight" 
      AND Date/Time = "12:30" 
      AND Observer = "Bill"
      AND Observation_Data.Observation_No = Observation.Observation_No

2. SELECT Data_Value FROM Observation_Data
    WHERE Data_type = Water_Height
      AND Observation_Data.Observation_No = 
      (SELECT Observation.Observation_No from Observation, Observation_Data
      WHERE Observer = "Fred" 
        AND Data_Type = "Shore"
        AND Data_Value = "South",
        AND Observation_Data.Observation_No = Observation.Observation_No)

3. SELECT Data_Value FROM Observation_Data, Observation
    WHERE Data_Type = "WaterTemp" 
      AND Date/Time = "1:30" 
      AND Observer IN ("Tom","Fred")
      AND Observation_Data.Observation_No = Observation.Observation_No
-- 
David I. Berg (dberg@nosc.mil)                    GENISYS Information Systems
MILNET: dberg@nosc.mil                            4250 Pacific Hwy #118 
UUCP:   {akgua decvax dcdwest ucbvax}!            San Diego, CA 92110
         sdcsvax!noscvax!dberg                    (619) 226-1122