Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Accounting Information Systems
4-1
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
QBE (Query By Example) prepared in Microsoft Access.
a. Which customers (show their names) made purchases from Martinez?
Query
Query Result
Accounting Information Systems
4-2
b. Who has the largest credit limit?
Query
Query Result
Accounting Information Systems
4-3
Accounting Information Systems
4-4
c. How many sales were made in October?
Query
Query Result
Accounting Information Systems
4-5
d. What were the item numbers, price, and quantity of each item sold on invoice number
103?
Query
Query Result
Accounting Information Systems
4-6
e. How much did each salesperson sell?
Query
Query Result
Accounting Information Systems
4-7
f. How many customers live in Arizona?
Query
Query Result
Accounting Information Systems
4-8
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
Query
Accounting Information Systems
4-9
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.
Accounting Information Systems
4-10
h. How much of each item was sold? (Include the description of each item in your answer.)
Query
Query Result
Accounting Information Systems
4-11
i. Which customers still have more than $1,000 in available credit?
Query
Query Result
Accounting Information Systems
4-12
j. For which items are there at least 100 units on hand?
Query
Query Result
4.6 The BusyB Company wants to store data about its 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 hired
date of birth
date skill acquired
employee name
employee number
pay rate
skill name
skill number
supervisor
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.
The necessary tables, with their attendant primary and foreign keys, are as follows:
4-14
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. You want to store the following information:
amount applied to a specific
invoice
cash receipt number
customer name
customer number
date of receipt
employee processing payment
invoice payment applies to
total amount received
Accounting Information Systems
4-15
a. Modify the set of tables in Table 4-16 to store this additional data.
b. 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
Table Name
Primary Key
Foreign Keys
Other Attributes
Cash Receipts
Cash Receipt Number
Customer number
Employee processing
payment (employee number)
Date of receipt
Total amount received
Cash Receipts-
Sales
Invoice payment applies
to (Invoice number)
Cash Receipt number
Amount applied to a
specific invoice
c. Implement your schema using any relational DBMS package. Indicate 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-16
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
ID
Customer
Name
Item#
Description
Quantity
52
6-19-18
5-25-18
201
Johnson
103
Trek 9000
5
52
6-19-18
5-25-18
201
Johnson
122
Nimbus 4000
8
52
6-19-18
5-25-18
201
Johnson
10
Izzod 3000
11
52
6-19-18
5-25-18
201
Johnson
71
LD Trainer
12
57
6-20-18
6-01-18
305
Henry
535
TR Standard
18
57
6-20-18
6-01-18
305
Henry
115
NT 2000
15
57
6-20-18
6-01-18
305
Henry
122
Nimbus 4000
5
INVOICE TABLE
Invoice#
(PK)
Date
OrderDate
CustomerID (FK)
52
6-19-18
5-25-15
201
57
6-20-18
6-01-15
305
INVOICE-INVENTORY TABLE
Invoice# (PK/FK)
Item# (PK/FK)
Quantity
52
103
5
52
122
8
52
10
11
52
71
12
57
535
18
57
115
15
57
122
5
CUSTOMER TABLE
CustomerID (PK)
CustomerName
201
Johnson
305
Henry
Item Table
Item# (PK)
Description
10
Izzod 3000
71
LD Trainer
103
Trek 9000
115
NT 2000
122
Nimbus 4000
535
TR Standard
Note: PK-Primary Key, FK – Foreign Key, PK/FK – Primary Key/Foreign Key
