Data Management, Databases and the REA Model

Introduction

Accounting information systems (AIS) depend on data that are accurate, complete, timely and appropriately controlled. Sales invoices, supplier records, inventory movements, cash receipts and journal entries must be stored so that they can support operations, financial reporting, audit and managerial analysis.

Traditional accounting systems often stored data in separate files owned by individual departments. Modern enterprise resource planning (ERP) systems generally use integrated relational databases. Integration reduces duplication and allows one recorded economic event—such as a sale—to update receivables, inventory, revenue and management reports. However, it also increases the consequences of poor database design, unauthorized access or incorrect master data.

This chapter explains file-based systems, relational database structures, keys, normalization, SQL, data dictionaries, master and transactional data, and the resources-events-agents (REA) model. These concepts connect accounting knowledge with system design and data governance.

Learning Objectives

After studying this chapter, students should be able to:

1. Distinguish file-based data processing from database-based processing.
2. Explain tables, records, fields, primary keys and foreign keys.
3. Interpret relationships and cardinalities in a relational database.
4. Apply first, second and third normal forms to accounting data.
5. Use fundamental SQL concepts to retrieve and update accounting data.
6. Explain the role of data dictionaries and metadata.
7. Distinguish master, reference and transactional data.
8. Construct and interpret a basic REA model.
9. Evaluate database controls, governance responsibilities and emerging technologies.

From Files to Integrated Databases

Files, records and fields

A field is a single data element, such as CustomerID, InvoiceDate or Amount. A record is a collection of related fields describing one instance, such as one customer. A file is a collection of similar records.

In a traditional file-processing environment, departments maintain separate files for their own applications. The sales department might maintain a customer file, while accounts receivable maintains another customer file. This arrangement can create several problems:

• Data redundancy: the same fact is stored more than once.
• Data inconsistency: copies of the same fact contain different values.
• Program–data dependence: changing a file structure requires changes to application programs.
• Data isolation: information is difficult to combine across departments.
• Weak control: different files may have inconsistent validation, security and backup procedures.

A database is an organized collection of related data managed as a shared resource. A database management system (DBMS) is software that defines, stores, retrieves, secures and controls those data. Examples include PostgreSQL, Microsoft SQL Server, Oracle Database and MySQL.

A database does not automatically eliminate duplication or error. Poorly designed databases can still contain redundant fields, invalid relationships and inadequate controls.

The Relational Database Model

A relational database represents data in tables, formally called relations. Each row, or tuple, represents an occurrence of an entity; each column, or attribute, represents a characteristic of that entity.

Consider the following Customer table:

Each column should have a defined domain: the permitted data type, format and range. CreditLimit might be a nonnegative decimal amount in the entity's operating currency, while Status might be limited to approved values such as Active, Suspended and Closed.

Entities and relationships

An entity is a distinguishable object or concept about which data are stored, such as Customer, Product, Sale or Employee. Relationships connect entities. For example, a customer places sales orders, and a sales order contains products.

Common cardinalities are:

• One-to-one (1:1): one row relates to at most one row in another table.
• One-to-many (1:M): one customer may have many sales, but each sale belongs to one customer.
• Many-to-many (M:N): one sale may include many products, and one product may appear in many sales.

A relational database implements an M:N relationship through an associative table. Sale and Product are connected through SaleLine, which may contain Quantity and UnitSellingPrice.

Figure: Relational structure for customer and sales data.

Keys and referential integrity

A candidate key is a minimal attribute, or minimal combination of attributes, that uniquely identifies a row. One candidate key is selected as the primary key. Other candidate keys are alternate keys.

A composite key contains more than one attribute. In SaleLine, the combination of SaleID and LineNo can form the primary key. A foreign key is an attribute that references a candidate key—normally the primary key—of another table.

Key controls include:

• Entity integrity: primary-key values must be unique and not null.
• Referential integrity: a foreign-key value must match an existing referenced key, unless a null value is explicitly permitted.
• Domain integrity: values must conform to their defined domains.

For example, a Sale row should not reference CustomerID C999 if that customer does not exist. When a customer has sales history, deletion may be prohibited or replaced by an inactive status to preserve the audit trail.

Natural and surrogate keys

A natural key has business meaning, such as a government tax identifier. A surrogate key is a system-generated identifier without intrinsic business meaning, such as CustomerID 10482. Surrogate keys are stable and compact, but business identifiers may still require unique constraints. A surrogate key must not become an excuse to permit duplicate customer records.

Normalization

Normalization is the systematic organization of data to reduce harmful redundancy and prevent modification anomalies. It is based on functional dependencies.

