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.
How much of each item was sold? (Include the description of each item in your answer.)
Query
Query Result
i. Which customers still have more than $1,000 in available credit?
Accounting Information Systems
©2018 Pearson Education, Inc.
Accounting Information Systems
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:
Table Name Primary Key Foreign Keys Other Attributes
Employee Employee Number Supervisor number
(another employee
number)
Employee name
Pay rate
Date hired
Date of birth
Skills Skill number Skill name
Employees-Skills Skill number
Employee number
Date skill acquired
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.
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
4-4
©2018 Pearson Education, Inc.
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 Receipt number
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.
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
INVOICE TABLE
Invoice# (PK) Date OrderDate CustomerID (FK)
INVOICE-INVENTORY TABLE
Invoice# (PK/FK) Item# (PK/FK) Quantity
52 103 5
52 122 8
CUSTOMER TABLE
CustomerID (PK) CustomerName
305 Henry
Item Table
Item# (PK) Description
10 Izzod 3000
4.9 Create relational tables that solve the update, insert, and delete anomalies
TABLE 4-18
Purchase
Order # Purchas
e Order
Date
Part
#
Description Unit
Price
Quantity
Ordered
Vendor
#
Vendor
Name
Vendor
Address
2 3/9/18 334 XYZ $30 3 504 KL Supply 75 Stevens Dr.
PART TABLE
Part # (PK) Description Unit Price
334 XYZ 30
PURCHASE ORDER TABLE
Purchase Order # (PK) Vendor # (FK) Purchase Order Date
VENDOR TABLE
Vendor # (PK) Vendor Name Vendor Address
PURCHASE-PART TABLE
Purchase Order # (PK/FK) Part # (PK/FK) Quantity Ordered
2 334 3
Note: PK-Primary Key, FK – Foreign Key, PK/FK – Primary Key/Foreign Key
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
a. Which borrowers use Advent Appraisers?
Query
Query Result
b. What is the average amount borrowed from National Mortgage?
Query
