1) Assuming you want something that can interface directly with the main parallel bus of your SBC (rather than going through something like SPI), essentially all UARTs currently available are clones or supersets of either the 16C450 (which was the UART used in early IBM PCs) or the SCC269x (the NXP one). They are also pretty much only available in SMT packages. If you want through-hole, and you don't want NOS, you'll need an I2C/SPI based UART, or to use the old-fashioned
WDC W65C51N or
Zilog Z0847006PSG.
Link:
One of many examples of a 16C450-compatible UART available.
2) Actually, many desktop PC motherboards do in fact include an RS-232 serial port on the backplate. Business orientated boards often have 2x serial and a parallel port, even these days. Consumer and "Enthusiast" boards often have an internal serial header which can be accessed using an
inexpensive bracket. My own computer falls into the latter camp, and I have just such a bracket in place. But failing that,
FTDI make a range of cables and adapters to suit for converting RS-232 or other similar protocols to USB. I'm not sure about an Arduino Nano specifically, but you can definitely use the Arduino Uno as a 232-to-USB adapter: remove the Atmega328p processor chip from the Uno, and connect the TX/RX signals from your UART at TTL level (
i.e. not via a MAX232) to the Uno's pins D1 and D0. You now have a somewhat physically fragile but nevertheless fully functional RS-232 to USB adapter - I have done this in the past.
3) In addition to (a) and (b) that you gave, RS-232 can also connect two SBCs together to create networks, or use it as a general peripheral interface bus (there are plenty of industrial widgets which can be controlled, or report data, via 232). For 6502 hobbyist use, it usually comes down to the fact that an RS-232 based interface is one of the easiest and quickest to implement and get you up and running quickly, then you can use that to help you launch off into (and debug) any more complex interfaces you may want. A UART using different logic buffers can also implement RS-422, RS-485, MIDI and DMX-512 protocols, and no doubt others too.
_________________
Want to design a PCB for your project? I strongly recommend
KiCad. Its free, its multiplatform, and its easy to learn!
Also, I maintain KiCad libraries of
Retro Computing and
Arduino components you might find useful.