9.3 Data & Rules Modeling: ERDs, Data Dictionaries, DFDs & Decision Tables

9.3 Data & Rules Modeling: ERDs, Data Dictionaries, DFDs & Decision Tables

[!NOTE] Bridging the Functional and Information Divide: Software applications and business processes are fundamentally mechanisms for transforming, evaluating, and persisting data in accordance with non-negotiable business rules. If a business analyst models workflows without rigorously defining the underlying data structures and decision logic, developers will make arbitrary architectural assumptions, resulting in corrupted databases, calculation discrepancies, and system failure. Domain 3 (Analysis) tests your ability to model data structures across multiple levels of abstraction, audit data flows for structural integrity, and structure complex conditional logic using Decision Tables and Decision Trees.

---

Entity Relationship Diagrams (ERDs): Information Architecture

An Entity Relationship Diagram (ERD) is a visual model that depicts the data entities within a business domain and the structural relationships connecting them. In business analysis, ERDs progress through three distinct tiers of abstraction:

1. Conceptual ERD (Business Domain Level):

   • Developed during early needs assessment and scope definition.
   • Identifies high-level business entities (e.g., Customer, Account, Loan, Collateral) and their fundamental relationships.
   • Completely independent of technology, database platforms, or technical normalization rules. Attributes and keys are omitted.

2. Logical ERD (Business Analysis Specification Level):

   • The primary data artifact owned and refined by the business analyst during requirements analysis.
   • Identifies all business attributes, primary keys, foreign keys, exact cardinality, and optionality.
   • Fully normalized (typically to Third Normal Form / 3NF) to eliminate data redundancy, but remains independent of physical database engines (SQL vs. NoSQL).

3. Physical ERD (Database Engineering Level):

   • Engineered by database administrators (DBAs) and technical architects.
   • Specifies database-specific table names, column data types (e.g., VARCHAR2(64), BIGINT), storage partitions, indexing strategies, foreign key constraints, and performance de-normalization.

Resolving Many-to-Many (M:N) Relationships

A critical competency tested on the PMI-PBA exam is the identification and resolution of Many-to-Many (M:N) relationships during logical data modeling. Relational database engines cannot directly implement an unconstrained M:N relationship without creating extreme data anomalies.

• The Business Scenario: A Physician can treat multiple Patients, and a Patient can be treated by multiple Physicians (an M:N relationship).
• The Problem: Where do we record the specific attributes of an individual consultation—such as the Encounter Date, Primary Diagnosis, and Treatment Notes? If placed on the Physician entity, it duplicates physician data; if placed on the Patient entity, it duplicates patient data.
• The Standard Solution: The business analyst resolves the M:N relationship by introducing an Associative Entity (also known as a Junction Table or Intersection Entity), such as Medical Encounter:
  • The single M:N relationship is split into two One-to-Many (1:N) relationships: Physician (1) to Medical Encounter (N) and Patient (1) to Medical Encounter (N).
  • The Associative Entity holds a composite primary key consisting of the foreign keys of both parent entities (Physician_ID + Patient_ID), along with the unique transactional attributes (Encounter_Date, Diagnosis_Code, Fee_Charged).

   UNRESOLVED MANY-TO-MANY (M:N) RELATIONSHIP
   ┌──────────────┐                             ┌──────────────┐
   │  PHYSICIAN   │◄═══════════════════════════►│   PATIENT    │
   └──────────────┘                             └──────────────┘

   RESOLVED VIA ASSOCIATIVE ENTITY (TWO 1:N RELATIONSHIPS)
   ┌──────────────┐                             ┌──────────────┐
   │  PHYSICIAN   │                             │   PATIENT    │
   │ (Parent 1)   │                             │ (Parent 2)   │
   └──────┬───────┘                             └──────┬───────┘
          │ 1                                          │ 1
          │                                            │
          ▼ N                                          ▼ N
   ┌───────────────────────────────────────────────────────────┐
   │              MEDICAL ENCOUNTER (Associative)              │
   │  - Physician_ID (FK)                                      │
   │  - Patient_ID (FK)                                        │
   │  - Encounter_Date                                         │
   │  - Diagnosis_Code                                         │
   └───────────────────────────────────────────────────────────┘

Crow's Foot Notation: Cardinality and Optionality

Crow's Foot notation uses standardized terminal symbols on relationship lines to indicate two distinct mathematical dimensions:

• Cardinality (Maximum): The maximum number of times an entity instance can be associated with instances of the related entity (either One or Many).
• Optionality / Modality (Minimum): The minimum number of times an entity instance must be associated with instances of the related entity (either Zero [Optional] or One [Mandatory]).

   Symbol Notation:        Interpretation:
   ──||───────             Mandatory One   (Min: 1, Max: 1)
   ──O|───────             Optional One    (Min: 0, Max: 1)
   ──|<───────             Mandatory Many  (Min: 1, Max: N)
   ──O<───────             Optional Many   (Min: 0, Max: N)

---

The Data Dictionary: Authoritative Business Metadata

