Data Modeling Certification (DMC) — Questions and Answers

Question 1: What is schema-on-read, and how does it differ from schema-on-write?

Schema-on-read enforces structure when data is written; schema-on-write enforces it at query time
Both are identical but used in different DBMS platforms
Schema-on-read applies structure at query time; schema-on-write enforces structure when data is stored (Correct answer)
Schema-on-read is only used in relational databases

Correct answer: Schema-on-read applies structure at query time; schema-on-write enforces structure when data is stored

Schema-on-write (traditional RDBMS) enforces a defined schema when data is stored, while schema-on-read (data lakes) applies interpretation at query time, allowing raw data ingestion.

Question 2: What does GDPR require of data models that store personal data of EU residents?

All personal data must be replicated to US servers
Personal data columns must use numeric surrogate keys only
All personal data must be stored unencrypted for audit purposes
Models must accommodate data subject rights including access, correction, and erasure of personal data (Correct answer)

Correct answer: Models must accommodate data subject rights including access, correction, and erasure of personal data

GDPR requires that data systems support the right to access, rectify, port, and erase personal data — data models must be designed to accommodate these rights technically.

Question 3: What is an ODS (Operational Data Store) and how does it differ from a data warehouse?

An ODS stores current integrated operational data for near-real-time reporting; a warehouse stores historical data for strategic analysis (Correct answer)
An ODS stores historical data going back decades; a warehouse only stores current data
An ODS is normalized; a warehouse uses only flat files
An ODS is cloud-based; a warehouse is on-premises only

Correct answer: An ODS stores current integrated operational data for near-real-time reporting; a warehouse stores historical data for strategic analysis

An ODS integrates current operational data from multiple source systems for tactical, near-real-time reporting, while a data warehouse stores historical data optimized for strategic analytics.

Question 4: Which DMC concept refers to a consistent definition of a business entity agreed upon across the enterprise?

Operational data
Master data (Correct answer)
Derived data
Transactional data

Correct answer: Master data

Master data represents the core business entities — customers, products, employees, locations — that are shared and used consistently across multiple business processes and systems.

Question 5: Which normal form eliminates partial dependencies on a composite primary key?

First Normal Form (1NF)
Second Normal Form (2NF) (Correct answer)
Boyce-Codd Normal Form (BCNF)
Third Normal Form (3NF)

Correct answer: Second Normal Form (2NF)

2NF requires that every non-key attribute is fully functionally dependent on the entire primary key, eliminating partial dependencies.

Question 6: What does the term 'optionality' refer to in a data model?

Whether a relationship participation is mandatory or optional (Correct answer)
Optional indexes on non-key columns
The choice between surrogate and natural keys
Whether an attribute can store NULL values

Correct answer: Whether a relationship participation is mandatory or optional

Optionality describes whether an entity's participation in a relationship is mandatory (must exist) or optional (may or may not exist).

Question 7: Which of the following best describes denormalization?

A process used exclusively in NoSQL databases.
A technique used to optimize read performance by combining tables. (Correct answer)
The act of restructuring databases to eliminate foreign keys.
A process of increasing table normalization to 5NF or higher.

Correct answer: A technique used to optimize read performance by combining tables.

Denormalization is a database optimization technique where redundant data is intentionally added to a normalized database, or tables are combined, to improve read performance. While it increases data redundancy, it reduces the number of joins required for complex queries, making data retrieval faster. This approach is often employed in data warehousing and reporting systems where read speed is prioritized over strict normalization.

Question 8: Which of the following is a real-world use case for data modeling?

Developing a backup and disaster recovery system
Structuring a data warehouse for financial reporting (Correct answer)
Creating a machine learning pipeline to clean and preprocess data
Designing a database for a hospital to manage patient records (Correct answer)

Correct answer: Structuring a data warehouse for financial reporting

Data modeling is fundamentally about designing the structure of data storage to meet specific business requirements. Designing a database for a hospital to manage patient records involves defining entities like patients, doctors, and appointments, along with their relationships. Similarly, structuring a data warehouse for financial reporting requires modeling facts (e.g., sales figures) and dimensions (e.g., time, product) to support analytical queries. Both are direct applications of data modeling principles.

Question 9: What type of database schema is typically used for reporting and business intelligence systems?

Flat file structure
Key-value storage
Normalized schema with multiple joins
Star or snowflake schema (Correct answer)

Correct answer: Star or snowflake schema

Star and snowflake schemas are specifically designed for data warehousing and business intelligence (BI) systems, which prioritize fast query performance for reporting and analytical purposes. These dimensional models denormalize data to reduce the number of joins required for queries, making it much quicker to retrieve aggregated data compared to highly normalized transactional schemas. They are optimized for read-heavy workloads typical of BI.

Question 10: What is the purpose of a data model review process in a governed organization?

To audit SQL query execution plans
To review ETL job scheduling
To ensure proposed data model changes conform to standards, don't break integrations, and are documented before implementation (Correct answer)
To approve changes to server hardware configurations

Correct answer: To ensure proposed data model changes conform to standards, don't break integrations, and are documented before implementation

A formal data model review process validates that changes meet naming standards, maintain data quality constraints, don't break downstream systems, and are properly documented in the data dictionary.

Question 11: What is the primary key in a relational database?

A column that must contain unique and non-null values. (Correct answer)
A field used to link two tables together.
A column or set of columns that ensures referential integrity.
A unique identifier for a row in a table. (Correct answer)

