Interactive Lesson: Data Modeling
🛍️ 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
OrderID, Amount, Quantity, Discount
Customer
CustomerID, Name, Email, City, State
CustomerID, Name, Email, City, State
Product
ProductID, Name, Category, Price, Brand
ProductID, Name, Category, Price, Brand
Time
DateID, Date, Month, Quarter, Year
DateID, Date, Month, Quarter, Year
Store
StoreID, Location, Manager, Region
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