4.2 Relational Database Architecture & Normalization

CPA Information Systems and Controls ISC

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Introduction & CPA Evolution Exam Overview

1.1 CPA Evolution Architecture & ISC Exam Logistics 1.2 Blueprint Navigation & Study Methodology 1.3 Task-Based Simulations & IT Audit Mechanics

2 Chapter 2: IT Infrastructure, Architecture & Cloud Environments

2.1 Hardware, Operating Systems & Network Architecture 2.2 Cloud Computing Service & Deployment Models 2.3 Enterprise Resource Planning (ERP) & AIS Integration 2.4 Cloud Governance, End-User Computing & AIS Process Improvement 2.5 Blockchain, Distributed Ledgers & COSO Internal Control

3 Chapter 3: IT General Controls, SDLC & Business Process Controls

3.1 IT General Controls (ITGC) Architecture 3.2 System Development Life Cycle (SDLC) & Implementation 3.3 Program Change Management & Release Controls 3.4 Business Process & Automated Application Controls 3.5 Business Process Models, Flowcharts, DFDs & BPMN

4 Chapter 4: Data Governance, Relational Databases & Data Extraction

4.1 Data Governance Frameworks, Lineage & Quality Dimensions 4.2 Relational Database Architecture & Normalization 4.3 SQL Fundamentals, Data Extraction & Analytical Pipelines 4.4 The Data Life Cycle, Extraction Methods & Data Integration 4.5 SQL Operators, Aggregate & String Functions: Reviewing Retrieved Data Sets

5 Chapter 5: Security Governance, Frameworks & Threat Landscape

5.1 Cybersecurity & IT Risk Frameworks 5.2 Threat Modeling & Modern Cyber Attack Vectors 5.3 Vulnerability Management, Patching & Security Testing 5.4 The NIST Framework Family: CSF Parts, Privacy Framework & SP 800-53 5.5 COBIT 2019 Governance System, CIS Controls v8.1 & PCI DSS Requirements 5.6 Threat Agents, Attack Techniques & the Stages of a Cyber-Attack 5.7 Cloud, IoT, Mobile & Third-Party Connection Risk

6 Chapter 6: Identity and Access Management (IAM) & Cryptography

6.1 Identity & Access Management (IAM) Architecture 6.2 Cryptography Fundamentals: Encryption & Hashing 6.3 Public Key Infrastructure (PKI), TLS & Network Security 6.4 Network & Endpoint Protection, Defense in Depth & Acceptable Use 6.5 Security Awareness, Control Testing & Security Assessment Reporting

7 Chapter 7: Incident Response, Business Continuity & Privacy Regulations

7.1 Incident Response Lifecycle & Forensic Procedures 7.2 Business Continuity, Disaster Recovery & Resiliency 7.3 Privacy Regulations & Compliance Frameworks 7.4 Data Classification, Sanitization & Privacy Controls 7.5 System Availability Measures, Mirroring & Replication 7.6 Protecting Confidential Data in Development, Operations & Data Loss Prevention

8 Chapter 8: Attestation Standards & the SOC Framework

8.1 AICPA Attestation Standards & SSAE 21 Framework 8.2 The System and Organization Controls (SOC) Reporting Suite 8.3 SOC 1 Engagements & Financial Statement Reporting (AT-C 320) 8.4 Service Auditor vs. User Auditor Responsibilities 8.5 Planning a SOC Engagement: Independence, Materiality, Risk Assessment & Representations

9 Chapter 9: SOC 2 Trust Services Criteria & Reporting Mechanics

9.1 Trust Services Criteria (TSC) for SOC 2 Engagements 9.2 Type 1 vs. Type 2 Reports & Testing Procedures 9.3 Complementary User Entity Controls (CUECs) 9.4 Subservice Organizations & Reporting Approaches 9.5 System Description Criteria, Boundaries, Service Commitments & Complaint Channels

4.2 Relational Database Architecture & Normalization

Key Takeaways Explain This Topic AI

The relational database model organizes data into two-dimensional tables (relations) defined by attributes (columns) and tuples (rows), governed by formal mathematical schema constraints.

Entity integrity requires that every primary key is strictly unique and NOT NULL, while referential integrity requires every foreign key value to match an existing primary key in the referenced parent table or be explicitly NULL.

Database normalization is a systematic design discipline that eliminates redundant data storage and prevents Insert, Update, and Delete anomalies in transactional systems.

The standard normalization progression advances through First Normal Form (1NF: atomic values, no repeating groups), Second Normal Form (2NF: in 1NF with no partial dependencies), to Third Normal Form (3NF: in 2NF with no transitive dependencies).

Relational enterprise systems enforce ACID properties (Atomicity, Consistency, Isolation, Durability) to guarantee ledger transaction integrity, contrasting with distributed NoSQL systems operating under BASE (Basically Available, Soft state, Eventual consistency).

Last updated: September 2026

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

Formal Relational Term Common Database Term Spreadsheet Equivalent Architectural Definition

Relation Table Worksheet / Tab A two-dimensional structure containing unordered tuples sharing identical attributes.

Attribute Column / Field Column A named characteristic or data element with a defined data domain (type and constraints).

Tuple Row / Record Row A single instance of an entity containing an ordered set of attribute values.

Domain Data Type & Range Column Validation Rule The set of all permissible, valid atomic values for a specific attribute.

Cardinality Row Count Number of Rows The total count of tuples currently stored within a relation.

Degree Column Count Number of Columns The total count of attributes comprising the relation schema.

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:

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.

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

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

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

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.

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.

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:

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.

There must be no repeating groups or duplicate columns (e.g., Item_1 , Item_2 , Item_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:

The relation must be in First Normal Form (1NF) .

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:

Invoices ( Invoice_Num [PK], Invoice_Date , Cust_ID , Cust_Name , Cust_City )
Items ( Item_ID [PK], Item_Desc , Current_List_Price )
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 : Invoice_Num ⟶ Cust_ID ⟶ Cust_Name, Cust_City \text{Invoice\_Num} \longrightarrow \text{Cust\_ID} \longrightarrow \text{Cust\_Name, Cust\_City} Invoice_Num ⟶ Cust_ID ⟶ 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:

The relation must be in Second Normal Form (2NF) .

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:

Customers Table: Cust_ID [PK], Cust_Name , Cust_City
Invoices Table: Invoice_Num [PK], Invoice_Date , Cust_ID [FK referencing Customers]
Invoice_Line_Items Table: Invoice_Num [PK, FK], Item_ID [PK, FK], Quantity_Billed , Billed_Unit_Price
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)

Architectural Attribute OLTP (Online Transaction Processing) OLAP (Online Analytical Processing)

Primary Business Goal High-speed, high-volume real-time transaction processing Complex analytical querying, trend analysis, and aggregation

Database Design Highly Normalized (3NF / BCNF) Denormalized (Star Schema / Snowflake Schema)
Data Redundancy Strictly minimized to zero Intentionally introduced to eliminate expensive SQL joins

Performance Profile Fast, lock-free INSERT , UPDATE , and DELETE Ultra-fast SELECT aggregations across millions of rows

Audit Concern ACID compliance, entity integrity, referential integrity ETL data reconciliation, point-in-time snapshot consistency

Loading diagram... Third Normal Form (3NF) Relational Financial Schema (ERD)

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:

UPDATE Cash SET Balance = Balance + 10000;
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

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.

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 ( A s s e t s = L i a b i l i t i e s + E q u i t y Assets = Liabilities + Equity A sse t s = L iabi l i t i es + Eq u i t y ). If any operation violates a rule, the entire transaction aborts.

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.

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.