Correct answer: A column that must contain unique and non-null values.

A primary key is a crucial component in relational databases, serving as a unique identifier for each record (row) within a table. It ensures that every row can be distinctly identified and accessed, preventing duplicate entries. By definition, a primary key must contain unique and non-null values, guaranteeing data integrity and enabling relationships with other tables through foreign keys.

Question 12: What principle ensures that every fact in a data model is stored in exactly one place?

Entity completeness
Single Source of Truth (SSOT) (Correct answer)
Referential integrity
Domain constraint

Correct answer: Single Source of Truth (SSOT)

The Single Source of Truth principle ensures each fact is stored once and only once, eliminating redundancy and reducing inconsistency risk.

Question 13: Which of the following tools are commonly used to integrate data modeling into data pipelines?

Talend (Correct answer)
Apache NiFi (Correct answer)
SQL Server Integration Services (SSIS) (Correct answer)
ER/Studio

Correct answer: Talend

Apache NiFi, Talend, and SQL Server Integration Services (SSIS) are all powerful tools commonly used in data pipelines for Extract, Transform, Load (ETL) processes. They facilitate the movement and transformation of data between various sources and destinations, including databases designed with data modeling. These tools are essential for integrating and preparing data for various analytical and operational purposes.

Question 14: In data governance, what is the role of a data governance council?

To conduct database performance tuning
To write SQL for data transformation projects
To serve as a cross-functional decision-making body that sets data policies, resolves conflicts, and prioritizes data initiatives (Correct answer)
To operate the backup and recovery systems

Correct answer: To serve as a cross-functional decision-making body that sets data policies, resolves conflicts, and prioritizes data initiatives

A data governance council brings together senior stakeholders from business and IT to establish data policies, resolve disputes about data ownership, and prioritize governance initiatives.

Question 15: In Data Vault modeling, what is the purpose of a Link?

To connect the data vault to the presentation layer
To store descriptive attributes about an entity
To capture the relationships and associations between two or more Hubs (Correct answer)
To record the historical values of Hub business keys

Correct answer: To capture the relationships and associations between two or more Hubs

A Link in Data Vault modeling captures the many-to-many or associative relationships between Hubs, storing only the foreign keys to the related Hubs plus metadata.

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

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
To verify the column exists in the schema

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 17: What is a fact table in dimensional modeling?

A table containing only lookup codes and their descriptions
A central table in a star or snowflake schema that stores measurable business events with foreign keys to dimension tables (Correct answer)
A table containing master data like customers and products
A table that stores audit logs of all data changes

Correct answer: A central table in a star or snowflake schema that stores measurable business events with foreign keys to dimension tables

A fact table stores measurable, quantitative data about business events — such as sales amounts, quantities, or counts — along with foreign keys linking to the relevant dimensions.

Question 18: What is the purpose of a data dictionary in data modeling?

To document the definitions, formats, and usage of data elements (Correct answer)
To map network traffic routes
To translate SQL queries automatically
To store compressed database backups

Correct answer: To document the definitions, formats, and usage of data elements

A data dictionary provides authoritative documentation of data element definitions, formats, relationships, and business rules.

Question 19: What is data lineage, and why is it important in metadata management?

A summary of database query performance metrics
A diagram of the organization hierarchy, showing data ownership
A historical record of metadata updates
A record of where data originates, how it transforms, and where it moves (Correct answer)

Correct answer: A record of where data originates, how it transforms, and where it moves

Data lineage is a critical aspect of metadata management that tracks the complete lifecycle of data, from its origin to its current state. It provides a detailed record of where data comes from, how it has been transformed or processed, and where it has moved across different systems. This information is vital for understanding data quality, ensuring compliance, troubleshooting errors, and building trust in data assets.

Question 20: Which technique is commonly used in data modeling to detect and merge duplicate entity records that represent the same real-world object?

Master data management (MDM) (Correct answer)
Data partitioning
Schema migration
Horizontal scaling

Correct answer: Master data management (MDM)

Master Data Management (MDM) provides processes and tools to identify, merge, and maintain a single authoritative record for duplicate entities across systems.

Question 21: What is the Star Schema pattern used in data warehouse modeling?

A central fact table surrounded by denormalized dimension tables, optimized for analytical query performance (Correct answer)
A fully normalized OLTP schema shaped like a star in the ERD
A schema using only one large table with all data
A schema where all tables connect in a circular chain

Correct answer: A central fact table surrounded by denormalized dimension tables, optimized for analytical query performance

The Star Schema places a central fact table containing measures and foreign keys surrounded by denormalized dimension tables, enabling fast analytical queries with simple joins.

Question 22: What is data governance in the context of data modeling?

The framework of policies, processes, roles, and standards that ensure data is managed consistently and responsibly (Correct answer)
A software tool for automating database deployments
A backup and recovery strategy
A method for encrypting sensitive columns

Correct answer: The framework of policies, processes, roles, and standards that ensure data is managed consistently and responsibly

Data governance establishes the policies, processes, accountability structures, and standards that define how data is created, stored, maintained, used, and retired across the organization.

Question 23: 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 24: How does data modeling improve machine learning workflows?

By structuring data for efficient feature extraction (Correct answer)
By optimizing machine learning algorithms
By training models directly on ERD diagrams
By enhancing models deployment pipelines

