Chapter 2
Databases and Accounting Systems
Notes to the Instructor
In this chapter, students learn about database accounting systems and how they differ from
double-entry bookkeeping systems. It explains the connections between accounting systems and
database systems, why a relational database system is superior to double-entry bookkeeping
systems for capturing detailed accounting information. Upon finishing this chapter, each student
will understand how to create efficient, optimal database objects to capture accounting
information Completed instructor files are available for use in a protected area of the companion
Lecture Outline
This chapter is independent of other chapters in the book in that it provides a theoretical
foundation that links accounting systems and database systems.
Objectives
• Chapter 2 presents the theoretical foundation for the remainder of the text and contains both
practice and theory for both accounting transaction cycles and database management systems
➢ Performing database selections, projections, and joins.
➢ The resources-events-agents (REA) model for accounting databases.
• The Coffee Merchant, a fictitious coffee bean and tea wholesaler, demonstrates the classic
accounting application involving processing and maintaining invoice data
➢ Keeping track of unpaid customer invoices
➢ Generating relevant reports based on current and historical customer data
Introduction
2-2 CHAPTER 2 • DATABASES AND ACCOUNTING SYSTEMS
• The business activity classifications in the chapter provide a three-level taxonomy of
complexity that can help system designers decide when to incorporate certain features in
their designs.
• Transaction cycles provide accountants and auditors a way to organize economic events into
related categories.
Database Accounting Systems
• Chapter 2 introduces database theory and explains how firms can use relational databases as
part of their accounting systems.
Events-Based Theories of Accounting
• Researchers such as William McCarthy and George Sorter developed events approaches to
accounting that lend themselves to the use of relational databases to perform accounting
tasks.
• Events approaches to accounting argue that accountants ideally should store all relevant
attributes of economic events in a readily accessible form.
• This is not a fully attainable objective with currently available technology; however,
relational databases do a fairly good job of storing many information attributes and giving
users a variety of ways to retrieve those attributes.
Double-Entry Bookkeeping Versus Database Accounting
• Double-entry bookkeeping satisfied accountants’ need to capture transaction essentials for
many years.
• Five hundred years ago, the costs of gathering and storing information were very high.
• Double-entry bookkeeping let businesspersons capture key attributes of transactions in a
highly aggregated form, which minimized the cost of information gathering and storage.
names of common shipping companies (for example, Federal Express might be entered as
FedEx, FEDEX, Fed Express, FEx, or Federal Express).
• A Try it exercise asks students to identify interesting facts that can be found in or calculated
from the database diagrammed in Figure 2.1.
• The database approach can do everything that double-entry bookkeeping can do and more.
Advantages of Database Accounting Systems
• Reduce data storage costs.
• Eliminate data redundancy.
• Eliminate data inconsistencies.
• Avoid duplicate processing.
• Ease add, delete, and update data maintenance tasks.
• Make data independent of applications.
• Database accounting systems offer much greater flexibility in extracting data than flat file
double-entry accounting systems. This flexibility leads to other advantages such as:
➢ Ease report modifications and updates.
➢ Provide ad hoc query capabilities.
➢ Facilitate cross-functional data analysis.
➢ Permit multiple users simultaneous data access.
• Provide data entry and integrity controls as part of the database management system.
Disadvantages of Database Accounting Systems
• Greater hardware requirements can be more expensive.
• The cost of the database software itself.
• Cost of employing a database administrator (DBA).
• Centralizing management and security control functions creates several drawbacks:
Business Processes
• A value chain is an accounting system viewed as a collection of processes rather than
financial statements.
2-4 CHAPTER 2 • DATABASES AND ACCOUNTING SYSTEMS
Sales/Collection Process
• Includes all sales and cash collection activities.
• Transactions recorded include customer orders, sales, and cash receipts.
Acquisition/Payment Process
• In a manufacturing firm, the acquisition and payment processes includes all activities related
Human Resources Process
• HR processes revolve around employees and include calculating employee pay, deductions,
and net pay.
• HR reports include checks to employees and vendors, commission reports, timecard reports,
and payroll registers.
Financing Process
• Cash receipts and payments related to equity and debt financing fall are part of the financing
process.
• Recorded financing transactions involve issuance of stock, receipt of cash for stock,
declaration of dividends, and payment of dividends.
Accounting Information Systems and Database Systems
• Historically, accounting information has been captured in journals and posted to ledgers.
➢ Strict rules were applied to the process of gathering data and presenting information.
➢ Standard reports (e.g., Income Statement or Balance Sheet) provided a specific set of
information with a format and level of aggregation that was determined before the reports
were generated.
➢ Off-the-shelf accounting software provided these standard reports.
• Modern accounting systems are usually built on relational databases and allow management
• Events account records details about economic events in a database including who was
involved, when the event occurred, and what resources were affected.
Database Management Systems
• Database management systems (DBMSs) are valuable to business enterprises because they
provide the software to store, retrieve and modify crucial business data.
Pre-DBMS Data Acquisition and Reporting
CHAPTER 2 • DATABASES AND ACCOUNTING SYSTEMS 2-5
• Custom or unusual reports had to be specially designed and written, often a time consuming
and expensive task.
Functions of a Database Management System
The capabilities that a database management system provides in development of an information
system are:
• Efficient data storage, update, and retrieval.
• User accessible catalog.
• Concurrency control.
• Transaction control.
Advantages of Database Management Systems
• Subschema: Provide each user with particular or unique view (authorized access) of the
database.
• Data independence: system’s ability to hide the details of the physical storage of information
from the application programs, or the user’s views of access techniques and methods.
• Structure changes are transparent.
• Data sharing: centrally stored information is current and consistent and everyone can have
access.
Disadvantages of Database Management Systems
• Secondary (disk) storage—DBMS’s require more storage space than flat files.
• Additional people—database experts may be needed to operate system.
Relational Database Management Systems
• Implementation of RDBMS requires a data model—an abstract representation of a database
system providing a description of the data and methods for accessing the data managed by
the database.
• Three models have been used during the history of databases:
2-6 CHAPTER 2 • DATABASES AND ACCOUNTING SYSTEMS
Database Objects
• RDBMS model defines the conceptual view that the user has of all the objects contained by
the database system.
➢ The RDBMS model represents both data and relationships between them.
➢ The order (position) of the columns in relation to each other is unimportant.
➢ Each row is unique from all other rows in the relation.
• Customer table—Coffee merchant (Figure 2.4)
➢ Rows represent customers—each row must be unique
➢ Columns represent attributes of each customer, schema for table includes: CustomerID
(primary key), CompanyName, PhoneNumber, and Contact
➢ Attribute values are within columns
Primary and Foreign Key Attributes
• Primary key is a column (or group of columns) that uniquely identifies a given row—the
CustID column, for example (Figure 2.5).
• Data Dictionary is a collection of tables containing the definition, characteristics, structure,
and description of all data maintained by the RDBMS.
• Foreign key is an attribute in one table that must match the primary key in another table—the
CustomerID column of the tblInvoice table, for example, shown in Figure 2.5.
• tblInvoice and tblCustomer tables can be joined on the CustID/CustomerID fields.
• Each column of a table is an attribute.
• Columns, including the primary key column and any foreign key columns, can appear in any
order.
• Microsoft Access does not require that each table have primary key, but each table should
have one.
Schema of a Relation
• A schema is a representation of a table that lists all its attributes and identifies the primary
key and, optionally, foreign keys
Data Dictionary
• Every relational database system has a data dictionary.
CHAPTER 2 • DATABASES AND ACCOUNTING SYSTEMS 2-7
• Data Dictionary is a collection of tables containing the definition, characteristics, structure,
and description of all data maintained by the RDBMS.
• Information about tables and their attributes is self contained in the database.
• When a user changes a structure element of a table or other database object, the RDBMS
automatically changes the object’s definition in the data dictionary to keep it constantly
The Coffee Merchant Tables
• Figure 2.6 shows the schema of all the tables that make up the Coffee Merchant invoicing
system.
• The students should study the primary-key-to-foreign-key relationships in Figures 2.7
through 2.9.
Normalization
• Normalization is the process of determining the correct location for each attribute to meet the
relational database rules.
• Unnormalized databases lead to redundant, inconsistent, and anomalous information being
➢ tblCountry—contains country names for the World and coffee/tea export data; pk is
CountryID.
First Normal Form
• First Normal Form (1NF) requires that repeating groups (unnormalized table Figure 2.10) be
eliminated
2-8 CHAPTER 2 • DATABASES AND ACCOUNTING SYSTEMS
➢ New customer cannot be added without an invoice being generated—known as an
insertion anomaly—invoice attributes (e.g., Invoice) cannot be null according to
relational database rules.
➢ Invoice cannot be deleted without deleting customer information—known as a deletion
anomaly.
Second Normal Form
• Second Normal Form (2NF) requires that each non-key attribute be fully dependent on the
entire primary key, not just part of it (e.g., Figure 2.11—Total is functionally dependent on
Invoice because a value of Invoice determines a single value of Total, not the primary key,
CustID).
• Figure 2.16 shows functional dependencies in the Customer table.
• To attain 2NF status, break Customer table into two tables (Customer and Invoice) and add
an additional attribute—a foreign key—linking both tables on their keys CustID and
CustomerID, respectively.
Third Normal Form
• Third Normal Form (3NF) requires that a table be in 2NF with all transitive dependencies
eliminated (that is, all attributes functionally dependent on the single-attribute primary key).
➢ Transitive Dependency occurs when, for example, attribute B determines attribute C, and
C determines D (Figure 2.14).
Table Relationships
• Process of normalizing creates additional tables with relationships maintained by foreign key
to primary key links.
• Three fundamental relationships exist between related tables:
➢ One-to-one (1–1)—e.g., a master table contains customer information—a related table
contains occasional notes about a few customers.
➢ One-to-many (1–M)—e.g., a master table contains customer information—a related table
has unpaid invoices for each customer.
CHAPTER 2 • DATABASES AND ACCOUNTING SYSTEMS 2-9
➢ Many-to-many relationships are handled by creating an additional table—the relationship
table—that consists entirely of the parent tables’ primary keys. (The relationship table
can contain other columns as well.)
➢ Most one-to-one relationships indicate unnecessary tables in the database design.
Normally, you should merge the two tables. Exceptions occur when there are just too
many columns in one table, or one group of fields is used far more frequently than
another group. Then, you can consider separating them into different tables for
efficiency’s sake.
Fundamental Relational Database Operations
Select
• Select operator chooses a set of rows from a table (Figure 2.15)—the query selects rows in
which the hire date later than 1/1/2008 and delivers answers in table form.
Project
Join
• Join provides the ability to pull together data from disparate but associated tables into a
single, virtual table based on a common attribute (Figure 2.17)—EmpDivisionID is foreign
key in tblEmployee table and primary key in tblEmployeeDivision table. Instructors note the
following: The tblEmployeeDivision table has been omitted from this edition of the textbook
for simplification reasons.
• Equijoin—rows from the two tables are linked or chained together on matching join column
Introduction to Database Design
• An important aspect of database design is the care in choosing the tuples and attributes that
each table comprises. This activity is called modeling
Developing Entity-Relationship Models
• Another way to model database objects, called Entity-Relationship (E-R) modeling, to
represent a business is a graphical approach using three terms to describe a company’s
information.
➢ Entities (nouns) are the objects found in the company (e.g., invoices, purchase orders,
etc.)
➢ Relationships are the way in which the distinct entities interact or are related to one
2-10 CHAPTER 2 • DATABASES AND ACCOUNTING SYSTEMS
➢ Lines represent the connections between the two.
➢ Digit or letter above line indicates degree of the relationship: 1–1, 1–M, M–M.
• Completed E-R diagrams can be combined into a system E-R diagram—a process called
view integration.
➢ Process started by placing the most often used entity in the center of the diagram.
➢ Lines connect the related entities.
➢ Identify and create tables from entities.
Resources, Events, Agents (REA) Modeling
• In the late 1970s, Professor William McCarthy drew upon the principles of relational
database theory developed by E. F. Codd and the entity-relationship modeling principles of
Peter Chen to
➢ create a modeling approach specifically designed for accounting systems.
➢ develop an entity classification system that would allow accountants to use relational
databases handle accounting information.
➢ Eric Denna devised a REAL model, which added an entity classification for location
➢ Most accounting researchers agree that the REA model has considerable value as an
approach to the design of accounting information systems
➢ Figure 2.19 shows a list of entities for a purchase cycle accounting system. The entities
are classified using the REA model.
CHAPTER 2 • DATABASES AND ACCOUNTING SYSTEMS 2-11
Answers to Review Exercises
Multiple Choice Questions
1.
b
6.
c
Discussion Questions
The solutions presented here come from the textbook discussion. Your students may include other
insight that is relevant but not presented in these solutions.
1. Double-entry bookkeeping provided an excellent way of recording transactions for many
years. It helped accountants capture the essence of each transaction in a safe, effective
manner. The double-entry nature of the approach also provided an automatic internal control
2. In manual systems, the dual nature of the accounting debit and credit model provides a built–
in error correction mechanism. In automated systems, this same duality is inefficient and
3. Problems of storing data in two or more places include the creation of outdated and
redundant data. Years ago, it was common for departments and individuals to create and
2.
d
7.
a
3.
b
8.
4.
a
9.
5.
c
10.
2-12 CHAPTER 2 • DATABASES AND ACCOUNTING SYSTEMS
While the master list of customers was kept current by the data processing department from
4. The primary key consists of one or more fields in each table that provides a unique identifier
for each row in the table. Every table in a relational database (a table in a relational database
is also called a relation) must have a primary key that is unique and that exists for each row
Practice Exercises
The solutions are included in the Ch02-PracticeExercises.accdb file on the Instructor web site.
1. One possible solution is shown below in Design view and Datasheet view.
CHAPTER 2 • DATABASES AND ACCOUNTING SYSTEMS 2-13
2. One possible solution to this exercise is the database shown in the following figure, which
shows a screen capture of an Access Relationships window. In creating this file, we assumed
that the grocery store does not track its customers’ names or addresses and that it only
3. The additional entities would be best modeled in two separate tables, an Office table and a
4. Omit the conversion process bubble and the related resource flow arrows from Figure 2.2.
5. The problem is that age is a value that changes each year. The solution is to store a person’s
Problems
1. One possible solution is the object qryProblem01, which is included in the
2-16 CHAPTER 2 • DATABASES AND ACCOUNTING SYSTEMS
2. The Invoice Line table is a relationship table. All relationship tables have a composite
primary key that includes two or more individual fields (the primary key fields of the entity
3. The relationship between the Instructor table and a Classes table is one-to-many (1—M).
Each instructor teaches many classes (unless he or she is a researcher and teaches only one