Star schema
From Wikipedia, the free encyclopedia
| Database model |
|---|
| Common models |
| Other models |
|
Associative |
The star schema (sometimes referenced as star join schema) is the simplest style of data warehouse schema, consisting of a few "fact tables" (possibly only one, justifying the name) referencing any number of "dimension tables". The "facts" that the data warehouse helps analyze are classified along different "dimensions": the fact tables hold the main data, while the usually smaller dimension tables describe each value of a dimension and can be joined to fact tables as needed.
Dimension tables have a simple primary key, while fact tables have a compound primary key consisting of the aggregate of relevant dimension keys.
It is common for dimension tables to consolidate redundant data and be in second normal form, while fact tables are usually in third normal form because all data depend on either one dimension or all of them, not on combinations of a few dimensions.
The star schema is a way to implement multi-dimensional database (MDDB) functionality using a mainstream relational database: given the typical commitment to relational databases of most organizations, a specialized multidimensional DBMS is likely to be both expensive and inconvenient.
Another reason for using a star schema is its simplicity from the users' point of view: queries are never complex because the only joins and conditions involve a fact table and a single level of dimension tables, without the indirect dependencies to other tables that are possible in a better normalized snowflake schema.
[edit] Example
Consider a database of sales, perhaps from a store chain, classified by date, store and product.
f_sales is the fact table and there are three dimension tables d_date, d_store and d_product.
Each dimension table has a primary key called id, corresponding to a three-column primary key (date_id, store_id, product_id) in f_sales.
Data columns include f_sales.units_sold (and sale price, discounts etc.); d_date.year (and other date components); d_store.country (and other store address components); d_product.category and d_product.brand (and product name etc.).
The following query extracts how many TV sets have been sold, for each brand and country, in 1997.
SELECT P.brand, S.country, sum (FS.units_sold) FROM f_sales FS INNER JOIN d_date D ON D.id = FS.date_id INNER JOIN d_store S ON S.id = FS.store_id INNER JOIN d_product P ON P.id = FS.product_id WHERE D.year = 1997 AND P.category = 'tv' GROUP BY P.brand, S.country