Correct answer: By structuring data for efficient feature extraction

Data modeling significantly improves machine learning workflows by providing a structured and organized foundation for data. A well-designed data model ensures that data is clean, consistent, and logically related, which is crucial for efficient feature extraction and engineering. This structured approach simplifies the process of preparing data for model training, leading to more accurate and robust machine learning models.

Question 25: Which of the following best describes an associative entity (also known as an intersection or bridge entity)?

An entity representing a lookup or code table
An entity that has no attributes other than its primary key
An entity that links two or more entities in a many-to-many relationship and may contain its own attributes (Correct answer)
An entity used only in physical data models

Correct answer: An entity that links two or more entities in a many-to-many relationship and may contain its own attributes

An associative entity resolves a many-to-many relationship by becoming its own entity with a composite key and potentially additional attributes.

Question 26: What is an abstract entity in a supertype/subtype model?

An entity with no attributes
A supertype entity that cannot have direct instances — all instances must belong to a subtype (Correct answer)
An entity used only in conceptual models
An entity representing an external system

Correct answer: A supertype entity that cannot have direct instances — all instances must belong to a subtype

An abstract supertype has no instances of its own; every instance must be classified as one of its subtypes, such as EMPLOYEE being abstract with subtypes FULL_TIME and PART_TIME.

Question 27: What is the difference between data classification and data categorization in governance?

Classification assigns sensitivity/security labels (PII, confidential); categorization groups data by business subject area (Finance, HR) (Correct answer)
Classification is physical; categorization is logical
Classification applies only to structured data; categorization to unstructured
They are synonymous terms used interchangeably

Correct answer: Classification assigns sensitivity/security labels (PII, confidential); categorization groups data by business subject area (Finance, HR)

Data classification assigns security/sensitivity labels (public, internal, confidential, restricted) while data categorization organizes data by business domain or subject area.

Question 28: What does DDL stand for and what does it contain?

Data Dictionary Language — the language of the data catalog
Data Definition Language — SQL statements that create, alter, and drop database objects (Correct answer)
Database Design Level — the physical design tier
Data Deployment Language — scripts used to deploy ETL pipelines

Correct answer: Data Definition Language — SQL statements that create, alter, and drop database objects

DDL (Data Definition Language) is the subset of SQL containing CREATE, ALTER, DROP, TRUNCATE, and RENAME statements that define and modify database structure.

Question 29: What is a data catalog and how does it relate to data governance?

A list of approved SQL query templates
A list of all ETL jobs scheduled in the pipeline
An organized inventory of data assets with metadata, definitions, and ownership that enables data discovery and governance (Correct answer)
A read-only backup of the production database

Correct answer: An organized inventory of data assets with metadata, definitions, and ownership that enables data discovery and governance

A data catalog provides a searchable inventory of data assets enriched with business definitions, ownership, lineage, quality metrics, and usage statistics to enable governance and self-service analytics.

Question 30: In data modeling, what is an entity?

A database index
A foreign key constraint
A person, place, object, event, or concept about which data is stored (Correct answer)
A stored procedure

Correct answer: A person, place, object, event, or concept about which data is stored

An entity is a real-world object or concept — such as a customer, product, or order — about which data is collected.

Question 31: Which of the following tools is commonly used for creating Entity-Relationship Diagrams (ERDs)?

Lucidchart (Correct answer)
Tableau
Microsoft Excel
ER/Studio (Correct answer)

Correct answer: Lucidchart

ER/Studio and Lucidchart are widely recognized tools specifically designed for creating Entity-Relationship Diagrams (ERDs) and other data modeling visualizations. ER/Studio is a dedicated data modeling tool offering advanced features for enterprise-level database design, while Lucidchart is a versatile diagramming application that supports ERD creation among many other diagram types. Both provide intuitive interfaces for visually representing database structures and relationships.

Question 32: In physical data modeling, what does 'fill factor' control in an index?

The maximum number of columns in a composite index
The compression ratio of index storage
How many rows fit in a single data page
The percentage of each index page left free to accommodate future insertions and reduce page splits (Correct answer)

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

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

Question 33: What anomaly occurs when inserting new data requires entering dummy or NULL values for unrelated attributes?

Deletion anomaly
Referential anomaly
Update anomaly
Insertion anomaly (Correct answer)

Correct answer: Insertion anomaly

An insertion anomaly occurs when you can't add new data without also supplying values for unrelated attributes that have no current meaning.

Question 34: A data steward discovers that customer records have phone numbers stored as both '555-1234' and '5551234'. Which data quality dimension is primarily affected?

Conformity (Correct answer)
Uniqueness
Consistency
Accuracy

Correct answer: Conformity

Conformity measures whether data follows specified formats and standards; inconsistent phone number formats indicate a conformity failure.

Question 35: Which ERD notation style uses rectangles for entities, diamonds for relationships, and ovals for attributes?

Chen notation (Correct answer)
Crow's Foot (IE) notation
IDEF1X notation
Barker notation

Correct answer: Chen notation

Chen notation, developed by Peter Chen in 1976, uses rectangles for entities, diamonds for relationships, and ovals for attributes in its ERD representation.

Question 36: How does a Snowflake Schema differ from a Star Schema?

