ON any ship being more towards the bow or stern and on a higher deck will always have more motion than being on a low deck midship. BUT the motion is not "choppy" it is more of a rocking, though those that truly suffer from seasickness (not generalized motion sickness) this can still be a problem. So if available pick a cabin closer to the midship area, if the idea of these cabins appeal to you.
AS for noise from the water slides I can't absolutely state there will be no sound, but the water slides only operate from about 10am till 6pm. So unless you take afternoon naps there shouldn't be too much of a problem with sound interrupting sleep. The biggest cabins that experience sound, as noted by a previous poster, are the ones directly below the gym and class areas. The spa opens the gym section at 6am on most ships and the use of heavy weighted equipment can be heard.
There is no age restrictions for these cabins, just for the use of the Cloud 9 Spa pass and other included spa amenities, but you might get the "stink-eye" from other passengers in this area if the kids are noisy. Many that pick this area because the passways ares known to be very quiet, but if the kids are the reserved type they many not even notice that there are children with you. As noted
As an alternative you can also buy the Cloud 9 spa pass (either for the entire cruise or on a daily basis) and stay in a cabin in any location. You do lose amenities like the in cabin lones (extra trial sized spa toiletries, the slippers, and special on board use robe), the 2 free exercise classes and the body index analysis. SO compare the price of your prefered cabin location plus the price of the separate pass (found in the carnival.com FAQs) to see if it may economical sense.
