Accounts Payable Master File Supplier Account Number Information Technology Essay

Almost every AIS must organize and store data in permanent files. This leads to the need for databases that help users store, modify, extract, and distribute accounting data. The text discusses seven specific reasons why databases are important to AISs: (1) the fact that AIS databases usually store valuable information, (2) the large volume of data in some databases, which increases the importance of organizing data efficiently, (3) the complexity of modern database designs (e.g., because of client/server networks), (4) the need for privacy and security, (5) the fact that most AIS databases contain irreplaceable data, (6) the importance of accuracy, and (7) Internet uses such as storing online customer transactions. The first section of the chapter discusses each of these reasons in greater detail.

13-2. The hierarchy of data describes the fact that AISs store the data in a database in natural levels. These are, from smallest to largest:

bit â†’ character â†’ data field â†’ record â†’ file â†’ database

Almost any accounting application fits this hierarchy. For example, payroll records consist of characters that in turn form data fields, records, files, and databases.

13-3. The data field in each record that uniquely distinguishes one record from another on a computer file is called the primary record key or just the primary key. Typically, the record key is numeric, although alphabetic or alphanumeric record keys are also possible. A record key can be a simple number such as a customer account number, or a complicated value such as (1) a composite number (e.g., a bank account number including branch and individual account number) or (2) a large number with imbedded codes (e.g., a credit-card number).

In addition to primary record keys, many AIS databases employ secondary record keys to help users identify records or extract database information. These secondary record keys are rarely unique. Examples include customer names, department codes, pay rates, and zip codes. Finally, AIS databases use foreign record keys to link the records in one database table to the records in other tables. The presence of all these record keys may seem complicated, but their uses are vital to the efficient functioning of the databases used by AISs.

13-4. Here are some examples of typical accounting information system files and the potential record keys used for each:

Computer File Potential Record Key

Accounts Receivable Master File Customer account number

Accounts Payable Master File Supplier account number

Employee Payroll File Employee social security number

Employee Personnel File Employee social security number

Chart of Accounts File Journal category code

General Ledger File General Ledger account number

Budget File General Ledger account number

Purchase Order File Invoice number

Inventory File Inventory Part number

Checkâ€‘Writing File Check number

Transaction File Account number and date

13-5. The data in large, commercial databases pose special challenges for database designers and users. Administration needs supervise and coordinate organizational data typically gathered and used by many different functional areas of business and databases must be modified, updated, or audited. Some major concerns are: (1) data integrity, which includes the requirement that stored data are both complete and completely accurate, (2) processing accuracy and completeness, which includes the concern that any changes made to a database are performed properly and completely, (3) concurrency, which means ensuring that two users do not access and sequentially change the same database record at the same time, and (4) backup and security, which includes the requirement that database information be protected from external access and fraudulent manipulation. The text describes each of these concerns in greater detail.

13-6. The term “REA” is an acronym for “resources, events, and agents.” In the REA model, an AIS database stores information about these file entities-for example, information about inventories (resources), cash sales (events), and customers (agents). The REA model differs from traditional accounting systems in that REA databases tend to store information about resources, events, or agents that do not immediately affect the financial statements of a company. Two examples of such information mentioned in the text are “sales orders” and “hiring decisions.” Similar information includes data about customer demographics, interest rates, or competitor activities.

13-7. Database cardinalities represent the relationships between database entities-for example, one-to-one, one-to-many, or many-to-many. In a payroll application, for example, a one-to-one relationship would be “employee” and “social security number,” a one-to-many relationship would be “employee” and “insurance beneficiaries,” and a many-to-many relationship would be “employees” and “pay rates.”

13-8. The entity-relationship (E-R) model is a graphic tool for helping developers design databases. As shown in Figure 13-5, ovals denote data attributes (e.g., hourly pay rate), rectangles denote file entities (e.g., an employee), diamonds denote relationships (e.g., one-to-many), and straight lines denote data flows.

13-9. Assuming that the records in the Salesperson table stores information about individual sales people (e.g., name, employee number, office phone number, etc.) and that the records in the Sales table stores information about individual sales transactions (e.g., date, amount, type of payment, salesperson number, etc.), the relationship between these two tables is one-to-many. This means that each salesperson could have many sales transactions, but each sales transaction could have, at most, one salesperson. To show the sales for each salesperson in any given month, you would need to create a relationship between them to link the file records in these two tables together. The use of foreign keys would be sufficient to do this, and you would not need to create an intermediate relationship table for this purpose.

