Path: utzoo!utgpu!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!unmvax!ncar!ames!pasteur!ucbvax!hplabs!hpda!hpcuhb!hpsmtc1!donovan
From: donovan@hpsmtc1.HP.COM (Donovan Hsieh)
Newsgroups: comp.databases
Subject: Re: One to Many to One to One relationships in Informix SQL  (LONG!!)
Message-ID: <11410003@hpsmtc1.HP.COM>
Date: 12 Dec 88 18:55:38 GMT
References: <9176@ihlpb.ATT.COM>
Organization: Hewlett Packard, Cupertino
Lines: 26

It appears that your DB schema is not NORMALIZED into proper normal form (e.g.
3 NF). For example in Table A, I can formulate the following set of Functional
Dependencies (FD) based on your description on the relationship of each 
attribute for each FD :

   FD 1 : leadsource location type --> leadnumber /* unique key on the LHS */
   FD 2 : location --> leadsource  /* many location for the same leadsource,
				      I also assume that each location only
				      has one leadsource */
   FD 3 : type --> location /* several types for one location, and I assume 
			       that each type only has one location */

The above DB scheme only satisfies 2nd normal form, i.e. there exists 
Transitive Dependancy from FD 2 to FD 1 and FD 3 to FD 2. Further decompostion 
into 3rd normal form is required to achieve lossless join property , i.e. the 
join anomaliy sympton which you have described.

And In your table B description "Table B may have several entries for the same
leadsource, location and type but with unique rundate", it doesn't make any
sense to me.


Donovan Hsieh
Hewlett-Packard
Software Method Lab
email : donovan@hpsmtc1.HP.COM