Physical Data Modeling Flashcards

What is a materialized view and when is it typically used in physical design?

Answer: A precomputed, physically stored query result refreshed on a schedule to speed up complex queries

A materialized view stores the results of a query physically and refreshes them periodically, significantly speeding up expensive aggregations and joins in read-heavy environments.

Which physical design technique is used to handle very large binary objects like documents or images stored in a database?

Answer: BLOB (Binary Large Object) or CLOB (Character Large Object) data types with external storage pointers

BLOB and CLOB data types store large binary or text objects, and databases often support external file storage with pointers to keep table rows compact.

What is vertical partitioning (also called column splitting) in physical data modeling?

Answer: Splitting a wide table into two narrower tables sharing the same primary key, separating frequently and rarely accessed columns

Vertical partitioning splits a table's columns into two or more tables with the same primary key, improving performance by keeping frequently accessed columns together.

What is the purpose of a covering index?

Answer: To include all columns needed by a query in the index itself, avoiding a table row lookup

A covering index contains all columns needed by a query, allowing the database engine to satisfy the query entirely from the index without touching the base table rows.

In physical data modeling, what does 'fill factor' control in an index?

Answer: The percentage of each index page left free to accommodate future insertions and reduce page splits

Fill factor specifies the percentage of each index leaf page to fill during creation, leaving space for future inserts to reduce expensive page splits.

What physical design technique is most appropriate for implementing a slowly changing dimension (SCD) Type 2 in a data warehouse?

Answer: Add effective date and expiration date columns along with a current flag to track historical versions

SCD Type 2 preserves history by inserting a new row for each change, with effective_date, expiration_date, and is_current columns to track each version.