ER Model for Star Schema - Part 2

In last blog I introduced a new construct called dimension (Dm). In this post I will go in detail of it.

I would first explain some existing constructs available and why we can't use them.

1.) Aggregation: An aggregate entity instance is a collection of entities from one or more entity class.

For ex: Attendants, residents and nurses can be combined to form aggregate entity called Medical Team.

We can't use this approach to model star or snow-flake schema because fact table is not collection of one or more dimensional class.

2.) Composite Class: A composite entity instance is composed of a set of members from another entity class.

For ex: combination of different types of employees form employee type.

We can't use it because dimension table is not specialization of fact table. 

3.) Typing and Instantiation Classes: This is generally used when we have to model instances of an entity type.

So, as we can see there is no existing construct which can help us model ER Diagram efficiently and so we need a new construct.

Now, let's have a look at an example of star schema modeled using Dimensional (Dm) approach.

If we create ER Model for it, district and region will be joined to market using Dm (Dimension) construct. Fact table will be modeled as weak entity. Following is the ER diagram for it:

If required we can show cardinality in the ER-Digaram.

Degenerate Dimensions: We can model degenerate dimension as a normal entity with only primary key. As, fact table is weak entity so the primary key of degenerate dimension becomes fact table's foreign key.

References: 

"The Relation of Star Schema into Entity-Relation Diagrams" by Dr. Krippendorf and Dr. Song

ER Diagram Slides by Dr. Currim