AdvancedDataModeling

Welcome to my take on data modelling, in this journey I will

• Design a conceptual model using draw.io
• Use MySQL Workbench to build er diagrams
• Use the forward engineering method to create databases
• Design a relational database model by using a systematic approach and applying the correct normalization levels.
• Build a dimensional data model.
• Data visualization... connecting and preparing data in Tableau and creating interactive dashboards in Tableau.

Mission 1: Design a database model for M&G

Objective: Design a simplified logical relational database model for Mangata and Gallo.

Mangata and Gallo (M&G) has built an ad hoc database system to store data about their customers, products, orders and delivery status in one large table with the columns listed below:

• Client name
• Client address
• Order Date
• Delivery Status
• Delivery Date
• Contact Number
• Email
• Item name
• Item price
• Total cost

This database is difficult to manage and includes loads of redundant data. Create a proper database model for a simplified and logical relational database. The database modelling tool I used was draw.io.

Task 1: Create a conceptual model

Create a conceptual model to support M&G’s online ordering system. The model should consider the entities listed in the M&G big table.

Task 2: Create a logical ER diagram

Based on the conceptual model developed, create a logical ER diagram as following:

• Translate each entity in the conceptual diagram into a table with relevant attributes
• Specify the primary key of each table.
• Create a multiplicity relationship between the tables.
• Define relevant constraints such as NOT NULL and foreign keys.
• Review the logical ER diagram, and make sure that your data model conforms to the first normal form by applying the data atomicity rule. Some clients could have multiple delivery addresses, to apply data atomicity create an Address table and relate it to the delivery table

Mission 2: Design a database model in MySQL Workbench

Mangata & Gallo (M&G) jewelry store wants to make use of the logical database model outlined in the diagram below. Develop this model using MySQL Workbench and implementing it in your MySQL server.

Task 1: Create an ER diagram

Using the visual data modeling tool in MySQL Workbench to create the proposed ER diagram for M&G.

NOTE: Do not use "&" (or any symblol) when naming a database like I did, running a query with it is dramaaa

Task 2: Implement the internal schema

Use MySQL Workbench’s forward engineer feature to implement the internal schema in your MySQL server.

It then should be able to appear the schema list in the navigator section.

Task 3: Populate the M&G database & create view

Populate the M&G database with data provided using the SQL Workbench editor.

Use the INSERT statements.

Create a virtual table to easily find information on orders. This information must contain data from all tables including:

• Clients
• Orders
• Products
• Delivery
• Address

The output will be ..........

Mission 3: Create a dimensional data model

Create a dimensional model for Global Super Store to help them make sense of their sales and profits. Global Super Store have experienced a decline in their profits in the last few years.There are several factors that impacted their profits including:

• Global instability around shipping and product costs.
• New competitors appearing in different markets around the world.
• Out of date products.
• Emerging new technologies.
• The development of new products.

Global Super Store needs to understand how these factors are affecting their sales and profits. They need to compare data amongst different customers, products, times and locations to understand the problem.

Task 1: Identify key information

Identifying the business process you want to deal with in this case it is the sales process. Identify the grain, the dimensions and the measures to be used to build the dimensional model.

Levels of granularity:

• Region, country and city
• Year, quarter, month, day or event levels
• Category, subcategory and items

The dimensions:

• Location
• Time
• Product
• Customers

The facts:

• The buy and sale prices of all products
• The quantity sold of each product
• The shipping cost of each product

Task 2: Create a star schema

Create a Star Schema based on the dimensions and facts identified in task 1. Create the dimensions and the fact tables including relevant attributes and data types in each table. Define the primary and the foreign keys in the data model.

Task 3: Create a snowflake schema

Extend the Star Schema developed in task 2 by creating a suitable Snowflake Schema with a particular focus on the products dimension.

Mission 4: Data analysis in Tableau

The Global Super Store dataset includes more than 51000 records of data about customers, orders and products in MS Excel file. We now need to analyze this data to understand their business activities and maximize their profits.

The tasks are completed in Tableau.

Task 1: Prepare the data set for analysis

Connect to the Global Super Store data set.

Prepare it for data analysis by making sure that all fields like the Order date and the Ship date contain the correct data types.

Create a new calculated field called Warranty based on 90 days from the order date.

Task 2: Create a map chart

Global Super Store want to investigate their business performance in Africa. Create a map chart that shows sales in different countries in Africa. The map should show the sales in proportional sizes. If you rollover a country, you should be able to see:

• Country name
• Quantity sold
• Sales figures

From the visualization we can easily determine the countries with the highest and lowest sales and their quantities.

Here is a link to the worksheet on Tableau Public https://public.tableau.com/views/GlobalSuperStoreSalesinAfrica/Sheet1?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link

Task 3: Create a bar chart

Global Super Store want to check the profits made in each country in Africa. However, they are only interested in data from countries where they have made at least $500 in profit.

Create a bar chart in Tableau called Profits in Africa. When you rollover a bar, you should be able to see

• Name of the country
• Profits
• Shipping cost

From the visualisation we can easily determine the country with the highest and lowest profits, estimate the median though we can't determine if there is a relationship between profit and shipping cost, we would need a different chart, a scatter plot, for that

Here is a link to the worksheet on Tableau Public https://public.tableau.com/views/GlobalSuperStoreProfitsinAfrica/ProfitsinAfrica?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link

Step 4: Create a dashboard

Develop a dashboard that includes the two visualizations created: Sales in Africa map and the profits in Africa bar chart

The dashboard can be made interactive so that when you click on a specific country in the map the information related to that country will be displayed in the bar chart.

Here is a link to the dashboard on Tableau Public https://public.tableau.com/views/GlobalSuperStoreSalesProfitsDashboard/AfricaSalesProfitDashboard?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link