Is a star schema a denormalized schema?
An OLAP database consists of data in denormalized form. This means data redundancy and this data redundancy helps retrieve data through less number of joins, hence facilitating faster retrieval.
But a popular design for OLAP database is fact-dimension model. Fact table will store numerical fact-based entries (# of Sales, etc.) while dimension tables will store "descriptive attributes" related to the fact, i.e. details of the customer to which the sale was made.
My question is, in this design, it does not seem denormalized at all, as all dimension tables will have foreign key references to the fact table. How is it different from an OLTP design?
Answer
The denormalization is in the dimension tables in a star schema: E. g. in a product table, you explicitly have many columns like several levels of product category in this one table, instead of having one table for each level, and using foreign keys referencing those values.
This means you have normalization with regard to facts, but stop normalizing on the dimension tables.
Furthermore, you often do not even completely normalize the facts. A typical example would be this: in a completely normalized table, you would use only two columns 'number of units sold' and 'price per unit', but in an OLAP database, it may make sense to redundantly have another column for the 'sales value' which could easily be calculated by multiplying units sold and the price per unit.