DMC Physical Data Modeling 2

Question 1

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

Accepted Answer

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

Answer

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?

Accepted Answer

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

Answer

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?

Accepted Answer

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

Answer

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?

Accepted Answer

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

Answer

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?

Accepted Answer

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

Answer

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

DMC - Data Modeling Certification Practice Test

DMC - Data Modeling Certification Practice Test

DMC Physical Data Modeling 2