4.2 Relational Database Architecture & Normalization

Relational Database Architecture & Normalization

Quick Summary: Relational Database Management Systems (RDBMS) form the structural backbone of enterprise financial accounting, ERP platforms, and general ledgers. CPAs must understand relational database theory—including primary and foreign keys, entity and referential integrity constraints, and database normalization (1NF through 3NF)—to assess whether an organization's systems prevent data anomalies, prevent transaction loss, and maintain strict ACID transactional guarantees.

1. Foundations of Relational Architecture: Tables, Keys & Constraints

Formulated by E.F. Codd in 1970, the relational database model organizes structured data into mathematical relations, universally visualized as two-dimensional tables.

Formal Relational Terminology vs. Common Terms

The Hierarchy of Database Keys

Keys are attributes or groups of attributes that enforce uniqueness and establish relational links between tables:

┌─────────────────────────────────────────────────────────────┐
│                     SUPERKEYS                               │
│  Any set of attributes that uniquely identifies a tuple     │
│  Example: {SSN}, {SSN, Name}, {SSN, Email, Zip}             │
│                                                             │
│    ┌───────────────────────────────────────────────────┐    │
│    │                CANDIDATE KEYS                     │    │
│    │  Minimal Superkeys with no redundant attributes   │    │
│    │  Example: Candidate 1 = {SSN}, Candidate 2 = {Email}│    │
│    │                                                   │    │
│    │    ┌─────────────────────────────────────────┐    │    │
│    │    │              PRIMARY KEY                │    │    │
│    │    │  The chosen unique identifier           │    │    │
│    │    │  (Strictly Unique and NOT NULL)         │    │    │
│    │    └─────────────────────────────────────────┘    │    │
│    └───────────────────────────────────────────────────┘    │
└─────────────────────────────────────────────────────────────┘

• Superkey: Any combination of attributes that uniquely identifies a row in a relation.
• Candidate Key: A minimal superkey—a set of attributes that guarantees uniqueness with zero redundant columns. A table may have multiple candidate keys.
• Primary Key (PK): The specific candidate key chosen by database architects to uniquely identify each tuple in the table. Must be strictly unique and cannot contain NULL values.
• Foreign Key (FK): An attribute in a child table that references the primary key of a parent table, establishing a formal relational link.
• Composite Key: A primary key composed of two or more attributes combined (e.g., Order_ID + Line_Item_ID).
• Surrogate Key vs. Natural Key:
  • Natural Key: A data value with inherent business meaning (e.g., Social Security Number, Taxpayer ID, Vehicle Identification Number).
  • Surrogate Key: A system-generated, artificial unique identifier with zero intrinsic business meaning (e.g., an auto-incrementing integer ID: 10048 or a 128-bit UUID). Surrogate keys protect database relationships when natural business keys change.

Core Relational Integrity Constraints

RDBMS engines enforce four universal integrity constraints that auditors evaluate as key automated IT application controls:

1. Entity Integrity: Mandates that no primary key attribute can evaluate to NULL. Because the primary key serves to uniquely identify a specific tuple, a NULL primary key would imply an entity cannot be identified, violating fundamental relational logic.
2. Referential Integrity: Mandates that every foreign key value in a child table must either (a) match an existing primary key value in the referenced parent table, or (b) be explicitly NULL (if business rules permit optional relationships).
3. Domain Integrity: Mandates that all attribute values conform strictly to defined data types, lengths, character formats, and allowable range checks (e.g., CHECK (Account_Type IN ('Asset', 'Liability', 'Equity', 'Revenue', 'Expense'))).
4. User-Defined / Business Integrity: Custom business logic rules enforced via database triggers, stored procedures, or transaction constraints (e.g., verifying that the sum of debits strictly equals the sum of credits before committing a general ledger journal batch).

Referential Integrity Violations & Audit Risks

When developers disable database foreign key constraints (often done improperly to increase bulk data ingestion speed), severe accounting anomalies occur:

• Orphaned Transactions: If a customer record is deleted from the Customers parent table while their sales orders remain in the Orders child table, those orders become "orphaned." Financial reporting queries joining these tables will omit these sales, leading to an understatement of accounts receivable and revenue.
• Cascading Delete Risks (ON DELETE CASCADE): If a parent table is configured with cascading deletes, deleting a master customer profile will automatically and irreversibly delete all historical invoices, payments, and credit memos associated with that customer. In financial accounting, CPAs expect databases to enforce ON DELETE RESTRICT or ON DELETE NO ACTION, prohibiting the deletion of any master entity that possesses historical transaction records.

2. Database Anomalies in Unnormalized Data

When a database is poorly designed—such as storing flat spreadsheet-like structures with repeating attributes in a single table—the system suffers from data redundancy, which directly generates three catastrophic operational flaws known as modification anomalies:

┌───────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ UNNORMALIZED SALES SPREADSHEET TABLE: `tbl_Sales_Dump`                                                │
├──────────┬────────────┬─────────────┬──────────────┬─────────────┬─────────┬──────────────┬───────────┤
│ Inv_Num  │ Inv_Date   │ Cust_ID     │ Cust_Name    │ Cust_City   │ Item_ID │ Item_Desc    │ UnitPrice │
├──────────┼────────────┼─────────────┼──────────────┼─────────────┼─────────┼──────────────┼───────────┤
│ 1001     │ 2026-10-01 │ C-401       │ Apex Global  │ Chicago     │ ITM-88  │ 4K Monitor   │ $350.00   │
│ 1001     │ 2026-10-01 │ C-401       │ Apex Global  │ Chicago     │ ITM-92  │ USB-C Dock   │ $150.00   │
│ 1002     │ 2026-10-02 │ C-401       │ Apex Global  │ Chicago     │ ITM-88  │ 4K Monitor   │ $350.00   │
│ 1003     │ 2026-10-03 │ C-505       │ Beacon Ltd   │ Boston      │ ITM-14  │ Ergonomic Kbd│ $85.00    │
└──────────┴────────────┴─────────────┴──────────────┴─────────────┴─────────┴──────────────┴───────────┘

The Three Modification Anomalies

1. Insert Anomaly: The inability to record critical business information without artificially inserting unrelated data.
   • Example: In the table above, management cannot enter a newly introduced inventory item (ITM-99: Wireless Mouse, $45.00) into the database until a customer actually purchases it, because the table's composite primary key requires an Inv_Num. Inserting a dummy invoice with NULL or fake customer data violates entity integrity.
2. Update (Modification) Anomaly: When an attribute value that appears redundantly across many tuples is altered, every single instance must be updated. If the update is incomplete, the database enters an inconsistent, corrupt state.
   • Example: If customer C-401 (Apex Global) relocates from Chicago to Dallas, an automated update script that only touches the most recent invoice row will leave prior invoice records showing Chicago. A subsequent sales tax audit query will generate conflicting geographic revenue numbers.
3. Delete Anomaly: When the deletion of one economic fact inadvertently causes the unintended, irreversible loss of an entirely separate business fact.
   • Example: If invoice 1003 was entered in error and must be purged, deleting that row completely erases customer C-505 (Beacon Ltd) and their location from the enterprise database. All corporate knowledge of Beacon Ltd's existence vanishes simply because their only transaction was cancelled.

3. Step-by-Step Normalization Walkthrough (1NF $\rightarrow$ 2NF $\rightarrow$ 3NF)

Database Normalization is a systematic mathematical design procedure that decomposes complex tables into smaller, well-structured relations, eliminating redundancy and curing all three modification anomalies.

The Golden Rule of Normalization

In the memorable phrasing attributed to Bill Kent: "Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key, so help me Codd."

