You were doing it correctly. You just forgot the base water tile(s) with no border at the very bottom. Like so:
As for the waterfall/whirlpool animations, well those are simply groups of 4 tiles that are animated in order from top to bottom, and then repeat.
Also worth noting, on the tileset tab of the database editor, you can change the sequence and speed of the animated tiles.
Going back to your tileset, notice that from left to right there are only 3 frames for each piece of water, correct? Well imagine those are numbered 1, 2, and 3.
You can choose to have them show each once, in order, and then reset back at 1, or do more of a natural, wave like sequence, working backwards once it gets to 3.
This distinction might not seem very important for normal water tiles, but lets look at another example. Suppose you have some custom water (like in my
water tutorial) and you have an animation for the water rising and then lowering against a wall. If you leave it as a 1-2-3 sequence (top) then it suddenly makes an abrupt leap from HIGH water to LOW water, and will look quite unnatural in-game. This is where the 1-2-3-2 sequence really shines.