Want to make interactive content? It’s easy in Genially!

Over 30 million people build interactive content in Genially.

Check out what others have designed:

Transcript

Preparation Task (10 minutes)

Research Task (30 minutes)

Analysis Task (30 minutes)

Create Task (20 minutes)

Hear from a professional in this role to see how they might approach this task.The following pages are a detailed model answer for the work simulation on Marketing Intelligence, geared towards a student interested in becoming a Data Visualisation Specialist.

Document and Present (15 minutes)

Reflection Task (10 minutes)

Expected Outcome

Model Answer

1/9

2/9

Simulation

Model answers

1. Customer Data

4. Sales Data

5. Relationships Between Entities

3. Order Data

6. Inventory Monitoring

2. Product Data

Preparation Task (15 minutes)

Objective: Understand the business context and plan the database architecture.

Customer Analysis: Visualise customer distribution by state or city to help with targeted marketing.Sales Trends: Create a line graph showing how TotalAmount of sales changes over time.Product Performance: Use a bar chart to show which product categories have the most sales, helping with stock and marketing decisions.Order Frequency and Seasonality: Use heat maps to detect times of high sales activity, which helps in planning promotions.

Example Goals for Visualisation:

3/9

Simulation

Model answers

Research Task (20 minutes)

Objective: Conduct research on database design principles to ensure a well-structured database diagram.

Database Design Principles: I focused on reading about data normalisation and best practices for database design. I learned that data should be organised to reduce redundancy and that entities should be split into separate tables.Normalisation ensures that data is scalable, easy to query, and avoids anomalies during updates or deletionsIndustry Examples: I reviewed database diagrams from similar e-commerce platforms. For instance, I noticed many platforms break down their data into logical entities like "Customers," "Orders," and "Products," with foreign keys linking them. I also looked at how they handled product categories and the connection between products and customers through ordersKey Learnings: I realised the importance of using primary keys for identifying records uniquely, foreign keys for defining relationships between tables, and indexing for improving query performance.

4/9

Simulation

Model answers

Analysis Task (15 minutes)

Objective: Identify key entities and relationships for the database based on the business scenario.

