3  The Accounting Data Environment

Learning Objectives

After completing this chapter, you will be able to:

1. Explain the structure of a relational database, describe how tables, rows, columns, and data types organize accounting information, and describe how the Charles River database file stores that information.
2. Distinguish between primary keys and foreign keys and explain how they establish relationships between tables in an accounting database.
3. Read and interpret an Entity-Relationship diagram using crow’s foot notation to identify tables, relationships, and cardinality.
4. Map familiar accounting structures such as the chart of accounts, general ledger, and sub-ledgers to their representations as related tables in a database.
5. Compare simple and complex table groups within the Charles River database and trace how cross-group bridge keys connect operational activity to the posted general ledger.
6. Explain why the source-document columns in GLEntry act as a logical reference to many possible tables rather than a declared foreign key, and use them to trace a transaction across table groups.

Chapter slides View slides Download PowerPoint

Opening Scenario

You are back in the staff accountant role from Chapter 1. The controller’s question about why gross margins on the Furniture product line declined in the most recent quarter is still open, and she has asked you to start on it. She explains that everything you need is stored in the company’s database and hands you a printout of the database diagram. The diagram shows dozens of tables organized into groups with names like “Order-to-Cash,” “Accounting Core,” and “Manufacturing.” Lines connect the tables in a web of relationships, and symbols at the endpoints of each line indicate how many rows on each side can be associated. You recognize some of the table names from your accounting courses, such as “Account,” “GLEntry,” and “SalesInvoice,” but you cannot yet tell which tables hold furniture revenue, which hold its cost, or how a sale in one table connects to its postings in another. You realize that before you can run any queries or build any reports, you need to understand how the company’s accounting data is organized within this database. This chapter provides that understanding. It introduces the relational database model, explains how accounting data is organized within it, and walks you through the Charles River database design that you will use throughout this book.

3.1 From Accounting Records to Database Tables

In your introductory accounting courses, you learned to work with familiar structures such as the chart of accounts, the general journal, the general ledger, and various sub-ledgers. You recorded transactions as debits and credits, posted them to T-accounts, prepared trial balances, and produced financial statements. These structures have served the accounting profession for centuries, and they remain the conceptual foundation of financial record-keeping.

What has changed is how organizations store and access these structures. In a manual system, the general journal is a physical book. In a spreadsheet-based system, it is a worksheet. In a modern organization like Charles River, it is a table in a relational database. The chart of accounts is another table. The accounts receivable sub-ledger is yet another table. These tables are connected to one another through defined relationships that mirror the connections you already understand from accounting. The CustomerID on a sales invoice connects that invoice to a specific customer record. The AccountID on a general ledger entry connects that entry to a specific account in the chart of accounts. These connections exist in manual systems as cross-references and posting notations. In a database, they are formalized as structural features that the database can enforce and that analytical tools can use.

Understanding how accounting data is organized in a database is a prerequisite for everything that follows in this book. When you write SQL queries in Part III, you will need to know which tables contain the data you need and how those tables connect to one another. When you build data models in Power BI in Part IV, you will define relationships between tables that determine how your calculations and visualizations behave. Even in the Excel chapters of Part II, understanding the source structure helps you anticipate what the data will look like when you import it and what preparation steps will be necessary. The concepts in this chapter are not abstract. They are the practical foundation for every analytical task you will perform.

TipIn Practice

The profession now counts database knowledge as part of an accountant’s preparation. The model curriculum that the American Institute of Certified Public Accountants (AICPA) and the National Association of State Boards of Accountancy (NASBA) developed to prepare aspiring CPAs for the CPA Exam covers relational concepts in its core, and its Information Systems and Controls discipline adds objectives on the characteristics of relational databases, the attributes of data repositories, and the capabilities of data extraction tools (AICPA & NASBA, 2021). In a survey of the leaders of accounting departments accredited by AACSB International (the Association to Advance Collegiate Schools of Business), Andiola et al. (2020) found those departments building technology and data analytics skills into their curricula, although the courses and methods varied from program to program. An auditor who can read a database diagram can find the tables a procedure needs before asking the client for data.

3.2 The Relational Database Model

A relational database stores data in a collection of tables that are related to one another through shared values. The relational model was proposed by Edgar F. Codd (1970), and relational databases are now widely used in business information systems, including enterprise resource planning systems, accounting software packages, and transaction processing systems. Understanding this model is therefore essential for any accountant who needs to access data directly rather than relying on pre-built reports.

3.2.1 Tables, Rows, and Columns

The fundamental unit of a relational database is the table. A table stores information about one type of entity or event. In an accounting context, a table might store information about customers, products, sales orders, journal entries, or accounts. Each table has a name that identifies what it contains. The Charles River database, for example, has a table called Customer that stores information about every customer the company does business with, and a table called SalesOrder that records every sales order the company has received.

A table is organized into rows and columns. Each row represents a single instance of the entity or event that the table describes. In the Customer table, each row represents one customer. In the SalesOrder table, each row represents one order. Each column represents a single attribute of that entity or event. In the Customer table, columns include CustomerID, CustomerName, CustomerSegment, Region, CreditLimit, and PaymentTerms. In the SalesOrder table, columns include SalesOrderID, CustomerID, OrderDate, RequestedDeliveryDate, SalesRepEmployeeID, and Status. Figure 3.1 labels these parts on a sample of real Charles River customers.

Figure 3.1: Anatomy of a Database Table.

If this structure looks familiar, it should. A database table is structurally identical to a well-organized spreadsheet or to the tidy data format described in Chapter 2. Each variable occupies its own column, and each observation occupies its own row. The difference is that a database defines this structure in advance. When a table is created, the database records the name of every column and, depending on the system, its data type and constraints. Every row has the same columns, which prevents the kind of structural drift that occurs in spreadsheets, where a user might insert a comment in a data column or merge cells in ways that break the tabular format.

3.2.2 Data Types

Every column in a database table has a data type that specifies what kind of values the column is meant to hold. The most common data types in accounting databases include integers (whole numbers used for identifiers and counts), decimal or numeric values (numbers with fractional parts used for monetary amounts, prices, and quantities), text or character strings (used for names, descriptions, and codes), dates (used for transaction dates, due dates, and posting dates), and Boolean values (true or false values used for status flags such as whether a supplier has been approved).

Data types matter for accounting analytics because they determine how a tool interprets and calculates with a column’s values. A sum is meant for a column defined as a decimal, not one defined as text: depending on the tool, summing text values fails, returns zero, or quietly converts the text, and none of these is a result to rely on. You can sort dates chronologically but only if the values are stored in a form that sorts correctly. Many of the data quality problems discussed in Chapter 2, such as a transaction amount stored as text because it includes a currency symbol, are essentially data type problems.

How a particular database stores these types depends on the database system. The Charles River database is provided as a SQLite file, and SQLite stores every value in one of five storage classes: INTEGER for whole numbers, REAL for numbers with fractional parts, TEXT, BLOB for binary data (which Charles River does not use), and NULL for a missing value. A column’s declared type is a preference rather than a rule: SQLite converts a value to that type where it can but does not reject a value of another type, so the consistent types you find in Charles River are a convention the data follows. SQLite has no separate date or Boolean type, so Charles River stores dates as text in the year-month-day format (such as 2025-03-05), which sorts in chronological order, and stores true or false flags as the integers 1 and 0. Table 3.1 summarizes the common data types and how the Charles River file stores each one. When you import the data into Excel or Power BI, those tools convert the stored values into their own date, number, and text types, so it is worth checking that each column arrives with the type you expect.

Table 3.1: Common Data Types and How the Charles River Database Stores Them

Data Type	Description	How Charles River Stores It	Charles River Example
INTEGER	Whole numbers without decimal places, used for identifiers, codes, and counts.	INTEGER	SalesOrderID in SalesOrder (for example, 1697). AccountNumber in Account (for example, 4010).
DECIMAL / NUMERIC	Numbers with fractional parts, used for monetary amounts, prices, and quantities.	REAL (floating point)	StandardCost and ListPrice in Item (for example, 229.66 and 382.45). Debit and Credit in GLEntry.
TEXT / VARCHAR	Character strings used for names, descriptions, codes, and classifications.	TEXT	CustomerName in Customer (for example, “Walters PLC”). OrderNumber in SalesOrder (for example, “SO-2024-001697”).
DATE	Calendar dates, sometimes with a time, used for transaction, due, and posting dates.	TEXT in year-month-day form	InvoiceDate in SalesInvoice (for example, 2025-03-05). CreatedDate in GLEntry, which also includes the time.
BOOLEAN	True or false values used for status flags.	INTEGER, with 1 for true and 0 for false	IsApproved in Supplier. IsActive in Customer and Item.

WarningWatch out

A column that contains numbers is not necessarily a quantity. The Charles River Account table stores account numbers such as 1010 and 4010 as integers, so Excel, SQL, and Power BI will happily add them together or calculate their average. The result is meaningless, because account numbers are identifiers, not amounts. Other systems store codes like these as text precisely to prevent that kind of arithmetic and to preserve leading zeros, which a numeric type would drop from a code such as 0450. When you encounter a column of numbers in a database, consider whether the values represent quantities to be calculated or codes to be matched.

3.3 Keys and Relationships

The power of a relational database comes not from individual tables but from the connections between them. In an accounting system, transactions do not exist in isolation. A sales order is placed by a customer, fulfilled from a warehouse, and contains items from the product catalog. A journal entry debits one or more accounts and credits one or more accounts, and every account it names appears in the chart of accounts. These connections are represented in a database through keys.

3.3.1 Primary Keys