If each value of attribute set X determines exactly one value of attribute set Y, then Y is functionally dependent on X:

This notation describes a data constraint, not a numerical calculation.

Update anomalies

Poorly structured tables can cause:

• Update anomalies: a product description must be changed in many rows.
• Insertion anomalies: a new product cannot be recorded until it is sold.
• Deletion anomalies: deleting the last sale of a product removes the only product description.

First normal form

A table is in first normal form (1NF) when each field contains one atomic value for the intended use, repeating groups are removed, and rows can be uniquely identified. A field containing “P10, P12, P18” violates 1NF because it stores several product identifiers in one field.

Atomicity is contextual. A full address might be sufficient for displaying a label but is not atomic if the system must separately analyze city, postal code and country.

Second normal form

A table is in second normal form (2NF) when it is in 1NF and every non-key attribute depends on the whole candidate key, not merely part of a composite candidate key. If all candidate keys contain one attribute, partial dependency cannot arise.

Suppose, for teaching purposes, that each product appears at most once on an invoice and the unnormalized sales table has the composite key (InvoiceNo, ProductID):

Relevant dependencies include:

• InvoiceNo → InvoiceDate, CustomerID
• CustomerID → CustomerName
• ProductID → ProductDescription
• (InvoiceNo, ProductID) → Quantity, UnitPrice

InvoiceDate depends only on InvoiceNo, while ProductDescription depends only on ProductID. These partial dependencies violate 2NF.

Third normal form

A table is in third normal form (3NF) when it is in 2NF and non-key attributes do not depend transitively on a candidate key through another non-key attribute. In the example, InvoiceNo determines CustomerID, which determines CustomerName. Storing CustomerName in the invoice table therefore creates a transitive dependency.

A suitable decomposition is:

• Customer(CustomerID, CustomerName)
• Product(ProductID, ProductDescription)
• Sale(InvoiceNo, InvoiceDate, CustomerID)
• SaleLine(InvoiceNo, LineNo, ProductID, Quantity, UnitPrice)

LineNo permits the same product to appear on separate lines when business practice requires it. The decomposition should be lossless, meaning that joining the tables reconstructs the valid original facts, and should preserve important dependencies where practical.

Denormalization

Denormalization deliberately introduces selected redundancy for performance or reporting. It may be justified in a controlled data warehouse, but it increases synchronization risk. Operational ERP databases are generally normalized, while analytical structures may use star schemas containing fact and dimension tables. Denormalization should be documented, governed and supported by reliable refresh controls.

SQL Concepts for Accounting Systems

Structured Query Language (SQL) is used to define, retrieve and manipulate relational data. SQL implementations differ slightly, so production syntax should be checked against the relevant DBMS.

SQL categories

A simple query is:

SELECT CustomerID, CustomerName, CreditLimit
FROM Customer
WHERE Status = 'Active'
ORDER BY CustomerName;

A join combines related tables:

SELECT s.SaleID, s.SaleDate, c.CustomerName
FROM Sale AS s
JOIN Customer AS c
  ON c.CustomerID = s.CustomerID;

Worked sales-total example

Assume Sale S410 has two lines: four units at $75 each and two units at $120 each. Prices are in dollars per unit and exclude tax and discounts. The sale total is:

where n is the number of sale lines, Quantity_i is the number of units on line i, and UnitPrice_i is dollars per unit. Thus:

The units are consistent: units × dollars per unit = dollars.

The corresponding aggregate query is:

SELECT SaleID, SUM(Quantity * UnitPrice) AS SaleTotal
FROM SaleLine
WHERE SaleID = 'S410'
GROUP BY SaleID;

In practice, calculations may also require discounts, taxes, returns and currency rules. Historical UnitPrice is normally stored on SaleLine because the current product list price may later change.

Transactions and ACID properties

A database transaction is a logical unit of work. Posting a cash receipt may create a receipt record, update invoice allocations and update control totals. These operations should succeed or fail together.

The ACID properties are:

• Atomicity: all transaction operations are completed or none are.
• Consistency: defined constraints remain satisfied.
• Isolation: concurrent transactions do not improperly interfere.
• Durability: committed changes survive failures.

COMMIT makes a transaction permanent; ROLLBACK reverses uncommitted work. ACID consistency means conformance to programmed database rules; it does not guarantee that an economically incorrect but structurally valid amount was entered.

Secure SQL use