Snowflake is for OLTP; Star is for OLAP
Snowflake adds surrogate keys; Star uses natural keys
Snowflake normalizes dimension tables into sub-dimensions, reducing redundancy but requiring more joins; Star denormalizes for simplicity (Correct answer)
Snowflake uses more fact tables; Star uses more dimension tables

Correct answer: Snowflake normalizes dimension tables into sub-dimensions, reducing redundancy but requiring more joins; Star denormalizes for simplicity

A Snowflake Schema normalizes dimension tables by splitting hierarchies into separate tables, reducing data redundancy at the cost of more complex queries requiring additional joins.

Question 37: What is the purpose of a data quality dimension in governance?

To classify data by sensitivity level
To define measurable characteristics — such as completeness, accuracy, and timeliness — used to assess data quality (Correct answer)
To measure storage consumption by data domain
To segment data by geographic region

Correct answer: To define measurable characteristics — such as completeness, accuracy, and timeliness — used to assess data quality

Data quality dimensions — including completeness, accuracy, consistency, timeliness, validity, and uniqueness — provide measurable criteria for evaluating how fit data is for its intended use.

Question 38: What is a functional dependency in the context of normalization?

A dependency between two database servers
A trigger that enforces a business rule
A situation where the value of one attribute determines the value of another (Correct answer)
A referential integrity constraint between tables

Correct answer: A situation where the value of one attribute determines the value of another

A functional dependency (A → B) means that knowing the value of attribute A uniquely determines the value of attribute B.

Question 39: What does 'data sovereignty' mean and why does it affect physical data modeling decisions?

The rule that production data must not leave the data center
The legal concept that data is subject to the laws of the country where it is stored, affecting where data can physically reside (Correct answer)
The right of developers to define their own schemas
The principle that the data owner controls all access

Correct answer: The legal concept that data is subject to the laws of the country where it is stored, affecting where data can physically reside

Data sovereignty means data is governed by the laws of the country it is stored in, requiring physical models to implement region-specific data stores for compliance with local regulations.

Question 40: 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)
An index created during physical design
A foreign key candidate waiting to be promoted

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 41: What is the purpose of a covering index?

To span multiple tables in a join
To enforce NOT NULL on all columns
To cover foreign key relationships automatically
To include all columns needed by a query in the index itself, avoiding a table row lookup (Correct answer)

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

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 42: In the context of DMC, what does 'domain' mean when applied to an attribute?

The business unit that owns the data
The geographic region of data storage
The set of all valid values an attribute can hold (Correct answer)
The network domain hosting the database

Correct answer: The set of all valid values an attribute can hold

A domain is the complete set of allowable values for an attribute, defined by data type, length, format, and business constraints.

Question 43: Which normal form specifically addresses multi-valued dependencies?

2NF
4NF (Correct answer)
BCNF
3NF

Correct answer: 4NF

Fourth Normal Form (4NF) eliminates non-trivial multi-valued dependencies, ensuring a table doesn't independently store two or more multi-valued facts about an entity.

Question 44: What is the purpose of a data catalog in metadata management?

To automate the process of data cleaning
To provide a central repository for metadata (Correct answer)
To store and query large volumes of data
To analyze business trends

Correct answer: To provide a central repository for metadata

A data catalog serves as a central repository for an organization's metadata, providing a comprehensive inventory of all available data assets. Its purpose is to make data discoverable, understandable, and trustworthy by collecting, organizing, and enriching metadata from various sources. This enables users to easily find, comprehend, and utilize the data they need, improving data governance and collaboration.

Question 45: What is a subtype in data modeling?

A lookup table containing code values
A specialized category of a supertype entity that inherits its attributes (Correct answer)
A secondary index on a table
A data type smaller than INTEGER

Correct answer: A specialized category of a supertype entity that inherits its attributes

A subtype is a specialized version of a supertype entity that inherits all supertype attributes and relationships while adding its own distinct ones.

Question 46: In an ERD, what does a crow's foot notation symbol at the end of a relationship line represent?

Primary key
Zero or one
Many (zero or more) (Correct answer)
One and only one

Correct answer: Many (zero or more)

In crow's foot notation, the multi-pronged symbol (crow's foot) indicates the 'many' side of a one-to-many or many-to-many relationship.

Question 47: What is a recursive (or self-referencing) relationship in data modeling?

A stored procedure that calls itself
A circular foreign key between two tables
A relationship that loops through three tables
A relationship where an entity is associated with instances of itself (Correct answer)

Correct answer: A relationship where an entity is associated with instances of itself

A recursive relationship occurs when instances of a single entity type are related to other instances of the same entity type, such as an employee managing other employees.

Question 48: What makes Boyce-Codd Normal Form (BCNF) stricter than 3NF?

BCNF also eliminates multi-valued dependencies
BCNF requires surrogate keys instead of natural keys
BCNF requires every determinant to be a candidate key, including in cases 3NF misses (Correct answer)
BCNF eliminates all repeating groups

Correct answer: BCNF requires every determinant to be a candidate key, including in cases 3NF misses

BCNF strengthens 3NF by requiring that every determinant in the relation is a candidate key, handling edge cases where 3NF still allows anomalies.

Question 49: What is denormalization, and when is it appropriate?

Removing all primary keys to reduce constraint overhead
Adding NULL columns to avoid schema changes
Converting a logical model to a physical model
Reversing normalization by intentionally adding redundancy to improve read performance (Correct answer)

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 50: Which of the following best describes the star schema used in dimensional modeling?

