Air Quality Dashboard

Group Members : Meenakshy Manju, Mounika Kaluvakuntla

1. Introduction

The Air Quality Dashboard is a web-based application designed to monitor and analyze air quality data across multiple locations. The app allows users to view detailed measurements, calculate summary statistics and visualize trends over time. Air quality data includes parameters such as PM10, PM2.5, CO2 and others collected at specific locations. The system is built for environmental researchers, facility managers and students to analyze air quality efficiently across different locations and parameters.

Primary use cases:

a. View real-time and historical air quality measurements for a selected location.
b. Compare multiple parameters through interactive charts.
c. Calculate summary statistics (min, max and average) for chosen parameters.
d. Filter data by location and parameter for detailed analysis.

User Roles:

There are two roles - Admin and User

Main purpose of each user:

User Role	Purpose
Admin	Maintain the database (create/edit/delete users, locations, measurements and parameters)
User	Access the dashboard, view measurements and summary statistics and analyze air quality trends

2. Relational Diagram

Entities:

Users - Stores login credentials and role.
Location - Stores information about each location (state and county).
Measurement - Stores air quality measurements with timestamp, locationid, parametercode and measurement value.
Parameter - Stores air quality parameter names, corresponding parameter codes and units of measurement.

3. Credentials per user role

Role	Username	Password
Admin	admin@gmail.com	123
User	user@gmail.com	456

4. SQL Queries

These are some of the SQL queries used in the app

Summary Statistics

SELECT 
    p.parametername,
    ROUND(AVG(m.samplemeasurement), 2) AS avg_value,
    ROUND(MIN(m.samplemeasurement), 2) AS min_value,
    ROUND(MAX(m.samplemeasurement), 2) AS max_value
FROM Measurement m
JOIN Parameter p ON m.parametercode = p.parametercode
WHERE m.locationid = '{locationid}'
GROUP BY m.parametercode
ORDER BY p.parametername;

Fetch Measurement Results with Location & Parameter Details

SELECT 
    m.measurement_time,
    m.samplemeasurement,
    l.state,
    l.county,
    p.parametername,
    p.unitsofmeasurement
FROM Measurement m
JOIN Location l ON m.locationid = l.locationid
JOIN Parameter p ON m.parametercode = p.parametercode
WHERE m.locationid = '{locationid}'
AND m.parametercode = '{parametercode}'
ORDER BY m.measurement_time DESC;

Fetch All Locations

SELECT locationid, state, county
FROM Location
ORDER BY state, county;

Fetch All Parameters

SELECT parametercode, parametername, unitsofmeasurement
FROM Parameter
ORDER BY parametercode;

5. User Dashboard

The image above shows the Air Quality Dashboard in action. On the left, the measurement results table displays detailed data for the selected location and parameter. On the right, the summary statistics table provides quick insights into the minimum, maximum and average values of the selected parameters.

The chart above visualizes trends over time, allowing users to easily compare multiple parameters and observe changes in air quality.