I think there are two questions here. First, how does a single theme/template get rendered/used/displayed and second, how does the whole site come together. I think the second question has already been answered above, so I'm going to try to explain the first a bit more.
First, a module (that's why system.module exists, for all these stuff that only a module can do like implementing hook_menu()) needs to define that a specific theme function/template exists, by declaring it in hook_theme()
Speaking of that, there are two different things which can be used. A theme function, which is a function prefixed with theme_. Often used for smaller elements of a page wich have more complex logic/PHP like theme_table(). And a template, which is a file with the tpl.php like page.tpl.php
To use a theme function/template, you can either call theme() like this:
$output = theme('table', array('rows' => $rows, 'header' => $header));
Or you can use the new, so called renderable array thing. That is basically an array of data + information which theme to use:
$output = array(
'#theme' => 'table',
'#rows' => $rows,
'#header' => $header,
);
The second is preferred because it means that it will be themed as late as possible and other modules can change it in hooks. The result will be exactly the same, drupal_render() will then call theme() itself during the final rendering.
When theme() is called, it looks up the function/file to use, looks if it has been overriden the used theme, if there are so called template suggestions and then uses them.
To override a theme function in a theme, copy it to your template.php file and replace "theme_" with "yourthemename_", if it is a tpl.php file, copy it to your directory.
Now, what the final rendering process is doing is basically building up a large $page array, that is a renderable array (Some documentation of that is in hook_page_alter() and then call drupal_render() on it.
The global structure of the page/template hierarchy (which is not hardcoded, but given through whatever is in $page) is something like this:
html.php.tpl
head.php.tpl
page.php.tpl
multiple regions
multiple blocks
Almost everything is a block in Drupal 7, including the actual content, which is typically a node. A theme defines which regions it has and then you can assign blocks to it in the UI.
Best Answer
Make sure to clear your cache in Drupal after major theme changes which include adding files to the theme as above, otherwise your theme may not pick up the new files.
Last I checked, the Devel module was still buggy for D7, but if it has come along then I highly recommend using this along with the devel_themer module, it has a tool that will tell you what exactly to name your theme files for different elements (pages, nodes, fields, etc).