iPedia Free Information Center

Information about Star Schema

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

Example

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_store.country (and other store address components); d_date.year (and other date 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
d_product.brand, d_store.country, sum (f_sales.units_sold)
FROM
f_sales, d_date, d_store, d_product
WHERE
f_sales.date_id = d_date.id AND f_sales.store_id = d_store.id AND f_sales.product_id = d_product.id
AND d_date.year = 1997
AND d_product.category = 'tv'
GROUP BY
d_product.brand, d_store.country

See also

External links

A database model is a theory or specification describing how a database is structured and used. Several such models have been suggested.

Common models include:
  • Hierarchical model
  • Network model
  • Relational model
  • Entity-relationship

..... Click the link for more information.
In a hierarchical data model, data are organized into a tree-like structure. The structure allows repeating information using parent/child relationships: each parent can have many children but each child only has one parent.
..... Click the link for more information.
The network model is a database model conceived as a flexible way of representing objects and their relationships. Its original inventor was Charles Bachman, and it was developed into a standard specification published in 1969 by the CODASYL Consortium.
..... Click the link for more information.
The relational model for database management is a database model based on predicate logic and set theory. It was first formulated and proposed in 1969 by Edgar Codd with aims that included avoiding, without loss of completeness, the need to write computer programs to
..... Click the link for more information.
An object-relational database (ORD) or object-relational database management system (ORDBMS) is a relational database management system that allows developers to integrate the database with their own custom data types and methods.
..... Click the link for more information.
'' In an object oriented database, information is represented in the form of objects'' as used in Object-Oriented Programming. When database capabilities are combined with object programming language capabilities, the result is an object database management system (ODBMS).
..... Click the link for more information.
The associative model of data is an alternative data model for database systems. Other data models, such as the relational model and the object data model, are record-based. These models involve encompassing attributes about a thing, such as a car, in a record structure.
..... Click the link for more information.
The concept-oriented data model is a data model based on lattice theory and ordered sets. Another source of inspiration in creating this model is formal concept analysis (FCA).
..... Click the link for more information.
Multidimensional databases are variously (depending on the context) data aggregators which combine data from a multitude of data sources; databases which offer networks, hierarchies, arrays and other data formats difficult to model in SQL; or databases which give a high degree of
..... Click the link for more information.
XML-enabled. These map all XML to a traditional database (such as a relational database), accepting XML as input and rendering XML as output.
  • Native XML (NXD) The internal model of such databases depends on XML and uses XML documents as the fundamental unit of storage.
    ..... Click the link for more information.
    • A data warehouse is the main repository of an organization's historical data, its corporate memory. It contains the raw material for management's decision support system. The critical factor leading to the use of a data warehouse is that a data analyst can perform complex queries
    ..... Click the link for more information.
    In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is often located at the centre of a star schema, surrounded by dimension tables.
    ..... Click the link for more information.
    In relational database design, a unique key or primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns.
    ..... Click the link for more information.
    In relational database design, a unique key or primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns.
    ..... Click the link for more information.
    Second normal form (2NF) is a normal form used in database normalization. 2NF was originally defined by E.F. Codd[1] in 1971. A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form.
    ..... Click the link for more information.
    The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E.F. Codd[1] in 1971. Codd's definition states that a table is in 3NF if and only if both of the following conditions hold:

    ..... Click the link for more information.
    Multidimensional databases are variously (depending on the context) data aggregators which combine data from a multitude of data sources; databases which offer networks, hierarchies, arrays and other data formats difficult to model in SQL; or databases which give a high degree of
    ..... Click the link for more information.
    A relational database is a database that conforms to the relational model, and refers to a database's data and schema (the database's structure of how that data is arranged).
    ..... Click the link for more information.
    This article or section may be confusing or unclear for some readers.
    Please [improve the article] or discuss this issue on the talk page. This article has been tagged since December 2006.
    ..... Click the link for more information.
    This article or section may be confusing or unclear for some readers.
    Please [improve the article] or discuss this issue on the talk page. This article has been tagged since December 2006.
    ..... Click the link for more information.


    This article is copied from an article on Wikipedia.org - the free encyclopedia created and edited by online user community. The text was not checked or edited by anyone on our staff. Although the vast majority of the wikipedia encyclopedia articles provide accurate and timely information please do not assume the accuracy of any particular article. This article is distributed under the terms of GNU Free Documentation License.
    Herod_Archelaus


    page counter