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

The RETAIL_O RDER
table—the key symbol
shows the primary key

The relationship
between
RETAIL_ORDER
and ORDER_ITEM—
the number 1 and the
infinity symbol indicate
that one retail order
may be linked to many
order items by
OrderNumber

Figure 2-3
Cape Codd Extracted Retail
Sales Data Database Tables
and Relationships

CustomerLastName, CustomerFirstName, and OrderDay columns. The data types for the
columns in the tables is shown in Figure 2-4.
As shown in Figures 2-3 and 2-4, three tables are needed: RETAIL_ORDER, ORDER_ITEM,
and SKU_DATA. The RETAIL_ORDER table has data about each retail sales order, the
ORDER_ITEM table has data about each item in an order, and the SKU_DATA table has data
about each stock-keeping unit (SKU) . SKU is a unique identifier for each particular item that
Cape Codd sells. The data stored in the tables is shown in Figure 2-5.

By The Way The dataset shown is a small dataset we are using to illustrate the con-
cepts explained in this chapter. A “real world” data extract would produce
a much larger dataset.

ReTaIL_ORDeR Data
As shown in Figures 2-3, 2-4, and 2-5, the RETAIL_ORDER table has columns for OrderNumber,
StoreNumber, StoreZIP (the ZIP code of the store selling the order), OrderMonth, OrderYear,

Figure 2-4
Cape Codd Extracted
Retail Sales Data Format

Table Column Date Type

RETAIL_ORDER Integer

Integer
Character (9)

OrderNumber
StoreNumber

StoreZIP

OrderMonth

Character (12)

Integer

OrderYear
OrderTotal

Currency

ORDER_ITEM

Integer
Integer

OrderNumber

SKU

Integer

Quantity

Price

Currency
Currency

ExtendedPrice

SKU_DATA Integer

Character (35)

SKU

SKU_Description
Department

Character (30)

Buyer Character (30)