If you have not already done so, create and populate the Marcia’s Dry Cleaning (MDC) database for the DBMS you are using as described in:

● Chapter 10A for Microsoft SQL Server 2012

● Chapter 10B for Oracle Database 11g Release 2

● Chapter 10C Oracle MySQL 5.6

Chapter 12 Big Data, Data Warehouses, and Business Intelligence Systems 577

A. You need about 20 INVOICE transactions with supporting INVOICE_ITEMs in the database.

Write the needed SQL statements for any needed additional INVOICE transactions, 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 transformations 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 worksheet 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).

L. Describe how an RFM analysis could be useful in Marcia’s business.