And lastly (though not necessarily the most powerful) – some kind of artillery piece that attacks an area. The impact animation could be based on a scaled-down meteor shower.

But of course, we also need to consider whether this is feasible given the game's limitations.

Let's create a mortar that explodes and deals area-of-effect damage with a meteor shower. We can use a pipe from a dwarven building, place a couple of dwarves nearby, and then use the animation of a priest's spell cast, or rather, just the effect that comes out of that pipe, followed by a meteor shower.
By the way, can we make a city tower attack an area or have some other effects? We could diversify sieges by giving the Lizardmen a tower attack that inflicts a crippling wound, the Dwarves an area-of-effect attack, and the Elementals a freeze effect for 0.30 turns (I think there was something like that, but if not, then a fire sign).

Added 4 minutes later
By the way, regarding the ship cannon, the Skull of Shadows mod has a Tier 6 pirate unit with a real cannon on its back. We could take it from there; the mod creators seem to have taken it from somewhere as well