A primary key is a column, or a combination of columns, that uniquely identifies each row in a table. No two rows in the same table can have the same primary key value, and the primary key cannot be blank. In the Charles River Customer table, the primary key is CustomerID. Every customer has a unique CustomerID, and no two customers share the same value. In the SalesOrder table, the primary key is SalesOrderID. Every order has a unique SalesOrderID that distinguishes it from every other order in the table. A primary key made of two or more columns is called a composite key, and many accounting systems use them. Every Charles River table uses a surrogate key instead: a single integer column that exists only to identify rows.

Primary keys serve the same purpose in a database that unique identifiers serve in accounting. An invoice number uniquely identifies an invoice. A check number uniquely identifies a payment. An employee number uniquely identifies an employee. The database enforces primary key uniqueness automatically, meaning it will reject any attempt to insert a second row with an existing primary key value. The Charles River database also enforces uniqueness on many business document numbers through unique indexes, a form of unique constraint, including OrderNumber in SalesOrder, InvoiceNumber in SalesInvoice, EntryNumber in JournalEntry, and AccountNumber in Account. These unique indexes are a form of data integrity control. Notice what is not constrained: the InvoiceNumber in the PurchaseInvoice table is the number printed on the supplier’s invoice, and nothing prevents the same supplier invoice number from being recorded twice. That is exactly how the duplicate supplier invoice numbers you examined in Chapter 2 entered the data.

3.3.2 Foreign Keys

A foreign key is a column in one table that references the primary key of another table, or of the same table, as you will see in the chart of accounts. If the primary key it references is a composite key, the foreign key has the same number of columns. Foreign keys create the connections between tables that make a relational database relational. In the Charles River SalesOrder table, the column CustomerID is a foreign key that references the CustomerID primary key in the Customer table. This relationship means that every order is linked to a specific customer. If you know the CustomerID on an order, you can look up that customer’s name, segment, region, and credit limit in the Customer table. Figure 3.2 shows this connection with real orders.

Figure 3.2: Primary Keys and Foreign Keys Illustrated.

Foreign keys mirror the cross-references that exist in accounting records. When you post a journal entry to the general ledger, the account number on the entry refers to a specific account in the chart of accounts. That account number is functioning as a foreign key. When a sales invoice references a customer number, that customer number links the invoice to the customer master file. A foreign key does not always share the name of the primary key it references. In the BillOfMaterialLine table, for example, ComponentItemID is a foreign key that references ItemID in the Item table, and its name describes the role the item plays in the relationship.

Chapter 2 introduced referential integrity, the requirement that every foreign key value that is not blank corresponds to an existing primary key value in the referenced table. The link between a primary key and a foreign key is what gives that requirement its meaning. If a customer is deleted from the Customer table while orders referencing that customer still exist in the SalesOrder table, the orders now reference a customer that does not exist, and a join that keeps only matching rows silently drops those orders from the result. A database can prevent this with foreign key constraints, which reject any insertion, update, or deletion that would leave an orphaned reference. A blank foreign key (a NULL in the database, an empty cell in Excel) is a different matter. Where a relationship is optional, as on the invoice lines for design services, which have no shipment line, the blank is allowed rather than orphaned.

It is important to distinguish the relationships a database is designed around from the constraints it actually enforces. The Charles River database, like many data extracts, data warehouses, and analytics datasets, declares its primary keys but does not declare its foreign keys. The relationships exist in the data, and the tests you performed in Chapter 2 found that every AccountID in GLEntry matches an account in the chart of accounts, but nothing in the file would stop an orphaned record from being added. These undeclared relationships are sometimes called logical foreign keys. They are why analysts test referential integrity rather than assume it, and they have a practical consequence: the file supplies no declared relationships for a tool to import. Power BI can still suggest relationships from matching column names, but each suggestion must be checked against the diagram, and in this book you will define and test the relationships of every data model yourself.

NoteConnecting the Dots

In the SQL chapters of Part III, you will write JOIN statements that use exactly these key relationships to combine data from multiple tables. A JOIN on SalesOrder.CustomerID = Customer.CustomerID retrieves the customer name for each order. Understanding keys now will make JOIN syntax intuitive rather than mysterious when you encounter it.

3.4 Relationships and Cardinality

The relationship between two tables has a cardinality, meaning it specifies how many rows in one table can be associated with a single row in the other table. Three types of cardinality are common in accounting databases, and Figure 3.3 illustrates each one with Charles River tables.

A one-to-many relationship means that one row in the first table can be associated with many rows in the second table, but each row in the second table is associated with only one row in the first table. This is the most common relationship type in accounting data. One customer can place many sales orders, but each sales order belongs to one customer. One account in the chart of accounts can have many general ledger entries, but each entry is posted to one account. One item in the product catalog can appear on many sales order lines, but each line references one item. In the Charles River database, the relationship between Customer and SalesOrder is one-to-many. Walters PLC, one of the company’s strategic customers, has placed dozens of orders, and each of those orders carries the same CustomerID linking it back to that single customer record.

A one-to-one relationship means that each row in the first table is associated with at most one row in the second table, and vice versa. One-to-one relationships are less common in accounting databases, and they usually arise when a later stage of a process is recorded in its own table. In the Charles River database, each work order is closed at most once, and the close is recorded in the WorkOrderClose table along with the manufacturing variances it posts. A work order that is still in production has no close record yet, so the relationship is more precisely one-to-zero-or-one. Similarly, each production completion in the ProductionCompletion table has exactly one line in the ProductionCompletionLine table.

A many-to-many relationship means that each row in the first table can be associated with many rows in the second table, and each row in the second table can also be associated with many rows in the first table. In accounting, consider the relationship between sales orders and items. One sales order can contain many items, and one item can appear on many sales orders. Relational databases cannot directly represent a many-to-many relationship. Instead, they use a junction table (also called a bridge table or associative table) that sits between the two tables and converts the many-to-many relationship into two one-to-many relationships. In the Charles River database, the SalesOrderLine table serves this purpose. It sits between SalesOrder and Item, with each row representing one line on one order, and it has a foreign key to SalesOrder (SalesOrderID) and a foreign key to Item (ItemID). The CashReceiptApplication table plays the same role between cash receipts and sales invoices: one customer check can pay several invoices, and one invoice can be paid by several receipts.

Figure 3.3: The Three Types of Cardinality.

Cardinality has a companion property called optionality, which describes whether a row must participate in a relationship at all. Every sales order must belong to a customer, but a customer does not have to have placed an order: some Charles River customers have no orders yet. Every sales order has at least one line, but some items in the catalog have never been sold. Optionality matters for analysis because a relationship that allows zero matches will leave some rows out of a combined result unless you account for them, which is why a list of customers built by joining to their orders can silently omit the customers who have never ordered.

Understanding cardinality is important for analytics because it affects the results you get when you combine tables. When you join a one-to-many relationship in SQL, or merge two tables in Power Query and expand the result, each row on the one side is repeated for every matching row on the many side. If you join the Customer table to the SalesOrder table and a customer has 15 orders, that customer’s information will appear 15 times in the output, once for each order. This is correct and expected behavior, but if you are not aware of the cardinality, you might accidentally count the customer 15 times when calculating the number of customers. A relationship in a Power BI data model works differently: it keeps the two tables separate and carries filters from one to the other, yet the same question of grain still decides what a count or a sum means. Many analytical errors in accounting result from misunderstanding the cardinality of a relationship, and the ER diagrams described in the next section help you avoid those errors by making cardinality visible.

WarningWatch out

The most common mistake when working with related tables is double-counting. If you join the Customer table to SalesOrder to SalesOrderLine, a single customer with 5 orders and 20 line items across those orders will appear 20 times in the result set. Summing the order-line amounts at the line-item level is correct, because each line appears once, but those amounts are what was ordered, not revenue: recognized revenue comes from the invoice lines and their postings, within the reporting period. Counting the customer at the line-item level overstates the customer count by a factor of 20. Always consider the cardinality of each join before performing aggregations.

3.5 Entity-Relationship Diagrams

An Entity-Relationship diagram, commonly called an ER diagram, is a visual representation of the tables in a database and the relationships between them. ER diagrams are the maps that database designers, analysts, and auditors use to understand the structure of a data environment. Reading an ER diagram is a skill you will use repeatedly in this book and in professional practice.

3.5.1 How to Read an ER Diagram

In the notation used throughout this book, each table appears as a rectangle. The rectangle has a header bar containing the table name and a body listing the column names. Primary key columns are marked with “PK” and foreign key columns are marked with “FK.” Relationships between tables are shown as lines connecting the table that holds the primary key to the table that holds the foreign key. Tables from another table group are drawn with a gray header, and relationships that cross into another group are drawn as heavier amber lines. The source-document trace, which is not a foreign key at all, is drawn as a dashed amber line.

The endpoints of each relationship line use crow’s foot notation to indicate cardinality and optionality, as Figure 3.4 shows. A perpendicular bar means “one,” and a three-pronged fork, which resembles a crow’s foot, means “many.” A circle means “zero,” which marks the relationship as optional. Each endpoint combines two symbols. A pair of bars means exactly one, a circle with a bar means zero or one, a bar with a crow’s foot means one or many, and a circle with a crow’s foot means zero or many.

Figure 3.4: Reading Crow’s Foot Notation.

Consider a concrete example. The Charles River ER diagram shows a line between the Customer table and the SalesOrder table. The Customer end of the line has two bars, meaning exactly one: every order belongs to exactly one customer. The SalesOrder end has a circle and a crow’s foot, meaning zero or many: a customer can have many orders or none at all. Reading the relationship aloud, you would say “one customer can have zero or many sales orders, and each sales order belongs to exactly one customer.” Looking at the columns, you can see that CustomerID is the primary key of the Customer table and a foreign key in the SalesOrder table. This tells you that the connection between the two tables is made through the CustomerID column, and that to retrieve a customer’s name alongside their orders, you would match these two columns.

