Embedded Files
Relational Database Creation and Data Exploration
Designed and analyzed a comprehensive SQL database for a bike shop, leveraging SQL queries to uncover key insights into sales performance and employee effectiveness. I aimed to enhance strategic decision-making and optimize business operations by creating detailed visualizations and actionable recommendations.
Tools: MySQL Workbench, MS Excel Charts
Data Source: The dataset comprises nine CSV files with simulated data for a bike shop, created as part of my graduate coursework in Data Management. While the initial data modeling was done during the course, the subsequent queries and visualizations are independently developed and go beyond the scope of the original assignments.
Database Creation and Preparation
Database Creation and Preparation
1. Conducted an in-depth analysis of 9 provided CSV files to discern their entity relationships.
2. Established key constraints to ensure data integrity.
3. Developed an ER-diagram illustrating tables, attributes, primary keys, and foreign keys.
4. Executed the creation of the bike shop database, running custom create scripts for all 9 tables.
5. Successfully imported CSV files into the bike shop database, ensuring seamless data integration.
Exploratory Data Analysis
Exploratory Data Analysis
-Determined the bicycle model generating the highest revenue.
- Identified states boasting the highest sales revenue.
- Explored the most popular bike model per state.
- Conducted a month-over-month comparison of sales for 2003 and 2004.
- Analyzed employee sales in conjunction with their respective salaries to pinpoint top performers.
- Transferred data from SQL queries to MS Excel for data visualization.
Bike Model Analysis
Bike Model Analysis
Highest overall revenue model: "Mountain Full" generates the highest overall revenue totaling 20,042,003.35
Further investigation: This prompts an investigation into whether mountain bikes are the most commonly purchased model, or if they have the highest average sale price.
Full mountain bikes are the highest revenue-generating and most commonly purchased model, with 6,403 units sold, reflecting strong market demand for this product.
State Sales Analysis
State Sales Analysis
Leading states in sales revenue: California, New York, Texas, Florida, and Illinois are the top states by sales revenue, possibly connected to factors like population density or average household income levels.
Regional preferences: Road bikes appear to be most popular in densely populated areas such as NYC, possibly influenced by higher numbers of individuals opting for bicycle commuting in urban settings.
Month-over-Month Sales Comparison
Month-over-Month Sales Comparison
Highest revenue months: November 2003 and December 2004 have the highest sales revenue.
Lowest sales months: February experienced the lowest sales volume in both 2003 and 2004, indicating a potential seasonal trend.
Employee Performance Analysis
Employee Performance Analysis
Salary vs. Sales volume: The employee with the highest salary also boasts the highest sales volume.
Salary vs. Sales performance discrepancy: Interestingly, the second-highest salary-earning employee demonstrates the second-lowest sales volume, suggesting a potential disparity between salary and sales performance.
These insights provide valuable information for strategic decision-making, such as inventory management, marketing strategies, and employee performance evaluations.
Page updated
Google Sites
Report abuse