Star schema concept in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a star schema in data warehousing?

A star schema is a simple database design where a central fact table connects to multiple dimension tables, forming a star-like shape. It helps organize data for easy and fast analysis.

beginner

What is the role of the fact table in a star schema?

The fact table stores measurable, quantitative data like sales or revenue. It links to dimension tables using keys and contains numeric values used for calculations.

beginner

What are dimension tables in a star schema?

Dimension tables hold descriptive information like product names, dates, or customer details. They provide context to the numbers in the fact table.

beginner

Why is the star schema called 'star'?

Because the fact table is at the center and dimension tables spread out around it like points on a star, making the structure look like a star shape.

intermediate

How does a star schema improve query performance?

It simplifies joins by having one central fact table connected directly to dimension tables, reducing complexity and speeding up data retrieval.

In a star schema, what type of data does the fact table usually contain?

A User login details

B Descriptions like product names

C Quantitative measurements like sales amounts

D Database configuration settings

The fact table contains measurable data such as sales or revenue, which are quantitative.

Which of the following best describes dimension tables?

A Tables that provide descriptive context

B Tables that store numeric facts

C Tables that store system logs

D Tables that hold temporary data

Dimension tables provide descriptive information like product or customer details.

Why is the star schema design popular in BI systems?

A It uses many complex joins

B It simplifies queries and improves performance

C It stores unstructured data

D It requires no indexing

Star schema simplifies queries by having a central fact table connected to dimension tables, improving performance.

What shape does the star schema resemble?

A Circle

B Square

C Triangle

D Star

The schema looks like a star with the fact table in the center and dimension tables around it.

Which key connects the fact table to dimension tables?

A Foreign key in fact table

B No keys are used

C Primary key in dimension table only

D Primary key in fact table

The fact table contains foreign keys that link to primary keys in dimension tables.

Explain the structure and components of a star schema in simple terms.

Think about a central table with spokes connecting to descriptive tables.

Describe how a star schema helps improve data analysis and query speed.

Focus on how the design reduces complexity in queries.