DMC Physical Data Modeling

Question 1

What is the primary purpose of a physical data model?

Accepted Answer

To specify how data will be stored in a specific DBMS including tables, columns, indexes, and constraints

Answer

A physical data model specifies the actual implementation details — table names, column data types, indexes, partitioning, and storage parameters — for a specific target DBMS.

Question 2

When transitioning from a logical to a physical data model, what typically changes?

Accepted Answer

Entity names become table names, attributes become columns, and DBMS-specific data types are assigned

Answer

Moving to physical design involves renaming entities as tables, mapping attributes to typed columns, resolving relationships into foreign keys, and adding indexes and constraints.

Question 3

What is a clustered index and how does it differ from a non-clustered index?

Accepted Answer

Clustered physically orders table rows by the index key; non-clustered creates a separate pointer structure

Answer

A clustered index determines the physical sort order of rows in the table itself, while a non-clustered index maintains a separate structure with pointers back to table rows.

Question 4

What is table partitioning in physical data modeling?

Accepted Answer

Dividing a large table's rows into segments based on a partition key to improve performance and manageability

Answer

Table partitioning divides a large table into smaller, manageable segments (partitions) based on a key such as date range or region, improving query performance and data maintenance.

Question 5

In physical data design, what is the purpose of a column constraint CHECK?

Accepted Answer

To restrict the range or set of valid values that can be inserted into a column

Answer

A CHECK constraint enforces a condition on column values at the database level, rejecting any insert or update that violates the rule.

DMC - Data Modeling Certification Practice Test

DMC - Data Modeling Certification Practice Test

DMC Physical Data Modeling