We are building a centralized SQL database for ANORD, a pharmaceutical distributor, to capture and organize all operational data: clients, branches, products, laboratories, employees, invoices, and sales transactions.
Why are we doing this?
This database allows ANORD’s leadership team to evaluate performance across clients, products, and sales representatives. By comparing sales versus last year, monitoring profitability, and identifying trends, ANORD gains the ability to:
- 📈 Spot top and underperforming clients
- 💊 Track product growth and declines
- 💶 Measure profitability by client, product, and representative
- 🧭 Support data-driven decision-making for strategy and sales optimization
In short, this project transforms raw sales and operational data into actionable insights for executives and managers.
We designed a relational database schema and developed analytical SQL views.
The schema defines key entities (clients, products, invoices, employees, laboratories, etc.), while the views provide dashboards to measure performance, profitability, and growth trends.
- Install PostgreSQL and a SQL IDE such as DBeaver.
- Load the ANORD dataset into PostgreSQL.
- Sample dataset: PostgreSQL Sample Database
- Open the project files in DBeaver:
Scripts/schema.sql→ Creates all tables.View 1.1,View 1.2, …View 7→ SQLCREATE VIEWstatements.
- Execute the scripts to generate the schema and views.
- Query the views to access ready-made reports.
The following views are available:
- View 1.x → Customer performance analysis (best/worst vs. last year)
- View 2.x → Product performance analysis (best/worst vs. last year)
- View 3.x → Profitability by client-product
- View 4.x → Profitability by client
- View 5 → Sales representatives’ performance
- View 6 → Discount and profitability tracking
- View 7 → Client-product trends and supporting insights
- Paul Guillard
- Mario Perez
Developed as part of the Business Reporting Tools – Group Assignment 2024.