Photo album software

It stores in your computers folders (its not just for organizing photos but all kinds of contant) you can add key words and key word groups and search by them, if thats what you mean by tagging.

Lightroom is well worth the $$ its almost all I use to store/ look at photos, and its a mighty good raw processor, it has taken over from capture as my photo editing work horse, I only open photoshop for the heavy stuff.

Likewise, i've been using Lightroom for a few years now, wayyyyyy superior to Capture one in so many ways, the only people I know that use capture one do so because its just easier to shoot tethered from it on their hasselblads etc, although I know lightroom are working on that for the next update/version.
I only shoot RAW, and I love using lightroom to get the histogram in the best possible shape, a little bit of sharpening, colour correction, and then as you say, onto photoshop CS for the heavy stuff.

Picasa does tags.

It probably does loads of other shit too but I literally just use it for quick viewing of my photos and the occasional tweak + upload.

For RAW stuff I use CaptureOne.. but this is getting into a whole other area..

Lightroom FTW unless you're shooting tethered.....for now anyways ;)
Lightroom organizes photo's better than any other software out there right now, the functionality of its library section and its organizational abilities/tools are unsurpassed at the moment.