Chapter 2 Accounting Database PDF

Preview

Summary

Download Chapter 2 Accounting Database PDF

Description

Accounting Databases Can a Faulty Database Cost American Consumers Billions? Meet Ingenix. You’re the Boss… If you were the CFO of United Healthcare, what would you say to the American consumer? How would you explain they were overcharged billions for their healthcare because of a flawed database?

A U.S. Senate Committee found that due to a faulty database “American consumers have paid billions of dollars for health care services that their insurance companies should have paid.” Basically, the database was undercharging the insurance companies for their share of medical bills and overcharging the patients by billions of dollars. Two-thirds of U.S. healthcare insurers, including United HealthCare and Aetna, used the flawed database operated by Ingenix.

Crossroads At the crossroads of accounting and IT, we focus on the integral role that databases play in accounting today.

My Questions

40

Q1

What role does the database play in an accounting system?

Q2

What is the role of the database in an enterprise?

Q3

How do I build an accounting database?

Q4

What is database integrity?

Q5

What are the differences between a manual accounting system and a database accounting system?

What Role Does the Database Play inan Accounting System? Do you shop online? When you make a purchase online, what information do you enter? Where do you think your information is stored? Have you ever purchased items more than once from the same online retailer? Does your information, such as shipping address, automatically appear on the screen? Imagine you place an online order with Amazon.com, the world’s largest online retailer. The information you enter into the online order form is stored in a database. Databases store information about online purchases, shipping addresses, billing addresses, and various accounting transactions. One of the benefits of a database is that the information, such as your shipping address for online purchases, can be stored and retrieved to be reused at a later time. Also, your shipping address can be shared electronically with the delivery service, such as FedEx. As a customer, you can track the status of your shipment in real-time over the Internet. The use of databases permits the data to be shared between you, Amazon, and FedEx. This brings us to an Accounting Insight.

Amazon.com… processes millions of transactions a day, reporting over $34 billion in revenue (Amazon annual report 2010).

Accounting Insight: Enter Data Once This Accounting Insight states that when you enter data into a database, you want to enter the data once. One of the advantages of the relational database is that we can enter data into the database once, and then reuse that data in various locations when and where it is needed. Imagine that as an accounting professional for EspressoCoffee you will be entering customer orders. If EspressoCoffee has a customer named Vincent Pico whose address is 58 Dante, Pisa, Italy, would you want to enter the customer’s address every time the customer placed an order? Wouldn’t you prefer to enter the customer’s address once and then store it in the CUSTOMER table? The address in the CUSTOMER table could be reused each time the customer places an order, with the address appearing on the order invoice. Better yet, wouldn’t you prefer that customers entered their addresses so you didn’t have to enter it for them?

When Data Needs Scrubbing…. Dirty data occurs when errors are entered into a system. Consider the case of a stock trader erroneously entering a “b” (for billion) instead of an “m” (for million), causing the New York Stock Exchange to plummet. This actual event occurred in May 2010. After the “b” was entered, programmed trading using algorithms kicked in, resulting in the stock market plummet. Accenture, a technology consulting firm, saw its stock drop from over $40 at 2:47 PM to less than a penny one minute later at 2:48 PM that day. Imagine that you go to your local bank to wire $1,800 to your brother in Zurich, Switzerland. A couple of days later when you log in to your online bank account, you notice that your account balance is much less than you anticipated. Then you see a withdrawal for $18,000. When you return to your local bank to unravel what has happened, you discover that the bank manager mistakenly entered an extra “0,” resulting in $18,000 being withdrawn from your account, instead of $1,800. Your brother was gracious enough to authorize the Zurich bank to return the excess funds, after the bank deducted $1,700 in service charges. So an extra “0” cost the bank $1,700 and one unhappy bank customer, who cautioned the bank manager to always check the number before pressing the Enter key. How many times do you check a number before pressing the Enter key?

ACCOUNTING INSIGHT Enter Data Once

When Data Needs Scrubbing…. The sales manager for EspressoCoffee has just landed a large customer order. He is eager to close the deal and has asked for your assistance. He wants to know if the customer he is working with is the same as the customer already in your database. Is Espresso Cafe, 5858 Grande Avenue, Sainte Louis, Missouri, the same customer as Expresso Cafe, 5858 Grand Ave, Saint Louis, MO? What do you say? Is this the same customer? Will your database view it as the same customer?

41

ACCOUNTING DATABASES

There are three main benefits associated with entering data once: ◾

◾

◾

It is more efficient. You do not spend non-value-added time reentering the same information again and again. Reentering the same data again and again increases the chances of entering erroneous data, such as misspellings and transpositions. When you need to update the customer’s address, you only need to update it once. This eliminates the possibility of updating some, but not all, of the other entries.

Accounting Insight: 80/20 Rule ACCOUNTING INSIGHT Use the 80/20 Rule When Designing Accounting Databases… 80% Design 20% Maintenance

