Re: Second Normal Form help.

Tom wrote:
>
> Sure, Its valid in first normal form. To be in second normal form, all
> non-key attributes need to be functionally dependant on the entire key, not
> key combinations.
>
> James_one <James_one_at_deja.com> wrote in message
> news:WUMw6.271824$65.1466583_at_news1.rdc1.fl.home.com...
> > Is that not a valid primary key, the combination of BuildingID and
> > GarageNumber?
>

It is so nice to have an functional ISP and be able to post again, instead of having to lurk at work.

If someone would be so kind as to explain where the above nonsense comes from I would be most grateful. I know that Oracle goes overboard with the notion that the only valid primary key is a unique integer, and I have some reason to belive that MicroSoft is even worse, so maybe it comes from there.

Anyway (warning, long discussion ahead), suppose you have two buildings: Casino I, with a basement, attached street level, and rooftop garage, and Casino II, with an attached street level and rooftop garage.

Then, your garage table (with data) will look something like this:

Building Garage Garage
  ID ID Attributes
-------- -------- ------------

   I      basement   whatever belongs to the basement
                     garage in Casino I
   I      street     Similar for Casino I's street level
   I      rooftop    Similar for Casino I's roof
   II     street     whatever beongs to Casino II's street
   II     rooftop    similar for Casino II's roof.

It should be obvious that if you want to know something about the rooftop garage on Casino I you have to specify both the building ID and the garage ID, nothing depends on just part of the key. The Whole Key is the building ID and the garage ID. This is What A Concatenated Key Is All About: you need All of it to specify a unique row.

Now, lets suppose this Casino Owner goes into business in another city, and gets into oursourcing: He buys two casinos and 5 garages, but they are all detached, so that he can assign any garage to any casino depending on need. Then, you would need an association table between building and garage to specify who goes where, and you would no longer have building ID in the garage table.

But then, you would really have a many-to-many relationship, and the original post specified a one-to-many relationship. This is what the first model tells you: that the garages cannot be separated from their buildings, and you cannot use the second model.

I don't want to get into a big discussion over which model is better (because I know nothing about the business reality). But I hope I have explained (in too much depth) that there is nothing intrinsically wrong with a compound key and 2nd normal form.  

-- 
Phil Singer                |    psinger1_at_chartermi.net
Oracle DBA

Go Wings!!!!!!!