Entities Identified:

  • Customers: Stores customer info (CustomerID, Name, City)
  • Products: Stores product info (ProductID, Name, Price, Type)
  • Orders: Records order details (OrderID, OrderDate, C
  • Sales: Stores sales data (SalesID, Quantity, Price)
Relationships Identified:
  • A customer can place multiple orders (one-to-many relationship between Customers and Orders)
  • An order can contain multiple products (many-to-many relationship between Orders and Sales, managed by the Sales table)
  • Products belong to one category (one-to-one relationship between Products and Categories).
Data Flow Consideration: To support visualisation, this structure allows tracking of individual customer purchases, product sales by type, and overall order trends, essential for creating reports such as "total revenue per product type" or "customer order frequency."

5/9

Simulation

Model answers

Tools Used

Defining Relationships

Data Types and Constraints

Entities and Fields

Create Task (40 minutes)

Objective: Design a relational database schema. Use a database diagramming tool to create a schema that meets the necessary requirements.

5/9

Simulation

Model answers

Document and Present Task (10 minutes)

Step one:Key Relationships

  1. Customers → Orders (One-to-Many): A customer can place multiple orders, enabling tracking of customer order history and behaviour.
  2. Orders → Sales (One-to-Many): Each order can include multiple products, capturing detailed order information for reporting.
  3. Products → Sales (One-to-Many): Products can appear in multiple orders, supporting product performance analysis.
Step two:Data Normalisation
  1. Redundancy Reduction: Separated data into distinct tables (Customers, Orders, Products, Sales) to eliminate duplication.
  2. Data Integrity: Used primary keys for unique identification and foreign keys to enforce relationships, ensuring consistent and accurate data.
Step three & four:Support for Data Visualisation
  1. Customer Order Tracking: Enables metrics like total orders per customer, average order value, and purchase frequency.
  2. Product Performance: Provides insights on sales volume, revenue per product, and best-sellers by type.
  3. Dashboard Metrics: Supports real-time visualisations for total sales, sales trends, and customer lifetime value.

7/9

Simulation

Model answers

Reflection Task (10 minutes)

  • What did you learn from creating this database diagram, and how does the final design differ from your initial sketch?
I learned the importance of normalisation to reduce redundancy, using primary and foreign keys to ensure data integrity, and defining clear relationships between entities. The final design is more scalable and flexible, with separate tables for Products, Categories, and OrderDetails, unlike the initial sketch, which combined related data into fewer tables.
  • If you were to redesign this database, what changes would you make to better support data visualisation?
I would add summary tables or materialised views to pre-aggregate key metrics like total sales by product or customer, incorporate timestamps for better trend analysis, and introduce indexing on frequently queried fields to improve performance for real-time dashboards.

Objective: Reflect on the process and learning outcomes.

8/9

Simulation

Expected outcome

By completing this simulation, I successfully designed a well-structured, normalised database diagram that supports effective data visualisation. The experience improved my skills in data modelling, relationship design, and database diagramming, which are essential for any Data Visualisation Specialist.

Expected Outcome

9/9

Demographic Information:

  • Information like CustomerID, Customer Name, Age, City, State.
Goal for Visualisation: Understand customer demographics to identify target markets or areas with high demand. Possible Charts/Visuals:
  • Heat Map: Visualise the distribution of customers by state.
  • Bar Chart: Show the number of customers in different age groups.

1. Customer Data

Entities and Fields: Customers table:CustomerID (Primary Key)Customer NameGenderAgeHome AddressZip CodeCityStateProducts table:ProductID (Primary Key)Product TypeProduct NameSizeColourPriceQuantityOrders table:OrderID (Primary Key)CustomerID (Foreign Key to Customers table)Order DateDelivery DateSales table:SalesID (Primary Key)OrderID (Foreign Key to Orders table)ProductID (Foreign Key to Products table)Price Per UnitQuantityTotal Price

Stock Quantity: Tracking the available stock quantity for each product.Goal for Visualisation: Monitor inventory and ensure items are restocked when quantities get low.Possible Charts/Visuals:Inventory Control Dashboard: Show stock levels with visual indicators for products that are running low (e.g., red/yellow/green lights).

6. Inventory Monitoring

Order Details: Key attributes like SalesID, Quantity, Price Per Unit, and Total Price.Goal for Visualisation: Track customer purchasing behaviour and monitor sales trends.Possible Charts/Visuals:

  • Stacked Bar Chart: Visualise how the total order price is broken down into units.
  • Bar Chart: Examine the total revenue generated per product.

4. Sales Data

Order Details: Key attributes like OrderID, CustomerID, Order Date, and Delivery Date.Goal for Visualisation: Understand when orders are most likely to be placed.Possible Charts/Visuals:

  • Line Chart: Visualise sales trends over a timeline to understand peaks and slow periods in Order Date.
  • Heat Map: Display the frequency of orders over different times of the year to identify seasonal trends.

3. Order Data

Relationships to Analyse: Understanding one-to-many and many-to-many relationships, such as:

  • Customers and Orders (i.e., one customer placing multiple orders).
  • Orders and Sales (i.e., an order containing multiple products).
Goal for Visualisation: Analyse customer order frequency and relationships between product sales and order data.Possible Charts/Visuals:Network Diagram: To visualise relationships between Customers and their Orders, as well as Products within those orders.

5. Relationships Between Entities

Data Types and Constraints:

  • I defined data types for the fields (e.g., CustomerID as an integer, Name as a varchar)
  • Primary keys were set for each table, and foreign key constraints were applied to ensure proper relationships between tables.

Product Information: Data such as ProductID, Product Name, Product Type, Size, Price, and Quantity.Goal for Visualisation: Understand product availability and inventory status.Possible Charts/Visuals:

  • Radar Chart: Compare different product categories and sizes based on inventory levels.
  • Scatter Chart: Evaluate any relationship between price and stock level.

2. Product Data

Tool Used: Lucidchart was used to design the diagram

Defining Relationships:

  • I linked the Customers table to the Orders table via CustomerID to show that customers can place multiple orders
  • The Sales Table was connected to Orders via OrderID and also to Products via ProductID to capture multiple products within an order