Use the 80/20 Rule to design accounting databases. Invest 80% of your time and energy on the planning and design of the database. If there is a sound design, then only 20% of your time will be spent on maintaining and updating the database. If the 80/20 Rule is reversed and you spend 20% of your time and energy designing the database, then you can expect to spend 80% of your time maintaining a poorly designed accounting database.

80/20 Rule…

Accounting Databases: Behind the Screen

The 80/20 Rule is also called the Pareto Principle, named after the Italian economist Vilfredo Pareto, who noticed that 20% of the population in Italy received 80% of the income. Some businesses find that 20% of their customers generate 80% of their revenues. Of course, the key is to know which customers make up that 20%. Can you think of any other ways you can use the 80/20 Rule?

Nearly all accounting information today is stored in databases. As you learned in Insight, to understand accounting systems, the accountant must understand databases. Large-scale accounting systems, such as SAP, and even small business software like QuickBooks use databases to store accounting data. Behind the front-end accounting software screen is an accounting database and database management system (DBMS) software. As you can see in Figure 1, accounting data is entered into accounting software using onscreen forms. The data is passed to the DBMS software, which stores it in the appropriate database table.

Accounting Database

Accounting Software… provides an interface for the user to: 1. Input data using onscreen forms. 2. Output data using reports.

FIGURE 1 Accounting Database Behind the Screen Pair and Share: Share with a classmate your response to the following: When you enter data into accounting software, what happens behind the screen? 42

DBMS

Front-end Accounting Software

ACCOUNTING DATABASES

DBMS… is software used to: 1. Create database tables. 2. Transfer data from the accounting software to the database. 3. Update data in the database. 4. Delete data from the database. 5. Sort database tables. 6. Run queries.

Crossroads… What drives a database engine? When IT professionals talk about a database engine, what are they talking about? A database engine is a database program, such as MS Access, that includes DBMS software. The DBMSsoftware drives the database engine.

The DBMS software processes the accounting data and the accounting database stores accounting data. When you request an accounting report, the accounting software interacts with the DBMS software to retrieve the requested data from the specified tables in the accounting database to generate the report. Figure 2 shows the three tiers in the accounting system architecture: ◾ ◾ ◾

Database tier Application tier User tier

Operational Database Database Tier Database + DBMS

Database Management System (DBMS)

Accounting Software

Accounting Interface

Accounting Interface

Application Tier Accounting Software

User Tier Database Forms and Reports

FIGURE 2 Accounting System Architecture Pair and Share: Share with a classmate your response to the following: When you enter new customer information into accounting software, can you trace that data in this figure to the database table in which it would be stored? 43

ACCOUNTING DATABASES

Oracle…A Database Giant… Oracle has over 30 million lines of programming code. When a defect in the code is detected, a patch of programming code is used to fix the defect. One Oracle defect required 78 patches at a cost of over $1 million.

My Connection… Make connections. Try:

The database tier consists of the database and the DBMS. DBMS software is used to insert, update, and delete data in the database. All database engines, from Microsoft Access to large Oracle databases, use DBMS software. The application tier consists of software applications, such as accounting software. The accounting software interacts with a DBMS to add, update, and delete accounting data in the database. The user tier consists of the user interface that appears on the accountant’s computer screen. As an example, a QuickBooks Customer List is related to the Customer database table. Each time you add a customer to your QuickBooks Customer List, you are adding a new record to the Customer table. When you enter a customer name on a sales invoice in QuickBooks, customer information from the Customer List, such as address and contact information, is automatically completed on the sales invoice. Behind the screen, QuickBooks is retrieving this data from the Customer database table and inserting it into the sales invoice form on your computer screen. When the accounting professional understands how the accounting data is stored and retrieved in a database, it is easier to trace errors. An accounting database is one of several types of databases used by enterprises. Next, we will look at the different types of databases an enterprise might employ.

What Is the Role of the Database inan Enterprise? Databases provide enterprises with the capability to collect and use data that simply was not possible or cost effective before the advent of databases. For example, databases can be used to collect large amounts of data about customers that later is analyzed for business intelligence. The database administrator (DBA) is responsible for managing the enterprise’s databases. The DBA, together with internal auditors, establish policies and procedures for database security, including backup and disaster recovery. Databases used by an enterprise can be considered as one of two types: internal databases or external databases (Figure 3).

Internal Databases Internal databases are databases in which the data belongs to the organization. Internal databases consist of two varieties: operational databases and data warehouses. Operational databases are used to store data related to operating the business. For example, Apple Inc. would use an operational database to store and track data about business operations, such as sales of iPads. Data stored in operational databases include data collected from enterprise accounting transactions, such as vendor transactions, customer transactions, and employee payroll. The operational database also includes data about people, including vendors, customers, and employees. Data in the operational databases are stored using a unique identifier or primary key so specific data can be retrieved. Data warehouses store data from a variety of sources. Data stored in the data warehouse may be current data, historical data, or future estimates. The difference between the operational database and the data warehouse is that the data warehouse is not used for routine business activities. Instead, the data warehouse is often used for business intelligence purposes to improve management decision making.

