Where do SQL Server databases fit into the SharePoint architecture? Here's my non-expert understanding:
A SharePoint instance (aka a SharePoint farm) has one or more Web Apps.
A Web App has one or more Content Databases, where a Content Database is a single database in SQL Server. Each Web App has its own IIS application pool.
A Content Database has one or more Site Collections.
A Site Collection has one or more Sites.
A Site has zero to many Sub-Sites and one or more Web Parts. A Sub-Site is a regular Site that is hierachally below another Site or Sub-Site.
When a SharePoint instance is growing large, you can break it into multiple databases with the most granular level possible being that of Site Collections. In other words, each Site Collection can have its own database, but Site and Sub-Sites cannot have their own databases.
When a single Site Collection grows so large that the manageability of the databases becomes an issue, you can redesign a Site Collection by essentially making each Site into a Site Collection (with its own database) and using "pointers" (managed paths) to make a set of Site Collections appear to the users as if it is a single Site Collection with multiple Sites.
This is definitely off-topic, but I'm putting it here so I can easily find it again. Thanks to Shawn Roehrig for the lesson!