ER diagrams serve the same purpose for a database that a chart of accounts serves for an accounting system. The chart of accounts tells you how the organization classifies its financial transactions. The ER diagram tells you how the database organizes its data. Just as you would consult the chart of accounts before preparing a journal entry, you should consult the ER diagram before writing a query or building a data model.

TipIn Practice

An audit team can use the client’s ER diagrams or data dictionaries during the planning phase of an engagement. Understanding the client’s database structure helps the audit team identify which tables contain the data needed for analytical procedures, which relationships need to be traversed to connect transactions to their supporting details, and where potential data integrity risks exist. The AICPA’s standards for audits of financial statements require the auditor to understand the client’s information system, including its data, how information flows from the initiation of a transaction to the general ledger and the financial statements, and the IT environment that supports it (AICPA, 2021).

3.6 The Accounting Data Model

The relational concepts described so far apply to databases in any domain. What makes an accounting database distinctive is the specific set of entities it stores and the relationships between them. This section shows how the familiar structures of accounting map to tables and relationships in the Charles River database. Seeing these connections will help you move between accounting concepts and database structures without difficulty.

3.6.1 The Chart of Accounts as a Table

The chart of accounts is the organizational backbone of any accounting system. It defines every account the organization uses to classify its financial transactions, typically organized into five major categories: assets, liabilities, equity, revenues, and expenses. In a database, the chart of accounts is stored as a table where each row represents one account. Columns typically include an account identifier, an account name, an account type classification, and a parent account reference that defines the hierarchical structure.

In the Charles River database, the chart of accounts is stored in the Account table. Each row contains an AccountID, an AccountNumber, an AccountName, an AccountType (Asset, Liability, Equity, Revenue, or Expense), an AccountSubType for finer classification, a NormalBalance of debit or credit, and a ParentAccountID that places the account within the hierarchy. The ParentAccountID is a foreign key that points to another row in the same table, creating what database designers call a self-referencing relationship or recursive relationship. The Cash and Cash Equivalents account (1010), for example, has Assets (1000) as its parent. Deeper levels appear under Fixed Assets and Long-Term Liabilities. Furniture and Fixtures (1110), for example, sits under Fixed Assets (1100), which in turn sits under Assets. Summary accounts such as Assets and Fixed Assets are marked Header in the AccountSubType column, and no entries are posted to them. This hierarchical structure allows the database to represent the multi-level account groupings that appear on financial statements, as Figure 3.5 shows.

Figure 3.5: The Chart of Accounts as a Database Table.

3.6.2 The General Ledger as a Related Table

The general ledger records every financial transaction as a series of debit and credit entries. In a database, the general ledger is typically stored as a table where each row represents one side of a journal entry: a single debit or a single credit. Columns include the posting date, the account reference, the debit amount, the credit amount, the fiscal year and period, identifiers linking the entry to its source, and a cost center reference.

The relationship between the general ledger table and the chart of accounts table is one-to-many. One account can appear in many general ledger entries, but each general ledger entry is posted to one account. Some accounts, such as the summary header accounts, have no entries at all, so more precisely the relationship is one-to-zero-or-many. The account reference in the general ledger table is a foreign key that points to the primary key of the chart of accounts table. This relationship is the database equivalent of posting a journal entry to a T-account. When you total the debits and the credits for a given account across the general ledger entries up to a date, the difference between the two totals is that account’s balance at that date, which is what a trial balance reports.

In the Charles River database, the GLEntry table stores the general ledger, and it is by far the largest table in the database. Each row has an AccountID field that references the Account table, establishing the connection between individual entries and the chart of accounts. This relationship is what makes it possible to produce a trial balance from the database. You group the GLEntry rows posted up to the trial balance date by account, sum the Debit and Credit columns for each group, and take the difference as the account’s balance. At a year-end, the closing entries dated that day decide which trial balance you get. Leave them out for a pre-closing trial balance, and keep them for a post-closing one.

3.6.3 Sub-Ledgers and the Source-Document Trace

Chapter 2 introduced sub-ledgers, the detailed records for specific account categories, such as individual customer invoices and payments, that roll up to a control account in the general ledger. The control account balance should equal the sum of the sub-ledger balances.

In the Charles River database, the sub-ledgers are represented by the transactional cycle tables. The SalesInvoice, CashReceipt, and CashReceiptApplication tables in the O2C group function as the accounts receivable sub-ledger. The PurchaseInvoice and DisbursementPayment tables in the P2P group function as the accounts payable sub-ledger. In the Payroll and Time group, each row of the PayrollRegister table records one employee’s pay for one pay period, and payroll reaches the ledger through summary postings, payment postings, and liability remittances. Each of these business events generates entries in the GLEntry table.

Chapter 1 introduced source-to-ledger traceability, and Chapter 2 used it as a data quality control. It is the ability to follow a ledger entry back to the business document that created it through the SourceDocumentType, SourceDocumentID, and SourceLineID columns of GLEntry. From a database perspective, these columns are unusual. SourceDocumentType names the table the posting came from, such as SalesInvoice, PurchaseInvoice, PayrollPayment, or JournalEntry, and SourceDocumentID holds the primary key of a row in that table. Payroll summary postings are the one exception: their type, PayrollSummary, names a summary of a pay period by cost center rather than a table, and their SourceDocumentID is a code rather than the key of a row. Because the same SourceDocumentID column points to a different table depending on the value in SourceDocumentType, no single foreign key can describe the relationship. Figure 3.6 shows this pattern.

Figure 3.6: The Relationship Between the General Ledger and Sub-Ledgers.

NoteConnecting the Dots

In the audit analytics chapters of Parts II and III, you will use the SourceDocumentType column in GLEntry to isolate journal entries made by the accounting team from entries generated automatically by sales invoices, purchase invoices, and payroll. This source-document trace is one of the most important structures for audit analytics because it allows you to test whether the amounts reported in the financial statements are supported by underlying operational transactions.

3.7 Exploring the Charles River Schema by Table Group

Now that you understand the components of a relational database, it is time to examine how the Charles River database is organized. The database is arranged into table groups, each covering a different business function, and the groups range from a handful of reference tables to large transactional cycles. Studying several groups together will show you how the same relational principles apply at different scales within a single company.

3.7.1 Table Groups as Organizational Units

A table group is a logical grouping of related tables within a database, organized by business function. Table groups serve the same organizational purpose that departments serve in a company or that schemas serve in databases like Microsoft SQL Server: they cluster related activities together and make it easier to find what you need. In the Charles River database, the O2C (Order-to-Cash) group contains the tables related to customer orders, shipments, invoices, and collections. The Manufacturing group contains the tables related to bills of material, work orders, material usage, and production. The Accounting Core group contains the chart of accounts, journal entries, and general ledger.

The groups include the business cycles, such as Order-to-Cash, Procure-to-Pay, Manufacturing, and Payroll and Time, the Accounting Core, and reference and planning groups such as Master Data. Table 1.3 in Chapter 1 lists every group and its key tables. The size of each group reflects the complexity of the business process it covers. A reference function like Master Data needs only a few tables, while a transactional cycle like Order-to-Cash, which spans pricing, ordering, shipping, invoicing, commissions, returns, and cash collection, is the largest group in the database.

3.7.2 Simple Table Groups: Master Data

The Master Data group is the simplest starting point for understanding the Charles River database. Its tables are Item, Warehouse, and Employee. Each table stores reference information about one type of entity that is shared across the rest of the database.

The Item table stores product information, including ItemID, ItemName, ItemGroup, SupplyMode (Purchased or Manufactured), StandardCost, and ListPrice. Its ItemGroup column classifies items into the four product groups the company sells (Furniture, Lighting, Textiles, and Accessories) and into the Services, Raw Materials, Packaging, and Capex (capital equipment) groups. The Warehouse table stores location information for inventory storage. The Employee table stores personnel information used across sales, manufacturing, payroll, and accounting, and its ManagerID column is a self-referencing foreign key that points to each employee’s manager in the same table.

Master Data also carries accounting information. Each product records the accounts it posts to through InventoryAccountID, RevenueAccountID, and COGSAccountID, all of which reference the Account table. When Charles River sells a desk, the item’s account mapping is what directs the revenue to Sales Revenue - Furniture and the cost to COGS - Furniture.

What makes Master Data important is how it connects to everything else in the database. The ItemID column appears as a foreign key in tables across multiple groups: SalesOrderLine in the O2C group, PurchaseOrderLine in the P2P group, WorkOrder in the Manufacturing group, and many others. The EmployeeID column appears under role-specific names in tables across the database, such as SalesOrder (SalesRepEmployeeID), JournalEntry (CreatedByEmployeeID and ApprovedByEmployeeID), PurchaseOrder (CreatedByEmployeeID), PayrollRegister, and LaborTimeEntry. These shared columns are called cross-group bridge keys because they allow analysts to join data across different table groups. When you want to know which items a particular customer ordered, you join the O2C tables to the Item table through the ItemID bridge. When you want to know which employee approved a manual journal entry, you join the Accounting Core to the Employee table through EmployeeID. Table 3.2 compares Master Data with two larger groups.

Table 3.2: Comparison of Three Charles River Table Groups

