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

SELECT CategoryName, SUM(Amount)
FROM Product P, Sales S
WHERE P.ProductKey = S.ProductKey
GROUP BY CategoryName

while in the snowflake schema in Fig. 5.2 , we need an extra join, as follows:
SELECT CategoryName, SUM(Amount)
FROM Product P, Category C, Sales S
WHERE P.ProductKey = S.ProductKey AND P.CategoryKey = C.CategoryKey
GROUP BY CategoryName
A starflake schema is a combination of the star and the snowflake
schemas, where some dimensions are normalized while others are not. We
would have a starflake schema if we replace the tables Product , Category ,and
Department in Fig. 5.2 , by the dimension table Product of Fig. 5.1 , and leave
all other tables in Fig. 5.2 (like dimension table Store ) unchanged.

Promotion Sales Store

PromotionKey
PromotionDescr
DiscountPerc
Type
StartDate
EndDate
...

ProductKey
StoreKey
PromotionKey
TimeKey
Amount
Quantity

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

Time

Product

TimeKey
Date
Event
WeekdayFlag
WeekendFlag
Season
...

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

Purchases Supplier

ProductKey
SupplierKey
OrderTimeKey
DueTimeKey
Amount
Quantity
FreightCost

SupplierKey
SupplierName
ContactPerson
SupplierAddress
CityName
StateName
...

Fig. 5.3 An example of a constellation schema

Finally, a constellation schema has multiple fact tables that share
dimension tables. The example given in Fig. 5.3 has two fact tables Sales and