Ch. 4: Relational Databases
4-32
4.9 Create relational tables that solve the update, insert, and delete anomalies in Table 4-18.
TABLE 4-18
Purchase
Order #
Purchas
e Order
Date
Par
t #
Description
Unit
Price
Quantity
Ordered
Vendor
#
Vendor
Name
Vendor
Address
2
3/9/15
334
XYZ
$30
3
504
KL Supply
75 Stevens Dr.
PART TABLE
Part # (PK)
Description
Unit Price
334
XYZ
30
231
PDQ
50
444
80
PURCHASE ORDER TABLE
Purchase Order # (PK)
Vendor # (FK)
Purchase Order Date
2
504
3/9/15
3
889
4/5/15
VENDOR TABLE
Vendor # (PK)
Vendor Name
Vendor Address
504
889
Oscan Inc.
55 Cougar Cir.
PURCHASE-PART TABLE
Purchase Order #
(PK/FK)
Part # (PK/FK)
Quantity Ordered
2
334
3
231
5
2
444
6
3
231
2
Note: PK-Primary Key, FK – Foreign Key, PK/FK – Primary Key/Foreign Key
2
3/9/15
231
PDQ
$50
5
504
2
3/9/15
444
YYM
$80
6
504
KL Supply
75 Stevens Dr.
3
4/5/15
231
PDQ
$50
2
889
Oscan Inc
55 Cougar Cir.
Accounting Information Systems
4-33
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.
a. Which borrowers use Advent Appraisers?
Query
Query Result
Ch. 4: Relational Databases
4-34
b. What is the average amount borrowed from National Mortgage?
Query
Query Result
Accounting Information Systems
4-35
c. List all of the property appraisers.
Query
Query Result
Ch. 4: Relational Databases
4-36
d. List all of the lenders.
Query
Query Result
Query
Accounting Information Systems
4-37
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.
Ch. 4: Relational Databases
4-38
Query Result
Accounting Information Systems
4-39
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
Ch. 4: Relational Databases
4-40
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
Accounting Information Systems
4-41
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
• 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.