DMC Physical Data Modeling 1 — Questions and Answers
Question 1: What is the primary purpose of a physical data model?
- To communicate business concepts to stakeholders
- To specify how data will be stored in a specific DBMS including tables, columns, indexes, and constraints (Correct answer)
- To document entity-relationship diagrams independent of technology
- To define ETL pipeline scheduling
Correct answer: To specify how data will be stored in a specific DBMS including tables, columns, indexes, and constraints
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?
- Business rules are removed
- Entity names become table names, attributes become columns, and DBMS-specific data types are assigned (Correct answer)
- Relationships are eliminated
- Normalization is reversed entirely
Correct answer: Entity names become table names, attributes become columns, and DBMS-specific data types are assigned
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?
- Clustered stores data in a separate structure; non-clustered stores data inline with the table
- Clustered physically orders table rows by the index key; non-clustered creates a separate pointer structure (Correct answer)
- Clustered indexes can only be on primary keys; non-clustered cannot be on primary keys
- Clustered applies to views; non-clustered applies to tables
Correct answer: Clustered physically orders table rows by the index key; non-clustered creates a separate pointer structure
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?
- Splitting a table's columns into multiple tables
- Dividing a large table's rows into segments based on a partition key to improve performance and manageability (Correct answer)
- Creating read-only replicas of a table
- Encrypting specific columns for security
Correct answer: Dividing a large table's rows into segments based on a partition key to improve performance and manageability
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?
- To verify the column exists in the schema
- To restrict the range or set of valid values that can be inserted into a column (Correct answer)
- To create a unique index on the column
- To cascade deletes to related tables
Correct answer: To restrict the range or set of valid values that can be inserted into a column
A CHECK constraint enforces a condition on column values at the database level, rejecting any insert or update that violates the rule.
Question 6: What does 'data type alignment' mean in physical data modeling?
- Matching column widths to screen display width
- Choosing DBMS data types that accurately represent the logical domain while optimizing storage (Correct answer)
- Aligning table columns alphabetically
- Using the same data type for all columns in a table
Correct answer: Choosing DBMS data types that accurately represent the logical domain while optimizing storage
Data type alignment means selecting the most appropriate DBMS-specific data type that matches the attribute's domain, constraints, and expected value range while minimizing storage.
DMC Physical Data Modeling 1
What is the primary purpose of a physical data model?