This project focuses on building and analyzing a relational database using SQL. Starting from several CSV files, the work involves designing a star‑schema database, creating its tables, importing data, and performing analytical queries to extract insights.
The workflow follows a complete data engineering and analytics pipeline:
- Designing the database structure.
- Creating dimension and fact tables.
- Importing and validating data from multiple CSV sources.
- Handling MySQL configuration constraints such as
local_infileandsecure_file_priv. - Executing analytical SQL queries involving joins, aggregations, and subqueries.
The result is a fully functional database (sales_track) ready for business analysis.
This level covers all tasks required to set up the database environment:
- Creation of the database.
- Definition of the schema following a star model:
- Creation of all tables with appropriate data types, primary keys, and foreign keys.
- Configuration of MySQL to allow file imports:
- Checking and enabling
local_infile - Identifying the allowed directory via
secure_file_priv
- Checking and enabling
- Importing data from CSV files
- Verifying data integrity through exploratory queries.
This level establishes the full data infrastructure needed for analysis.
- Initial exploratory subquery to inspect transaction counts per user.
- Identification of the most active users in the dataset.
- Identification of the company ID of Donec Ltd and inspection of all transactions associated with that company.
- Analytical queries and calculation of the average spending per credit card for transactions made at Donec Ltd.
- SQL (MySQL dialect) — used for schema creation, data loading, and analytical queries.
- MySQL Workbench
- MySQL Server 8.0
- Relational database engine
- Management of
secure_file_privandlocal_infile