Reference no: EM133705082
PROJECT ASSESSMENT
Purpose : Deepen understanding of data mining and warehousing principles.
Apply advanced SQL techniques to analyze real-world business data.
Gain insights into sales performance, customer satisfaction, and staff efficiency.
Enhance analytical skills for data-driven decision-making.
Derive meaningful business intelligence from complex datasets.
Develop essential competencies for today's business landscape.
Learning Outcome 1: Deconstruct complex business problems to apply data mining and warehousing solutions, data requirements and data models using relational databases
Learning Outcome 2: Design and critically reflect upon data warehousing architecture and multidimensional data models using internal and external business data
OVERVIEW
This assessment is designed to test your practical skills in data mining and warehousing using MySQL, focusing on simulated real-world business scenarios. You are provided with a MySQL schema and corresponding records to perform data analytics.
SCENARIO OVERVIEW
A retail chain, "AussieRetailers," operates across multiple states in Australia, tracking sales transactions, maintaining customer profiles, monitoring product inventory, scheduling staff shifts, and recording customer complaints. This data is stored in separate tables within a MySQL database. Download bco7004- assessment2.sql from VU Collaborate and upload it into your MySQL Workbench software.
DATABASE
Download bco7004-assessment2.sql by Clicking Here (bco7004-assessment2.sql? isCourseFile=true&ou=1968930).
TABLES OVERVIEW
Sales: Records each sale, including sale ID, date, product ID, quantity, customer ID, and branch ID.
Customer Details: Stores customer information, including customer ID, name, contact details, and loyalty program status.
Product Details: Contains product information, such as product ID, name, category, price, and stock levels.
Staff Shift Details: Logs staff shifts, including staff ID, branch ID, shift date, start time, and end time.
Complaint Details: Records customer complaints, with complaint ID, customer ID, product ID, complaint date, and resolution status.
ASSESSMENT INSTRUCTIONS
Objective
Analyze "AussieRetailers'" operational data using advanced SQL queries, focusing on sales performance, customer satisfaction, and staff efficiency. The analysis should culminate in a detailed report with insights, trends, and recommendations.
TASKS
Data Preparation:
Draw an ERD diagram with foreign and primary keys for each table (entity).
Normalize the data if necessary to eliminate redundancy and ensure database efficiency (if applicable).
Analysis Requirements:
List all products and their categories with sales greater than 100 units.
Calculate the total revenue per branch, considering the quantity sold and product prices.
Identify customers who have made purchases in more than 3 different branches.
Determine the average sale quantity of products by category for sales made in the last quarter.
Rank products within each category based on the total sales quantity using a window function.
Show the month-over-month percentage growth in sales for the top 5 products by total quantity sold.
Find all products that have not been sold in the last 6 months (from June to December 2022) but have stock levels above 50.
Calculate the total number of complaints lodged against products in each category.
List the top 10 customers by total spending and show their most frequently bought product category.
Identify days of the week with the highest sales transactions volume.
Determine the branch with the lowest stock levels across all products.
Analyze the correlation between loyalty program status and the average transaction value per customer.
Calculate the average duration between complaint registration and resolution.
Identify staff members with shifts longer than 8 hours and list their corresponding branches and shift dates.
Find the product with the highest number of complaints and detail the nature of these complaints.
Additionally:
Design 5 of your own SQL questions that are relevant to the dataset and aimed at uncovering insightful analytics to support business decisions.
Answer the following 15 SQL questions using the provided MySQL schema and records. These questions are designed to test various aspects of data mining and warehousing techniques:
Report Writing:
Submit a comprehensive report explaining your methodology for answering each of the 20 questions (15 provided + 5 self-designed).
For each question, detail the SQL query design process, explaining the choice of SQL clauses and functions.
For the self-designed questions, provide a rationale explaining their importance and relevance to business analytics.
Include screenshots from your MySQL Workbench as evidence of query execution and results.
Criterion 1: Deconstruct and identify aspects of business problem
Criterion 2: Selection and application of solutions
Criterion 3: Critically evaluate big data processing frameworks and technologies
Criterion 4: Contextualise the use of advanced SQL
Criterion 5: Implement and critically evaluate streaming methods in big data processing