AIS E12 CH04

CHAPTER 4
RELATIONAL DATABASES
SUGGESTED ANSWERS TO DISCUSSION QUESTIONS
4.1
Contrast the logical and the physical view 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 view). The DBMS interprets the users' requests and retrieves, manipulates, or stores the
data as needed. The two distinct views separate the applications from the physical information,
providing increased flexibility in applications, improved data security, and ease of use.
In a database system, the manager will rarely need to understand or be familiar with the physical
view of the data. Nor, in most instances, will the internal auditor and the programmer as most
everything they do involves the logical view of the data.
If accountants understand logical data structures and the logical view of the data, they are better
able to manage, use, and audit a database and its data.
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 accountants
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.
Accountants familiarity with spreadsheets might hinder their ability to design and use relational
DBMS because many links in spreadsheets are preprogrammed and designed in, whereas a welldesigned relational database is designed to facilitate ad-hoc queries.
Accountants familiarity with spreadsheets sometimes leads them to use a spreadsheet for a task
that a database could handle much better. Over the years, the Journal of Accountancy has published
a number of very good articles on how to use databases and when to use databases and when to use
spreadsheets. These articles can be found on the Journals website:
http://www.journalofaccountancy.com/
Accounting Information Systems

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 doubleentry 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?
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
Database technology does permit abandonment of double entry, but there will likely be great
resistance to such a radical change. Thus, students choosing this option need to present
reasons why they think such a radical change would succeed.
The use of a schema for accountants seems quite plausible. It does eliminate the redundancy
of double entry from the database system, yet it still provides a framework familiar and useful
to accountants and financial analysts.
There is a good possibility that double entry will remain, even in databases, due to inertia.
Indeed, many modern AIS, such as ERP systems, use databases but also retain the principles
of double entry.
Relational DBMS query languages provide easy access to information about the
organizations 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.
Real-time financial statements are useful for planning and provide management with better ability
to react to changes in the environment. Nevertheless, real-time financial statements may present
distorted pictures of reality if accruals have been ignored or not properly recognized.
4-2

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:
For quite some time, a company sent half its catalogs to incorrect addresses. A manager
finally investigated the large volume of returns and customer complaints and corrected the
customer addresses in the database. He saved the company $12 million a year.
Valparaiso, Indiana used the county database to develop its tax rates. After mailing the tax
notices, it was discovered that a $121,900 home was valued at $400 million. Due to the
$3.1 million property tax revenue shortfall, the city, the school district, and governmental
agencies had to make severe budget cuts.
Managing data is not going to get any easier as the quantity of data generated and stored
doubles every 18 months.
4-3

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, whose inputs include new or deleted data elements and changes in data element
names, descriptions, or uses. Outputs include reports for programmers, designers, and users.
These reports are used for system documentation, database design and implementation, and as
part of the audit trail.
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, centrally coordinated files forms a database.
Figure 4-2 illustrates the differences between file-oriented and database systems. In the database
approach, data is an organizational resource that is used by and managed for the entire organization,
not just the originating department. A database management system (DBMS) is the interface
between the database and the various application programs. The database, the DBMS, and the
application programs that access the database through the DBMS are referred to as the database
system.
Database systems were developed to address the proliferation of master files. This proliferation
created problems such as the same data stored in two or more master files. This made it difficult to
integrate and update data and to obtain an organization-wide view of data. It also created problems
because the data in the different files was inconsistent.
Databases provide organizations with the following benefits:
Data integration. Master files are combined into large pools of data that many application
programs access. An example is an employee database that consolidates payroll, personnel, and
job skills master files.
Data sharing. Integrated data is more easily shared with authorized users. Databases are easily
browsed to research a problem or obtain detailed information underlying a report. The FBI,
which does a good job of collecting data but a poor job of sharing it, is spending eight years and
$400 million to integrate data from their different systems.
Minimal data redundancy and data inconsistencies. Because data items are usually stored
only once, data redundancy and data inconsistencies are minimized.
Data independence. Because data and the programs that use them are independent of each
other, each can be changed without changing the other. This facilitates programming and
simplifies data management.
Cross-functional analysis. In a database system, relationships, such as the association between
selling costs and promotional campaigns, can be explicitly defined and used in the preparation
of management reports.
4-5
Ch. 4: Relational Databases

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:
item number
description
quantity-on-hand
price
customer name
shipping address
credit limit
account balance
To create and mail a bill (invoice), the billing clerk needs access to the following data
stored in the database:
customer name
customer number
billing address
item numbers
quantity sold
price
terms
To manage inventory, the inventory control department needs access to the following data
stored in the database:
item number
description
quantity on hand
To purchase inventory, the purchasing department needs access to the following data
stored in the data base:
4-6
item number
description
quantity on hand
cost
4-7
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.
O
F
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
o
r
4-8
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
o
r
4-9
l
i
s
t
)
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
u
a
l
s
a
l
e
s
4-10
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
n
g
a
d
d
r
e
4-11
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-12
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
The DDL - this is the language used to define 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.
The DQL - this is an example of a query.
c. A programmer develops a program to update the fixed-assets records stored in the

