Schema

     Star Schema:

A star schema is a logical database design which contains a centrally located fact table surrounded by at least one or more dimension tables.

 A Fact table contains composite keys (More than one key) where each candidate key is a foreign key to the dimension table.

The facts that the data warehouse helps analyze are classified along different dimensions:

·         The fact table holds the main data. It includes a large amount of aggregated data, such as price and units sold. There may be multiple fact tables in a star schema.

·         Dimension tables, which are usually smaller than fact tables, include the attributes that describe the facts. Often this is a separate table for each dimension. Dimension tables can be joined to the fact table(s) as needed.

·         Dimension tables have a simple primary key, while fact tables have a set of foreign keys which make up a compound primary key consisting of a combination of relevant dimension keys.

 Example: Fact.Sales is the fact table and there are three dimension tables Dim.Date, Dim.Store and Dim.Product. Each dimension table has a primary key on its PK column, relating to one of the columns (viewed as rows in the example schema) of the Fact.Sales table's three-column (compound) primary key (Date_FK, Store_FK, Product_FK). The non-primary key [Units Sold] column of the fact table in this example represents a measure or metric that can be used in calculations and analysis. The non-primary key columns of the dimension tables represent additional attributes of the dimensions (such as the Year of the Dim.Date dimension).

1.       Snow Flake Schema:

 In a Star schema database design, if the dimension table is split into a one or more dimension tables which results in Normalization. Since the database design looks like a snow flake. Hence it is known as Snow flake schema.

  Generally these types of schema designs are not recommended for the warehouse implementations because dimension tables results in Normalization and decrease the performances.

                  The snowflake schema is similar to the star schema. However, in the snowflake schema,
                   dimensions are normalized into multiple related tables, whereas the star schema's dimensions are
                   denormalized with each dimension represented by a single table

·         The advantages and disadvantages of snow flake schema are given below.

1.       Galaxy Schema:

  Sophisticated applications may require multiple fact tables to share dimension tables. This kind of schema 
  can be viewed as a collection of stars, and therefore it's called a Galaxy Schema or a Fact Constellation. As
  we see in the two star schemas as above, the two fact tables, sales table and purchase table are now
  sharing both the 'product' and 'time' dimension tables. Therefore we decide to choose Galaxy Schema as
  the model for our data warehouse, which is displayed as follow:

   Before applying Galaxy schema:

  After applying Galaxy schema: