waahayyy something i can give advice on.
righty, I have all 3!!!!
Lightroom will fulfill the majority of your needs. It imports raws, has pleanty of procesing tools for either the whole image or selective parts of the image. Its great for building slideshows, web galleries. Export jpegs etc. very versatile, very usefull, and ecourages a very easy workflow
90% of what i do is on lightroom
Elements, this is the next tool in line. If i cant do what i need to in lightroom for example, adjustments that require layers i use elements.
I also find elements auto adjustments very usefull. Sometimes i cant get it right in lightroom. I also prefer the dodge and burn tools in elements better than lightroom. (maybe im just mre used to them).
The image, can be opened from lightroom, automatically in elemens and then a copy is saved in lightroom as a tiff fil ( ithink).
CS3 / 4. rarely ever use this. Its a very comlex tool and unless you are into large amounts of layers, image manipulation etc its probably daunting and over the top for your requirements.
So, in summary.
Lightroom should cover the majority of your requirments. Try the trial
Elements will most likely fill the gap in what lightroom cant do. Again, try the trial version
CS4 is a serious tool, hugely expensive and your highly unlikely to use it to its full capabilities.
Couple in some stand alone noise reduction software (noiseware is recomended to me) and maybe a stand alone sharpening tool you should have more than you need to produce the results you want.
Greg