Back in the day, those things were hard to aim at a massive effing wall. I remember seeing a reenactment on the History channel where it took an entire day to aim a trebuchet -- a more advanced and more easily aimed version of the ballista -- at a wall. I don't know how effective they'd be versus a dragon, which is able to fly and dodge.
A trebuchet is a magnitude larger than a ballista it is also a medieval weapon while the ballista dates back to the ancient Greece.
A ballista is basically a giant crossbow and is as accurate at one. Size ranged from the smaller ones who is simply a crossbow to large to carry to giants who was mounted on a wagon. The one in Morrowind is large and would require a team to recharge after use.
The trebuchet is basically a giant counterweight catapult, because of the counterweight and the large size it has to be build in place, they was used under sieges to break down walls as you say.
Ballista was also used on battlefields, mostly to keep the enemy from lining up to close to you, if they had to start longer away you would be ready then they come and the archers could start firing at maximum range. used in castles of the same reason, very popular on ships as it could do damage at great range, killing or wounding rowers would reduce speed.