Applications should use parameterized statements rather than constructing SQL by concatenating user input. Parameterization reduces SQL-injection risk. Access should follow least privilege: a reporting user may receive SELECT permission without UPDATE or DELETE permission. Sensitive values should also be protected through encryption, logging and controlled administration.

Data Dictionaries and Metadata

A data dictionary is a controlled repository of metadata—data that describe other data. It creates a shared vocabulary for accountants, auditors, developers and data owners.

A dictionary entry may specify:

A technical DBMS catalog records tables, columns, constraints and indexes. A business data dictionary adds meaning, ownership, lineage, sensitivity and calculation rules. Without common definitions, departments may calculate “net sales” differently even when using the same database.

Master, Reference and Transactional Data

Master data describe relatively stable business entities shared across processes. Examples include customers, suppliers, products, employees and chart-of-account codes. Master data change less frequently than transactions but are not permanent.

Transactional data record business events, such as sales, purchases, receipts, payments and inventory issues. They usually include dates, quantities, amounts and links to master data.

Reference data provide controlled classifications, such as currency codes, tax codes, payment terms and country codes.

Master-data errors can affect thousands of transactions. Accordingly, ERP governance commonly requires duplicate checks, approval workflows, effective dates and audit logs. Changes to supplier bank details are especially sensitive and may require independent verification and segregation of duties.

The REA Model

Conceptual foundation

The resources-events-agents (REA) model is an accounting-oriented framework for modeling an enterprise's economic activities. Rather than beginning with debits, credits and journals, REA begins with the underlying economic phenomena.

• Resources are economically valuable objects controlled by the organization, such as cash, inventory or equipment.
• Events are activities that change resources or represent commitments, such as a sale, cash receipt or inventory purchase.
• Agents are people or organizations participating in events, such as customers, employees and suppliers.

REA distinguishes the economic reality from its accounting representation. A sale event can later be summarized into revenue, receivables, cost of sales and inventory entries.

Stock-flow, participation and duality relationships

A stock-flow relationship links an economic event to the resource it increases or decreases. A sale decreases inventory, while a cash receipt increases cash. In the following figure, the stock-flow arrows deliberately run from each event to the affected resource.

A participation relationship links an event to internal and external agents. A duality relationship connects paired give-and-receive events. A sale gives inventory, while a cash receipt receives cash. For credit sales, one cash receipt may settle several sales and one sale may be settled by several receipts, producing a possible M:N relationship implemented through an allocation table.

Figure: Revenue-cycle REA model. Sale → Inventory means the sale decreases inventory; Cash Receipt → Cash means the receipt increases cash.

REA cardinalities and implementation

Cardinalities express business rules, not universal truths. A company permitting installment payments may specify that each Sale is linked to zero or many CashReceipts before settlement, and each CashReceipt may be allocated to one or many Sales. Another company may require a separate receipt for each invoice. Analysts must determine actual policy through interviews, documents and observation.

An implementation could include:

• Inventory(InventoryID, Description, QuantityOnHand)
• Sale(SaleID, SaleDate, CustomerID, EmployeeID)
• SaleLine(SaleID, LineNo, InventoryID, Quantity, UnitPrice)
• CashReceipt(ReceiptID, ReceiptDate, CustomerID, CashAccountID, CashierID)
• ReceiptAllocation(ReceiptID, SaleID, AmountApplied)
• Customer(CustomerID, Name)
• Employee(EmployeeID, Name)
• CashAccount(CashAccountID, AccountName)

REA models can support richer operational analysis than a general-ledger-only design. Managers can ask what resource was exchanged, which event caused the change and which agents participated. Nevertheless, REA does not remove the need for ledgers, financial reporting rules, valuation policies or internal controls.

Illustrative Case: Meridian Office Supplies

Background

Meridian Office Supplies is a fictional regional distributor implementing an ERP system. Its sales, warehouse and accounts receivable departments maintain separate spreadsheets.

Problem

Customer names and credit limits differ between files. Product descriptions are repeated on every invoice line. Cash receipts are entered as reductions to monthly receivable totals without identifying the invoices settled. Management cannot reliably determine which salesperson handled a sale or which receipt settled an invoice.

Analysis

The spreadsheets contain redundancy, partial dependencies and weak identifiers. Product descriptions depend on ProductID rather than on an invoice line. Customer details depend on CustomerID. The absence of a receipt-allocation table prevents the system from representing partial and combined settlements. Uncontrolled credit-limit changes also create fraud and credit risks.

Approach and solution

Meridian designs normalized Customer, Product, Sale, SaleLine, CashReceipt and ReceiptAllocation tables. Surrogate primary keys identify customers and events, while customer tax numbers and product stock codes receive unique constraints where appropriate. Foreign keys enforce valid relationships.

