Modelling a STAR schema from ER model

An ER model is a two dimensional data model used in transactional databases. These models does not provide features required in a data warehouse. For this purpose star schema model is used. We can design a star schema model from a ER data model.

Few points that needs to be considered for converting an ER model to a star schema are as fallows:

• In an ER model, generally a FACT table is considered a table in ER model where there is maximum cardinality for all the relationships viz many in one to many or many to many relations.


• Dimensions are the tables with cardinality of 0 or 1.

A star schema can also be modeled from scratch using top down approach. Following are the steps to be followed for modeling a STAR schema:

• Identify the business process for analysis.


• Identify measures or facts.


• Identify dimensions for facts.


• Determine lowest grain i,e lowest level of cardinality for fact table.

In the next post we will see the concept of surrogate keys.