Logical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 119

5.2 Relational Data Warehouse Design

One possible relational representation of the multidimensional model is based
on the star schema , where there is one central fact table ,andasetof
dimension tables , one for each dimension. An example is given in Fig. 5.1 ,
where the fact table is depicted in gray and the dimension tables are depicted
in white. The fact table contains the foreign keys of the related dimension
tables, namely, ProductKey , StoreKey , PromotionKey ,and TimeKey ,andthe
measures, namely, Amount and Quantity . As shown in the figure, referential
integrity constraints are specified between the fact table and each of the
dimension tables.

Product Store

ProductKey
ProductNumber
ProductName
Description
Size
CategoryName
CategoryDescr
DepartmentName
DepartmentDescr
...

StoreKey
StoreNumber
StoreName
StoreAddress
ManagerName
CityName
CityPopulation
CityArea
StateName
StatePopulation
StateArea
StateMajorActivity
...

Sales

ProductKey
StoreKey
PromotionKey
TimeKey
Amount
Quantity

Promotion Time

PromotionKey
PromotionDescr
DiscountPerc
Type
StartDate
EndDate
...

TimeKey
Date
Event
WeekdayFlag
WeekendFlag
Season
...

Fig. 5.1 An example of a star schema

In a star schema, the dimension tables are, in general, not normalized.
Therefore, they may contain redundant data, especially in the presence
of hierarchies. This is the case for dimension Product in Fig. 5.1 since all
products belonging to the same category will have redundant information for
the attributes describing the category and the department. The same occurs
in dimension Store with the attributes describing the city and the state.
On the other hand, fact tables are usually normalized: their key is the union
of the foreign keys since this union functionally determines all the measures,
while there is no functional dependency between the foreign key attributes.
In Fig. 5.1 , the fact table Sales is normalized, and its key is composed by
ProductKey , StoreKey , PromotionKey ,and TimeKey .