Program Auto mode or Program AE mode: Believe it or not, program mode is almost identical to Auto mode but you have more control over some of the little features such as flash, exposure compensation, white balance, ISO, etc. In Program Auto mode your camera automatically selects the shutter speed and aperture value, you select the ISO appropriate to the lighting in which you are shooting. This is a great mode to use if you want to get out of Auto mode and play with different things to tweek your photos but you still want the camera to have the most control.
Shutter Priority Mode: Don't ask me why it is Tv on some camera models because I have no clue. In Shutter Priority Mode you select your shutter speed and the camera selects the aperture and ISO to match. This is a great mode to use when you want complete control of your shutter speed but you don't know how to select aperture and ISO. Use a faster shutter speed to "freeze" motion and a slower shutter speed to blur motion. I guess you could compare this mode to Action and Slow Shutter Speed modes but you have more control over the shutter speed and other small features such as flash, exposure compensation, light metering, etc.
Aperture Priority Mode: In Aperture Priority Mode you choose the aperture value and your camera selects the shutter speed and ISO to match. This is a good mode to use when you want to control your depth of field (another tutorial to come). Essentially, a small aperture (high f-stop number) increases your depth of field thus bringing both your subject and background into focus. A high aperture (low f-stop number) will soften or blur your background. You won't get that really blurred background with a point-and-shoot camera like you can get with a SLR camera, but your background can be softened or slightly out of focus to make your subject stand out a little more. I guess you could compare this mode to Portrait and Landscape modes but you have more control over the aperture and other small features.
Manual Mode: The scariest mode of all for most people. This is the mode where you have complete control of all of your camera settings. It's pretty much self explanatory. I'm not sure if I'll ever get to the point that I shoot in complete manual mode but it's a dream for me to at least know enough that I could shoot in manual mode if I wanted to....
That rounds out the overviews of the different, common modes on a point-and-shoot camera. When you look at those "scary" modes in this simple way they don't look so scary do they?