Mixed Deck — All DMC Topics Flashcards

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

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.

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

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.

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

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.

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

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.

What is the primary goal of a conceptual data model?

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.

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

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.

What is the primary purpose of a physical data model?

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.

What is denormalization, and when is it appropriate?

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.

In the context of DMC, what does 'domain' mean when applied to an attribute?

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.

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

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.

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

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.

What technique is used in logical modeling to resolve a many-to-many relationship?

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.

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

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.

What does DDL stand for and what does it contain?

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.

What defines a 'grain' in the context of a fact table, and why is it important?

Answer: The precise definition of what a single row in the fact table represents, declared before adding dimensions or measures

The grain defines exactly what one row in the fact table represents — such as one line item on one sales invoice — and must be declared first to ensure all measures and dimensions are consistent.

What is a fact table in dimensional modeling?

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.

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

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.

Which physical design technique is used to handle very large binary objects like documents or images stored in a database?

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.

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

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.

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

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).