13-10. It is crucial that primary key values be stored accurately within the different tables of the same database. For example, if a Salesperson table uses an employee number in the form “123-456” (with a dash in the middle), a Sales table should use this same format as a foreign key (rather than, say, the number “123456” without a dash). If primary key values are not entered consistently and accurately, the application will not be able to link file records together, and therefore be unable to extract the information that a user requires. In other words, the primary key will lose its integrity as a foreign key.

13-11. The seven major objects that you can create in Access are tables, forms, queries, reports, pages, macros, and modules. However, there are yet further objects you can create with Access. An important additional one is “relationships.”

Problems

13-12.

a) Definitions:

1) Field: A set of characters that describe or define a particular file entity, (e.g., the social security number field of an employee in a payroll file).

2) Record: A set of data fields about one file entity, (e.g., the set of data fields that describe an employee in a payroll file).

3) File: A collection of records about similar file entities, (e.g., a collection of employee records in a payroll file).

b) 1) Database: A collection of data files that are shared by one or more accounting applications. Typical database files include files containing the accounting data themselves (e.g., payroll information), as well as a set of auxiliary files such as inquiry files and index files that help manage this data.

2) Advantages:

a) Eliminates or reduces data redundancy

b) Reduces data conflicts that often result from files of duplicate information

c) Centralizes file information

d) Separates file data from the applications that use them, enabling the application to focus on its tasks rather than the format of the data

e) Availability of excellent software for easily altering, inquiring, or reporting file data

3) Disadvantages:

a) Increases work of creating and maintaining databases

b) Increases coordination among the data needs of several departments requiring, or collecting, the data in the database

c) Increases security for protecting a valuable informational asset

d) Increases complexity of the systems that use them

e) Increases cost

13-13. Data items likely to be included in a cash table are: Cash Account #, Type of Account, Bank, Beginning Balance, Beginning Balance Date. As suggested by the underlining, the Cash Account # would be the primary key.

The Cash Receipts table would include: Cash Receipt #, Cash Account #, Date Received, Employee#, and Amount. Cash Receipt# is a primary key, Cash Account # and Employee# are foreign keys.

13-14. a) The file entity “student” has three attributes: (1) student number (used as a primary key), (2) name, and (3) address

b) This diagram shows a one-to-many relationship: A sales account representative has numerous customers.

c) This diagram shows a one-to-one relationship: A credit card has a PIN number.

d) This diagram illustrates a supertype relationship: The airline passenger supertype can be a first-class passenger, a business-class passenger, or a coach-class passenger.

13-15.

13-16. This problem requires students to log onto Stevie at www.aisvillage.com/stevie to learn about database cardinalities.

13-17. There are several other database management systems available to users, including MySQL, Oracle, FoxPro, Avanquest Database Professional, Microsoft SA SQL, Filemaker Pro 7, Eltron Card Database, and IBM DB2.

Here are some characteristics of data fields for the Customer Table of Figure 13-6:

Data field

Data type

Size

Customer Number

Text

Last Name

Text

First Name

Text

Street Address

Text

City

Text

State Abbreviation

Text

Zip Code

Numeric

Credit Limit

Numeric

7 (long integer)

As shown here, it would be better to use separate fields for the customer’s first and last names. This will enable the system so search for a customer by either first or last name separately.

13-19. The different data types available in Access include: Text, Memo, Number, Data/Time, Currency, AutoNumber, and Yes/No. You can also create hyperlinks and OLE objects. The types of numbers you can create in Access include byte, Integer, Long Integer, Single, Double, ReplicationID, and Decimal.

13-20. This problem requires students to create their own Salesperson table, using the information provided in Figure 13-6.

Case Analyses

13-21. The Carl Beers Enterprises (Using a Relational Database)

This case helps students understand what types of information might be stored in accounting databases, and requires them to use this information to answer important accounting questions. Answers to specific questions:

1. Sales data that are listed by inventory item help inventory personnel identify both fast- and slow-moving items, track customers in the event of recalls, and replenish stock. If this information were listed by invoice number, it would provide the detailed invoice information needed to reconstruct the invoice amounts listed in the “sales by invoice number” records (this would make it a lot easier to answer question 2).

2. Invoice V-3 shows a sale to customer C-5, or D. Lund, Inc. Details of this sale, found in the “sales by inventory number” records, are:

Item Quantity Price Extension

I-1 1 2,000 2,000

I-3 6 1,000 6,000

