Ms. Marcia Wilson owns and operates Marcia’s Dry Cleaning, which is an upscale dry clean...

Ms. Marcia Wilson owns and operates Marcia’s Dry Cleaning, which is an upscale dry cleaner in a well-to-do suburban neighborhood. Marcia makes her business stand out from the competition by providing superior customer service. She wants to keep track of each of her customers and their orders. Ultimately, she wants to notify them that their clothes are ready via email.

Assume that Marcia has hired you as a database consultant to develop an operational database named MDC that has the following four tables:

CUSTOMER (CustomerID, FirstName, LastName, Phone, Email)

INVOICE (InvoiceNumber, CustomerID, DateIn, DateOut, Subtotal, Tax,

TotalAmount)

INVOICE_ITEM (InvoiceNumber, ItemNumber, ServiceID, Quantity, UnitPrice,

ExtendedPrice)

SERVICE (ServiceID, ServiceDescription, UnitPrice)

A Microsoft Access 2013 version of the MDC database and SQL scripts to create and populate the MDC database are available for Microsoft SQL Server 2014, Oracle Database Express Edition 11g Release 2, and MySQL 5.6 Community Server at the Database Concepts Web site at www.pearsonhighered.com/kroenke. Sample data for the CUSTOMER table are shown in Figure 7-37, for the SERVICE table in Figure 7-38, for the INVOICE table in Figure 7-39, and for the INVOICE_ITEM table in Figure 7-40.

A. Create a database in your DBMS named MDC, and use the MDC SQL scripts for your DBMS to create and populate the database tables. Create a user named MDCUser with the password MDC-User+password. Assign this user to database roles so that the user can read, insert, delete, and modify data.

B. Create an appropriate ODBC data source for your database.

C. 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 database.

D. Design a data warehouse star schema for a dimensional database named MDC-DW. The fact table measure will be ExtendedPrice.

E. Create the MDC-DW database in your DBMS product.

F. 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.

G. Write the complete set of SQL statements necessary to load the transformed data into the MDC-DW database.

H. Populate the MDC-DW database, using the SQL statements you wrote to answer part G.

I. Write an SQL query similar to the one shown in the text on page 456 that uses the ExtendedPrice as the measure.

J. Write the SQL view equivalent of the SQL query you wrote to answer part I.

K. Create the SQL view you wrote to answer part J in your MDC-DW database.

L. Create the Microsoft Excel 2013 workbook named MDC-DW-BI-Exercises.xlsx.

M. Using either the results of your SQL query from part I (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 8-17. (Hint: If you need help with the needed Microsoft Excel actions, search in the Microsoft Excel help system for more information.)