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

A. You need about 20 INVOICE transactions with supporting INVOICE_ITEMs in the da-
tabase. Write the needed SQL statements for any needed additional INVOICE transac-
tions, and insert the data into your MDC database.
B. Design a data warehouse star schema for a dimensional database named MDC-DW.
The fact table measure will be ExtendedPrice.
C. Create the MDC-DW database in your DBMS product.
D. What transformations of data will need to be made before the MDC-DW database can
be loaded with data? List all the transformations, showing the original format of the
MDC data and how it appears in the MDC-DW database.
e. Write the complete set of SQL statements necessary to load the transformed data into
the MDC-DW database.
F. Populate the MDC-DW database, using the appropriate MDC data or transforma-
tions of that data.
G. Write an SQL query similar to the one shown in the text on page 547 that uses the
ExtendedPrice as the measure.
h. Write the SQL view equivalent of the SQL query you wrote to answer part G.
I. Create the SQL view you wrote to answer part H in your MDC-DW database.
J. Create the Microsoft Excel 2013 workbook named MDC-DW-BI-Exercises.xlsx.
K. Using either the results of your SQL query from part J (copy the results of the query into
a worksheet in the MDC-DW-BI.xlsx workbook and then format this range as a work-
sheet table) or your SQL view from part I (create a Microsoft Excel data connection to
the view), create an OLAP report similar to the OLAP report shown in Figure 12-21(d).
( Hint: If you need help with the needed Microsoft Excel actions, search in the Microsoft
Excel help system for more information.)
L. Describe how an RFM analysis could be useful in Marcia's business.
If you have not already implemented The Queen Anne Curiosity Shop database shown
in Chapter 7 in a DBMS product, create and populate the QACS database now in the
DBMS of your choice (or as assigned by your instructor).
A. You need about 30 PURCHASE transactions in the database. Write the needed SQL
statements for any needed additional PURCHASE transactions, and insert the data
into your QACS database.
B. Design a data warehouse star schema for a dimensional database named QACS-DW.
The fact table measure will be ItemPrice.
C. Create the QACS-DW database in a DBMS product.
D. What transformations of data will need to be made before the QACS-DW database can
be loaded with data? List all the transformations, showing the original format of the
QACS and how it appears in the QACS-DW database.
e. Write the complete set of SQL statements necessary to load the transformed data into
the QACS-DW database.
F. Populate the QACS-DW database, using the appropriate QACS data or transforma-
tions of that data.