I85PREP
⌘ K
CDMP study materials
CDMP study resources

CDMP: Data Modeling and Design

CDMP · Work through your exam, one topic at a time.

All guide sections

Data Modeling and Design

Data Modeling and Design

This domain tests whether you can turn requirements into a precise, usable model and explain why each modeling choice was made. Questions cluster around model levels, notation, keys and relationships, normalization, dimensional design, and model quality review.

Core rules and concepts

  • Three model levels. Conceptual: business entities and relationships, no attributes or keys, technology-neutral. Logical: attributes, keys, cardinality, domains, fully normalized, still technology-independent. Physical: tables, columns, data types, indexes, partitioning, denormalization for performance.
  • Model components: entity, attribute, relationship, domain, key (natural, surrogate, primary, alternate, foreign), constraint, supertype/subtype, and associative entity for many-to-many relationships.
  • Cardinality (one-to-one, one-to-many, many-to-many) and optionality (mandatory or optional participation) are separate facts; a relationship can be one-to-many and optional on one side.
  • Normalization: 1NF removes repeating groups and ensures atomic values; 2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies between non-key attributes; BCNF addresses overlapping candidate keys. Normalize logical models to reduce update anomalies; denormalize physical models deliberately for query performance.
  • Dimensional models: declare the grain of the fact table first; facts are numeric, additive measures; dimensions provide the by context. Star schema keeps dimensions one join from the fact; snowflake normalizes dimension hierarchies.
  • Model quality: accuracy, completeness, consistency, precision, and readability; review with business and technical stakeholders; maintain a model repository with versions and lineage.

Worked example

An order line table is keyed by OrderID plus ProductID and contains ProductName and ProductCategory. Is it normalized? ProductName depends only on ProductID, so it is a partial dependency and a 2NF violation. Move product attributes into a Product entity keyed by ProductID; the order line keeps OrderID, ProductID, quantity, and unit price. ProductCategory is transitive from ProductName if left in Product and is moved to a Category entity only if category attributes exist independently. In a warehouse, the same data becomes FactOrderLine at the grain of one row per order line, with dimension keys ProductKey, CustomerKey, and DateKey, and measures Quantity and ExtendedAmount.

Traps that cost marks

  • Putting attributes and primary keys into a conceptual model.
  • Treating denormalization as a modeling error rather than a documented performance trade-off.
  • Confusing cardinality with optionality, or drawing many-to-many without an associative entity.
  • Declaring the grain after choosing dimensions, which produces double-counted facts.
  • Assuming surrogate keys are business keys; surrogate keys are meaningless identifiers and should not be exposed as business codes.