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

warehouse DBMS for use by BI users who access the data by various BI tools. As described in
Chapter 1, the DBMS used for the data warehouse stores both databases and the metadata for
those databases.

By The Way A small, specialized data warehouse is referred to as a data mart . Data marts
and their relationship to data warehouses are discussed in Chapter 12. Note
that the DBMS used for the data warehouse may or may not be the same DBMS product
used for the operational databases. For example, operational databases may be stored
in an Oracle Database 11 g Release 2 DBMS, while the data warehouse uses a Microsoft
SQL Server 2012 DBMS.

Cape Codd Outdoor Sports

For our work in this chapter, we will use data from Cape Codd Outdoor Sports (although based
on a real outdoor retail equipment vendor, Cape Codd Outdoor Sports is a fictitious company).
Cape Codd sells recreational outdoor equipment in 15 retail stores across the United States
and Canada. It also sells merchandise over the Internet from a Web storefront application and
via mail order. All retail sales are recorded in a sales database managed by the Oracle Database
11 g Release 2 DBMS, as shown in Figure 2-2.

The extracted Retail Sales Data
Cape Codd's marketing department wants to perform an analysis of in-store sales.
Accordingly, marketing analysts ask the IT department to extract retail sales data from the
operational database. To perform the marketing study, they do not need all of the order
data. They want just the tables and columns shown in Figure 2-3. Looking at this figure,
it is easy to see that columns that would be needed in an operational sales database are
not included in the extracted data. For example, the RETAIL_ORDER table does not have

Figure 2-2
The Cape Codd Retail
Sales Data Extraction
Process

Point-of-Sale
Application

Store 1

Point-of-Sale
Application

Store 2
. . .

Point-of-Sale
Application

Oracle
Database 11 g
Release 2
Sales
Database

Store 15

Web Storefront
Internet Sales

Retail Store Sales
Data Extraction

Internet Customers

Sales
Extraction
Database

Mail Order
Sales

Mail Order Customers