Big Data, Data Warehouses, and Business Intelligence Systems - Database Processing: Fundamentals, Design, and Implementation - page 544

Heather Sweeney Designs Case Questions show the HSD database column specifications in
Figure 7-39, HSD database referential integrity constraint enforcement in Figure 7-40, the SQL
statements to create the HSD database Figure 7-41, and the SQL statements to populate the
HSD database Figure 7-42. The HSD database is the operational database for Heather Sweeney
Designs. All production data is stored in the HSD database, and that data provide the source
data that we will load into a dimension database for BI work at Heather Sweeney Designs.
By The WAy You do not need to create the HSD database in order to create and use the
HSD-DW database used in this chapter. However, because the HSD-DW
database uses data extracted from the HSD database, it is worthwhile to study and
understand the structure of the HSD database and data contained in the HSD database
in order to appreciate how we transform that data for use in the HSD-DW database.
The actual dimensional database for BI use is named HSD-DW, and it is shown in
Figure 12-12. The SQL statements needed to create the tables in the HSD-DW database are
shown in Figure 12-13, and the data for the HSD-DW database are shown in Figure 12-14.
Compare the HSD-DW dimensional database model in Figure 12-12 to the HSD database
diagram shown in Figure 12-11, and note how data in the HSD database have been used in
the HSD-DW schema.
By The WAy Note that, in the HSD-DW database, the CUSTOMER table has a surrogate
primary key named CustomerID, which has an integer value, whereas, in
the HSD database, the primary key EmailAddress was used. There are two reasons for
this. First, the primary key EmailAddress used in the HSD database is simply too cum-
bersome for a data warehouse, so we switched to the preferable small and numeric
surrogate key. Second, we do not use individual EmailAddress values in the HSD-DW
database, only values of EmailDomain, which is not unique and cannot be used as a
primary key.
Figure 12-12
The HSD-DW Star Schema
PRODUCT dimension
table
PRODUCT_SALES
fact table
TIMELINE dimension
table
CUSTOMER
dimension table