Learn the difference between star and snowflake schemas in database design. Discover how dimensions are represented in each schema type and their impact on query performance.
Table of Contents
Question
A database consists of one fact table that is composed of multiple dimensions. Depending on the dimension, each one can be represented by a denormalized table or multiple normalized tables. This structure is an example of a:
A. transactional schema.
B. star schema.
C. non-relational schema.
D. snowflake schema.
Answer
D. snowflake schema.
Explanation
The described database structure, where a fact table is surrounded by dimension tables that can be either denormalized or normalized, is an example of a snowflake schema.
In a snowflake schema, the dimensions are represented by a combination of denormalized and normalized tables. The normalized dimension tables are further broken down into multiple related tables, resembling the shape of a snowflake. This design allows for a more granular representation of the dimensions and reduces data redundancy.
On the other hand, a star schema (option B) consists of a fact table surrounded by denormalized dimension tables. Each dimension is represented by a single, denormalized table, resulting in a simpler structure that resembles a star.
A transactional schema (option A) is designed for efficient transaction processing and typically follows a highly normalized structure, which is not the case in the given scenario.
A non-relational schema (option C) refers to a schema used in non-relational databases, such as NoSQL databases, and does not apply to the described structure.
Therefore, the database structure described in the question is an example of a snowflake schema.
CompTIA DA0-001 certification exam practice question and answer (Q&A) dump with detail explanation and reference available free, helpful to pass the CompTIA DA0-001 exam and earn CompTIA DA0-001 certification.