These Entity Relation Diagrams for version 2.0 were created following Ben Vautier's work on the 1.x series and have now been updated for version 2.0. The ERD for 1.7 can be found here.
It is worth noting that some tables have a 'entity_table' and a 'entity_id' field, such as the 'civicrm_location' table. This allows multiple tables to have a relationship with 'civirm_location'. The 'entity_table' will store the name of the table name, such as 'civicrm_contact' and the 'entity_id' field stores the 'id' of the entity contained in the referenced 'entity table'.
The picture below focuses on the "group" tables. The "civicrm_contact" table is missing because it would be too messy to include it. However you can see it's relationship in the picture above. The "civicrm_group_contact" is a child of "civicrm_contact".
User Framework Tables
This is a picture of the relationship between the civicrm_uf_* tables. The "uf" letters stand for "User Framework". Some of these tables are used to store CiviCRM Profiles and their Profile fields. The civicrm_uf_match table is important as it stores the information that matches the contact_id with the user id (or uf_id) in Drupal or Joomla.
Mailing List Tables
This ERD is contributed by Henry Bennett. Original text by Ben Vautier.