It has no foreign key relationships between tables.
It is used exclusively for transactional systems.
It uses a central fact table surrounded by dimension tables. (Correct answer)
It contains normalized tables with minimal redundancy.

Correct answer: It uses a central fact table surrounded by dimension tables.

The star schema is a foundational structure in dimensional modeling, commonly used in data warehouses for analytical purposes. It consists of a central 'fact' table, which contains quantitative measures, surrounded by multiple 'dimension' tables that provide descriptive context for those measures. This simple, denormalized structure optimizes query performance for reporting and business intelligence.

Question 51: What is a data steward's primary responsibility?

Administering database server hardware
Writing SQL queries for data extraction
Accountable for the quality, definition, and appropriate use of specific data elements within a business domain (Correct answer)
Developing ETL pipelines

Correct answer: Accountable for the quality, definition, and appropriate use of specific data elements within a business domain

A data steward is the business owner accountable for defining, maintaining, and ensuring the quality and correct usage of specific data assets within their domain.

Question 52: What is a multi-valued attribute?

An attribute that can hold multiple independent values for a single entity instance (Correct answer)
An attribute with a composite data type
A foreign key that references two parent tables
An attribute that maps to multiple physical columns

Correct answer: An attribute that can hold multiple independent values for a single entity instance

A multi-valued attribute is one that can hold more than one value simultaneously for the same entity, such as a person having multiple phone numbers.

Question 53: What is a business glossary and why is it important in data modeling?

A list of approved database vendor products
An authoritative list of business terms with agreed definitions used consistently across the organization (Correct answer)
A glossary of data modeling notation symbols
A SQL keyword reference manual

Correct answer: An authoritative list of business terms with agreed definitions used consistently across the organization

A business glossary provides standard definitions for business terms, ensuring that data modelers, developers, and business users all mean the same thing when they use key terms.

Question 54: Which of the following is a characteristic of a natural key?

It derives its value from business attributes that already exist (Correct answer)
It never appears in reports
It is generated by the database system
It is always numeric

Correct answer: It derives its value from business attributes that already exist

A natural key is derived from existing business data — such as a Social Security number or product barcode — that already has meaning in the real world.

Question 55: What does cardinality describe in a data model?

The primary key selection strategy
The data type of an attribute
The number of instances of one entity related to instances of another (Correct answer)
The indexing method used in storage

Correct answer: The number of instances of one entity related to instances of another

Cardinality defines how many instances of one entity can be associated with instances of another entity in a relationship.

Question 56: What is the primary purpose of a physical data model?

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
To communicate business concepts to stakeholders

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 57: What is the primary goal of a conceptual data model?

To document ETL transformation rules
To define indexes and storage parameters
To capture high-level business concepts and their relationships independent of technology (Correct answer)
To specify SQL DDL scripts for deployment

Correct answer: To capture high-level business concepts and their relationships independent of technology

A conceptual data model captures major business entities and relationships at a high level, focusing on what the business needs to track rather than how data will be stored.

Question 58: Which data quality dimension measures the degree to which data values fall within acceptable ranges and adhere to defined business rules?

Timeliness
Validity (Correct answer)
Completeness
Consistency

Correct answer: Validity

Validity measures whether data conforms to defined formats, ranges, and business rules, ensuring values are acceptable within the data model.

Question 59: What is data masking and when is it applied in a governed data environment?

Encrypting the entire database at rest
Hiding table names from non-admin users
Replacing sensitive data values with realistic but fictitious substitutes to protect privacy in non-production environments (Correct answer)
Removing deleted rows from audit logs

Correct answer: Replacing sensitive data values with realistic but fictitious substitutes to protect privacy in non-production environments

Data masking substitutes sensitive production data (like SSNs or credit card numbers) with realistic fictitious values, allowing development and testing without exposing real personal data.

Question 60: When designing a database for an e-commerce application, which entities are most likely required?

Operating System Logs
Sales Transactions (Correct answer)
Products (Correct answer)
Customers (Correct answer)

Correct answer: Sales Transactions

For an e-commerce application, core entities represent the fundamental components of the business operations. Products are what are sold, Customers are who buy them, and Sales Transactions record the actual purchases. These entities are essential for managing inventory, tracking customer information, and processing orders, forming the backbone of the application's database design.

Question 61: In an ERD, what does the notation '1' on one side and 'M' on the other side of a relationship line indicate?

A one-to-many relationship between the two entities (Correct answer)
The storage size difference between entities
The number of attributes in each entity
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 62: What does referential integrity enforce in a relational data model?

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
That all attributes have non-NULL values

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.

Question 63: What is the purpose of a data naming convention standard?

To compress data by using shorter names
To ensure consistent, meaningful, and unambiguous naming of data elements across the organization (Correct answer)
To restrict who can create new tables
To speed up query execution

Correct answer: To ensure consistent, meaningful, and unambiguous naming of data elements across the organization

Naming convention standards ensure that all data objects — tables, columns, entities — follow consistent patterns, reducing ambiguity and making models easier to understand and maintain.

Question 64: In logical data modeling, what is the purpose of a business rule?

To document server backup procedures
To define the physical storage engine
To capture constraints and policies that govern data and must be enforced by the model (Correct answer)
To specify the ETL schedule

Correct answer: To capture constraints and policies that govern data and must be enforced by the model

