I haven't looked at the market for animation software in a while, so I can't give any specific software recommendations. Some of your questions are theoretical, and I can noodle on those.

Everywhere you go in the graphics world, you encounter illusion. JPEG compression is based on studies of how the human eye perceives things, and it judiciously discards detail the eye won't miss, and relies on the eye/brain to imagine missing detail.

"3D" graphics aren't three dimensional, they're projections onto a 2D surface. Animation isn't really smooth motion, it's a series of still frames containing visual cues, such us motion blurring, that the mind assembles into an illusion of motion.

The seemingly infinite detail in advanced graphics is an illusion too. The first stage is to prepare models based on solid geometry, with smooth surfaces and clean lines. Then the "grunge" artists go to work, telling the software to apply random noise, or non-repeating fractal patterns, to surfaces on the object. Grunge artists got their name because part of what they do is dirty things up, applying random "dirt", scuffs, marks, etc., to the original perfect objects to give them a natural feel.

Another kind of illusion involves creating simulations of real objects, like clouds or dripping blood. The artist doesn't have to draw them, but rather, a programmer creates a Cloud routine, and the artist tells it "draw me some clouds in this blue area". Clouds turn out to be easy to simulate using fractals. Or a DrippingBlood routine, which is probably a simple model of fluid flow and surface tension, colored deep red. The artist draws a wound on the corpse, clicks on it, and tells DrippingBlood "Gimme two pints leaking from here, over 30 seconds".

In a sense the grandest illusion of all in animation is time. It's the fundamental quantity an animation artist manipulates. The amount of time it takes to render an animation--convert the mathematical specifications of objects and motion into a series of still frames making up the animation--doesn't have to bear any relation to its running time. One second of animation might take a day to render on a "farm" of several hundred computers cooperatively rendering bits of the animation.

That's an answer to your wonderment: "It really doesn't look as if any reasonable amount of computing power and memory would be enough." It doesn't actually take any particular amount of computer power and memory, but rather, your farm of rendering computers takes as much time as it needs to get the job done. You have a fixed amount of computer power at any instant in time, and if your animation needs a lot of power to render, you run the renderer a long time.

That's something to keep in mind when considering the idea of realtime simulation of reality, a la The Matrix. There isn't enough computer power on Earth, yet, to construct a reality in realtime that would fool the human mind. If you wanted to run that trip on somebody today, you'd have to find a way to slow down their brain to match the ability of the simulation hardware to feed sensory input to the brain.

Bottom line: All that detail is based on cheating, in one form or another. Or to put more diplomatically, animation is the scientific manipulation of human perception to create illusions that seem to contain far more detail than they actually have.