⚡ Smart EV Charging Station – Database Backend

A complete, production-ready MySQL database backend for a Smart EV Charging Station Management System.
This system leverages advanced SQL features like Stored Procedures, Triggers, and Transactions to create a reliable, automated, and concurrency-safe backend for real-world EV station management.

This serves as the foundational data layer for a web or mobile app—managing users, stations, reservations, payments, and live analytics seamlessly.

---

🚀 Key Features

• 🧠 Automated Reservation System:
  The stored procedure sp_MakeReservation ensures no overlapping bookings by checking slot availability in real time.

• 💾 Transactional Integrity (ACID):
  Every booking and payment runs inside a transaction (COMMIT/ROLLBACK), guaranteeing data consistency and safety.

• ⚙️ Real-time Slot Automation:
  Triggers update slot status automatically—marking them as Booked after reservation and back to Available after payment.

• 📊 Live Energy Analytics:
  The trg_after_payment_update_energy trigger aggregates daily energy data into the Energy_Record table—zero manual work needed.

• 🧩 Normalized Schema:
  Six fully normalized tables ensure data scalability, efficiency, and zero redundancy.

---

🧱 Database Schema Overview

The database contains 6 core tables:

Table	Purpose
User	Stores customer details like name, vehicle, and contact info.
Charging_Station	Holds station details such as name, location, and rate per unit.
Charging_Slot	Represents individual slots at a station with status and capacity.
Reservation	Links users to slots for specific time intervals.
Payment	Records all transaction details for charging sessions.
Energy_Record	Stores aggregated daily energy consumption per station.

---

🧠 Core Logic Breakdown

1️⃣ Stored Procedure: sp_MakeReservation (Booking & Concurrency)

This procedure is the only entry point for making a new booking.
It dynamically checks for non-overlapping slots and prevents double-booking using the following logic:

-- Find a slot that is NOT in the set of busy slots
SELECT s.Slot_ID INTO v_slot_id
FROM Charging_Slot s
WHERE s.Station_ID = p_station_id
  AND s.Status != 'Maintenance'
  AND s.Slot_ID NOT IN (
    SELECT DISTINCT r.Slot_ID
    FROM Reservation r
    WHERE r.Slot_ID IN (
      SELECT Slot_ID FROM Charging_Slot WHERE Station_ID = p_station_id
    )
    AND (p_start_time < r.End_Time AND p_end_time > r.Start_Time)
    AND r.Payment_Status != 'Failed'
)
LIMIT 1;

---

2️⃣ Stored Procedure: sp_ProcessPayment (Billing & Settlement)

Handles payment processing, cost calculation, and session closure—all within a single transaction.

START TRANSACTION;

-- Step 1: Record payment
INSERT INTO Payment (User_ID, Reservation_ID, Amount, Payment_Mode, Transaction_Time)
VALUES (v_user_id, p_reservation_id, v_calculated_amount, p_payment_mode, NOW());

-- Step 2: Update reservation to 'Paid'
UPDATE Reservation
SET Payment_Status = 'Paid', Units_Consumed = p_units_consumed
WHERE Reservation_ID = p_reservation_id;

COMMIT;

---

3️⃣ Automation Triggers (Real-Time System Updates)

Trigger	Fires On	Action
trg_after_booking_insert	After new reservation	Updates slot status → Booked
trg_after_payment_free_slot	After payment confirmation	Sets slot status → Available
trg_after_payment_update_energy	After payment confirmation	Updates/aggregates Energy_Record with units consumed

---

🧩 Setup & Demonstration

✅ Requirements

• MySQL Server (8.0+ recommended)
• Any SQL client (MySQL Workbench, DBeaver, or CLI)

---

⚙️ Setup Steps

1. Clone this repository or download the .sql file.

2. Open the file in your MySQL client.

3. Execute the entire script to automatically:

   • Create the ev_charging_db database
   • Create all 6 tables with relationships and constraints
   • Generate 2 stored procedures and 3 triggers
   • Insert demo data (users, stations, slots)

---

🧪 Demonstration

Run these queries step-by-step to test the system.

🔹 Demo 1: Check Available Slots

SELECT s.Slot_ID, s.Status, s.Energy_Capacity
FROM Charging_Slot s
JOIN Charging_Station cs ON s.Station_ID = cs.Station_ID
WHERE cs.Station_Name = 'Gharuan SuperCharge' AND s.Status = 'Available';

🔹 Demo 2: Make a Reservation

CALL sp_MakeReservation(1, 1, TIMESTAMP(CURDATE(), '15:00:00'), TIMESTAMP(CURDATE(), '16:00:00'), @msg);
SELECT @msg AS 'Reservation_Status';

Result: Reservation successful for Slot ID: 1

---

🔹 Demo 3: Trigger Activation Check

SELECT Slot_ID, Station_ID, Status FROM Charging_Slot WHERE Slot_ID = 1;

Result: Slot 1 → Booked

---

🔹 Demo 4: Concurrency Logic Test

CALL sp_MakeReservation(2, 1, TIMESTAMP(CURDATE(), '15:30:00'), TIMESTAMP(CURDATE(), '16:30:00'), @msg);
SELECT @msg AS 'Reservation_Status';

Result: Books next available slot or safely rolls back if none are available.

---

🔹 Demo 5: Payment Processing

CALL sp_ProcessPayment(1, 25.5, 'UPI', @msg);
SELECT @msg AS 'Payment_Status';

Result: Payment successful! Amount: 382.50

Triggers Fired:

1. Payment recorded in Payment
2. Slot set to Available
3. Energy usage updated in Energy_Record

---

🔹 Demo 6: Check Analytics

SELECT * FROM Energy_Record WHERE Station_ID = 1 AND Date = CURDATE();

Result: Total_Units_Consumed: 25.50

---

🔮 Future Scope

• REST API Layer: Build an API (Node.js, Flask, or Spring Boot) that directly calls stored procedures.

  • POST /api/reservations → CALL sp_MakeReservation(...)
  • POST /api/payments → CALL sp_ProcessPayment(...)

• Frontend Integration: Develop a dashboard using React, Vue, or Angular.

• IoT Hardware Link: Sync slot Status with real-world sensors and chargers.

---

📄 License

Licensed under the MIT License. Free to use, modify, and distribute for educational or commercial purposes.

---

---

Would you like me to also **add badges**, **a table of contents**, and **syntax-highlighted SQL snippets** (GitHub style) to make it look like a professional open-source project README (like on GitHub trending repos)?