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

To communicate business concepts to stakeholders

Answer

To document entity-relationship diagrams independent of technology

Answer

To define ETL pipeline scheduling

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

Business rules are removed

Answer

Relationships are eliminated

Answer

Normalization is reversed entirely

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

Clustered stores data in a separate structure; non-clustered stores data inline with the table

Answer

Clustered indexes can only be on primary keys; non-clustered cannot be on primary keys

Answer

Clustered applies to views; non-clustered applies to tables

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

Splitting a table's columns into multiple tables

Answer

Creating read-only replicas of a table

Answer

Encrypting specific columns for security

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

To verify the column exists in the schema

Answer

To create a unique index on the column

Answer

To cascade deletes to related tables

Question 6

What does 'data type alignment' mean in physical data modeling?

Accepted Answer

Choosing DBMS data types that accurately represent the logical domain while optimizing storage

Answer

Matching column widths to screen display width

Answer

Aligning table columns alphabetically

Answer

Using the same data type for all columns in a table