┌────────────────────────┐
│   Unnormalized Form    │  Contains repeating groups, non-atomic values, or nested arrays.
└───────────┬────────────┘
            │  Rule: Make all attributes atomic; eliminate repeating groups; assign a primary key.
            ▼
┌────────────────────────┐
│  First Normal Form     │  All values are atomic. Primary key assigned (often composite).
│        (1NF)           │  Flaw: Contains Partial Dependencies.
└───────────┬────────────┘
            │  Rule: Eliminate partial dependencies (non-key attributes depending on part of composite key).
            ▼
┌────────────────────────┐
│  Second Normal Form    │  In 1NF + Every non-key attribute depends on the ENTIRE primary key.
│        (2NF)           │  Flaw: Contains Transitive Dependencies.
└───────────┬────────────┘
            │  Rule: Eliminate transitive dependencies (non-key attributes depending on other non-key attributes).
            ▼
┌────────────────────────┐
│  Third Normal Form     │  In 2NF + No non-key attribute depends transitively on the primary key.
│        (3NF)           │  Result: Redundancy minimized; Insert, Update, Delete anomalies eliminated!
└────────────────────────┘

Step 1: First Normal Form (1NF)

• Formal Requirements:
  1. Every attribute cell must contain only atomic (indivisible) values. Multi-valued fields (e.g., storing a comma-separated list of items "ITM-88, ITM-92" in a single cell) are strictly prohibited.
  2. There must be no repeating groups or duplicate columns (e.g., Item_1, Item_2, Item_3).
  3. Each record must be uniquely identifiable via an established Primary Key.
• Resolution: We flatten repeating rows so each line item has its own tuple and establish a composite primary key consisting of {Invoice_Num, Item_ID}.
• Why 1NF Is Insufficient: While all fields are atomic, the table contains Partial Functional Dependencies:
  • Cust_Name and Cust_City depend only on Invoice_Num, not on Item_ID.
  • Item_Desc and UnitPrice depend only on Item_ID, not on Invoice_Num.
  • This partial dependency causes massive data redundancy across multi-line invoices.

Step 2: Second Normal Form (2NF)

• Formal Requirements:
  1. The relation must be in First Normal Form (1NF).
  2. The relation must have no partial functional dependencies—every non-key attribute must be fully functionally dependent on the entire primary key. (Note: If a table in 1NF has a single-column primary key rather than a composite key, it is automatically in 2NF!)
• Resolution: Decompose the relation into three separate tables, isolating attributes with their functional determiners:
  1. Invoices (Invoice_Num [PK], Invoice_Date, Cust_ID, Cust_Name, Cust_City)
  2. Items (Item_ID [PK], Item_Desc, Current_List_Price)
  3. Invoice_Line_Items (Invoice_Num [PK, FK], Item_ID [PK, FK], Quantity_Billed, Billed_Price)
• Why 2NF Is Insufficient: Look at the Invoices table. Invoice_Num is the primary key. Cust_ID depends on Invoice_Num. However, Cust_Name and Cust_City depend directly on Cust_ID! This is a Transitive Dependency: $\text{Invoice\_Num} \longrightarrow \text{Cust\_ID} \longrightarrow \text{Cust\_Name, Cust\_City}$ Because of this transitive chain, an update anomaly still exists: if Apex Global changes its address, we still have to update multiple invoice records.

Step 3: Third Normal Form (3NF)

• Formal Requirements:
  1. The relation must be in Second Normal Form (2NF).
  2. The relation must contain no transitive functional dependencies—no non-key attribute can depend on another non-key attribute. Every non-key attribute must depend directly on the primary key alone.
• Resolution: Extract the transitively dependent attributes out of Invoices into a dedicated Customers table:
  1. Customers Table: Cust_ID [PK], Cust_Name, Cust_City
  2. Invoices Table: Invoice_Num [PK], Invoice_Date, Cust_ID [FK referencing Customers]
  3. Invoice_Line_Items Table: Invoice_Num [PK, FK], Item_ID [PK, FK], Quantity_Billed, Billed_Unit_Price
  4. Items Table: Item_ID [PK], Item_Desc, Standard_List_Price

