A retail company wants to build a data warehouse to analyze their sales data. The data is sourced from their transactional database which contains information on sales transactions, products, customers, and stores. Here are the details:

• Sales Transactions: Contains records of each sale, including transaction ID, date, customer ID, product ID, store ID, quantity sold, and total amount.
• Products: Contains details of each product including product ID, name, category, price, and manufacturer.
• Customers: Contains customer details including customer ID, name, age, gender, and contact information.
• Stores: Contains information on each store including store ID, location, size, and the number of employees.

Assignment Instructions - Identifying Fact and Dimension Tables

Objective

To analyze a real-world scenario and identify the fact and dimension tables, along with the relationships, primary keys, and foreign keys.

Instructions

Review the Scenario: Carefully read the scenario provided above. Ensure you understand the details and the data involved in the scenario.

Identify the Tables

• Fact Table(s): Identify the fact table(s) from the scenario. The fact table should contain the measurable, quantitative data that can be analyzed.
• Dimension Table(s): Identify the dimension table(s) that will provide the context to the data stored in the fact table.

Identify the Keys

• Primary Keys: For each table identified, determine the primary key. The primary key is a unique identifier for each record in the table.
• Foreign Keys: Identify the foreign keys that establish relationships between the fact and dimension tables.

Establish Relationships: Define the relationships between the tables. Explain how the tables relate to each other and how data flows between them.

Visual Representation: Create a visual representation (like an ERD - Entity Relationship Diagram) of the tables, showcasing the relationships, primary keys, and foreign keys.

What to Submit

• Visual Representation: Using the Visio Professional software provide an ERD for this scenario.