CHAPTER 4
RELATIONAL DATABASES
SUGGESTED ANSWERS TO DISCUSSION QUESTIONS
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 DBMS is a software program that sits between the actual data stored in the system and the
application programs that use the data. As shown in Figure 4.4, this allows users to separate the way
they view the data (called the logical view) from the way the data is actually stored (the physical
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?
A major difference between spreadsheets and databases is that spreadsheets are designed primarily
to handle numeric data, whereas databases can handle both text and numbers. Consequently, the
query and sorting capabilities of spreadsheets are much more limited than what can be
accomplished with a DBMS that has a good query language.
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-1
©2018 Pearson Education, Inc.
Ch. 4: Relational Databases
There is no correct answer to this question because it is asking the student to express his opinion on
what will happen in the future. Therefore, the quality of his answer depends on the justifications
provided. Good answers should address the following:
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?
On-line real-time processing is not necessary for every business transaction. For example, batch
processing is adequate for payroll: there is little need for the data to be current except on payday.
4.5 Why is it so important to have good data?
Bad data costs businesses over $600 billion a year. Some people estimate that over 25% of business
data is inaccurate or incomplete. In addition, incorrect database data can lead to bad decisions,
embarrassment, and angry users. The text illustrated this with the following examples:
4.6 What is a data dictionary, what does it contain, and how is it used?
A data dictionary contains information about the structure of the database. Table 4-1 shows that there is
a record in the dictionary describing each data element. The DBMS maintains the data dictionary,
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 interrelated,
4-2
©2018 Pearson Education, Inc.
Accounting Information Systems
centrally coordinated files forms a database.
Databases provide organizations with the following benefits:
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:
4-3
©2018 Pearson Education, Inc.
item number
To purchase inventory, the purchasing department needs access to the following data
description
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.
F
O
t
h
e
r
A
t
t
r
i
b
u
t
e
s
C
o
s
t
(
s
t
a
n
d
a
r
d
4-4
©2018 Pearson Education, Inc.
Accounting Information Systems
o
r
l
i
s
t
)
D
e
s
c
r
i
p
t
i
o
n
Q
u
a
n
t
i
t
y
o
n
H
a
n
d
P
r
i
c
e
(
s
t
a
n
d
a
r
d
4-5
Ch. 4: Relational Databases
o
r
l
i
s
t
)
C
D
a
t
e
o
f
s
a
l
e
T
e
r
m
s
Q
u
a
n
t
i
t
y
s
o
l
d
P
r
i
c
e
(
a
c
t
4-6
Accounting Information Systems
u
a
l
s
a
l
e
s
p
r
i
c
e
)
C
u
s
t
o
m
e
r
n
a
m
e
S
h
i
p
p
i
n
g
a
d
d
r
e
s
s
B
i
l
l
i
4-7
Ch. 4: Relational Databases
n
g
a
d
d
r
e
s
s
C
r
e
d
i
t
L
i
m
i
t
A
c
c
o
u
n
t
B
a
l
a
n
c
e
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.
a. A database administrator defines the logical structure of the database
c. A programmer develops a program to update the fixed-assets records stored in the
database.
4-8
Accounting Information Systems
database.
d. The human resources manager requests a report noting all employees who are
retiring 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.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.
T
P
r
i
m
a
r
y
K
e
y
F
O
t
h
e
r
A
t
t
r
i
b
u
t
e
s
4-9
Ch. 4: Relational Databases
I
I
t
e
m
N
u
m
b
e
r
D
e
s
c
ri
p
ti
o
n
Q
u
a
n
ti
t
y
o
n
H
a
n
d
P
P
u
r
c
h
a
s
e
o
r
d
e
r
n
u
m
b
e
r
V
P
D
a
t
e
o
f
p
u
r
c
h
a
s
e
T
o
t
a
l
a
m
o
u
n
t
o
f
p
4-10
Accounting Information Systems
u
r
c
h
a
s
e
P
I
t
e
m
n
u
m
b
e
r
P
u
r
c
h
a
s
e
o
r
d
e
r
n
u
m
b
e
r
Q
u
a
n
ti
t
y
p
u
r
c
h
a
s
e
d
U
n
it
c
o
s
t
(
a
c
t
u
a
l)
E
x
t
e
n
d
e
d
a
m
o
u
n
4-11
Ch. 4: Relational Databases
t
V
V
e
n
d
o
r
n
u
m
b
e
r
V
e
n
d
o
r
n
a
m
e
V
e
n
d
o
r
a
d
d
r
e
s
s
E
E
m
p
l
o
y
e
e
n
u
m
b
e
r
E
m
p
l
o
y
e
e
n
a
m
e
Extended amount and Total amount of purchase do not have to be stored in the database as
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.
4-12
Accounting Information Systems
There is no solution to parts d through f as students will select different software packages
and come up with different queries.4.4 Retrieve the S&S In-Chapter Database (in Microsoft
Access format) from the text’s website (or create the tables in Table 4-5 in a relational DBMS
product). Write queries to answer the following questions. Note: For some questions, you may
have to create two queries—one to calculate an Invoice Total and the second to answer the
question asked.
Answers depend upon the specific DBMS and query language used. Here are
suggested answers in QBE (Query By Example) prepared in Microsoft Access.
a. How many different kinds of inventory items does S&S sell?
Query
Query Result
b. How many sales were made during October?
4-13
Ch. 4: Relational Databases
Query…
..
Query Result
. What were total sales in October?
Query
Query Result …
4-14
Accounting Information Systems
…
d. What was the average amount of a sales transaction?
This question requires the use of a total invoice calculation, thus, a total invoice table is
Make Table Query
Table Result
…
4-15