Feature	Master Data	Order-to-Cash	Accounting Core
Key tables	Item, Warehouse, Employee	Customer, SalesOrder, SalesOrderLine, Shipment, ShipmentLine, SalesInvoice, SalesInvoiceLine, CashReceipt, CashReceiptApplication, and the pricing, service, commission, and returns tables	Account, JournalEntry, GLEntry
Primary relationships	One-to-many from each master table outward to transactional tables in other groups. One self-reference (Employee.ManagerID) and links from Item to the Account table.	Long chains of mostly one-to-many relationships following the revenue cycle, from Customer to SalesOrder to SalesOrderLine to ShipmentLine, ending in a zero-or-one link from ShipmentLine to SalesInvoiceLine. SalesOrderLine and CashReceiptApplication are junction tables.	Account to GLEntry (one to zero or many), with a self-reference in Account.ParentAccountID. GLEntry reaches source documents in every group through the source-document trace.
Cross-group bridge keys	ItemID reaches most groups, including O2C, P2P, Manufacturing, and Demand Planning and MRP. Employee references appear across the database under role names such as SalesRepEmployeeID.	CustomerID stays within O2C. ItemID bridges to Master Data. Sales invoices, shipments, and cash receipts post to GLEntry through the source-document trace.	AccountID is referenced by GLEntry rows from every business cycle. CostCenterID bridges to Organizational Planning.
Header-and-line pattern	Not applicable (reference tables).	SalesOrder and SalesOrderLine, Shipment and ShipmentLine, SalesInvoice and SalesInvoiceLine. CashReceiptApplication settles receipts against invoices.	None. JournalEntry and GLEntry are linked through the source-document trace and a matching VoucherNumber, not a stored foreign key.
Accounting depth	No transactions, but each product carries its inventory, revenue, and COGS account mappings.	Records the full revenue cycle from order through cash collection. The debits and credits themselves are posted in GLEntry.	Contains the general ledger. Every balance on the financial statements is derived from GLEntry joined to Account, so the statements are only as complete as its postings.
Complexity profile	Small and relatively static. Referenced by every other group.	The largest group, with deep relationship chains and sub-paths for pricing, services, commissions, and returns.	Simple in structure but deep in scope. GLEntry is the largest table in the database and the integration point for all other groups.

3.7.3 Complex Transaction Groups: Order-to-Cash

The Order-to-Cash group demonstrates how a single business cycle creates a long chain of related tables. The O2C group traces the full revenue cycle from the customer’s initial order through shipment, invoicing, cash collection, and even returns and credits.

The core O2C path follows a sequence of relationships. The Customer table stores customer master data. Each customer can place many sales orders, recorded in the SalesOrder table. Each sales order contains one or more line items, recorded in the SalesOrderLine table. This header and line pattern, in which one table records a document as a whole and a related table records its individual lines, repeats for shipments and invoices. Each sales order line references an item from the Item table (Master Data group) through the ItemID foreign key. When the order is fulfilled, one or more shipments are created in the Shipment table, with individual shipped lines in the ShipmentLine table. Each shipped line is then billed on a line of a sales invoice, recorded in SalesInvoice and SalesInvoiceLine. The customer eventually pays through a CashReceipt, and the CashReceiptApplication table records which specific invoices each receipt settles.

At every step in this chain, the same relational principles apply. Each table has a primary key that uniquely identifies its rows, and foreign keys link each table to the next table in the sequence. Most steps are one-to-many: one customer has many orders, one order has many lines, one order has many shipments. One step is different. Each shipment line is billed on at most one invoice line, so the relationship between ShipmentLine and SalesInvoiceLine is one-to-zero-or-one, and invoice lines for design services have no shipment line at all because nothing was shipped. The SalesOrderLine table also functions as a junction table between SalesOrder and Item, resolving the many-to-many relationship between orders and products. Figure 3.7 shows the complete core path.

Figure 3.7: Entity-Relationship Diagram for the Charles River O2C Core Path.

Beyond the core path, the O2C group includes tables for commercial pricing (PriceList, PriceListLine, PromotionProgram, PriceOverrideApproval), design service engagements and billing (ServiceEngagement, ServiceTimeEntry, ServiceBillingLine), sales commissions (SalesCommissionRate, SalesCommissionAccrual), and a returns chain (SalesReturn, CreditMemo, CustomerRefund). You do not need to memorize these tables now. The important point is that a single business cycle in a real organization generates a substantial network of related tables, and the ability to read the ER diagram is what allows you to navigate that network.

3.7.4 The Manufacturing Group

The Manufacturing group provides a second example of a complex transactional cycle. It covers bills of material, routings, work centers, work orders, material usage, labor support, and cost variance analysis. Production planning itself, including demand forecasts and material requirements planning, sits in the separate Demand Planning and MRP group.

The production path begins with the BillOfMaterial and BillOfMaterialLine tables, which define the components required to manufacture each product. BillOfMaterial identifies the finished product through ParentItemID, and each BillOfMaterialLine identifies a component through ComponentItemID, so a single Item table plays two roles. When production begins, a WorkOrder is created for a specific item. The WorkOrder connects to WorkOrderOperation records that define the manufacturing steps, and MaterialIssue and MaterialIssueLine records track the components consumed during production. When production is complete, ProductionCompletion and ProductionCompletionLine records move finished goods into inventory at standard cost. Finally, the WorkOrderClose record resolves any difference between standard cost and actual cost, posting manufacturing variances to the general ledger. Figure 3.8 shows these tables and their relationships.

Figure 3.8: Focused Entity-Relationship Diagram for the Charles River Manufacturing Group.

The Manufacturing group illustrates a pattern common in complex table groups: the distinction between planning and execution tables. The BillOfMaterial defines what should happen (the recipe). The WorkOrder and its related tables record what actually happened (the production run). This distinction is important for cost accounting because variances arise when actual performance differs from the plan.

TipIn Practice

Manufacturing companies often have complex database structures. Production data introduces tables for bills of material, work centers, routing steps, material issues, and production completions, all of which connect to the financial data through cost records and variance postings. Cost accountants and auditors working in manufacturing frequently need to traverse relationships that span multiple table groups to answer questions such as “What was the actual cost of producing this item, and how does it compare to the standard cost?” The Charles River Manufacturing group prepares you for exactly this type of analysis.

3.8 Guided Tutorial 3.1: Reading the Charles River O2C Entity-Relationship Diagram

Context and objective. In this tutorial, you will study the O2C core ER diagram and practice identifying tables, keys, relationships, and cardinality. The goal is to develop fluency in reading ER diagrams so that you can navigate any table group you encounter in later chapters or in professional practice.

Prerequisites. You need the Charles River O2C ER diagram (Figure 3.7) and the crow’s foot notation reference (Figure 3.4).

Step-by-step instructions.

Step 1. Count the tables. Locate Figure 3.7, the Charles River O2C core ER diagram, and begin by counting the tables shown. You should identify the core tables in the revenue path: Customer, SalesOrder, SalesOrderLine, Item, Shipment, ShipmentLine, SalesInvoice, SalesInvoiceLine, CashReceipt, and CashReceiptApplication. Each table is represented as a rectangle with a header bar and a list of columns. Notice that Item has a gray header, because it belongs to the Master Data group rather than to O2C.

Step 2. Identify the primary key of each table. Look for the column marked “PK” in each table. The primary keys include CustomerID (Customer), SalesOrderID (SalesOrder), SalesOrderLineID (SalesOrderLine), ItemID (Item), ShipmentID (Shipment), ShipmentLineID (ShipmentLine), SalesInvoiceID (SalesInvoice), SalesInvoiceLineID (SalesInvoiceLine), CashReceiptID (CashReceipt), and CashReceiptApplicationID (CashReceiptApplication). Notice that the Charles River database uses surrogate primary keys, single-column integer identifiers, for every table.

Step 3. Identify the foreign keys in the SalesOrderLine table. The diagram shows two of them: SalesOrderID (referencing SalesOrder) and ItemID (referencing Item in the Master Data group). The full table has others, such as PromotionID, that the diagram leaves out to keep the core path readable. Trace each foreign key to the table it references and confirm that the referenced column is the primary key of that table. The ItemID foreign key is a cross-group bridge key because it connects the O2C group to the Master Data group, which is why its relationship line is drawn in amber.

Step 4. Read the relationship between Customer and SalesOrder. Find the line connecting the two tables and read its cardinality and optionality. The Customer end shows two bars (exactly one), and the SalesOrder end shows a circle with a crow’s foot (zero or many). Read the relationship aloud: “Each sales order belongs to exactly one customer. One customer can have zero or many sales orders.” The circle tells you that a customer can exist in the database without having placed an order.

Step 5. Recognize a junction table. Find the SalesOrderLine table. Notice that it sits between SalesOrder and Item. Trace its two foreign keys: SalesOrderID connects to the SalesOrder table, and ItemID connects to the Item table. At the SalesOrderLine end, the line from SalesOrder shows one or many (every order has at least one line), while the line from Item shows zero or many (some items have never been ordered). The SalesOrderLine table is the junction table that resolves the many-to-many relationship between sales orders and items.

Step 6. Trace the fulfillment path. Follow the relationships from the order line to the cash receipt, reading the symbols on each line as you go:

1. From SalesOrderLine, follow the relationship to ShipmentLine. One order line can be shipped in zero, one, or several shipment lines.
2. From ShipmentLine, follow the relationship to SalesInvoiceLine through the ShipmentLineID foreign key. Notice the symbols on this line: zero or one at both ends. Each shipment line is billed on at most one invoice line, and invoice lines for design services have no shipment line because nothing was shipped.
3. From SalesInvoiceLine, move up to SalesInvoice. Each invoice has one or many lines.
4. From SalesInvoice, follow the settlement path to CashReceiptApplication and then to CashReceipt. One receipt can settle several invoices, and one invoice can be settled by several receipts, so CashReceiptApplication is a second junction table.

You have now traced the full O2C path from order through fulfillment, billing, and settlement.

Checkpoint. You should now be able to answer the following questions by reading the O2C ER diagram:

• How many foreign keys does the diagram show for the SalesOrderLine table? (Two: SalesOrderID and ItemID.)
• Which tables serve as junction tables? (SalesOrderLine between SalesOrder and Item, and CashReceiptApplication between CashReceipt and SalesInvoice.)
• What is the cardinality of the relationship between Customer and SalesOrder? (One to zero or many: each order has exactly one customer, and a customer can have any number of orders, including none.)

If you can answer all three questions correctly, you have the ER diagram reading skills you will need for the chapters ahead.

3.9 The Accounting Core as the Integration Hub

The Accounting Core group has only a few tables, but it is the most important group in the database because every other group flows into it. The Account table stores the chart of accounts. The JournalEntry table stores the headers for manually created journal entries, including the posting date, the entry type, the employee who created the entry, and the employee who approved it. The GLEntry table stores every debit and credit that makes up the general ledger.

What makes the Accounting Core special is its role as the integration hub. Each business cycle posts to GLEntry through several kinds of documents. The O2C group posts through sales invoices, shipments, cash receipts, and cash applications, as well as sales commissions, returns, credit memos, and customer refunds. The P2P group posts through goods receipts, purchase invoices, and disbursement payments. The Manufacturing group posts through material issues, production completions, and work order closes. The Payroll and Time group posts through payroll summaries, payroll payments, and liability remittances. And the JournalEntry table handles manual entries such as accruals, reclassifications, and period-end adjustments. The financial activity of every business cycle flows into GLEntry, which makes it the central record of the organization’s posted financial activity. Figure 3.9 shows the Accounting Core and its connections.

Figure 3.9: Entity-Relationship Diagram for the Charles River Accounting Core and Source Document Connections.

This design means that the balances reported on the financial statements can be traced through the GLEntry table to the documents behind them. Revenue can be traced back to sales invoices. Cost of goods sold can be traced back to shipments and manufacturing close records. Payroll expense can be traced back to payroll postings. The source-document trace in GLEntry makes all of this possible without requiring the analyst to know in advance which business cycle generated a particular entry. Employers of accounting graduates make the same point: graduates need to understand how transactions move through the accounting process so that they can judge whether the resulting accounting information is reasonable (Daff, 2021).

WarningWatch out

The source-document trace in GLEntry uses the SourceDocumentType and SourceDocumentID columns to link entries to their originating documents. This is not a standard foreign key relationship because a foreign key can reference only one table, while a single SourceDocumentID column might reference a SalesInvoice, a PurchaseInvoice, or a JournalEntry depending on the SourceDocumentType value. The link therefore has to be tested rather than trusted. In the Charles River data, a small number of ledger rows typed as PayrollPayment point to payment records that do not exist, which is exactly the kind of exception such a test would reveal. In the SQL chapters of Part III, you will write queries that check whether every GLEntry with a given SourceDocumentType has a valid corresponding record in the expected source table.

3.10 Guided Tutorial 3.2: Tracing a Sale from Sales Order to General Ledger

Context and objective. In this tutorial, you will follow one real Charles River sale through the workbook, from the sales order to the general ledger and the cash receipt that paid for it. The goal is to practice using keys to move between tables and to see how the source-document trace connects operational data to the posted ledger. You will work with invoice SI-2025-007947, which billed a customer for two desks. Its revenue posting is the ledger row you saw in Figure 1.6, and this tutorial follows the rest of the sale.

Prerequisites. You need Microsoft Excel installed on your computer and your working copy CharlesRiver_Work.xlsx (Tutorial 2.1), together with the O2C ER diagram (Figure 3.7) and the Accounting Core ER diagram (Figure 3.9). Several worksheets are large, so use the filter buttons in the column headers rather than scrolling to find the rows you need. Clear any filters left on a worksheet before you use it, and each time you return to GLEntry for another document, clear its filters and set both SourceDocumentType and SourceDocumentID, then check the number of visible rows. A filter left on CostCenterID, for example, would hide the rows of the sale that carry no cost center.

Step-by-step instructions.

Step 1. Find the invoice. Click the SalesInvoice worksheet tab and filter the InvoiceNumber column for SI-2025-007947. Record the SalesInvoiceID (7947), the SalesOrderID (1697), the CustomerID (135), and the amounts. The invoice has a SubTotal of $688.42, a FreightAmount of $51.00, and a TaxAmount of $48.06, for a GrandTotal of $787.48.

Step 2. Read the invoice line. Click the SalesInvoiceLine worksheet tab and filter the SalesInvoiceID column for 7947. The invoice has one line (SalesInvoiceLineID 10645) for 2 units at a UnitPrice of $344.21, a LineTotal of $688.42. The line carries foreign keys that lead back into the O2C group, SalesOrderLineID 6522 and ShipmentLineID 10449, and one that crosses into Master Data, ItemID 218.

Step 3. Follow the foreign keys. Use each key on the invoice line to find the row it refers to:

1. In the ShipmentLine worksheet, filter ShipmentLineID for 10449: it belongs to ShipmentID 3603 and records 2 units shipped.
2. In the Shipment worksheet, ShipmentID 3603 is shipment SH-2025-003603, shipped on 2025-03-02, three days before the invoice date.
3. In the SalesOrderLine worksheet, SalesOrderLineID 6522 belongs to SalesOrderID 1697.
4. In the SalesOrder worksheet, that is order SO-2024-001697, placed on 2024-11-21 by CustomerID 135, and the Customer worksheet shows that customer 135 is Sullivan-Mcdonald, a wholesale customer.
5. In the Item worksheet, ItemID 218 is FUR-DSK-0075, an Alder Desk in the Furniture group.

Notice that the order line covers a larger quantity than this invoice: the order was shipped and invoiced in several parts, and this invoice bills only the 2 units shipped on SH-2025-003603.

Step 4. Find the invoice’s ledger postings. Now cross into the Accounting Core.

1. Click the GLEntry worksheet tab and filter SourceDocumentType for SalesInvoice and SourceDocumentID for 7947. You should see four rows, all posted on 2025-03-05:
   • a debit to AccountID 3 of $787.48;
   • a credit to AccountID 42 of $688.42;
   • a credit to AccountID 46 of $51.00;
   • a credit to AccountID 26 of $48.06.
2. Add the Debit amounts and the Credit amounts of the four rows and confirm that the entry balances. Four rows are easy to add by hand, while a SUM over the whole column would also add the rows that the filter hides.

Notice that only the revenue row has a SourceLineID (10645), because it is the only posting that comes from a specific invoice line.

Step 5. Translate the AccountIDs into account numbers. GLEntry stores the Account table’s surrogate key, AccountID, not the account number you would see in a chart of accounts. Look up each AccountID from Step 4 in the Account worksheet:

• AccountID 3 is account 1020, Accounts Receivable (an asset).
• AccountID 42 is 4010, Sales Revenue - Furniture, and AccountID 46 is 4050, Freight Revenue (both revenue).
• AccountID 26 is 2050, Sales Tax Payable (a liability).

Compare AccountID 42 with the RevenueAccountID of item 218 in the Item worksheet: the item’s account mapping, also 42, is what directed this revenue to the furniture revenue account.

Step 6. Find the shipment’s postings. The same sale also created postings when the desks were shipped. In the GLEntry worksheet, filter SourceDocumentType for Shipment and SourceDocumentID for 3603. The rows with SourceLineID 10449 debit AccountID 50 (5010, COGS - Furniture) and credit AccountID 5 (1040, Inventory - Finished Goods) for $459.32 each, which is 2 units at the item’s standard cost of $229.66. These are the COGSAccountID and InventoryAccountID of item 218. The shipment’s remaining rows record the freight cost of delivering it.

Step 7. Trace the payment.

1. In the CashReceiptApplication worksheet, filter SalesInvoiceID for 7947. One application (CashReceiptApplicationID 10686) applies $787.48 from CashReceiptID 3043 on 2025-04-17.
2. In the CashReceipt worksheet, receipt 3043 is CR-2025-003043, a check for $23,870.99.
3. In the CashReceiptApplication worksheet, clear the filter on SalesInvoiceID, then filter CashReceiptID for 3043. You will see seven applications: this single check paid seven invoices, which is why a junction table is needed.
4. In GLEntry, find the receipt itself (SourceDocumentType CashReceipt, SourceDocumentID 3043). It debits AccountID 2 (1010, Cash and Cash Equivalents) and credits AccountID 27 (2060, Customer Deposits and Unapplied Cash).
5. Then find the application (SourceDocumentType CashReceiptApplication, SourceDocumentID 10686). It debits AccountID 27 and credits AccountID 3 for $787.48, moving the payment out of unapplied cash to settle Accounts Receivable.

Figure 3.10 summarizes the path you have just followed.

Figure 3.10: Annotated Charles River Cross-Group Traced Path.

Checkpoint. You should now be able to explain how a sale flows from the O2C group into the Accounting Core. You should be able to:

• show that the invoice’s ledger postings balance and that the debit to Accounts Receivable equals the invoice’s GrandTotal of $787.48;
• identify SourceDocumentType and SourceDocumentID as the columns that trace a GLEntry row back to its source document;
• explain why the same sale produces postings from several different documents: the shipment, the invoice, the cash receipt, and its application;
• translate an AccountID in GLEntry into an account number through the Account table;
• optionally, find a fifth document by filtering the SalesCommissionAccrual worksheet for SalesInvoiceLineID 10645 and finding that record’s rows in GLEntry, which accrue the sales commission on the line.

If you can explain all of these points, you have the cross-group navigation skills that the remaining chapters require.

3.11 Comparing Simple and Complex Table Groups

Looking across the Master Data, O2C, Manufacturing, and Accounting Core groups together reveals how the relational model adapts to business functions of different sizes and complexities within a single company. Several patterns emerge from the comparison.