Business rules define constraints, policies, and conditions that data must satisfy, and they guide the structure and constraints built into the logical data model.

Question 65: What are the three levels of data abstraction in the ANSI/SPARC architecture?

Physical, Logical, Virtual
External, Conceptual, Internal (Correct answer)
Operational, Analytical, Strategic
Raw, Processed, Stored

Correct answer: External, Conceptual, Internal

The ANSI/SPARC architecture defines three levels: External (user views), Conceptual (logical whole), and Internal (physical storage).

Question 66: Which of the following are NoSQL database technologies?

Neo4j (Correct answer)
Cassandra (Correct answer)
MongoDB (Correct answer)
Oracle Database

Correct answer: Neo4j

Neo4j, MongoDB, and Cassandra are all examples of NoSQL database technologies, which offer alternatives to traditional relational databases. Neo4j is a graph database, MongoDB is a document database, and Cassandra is a wide-column store. These databases are designed to handle large volumes of unstructured or semi-structured data, providing flexibility, scalability, and high performance for specific use cases.

Question 67: Which of the following are key objectives of normalization in data modeling?

Reducing data redundancy (Correct answer)
Increasing query execution speed
Improving data integrity (Correct answer)
Enhancing database security

Correct answer: Reducing data redundancy

Normalization in data modeling aims to organize database tables to minimize data redundancy and improve data integrity. By breaking down large tables into smaller, related ones, it ensures that data is stored only once, reducing storage space and preventing inconsistencies. This structured approach helps maintain the accuracy and reliability of the data.

Question 68: What is data profiling in the context of data governance and modeling?

Benchmarking query performance across tables
Documenting which teams access which tables
Creating user profiles for database access control
Analyzing existing data to understand its structure, content, quality, and patterns (Correct answer)

Correct answer: Analyzing existing data to understand its structure, content, quality, and patterns

Data profiling examines source data to discover its structure, value ranges, null rates, uniqueness, and patterns — essential input for building accurate data models and governance policies.

Question 69: What is a data standard in the context of enterprise data governance?

A benchmark for query performance
An agreed-upon rule or format that data must conform to — such as date formats, code values, or naming patterns — enforced across systems (Correct answer)
A minimum row count requirement for tables
A database vendor certification

Correct answer: An agreed-upon rule or format that data must conform to — such as date formats, code values, or naming patterns — enforced across systems

Data standards define mandatory formats, code sets, naming rules, and quality thresholds that all data systems must conform to, enabling integration and consistent interpretation.

Question 70: What is the lossless decomposition property in normalization?

The property that all foreign keys retain their referenced values after splitting
The ability to compress tables without losing row data
The rule that normalization never reduces the number of tables
The guarantee that decomposing a table into smaller tables and then rejoining them reproduces the original table exactly (Correct answer)

Correct answer: The guarantee that decomposing a table into smaller tables and then rejoining them reproduces the original table exactly

Lossless decomposition guarantees that when you split a table into two or more tables, joining them back together returns exactly the original data with no extra or missing rows.

Question 71: Which of the following best distinguishes a logical data model from a conceptual data model?

A logical model includes physical storage parameters
A logical model only documents relationships, not entities
A logical model adds attributes, primary keys, and data types without specifying a DBMS (Correct answer)
A logical model is less detailed than a conceptual model

Correct answer: A logical model adds attributes, primary keys, and data types without specifying a DBMS

A logical data model refines the conceptual model by adding detailed attributes, primary and foreign keys, and data types while remaining DBMS-independent.

Question 72: Which of the following database systems is relational?

PostgreSQL (Correct answer)
MongoDB
Cassandra
MySQL (Correct answer)

Correct answer: PostgreSQL

MySQL and PostgreSQL are both prominent examples of relational database management systems (RDBMS). They store data in structured tables with predefined schemas, enforce relationships between tables using keys, and support SQL (Structured Query Language) for data manipulation. This relational model ensures data integrity and consistency, making them suitable for a wide range of applications.

Question 73: What is a join dependency, and which normal form addresses it?

A dependency where a table can only be reconstructed losslessly by joining three or more projections — addressed by 5NF (PJNF) (Correct answer)
A dependency that requires a JOIN operation to retrieve a value — addressed by BCNF
A circular reference between two tables — addressed by 4NF
A constraint that two foreign keys must reference the same parent — addressed by 3NF

Correct answer: A dependency where a table can only be reconstructed losslessly by joining three or more projections — addressed by 5NF (PJNF)

A join dependency exists when a table can only be decomposed into more than two tables and still be reconstructed losslessly, which Fifth Normal Form (5NF/PJNF) eliminates.

Question 74: What is the key difference between a strong entity and a weak entity?

Weak entities cannot have relationships
Strong entities use surrogate keys; weak entities use natural keys
A weak entity cannot be uniquely identified without referencing its parent entity (Correct answer)
Strong entities have more attributes than weak entities

Correct answer: A weak entity cannot be uniquely identified without referencing its parent entity

A weak entity depends on a parent (owner) entity for its identification and cannot be uniquely identified by its own attributes alone.

Question 75: What is a surrogate key?

A system-generated identifier with no business meaning (Correct answer)
A natural identifier from the real world
A composite key made of multiple columns
A key borrowed from another table

Correct answer: A system-generated identifier with no business meaning

