DMC Conceptual and Logical Data Design 2 — Questions and Answers
Question 1: What is a candidate key?
- Any column with a NOT NULL constraint
- A minimal set of attributes that uniquely identifies each row in a relation (Correct answer)
- A foreign key candidate waiting to be promoted
- An index created during physical design
Correct answer: A minimal set of attributes that uniquely identifies each row in a relation
A candidate key is a minimal set of attributes (one or more columns) that can uniquely identify every row in a table, from which the primary key is chosen.
Question 2: In an ERD, what does the notation '1' on one side and 'M' on the other side of a relationship line indicate?
- The number of attributes in each entity
- A one-to-many relationship between the two entities (Correct answer)
- The storage size difference between entities
- The number of indexes on each table
Correct answer: A one-to-many relationship between the two entities
The 1:M notation indicates a one-to-many relationship where one instance of the first entity can relate to many instances of the second.
Question 3: What technique is used in logical modeling to resolve a many-to-many relationship?
- Adding a NOT NULL constraint
- Creating an associative (bridge/intersection) entity (Correct answer)
- Splitting the relationship into two recursive ones
- Adding a composite index
Correct answer: Creating an associative (bridge/intersection) entity
A many-to-many relationship is resolved by introducing an associative entity that holds the foreign keys of both related entities and may include relationship-specific attributes.
Question 4: Which design artifact maps source data fields to target data model attributes?
- Data flow diagram
- Data lineage map / data mapping document (Correct answer)
- Physical schema diagram
- Network topology chart
Correct answer: Data lineage map / data mapping document
A data mapping document (or lineage map) traces each source field to its target attribute, documenting transformations, business rules, and data origin.
Question 5: What is denormalization, and when is it appropriate?
- Reversing normalization by intentionally adding redundancy to improve read performance (Correct answer)
- Removing all primary keys to reduce constraint overhead
- Converting a logical model to a physical model
- Adding NULL columns to avoid schema changes
Correct answer: Reversing normalization by intentionally adding redundancy to improve read performance
Denormalization intentionally introduces redundancy into a data model to improve query read performance, typically used in data warehouses or high-read OLAP systems.
Question 6: What does referential integrity enforce in a relational data model?
- That all attributes have non-NULL values
- That a foreign key value must match an existing primary key value in the referenced table (Correct answer)
- That every table has at least one index
- That queries return consistent results under concurrent access
Correct answer: That a foreign key value must match an existing primary key value in the referenced table
Referential integrity ensures that foreign key values always correspond to an existing primary key in the referenced table, preventing orphaned records.
DMC Conceptual and Logical Data Design 2
What is a candidate key?