The first pattern is that table group complexity increases with process complexity. Master Data stores relatively static reference information in a handful of tables. The O2C group, the largest in the database, captures a transactional cycle that spans pricing, ordering, shipping, invoicing, commissions, returns, and cash collection. The Manufacturing group covers the production process from bills of material through cost variance analysis. The number of tables reflects the number of distinct events and entities the business process involves.

The second pattern is that the same relational principles apply at every scale. A foreign key in the Master Data group works exactly the same way as a foreign key in the O2C group. A one-to-many relationship between Customer and SalesOrder in the largest group follows the same rules as a one-to-many relationship between Account and GLEntry in one of the smallest. The SQL syntax, the Power BI modeling techniques, and the Excel lookup functions you will learn all operate on these same relational foundations regardless of group size.

The third pattern is that cross-group bridge keys tie the entire database together. ItemID connects Master Data to O2C, P2P, Manufacturing, and planning. AccountID connects the Accounting Core to every group that posts to the general ledger. EmployeeID connects Master Data to most other groups, for example Payroll and Time, O2C (through sales representatives), and the Accounting Core (through the employees who create and approve journal entries). These bridge keys are what make it possible to ask questions that span multiple business functions, such as “What is the total cost of manufacturing a specific item, and how does it compare to the revenue that item generated?”

3.12 Looking Ahead

This chapter has introduced the relational database model and shown you how accounting data is organized within it. You have learned how tables, rows, columns, and data types store information, how primary keys and foreign keys create relationships between tables, and how Entity-Relationship diagrams make these structures visible. You have seen the difference between the relationships a database is designed around and the constraints it enforces, examined the Charles River database design across multiple table groups, and traced how cross-group bridge keys and the source-document trace connect operational activity to the posted general ledger.

You can now return to the controller’s question with a map of where the answer lives. Furniture revenue is posted from sales invoice lines to account 4010, its cost is posted from shipments to account 5010, and the Item table’s ItemGroup column tells you which products belong to the Furniture line. The Part I Comprehensive Case that follows asks you to put that map to work: to frame the question, test the data, and reconcile the sub-ledger to the ledger before any analysis begins. Part II then introduces Excel as an analytical tool. Chapter 4 begins by showing you how to build a workbook of your own for the analysis, bringing in the data with Power Query so that you can sort, filter, and summarize it without altering the company’s data. The relational concepts from this chapter will remain in the background as you work, because the Tables you bring into Excel hold the same tables and relationships you have just studied.

3.13 Chapter Summary

Accounting data in modern organizations is stored in relational databases, which organize information into tables connected by defined relationships. Understanding this structure is a prerequisite for writing SQL queries, building Power BI data models, and preparing data for analysis in Excel. The relational model, first proposed by Codd (1970), is widely used for data storage in business information systems, including ERP systems and accounting software packages.

A relational database consists of tables, each storing information about one type of entity or event. Tables are organized into rows (one per instance) and columns (one per attribute), and every column has a data type that determines what values it can hold and what operations are possible. How a database stores those types, and how strictly it enforces them, depends on the system: the Charles River SQLite file stores numbers as INTEGER or REAL, text and dates as TEXT, and missing values as NULL. Primary keys uniquely identify each row in a table, and foreign keys create connections between tables by referencing the primary key of a related table. These keys formalize the cross-references that have always existed in accounting records: a sales invoice references a customer, a general ledger entry references an account, and an order line references a product.

A database can enforce these relationships through constraints, but it does not have to. The Charles River database enforces its primary keys and the uniqueness of many document numbers, while its foreign keys are logical relationships that the data follows but the file does not enforce. That is why analysts test referential integrity rather than assume it, and why, when the data is loaded into a tool such as Power BI, the relationships are defined and checked rather than imported.

Relationships between tables have a cardinality that specifies how many rows on each side of the relationship can be associated, and an optionality that specifies whether a row must participate at all. One-to-many relationships are the most common in accounting data: one customer has many orders, one account has many GL entries, one item appears on many order lines. One-to-one relationships appear when a later stage of a process has its own table, such as a work order and its close record. Many-to-many relationships are resolved through junction tables, such as the Charles River SalesOrderLine table that connects SalesOrder to Item and the CashReceiptApplication table that connects receipts to invoices. Understanding cardinality is essential for avoiding analytical errors such as double-counting when combining data from multiple tables.

Entity-Relationship diagrams provide a visual map of a database’s structure. They show tables as labeled rectangles, relationships as connecting lines, and cardinality and optionality through crow’s foot notation. Reading an ER diagram is a practical skill that auditors, analysts, and accountants use when planning data extractions, designing queries, and building data models. The Charles River ER diagrams and the table-group comparison in this chapter illustrate how table group complexity scales with business process complexity, from the small Master Data group to the large Order-to-Cash group, while the underlying relational principles remain constant.

The familiar structures of accounting, including the chart of accounts, the general ledger, and the sub-ledgers, map directly to tables and relationships in the Charles River database. The chart of accounts is the Account table with a self-referencing hierarchy through ParentAccountID. The general ledger is the GLEntry table, linked to the chart of accounts through AccountID. Sub-ledgers are the transactional cycle tables, such as SalesInvoice and PurchaseInvoice, that connect to GLEntry through the source-document columns SourceDocumentType, SourceDocumentID, and SourceLineID, a logical link that can point to a different table for every posting. Cross-group bridge keys such as ItemID, AccountID, and EmployeeID tie the entire database together, making it possible to perform financial reporting, audit testing, and managerial analysis directly from the database, which is exactly what you will do in the chapters ahead.

3.14 Key Terms

Cardinality. The specification of how many rows on each side of a relationship can be associated with one another. The three types are one-to-one, one-to-many, and many-to-many. In accounting databases, one-to-many is the most common cardinality.

Column. A single attribute within a database table, representing one piece of information recorded for every row. Examples include OrderDate, AccountName, and ListPrice.

Composite key. A primary key made up of two or more columns that together uniquely identify each row. Every Charles River table uses a single-column surrogate key instead, but composite keys are common in other accounting systems.

Cross-group bridge key. A column that appears in tables across different table groups within a database, enabling joins that span business functions. In the Charles River database, ItemID connects Master Data to O2C, P2P, Manufacturing, and the planning tables, and AccountID connects the Accounting Core to all groups that post to the general ledger.

Crow’s foot notation. A visual notation system used in Entity-Relationship diagrams to indicate cardinality and optionality. A bar represents “one,” a three-pronged fork represents “many,” and a circle represents “zero.”

Data dictionary. A reference document that describes each table and column in a database, including its meaning, data type, and relationships. Auditors often request a data dictionary along with an ER diagram when planning data extraction.

Data type. The defined category of values that a column is meant to hold, such as integer, decimal, text, date, or Boolean. Data types determine how a tool interprets and calculates with a column’s values, and how strictly a type is enforced depends on the system. The Charles River SQLite file stores dates as text in year-month-day form and Boolean flags as the integers 1 and 0.

Entity-Relationship (ER) diagram. A visual representation of the tables in a database and the relationships between them, showing table names, column names, keys, and cardinality using standardized notation.

Foreign key. A column in one table that references the primary key of another table, creating a relationship between the two tables. For example, the CustomerID column in the Charles River SalesOrder table is a foreign key that references the Customer table. A foreign key may be declared as a constraint that the database enforces, or it may be a logical relationship that the data follows without enforcement, as in the Charles River database. It can be blank where the relationship is optional, and it can reference its own table, as ParentAccountID does in the Account table.

Header and line tables. A design pattern in which one table records a document as a whole (the header) and a related table records its individual lines. Charles River examples include SalesOrder and SalesOrderLine, Shipment and ShipmentLine, and SalesInvoice and SalesInvoiceLine.

Junction table. A table that resolves a many-to-many relationship between two other tables by converting it into two one-to-many relationships. The Charles River SalesOrderLine table is a junction table between SalesOrder and Item, and CashReceiptApplication is a junction table between CashReceipt and SalesInvoice.

Many-to-many relationship. A relationship in which each row in either table can be associated with multiple rows in the other table. Relational databases represent many-to-many relationships through junction tables.

One-to-many relationship. A relationship in which one row in the first table can be associated with many rows in the second table, but each row in the second table is associated with only one row in the first table. This is the most common relationship type in accounting databases.

One-to-one relationship. A relationship in which each row in one table is associated with at most one row in the other table. In the Charles River database, a WorkOrder has zero or one WorkOrderClose record, because a work order is closed at most once.

Optionality. The property of a relationship that specifies whether a row must participate in it. In crow’s foot notation, a circle marks an optional relationship, such as a customer who has not yet placed a sales order.

Primary key. A column, or combination of columns, that uniquely identifies each row in a table. No two rows can share the same primary key value, and the value cannot be blank.

Referential integrity. The requirement that every foreign key value that is not blank corresponds to an existing primary key value in the referenced table. A database can enforce it through declared foreign key constraints. The Charles River file declares none, so analysts test for orphaned records instead.

Relational database. A system that stores data in tables connected to one another through primary and foreign keys. The Charles River database is a relational database provided in SQLite format, and its relationships are logical rather than declared: the file defines its primary keys but not its foreign keys.

Row. A single record within a database table, representing one instance of the entity or event the table describes. Each row in the Charles River SalesOrder table represents one customer order.

Self-referencing relationship. A relationship in which a foreign key in a table references the primary key of the same table, creating a hierarchy within a single table. The Charles River Account table uses ParentAccountID to define the parent-child structure of the chart of accounts, and the Employee table uses ManagerID to link each employee to a manager.

Source-to-ledger traceability. The ability to trace a general ledger entry back to the business document that created it. In the Charles River database, GLEntry uses SourceDocumentType, SourceDocumentID, and SourceLineID to identify the originating document in any business cycle, a logical link that points to a different table depending on SourceDocumentType.

