Star and snowflake schemas
Two shapes of one dimensional model: the star, the snowflake and the constellation, surrogate keys, denormalisation, and the star join that answers a question.
- 22 cards
- 6 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
- Scenarios
- Understand
A few cards from the deck
Star schema: what is it?
Show the answer
An analytical model where a central table of measures is linked directly to several flat context tables, one per axis of analysis.
Any question needs at most one join per axis: product, store, date, customer. The drawing looks like a star with the table of measures at the centre.
With these tables, which row does the query return?
```sql
-- produit(1, Stylo, Papeterie), (2, Cahier, Papeterie), (3, Sac, Bagagerie)
-- ventes(produit_id, montant) : (1, 10), (1, 20), (2, 30), (3, 40), (3, 50)
SELECT p.categorie, SUM(v.montant) AS ca
FROM ventes v
JOIN produit p ON p.produit_id = v.produit_id
GROUP BY p.categorie;`Papeterie | 60Papeterie | 30Papeterie | 100Bagagerie | 60
Show the answer
Papeterie | 60The sales of Stylo (10, 20) and Cahier (30) are in the same category: 60. Bagagerie gives 90. The join gives each sale its product's category before the grouping.
In a star query, the fact table is joined to each dimension on its …, and the requested attributes go into the ….
Show the answer
In a star query, the fact table is joined to each dimension on its surrogate key, and the requested attributes go into the GROUP BY.
Each dimension the question needs adds one join. A dimension of which no attribute is asked for is not joined.
Snowflake schema: what is it?
Show the answer
A variant of the star where the context tables are normalised into sub-tables: store, then city, then region, then country.
A label is written only once, but a question that goes up to the country crosses the whole chain of joins.