Ahhh, it was all dramatisized
As much as I love Aeris and FF7, that scene HAD to put fantasy in it, or else it would be sad in the sense that you feel sorry for Cloud even trying rather than sad that the final cetra of the world has passed.
Through common science, you learn that a body is buoyant when dead and would have floated to the top of the water unless Cloud put some kinds of weights in Aeris's clothing.
So, the body sinks, and why? Because it is Final FANTASY, they had to put some fantasy elements into the movie to make that scene dramatic. It, just think of it, if he put her in a frickin puddle, would you be sad? Seeing her body sinking down that deep wouldn't be so sad, now would it?
Although, correct me if I'm wrong... Was she really dropped down in the church? Is there really a hole of water in her church, or wasn't she dropped at the City of Ancients? Guy who ever beat the game here...
But, before I ramble on... In the great words of Dread Lycan,
"When you put real life logic into a fantasy RPG, God kills a cat girl."
Please, please save the cat girls... They don't ask for much except to be loved...