1) The one thing you have to keep in mind is that composite (The yellow jack) and s-video are interlaced 480 formats and there is nothing your TV can really do with them to make them look much better.
2) If you are able to use a VGA or DVI connection, you should be using the very highest resolution that the TV can handle. From there, the PC will upscale as best as there is except to spend $5000 on an external video processor. Very seldom to you find TVs that have unquestionably stellar upscaling abilities. They are out there but they are not the majority.
3) The presentation software (MCE, SageTV, etc...) has little to nothing to do with the quality of the video. It is pretty much the domain of the video card and the drivers. This is why when people ask about a PC/HTPCs ability to produce better output than say a dedicated BluRay player, the only real answer is "That depends".
Every month ATI puts out newer drivers that usually make things better and faster. NVidia is close behind at every other month or so. Beyond that, there is a new generation of graphics card out every 12 months. And to top it off, there is still a 6 month later refresh that fixes mistakes or makes up for a lack of features until the next full new generation of card comes out.
The shortest answer (And it ain't short
) is that the best possible situation is that you have teh very newest video card series that you can put in your PC, you use the input on your TV that is the most "compatable" with the video card (For some this will be DVI/HDMI and others it could be component). From there, make sure you are using the latest drivers for the card and that the resolution that the card is set to output is as close to the native resolution of the TV as is possible. If all of this is done, your HTPC should be doing all or the lions share of the upscaling/downscaling of non-native formatted video content. This should deliver the very best image possible without breaking the bank or ending up divorced.