Sitecore is structured to use, at a minimum, 3 databases. However, implementations can be as large and varied as required. The typical databases are as follows:
Master
The Master database is the authoring database - it contains all versions of any content or assets.
Core
The Core database is all Sitecore settings, as well as the tables containing the .Net membership provider (i.e. users/roles contained in the Sitecore repository)
Web
The Web database is only the latest published version, and the content that is driving the live web site. Therefore it is a subset of the master database, optimised for size and speed. When content is published, or goes through the publishing task of a workflow, the latest content version is copied from the master to web database.
Of course, in an Enterprise deployment, you may have multiple Sitecore instances, which may share DBs (or not)
Simple multi-instance architecture example
In this example, the Content Management (CM) instance is behind the firewall. Since the site is fairly simple (no user logins), only the Web database is published to the Content Deliver (CD) live site server.
Complex multi-instance Architecture example
In this example, the clustered (or failover) Content Management (CM) instances are behind the firewall. Since the site does have a login, the Core database is required, along with the Web database is published to the Content Deliver (CD) live site server.
DMS
Adding DMS functionality requires the addition of one more database - the Analytics DB.
Documentation
There are a number of documents on SDN specific to architectural setup and optimization.
Advanced material