CHAPTER 4
4.1 Contrast the logical and the physical views of data and discuss why separate views are
necessary in database applications. Describe which perspective is most useful for each of the
following employees: a programmer, a manager, and an internal auditor. How will
understanding logical data structures assist you when designing and using database systems?
Databases are possible because of their database management system (DBMS). As shown in Figure
4.2 The relational data model represents data as being stored in tables. Spreadsheets are another
tool that accountants use to employ a tabular representation of data. What are some
similarities and differences in the way these tools use tables? How might an accountant’s
familiarity with the tabular representation of spreadsheets facilitate or hinder learning how
to use a relational DBMS?
4.3 Some people believe database technology may eliminate the need for double-entry accounting.
This creates three possibilities: (1) the double-entry model will be abandoned; (2) the double-
entry model will not be used directly, but an external-level schema based on the double-entry
model will be defined for accountants’ use; or (3) the double-entry model will be retained in
database systems. Which alternative do you think is most likely to occur? Why?
4.4 Relational DBMS query languages provide easy access to information about the
organization’s activities. Does this mean that online, real-time processing should be used for
all transactions? Does an organization need real-time financial reports? Why or why not?
4.5 Why is it so important to have good data?
$3.1 million property tax revenue shortfall, the city, the school district, and governmental
4.6 What is a data dictionary, what does it contain, and how is it used?
4.7 Compare and contrast the file-oriented approach and the database approach. Explain the
main advantages of database systems.
Information about the attributes of a customer, such as name and address, are stored in fields.
Fields contain data about one entity (e.g., one customer). Multiple fields form a record. A set of
related records, such as all customer records, forms a file (e.g., the customer file). A set of
Databases provide organizations with the following benefits:
4-4
SUGGESTED ANSWERS TO THE PROBLEMS
4.1
a. Identify three potential users and design a subschema for each. Justify your design by
explaining why each user needs access to the subschema data elements.
• To fill out a sales order, the sales order entry clerk needs access to the following data:
• To create and mail a bill (invoice), the billing clerk needs access to the following data
stored in the database:
• To manage inventory, the inventory control department needs access to the following
data stored in the database:
• To purchase inventory, the purchasing department needs access to the following data
stored in the data base:
Accounting Information Systems
4-5
Accounting Information Systems
4-6
b. Use Microsoft Access or some other relational database product to
create the schema tables. Specify the primary key(s), foreign key(s), and
other data for each table. Test your model by entering sample data in each
table.
Table Name
Primary Key
Foreign Keys
Other Attributes
Inventory
Item Number
Cost (standard or list)
Description
Quantity on Hand
Price (standard or list)
Sales
Invoice number
Customer number
Date of sale
Terms
Sales-Inventory
Item number
Invoice number
Quantity sold
Price (actual sales price)
Customer
Customer number
Customer name
Shipping address
Billing address
Credit Limit
Account Balance
4-7
4.2 Most DBMS packages contain data definition, data manipulation, and data query languages.
For each of the following, indicate which language would be used and why.
1. A database administrator defines the logical structure of the database
b. The controller requests a cost accounting report containing a list of all employees being
paid for more than 10 hours overtime in a given week.
c. A programmer develops a program to update the fixed-assets records stored in the
database.
within five years.
e. The inventory serial number field is extended in the inventory records to allow for
recognition of additional inventory items with serial numbers containing more than 10
digits.
f. A user develops a program to print out all purchases made during the past two weeks.
g. An additional field is added to the fixed-asset records to record the estimated salvage
value of each asset.
4-8
4.3 Ashton wants to store the following data about S&S’s purchases of inventory:
item number date of purchase
vendor number vendor address
vendor name purchase price
quantity purchased employee number
employee name purchase order number
description quantity on hand
extended amount total amount of purchase
a. Design a set of relational tables to store this data. Do all of the data items need to be
stored in a table? If not, which ones do not need to be stored and why do they not need
to be stored?
b. Identify the primary key for each table.
c. Identify the foreign keys needed in the tables to implement referential integrity.
Table Name
Foreign Keys
Other Attributes
Inventory
Description
Quantity on Hand
Purchases
Vendor number
Purchasing Agent
(employee number)
Date of purchase
Total amount of purchase
Purchases-
Inventory
Quantity purchased
Unit cost (actual)
Extended amount
Vendor
Vendor name
Vendor address
Employees
Employee name
Extended amount and Total amount of purchase do not have to be stored in the database as they can
be calculated from other values. Extended amount is Quantity purchased x Unit cost. Total
amount of purchase is the sum of all the extended amounts for all items on a particular purchase
order,
d. Implement your tables using any relational database product to which you have access.
e. Test your specification by entering sample data in each table.
f. Create a few queries to retrieve or analyze the data you stored.
There is no solution to parts d through f as students will select different software packages and
come up with different queries.