Is it possible to recover an account that was explicitly deleted by a Stack Exchange staff member (as opposed to an elected diamond moderator)? That is, the kind of account where the user requested the deletion, or committed atrocities against the community?
No, this is not possible. Once a profile is deleted, it is gone forever.
We can restore past content to a new profile, but we only do so for users who were deleted erroneously, usually due to a moderator making a mistake (I've done it a couple of times myself). If you requested the deletion in any way, then the content will remain anonymous forever.
By restore content, I mean that posts (questions and answers) and comments which were connected to the previous profile would be attached to a new profile that must be created. This new profile will have a fresh creation date, and won't have any past profile info, votes, favorites, bounties, reviews, or badges (some post-related badges may be automatically re-awarded if you still qualify for them).
It is now, apparently, not current Stack Overflow policy to undelete accounts unless they were deleted in error.
Here's a reply from team@stackoverflow I got when I asked them to undelete my own deleted account on 2016-05-18.
Unfortunately we do not restore profiles which have been deleted. That information on Meta is from 2009 and outdated. Undeleting profiles is something we reserve only for users which were deleted in error.