The star schema (sometimes referenced as star join schema) is the simplest data warehouse schema where a single "fact table" containing a compound primary key, with one segment for each "dimension" and with additional columns of additive, numeric facts.

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 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.

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

Equivalent ANSI SQL-92 Example

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

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

See also

• Snowflake schema

External Links

http://c2.com/cgi/wiki?StarSchema