Peterb seems a fairly reliable source of insight, as his recent Lights, Action, Camera post shows. This one paragraph summarises my feelings about game cameras rather nicely:
The camera in a 3D game needs to operate without the players' supervision. The best of all worlds is for the levels to be designed such that moving the camera around... simply isn't necessary. Level design should conform to the realities of the in-game camera, not vice-versa. Requiring the players to move the camera to play the game well reminds them that they are above the game world, and not a part of it.
I am so sick of console games in which the designers apparently decided to opt out of providing any sort of camera system at all. If you make a third person game require the player to use the second stick to manually control the camera you just made it basically unplayable to anyone who doesn't play FPS games on consoles regularly! Grrr!
Of course, having level design conform to the realities of the camera can be an expensive business... I expect the camera-perfect levels of Eternal Darkness cost quite a bit to develop.
As for Dan's comment:
Remember Mario 64? Give every frickin' 3D game Mario 64's camera system. Problem solved.
I agree... in principle. But remember how big the corridors were in Mario 64? They had to be that size or the camera system broke down. We still haven't completely cracked the problems of 3D camera in a generic environment, alas, and as Peterb suggests, it comes down to the level designers to ensure their levels work with the game camera.
As for Erol Otus, I always hated those D&D covers - but strangely, I found myself missing them when they were gone. Nostalgia is a strange thing...