While an ERD visually models relationships between entities, the Data Dictionary (or metadata catalog) provides the granular, tabular specification of every entity, attribute, and data element within the solution scope.

Essential Metadata Fields

A comprehensive data dictionary authored by a business analyst contains:

1. Attribute / Field Name: The logical business name (e.g., Annual Gross Income) and physical technical field name (e.g., ANN_GROSS_INC_AMT).
2. Business Definition: An unambiguous, standardized description written in business terms and approved by operational SMEs.
3. Data Type and Length: The structural format (e.g., DECIMAL(12,2), VARCHAR(50), ISO-8601 DATE, BOOLEAN).
4. Nullability / Optionality: Specifies whether the field is Mandatory (NOT NULL) or Optional (NULL permitted).
5. Default Value: The system value applied if no explicit input is provided.
6. Validation Rules and Range Constraints: Logical guardrails (e.g., Must be >= 0, Age must be >= 18 as of application date, Regex: ^[A-Z]{2}[0-9]{7}$).
7. Permitted Values / Enumeration: Explicit lists of allowed categorical codes (e.g., ['DRAFT', 'SUBMITTED', 'UNDERWRITING', 'APPROVED', 'DISBURSED']).
8. Authoritative Source (System of Record - SoR): Identifies which enterprise platform originates and maintains master data authority for the element (e.g., Enterprise Master Customer Index [EMCI]).

---

Data Flow Diagrams (DFDs): Level 1, Level 2 and Syntax Rules

As established in Section 9.1, Process 0 on a Context Diagram explodes into a Level 1 DFD, which partitions the system into its primary operational sub-processes. When necessary, complex Level 1 sub-processes further decompose into Level 2 DFDs.

The Four Core DFD Elements

• Processes (Circles or Rounded Rectangles): Transform incoming data into outgoing data. Named using an active verb-noun phrase (e.g., 2.1 Calculate Debt-to-Income Ratio).
• External Entities (Solid Rectangles): Upstream sources or downstream sinks outside system boundaries.
• Data Stores (Open-Ended Rectangles / Parallel Horizontal Lines): Repositories of data at rest (e.g., D1 Applications Database).
• Data Flows (Directional Solid Arrows): Packets of data in motion. Named with specific nouns.

Critical DFD Syntax Anti-Patterns (Exam Traps)

The PMI-PBA examination frequently tests your ability to detect structural and syntax errors on DFDs:

   DFD SYNTAX DEFECT 1: BLACK HOLE
   [Data Inflow A] ───> ┌──────────────────┐
   [Data Inflow B] ───> │ 2.1 Process Data │ (NO OUTFLOW! Data vanishes)
                        └──────────────────┘

   DFD SYNTAX DEFECT 2: MIRACLE
                        ┌──────────────────┐ ───> [Data Outflow A]
   (NO INFLOW! Data     │ 2.2 Generate Rpt │ ───> [Data Outflow B]
    created from air)   └──────────────────┘

   DFD SYNTAX DEFECT 3: GREY HOLE
   [Inflow: EmployeeID] ───> ┌──────────────────┐ ───> [Outflow: Net Pay, Federal Tax,
                             │ 2.3 Compute Pay  │             401k Deduction, YTD Gross]
                             └──────────────────┘ (Inflow insufficient to produce outflow)

   DFD SYNTAX DEFECT 4: ILLEGAL DIRECT CONNECTIONS
   [ Entity A ] ══════════════════════════════════════════════> [ Entity B ]
   (Illegal: External entities cannot communicate via internal DFD)

   [ Entity A ] ══════════════════════════════════════════════> ┌──────────────┐
   (Illegal: External entities cannot access Data Stores directly) │ D1 Database  │
                                                                 └──────────────┘
   ┌──────────────┐ ══════════════════════════════════════════> ┌──────────────┐
   │ D1 Database  │                                              │ D2 Ledger    │
   └──────────────┘ (Illegal: Data stores cannot transfer data)   └──────────────┘

1. The Black Hole: A process that possesses incoming data flows but zero outgoing data flows. Data enters the process and completely disappears. Every legitimate business process must produce an output, notification, or data store write.
2. The Miracle: A process that generates outgoing data flows but possesses zero incoming data flows. Data cannot be spontaneously generated from nothing.
3. The Grey Hole: A process where incoming data flows are factually insufficient to generate the declared outgoing data flows. For example, if a process receives only an Applicant_ID, but outputs a Calculated Amortization Schedule and Tax Deduction Breakdown without connecting to any loan terms data store, a Grey Hole defect exists.
4. Illegal Direct Connections:
   • Entity to Entity: External entities cannot connect directly to one another on a DFD; if they do, that communication is outside the solution scope.
   • Entity to Data Store: An external entity cannot directly write to or read from an internal data store; data must always pass through an intervening process that validates inputs.
   • Data Store to Data Store: A data store cannot move data directly into another data store; an autonomous process must extract, transform, and load the information.

---

Business Rules: Structural versus Behavioral

