The translation of star schema into entity-relationship diagrams

Abstract

The star schema is widely accepted as an appropriate underlying table structure for data warehouses, but enterprise data is frequently defined in terms of entity-relationship diagrams. A key issue for data warehouse designers is how to move from legacy OLTP designs into the star schema. In this paper, we examine the semantics and constraints of the star schema in some of its different forms and translate them into analogous ERDs. These different forms include the simple, multi-star and snowflaked star schema, and several variations on facts and dimensions, such as multiple fact and outboard dimension tables. We outline transformation rules for each case and show a series of examples. These heuristics will be useful in mapping and maintaining consistency between OLTP databases and data warehouses.