Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 113

James Morgan owns and operates Morgan Importing, which purchases antiques and home fur-
nishings in Asia, ships those items to a warehouse facility in Los Angeles, and then sells these
items in the United States. James tracks the Asian purchases and subsequent shipments of these
items to Los Angeles by using a database to keep a list of items purchased, shipments of the pur-
chased items, and the items in each shipment. His database includes the following tables:

ITEM ( ItemID , Description, PurchaseDate, Store, City, Quantity,
LocalCurrencyAmount, ExchangeRate)
SHIPMENT ( ShipmentID , ShipperName, ShipperInvoiceNumber, DepartureDate,
ArrivalDate, InsuredValue)
SHIPMENT_ITEM ( ShipmentID , ShipmentItemID , ItemID , Value)

In the database schema above, the primary keys are underlined and the foreign keys are
shown in italics. The database that James has created is named MI, and the three tables in the
MI database schema are shown in Figure 2-50.
The column characteristics for the tables are shown in Figures 2-51, 2-52, and 2-53. The
data for the tables are shown in Figures 2-54, 2-55, and 2-56. The relationship between ITEM

Figure 2-50
The MI Database

The ITEM
table

The SHIPMENT
table

The SHIPMENT_ITEM
table

Figure 2-51
Column Characteristics for
the MI Database ITEM Table

ITEM

Column Name Type Key Required Remarks

ItemID AutoNumber Primary Key Yes Surrogate Key

Description Text (255) No Yes Long Integer

PurchaseDate Date No Yes

Store Text (50) No Yes

City Text (35) No Yes

Quantity Number No Yes Long Integer

LocalCurrencyAmount Number No Yes Decimal, 18 Auto

ExchangeRate Number No Yes Decimal, 12 Auto