BIROn - Birkbeck Institutional Research Online

Why is the snowflake schema a good data warehouse design?

Levene, Mark and Loizou, George (2003) Why is the snowflake schema a good data warehouse design? Information Systems 28 (3), pp. 225-240. ISSN 0306-4379.


Download (206kB) | Preview
Official URL:


Database design for data warehouses is based on the notion of the snowflake schema and its important special case, the star schema. The snowflake schema represents a dimensional model which is composed of a central fact table and a set of constituent dimension tables which can be further broken up into subdimension tables. We formalise the concept of a snowflake schema in terms of an acyclic database schema whose join tree satisfies certain structural properties. We then define a normal form for snowflake schemas which captures its intuitive meaning with respect to a set of functional and inclusion dependencies. We show that snowflake schemas in this normal form are independent as well as separable when the relation schemas are pairwise incomparable. This implies that relations in the data warehouse can be updated independently of each other as long as referential integrity is maintained. In addition, we show that a data warehouse in snowflake normal form can be queried by joining the relation over the fact table with the relations over its dimension and subdimension tables. We also examine an information-theoretic interpretation of the snowflake schema and show that the redundancy of the primary key of the fact table is zero.

Item Type: Article
Keyword(s) / Subject(s): Data warehouse design, Star and snowflake schema, Independent and separable database schema, Acyclic database schema
School or Research Centre: Birkbeck Schools and Research Centres > School of Business, Economics & Informatics > Computer Science and Information Systems
Depositing User: Administrator
Date Deposited: 10 Aug 2005
Last Modified: 11 Oct 2016 12:00

Archive Staff Only (login required)

Edit/View Item Edit/View Item