Database Processing: Fundamentals, Design, and Implementation 13th Edition by David Kroenke, David Auer

Exercise 1

Based on the discussion of the Heather Sweeney Designs operational database (HSD) and dimensional database (HSD-DW) in the text, answer the following questions.
NOTE: The database design, table structure and data for the original Heather Sweeney Designs (HSD) database data are included in the Heather Sweeny Designs Case Questions to Chapter 7. The solution below is based on the complete HSD database as shown and created in the Chapter 7 HSD Case Questions.
A. Using the SQL statements shown in Figure 12-13, create the HSD-DW database in a DBMS.
B. What possible transformations of data were made before HSD-DW was loaded with data? List some possible transformations, showing the original format of the HSD data and how they appear in the HSD-DW database
C. Write the complete set of SQL statements necessary to load the transformed data into the HSD-DW database.
D. Populate the HSD-DW database, using the SQL statements you wrote to answer question C.
E. Figure 12-35 shows the SQL code to create the SALES_FOR_RFM fact table shown in Figure 12-18. Using those statements, add the SALES_FOR_RFM table to your HSD-DW database.
F. What possible transformations of data are necessary to load the SALES_FOR_RFM table? List any possible transformations, showing the original format of the HSD data and how they appear in the HSD-DW database.
G. Write an SQL query similar to the one shown on page 547 that uses the total dollar amount of each day's product sales as the measure (instead of the number of products sold each day).
H. Write the SQL view equivalent of the SQL query you wrote to answer question G.
I. Create the SQL view you wrote to answer question H in your HSD-DW database.
J. Create an Excel 2013 workbook named HSD-DW-BI-Exercises.xlsx.
K. Using either the results of your SQL query from part H (copy the results of the query into a worksheet in the HSD-DW-BI-Exercises.xlsx workbook and then format this range as a worksheet table) or your SQL view from part K (create an Excel data connection to the view), create an OLAP report similar to the OLAP report shown in Figure 12-32. (Hint: If you need help with the needed Microsoft Excel actions, search in the Microsoft Excel help system for more information.)
L. Heather Sweeney is interested in the effects of payment type on sales in dollars.