Surrogate key. A primary key with no business meaning that exists only to identify rows, usually a sequential integer. Every Charles River table uses a surrogate key, such as SalesOrderID or AccountID.

Table. The fundamental storage unit in a relational database, organized into rows and columns. Each table stores information about one type of entity or event, such as customers, orders, or general ledger entries.

Table group. A named set of related tables within the Charles River database that corresponds to a business process or functional area. The groups are a documentation convention: the SQLite file itself does not record which group a table belongs to, so Table 1.3 is the reference for group membership.

Unique constraint. A rule that prevents two rows in a table from having the same value in a column. The Charles River database enforces unique constraints on document numbers such as SalesInvoice.InvoiceNumber and JournalEntry.EntryNumber through unique indexes, in addition to its primary keys.

3.15 Multiple Choice Questions

1. In a relational database, which of the following best describes the role of a primary key?

1. It uniquely identifies each row and cannot be duplicated or blank.
2. It stores the most important data value in the table.
3. It connects one table to another by referencing a column in a different table.
4. It defines the data type for all columns in the table.

2. The CustomerID column appears in both the Charles River Customer table and the SalesOrder table. In the SalesOrder table, this column functions as a:

1. Primary key
2. Foreign key
3. Composite key
4. Data type

3. An ER diagram shows a line between two tables. The line has a single bar on one end and a crow’s foot on the other end. Ignoring optionality, this notation represents which type of relationship?

1. One-to-one
2. One-to-many
3. Many-to-many
4. The notation does not indicate cardinality

4. The Charles River SalesOrderLine table contains foreign keys to both SalesOrder and Item. This table exists because:

1. It stores the primary key of the SalesOrder table.
2. It is required for every database to have at least one table with two foreign keys.
3. It stores customer contact information for each order.
4. It resolves a many-to-many relationship between sales orders and items.

5. A cost accountant wants to compare standard item costs to actual production costs and analyze manufacturing variances. Which Charles River table group is best suited for this analysis?

1. The O2C group, because it contains product pricing data in SalesOrderLine
2. The Accounting Core group, because it contains a complete general ledger
3. The Manufacturing group, whose completions and closes hold standard costs and variances
4. The Master Data group, because the Item table contains the StandardCost and ListPrice columns

6. In the Charles River database, the Account table contains a column called ParentAccountID that references another row in the same Account table. This is an example of a:

1. Foreign key referencing a different table
2. Cross-group bridge key
3. Self-referencing relationship
4. Many-to-many relationship

7. An auditor wants to prepare a trial balance directly from the Charles River database. Which tables provide the necessary data?

1. Account and GLEntry from the Accounting Core group
2. SalesOrder and SalesOrderLine from the O2C group
3. WorkOrder and ProductionCompletion from the Manufacturing group
4. Item and Warehouse from the Master Data group

8. The Charles River SQLite file declares a primary key for every table but declares no foreign key constraints. What does this mean for an analyst who plans to join SalesOrder to Customer?

1. The join will fail because the relationship is not declared in the file.
2. The database automatically prevents orphaned orders, so the analyst does not need to test anything.
3. The join still works, but the analyst should test that every CustomerID matches a customer.
4. CustomerID must first be converted to text before the two tables can be joined.

9. A relational database enforces referential integrity. This means that:

1. Every table must contain at least one foreign key.
2. Primary key values can be duplicated, as long as the duplicate values appear in different tables of the database.
3. Tables without relationships are automatically deleted from the database.
4. Every foreign key value that is not blank matches an existing primary key in the referenced table.

10. An analyst joins the Charles River Customer table to SalesOrder and then to SalesOrderLine. One customer has 10 orders, and those 10 orders contain a total of 45 line items. How many rows will this customer contribute to the joined result?

1. 1 row
2. 10 rows
3. 45 rows
4. 55 rows

11. Which of the following accounting structures is represented in a database as a self-referencing table where child rows point to parent rows within the same table?

1. The chart of accounts hierarchy
2. The general ledger
3. The trial balance
4. The accounts receivable sub-ledger

12. The Charles River GLEntry table uses SourceDocumentType and SourceDocumentID to link general ledger entries to their source documents. This design differs from a standard foreign key because:

1. It uses two columns instead of one.
2. It points to a different table depending on SourceDocumentType, so no one foreign key describes it.
3. It stores the amount of the source document in the ledger row rather than the key of that document.
4. It is used only for manual journal entries, and not for invoices, payments, or other transaction types.

13. Which of the following is the best definition of a relational database?

1. A system that stores data in tables linked by primary and foreign keys
2. A database that keeps all of its data together in one single large table
3. A spreadsheet with multiple worksheets
4. A database whose data can be read and changed only by writing SQL statements

14. In a database that enforces data types, a column defined as INTEGER could store which of the following values?

1. “Accounts Receivable”
2. 2024-03-15
3. 99.73
4. 4500

15. The Charles River Master Data group has only a few tables, while the Order-to-Cash group is the largest table group in the database. Which of the following best explains this difference?

1. The O2C group is a newer part of the database and uses more modern design principles.
2. The O2C cycle has many stages, from pricing to cash collection, and each one needs its own tables.
3. The Master Data group contains errors that should have been stored in additional tables.
4. The number of tables is arbitrary and has no relationship to the complexity of the business process.

3.16 Applied Exercises

3.16.1 Financial Accounting Exercises

Exercise 3.1: Tracing a Purchase Transaction Through the Charles River Database

Table group: Procure-to-Pay and Accounting Core

Scenario. You are a financial reporting analyst at Charles River preparing to verify that purchasing transactions recorded in the system are properly reflected in the general ledger. Tutorial 3.2 traced a sale. Your task is to apply the same technique to a purchase, from the supplier’s invoice back to the goods received and forward to the payment and the ledger.

Requirements.

1. Open the Charles River workbook in Excel and select one purchase invoice from the PurchaseInvoice worksheet whose Status is Paid and that has a single line in the PurchaseInvoiceLine worksheet. Record its PurchaseInvoiceID, InvoiceNumber, SupplierID, and GrandTotal.

2. Follow the foreign keys on the invoice line. Use GoodsReceiptLineID to find the goods receipt line and its GoodsReceipt, and use POLineID to find the purchase order line and its PurchaseOrder. Look up the supplier in the Supplier worksheet. Write down each connecting key and the table it leads to. Note that a small number of invoice lines have no goods receipt line because they are linked to an accrual journal entry instead, so choose an invoice whose line has a GoodsReceiptLineID.

3. In the GLEntry worksheet, filter for the rows where SourceDocumentType is PurchaseInvoice and SourceDocumentID matches your invoice. Identify each account debited and credited, look it up in the Account worksheet, and verify that the debits equal the credits. Then find the ledger rows for the related goods receipt (SourceDocumentType GoodsReceipt). Explain why the purchase invoice debits Goods Received Not Invoiced rather than inventory.

4. Use the DisbursementPayment worksheet to find the payment or payments that settled the invoice, and trace their ledger rows. Prepare a brief written trace (one page) that documents the path of the transaction through the tables, identifying each table, the connecting keys, the accounts, and the amounts involved.

Deliverable. A one-page written transaction trace documenting the path of a purchase transaction through the Charles River database.

Exercise 3.2: Mapping the Charles River O2C Group to the Revenue Cycle

Table group: Order-to-Cash, with the Item table from Master Data

Scenario. You are a financial reporting analyst who has been asked to document how the Charles River database captures the revenue cycle. Your documentation will be used by the team as a reference when preparing revenue analyses in later chapters.

Requirements.

1. Using the O2C ER diagram (Figure 3.7), identify all tables involved in recording a sale from the initial order through the line-item details to the shipment, invoice, and cash collection. List each table and describe its role in the revenue cycle in one to two sentences.

2. For each pair of connected tables in the revenue cycle path, identify the foreign key that creates the connection and state the cardinality and optionality of the relationship, reading both ends of each line.

3. Identify what revenue cycle information requires joining to the Accounting Core group. Consider whether the O2C tables alone record the general ledger postings and the account classifications, and note which O2C table already carries a cost center. For each gap you identify, explain which Accounting Core table and column, or which table of another group, would provide the missing information.

4. Prepare a brief written summary (one page) that could serve as a data dictionary excerpt for the revenue-related tables in Charles River.

Deliverable. A one-page data dictionary excerpt covering the Charles River O2C revenue cycle tables, their relationships, and their connections to the Accounting Core.

3.16.2 Managerial Accounting Exercises

Exercise 3.3: Mapping the Charles River Manufacturing Group

Table group: Manufacturing, with the Item table from Master Data

Scenario. You are a cost analyst at Charles River. Your manager has asked you to document the database tables that support manufacturing cost analysis so that the team can plan a product costing project for the upcoming quarter.

Requirements.

1. Using the Manufacturing ER diagram (Figure 3.8) and the Excel workbook, identify the production tables that the diagram shows. For each table, write one to two sentences describing what it contains and how it relates to manufacturing cost analysis. Then name the Manufacturing tables that this focused diagram leaves out, such as the routings and the work center calendar.

2. Trace the path through the database that connects a finished product to its component materials. Start at the Item table and follow ParentItemID to BillOfMaterial, then BOMID to BillOfMaterialLine, and ComponentItemID back to the Item table for each component. Explain how a cost analyst would use this path to calculate the material cost of a product, and why the same Item table appears at both ends of the path.

3. Identify the table that records actual production activity (WorkOrder) and the tables that record completion (ProductionCompletion, ProductionCompletionLine) and cost close-out (WorkOrderClose). Examine their columns and determine what information is available for comparing planned production to actual production, including quantities and variance amounts.

