Accounting Information Systems
4-21
e. How much did each salesperson sell?
Query
Query Result
Ch. 4: Relational Databases
4-22
f. How many customers live in Arizona?
Query
Query Result
Accounting Information Systems
4-23
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
Query
Ch. 4: Relational Databases
4-24
Query Result
Accounting Information Systems
4-25
h. How much of each item was sold? (Include the description of each item in your
answer.)
Query
Query Result
Ch. 4: Relational Databases
4-26
i. Which customers still have more than $1,000 in available credit?
Query
Query Result
Accounting Information Systems
j. For which items are there at least 100 units on hand?
Query
Query Result
Ch. 4: Relational Databases
4-28
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 hired
date of birth
date skill
acquired
employee name
employee
number
pay 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.
The necessary tables, with their attendant primary and foreign keys, are as follows:
Table Name
Primary Key
Other Attributes
Employee
Employee Number
Employee name
Pay rate
Date hired
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.
Accounting Information Systems
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.
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
Date of receipt
Total amount received
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.
Ch. 4: Relational Databases
4-30
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-15
5-25-15
201
Johnson
103
Trek 9000
5
52
6-19-15
5-25-15
201
Johnson
122
Nimbus 4000
8
INVOICE TABLE
Invoice#
(PK)
Date
OrderDate
CustomerID (FK)
52
6-19-15
5-25-15
201
57
6-20-15
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
52
6-19-15
5-25-15
201
Johnson
10
Izzod 3000
11
52
6-19-15
5-25-15
201
Johnson
71
LD Trainer
12
57
6-20-15
6-01-15
305
Henry
535
TR Standard
18
57
6-20-15
6-01-15
305
Henry
115
NT 2000
15
57
6-20-15
6-01-15
305
Henry
122
Nimbus 4000
5
Accounting Information Systems
4-31