You don't seem too confused. None of Disney excursions include access to the waterpark at Atlantis. The Discover Atlantis one includes time at 'a beach' which is not the same thing. Atlantis has a big water slide, a tube slide, a lazy rizer and a number of internal pools. The only way to get full access to the waterpark stuff is to book a room at Atlantis or the Comfort Suites which is right next door.
The excursion does have some downsides. You get shuttled over in a Water Taxi that they will fill as full as they are able to do. It also drops you off a block or more from the resort, which is a downside for the return trip. The tour itself also moves at a very brisk clip. It's more intended as a teaser than anything else. We're not that slow, the last time we visited we spent 3 hours there, and, no joke, 3 tours passed us up. You are allowed to 'explore at your leisure' after the tour ends, but after all the rushing, not many have an inclination to rewalk the tour. The upside is the tour is cheaper than doing it yourself. Not a lot cheaper, but it is.
To do it yourself all you have to do is leave the ship, walk to the end of the dock and get a minivan taxi that'll cost you(prices do fluctuate-always ask upfront) approx $4 per person to get there, they'll drop you right at the front door. When you get in, you can get the same wristbands as the tour for $29 each for Adults, $20 Kids 4-12, under 4 free, and you can now explore the resort at your pace. You will be given a map of the resort that you can follow the route the tour does and see all the aquariums.
As far as the room, I'm not absolutely clear on this anymore, you might be best to book Comfort Suites in advance, if you want to use the waterpark at Atlantis. Atlantis you can usually book a room that day but it is expensive. Atlantis used to have this thing called a 'day room' that were first come, first served at a discount by just showing up there, but don't know if that is still in effect.