Pollution Monitoring System

Dashboard

System overview and pollution metrics

🏭 Top Polluted Locations (PM2.5)

⚠️ Violations Breakdown

📋 Recent Inspections

Teacher Mode

DBMS explanation of schema, keys, relationships, and SQL flow

Industrial Pollution Monitoring System

This project stores industries, their locations, monitoring stations, pollution readings, inspections, violations, and users. The main DBMS idea is that every real-world entity is represented as a table, connected using primary keys and foreign keys.

Project Aim and Problem

Aim: build a centralized relational database for industrial pollution monitoring and compliance management.
Problem: pollution readings, inspections, and violations are hard to track when they are kept in scattered files or manual records.
Solution: store industries, locations, stations, readings, inspections, and penalties in linked MySQL tables.
DBMS focus: ER modelling, relational mapping, normalization, constraints, joins, aggregates, views, triggers, procedures, cursors, and transactions.

Scope From Report

Industries and their city/area locations.
Monitoring stations categorized as Air, Water, Noise, or Combined.
Pollution readings for PM2.5, PM10, NO2, SO2, water pH, and noise level.
Inspection reports with inspector name, date, remarks, and result.
Violation records with penalty amount and status.

ER Diagram

Location PK location_id area_name city

Industry PK industry_id industry_name industry_type license_number UNIQUE FK location_id

MonitoringStation PK station_id FK location_id station_type

PollutionReading PK reading_id FK station_id reading_datetime PM25, PM10, NO2, SO2 water_ph, noise_level

Inspection PK inspection_id FK industry_id inspection_date inspector_name remarks, result

Violation PK violation_id FK industry_id FK reading_id violation_type penalty_amount, status

1:N

Explain this as: Location is the parent table. Industries and monitoring stations belong to a location. Stations generate readings. Industries are inspected. Violations connect both the industry responsible and the reading that caused the violation.

Overall Working

1. User logs inThe Users table checks username, password, and role.

2. Browser calls APIThe frontend sends requests like GET /api/tables/Industry/data.

3. Server runs SQLNode.js creates SELECT, INSERT, DELETE, and dashboard aggregate queries.

4. MySQL returns rowsThe result set is sent back as JSON and shown as tables/charts.

Tables, Primary Keys, and Purpose

Table	Primary Key	Main Purpose	Important Foreign Keys
Users	user_id	Stores login accounts and role-based access.	None
Location	location_id	Stores area and city details.	None
Industry	industry_id	Stores industry details and license number.	location_id references Location
MonitoringStation	station_id	Stores air, water, noise, or combined stations.	location_id references Location
PollutionReading	reading_id	Stores measured PM2.5, PM10, pH, noise, and gas values.	station_id references MonitoringStation
Inspection	inspection_id	Stores official inspection reports for industries.	industry_id references Industry
Violation	violation_id	Stores penalties raised from readings and industries.	industry_id references Industry; reading_id references PollutionReading

Normalization Explanation

1NF: every field stores atomic values; there are no repeating groups. Pollution values are separate columns like PM25, PM10, water_ph, and noise_level.
2NF: every table uses a single-column primary key, so non-key attributes depend on the full key and there are no partial dependencies.
3NF: non-key attributes depend only on the primary key. For example, city is kept in Location, not repeated inside Industry.
Result: lower redundancy, fewer update anomalies, and cleaner joins between related tables.

Functional Dependencies

location_id determines area_name, city

industry_id determines industry_name, type, license, location_id

station_id determines location_id, station_type

reading_id determines station_id and pollution values

inspection_id determines industry_id, date, inspector, result

violation_id determines industry_id, reading_id, penalty, status

Key DBMS Concepts Used

Primary Key: uniquely identifies each row, such as industry_id in Industry.
Foreign Key: connects two tables, such as Industry.location_id to Location.location_id.
Normalization: repeated data is separated into related tables instead of one large table.
Constraints: CHECK, UNIQUE, NOT NULL, and foreign keys protect data correctness.
CRUD: admin can create, read, update, and delete records using SQL through the backend.
Aggregate Queries: dashboard counts, sums, averages, GROUP BY, and ORDER BY summarize data.

Relationship Summary

Location 1 to many Industry

Location 1 to many MonitoringStation

MonitoringStation 1 to many PollutionReading

Industry 1 to many Inspection

Industry 1 to many Violation

PollutionReading 1 to many Violation

Useful SQL Examples for Explanation

SELECT i.industry_name, l.city
FROM Industry i
JOIN Location l ON i.location_id = l.location_id;

SELECT violation_type, COUNT(*) AS total
FROM Violation
GROUP BY violation_type;

SELECT l.city, ROUND(AVG(pr.PM25), 2) AS avg_pm25
FROM PollutionReading pr
JOIN MonitoringStation ms ON pr.station_id = ms.station_id
JOIN Location l ON ms.location_id = l.location_id
GROUP BY l.city;

SQL Features Demonstrated

DDL: CREATE TABLE statements define entities, keys, constraints, and cascades.
DML: INSERT, UPDATE, and DELETE manage sample data and admin operations.
DQL: SELECT queries retrieve data for tables, dashboards, and reports.
JOINs: combine Industry with Location, Violation with PollutionReading, and station readings with city details.
Aggregates: COUNT, SUM, AVG, MAX, GROUP BY, and HAVING create summaries like total penalties and average PM2.5.
Views: report-oriented views summarize pollution and compliance data.

Procedural SQL Layer

Trigger: trg_detect_violation runs after a new PollutionReading and auto-creates Violation rows when PM2.5 > 100, water pH < 5, or noise > 85 dB.
Procedures: sp_generate_pollution_report, sp_record_inspection, and sp_safe_insert_industry encapsulate common operations.
Functions: fn_pollution_severity and fn_water_quality classify pollution readings into meaningful labels.
Cursors: inspection and city dashboard procedures process row-by-row summaries.
Exception handling: duplicate license, invalid location, invalid industry, and SQL errors are handled with messages or rollback.

Transaction and ACID Explanation

Atomicity: inspection recording either fully inserts or rolls back on error.
Consistency: PK, FK, CHECK, UNIQUE, and trigger rules keep the database valid.
Isolation: concurrent users should not interfere with each other's transaction state.
Durability: after COMMIT, changes remain saved in MySQL.
Example: sp_resolve_violations updates pending violations inside a transaction and uses ROLLBACK when needed.

SQL Console

Run custom queries against the database

Quick Queries:

Run a query to see results here

👥 User Management

ID	Username	Full Name	Role	Created	Actions

Table

🌫️ Pollution Readings

View environmental monitoring data (read-only)

ID	Station	Type	Location	Date	PM2.5	PM10	NO2	SO2	Water pH	Noise (dB)	Severity	Violation

🔍 Inspection Reports

View inspection history (read-only)

ID	Industry	Date	Inspector	Remarks	Result

⚠️ Violation Records

View compliance violations (read-only)

ID	Reading ID	Station ID	Industry	Type	Penalty (₹)	Status

🏭 Registered Industries

View industry directory (read-only)

ID	Name	Type	License	Location	City

PollutionDB