DBMS Schemas for Decision Support
Decision-support databases are deliberately denormalized for fast, simple read queries. The three classic schemas are the star schema, the snowflake schema and the fact constellation.
"Draw the star and snowflake schema for a retail sales warehouse and compare them" is a very frequent 10-mark question. Also learn SCD types 1, 2, 3 with example rows.
1. Star Schema
One central fact table (numeric measures plus foreign keys) surrounded by denormalized dimension tables.
CREATE TABLE FACT_SALES (
date_key INT, prod_key INT, store_key INT, cust_key INT,
qty INT, amount DECIMAL(12,2), discount DECIMAL(5,2),
FOREIGN KEY (date_key) REFERENCES DIM_DATE(date_key),
FOREIGN KEY (prod_key) REFERENCES DIM_PRODUCT(prod_key),
FOREIGN KEY (store_key) REFERENCES DIM_STORE(store_key),
FOREIGN KEY (cust_key) REFERENCES DIM_CUSTOMER(cust_key));
Properties
- Fact columns are numeric, additive measures; the grain is explicit (one row = one product sold in one store on one day to one customer).
- Dimensions are wide and flat (few tables, many columns), so queries need few joins.
- Redundancy is accepted in exchange for query speed.
2. Snowflake Schema
Dimensions are normalized into hierarchies, so the dimension tables branch out like a snowflake.
3. Fact Constellation (Galaxy) Schema
Several fact tables share dimension tables. It is used for a full enterprise warehouse.
4. Comparison
| Criterion | Star | Snowflake | Constellation |
|---|---|---|---|
| Dimension form | Denormalized | Normalized | Shared dimensions, many facts |
| Joins | Few | Many | Many |
| Query performance | Best | Slower | Medium |
| Storage | Higher | Lower | Medium |
| ETL complexity | Simple | More complex | Highest |
| Ease of use | Easy | Confusing | Complex |
| Typical use | Data mart | Large DW with many hierarchies | Enterprise DW |
5. Grain, Dimensionality and Cube Explosion
For dimensions with members each:
For and , . For , . Most cells are empty (sparse), so store only non-empty cells and use pre-aggregation and materialized views.
6. Slowly Changing Dimensions (SCD)
| Type | Technique | History kept |
|---|---|---|
| Type 1 | Overwrite the old value | None |
| Type 2 | Insert a new row with a new surrogate key and valid_from, valid_to, is_current |
Full |
| Type 3 | Add a previous_value column |
Partial (one level) |
Example: a customer moves from Delhi to Mumbai.
| Type | Result |
|---|---|
| SCD-1 | city = Mumbai (the old value is lost) |
| SCD-2 | Row (101, Delhi, 2019-01-01, 2025-03-31, N) and row (108, Mumbai, 2025-04-01, NULL, Y) |
| SCD-3 | city_current = Mumbai, city_prev = Delhi |
7. Additivity of Measures
| Kind | Meaning | Example |
|---|---|---|
| Additive | Can be summed across every dimension | Sales amount |
| Semi-additive | Summable across some dimensions only | Account balance: across branches, not across time |
| Non-additive | Cannot be summed | Ratios, percentages, averages, unit price |
- Draw star and snowflake schemas for retail sales and compare them (10 marks, very frequent).
- Explain SCD types 1, 2, 3 with example rows (5 marks).
- Compute the number of cuboids or cells (2-3 marks numerical).
Frequently Asked Questions
QWhat is a fact table and a dimension table?
A fact table stores numeric measures (quantity, amount) and foreign keys to dimensions. A dimension table stores descriptive attributes (product name, city, month) used to slice the facts.
QWhat is the difference between star and snowflake schema?
A star schema keeps denormalized dimensions (fewer joins, faster, more storage). A snowflake normalizes the dimensions into sub-tables (less storage, more joins, slower).
QWhat is a fact constellation?
A schema with multiple fact tables sharing dimensions, also called a galaxy schema.
QWhat is a surrogate key and why is it used?
A system-generated integer key for a dimension row. It isolates the warehouse from source key changes and makes SCD type 2 possible.
QExplain additive, semi-additive and non-additive facts.
Additive facts can be summed across all dimensions, semi-additive across only some (balance not across time), and non-additive facts such as ratios can never be summed.
QHow many cuboids does an n-dimensional cube have?
when no hierarchies are used.