ADE Data Modeling & Schema Design

Question 1

In a star schema, what is the primary role of a fact table?

Accepted Answer

Store measurable, quantitative data about business events

Answer

Store descriptive attributes about business entities

Answer

Define relationships between dimension tables

Answer

Maintain historical records of slowly changing data

Question 2

Which normal form eliminates transitive dependencies in a relational database?

Accepted Answer

Third Normal Form (3NF)

Answer

First Normal Form (1NF)

Answer

Second Normal Form (2NF)

Answer

Boyce-Codd Normal Form (BCNF)

Question 3

What distinguishes a snowflake schema from a star schema?

Accepted Answer

Snowflake schemas normalize dimension tables into sub-dimensions

Answer

Snowflake schemas have no fact tables

Answer

Snowflake schemas store data in columnar format

Answer

Snowflake schemas use only surrogate keys

Question 4

A surrogate key in data modeling is best described as:

Accepted Answer

A system-generated unique identifier with no business meaning

Answer

A natural business key derived from source systems

Answer

A composite key made from multiple natural keys

Answer

A foreign key that references a dimension table

Question 5

Slowly Changing Dimension (SCD) Type 2 handles attribute changes by:

Accepted Answer

Creating a new row with the updated value and tracking effective dates

Answer

Overwriting the old value with the new value

Answer

Storing old and new values in separate columns on the same row

Answer

Ignoring the change and keeping the original value

Question 6

What is denormalization and when is it typically applied?

Accepted Answer

The process of combining normalized tables to reduce joins, applied in OLAP/analytical systems

Answer

The process of splitting tables to reduce redundancy, applied during data ingestion

Answer

The process of removing duplicate rows, applied during ETL cleaning

Answer

The process of adding indexes to tables, applied to speed up queries

Question 7

In dimensional modeling, a 'conformed dimension' is one that:

Accepted Answer

Is shared and has consistent meaning across multiple fact tables or data marts

Answer

Has only one row per dimension member

Answer

Contains only current records with no historical versions

Answer

Uses natural keys instead of surrogate keys