According to the Business Rules Group (BRG) and PMI standards, a business rule is a formal, actionable statement that defines or constrains some aspect of the business. It asserts business structure or controls the behavior of the enterprise. Business rules are declarative (stating what must be true, not how software should implement it) and must exist independently of technology.

The Dual Taxonomy of Business Rules

1. Structural Rules (Definitional / Fact-Asserting):

   • Establish definitions, classifications, and foundational relationships. They describe how the business organizes its concepts and knowledge.
   • They are "true by definition" and cannot be violated in daily operations.
   • Examples:
     • "A Commercial Fleet is defined as five or more registered motor vehicles owned and operated by the same corporate legal entity."
     • "A High-Value Wire Transfer is defined as any domestic or foreign funds transfer where the principal amount equals or exceeds $50,000.00 USD."
     • "A customer's Age is derived from the difference between the Current Date and the Customer Date of Birth."

2. Behavioral Rules (Governing / Operational):

   • Impose operational constraints, permissions, authorizations, and mandatory workflows on organizational actors and automated systems.
   • They govern day-to-day business conduct and can theoretically be violated (which is why enforcement mechanisms are required).
   • Examples:
     • "A wire transfer exceeding $50,000.00 USD must receive dual authorization from two certified compliance officers prior to release."
     • "A mortgage loan application shall not be conditionally approved if the applicant's Debt-to-Income (DTI) ratio exceeds 43.0%."
     • "Customer financial statements older than 90 calendar days must be rejected during underwriting intake."

The Business Rule Catalog

The ECO names the rule catalog as a core business rule analysis technique, alongside decision tables and decision trees. Where a decision table expresses the logic of one clustered decision, a rule catalog is the enterprise register of every rule: a single authoritative list that survives across projects and is owned by the business, not by any one system.

A rule catalog exists because business rules outlive the systems that implement them. When the same DTI threshold is coded independently into an origination platform, a servicing system, and a monthly regulatory report, a regulator's change to that threshold triggers three uncoordinated changes and at least one inconsistency. Cataloging the rule once, then tracing it to each implementing requirement, converts that scramble into a single controlled update.

The catalog attributes that carry exam weight are Source of Authority, Owner, and Effective Date. A rule sourced from statute cannot be negotiated away in a prioritization workshop, whereas a rule sourced from internal policy can be challenged and changed by its named owner, which is frequently the cheapest available solution to a stated problem. An effective date allows the catalog to hold both the current and a future rule simultaneously, which is exactly what a phased regulatory transition requires.

[!TIP] Rule Catalog Versus Decision Table: Reach for a rule catalog when the scenario involves rules scattered across multiple systems, departments, or documents that need a single owner and source of truth. Reach for a decision table when the scenario involves one decision with several interacting conditions whose combinations must be proven complete. The two are complementary: catalog entries are the inputs a decision table organizes.

---

Decision Tables and Decision Trees

When business rules involve multiple variables, nested conditionals, and divergent operational actions, documenting them in narrative prose creates severe ambiguity, logical gaps, and contradictory requirements. Business analysts utilize Decision Tables and Decision Trees to structure complex multi-condition logic.

The Anatomy of a Decision Table

A Decision Table is structured into four distinct quadrants:

1. Condition Stubs (Top Left): Enumerates all business input variables, criteria, or environmental conditions.
2. Condition Entries (Top Right): Documents the specific values, boolean states (Y/N), or ranges for each condition across individual rule columns ($R_1, R_2, \dots, R_n$).
3. Action Stubs (Bottom Left): Enumerates all possible business actions, system behaviors, or decisions that can be triggered.
4. Action Entries (Bottom Right): Indicates which specific actions execute for each rule column (marked with an X or checkmark).

The Mathematical Law of Rule Completeness

A fundamental test of a Decision Table is mathematical completeness. If a decision table evaluates $k$ independent binary conditions (conditions with two possible values, such as Yes/No), the table must initially evaluate exactly $2^k$ rule columns to achieve complete truth-table coverage:

• 2 binary conditions = $2^2 = 4$ rules.
• 3 binary conditions = $2^3 = 8$ rules.
• 4 binary conditions = $2^4 = 16$ rules.

Logic Optimization: Eliminating Redundancy with "Don't Care" (-)

Once the $2^k$ baseline matrix is constructed, the business analyst inspects the table to eliminate redundancy. If two rules produce the exact same action and differ by only one condition variable, that condition can be collapsed into a "Don't Care" entry (-), halving the required rules for that condition path.

Decision Table Example: Commercial Lending Underwriting & Pricing Matrix

The following table models the multi-variable credit approval and interest rate discount framework for commercial enterprise borrowing:

Decision Trees: Visualizing Sequential Logic

While Decision Tables excel at verifying mathematical completeness across matrix variables, Decision Trees are superior when conditional logic is inherently sequential or chronological. A Decision Tree models conditions as internal decision nodes (diamonds or circles), branches as conditional paths, and leaf nodes as terminal business actions. They are particularly effective when communicating complex logic to non-technical business executives.