Most of the fancy signals are left over from external telephone modem control. This is back when a computer had to talk to some external box that was then in turn connected to the phone line. Think about most of the other signals as not just transferring data, but controlling a computer-interfaced telephone.
About the only signals beyond RX and TX you are likely to encounter today are RTS and CTS. These are for flow control, and can still be useful today. However, the vast majority of today's devices that still use RS-232 use only the basic RX and TX lines.
Some microcontrollers have UARTs that can optionally use RTS and CTS, because out of band flow control can be a useful thing. I have a project going on right now where I've got two PICs on the same board that need to send data back and forth between each other, and I connected RX, TX, RTS, and CTS. One of the PICs implements this flow control in hardware, so that is particularly easy. On the other I had to do it in firmware since that UART has no such hardware capability.
The cable connector body must contact the chassis connector body, which is generally metal, conductive, and bolted to a conductive metal case. Therefore the only choice for the connector body is a connection to the equipment case.
This (the equipment case) is obviously grounded - to safety earth. There is no choice about this.
However it is common for safety earth connections to form ground loops or be connected to a noisy earth point shared with, e.g. multi-kilowatt triac dimmed lighting systems (i.e. spiky 50Hz waveforms.)
So audio equipment design must separate the two functions of an earth connection: safety, and noise reduction.
Connect external metalwork to a safety ground, without worrying about the noise on it.
Connect signal ground to a separate low noise ground, without worrying about whether it is fault-current rated.
And this dictates the need for a separate pin for signal ground connections.
When I worked in broadcast audio, "pin 1 is ground" was one of those rules we didn't even have to think about.
Consider many pieces of equipment connected to an audio mixer by XLR cable : there may be many signal ground connections - yet we must eliminate ground loops. There are options on some of these boxes to "float" the signal ground - isolate it from safety ground - using the signal cable to provide a signal ground reference from the mixer.
Best Answer
Assuming you mean the standard 25-pin connector, then there is a simple answer: The RS-232 specification states the defined function for every one of the 25 pins bar three. There are lots of features of RS-232 which are little used these days!
This diagram is a brief overview of the pinout of the 25-pin connector:
(Figure above courtesy of Dallas Semiconductor Application Note 83, Fundamentals of RS–232 Serial Communications)
It's a similar explanation for the 9-pin connector made popular by its use on the original IBM PC/AT (the earlier PC and PC/XT used the full 25-pin RS-232 connector). All 9 pins have a defined function, whether you use that function or not.
(Strictly, it's a DE-9 connector, not a DB-9. The first letter, D, refers to the D-shaped connector's metal shell. The 2nd letter is the size of the connector shell, and this smaller connector is size "E", whereas the larger 25-pin connector is size "B"; other sizes exist too. See the Wikipedia article on the D-subminiature connectors for more details.)
(Figure above also courtesy of Dallas Semiconductor Application Note 83, Fundamentals of RS–232 Serial Communications)
In fact there is no power pin in the RS-232 specification. Of course people can adapt an RS-232 connector to include a power connection, but then it doesn't fully meet the RS-232 specification.