Its a photoediting application, designed specifically for Nikon cameras. It does the majority of what you'd expect CS3 to do ( with respect to photography anyway - obviously CS3 has additional functions, but then it's really a pixel editor/graphics artist tool ). You can easily convert RAW files, change exposure, white balance, colour saturation, sharpness, B&W conversions - all the usual stuff you would expect of a photography based package - and more.
You will have a far tougher time learning CS3, than using a more photography based application such as Capture NX2 or Lightroom. CS3 is really for advanced photoediting/digital artists. Sure, an average Joe can use it for more mundane tasks - but that's like owning a Ferarri to drive to the corner shop 100 yds away.