4. Determine whether the Charles River database allows you to calculate the variance between standard cost and actual cost for a specific product. Identify which tables and columns would be involved.

5. Write a brief memorandum (one page) summarizing the production-related data available in the Charles River Manufacturing group and assessing its suitability for a product cost analysis project.

Deliverable. A one-page memorandum documenting the Charles River Manufacturing data and its suitability for cost analysis.

Exercise 3.4: Mapping the Keys Behind a Budget-Versus-Actual Report

Table group: Organizational Planning, with GLEntry from the Accounting Core

Scenario. You are a management accountant at Charles River preparing to build a departmental performance report that compares budgeted expenses to actual expenses by cost center. Exercise 2.4 assessed the quality of the budget data. Before you build the report, you need to understand the keys and relationships that will connect budgets to actual spending.

Requirements.

1. Open the Charles River workbook and examine the CostCenter worksheet. Identify its primary key, and use the ParentCostCenterID column to determine whether the cost centers form a hierarchy. Explain what your finding means for a report that would roll departments up into divisions.

2. Identify the foreign key that links GLEntry to CostCenter. Using crow’s foot notation, state the cardinality and optionality at each end of the relationship, and explain what the optional end means for the report. Exercise 2.4 counted the ledger entries with no cost center, so use that count rather than recounting.

3. Examine the Budget and BudgetLine worksheets. Identify the foreign keys that connect each table to CostCenter and to Account, describe what one row of each table represents (its grain), and compare the accounts each table covers. Explain which table you would set beside GLEntry for a comparison by cost center, account, and month, and why. The report compares the two sides at one grain (fiscal year, month, cost center, and account), so summarize the budget and the ledger to that grain separately before you combine them. Matched row to row, one budget row would be repeated for every ledger row of its month, cost center, and account.

4. Test whether every AccountID in the Budget table appears in GLEntry. Build a PivotTable of the distinct AccountID values in each worksheet and compare the two lists with XLOOKUP or COUNTIFS, as in Tutorial 2.2. Record any budgeted accounts with no ledger activity, and explain what a clean result tells you.

5. Write a brief assessment (one half to one page) that documents the join path from the budget to the ledger, the grain at which the two are compared, the accounts and periods in scope and how you treat the year-end closing entries (they are the journal entries whose EntryType begins with Year-End Close, and the source-document trace of Tutorial 3.2 finds their ledger rows), the relationships involved, and any gaps that would affect the report. Keep budgeted combinations with no actuals, actuals with no budget, and ledger rows with no cost center visible rather than letting them drop out.

Deliverable. A written assessment of the keys and relationships that connect Charles River budgets to actual spending by cost center.

3.16.3 Auditing Exercises

Exercise 3.5: Planning a Data Extraction Strategy for the Charles River Audit

Table group: Order-to-Cash, Procure-to-Pay, and Accounting Core

Scenario. You are an IT auditor responsible for planning the data extraction needed to support three audit procedures: a revenue completeness test, a purchasing cycle test including three-way matching, and a journal entry analysis for management override of controls. For each procedure, you need to identify which table group to use, which tables to extract, and which relationships you will need to traverse.

Requirements.

1. For the revenue completeness test, identify the table group and tables you would use. Explain which table contains the population of revenue transactions and which related tables you would need to join to verify that each transaction includes a valid customer, a valid item, and an amount. State the keys that connect the tables. Explain how you would use the SourceDocumentType column in GLEntry to confirm that every sales invoice generated corresponding ledger entries, why that posting test does not by itself show that every sale was invoiced, and which table would supply an independent population to test that.

2. For the purchasing cycle test, identify the table group and tables you would use. The test requires three-way matching by comparing the quantity and unit cost across PurchaseOrderLine, GoodsReceiptLine, and PurchaseInvoiceLine. Identify the foreign keys that link these three tables and note any gaps in the available data, such as a value that one of the three tables does not record.

3. For the journal entry analysis, identify the table group and tables you would use. The analysis requires access to every manually created journal entry, including the posting date, the amounts, the accounts debited and credited, and the employees who created and approved the entry. Identify which tables contain this information (JournalEntry and GLEntry) and explain how the two are linked even though GLEntry has no JournalEntryID column. For the risk-based filters, start from the columns you identified in Exercise 1.6 rather than listing them again, and note which of those columns sit in JournalEntry and which sit in GLEntry, because that determines which table each filter must read.

4. Prepare a data extraction plan (one to two pages) that documents your selections for all three procedures.

Deliverable. A one-to-two page data extraction plan for three audit procedures, identifying specific Charles River tables and relationships.

Exercise 3.6: Assessing Referential Integrity Across the Charles River Database

Table group: Order-to-Cash, Master Data, and Accounting Core

Scenario. Your audit senior has asked you to perform a preliminary check on the referential integrity of the Charles River database. Because the database file does not declare foreign key constraints, nothing prevents a record from referencing a primary key value that does not exist, and testing is the only way to know whether the relationships hold. Referential integrity problems can indicate data migration errors, deletion of master records without updating related transactions, or system configuration problems. Any violations you find will be flagged for further investigation.

Requirements.

1. Using the O2C and Accounting Core ER diagrams, identify at least six foreign key relationships to test. List each relationship by specifying the child table, the foreign key column, the parent table, and the primary key column. Include at least one cross-group relationship, such as SalesOrderLine.ItemID referencing Item.ItemID.

2. For each relationship, test whether every foreign key value that is filled in exists as a primary key value in the parent table. Count the blank values separately, and decide for each relationship whether a blank is allowed, as it is for an invoice line that bills a design service and has no shipment line. For small tables, use XLOOKUP or COUNTIFS on each row. For the GLEntry table, which holds hundreds of thousands of rows, first build a PivotTable of the distinct AccountID values and test that short list against the Account table, as in Tutorial 2.2. Record the number of orphaned references, if any, for each relationship.

3. Pay particular attention to the relationships between SalesOrder and Customer, between SalesOrderLine and Item, and between GLEntry and Account. For any orphaned references you find, record the specific foreign key values that have no match and the number of records affected.

4. Prepare a brief memorandum (one page) documenting your referential integrity testing results. For each relationship tested, state whether integrity was maintained or violated. Explain what a clean result tells the audit team, and why that assurance comes from your tests rather than from the database itself.

Deliverable. A one-page referential integrity testing memorandum documenting the results for all tested foreign key relationships in the Charles River database.

3.17 Further Reading

Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377–387. https://doi.org/10.1145/362384.362685 This paper introduced the relational database model, on which the databases described in this chapter are built. Although the paper is technical, the core ideas of organizing data into tables with defined relationships are accessible, and understanding their origin helps explain why relational databases work the way they do. This is one of the most influential papers in the history of computing.

Dunn, C. L., Cherrington, J. O., & Hollander, A. S. (2005). Enterprise information systems: A pattern-based approach (3rd ed.). McGraw-Hill/Irwin. This textbook provides a comprehensive treatment of how accounting information systems are designed using relational database concepts. The chapters on REA (Resources, Events, Agents) modeling are particularly useful for understanding how accounting transactions map to database tables and relationships.

Gelinas, U. J., Dull, R. B., Wheeler, P. R., & Hill, M. C. (2018). Accounting information systems (11th ed.). Cengage Learning. This widely used accounting information systems textbook includes detailed coverage of relational databases, ER diagrams, and their application to accounting data. The chapters on database design and data modeling provide additional depth on the concepts introduced in this chapter.

McCarthy, W. E. (1982). The REA accounting model: A generalized framework for accounting systems in a shared data environment. The Accounting Review, 57(3), 554–578. https://doi.org/10.2308/tar-4487748 This paper introduced the REA model, which applies entity-relationship concepts specifically to accounting system design. The framework describes accounting data in terms of resources, events, and agents rather than debits and credits, providing an alternative perspective on how accounting transactions are represented in databases.

Romney, M. B., Steinbart, P. J., Summers, S. L., & Wood, D. A. (2024). Accounting information systems (16th ed.). Pearson. This textbook provides thorough coverage of relational databases and REA modeling in the context of accounting information systems, and it includes chapters on transforming and analyzing data. The chapters on relational databases and on database design using the REA data model offer additional practice with the concepts introduced in this chapter, and the book includes numerous exercises using accounting scenarios.

Sledgianowski, D., Gomaa, M., & Tan, C. (2017). Toward integration of Big Data, technology and information systems competencies into the accounting curriculum. Journal of Accounting Education, 38, 81–93. https://doi.org/10.1016/j.jaccedu.2016.12.008 This paper proposes ways to integrate big data, technology, and information systems competencies into accounting courses, with course-by-course examples. It explains why accounting programs now teach the database concepts introduced in this chapter.

3.18 References

American Institute of Certified Public Accountants. (2021). Understanding the entity and its environment and assessing the risks of material misstatement (Statement on Auditing Standards No. 145). https://www.aicpa-cima.com/resources/download/aicpa-statement-on-auditing-standards-no-145

American Institute of Certified Public Accountants & National Association of State Boards of Accountancy. (2021). CPA Evolution model curriculum. https://www.thiswaytocpa.com/collectedmedia/files/cpa-evolution-model-curriculum-update.pdf

Andiola, L. M., Masters, E., & Norman, C. (2020). Integrating technology and data analytic skills into the accounting curriculum: Accounting department leaders’ experiences and insights. Journal of Accounting Education, 50, Article 100655. https://doi.org/10.1016/j.jaccedu.2020.100655

Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377–387. https://doi.org/10.1145/362384.362685

Daff, L. (2021). Employers’ perspectives of accounting graduates and their world of work: Software use and ICT competencies. Accounting Education, 30(5), 495–524. https://doi.org/10.1080/09639284.2021.1935282