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

The Cape Codd Outdoor Sports sale extraction database has been modified to include
two additional tables, the INVeNTORy table and the WaRehOUSe table. The table
schemas for these tables, together with the ReTaIL_ORDeR, ORDeR_ITeM, and SKU_
DaTa tables, are as follows:

RETAIL_ORDER ( OrderNumber , StoreNumber, StoreZip, OrderMonth, OrderYear,
OrderTotal)
ORDER_ITEM ( OrderNumber , SKU , Quantity, Price, ExtendedPrice)
SKU_DATA ( SKU , SKU_Description, Department, Buyer)
WAREHOUSE ( WarehouseID , WarehouseCity, WarehouseState, Manager, Squarefeet)
INVENTORY ( Warehou seID , SKU , SKU_Description, QuantityOnHand,
QuantityOnOrder)

The five tables in the revised Cape Codd database schema are shown in Figure
2-24. The column characteristics for the WaRehOUSe table are shown in Figure 2-25,
and the column characteristics for the INVeNTORy table are shown in Figure 2-26.
The data for the WaRehOUSe table are shown in Figure 2-27, and the data for the
INVeNTORy table are shown in Figure 2-28.
If at all possible, you should run your SQL solutions to the following questions
against an actual database. a Microsoft access database named Cape-Codd.accdb is
available on our Web site ( www.pearsonhighered.com/kroenke ) that contains all the

Figure 2-24
The Cape Codd Database
with the WAREHOUSE and
INVENTORY Tables

The INVENTORY
table

The WAREHOUSE
table

Figure 2-25
Column
Characteristics
for the
Cape Codd
Database
WAREHOUSE
Table

WAREHOUSE

Column Name Type Key Required Remarks

WarehouseID Integer Primary Key Yes Surrogate Key

WarehouseCity Text (30) Yes

WarehouseState Text (2) Yes

Manager Text (35) No No

SquareFeet Integer No No