I was in your shoes a few months ago when I first started messing around with Sugar CE for a real project.
Got no help. I was a bit surprised given the open source nature of the software, but I guess its because a lot of the developers here are so use to the paradigm that Sugar is business software, so unless a business wants to shell out big $$$ for a custom theme, themes are not worth it. Maybe I'm wrong, but if you search around you will find many, many threads looking for template help that have gone unanswered.
Anyways, I'll try and help where I can.
First off, Modern Aqua is an old theme and, in my opinion, not worth trying to make work with v6.+. I think it was a great theme in its day, but you will be better off making your own from scratch, as its not that difficult (if you really, really must have modern aqua, all you need to do is change the themedef file to include version 6 and upload it manually).
Sugar basically uses smarty templates, with seemingly random surprises for you to find once you get into editing things.
First, you typically install themes like you do any other module--by uploading a zip file using module loader. But you don't have to. You can easily just upload a new folder to your /themes/ directory. In that folder you must have a themedef.php file with the following content:
then css, images, js, and tpls folders...just like the "Classic" theme.
$themedef = array(
'name' => "New Theme",
'description' => "My New Theme Description",
'version' => array(
'regex_matches' => array('6\.*.*'),
'group_tabs' => true,
In fact, you should just duplicate the Classic theme and go from there. Modify the tpl files and CSS, and thats all you need. That documentation link you included has a good image of the basic theme compartments, which correspond to the tpl files nicely (but almost everything else in there is too much info. you wont use for your first theme).
Just start modifying those and you'll be able to get a pretty good start.
If you have any specific questions along the way, I'll try to answer them (but I'm no expert by any means). I have developed my own theme that feels much more modern than the classic theme, but isn't drastically different layout wise. I'll share it with you if you want.