To better illustrate how these different displays could be achieved, here are a few display diagrams.
A. is more or less the current system, but to help keep some of the weird transparency effects down, the engine would overlap water tiles like it does with non-transparent tiles and then alpha-blend them as one solid piece. This would keep the water from multiplying with itself, and creating the ugly water fadeout that happens on the edges of tiles.
B. creates the illusion of transparency, by displaying all tiles with edges under the water in a blue-shifted tone, as an overlay that displays on top of the water tiles. The overlay (all the tiles in blue-green), rather than the water, would then get a bit of alpha transparency. This creates a lot of subtle shades of blue on the surfaces, and keeps submerged tiles legible... and I daresay it looks really sharp. However, an elegant solution would need to be found for tiles only partially filled with water... perhaps the engine could create a mask to erase parts of the blue-shifted overlay tile, to match whatever is exposed above? Needs more brainstorming.
C. just for the sake of demonstrating the principle, a similar technique to B. could be used to create a reflective overlay on the surface of water. This could be displayed over other models, but care would need to be taken that the tiles don't get too complicated. Though looking pretty is always nice, making the map easily understandable is the first goal of a graphical display.