Path: utzoo!attcan!uunet!lll-winken!lll-tis!helios.ee.lbl.gov!pasteur!ames!pacbell!att!ihlpb!skennedy
From: skennedy@ihlpb.ATT.COM (S. Kennedy)
Newsgroups: comp.databases
Subject: Re: One to Many to One to One relationships in Informix SQL  (LONG!!)
Message-ID: <9200@ihlpb.ATT.COM>
Date: 14 Dec 88 16:35:39 GMT
References: <9176@ihlpb.ATT.COM> <11410003@hpsmtc1.HP.COM>
Reply-To: skennedy@ihlpb.UUCP (S. Kennedy)
Organization: AT&T Bell Laboratories - Naperville, Illinois
Lines: 75

In article <11410003@hpsmtc1.HP.COM> donovan@hpsmtc1.HP.COM (Donovan Hsieh) writes:
>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.


Let me show an example of this possibly screwed up structure.

TABLE B
Leadssource        Location        Type      Rundate         Cost            
-----------        --------        ----      -------         ----

  YRHD                B1            R        06/01/88       300.00
  YRHD                B1            S        06/01/88       300.00
  YRHD                B1            B        06/01/88       300.00
                  (through 24 more location sets)

  YRHD                B1            R        06/01/87       500.00
  YRHD                B1            S        06/01/87       700.00
  YRHD                B1            B        06/01/87       200.00
                  (through 24 more location sets)

  CBW                 A0            R        05/01/88      1100.00
  CBW                 A0            S        05/01/88       200.00

  INWJ                A0            R        09/12/88        50.00
  INWJ                A0            B        09/12/88        50.00
  (etc)

TABLE A
Leadssource        Location        Type      leadnumber                        
-----------        --------        ----      ----------

  YRHD                B1            R           1347
  YRHD                B1            R           1653
  YRHD                B1            B            987
  CBW                 A0            R            466
  CBW                 A0            S           1433 
  CBW                 A0            S           1944
  USER                A0            S            616
  USER                A0            S            789
  USER                A0            S            987
  (etc)



So I would have an entry in Table A that would not match Table B because there
was no cost associated  with that leadsource/location/type )LLT). Code USER for
example. Rundate is unique for each occurence of LLT. Table C contains the
list of *all possible leadsources* so I used that as my driving table to sort
by leadsource.                     


Would I have to come up with a unique Master Table to get around all of this??
  

Shawn Kennedy
att!ihlpb!skennedy
AT&T Bell Labs