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

includes basic order data such as the date and order total, and it has one line for each item you
purchase. The rows in the ORDER_ITEM table correspond to the lines on such an order receipt.
The OrderNumber Column in ORDER_ITEM relates each row in ORDER_ITEM to the
corresponding OrderNumber in the RETAIL_ORDER table. SKU identifies the actual item pur-
chased by its stock-keeping unit number. Further, the SKU column in ORDER_ITEM relates
each row in ORDER_ITEM to its corresponding SKU in the SKU_DATA table (discussed in the
next section). Quantity is the number of items of that SKU purchased in that order. Price is the
price of each item, and ExtendedPrice is equal to Quantity × Price.
ORDER_ITEM data are shown in the bottom part of Figure 2-5. The first row relates to
order 1000 and to SKU 201000. For SKU 201000, one item was purchased for $300.00, and the
ExtendedPrice was $300.00. The second row shows the second item in order 1000. There,
1 of item 202000 was purchased for $50.00, and the ExtendedPrice is 1 × $50.00, or $50.00.
This table structure of an ORDER table related to an ORDER_ITEM table is typical for a sales
system with many items in one order. We will discuss it in detail in Chapters 5 and 6, where we
will create a data model of a complete order and then design the database for that data model.
By The Way You would expect the total of ExtendedPrice for all rows for a given order
to equal OrderTotal in the RETAIL_ORDER table. It does not. For order
1000, for example, the sum of ExtendedPrice in the relevant rows of ORDER_ITEM is
$300.00 + $130.00 = $430.00. However, the OrderTotal for order 1000 is $445.00. The
difference occurs because OrderTotal includes tax, shipping, and other charges that do
not appear in the data extract.
SKU_DaTa Table
As shown in Figures 2-3, 2-4, and 2-5, the SKU_DATA table has columns SKU, SKU_Description,
Department, and Buyer. We can write this information in the following format, with SKU
underlined to show that it is the primary key of the SKU_DATA table:
SKU_DATA ( SKU , SKU_Description, Department, Buyer)
SKU is an integer value that identifies a particular product sold by Cape Codd. For example,
SKU 100100 identifies a yellow, standard-size SCUBA tank, whereas SKU 100200 identifies the
magenta version of the same tank. SKU_Description contains a brief text description of each
item. Department and Buyer identify the department and individual who is responsible for
purchasing the product. As with the other tables, these columns are a subset of the SKU data
stored in the operational database.
The Complete Cape Codd Data extract Schema
A database schema is a complete logical view of the database, containing all the tables, all
the columns in each table, the primary key of each table (indicated by underlining the column
names of the primary key columns), and the foreign keys that link the tables together (indi-
cated by italicizing the column names of the foreign key columns). The schema for the Cape
Codd sales data extract therefore is:
RETAIL_ORDER ( OrderNumber , StoreNumber, StoreZIP, OrderMonth, OrderYear, OrderTotal)
ORDER_ITEM ( OrderNumber , SKU , Quantity, Price, ExtendedPrice)
SKU_DATA ( SKU , SKU_Description, Department, Buyer)
Note how the composite primary key for ORDER_ITEM also contains the foreign keys linking
this table to RETAIL_ORDER and SKU_DATA.
By The Way In the Review Questions at the end of this chapter, we will extend this
schema to include two additional tables: WAREHOUSE and INVENTORY.
The figures in this chapter include these two tables in the Cape Codd database, but
they are not used in our discussion of SQL in the chapter text.