Star schema

From Wikipedia, the free encyclopedia

Database models
Common models
Hierarchical Network Relational Object-relational Object
Other models
Associative Concept-oriented Multi-dimensional Star schema XML database

The star schema (sometimes referenced as star join schema) is the simplest data warehouse schema, consisting of a single "fact table" with a compound primary key, with one segment for each "dimension" and with additional columns of additive, numeric facts. The name star schema is derived from the fact that the schema diagram is shaped like a star.

The star schema makes multi-dimensional database (MDDB) functionality possible using a traditional relational database. Because relational databases are the most common data management system in organizations today, implementing multi-dimensional views of data using a relational database is very appealing. Even if you are using a specific MDDB solution, its sources likely are relational databases. Another reason for using star schema is its ease of understanding. Fact tables in star schema are mostly in third normal form (3NF), but dimensional tables are in de-normalized second normal form (2NF). If you want to normalize dimensional tables, they look like snowflakes (see snowflake schema) and the same problems of relational databases arise - you need complex queries and business users cannot easily understand the meaning of data. Although query performance may be improved by advanced DBMS technology and hardware, highly normalized tables make reporting difficult and applications complex.

1 Example SQL
2 Equivalent ANSI SQL-92 Examples
3 Alternate ANSI SQL-92 Example
4 See also
5 External links

[edit] Example SQL

SELECT
 sum (f_sales.units_sold)
FROM
 f_sales, d_customer, d_time, d_store, d_product
WHERE
 f_sales.customer_id   = d_customer.customer_id AND
 f_sales.date_id       = d_time.date_id AND
 f_sales.store_id      = d_store.store_id AND
 f_sales.product_id    = d_product.product_id AND
 d_time.year_id        = 1997 AND
 d_product.category_id = "tv"
GROUP BY
 d_product.brand, d_store.country_iso_id

[edit] Equivalent ANSI SQL-92 Examples

SELECT
      sum (f_sales.units_sold)
FROM
      f_sales
INNER JOIN d_customer ON d_customer.customer_id = f_sales.customer_id
INNER JOIN d_time     ON d_time.date_id         = f_sales.date_id
INNER JOIN d_store    ON d_store.store_id       = f_sales.store_id
INNER JOIN d_product  ON d_product.product_id   = f_sales.product_id
WHERE
      d_time.year_id        = 1997
  AND d_product.category_id = "tv"
GROUP BY
      d_product.brand, 
      d_store.country_iso_id

SELECT
      sum (f_sales.units_sold)
FROM
      f_sales
INNER JOIN d_customer USING (customer_id)
INNER JOIN d_time     USING (date_id)
INNER JOIN d_store    USING (store_id)
INNER JOIN d_product  USING (product_id)
WHERE
      d_time.year_id        = 1997
  AND d_product.category_id = "tv"
GROUP BY
      d_product.brand, 
      d_store.country_iso_id

[edit] Alternate ANSI SQL-92 Example

SELECT
 sum (f_sales.units_sold)
FROM
 f_sales
NATURAL JOIN 
 d_customer
NATURAL JOIN
 d_time
NATURAL JOIN
 d_store
NATURAL JOIN
 d_product
WHERE
 d_time.year_id = 1997 AND
 d_product.category_id = "tv"
GROUP BY
 d_product.brand, d_store.country_iso_id