The REA analysis identifies Inventory and Cash as resources; Sale and CashReceipt as events; and Customer, Salesperson and Cashier as agents. The Sale event decreases Inventory, and CashReceipt increases Cash. ReceiptAllocation implements the potentially M:N duality relationship.

A data dictionary defines net sales, invoice date, receipt date and amount applied. Customer creation, credit-limit changes and bank-detail changes require approval. Salespeople cannot approve their own credit-limit overrides. Parameterized SQL and role-based privileges protect the application.

Outcome

Management obtains invoice-level ageing, customer exposure and salesperson analysis from a common database. Duplicate customer creation declines, and cash receipts can be traced to bank deposits and invoices. The normalized operational database becomes the controlled source for general-ledger postings and analytical reporting.

Learning points

Database design must reflect economic events and business rules rather than merely reproduce existing forms. Normalization, REA modeling, metadata and controls are complementary: structural quality alone cannot guarantee correct authorization or accounting treatment.

Governance, Ethics and Regulatory Considerations

Accounting databases contain personal, commercial and financial information. Governance should assign data owners, stewards and custodians. Important controls include:

• role-based access and least privilege;
• segregation of data entry, approval and administration;
• validation and referential-integrity constraints;
• immutable or strongly protected audit logs;
• encryption in transit and at rest;
• tested backups and recovery procedures;
• retention and defensible deletion policies;
• monitoring of privileged access;
• periodic master-data review.

Organizations must comply with applicable privacy, tax, corporate-record and sector-specific requirements. Privacy principles such as purpose limitation and data minimization may conflict with an instinct to retain every field indefinitely. Ethical governance requires collecting only justified data, explaining its use and preventing discriminatory or unauthorized analysis.

An audit trail should identify who performed an action, what changed, when it changed and, where required, why it changed. Audit logs must themselves be protected from alteration. Database administrators should not have unchecked authority over both operational data and monitoring records.

Contemporary Developments

Cloud ERP platforms provide scalable managed databases and standardized workflows, but responsibility is shared between provider and customer. Customers remain responsible for configuration, user access, data quality and lawful processing.

Application programming interfaces connect ERP databases with banks, tax authorities and customer platforms. Integration improves timeliness but introduces risks involving duplicate messages, unauthorized interfaces and incomplete processing. Reconciliation totals, idempotency controls and interface logs help manage these risks.

Artificial intelligence can assist with duplicate-master detection, metadata classification, exception identification and unusual-transaction analysis. AI output should not autonomously modify accounting records without validation, authorization and traceability. Models can produce false positives, inherit biased data and become less reliable when business patterns change.

Design and Control Best Practices

Effective accounting data management generally requires organizations to:

1. Model economic facts and business rules before selecting table structures.
2. Use stable primary keys and enforce foreign-key relationships.
3. Normalize operational data unless controlled denormalization has a documented purpose.
4. define business terms, owners and lineage in a data dictionary.
5. Separate master-data approval from transaction processing.
6. Use database transactions for multi-step postings.
7. Apply parameterized SQL and least-privilege access.
8. Reconcile detailed events to control accounts and external evidence.
9. Test backups, recovery procedures and interface completeness.
10. Review cardinalities and controls whenever business practices change.

Chapter Summary

File-based systems commonly create redundancy, inconsistency and data isolation. Relational databases improve integration by organizing data into tables connected through primary and foreign keys. Entity, referential and domain integrity protect structural validity.

Normalization uses functional dependencies to reduce update, insertion and deletion anomalies. First normal form removes repeating groups, second normal form removes partial dependencies, and third normal form removes inappropriate transitive dependencies. Controlled denormalization may support analytics but requires governance.

SQL defines, retrieves and modifies data. Joins combine related tables, aggregate functions support accounting calculations, and database transactions provide atomicity, consistency, isolation and durability. Data dictionaries document technical and business metadata, while master, reference and transactional data require different controls.

The REA model represents resources, events and agents. Stock-flow relationships show how events affect resources, participation relationships identify involved agents, and duality connects give-and-receive events. In the revenue cycle, a Sale event decreases Inventory and a CashReceipt event increases Cash. Effective accounting databases combine sound modeling with access control, auditability, privacy, master-data governance and professional judgment.

Key Terms

ACID properties: Atomicity, consistency, isolation and durability characteristics of database transactions.

Candidate key: A minimal attribute set capable of uniquely identifying a row.

