What is Star Schema?

Data MiningDatabaseData Structure

A star schema is a conference for constructing the data into dimension tables, fact tables, and materialized views. All data is saved in columns, and metadata is needed to identify the columns that function as multidimensional objects.

A star schema is a relational schema where a relational schema whose design defines a multidimensional data model. The star schema is the explicit data warehouse schema. It is referred to as star schema because the entity-relationship diagram of this schemas reproduces a star, with points, diverge from the main table. The middle of the schema includes a high fact table, and the star is the dimension table.

Dimension Tables − A star schema saves all of the data about a dimension in a single table. Each level of a hierarchy is defined by a column or column set in the dimension table. A dimension object can be used to describe the hierarchical relationship between two columns (or column sets) that defines two levels of a hierarchy; without a dimension object, the hierarchical relationships are represented only in metadata. Attributes are saved in columns of the dimension tables.

Fact Tables − Measures are saved in fact tables. Fact tables include a composite primary key, which is composed of multiple foreign keys (one for each dimension table) and a column for each measure that uses these dimensions.

Materialized Views − Aggregate data is computed based on the hierarchical relationships represented in the dimension tables. These aggregates are saved in independent tables, known as summary tables or materialised views. Oracle offers extensive support for materialised views, containing automatic refresh and query rewrite.

Queries can be written either opposite to a fact table or opposite to a materialized view. If a query is written against the fact table that needed aggregate data for its result set, the query is either redirected by query rewrite to a current materialized view, or the data is aggregated on the circle.

Features of Star Schema

There are the following features of star schema which are as follows −

  • It is used to make a DE-normalized database that can quickly support query responses.

  • It supports a flexible design that can be changed simply or inserted throughout the development cycle, and as the database increases.

  • It supports a parallel in design to how end-users generally think of and use the information.

  • It can decrease the difficulty of metadata for both developers and end-users.

Updated on 23-Nov-2021 10:19:30