Scenario Lab — 1–7
1 — Many-to-many Enrollment Rule
Situation: Student can enroll in many Courses; Course can contain many Students; business also needs Registration Date.
Best answer: create associative entity Registration between Student and Course and store Registration Date there.
Why: the M:M relationship itself has business data and needs its own modeled structure.
Weaker: repeating Course fields on Student or placing Registration Date on only one parent.
Changed fact: if each Student can belong to only one Course while a Course has many Students, the relationship becomes 1:M and a direct FK can model it without an associative entity solely to resolve M:M.
2 — Too Many “Primary” Keys
Situation: Email, Mobile Number, and Account Number are each unique; team calls all three primary keys.
Best answer: if each is minimal/unique, all are candidate keys; choose one primary and retain the others as alternate keys.
Weaker: call every unique value primary.
Changed fact: if only Account Number is truly unique/minimal, only it is a candidate (unless a surrogate is introduced).
3 — Analytics-First Design
Situation: executives need Admissions Count by Semester, School, Geography, Financial Aid.
Best answer: dimensional modeling with an explicit grain, Admissions fact/measures, and descriptive dimensions.
Weaker: select relational simply because source applications are relational.
Changed fact: if the dominant need becomes enforcing operational admissions transaction rules/update integrity, relational is the stronger primary scheme.
4 — Preserve Address History
Situation: analysts must preserve every Student address version and know the current row.
Best answer: SCD Type 2 — add a new row/version and retain the old row.
Weaker: Type 1, which overwrites history.
Changed fact: if only the current address matters and historical values are explicitly unnecessary → Type 1.
5 — Domain Permits Impossible Values
Situation: HireDate uses Date datatype but future dates are accepted.
Best answer: add a rule-based domain constraint such as HireDate < current date.
Weaker: change datatype alone; Date still permits future values.
Changed fact: if valid values are a fixed set such as EmploymentStatus {Active, Leave, Terminated}, a list domain fits better.
6 — Conceptual Model Overloaded with Columns
Situation: kickoff model includes DBMS datatypes, lengths, indexes, nullability.
Best answer: those are physical details; keep CDM at high-level business concepts/relationships and move implementation details to PDM.
Weaker: call the overloaded model “more complete.”
Changed fact: if the team is intentionally creating a PDM for a selected DBMS, those details are appropriate.
7 — Physical Performance Pressure
Situation: logically sound normalized design requires costly joins; measured read performance is unacceptable.
Best answer: evaluate less disruptive physical options such as indexes/views/partitions; if still justified, consider deliberate denormalization while controlling duplication/quality risk.
Weaker: denormalize logical requirements by default.
Changed fact: if performance already meets requirements, retain the normalized design; redundancy would add risk without benefit.