Mini Case for the Assignment in Module 3

The assignment in module 3 gives you the opportunity to apply most of the concepts from module 3 and some of the concepts from module 2. Since the assignment is closely based on the practice problems in module 3, you should attempt the mini case study for the practice problems and then review the solution details.

This mini case study contains two data sources with sample data along with a statement of business needs. Using the data sources and business needs, you will specify a dimensional model with dimensions, measures, and grain, create a schema design for the data warehouse that integrates the data sources, identify summarizability problems in the design, and populate data warehouse tables from sample rows in the data sources.

Data Sources

Fitness Unlimited is a leading provider of exercise centers with a variety of fitness programs and membership options. Fitness Unlimited maintains a retail database to track sales of services and merchandise. In the ERD for the retail database (Figure 1), a sale contains a heading (Sale) with sales date and a collection of merchandise recorded in the M-N relationship Contains. Service purchases are recorded in the ServPurchase entity type with 1-M relationships from ServiceCategory and Member. Typical services are lessons, premium equipment usage, and social events. The MemTypeOf relationship is optional for members because guest members can use a fitness center and purchase merchandise and services on a short term basis without having a paid membership. Tables with sample rows are shown after Figure 1.