database.
The DML - this is the language used to actually process transaction data and update the
database.
d. The human resources manager requests a report noting all employees who are
retiring within five years.
The DQL - another example of a task that involves querying the database.
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.
The DDL and the DML - the former to alter the structure, the latter to make the change.
f. A user develops a program to print out all purchases made during the past two
weeks.
The DQL this listing can be produced by a query.
g. An additional field is added to the fixed-asset records to record the estimated

salvage value of each asset.
The DDL and the DML - the former to add the field, the latter to enter data in it.
4-13
4.3
Ashton wants to store the following data about S&Ss 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.
O
T
r
F
t
i
h
m
e
a
r
r
A
y
t
t
r
K
i
e
b
y
u
t
e
s
D
I
t
e
e
s
m
c
ri
p
N
ti
u
o
m
n
b
e
Q
r
u
a
n
ti
t
4-14
u
r
c
h
a
s
e
o
r
d
e
r
n
u
m
b
e
r
t
e
m
n
4-15
y
o
n
H
a
n
d
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
u
r
c
h
a
s
e
Q
u
a
n
ti
t
u
m
b
e
r
P
u
r
c
h
a
s
e
o
r
d
e
r
n
u
m
b
e
r
e
n
d
o
r
n
u
m
4-16
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
t
V
e
n
d
o
r
n
a
m
e