External Databases External databases are databases containing data collected by other organizations, including: ◾

◾

44

Governmental agencies, such as the Internal Revenue Service (IRS) and the Securities and Exchange Commission (SEC) Research organizations, such as Gartner, Inc., and Marketing Research Association (MRA)

ACCOUNTING DATABASES

Data Warehouse External Databases

Operational Database

DBMS

SCM

Accounting

OPS

HRM

CRM

Accountant

Enterprise Software

FIGURE 3 Enterprise Databases As an accounting professional, which of the enterprise databases do you think you would use? ◾ ◾

Financial organizations, such as Dow Jones National and international trade organizations, such as Organic Trade Association

Data that can be obtained from these external databases include industry market indices data, market forecasting data, census data, and unemployment data. For example, your enterprise might use data from Gartner, Inc., for information about IT technology research and trends.

Database Structures

When we use the term database we are referring to the operational database.

The databases that an enterprise uses can be structured in different ways. The structure of a database pertains to how the stored data within the database is related to other stored data. How the data is stored, or structured, in a database affects how the data is retrieved. There are three basic database structures: ◾ ◾ ◾

Relational database Hierarchical database Network database 45

ACCOUNTING DATABASES

Database vs. Traditional File Systems… In the 1950s and early 1960s, organizations stored their operational data in files instead of databases. Each file was designed independently of other files, resulting in data redundancy and inconsistency. Today, most organizations store their operational data in well-designed, anomalyfree databases. Advantages of databases over traditional file systems include data sharing, centralized control, redundancy control, improved data integrity, improved data security, andreduced data maintenance cost.

My Connection…

Relational databases store data in database tables. These tables are related to each other using common fields in two different tables. These common fields are referred to as the primary key and the foreign key. For example, the Customer No. field is the primary key in the Customer table and the foreign key in the Sales Order table. The Customer No. field connects these two database tables. To retrieve information about a specific customer, such as name, address, and recent orders, the enterprise would search two database tables: the Sales Order table and the Customer table. The Sales Order table would be searched for a specific Customer No. to retrieve the sales order data. The Customer table is searched for the same Customer No. to retrieve the customer’s name and address. Data is retrieved by using the relationship between tables, thus it is called a relational database. Currently, most operational databases are relational databases due to storage space and processing time advantages. Therefore, in this text, when we refer to a database, we are referring to a relational database (RDB). Accordingly, RDB terminology is used throughout the text.

Hierarchical and Network Databases… Hierarchical databases and network databases store data in records that are not grouped into database tables. When data needs to be retrieved, instead of using the relationships between tables to retrieve data, hierarchical and network databases use record addresses to search for data. In hierarchical and network databases, records are categorized as parent or child records. In a hierarchical database structure, a parent record can have many child records and each child record can have only one parent record, resulting in a hierarchical structure, similar to your ancestry tree. In a network database, a parent record can have many child records and a child record can have many parent records, resulting in a network of records.

How Do I Build an Accounting Database? Database Essentials Revisited Database essentials that an accountant needs to know include the following database elements: ◾ ◾ ◾ ◾

Fields: Pieces of information about events, people, or objects Records: Collection of related fields Table: Collection of related records with a unique table name Database: Collection of related tables

Database fields are pieces of information about events, people, and objects. In the Customer table shown in Figure 4, there are 10 fields: Customer No. Company Name Last Name First Name Street Address City State ZIP Country Account Balance As shown in Figure 4, the field First Name contains the names of specific customers: Angela and Vincent.

46

ACCOUNTING DATABASES

FIGURE 4 Customer Table Pair and Share: How many fields do you see in this database table? How many records?

Database records are a collection of related fields, populated with data. The table in Figure 4 contains two records (rows). Each record is populated with data about properties of the specific customer. For example, the first record relates to the customer named Angela Ashuer who is located in Appleton, Wisconsin, and her Customer No. is 127127. In order to retrieve data stored in the database, each record must have one or more fields that are unique identifiers for each record in the database. The unique identifier is called a primary key. For example, in the Customer table, the unique identifier is the customer number field (Customer No.) because each customer should have a different customer number. A database table is a collection of related records with a unique table name. A table stores records of the same type, such as customer records. For example, the table in Figure 4 contains data about two specific customers and accordingly is called the Customer table. A database is a collection of related tables. In a relational database, the database tables are related or connected through fields common to two or more tables. For example, the Customer No. field appears in the Customer table, and the Customer No. field would also appear in the Sales Order table. Thus, the Customer No. field connects or relates the Customer table to the Sales Order table. Figure 5 illustrates the database elements and hierarchy of field, record, table, and database. Notice that the First Name field, Angela, appears in the Customer record, which appears in the Customer table. Also note that the four database tables (Customer, Sales Order, Sales Order Line, and Item) are related. ...