I'm a very new user of LR and CS3 but I'll give you my $0.02 worth. I'm sure das experten can illuminate further.
LR is (as I'm discovering) a very powerful library/cataloging system, with an ability to make simple adjustments and then "publish" that image (back to disk, the web, printer or email...) IMO, if you got it 95% right when you shot it, LR is great all by itself.
CS3 (PS10?) is for the hard-core fixes / effects jobs (but you can still catalog your efforts using Light Room.)
Between the two, the work is seamless. IOW, you won't see any adjustments you've made to an image if you only look at it in the LR library. Pop it into Light Room's Develop area or export it to CS3 and any chagnes you had already made (Crop, Color, etc...) magically appear. They call this "non destructive editing" where your originals always stay intact (unless you choose to edit the original...whcih you can do if you want.)
Elements (and I only used up through v4) takes a little of both LR and PS and combines them into one program. There is a catalog where you can catagorize and keyword your shots and then there is an editing module for making adjustments. You have to conciously choose to do a "Save As" so that you don't alter the original where in LR/CS3, you are simply saving different versions of your adjustments without dicking up the original.
I'll say this however: Light Room and CS3 are resource-hungry programs. I'm currently working on a 4+ yr old AMD 2100+ with only 1Gb of RAM and things are marginal at best. LR is slow and methodical and CS3 will only run if I flush the cache after each tweak otherwise, it crashes relentlessly. I'm already talking to my local custom PC shop about building a 2.13 Mhz Dual Core system with 4Gb of 533Mhz DDR RAM and a 256Gb Dual-Head video card.
Elements 4, on the other hand, runs just fine on my current (ancient) machine and positively screams on my Dell Inspiron 1405 laptop. Canon RAW, psd, jpg, tif, whatever, will open almost instantly.
Personally, I popped for the LR/CS3 combo for the seamless workflow but Elements would work just fine for someone who wants an "all together" solution but doesn't need the bells and whistles you get with full up Photoshop.
Hope my meager thoughts are in some way helpful.
Good Luck with your decision making.