Interactive Lesson: Data Modeling

Data Modeling Exercise

🛍️ Data Modeling Exercise

Master the Art of Database Design with ShopSmart E-Commerce

📊 Business Scenario

ShopSmart is an online retail store that needs a data warehouse to analyze their business performance. They track:

  • • Customer purchases and order details
  • • Product inventory across multiple categories
  • • Time-based sales patterns
  • • Store locations and employee performance

Your Mission: Design a star schema data model by identifying fact and dimension tables, defining primary/foreign keys, and establishing relationships.

Exercise 1: Classify Tables as Fact or Dimension

💡 Hint: Fact tables contain measurable business metrics (sales, quantity, revenue). Dimension tables contain descriptive attributes (who, what, where, when).
📌 Instructions: Click on a table to select it (it will highlight), then click the appropriate category box below to move it there. You can also click an already placed table to move it back.

Available Tables

Sales
OrderID, Amount, Quantity, Discount
Customer
CustomerID, Name, Email, City, State
Product
ProductID, Name, Category, Price, Brand
Time
DateID, Date, Month, Quarter, Year
Store
StoreID, Location, Manager, Region

📊 Fact Tables (Click here to add selected table)

📐 Dimension Tables (Click here to add selected table)

Exercise 2: Identify Primary and Foreign Keys

💡 Remember: Primary Keys (PK) uniquely identify records. Foreign Keys (FK) in fact tables reference dimension tables.

Sales Fact Table Structure

Select the correct key type for each column:

OrderID:
CustomerID:
ProductID:
DateID:
StoreID:
Amount:

Exercise 3: Define Table Relationships

💡 Star Schema Pattern: The fact table connects to dimension tables through foreign key relationships.

Complete the Relationship Statements

1. Sales.CustomerID Customer.CustomerID
2. Sales.ProductID Product.ProductID
3. Sales.DateID Time.DateID
4. One Customer can have Sales records