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
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
Ch. 4: Relational Databases
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?
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
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
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
Accounting Information Systems
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
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
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.
Ch. 4: Relational Databases
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:
− item number
− description
• To create and mail a bill (invoice), the billing clerk needs access to the following data
stored in the database:
− customer name
• 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:
Accounting Information Systems
4-5
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
Shipping address
Ch. 4: Relational Databases
4-6
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.
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.
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.
Accounting Information Systems
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
Primary Key
Other Attributes
Inventory
Item Number
Description
Quantity on Hand
Purchases
Purchase order number
Date of purchase
Total amount of purchase
Vendor
Vendor number
Vendor name
Vendor address
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.
Ch. 4: Relational Databases
4-8
4.4 Retrieve the S&S In-Chapter Database (in Microsoft Access format) from the text’s 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 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
Accounting Information Systems
4-9
b. How many sales were made during October?
Query
Query Result
Ch. 4: Relational Databases
4-10
c. What were total sales in October?
Query
Query Result
Accounting Information Systems
4-11
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
Ch. 4: Relational Databases
4-12
Query
Query Result
Accounting Information Systems
4-13
e. Which salesperson made the largest sale?
Make Table Query
Query Result
Ch. 4: Relational Databases
4-14
Query
Query Result
Accounting Information Systems
4-15
f. How many units of each product were sold?
Query
Query Result
Ch. 4: Relational Databases
4-16
g. Which product was sold most frequently?
Query
Query Result
Accounting Information Systems
4-17
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 queries—one 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
a. Which customers (show their names) made purchases from Martinez?
Query
Query Result
Ch. 4: Relational Databases
4-18
b. Who has the largest credit limit?
Query
Query Result
Accounting Information Systems
4-19
c. How many sales were made in October?
Query
Query Result
Ch. 4: Relational Databases
d. What were the item numbers, price, and quantity of each item sold on invoice number
103?
Query
Query Result