Database Management and Data Governance for CPA ISC
By SimplyCPA · 11 October 2026
On this page (8)
- Relational databases: Data is stored in tables (relations) linked by keys — a primary key identifies each record; a foreign key links to another table.
- Normalisation: Organising tables to reduce redundancy and anomalies; first, second and third normal forms are the ones to know.
- SQL: SELECT … FROM … WHERE … to query; JOIN to combine tables; GROUP BY with aggregates to summarise.
- Warehouses vs lakes: A data warehouse holds structured, cleaned data for reporting; a data lake holds raw data in any format.
- Governance: Data owners decide access and use, data stewards maintain quality and definitions, and custodians (usually IT) operate the storage and controls.
Relational database basics
- A table stores one type of entity (customers, invoices). Rows are records; columns are attributes (fields).
- A primary key uniquely identifies each row (customer ID).
- A foreign key is a field that refers to the primary key of another table (customer ID on an invoice).
- Referential integrity means a foreign key must match an existing primary key — you can't post an invoice to a customer that doesn't exist.
Normalisation
| Normal form | Rule |
|---|---|
| First (1NF) | Each field holds a single value; no repeating groups |
| Second (2NF) | 1NF, and every non-key field depends on the whole primary key (no partial dependency) |
| Third (3NF) | 2NF, and no non-key field depends on another non-key field (no transitive dependency) |
Normalisation prevents update, insert and delete anomalies — for example, a customer's address stored on every invoice would have to be changed in hundreds of places.
SQL you should recognise
SELECT c.customer_name, SUM(i.amount) AS total_billed
FROM invoices i
JOIN customers c ON i.customer_id = c.customer_id
WHERE i.invoice_date BETWEEN '2026-01-01' AND '2026-12-31'
GROUP BY c.customer_name
HAVING SUM(i.amount) > 100000;
This lists customers billed more than $100,000 in 2026. WHERE filters rows before grouping; HAVING filters groups after aggregation.
Storage architectures
| Operational database (OLTP) | Data warehouse (OLAP) | Data lake | |
|---|---|---|---|
| Purpose | Process day-to-day transactions | Reporting and analysis | Store large volumes of raw data |
| Data | Current, normalised | Historical, cleaned, structured (often denormalised star schemas) | Structured, semi-structured and unstructured |
| Schema | Defined in advance | Defined in advance (schema-on-write) | Applied when read (schema-on-read) |
Data governance
- Data owner — a business leader accountable for a data domain; approves access and use.
- Data steward — maintains definitions, quality rules and metadata day to day.
- Data custodian — usually IT; implements storage, backup and security controls.
- Master data management — one authoritative record for key entities (customers, vendors, products), with controlled changes.
The data life cycle
Creation or capture → storage → use → sharing → archiving → destruction. Controls apply at each stage — for example, input validation at capture, encryption in storage, access control in use, retention schedules for archiving, and secure deletion at the end. Retention must also meet legal requirements (for US tax records, generally at least the period of limitations).
Controls over financial data
- Restrict direct database access — changes should go through the application, not straight into tables.
- Log and review database administrator activity.
- Control changes to master data (vendor bank details) with approval and review.
- Reconcile data moved between systems (ETL) using record counts and totals.
Study the full topic in ISC data management and governance.