A surrogate key is a system-generated, meaningless identifier — typically an integer sequence — used solely to uniquely identify a row.

Question 76: What physical design technique is most appropriate for implementing a slowly changing dimension (SCD) Type 2 in a data warehouse?

Overwrite the existing row with new values
Delete old rows and insert new ones
Add effective date and expiration date columns along with a current flag to track historical versions (Correct answer)
Use a separate audit table for all changes

Correct answer: Add effective date and expiration date columns along with a current flag to track historical versions

SCD Type 2 preserves history by inserting a new row for each change, with effective_date, expiration_date, and is_current columns to track each version.

Question 77: What is a slowly changing dimension (SCD) Type 1?

A dimension updated on a slow nightly schedule
A dimension that is never updated once created
A dimension that stores one record per change with full history
A method of handling attribute changes by simply overwriting the old value, preserving no history (Correct answer)

Correct answer: A method of handling attribute changes by simply overwriting the old value, preserving no history

SCD Type 1 handles changes by overwriting the old attribute value with the new one, which is simple but destroys historical data — appropriate when history is not needed.

Question 78: Which of the following are common elements of an Entity-Relationship Diagram (ERD)?

Entities (Correct answer)
Attributes (Correct answer)
Data Pipelines
Keys (Primary and Foreign) (Correct answer)

Correct answer: Entities

An Entity-Relationship Diagram (ERD) is a visual tool used to model the structure of a database. Its common elements include entities, which represent real-world objects; attributes, which describe the properties of an entity; and relationships, which show how entities are connected. Keys, such as primary and foreign keys, are essential attributes that define these relationships and ensure data integrity.

Question 79: Which modeling artifact is used to document the meaning and origin of a single data element across an enterprise?

Conceptual schema
Entity-Relationship Diagram
Physical table script
Data element definition record (Correct answer)

Correct answer: Data element definition record

A data element definition record captures the business name, definition, data type, source, and steward of a single atomic piece of data.

Question 80: What is the primary purpose of a data quality scorecard in enterprise data modeling?

To rank database administrators by performance
To map ETL pipeline throughput
To document the physical storage layout
To measure and communicate data quality metrics against defined thresholds (Correct answer)

Correct answer: To measure and communicate data quality metrics against defined thresholds

A data quality scorecard provides a structured view of quality metrics versus targets, enabling stakeholders to track and improve data health over time.

Question 81: What does 'data lineage' document?

The age of data records from creation to deletion
The list of users who accessed specific data
The genealogy of database schema versions
The origin of data and how it flows and transforms through systems over time (Correct answer)

Correct answer: The origin of data and how it flows and transforms through systems over time

Data lineage tracks the complete journey of data from its source through every transformation and system it passes through to its final destination.

Question 82: What is the purpose of a data retention policy in a data model?

To prevent database indexes from growing too large
To define how long data must be kept and when it should be archived or deleted based on legal and business requirements (Correct answer)
To control how many backups are stored
To set limits on query execution time

Correct answer: To define how long data must be kept and when it should be archived or deleted based on legal and business requirements

A data retention policy specifies the required storage duration for data categories, driving physical design decisions about archiving, partitioning, and purging strategies.

Question 83: What is vertical partitioning (also called column splitting) in physical data modeling?

Splitting a wide table into two narrower tables sharing the same primary key, separating frequently and rarely accessed columns (Correct answer)
Dividing a table by business unit
Partitioning rows by date range across multiple tables
Creating column-level access controls

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

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 84: What is a degenerate dimension in data warehouse modeling?

A dimension attribute that lives in the fact table rather than in a separate dimension table because it has no associated descriptive attributes (Correct answer)
A dimension with very few rows and low cardinality
A deprecated dimension no longer used in queries
A dimension that has degraded over time due to poor data quality

Correct answer: A dimension attribute that lives in the fact table rather than in a separate dimension table because it has no associated descriptive attributes

A degenerate dimension is a dimensional attribute — such as an invoice number or order number — stored directly in the fact table because it has no associated descriptive data to justify its own table.

Question 85: Which design artifact maps source data fields to target data model attributes?

Data flow diagram
Data lineage map / data mapping document (Correct answer)
Network topology chart
Physical schema diagram

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 86: Which constraint type in a relational data model ensures that a column or set of columns cannot contain NULL values?

Default constraint
NOT NULL constraint (Correct answer)
Check constraint
Unique constraint

Correct answer: NOT NULL constraint

A NOT NULL constraint enforces the completeness dimension of data quality by requiring that a column always has a value.

Question 87: What is an update anomaly in an unnormalized table?

When a primary key value must be updated in every related table
When a stored procedure fails during a transaction
When the same fact is stored in multiple rows and updating one row without updating all causes inconsistency (Correct answer)
When an index is outdated after a bulk insert

Correct answer: When the same fact is stored in multiple rows and updating one row without updating all causes inconsistency

An update anomaly occurs when a single logical fact is stored in multiple rows, so updating it in one place without updating all copies creates inconsistent data.

Question 88: Which concept represents a property or characteristic of an entity in a data model?

Schema
Relationship
Attribute (Correct answer)
Cardinality

Correct answer: Attribute

An attribute is a property or characteristic that describes an entity, such as a customer's name or birth date.

Question 89: What is the role of a tablespace in physical database design?

