Right guys, this is my area!
I worked as a product tester for just over a year in a sourcing office in Shanghai for Best Buy and frequently had to explain exactly these kinds of things to the sourcing managers.
What TreeTop said is for the most part absolutely correct, however there are a few more things that need to be taken into account than just the quality of the connectors.
Composite a yellow labelled RCA connector is usually coupled with your analog stereo audio and most commonly know as the "AV" cable works in exactly the same way as the R-59/R-6 coaxial cable and provides two signals that have been combined, Y and C. They then have to be seperated to display a picture on your screen. Y is luminence (essentially the picture in B&W) and C is chrominence (color).
S-Video keeps these 2 signals separate, if you look down the end of the connector, you'll see 4 pins and a plastic rectangular shaped thing. The plastic is just to guide it in nicely as to not bend the pins, 2 of which carry the +ve and -ve Y signal and the other the +ve and -ve C signal.
If you are viewing a signal from a DVD player or other fairly high quality source (D-VHS player, PC, Digital Cable Box), the S-Video cable will always produce more accurate results, since the signals are already seperated to begin with.
They would have to be combined to pass through a composite cable and then seperated again by a filter in the TV to create a picture.
So, Gemini this is your prefered choice.
Now here is the tricky bit, if you are using a regular VCR or terrestrial TV through your VCR or other device as your source,
the signal is already combined to begin with! So now, the question is which is your best Y/C seperation filter? Is it the one in your VCR or is it the one in your TV?
If you have a plasma screen, chances are that it will have the better one, in which case you should use the composite/AV cable and let the TV do the filtering.
If you don't know which is better, compare the 2, it will depend on your own configuration.
I could go into this a lot further and talk about different Y/C seperation filters, but it will go on for a fair bit, so I'll abstain for the moment, but feel free to ask me later if you're interested.
Instead, I'll touch on some of the other cables mentioned and oh yeah, I just wanted to mention that S-Video does not have the bandwidth to support progressive scan so can only support up to 480i, not 480p (sorry Treetop but I'm pretty sure about this).
Component/RGB cable: This is a cable that has 3 RCA connectors labelled Green, Blue and Red, however the component signal IS NOT the same as a RGB signal.
The Component signal, often called and labelled Y Cb/Pb Cr/Pr sends the actual picture signal through the Y cable (labelled green) and then
components of the colors through the other 2 (I'm not going to go into how much %age-wise, it's too technical and I don't know it off-hand).
You guys may have noticed that I'm talking about Y and C again, right? That's because the component signal is just a further breakdown of the 2 signals passing through a S-Video cable, meaning less combining and seperating of signals (this time color-wise) to be done before and after the data transfer from your DVD player to your TV.
This cable is actually usually passing a digital signal (although it can pass an analog one) and is therefore capable of transmitting HD signals 1080i and even 1080p (in development, but I've seen it).
This cable is slightly better than using a VGA cable and is going to give pretty much the same results as a DVI cable.
Gemini, if you have a progressive scan DVD player, I would HIGHLY recommend you use this cable, this will dramatically improve the picture given by your projector. If you want to just try it, you can use your AV cable and just plug yellow into Y, white into Cb/Pb and red into Cr/Pr. It will work and look better than with your S-Vid cable, but eventually you should get a proper component cable to reduce signal loss (they are usually thicker and have gold cons).
As for DVI, shit this is getting long, there are 4 types of DVI connector, DVI-A, DVI-D, DVI-I and DVI-M.
DVI-A is analog.
DVI-D is digital.
DVI-I is integrated (both analog and digital).
DVI-M is a mini version of DVI-I, it has a different connector but leads are available to any of the others and it is compatible with all of them.
Right, now digital audio, the moment you've all been waiting for, lol.
OK, the coaxial digital cable we are talking about here is just an RCA cable (all RCA cables are in fact coaxial, they just look different to the R-59/R-6 one).
So, coax or optical(toslink)?
Right so even though I agree with smee when he says:
most of us mere mortals can't tell the difference
i've tested hundreds of products with both and I have never been able to tell which is better.
However, from a purely technical point of view (this has been tested with sofisticated equipment), coaxial is better under 8 meters and optical is better above 8 meters. There you have it! Basically that is the point when light starts to outperform metal in tems of signal loss.
However, make sure you don't bend yopur optical cable or walk on it by accident, the quality may go waaaaay down!
Sorry for the long answer guys, but it should cover most things. Tell me if you want to know more about DVI or Y/C seperators. I only touched the surface on them. Also I could go on about RG-59/RG-6, it can actually carry a digital signal, damn, better stop myself now...
And by the way, HDMI, I agree with Treetop, nothing to add.
Edit: fixed a couple of typos and formatting, also clarified a small part.