I-5 2 4,000 8,000

Total 16,000

3. Invoice V-2 shows that J.P. Carpenter purchased $10,000 worth of equipment, but made a payment of only $1,666. Thus, this customer has opted to pay over six months.

4. The quarterly sales amounts for the sales staff are:

a) $87,600 for salesperson S-10

b) $23,000 for salesperson S-11

c) $7,200 for salesperson S-12

Note that accounting personnel would have to make additional computations (based on information readily available in the databases) if sales commissions were only paid on the paid sales of customers instead of gross sales.

5. The net accounts receivable amounts are:

a) C-1 = 0

b) C-2 = $6,688

c) C-3 = $35,000

d) C-4 = $23,000

e) C-5 = 0

13-22. Martin’s Shoes, Inc. (Planning a Database Using REA and E-R Methodology)

The resources are cash and inventory. The events are sales orders, shipping goods, and cash collections. The agents are customer, sales personnel, shipping clerks, and billing clerks.

Database tables are: cash, sales orders, shipped goods, cash collections, customers, and employees. There should also be a table for the order/inventory and ship goods/inventory relationships as these are many-to-many relationships. An example of the data fields in the cash table includes: account number, cash type, beginning balance, and authorized personnel.

13-23. Souder, Oles, and Franek LLP (Data Modeling with REA)

2. Receive Goods Table: Receipt #, [Employee#], [Vendor#], Date, Comments

Pay for Goods Table: Cash Payment#, Amount Paid, Date, [Employee#], [Account#]

Merchandise Inventory Table: Item#, Description, Unit Cost, Sales Price, Beginning Quantity on Hand, Beginning Quantity on Hand Date

Cash Table: Account#, Account Type, Bank, Beginning Balance, Beginning Balance Date

Employee Table: Employee#, First Name, Middle Name, Last Name, Address, City, State, Zip Code, [Department3], [Job Classification Code], Date of Birth, Date Hired, Last Date of Review

Inventory/Order Table: Order#, Item#, Quantity

Inventory/Receive Table: Receipt#, Item#, Quantity

Order/Receive Table: Order#, Receipt#, Quantity Received

Receive/Pay Table: Receipt#, Cash Payment#, Amount Paid

13-24. BSN Bicycles (Starting from Scratch with Microsoft Access)

1. The resources, events, and agents for this case are as follows:

Resources: inventory and cash

Events: sales, cash receipts, purchases, and cash payments

Agents: the company’s employees (sales personnel, cashiers, and purchasing agents) customers, and vendors.

An E-R diagram may be found on the following page.

2. Examples of data fields for each of these tables:

cash: account #, cash type, beginning balance, authorized personnel

inventory purchases: purchase order #, item #, item name, quantity purchased, vender ID, vendor name

vendor cash payments: check #, vendor ID, vendor name, purchase order #, amount

vendors: vendor ID, vendor name, street address, city, state, zip code, contact person, phone #, and fax #

employees: employee ID, first name, middle initial, last name, department #, street address (probably not needed inasmuch as there are only three individuals in the company)

inventory table: item #, item description, units (e.g., dozens), unit cost, unit retail sales price, quantity on hand

vendor purchases/inventory (join) table: purchase order #, item #, quantity purchased

customers: First name, Last name, Customer #, Street Address, City, State, Zip Code, Home phone number, Work phone number, Cell phone number, Credit Card type (e.g., Visa), credit card number, credit card expiration date

customer invoices: invoice #, invoice date, invoice amount

Database tables for the purchasing process are: cash, purchases, cash payments, vendors, and employees. There should also be a table for the purchases/inventory relationship as this is a many-to-many relationship.

3. This part of the case requires students to create several records for each table.

4. This part of the case requires students to create relationships for each of the various tables.

5. This part of the case requires students to print hard copies of each table in data sheet view and also to create a report, documenting their relationships.

E-R Diagram for Part 1:

Employees (purchases)

Employees

(salesperson)

(0,N) (0,N)

(1,1) (1,1)

Sales

Purchases

(1,N)

(1,1)

(0,N) (1,N) (0,N) (0,N)

Vendors

(suppliers)

Customers

Merchandise

Inventory (0,N) (0,N)

(0,N)

(0,N) (1,N) (1,N)

Cash Payments

Cash Receipts

(1,1) (1,1)

(1,1) (1,1) (1,1) (1,1)

Employees (cashier) (0,N) (0,N)

Cash

Employees (cashier) (0,N) (0,N)

Order Now