Cardinality: A rule specifying the number of entity occurrences that may participate in a relationship.

Data dictionary: A controlled repository containing technical and business metadata.

Database management system: Software used to define, store, retrieve and control database data.

Denormalization: Deliberate introduction of selected redundancy for a documented purpose.

Entity integrity: The rule that primary-key values must be unique and not null.

Foreign key: An attribute that references a candidate key in another or the same table.

Functional dependency: A constraint in which one attribute set determines another.

Master data: Relatively stable data describing core business entities.

Metadata: Data describing the meaning, structure, ownership or use of other data.

Normalization: The process of organizing tables to reduce redundancy and anomalies.

Primary key: The candidate key selected to uniquely identify table rows.

REA model: A framework representing resources, economic events and participating agents.

Referential integrity: The requirement that foreign-key values match valid referenced values unless null is permitted.

SQL: A language used to define, query and manipulate relational databases.

Surrogate key: A system-generated identifier without inherent business meaning.

Transactional data: Data recording individual business events and their attributes.

Review Questions

Short Answer Questions

1. Distinguish a field, a record, a file and a database.
2. What is the difference between a primary key and a foreign key?
3. What problem does an associative table solve?
4. Distinguish master data from transactional data.
5. Identify the three fundamental components of an REA model.

Long Answer Questions

1. Compare file-based systems with relational database systems in an accounting environment.
2. Explain first, second and third normal forms and the anomalies they address.
3. Discuss SQL categories, joins, transactions and access controls in an ERP system.
4. Explain how a data dictionary supports accounting consistency, audit and governance.
5. Discuss stock-flow, participation and duality relationships in an REA revenue-cycle model.

Analytical / Application Questions

1. A table contains InvoiceNo, InvoiceDate, CustomerID, CustomerName, ProductID, ProductDescription, Quantity and UnitPrice. Identify the likely functional dependencies and redesign it into 3NF tables.
2. A customer makes one payment covering three invoices, while another invoice is paid in two installments. Design the tables and cardinalities needed to represent these facts.
3. Evaluate the controls required when an AI tool is used to identify duplicate suppliers and recommend record merges in an ERP system.

Multiple Choice Questions with Answers

Question 1

Which problem occurs when two departmental files store different credit limits for the same customer?

A. Data inconsistency

B. Entity integrity

C. Transaction durability

D. Data atomicity

Correct Answer: A. Data inconsistency

Question 2

Which constraint prevents a Sale row from referencing a nonexistent CustomerID?

A. A formatting mask

B. A foreign-key constraint

C. A sort sequence

D. A calculated column

Correct Answer: B. A foreign-key constraint

Question 3

A SaleLine table uses (SaleID, LineNo) to identify each row. This is an example of:

A. a foreign key only

B. a natural single-attribute key

C. a composite primary key

D. an uncontrolled duplicate

Correct Answer: C. a composite primary key

Question 4

A table in 1NF has a composite key (InvoiceNo, ProductID), but ProductDescription depends only on ProductID. Which normal form is violated?

A. First normal form only

B. Second normal form

C. Domain integrity only

D. Entity integrity only

Correct Answer: B. Second normal form

Question 5

Which SQL operation permanently accepts the changes made by a completed transaction?

A. ROLLBACK

B. SELECT

C. COMMIT

D. REVOKE

Correct Answer: C. COMMIT

Question 6

A sale contains 4 units at $75 per unit and 2 units at $120 per unit, excluding tax and discounts. What is the sale total?

A. $390

B. $420

C. $480

D. $540

Correct Answer: D. $540

Question 7

Which item is most appropriately classified as master data?

A. A customer record

B. A cash receipt event

C. An individual invoice line

D. A payment allocation

Correct Answer: A. A customer record

Question 8

In an REA revenue-cycle model, which stock-flow interpretation is correct?

A. Inventory increases the Sale event

B. Cash decreases the Cash Receipt event

C. The Sale event decreases Inventory

D. The Cash resource creates the Customer agent

Correct Answer: C. The Sale event decreases Inventory

Question 9

Why is a ReceiptAllocation table needed when receipts may settle multiple sales and sales may receive multiple payments?

A. To eliminate all foreign keys

B. To implement the many-to-many relationship

C. To replace the Customer master table

D. To store database passwords

Correct Answer: B. To implement the many-to-many relationship

Question 10

Which practice most directly reduces SQL-injection risk in an application?

A. Concatenating user input into SQL commands

B. Granting every user administrator access

C. Using parameterized SQL statements

D. Removing transaction logs

Correct Answer: C. Using parameterized SQL statements