As someone who beat the game, I can safely say that while the game is not as brutally difficult as it was made out to be (though some bosses, like False King Allant, are just infinitely irksome), it's definitely designed to be rather unfriendly to new players. This game truly throws the standard tendencies of most normal gamers in an RPG setting out the window. Usually, if you want to "brute-force" through an RPG, you simply roll a warrior, pick up the biggest pointy stick you can find, and hold down the swing button until you've cleared everything from the first measly grunt to the final boss. Sure there's the occasional nuanced fight where you might actually have to dodge an attack, but those fights are usually fairly forgiving, what with your "hold down forward + attack" strategy being temporarily thrown out the proverbial window and all.
Demon's Souls doesn't apologize for the fact that you can't "re-re-run" through the bulk of its content, and it punishes you for trying. Dying while alive (read: not in Soul Form), reduces the "tendency" of your world, and the enemies get stronger each time it happens. Checkpoints are non-existent because a lack of checkpoints is a proven, legitimate way to add difficulty to a game (see IWBTG). Dodging properly (read: not pressing circle just the one time as a get-out-of-danger-free button) becomes something you have to learn, and the game encourages you to treat its denizens as a real threat; even the lowly, psychotic, half-naked tribal fool with a broken sword.
The game provides a very stark, lonely atmosphere, and maintains it very well because of its limited multi-player capabilities (blue phantoms are hit-or-miss, and black phantoms are the bane of my existence), and while some of the player notes are rather useless, many of the ones I've found provided information I would not have had otherwise (thank you to whoever pointed out that those big red grubs were more dangerous than they seemed). The game encourages you to be more realistic in your approach to combat, forcing you to be cautious or selective about your targets (with the exception of those rare character setups that allow for utter domination with a few memorized sequences) lest you wind up some lesser beastie's evening snack.
I can understand Yahtzee's beef with the game, partially because (and this is key, folks) it's his job to find things to gripe about, and because, as mentioned earlier, the game is rather callous toward the uninitiated player. Dodging takes awhile to get the hang of, the game's not very kind about giving you "practice time" for your abilities, and some of the bosses have inventively cruel ways of punishing you for reacting the wrong way to a flashing-light-attack you're seeing for the first time (Curse you, Allant!!!). However, if you're tired of the over-the-shoulder dungeon crawler that rewards you for duct-taping your attack button down and your joystick forward while you run off to snag a piece of pie (or tail, whatever you prefer), then I'd recommend giving this thing a look. Maybe not a purchase, but at the very least, a rental.
However the name is appropriate because the big demon is collecting souls, the boss demons are doing the same, and the title references the Demon's Souls (which you are trying to steal, collect and trade for better items and equipment! [and ostensibly to increase enough in power to take the Old One behind the shed]) - sooooo 
