DMC Physical Data Modeling 2 — Questions and Answers
Question 1: What is a materialized view and when is it typically used in physical design?
- A view that enforces row-level security
- A precomputed, physically stored query result refreshed on a schedule to speed up complex queries (Correct answer)
- A view that spans multiple databases
- A temporary table used during ETL processing
Correct 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.
Question 2: Which physical design technique is used to handle very large binary objects like documents or images stored in a database?
- Column encryption
- BLOB (Binary Large Object) or CLOB (Character Large Object) data types with external storage pointers (Correct answer)
- Row-level compression
- Vertical partitioning
Correct 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.
Question 3: What is vertical partitioning (also called column splitting) in physical data modeling?
- Partitioning rows by date range across multiple tables
- Splitting a wide table into two narrower tables sharing the same primary key, separating frequently and rarely accessed columns (Correct answer)
- Dividing a table by business unit
- Creating column-level access controls
Correct 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.
Question 4: What is the purpose of a covering index?
- To enforce NOT NULL on all columns
- To include all columns needed by a query in the index itself, avoiding a table row lookup (Correct answer)
- To cover foreign key relationships automatically
- To span multiple tables in a join
Correct 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.
Question 5: In physical data modeling, what does 'fill factor' control in an index?
- How many rows fit in a single data page
- The percentage of each index page left free to accommodate future insertions and reduce page splits (Correct answer)
- The compression ratio of index storage
- The maximum number of columns in a composite index
Correct 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.
Question 6: What physical design technique is most appropriate for implementing a slowly changing dimension (SCD) Type 2 in a data warehouse?
- Overwrite the existing row with new values
- Add effective date and expiration date columns along with a current flag to track historical versions (Correct answer)
- Delete old rows and insert new ones
- Use a separate audit table for all changes
Correct 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.
DMC Physical Data Modeling 2
What is a materialized view and when is it typically used in physical design?