The Accounting Rationale for Historical Billed Prices

Notice that Billed_Unit_Price is retained in Invoice_Line_Items, even though Items has Standard_List_Price.

• CPA Exam Insight: This is not redundant data. Standard_List_Price represents the current catalog price, which fluctuates over time. Billed_Unit_Price represents the immutable historical price contracted at the exact moment the invoice was posted. Overwriting historical sales prices with current catalog prices would illegally alter prior-period audited revenues!

OLTP vs. OLAP (Normalization vs. Denormalization)

4. Transactional Integrity: ACID Properties vs. NoSQL / BASE

In financial accounting, a single economic event frequently alters multiple database tables simultaneously. When a customer pays a $10,000 receivable, the system must execute two distinct operations:

1. UPDATE Cash SET Balance = Balance + 10000;
2. UPDATE Accounts_Receivable SET Balance = Balance - 10000;

If a power failure or network severance occurs between Step 1 and Step 2, the general ledger becomes unbalanced. To prevent this, enterprise relational databases enforce ACID properties.

The Four ACID Properties

1. Atomicity (All or Nothing): Every database transaction is treated as an indivisible atomic unit of work. Either all SQL statements within the transaction block commit successfully to disk, or the entire transaction is completely rolled back to its pre-transaction state. Partial execution is mathematically impossible.
2. Consistency (Preserving Invariants): A transaction can only transition the database from one valid state to another valid state, maintaining all schema constraints, referential integrity rules, triggers, and balance sheet invariants ($Assets = Liabilities + Equity$). If any operation violates a rule, the entire transaction aborts.
3. Isolation (Concurrent Independence): Multiple transactions executing concurrently must execute without interfering with each other. The intermediate uncommitted state of an in-flight transaction must remain invisible to other concurrent transactions.
   • ANSI SQL Isolation Levels (from weakest to strongest):
     • Read Uncommitted: Lowest isolation; allows Dirty Reads (reading uncommitted data that might subsequently roll back).
     • Read Committed: Prevents dirty reads; queries only see committed data. However, Non-Repeatable Reads can occur (re-reading a row within the same transaction yields different data because another transaction committed an update).
     • Repeatable Read: Guarantees that any row read during a transaction cannot be modified by another transaction. However, Phantom Reads can occur (new rows inserted by concurrent transactions matching a WHERE range).
     • Serializable: Highest isolation level; forces concurrent transactions to execute as if they were processed strictly sequentially (serially). Completely eliminates dirty reads, non-repeatable reads, and phantoms, but imposes heavy lock contention and reduced throughput.
4. Durability (Survival Across Failures): Once a transaction commits and acknowledges success, its changes are permanently recorded in non-volatile storage. Even if the database server immediately suffers a catastrophic physical power loss, the transaction survives intact, guaranteed by Write-Ahead Logging (WAL).

BASE Architecture in Distributed NoSQL Systems

While traditional RDBMS suites (PostgreSQL, Oracle, SQL Server) enforce strict ACID guarantees, modern distributed NoSQL systems (MongoDB, Apache Cassandra, DynamoDB) often adopt the BASE model governed by Brewer's CAP Theorem:

• Basically Available: The system guarantees availability across distributed clusters, even during localized node outages.
• Soft State: Data values may drift or shift over time without direct user interaction due to background replication.
• Eventual Consistency: Given sufficient time without new updates, all distributed replicas will eventually synchronize and become consistent.

CPA Assurance Mandate: Core financial accounting ledgers, cash disbursement engines, and securities trading systems must never utilize eventual consistency BASE databases. Financial ledgers strictly mandate ACID compliance with high isolation levels to ensure that trial balances, cash balances, and financial statements remain mathematically precise and auditably defensible.