Star and snowflake schema

Conception de la base de données

Lis Sulmont

Curriculum Manager

Star schema

Dimensional modeling: star schema

Fact tables

Holds records of a metric
Changes regularly
Connects to dimensions via foreign keys

Dimension tables

Holds descriptions of attributes
Does not change as often

Example:

Supply books to stores in USA and Canada
Keep track of book sales

Star schema example

Snowflake schema (an extension)

Snowflake schema diagram

Same fact table, different dimensions

$$

Star schemas: one dimension

$$

Snowflake schema diagram

Snowflake schemas: more than one dimension

Because dimension tables are normalized

What is normalization?

Database design technique
Divides tables into smaller tables and connects them via relationships
Goal: reduce redundancy and increase data integrity

What is normalization?

Database design technique
Divides tables into smaller tables and connects them via relationships
Goal: reduce redundancy and increase data integrity

Identify repeating groups of data and create new tables for them

Book dimension of the star schema

$$ Book dimension of the star schema

Most likely to have repeating values:

Author
Publisher
Genre

Book dimension of the snowflake schema

Book dimension of the snowflake schema

Store dimension of the star schema

$$ Store dimension of the star schema

City
State
Country

Store dimension of the snowflake schema

Store dimension of the snowflake schema

Book dimension of the snowflake schema

$$ $$ $$ $$ $$ Time dimension of the star schema

$$ Time dimension of the snowflake schema

$$

Snowflake schema

Let's practice!

Conception de la base de données

Preparing Video For Download...