A storage container that maps database objects to physical disk files or storage devices (Correct answer)
An in-memory cache for frequently accessed tables
A temporary workspace used for sorting operations
A logical grouping of related tables by business domain

Correct answer: A storage container that maps database objects to physical disk files or storage devices

A tablespace is a physical storage unit that maps database objects to one or more underlying data files on disk, enabling storage management and I/O optimization.

Question 90: Which of the following features are provided by SQL Server Management Studio (SSMS)?

Writing and executing SQL queries (Correct answer)
Creating and managing database schemas (Correct answer)
Debugging database applications (Correct answer)
Data visualization and dashboards

Correct answer: Writing and executing SQL queries

SQL Server Management Studio (SSMS) is a comprehensive integrated environment for managing any SQL infrastructure, including SQL Server and Azure SQL Database. It provides a robust set of tools for writing and executing SQL queries, creating and managing database objects like tables and views (schemas), and debugging database applications. SSMS is essential for database administrators and developers working with Microsoft SQL Server.

Question 91: What is table partitioning in physical data modeling?

Creating read-only replicas of a table
Dividing a large table's rows into segments based on a partition key to improve performance and manageability (Correct answer)
Splitting a table's columns into multiple tables
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 92: Which physical design technique is used to handle very large binary objects like documents or images stored in a database?

Vertical partitioning
BLOB (Binary Large Object) or CLOB (Character Large Object) data types with external storage pointers (Correct answer)
Column encryption
Row-level compression

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

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 93: What does 'data type alignment' mean in physical data modeling?

Choosing DBMS data types that accurately represent the logical domain while optimizing storage (Correct answer)
Matching column widths to screen display width
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.

Question 94: What is a data owner in a governance framework?

The analyst who most frequently queries the data
The DBA who created the table
A senior business executive accountable for the strategic use and protection of a data domain (Correct answer)
The developer who wrote the ETL script

Correct answer: A senior business executive accountable for the strategic use and protection of a data domain

A data owner is a senior business stakeholder with ultimate accountability for the strategic direction, access policies, and quality of a data domain.

Question 95: What is the purpose of data modeling tools?

To design database schemas visually (Correct answer)
To define and manage data relationships (Correct answer)
To enforce data governance policies
To automate the ETL process

Correct answer: To design database schemas visually

Data modeling tools are specialized software applications designed to help users create, visualize, and manage database schemas. They allow designers to graphically represent entities, attributes, and the relationships between them, facilitating the definition of the logical and physical structure of a database. These tools are crucial for ensuring a well-organized, efficient, and consistent database design.

Question 96: In the context of data quality, which term describes data that exists in multiple places but has conflicting values?

Duplicate data
Sparse data
Inconsistent data (Correct answer)
Stale data

Correct answer: Inconsistent data

Inconsistent data refers to the same data element having different values across systems or tables, which violates the consistency dimension of data quality.

Question 97: What is a composite primary key, and what should you consider when using one?

A primary key that uses both a surrogate and natural key
A primary key shared across multiple tables
A primary key made of two or more columns that together uniquely identify a row (Correct answer)
A primary key generated by combining two hash values

Correct answer: A primary key made of two or more columns that together uniquely identify a row

A composite primary key uses multiple columns together to uniquely identify rows, which is common in associative entities but adds complexity to foreign key relationships.

Question 98: In physical data modeling, when is it appropriate to choose a CHAR data type over VARCHAR?

When the column allows NULL values
When the column is used as a foreign key
When the column will store very long strings
When the column stores fixed-length values that are always the same length, such as a 2-character state code (Correct answer)

Correct answer: When the column stores fixed-length values that are always the same length, such as a 2-character state code

CHAR is appropriate for truly fixed-length data (state codes, ISO country codes) because it stores exactly N characters without overhead, while VARCHAR varies in storage.

Question 99: What anomaly occurs when deleting a row unintentionally destroys other needed information?

Update anomaly
Deletion anomaly (Correct answer)
Referential anomaly
Insertion anomaly

Correct answer: Deletion anomaly

A deletion anomaly occurs when removing a row destroys other data that was stored in the same row but had no logical reason to be deleted.

Question 100: Which of the following best defines a data model?

A diagram showing network topology
An abstract representation of data structures and their relationships (Correct answer)
A physical server configuration plan
A software deployment pipeline

Correct answer: An abstract representation of data structures and their relationships

A data model is an abstract representation that defines data structures, relationships, and constraints within a system.

Data Modeling Certification (DMC)

The DMC is an industry-recognized certification for data modelers offered by Technics Publications, testing proficiency across 10 areas of data modeling expertise including conceptual/logical/physical design, normalization, notations, naming standards, and best practices. The exam draws from over 350 questions with randomized selection to ensure each test is unique.

100

Questions

90 min

Time Limit

85%

To Pass

Checking for saved progress...

Exam Rules

You can skip questions and return to them later
Flag questions for review before submitting
No feedback shown until you submit the entire exam
Unanswered questions count as wrong — answer everything
10 pretest questions are mixed in and don't affect your score
Timer auto-submits when time runs out
Your progress is auto-saved every 30 seconds

These practice questions were written with AI tools. They are not real exam questions. Exam facts are checked against the official sources listed below. Not reviewed by a subject-matter expert. Editorial policy

Sources & references

Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy

technicspub.com — Test delivery, eligibility, fees, exam format, passing score, retake policy, score release, time limit, exam information · verified Oct 2026