Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!utgpu!water!watnot!watmath!clyde!rutgers!lll-lcc!ames!ucbcad!ucbvax!jade!violet.berkeley.edu!phgun
From: phgun@violet.berkeley.edu.UUCP
Newsgroups: comp.databases
Subject: Re: Normalized Databases
Message-ID: <3014@jade.BERKELEY.EDU>
Date: Thu, 2-Apr-87 15:24:36 EST
Article-I.D.: jade.3014
Posted: Thu Apr  2 15:24:36 1987
Date-Received: Sun, 5-Apr-87 01:30:54 EST
References: <43239@beno.seismo.CSS.GOV> <739@rtech.UUCP>
Sender: usenet@jade.BERKELEY.EDU
Reply-To: phgun@violet.berkeley.edu (Himawan Gunadhi)
Distribution: na
Organization: University of California, Berkeley
Lines: 32
Keywords: normal form, relational

While I agree that in most cases it's important to at least have
the relations in 3NF, sometimes there's no neccessity for it.
Take the example where my relation is 

	 R(NAME, PHONE#, ROOM#)

where NAMEs are unique, each person has his own PHONE#, and more
than one person may be assigned to the same ROOM#. R is not in 3NF,
since ROOM# is transitively dependent on the key NAME
(i.e. NAME->PHONE#, PHONE->ROOM#, NAME->ROOM#).

By Normalization theory, we want to (non-loss) decompose R into 
R1(NAME, PHONE#) and R2(PHONE#, ROOM#). But this is only if there's
a reason to know the assignment of phones to rooms independent of
who is occupying them. Otherwise I don't see any reason why we
should worry about "losing" information about the location of
phones in rooms if we delete some name from R, if it is  of no
significance to us (e.g. we rip off the line if no one's using
it!). But if we have lots of queries pertaining to "who is in room
X?", or "which room is Y located in?", this decomposition means we
need to always JOIN R1 and R2, which is a performance LOSS.

Note  that R cannot be decomposed into R1(NAME, ROOM#) and 
R2(ROOM#, PHONE#) because this is a lossy-join decomposition,
though on the surface, they gives us a more efficient way of
executing the 2 queries above (after all they're both in 3NF,
but the joining-attribute is not a key in either relations).

---Himawan Gunadhi
   ARPA: phgun@violet.berkeley.edu
   UUCP: ...!ucbvax!jade!violet!phgun
   BITNET: phgun@ucbviolet.bitnet