The naming convention is explained in Chapter 4 of the FAA's Instrument Procedures Handbook:
When two or more straight-in approaches with the same type of
guidance exist for a runway, a letter suffix is added to the title of
the approach so that it can be more easily identified. These approach
charts start with the letter Z and continue in reverse alphabetical
The TERPS explanation (section 161) is a little more specific:
d. Duplicate identification suffix. When more than one procedure to the same runway uses the same type of navigation system for lateral
guidance within the final approach segment, differentiate each
procedure by adding a non-repeating alphabetical suffix using the
letters “S” through “Z.”
The Handbook gives the example of two approaches at Chicago Midway (KMDW), which has the RNAV (GPS) Z RWY 13C and RNAV (RNP) Y RWY 13C approaches. They're both straight-in GPS approaches to the same runway, but one requires RNP and the other doesn't.
As the question itself and this comment note, there are plenty of other possible differences between approaches to the same runway; RNP is just one example. Having multiple approaches allows an airport to accommodate aircraft with different equipment and capabilities or, as section 140 of the TERPS puts it, to "gain an operational advantage":
To permit use by aircraft with limited navigational equipment, an IFP [Instrument Flight Procedure]
should be formulated using a single navigation source whenever
possible. The use of multiple navigation sources of the same or
different types may be permitted to gain an operational advantage
I have no idea how to find out why a specific runway has multiple approaches or how the approaches were designed; presumably it's something that the FAA and the airport operator (and maybe even the airlines) work out based on local runway use and traffic requirements.
In the example in the question, one plate requires GPS, the other DME or radar. So a non-GPS equipped aircraft can still use the ILS, but with different ways of getting to it, different minima, etc.
Note that there's a similar naming convention for circling approaches:
Approaches that do not have straight-in landing minimums are
identified by the type of approach followed by a letter. [...] As can
be seen from the example, the first approach of this type created at
the airport is labeled with the letter A, and the lettering continues
in alphabetical order.
Their example is Rogue Valley International - Medford (KMFR), which has the following circling approaches:
- LOC/DME BC-B
- RNAV (GPS)-D
There's no explanation that I could find in the Handbook or TERPS to explain why circling approaches must have a letter even if the navigation systems are different. But as @RalphJ mentioned in a comment, this provides a clear way to identify multiple circling approaches using the same guidance, e.g. a VOR-A approach from the north and a VOR-B one from the south.