V
b
e
r
m
p
l
o
y
e
e
n
u
m
b
e
r
e
n
d
o
r
a
d
d
r
e
s
s
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
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.
4-17
4.4
Retrieve the S&S In-Chapter Database (in Microsoft Access format) from
the texts Web site (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 queriesone 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
4-18
4-19
b. How many sales were made during October?

Query
Query Result
4-20
What were total sales in October?

Query
Query Result
4-21
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
prepared as a Microsoft Make Table Query in Microsoft Office. A Make Table Query is prepared
the same as a normal query except that the user selects the Make Table Query option in the Query
Type portion of the Query Design Tools ribbon.
Make Table Query
Table Result
4-22
Query
4-23

Query Result
e.
Which salesperson made the largest sale?
Make Table Query
4-24
Query Result
4-25
Query
Query Result
4-26
f.
How many units of each product were sold?
Query
Query Result
4-27
Which product was sold most frequently?
Query
Query Result
4-28
4.5
Enter the tables in Table 4-15 into a relational DBMS package. Write
queries to answer the following questions. Note: For some questions, you may have to create
two queriesone to calculate a 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.
Which customers (show their names) made purchases from Martinez?
Query
Query Result
4-29
b.
Who has the largest credit limit?
Query
Query Result
4-30
4-31
c. How many sales were made in October?

Query
Query Result
4-32
4-33
d.
What were the item numbers, price, and quantity of each item sold on invoice
number 103?
Query
Query Result
4-34
e.
How much did each salesperson sell?

Query
Query Result
4-35
f. How many customers live in Arizona?
Query
Query Result
4-36

g. How much credit does each customer still have available?
Questions g and i require the use of a total customer sales calculation; thus, a customer total
sales table is prepared as a Microsoft Make Table Query in Microsoft Office. A Make Table
Query is prepared the same as a normal query except that the user selects the Make Table Query
option under the Query Design menu tab.
Make-Table Query
Table Result
4-37
Query
Query Result
NOTE: The above query only includes customers that have actually
purchased items. There are customers in the database who have not
purchased items and consequently were not included in the query results.
4-38

h. How much of each item was sold? (Include the description of each item in your answer.)
Query
Query Result
4-39
i.
Which customers still have more than $1,000 in available credit?
Query
Query Result
4-40

j.
For which items are there at least 100 units on hand?
Query
Query Result
4-41
4.6
The BusyB Company wants to store data about employee skills.
Each employee may possess one or more specific skills and several employees may have the
same skill. Include the following facts in the database:
date
date
empl
hired
skill acquired
oyee number
date
empl
pay
of birth
oyee name
rate
a. Design a set of relational tables to store these data.
b.
Identify the primary key for each table, and identify any needed foreign
keys.
follows:
The necessary tables, with their attendant primary and foreign keys, are as
r
i
m
a
r
y
o
r
e
i
g
n
K
e
y
K
e
y
s
S
u
p
e
r
v
i
s
o
r
m
p
l
o
y
e
e
N
u
m
b
e
r
n
u
m
b
e
r
(
a
n
4-42

o
t
h
e
r
e
m
p
l
o
y
e
e
n
u
m
b
e
r
)
k
i
l
l
n
u
m
b
e
r
S
k
i
l
l
n
u
m
b
e
r
E
m
4-43

p
l
o
y
e
e
n
u
m
b
e
r
c.
Implement your schema using any relational DBMS. Specify primary and
foreign keys, and enforce referential integrity. Demonstrate the soundness of your
design by entering sample data in each table.
There is no single solution to part c as students will select different software packages and
enter different data in the tables.
4-44
4.7
You want to extend the schema shown in Table 4-16 to include information
about customer payments. Some customers make installment payments on each invoice.
Others write a check to pay for several different invoices.
a.
b.
Modify the set of tables in Table 4-16 to store this additional data.
Identify the primary key for each new table you create.
The following additional tables, with their attendant primary keys, are needed to
store the other new attributes. Note that customer name is already stored in the customer table
P
Foreign
r
Keys
O
i
m
a
r
y
K
e
y
C
Customer
a
number
D
s
Employe
h
e
R
processin
To
e
g
c
e
payment
i
(employe
p
e
t
number)
N
u
m
b
e
r
n
A
v
o
i
c
e
p
a
y
4-45

m
e
n
t
a
p
p
li
e
s
t
o
(
I
n
v
o
i
c
e
n
u
m
b
e
r
)
C
a
s
h
R
e
c
e
i
p
t
n
u
m
b
e
r
c.
Implement your schema using any relational DBMS package. Indicate
4-46

which attributes are primary and foreign keys, and enter sample data in each table you
create.
There is no solution to part c as students will select different software packages and enter
different data in the tables.
4-47

4.8
Create relational tables that solve the update, insert, and delete anomalies in Table 4-17.
To avoid the update, insert, and delete anomalies, four separate relational tables
are created.
TABLE 4-17
Invoice # Date
Order Date Customer CustomerItem#
Desc
Qua
52
6-19-15
5-25-15
201
Johnson 103
Trek
52
6-19-15
5-25-15
201
Johnson 122
Nim
52
6-19-15
5-25-15
201
Johnson 10
Izzo
11
52
6-19-15
5-25-15
201
Johnson 71
LD
12
4-48
57
6-20-15
6-01-15
305
Henry
535
TR
18
57
6-20-15
6-01-15
305
Henry
115
NT
15
57
6-20-15
6-01-15
305
Henry
122
Nim
INVOICE TABLE
Invoic
Date
Order
52
6-19-
5-25-
57
6-20-
6-01-
INVOICE-INVENTORY TABLE
4-49

I
n
v
o
i
c
e
#
(
P
K
/
F
K
)
5
2
It
CUSTOMER TABLE
u
s
t
4-50

o
m
e
r
N
a
m
Item Table
Item
#
(PK)
10
e
J
o
h
n
s
o
n
H
e
n
r
y
71
103
115
122
4-51
535
Note: PK-Primary Key, FK Foreign Key, PK/FK Primary Key/Foreign Key
4-52
4.9
Table 4-18.
Create relational tables that solve the update, insert, and delete anomalies in
TABLE 4-18
Purchase
Part #
Descri Unit
Quantity Vendor #
Vendor
Vend
Price
3/9/15 334
XYZ
$30
504
KL
75
3/9/15 231
PDQ
$50
504
KL
75
3/9/15 444
YYM $80
504
KL
75
4/5/15 231
PDQ
$50
889
Oscan
55
4-53
PART TABLE
De
Unit
334
30
231
50
444
80
Part #
PURCHASE ORDER TABLE
4-54
P
u
Ve
50
3/
88
4/
r
c
h
a
s
e
O
r
d
e
r
(
P
K
)
2
4-55
VENDOR TABLE
PURCHASE-PART TABLE
Pur
chas
P
e
Ord
er #
(PK
/FK
)
2
Qu
ant
ity
Or
der
ed
Note: PK-Primary Key, FK Foreign Key, PK/FK Primary Key/Foreign Key
4-56
4.10
From the database created in the comprehensive problem, perform queries
based on the tables and query grid shown in Table 4-19.
The queries and the answers to the queries for the questions about the
comprehensive problem data (Table 4-19) are shown below.
Which borrowers use Advent Appraisers?
Query
a.
Query Result
4-57
4-58
b.
What is the average amount borrowed from National Mortgage?
Query
Query Result
4-59
c.
List all of the property appraisers.
Query
Query Result
4-60

d.List all of the lenders.
Query
Query Result
Query
4-61
4-62

e. List the lenders that lent more than $100,000.
NOTE: In order to get a list of lenders without duplicates the property sheet of
the query needs to be modified by setting the value of the Unique Values property
to Yes. This can be seen in the screenshot below. The property sheet is found
under the Design tab of the ribbon. Setting Unique Values to Yes is the
equivalent of entering the DISTINCT keyword in SQL select statements.
4-63
Query Result
4-64
f.
Which borrower requested the largest mortgage?
Query
Notice that in the Design section on the ribbon, you must set the Return value to
1 (located in the Query Setup group). This indicates to Access to only return the
top result. See the image below for a screenshot of this.
Query Result
4-65
g.
Which borrower requested the smallest mortgage?
Query
Query Result
As with problem 4-10-f, you must set the Return value to 1 in the Design section
of the ribbon (located in the Query Setup group). This indicates to Access to only return the top result.
See the image below for a screenshot of this.
4-66
SUGGESTED ANSWERS TO THE CASES
4.1 As in all areas of information technology, DBMSs are constantly changing and
improving. Research how businesses are using DBMSs, and write a report of your findings.
Address the following issues:
1.
Which popular DBMS products are based on the relational data model?
2.
Which DBMS products are based on a logical model other than the relational data
model?
3.
What are the relative strengths and weaknesses of the different types (relational versus
other logical models) of DBMSs
No single answer exists with this case; indeed, solutions will vary depending upon student
ingenuity and creativity. Reports should be graded in terms of how well each issue was addressed
and in terms of writing quality. Students should be able to find the following information:
Relational DBMSs include DB2, Oracle, SQL Server and Access.
Many newer products are based on the object-oriented data model, or are a hybrid of the
relational and object-oriented approaches. Older mainframe DBMS are based on hierarchical
or network logical models.
Hierarchical and network DBMSs often provide performance advantages--especially in terms

of processing speed. Those advantages, however, usually come at the cost of making it much
more difficult for end users to do ad-hoc queries of the database. Relational databases
support easy to use, yet powerful query languages like SQL and graphical query-by-example
languages such as that provided by Microsoft Access. Object-oriented databases are
especially effective for including multimedia, whereas hierarchical, network, and relational
databases are better suited for alphanumeric data (although the relational model can be
extended to include multimedia data). Pure object-oriented databases are more often
designed for special purpose scientific use when graphical images and sound need to be
stored in the database. Relational and hybrid object-relational DBMSs are commonly used in
newer transaction processing systems, although older systems are based on the hierarchical or
network data models.

AIS E12 CH04

Uploaded by

Copyright:

Available Formats

AIS E12 CH04

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

AIS E12 CH04

Uploaded by

Copyright:

Available Formats

CHAPTER 4

Accounting Information Systems

Accounting Information Systems

Why is it so important to have good data?

Accounting Information Systems

What is a data dictionary, what does it contain, and how is it used?

Accounting Information Systems

Ch. 4: Relational Databases

Accounting Information Systems

Ch. 4: Relational Databases

Accounting Information Systems

Ch. 4: Relational Databases

Accounting Information Systems

Ch. 4: Relational Databases

Accounting Information Systems

A database administrator defines the logical structure of the database

The DDL - this is the language used to define the database.

c. A programmer develops a program to update the fixed-assets records stored in the

g. An additional field is added to the fixed-asset records to record the estimated

Ch. 4: Relational Databases

Accounting Information Systems

Ch. 4: Relational Databases

Accounting Information Systems

Ch. 4: Relational Databases

a. How many different kinds of inventory items does S&S sell?

Accounting Information Systems

Ch. 4: Relational Databases

b. How many sales were made during October?

Accounting Information Systems

What were total sales in October?

Ch. 4: Relational Databases

What was the average amount of a sales transaction?

Make Table Query

Accounting Information Systems

Ch. 4: Relational Databases

Which salesperson made the largest sale?

Make Table Query

Accounting Information Systems

Ch. 4: Relational Databases

Accounting Information Systems

How many units of each product were sold?

Ch. 4: Relational Databases

Which product was sold most frequently?

Accounting Information Systems

Ch. 4: Relational Databases

Who has the largest credit limit?

Accounting Information Systems

Ch. 4: Relational Databases

c. How many sales were made in October?

Accounting Information Systems

Ch. 4: Relational Databases

Accounting Information Systems

How much did each salesperson sell?

Ch. 4: Relational Databases

f. How many customers live in Arizona?

Accounting Information Systems

Ch. 4: Relational Databases

Accounting Information Systems