voltlink.sql

-- =====================================================================
--  VoltLink - EV Charging Network Management System
--  Relational database (3NF) for MySQL 8.0+ / MySQL Workbench
--
--  CONTENTS
--    SECTION 1 : Database & schema (CREATE TABLE, keys, constraints)
--    SECTION 2 : Sample data (INSERT)
--    SECTION 3 : SQL queries (SELECT, JOIN, GROUP BY, subqueries, view)
--
--  All tables are in Third Normal Form (3NF). Run top to bottom.
-- =====================================================================

DROP DATABASE IF EXISTS voltlink;
CREATE DATABASE voltlink CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE voltlink;

-- =====================================================================
--  SECTION 1 : SCHEMA
--  Tables are created in dependency order (parents before children).
-- =====================================================================

-- Reference / lookup tables -------------------------------------------

CREATE TABLE region (
    region_id    INT AUTO_INCREMENT PRIMARY KEY,
    region_name  VARCHAR(60) NOT NULL UNIQUE
);

CREATE TABLE city (
    city_id    INT AUTO_INCREMENT PRIMARY KEY,
    city_name  VARCHAR(80) NOT NULL,
    region_id  INT NOT NULL,
    CONSTRAINT fk_city_region
        FOREIGN KEY (region_id) REFERENCES region(region_id),
    CONSTRAINT uq_city UNIQUE (city_name, region_id)
);

CREATE TABLE membership_plan (
    plan_id       INT AUTO_INCREMENT PRIMARY KEY,
    plan_name     VARCHAR(40) NOT NULL UNIQUE,
    monthly_fee   DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    discount_pct  DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT chk_discount CHECK (discount_pct BETWEEN 0 AND 100)
);

CREATE TABLE connector_type (
    connector_type_id  INT AUTO_INCREMENT PRIMARY KEY,
    type_name          VARCHAR(30) NOT NULL UNIQUE,   -- CCS2, CHAdeMO, Type2 ...
    max_power_kw       DECIMAL(6,2) NOT NULL
);

CREATE TABLE tariff (
    tariff_id        INT AUTO_INCREMENT PRIMARY KEY,
    tariff_name      VARCHAR(40) NOT NULL UNIQUE,
    rate_per_kwh     DECIMAL(6,3) NOT NULL,
    rate_per_minute  DECIMAL(6,3) NOT NULL DEFAULT 0.000
);

CREATE TABLE technician (
    technician_id  INT AUTO_INCREMENT PRIMARY KEY,
    full_name      VARCHAR(100) NOT NULL,
    phone          VARCHAR(25),
    hired_date     DATE NOT NULL
);

-- Core entities -------------------------------------------------------

CREATE TABLE driver (
    driver_id      INT AUTO_INCREMENT PRIMARY KEY,
    full_name      VARCHAR(100) NOT NULL,
    email          VARCHAR(120) NOT NULL UNIQUE,
    phone          VARCHAR(25),
    city_id        INT NOT NULL,
    plan_id        INT NOT NULL,
    registered_at  DATE NOT NULL DEFAULT (CURRENT_DATE),
    CONSTRAINT fk_driver_city FOREIGN KEY (city_id) REFERENCES city(city_id),
    CONSTRAINT fk_driver_plan FOREIGN KEY (plan_id) REFERENCES membership_plan(plan_id)
);

CREATE TABLE station (
    station_id    INT AUTO_INCREMENT PRIMARY KEY,
    station_name  VARCHAR(100) NOT NULL,
    address       VARCHAR(150) NOT NULL,
    city_id       INT NOT NULL,
    latitude      DECIMAL(9,6),
    longitude     DECIMAL(9,6),
    opened_date   DATE NOT NULL,
    CONSTRAINT fk_station_city FOREIGN KEY (city_id) REFERENCES city(city_id)
);

CREATE TABLE vehicle (
    vehicle_id            INT AUTO_INCREMENT PRIMARY KEY,
    driver_id             INT NOT NULL,
    plate_no              VARCHAR(15) NOT NULL UNIQUE,
    model                 VARCHAR(60) NOT NULL,
    battery_capacity_kwh  DECIMAL(6,2) NOT NULL,
    connector_type_id     INT NOT NULL,
    CONSTRAINT fk_vehicle_driver
        FOREIGN KEY (driver_id) REFERENCES driver(driver_id) ON DELETE CASCADE,
    CONSTRAINT fk_vehicle_connector
        FOREIGN KEY (connector_type_id) REFERENCES connector_type(connector_type_id)
);

-- CHARGER is modelled as a weak entity of STATION:
-- it is identified in the real world by its parent station + a local number
-- (uq_charger). A surrogate key (charger_id) is used as the practical PK.
CREATE TABLE charger (
    charger_id         INT AUTO_INCREMENT PRIMARY KEY,
    station_id         INT NOT NULL,
    connector_type_id  INT NOT NULL,
    tariff_id          INT NOT NULL,
    charger_no         INT NOT NULL,                 -- local number within the station
    power_kw           DECIMAL(6,2) NOT NULL,
    status             ENUM('available','charging','maintenance','out_of_service')
                       NOT NULL DEFAULT 'available',
    CONSTRAINT fk_charger_station
        FOREIGN KEY (station_id) REFERENCES station(station_id) ON DELETE CASCADE,
    CONSTRAINT fk_charger_connector
        FOREIGN KEY (connector_type_id) REFERENCES connector_type(connector_type_id),
    CONSTRAINT fk_charger_tariff
        FOREIGN KEY (tariff_id) REFERENCES tariff(tariff_id),
    CONSTRAINT uq_charger UNIQUE (station_id, charger_no)
);

-- Transactional / associative entities --------------------------------
-- Charging session overlap validation is enforced by the application layer.
-- UNIQUE(charger_id, start_time) prevents duplicate start times,
-- but does not prevent overlapping time intervals.

CREATE TABLE charging_session (
    session_id   INT AUTO_INCREMENT PRIMARY KEY,
    charger_id   INT NOT NULL,
    vehicle_id   INT NOT NULL,
    start_time   DATETIME NOT NULL,
    end_time     DATETIME,
    energy_kwh   DECIMAL(7,3) NOT NULL DEFAULT 0.000,
    total_cost   DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT fk_session_charger
        FOREIGN KEY (charger_id) REFERENCES charger(charger_id),
    CONSTRAINT fk_session_vehicle
        FOREIGN KEY (vehicle_id) REFERENCES vehicle(vehicle_id),
    CONSTRAINT chk_session_time CHECK (end_time IS NULL OR end_time >= start_time),
    CONSTRAINT uq_session_slot UNIQUE (charger_id, start_time)
);

-- A session may have at most one payment (0..1 relationship).
-- session_id is UNIQUE to enforce one payment per session.
CREATE TABLE payment (
    payment_id  INT AUTO_INCREMENT PRIMARY KEY,
    session_id  INT NOT NULL UNIQUE,
    amount      DECIMAL(8,2) NOT NULL,
    method      ENUM('credit_card','rfid_card','mobile_app','cash') NOT NULL,
    paid_at     DATETIME NOT NULL,
    status      ENUM('completed','pending','failed') NOT NULL DEFAULT 'completed',
    CONSTRAINT fk_payment_session
        FOREIGN KEY (session_id) REFERENCES charging_session(session_id) ON DELETE CASCADE
);

CREATE TABLE maintenance_task (
    task_id         INT AUTO_INCREMENT PRIMARY KEY,
    charger_id      INT NOT NULL,
    technician_id   INT NOT NULL,
    scheduled_date  DATE NOT NULL,
    completed_date  DATE,
    description     VARCHAR(200),
    status          ENUM('scheduled','in_progress','completed','cancelled')
                    NOT NULL DEFAULT 'scheduled',
    CONSTRAINT fk_task_charger
        FOREIGN KEY (charger_id) REFERENCES charger(charger_id),
    CONSTRAINT fk_task_tech
        FOREIGN KEY (technician_id) REFERENCES technician(technician_id)
);

-- Reservation overlap prevention is enforced by the application layer.
-- The database validates reservation times but does not prevent
-- overlapping reservations on the same charger.

CREATE TABLE reservation (
    reservation_id  INT AUTO_INCREMENT PRIMARY KEY,
    driver_id       INT NOT NULL,
    charger_id      INT NOT NULL,
    reserved_start  DATETIME NOT NULL,
    reserved_end    DATETIME NOT NULL,
    status          ENUM('active','completed','cancelled','confirmed')
                    NOT NULL DEFAULT 'confirmed',
    CONSTRAINT fk_res_driver  FOREIGN KEY (driver_id) REFERENCES driver(driver_id),
    CONSTRAINT fk_res_charger FOREIGN KEY (charger_id) REFERENCES charger(charger_id),
    CONSTRAINT chk_res_time CHECK (reserved_end > reserved_start)
);

-- =====================================================================
--  SECTION 2 : SAMPLE DATA
-- =====================================================================

INSERT INTO region (region_name) VALUES
('Muntenia'), ('Transilvania'), ('Moldova'), ('Banat');

INSERT INTO city (city_name, region_id) VALUES
('Bucharest', 1),      -- 1
('Ploiesti', 1),       -- 2
('Cluj-Napoca', 2),    -- 3
('Brasov', 2),         -- 4
('Iasi', 3),           -- 5
('Timisoara', 4);      -- 6

INSERT INTO membership_plan (plan_name, monthly_fee, discount_pct) VALUES
('Pay-As-You-Go', 0.00, 0.00),    -- 1
('Plus',          9.99, 10.00),   -- 2
('Pro',          19.99, 20.00);   -- 3

INSERT INTO connector_type (type_name, max_power_kw) VALUES
('Type2',   22.00),    -- 1
('CCS2',   350.00),    -- 2
('CHAdeMO',100.00);    -- 3

INSERT INTO tariff (tariff_name, rate_per_kwh, rate_per_minute) VALUES
('Standard AC', 0.450, 0.000),    -- 1
('Fast DC',     0.650, 0.050),    -- 2
('Ultra DC',    0.850, 0.080);    -- 3

INSERT INTO technician (full_name, phone, hired_date) VALUES
('Andrei Popescu',  '+40711000001', '2022-03-01'),  -- 1
('Maria Ionescu',   '+40711000002', '2023-06-15'),  -- 2
('Stefan Dumitru',  '+40711000003', '2024-01-10');  -- 3

INSERT INTO driver (full_name, email, phone, city_id, plan_id, registered_at) VALUES
('Mohammad Hadid',  'mohammad.h@example.com', '+40720000001', 1, 3, '2024-02-12'), -- 1
('Elena Marin',     'elena.m@example.com',    '+40720000002', 1, 2, '2024-04-03'), -- 2
('Radu Stan',       'radu.s@example.com',     '+40720000003', 3, 1, '2024-05-20'), -- 3
('Ana Georgescu',   'ana.g@example.com',      '+40720000004', 4, 2, '2024-07-01'), -- 4
('Victor Pop',      'victor.p@example.com',   '+40720000005', 5, 1, '2025-01-15'), -- 5
('Diana Toma',      'diana.t@example.com',    '+40720000006', 6, 3, '2025-03-09'); -- 6 (no vehicle)

INSERT INTO station (station_name, address, city_id, latitude, longitude, opened_date) VALUES
('VoltLink Unirii',   'Bd. Unirii 12, Bucharest',   1, 44.426800, 26.103200, '2023-09-01'), -- 1
('VoltLink AFI',      'Bd. Vasile Milea 4, Bucharest',1,44.430500, 26.054800, '2024-01-20'), -- 2
('VoltLink Cluj Mall','Str. Alexandru Vaida 53b',   3, 46.770400, 23.591600, '2024-03-10'), -- 3
('VoltLink Brasov',   'Calea Bucuresti 90, Brasov',  4, 45.640600, 25.621200, '2024-06-05'), -- 4
('VoltLink Iasi',     'Sos. Pacurari 121, Iasi',     5, 47.165500, 27.555800, '2025-02-01'); -- 5

INSERT INTO vehicle (driver_id, plate_no, model, battery_capacity_kwh, connector_type_id) VALUES
(1, 'B-101-EV',  'Tesla Model 3',      60.00, 2),  -- 1
(1, 'B-102-EV',  'Dacia Spring',       26.80, 1),  -- 2 (Mohammad has 2 vehicles)
(2, 'B-220-EV',  'Volkswagen ID.4',    77.00, 2),  -- 3
(3, 'CJ-330-EV', 'Nissan Leaf',        40.00, 3),  -- 4
(4, 'BV-440-EV', 'Hyundai Kona',       64.00, 2),  -- 5
(5, 'IS-550-EV', 'Renault Zoe',        52.00, 1);  -- 6
-- Driver 6 (Diana) intentionally owns no vehicle (for LEFT JOIN demo).

INSERT INTO charger (station_id, connector_type_id, tariff_id, charger_no, power_kw, status) VALUES
(1, 2, 2, 1, 150.00, 'available'),       -- 1  Unirii / CCS2 / Fast
(1, 1, 1, 2,  22.00, 'available'),       -- 2  Unirii / Type2 / Standard
(2, 2, 3, 1, 350.00, 'charging'),          -- 3  AFI / CCS2 / Ultra
(2, 3, 2, 2, 100.00, 'available'),       -- 4  AFI / CHAdeMO / Fast
(3, 2, 2, 1, 150.00, 'available'),       -- 5  Cluj / CCS2 / Fast
(3, 1, 1, 2,  22.00, 'out_of_service'),  -- 6  Cluj / Type2 / Standard
(4, 2, 3, 1, 350.00, 'available'),       -- 7  Brasov / CCS2 / Ultra
(5, 1, 1, 1,  22.00, 'available');       -- 8  Iasi / Type2 / Standard

INSERT INTO charging_session (charger_id, vehicle_id, start_time, end_time, energy_kwh, total_cost) VALUES
(1, 1, '2025-05-01 08:00:00', '2025-05-01 08:35:00', 42.500, 27.63),
(2, 2, '2025-05-01 09:10:00', '2025-05-01 11:10:00', 18.200,  8.19),
(3, 3, '2025-05-02 18:00:00', '2025-05-02 18:25:00', 55.000, 46.75),
(5, 4, '2025-05-03 12:00:00', '2025-05-03 12:50:00', 33.000, 21.45),
(1, 1, '2025-05-05 07:30:00', '2025-05-05 08:05:00', 40.000, 26.00),
(7, 5, '2025-05-06 14:00:00', '2025-05-06 14:20:00', 48.000, 40.80),
(8, 6, '2025-05-07 19:00:00', '2025-05-07 21:00:00', 22.000,  9.90),
(3, 4, '2025-05-08 10:00:00', NULL,                   0.000,  0.00); -- ongoing session

INSERT INTO payment (session_id, amount, method, paid_at, status) VALUES
(1, 27.63, 'mobile_app',    '2025-05-01 08:36:00', 'completed'),
(2,  8.19, 'credit_card',   '2025-05-01 11:11:00', 'completed'),
(3, 46.75, 'cash', '2025-05-02 18:26:00', 'completed'),
(4, 21.45, 'mobile_app',    '2025-05-03 12:51:00', 'completed'),
(5, 26.00, 'rfid_card',   '2025-05-05 08:06:00', 'completed'),
(6, 40.80, 'credit_card',   '2025-05-06 14:21:00', 'pending'),
(7,  9.90, 'mobile_app',    '2025-05-07 21:01:00', 'completed');
-- Session 8 is ongoing -> no payment row yet.

INSERT INTO maintenance_task (charger_id, technician_id, scheduled_date, completed_date, description, status) VALUES
(6, 1, '2025-05-04', '2025-05-04', 'Replace faulty Type2 cable',        'completed'),
(3, 2, '2025-05-10', NULL,         'Firmware upgrade for ultra-fast DC', 'in_progress'),
(1, 1, '2025-05-12', NULL,         'Quarterly safety inspection',        'scheduled'),
(7, 3, '2025-05-15', NULL,         'Cooling system check',               'scheduled');

INSERT INTO reservation (driver_id, charger_id, reserved_start, reserved_end, status) VALUES
(1, 1, '2025-05-20 08:00:00', '2025-05-20 09:00:00', 'active'),
(2, 3, '2025-05-20 18:00:00', '2025-05-20 18:45:00', 'active'),
(3, 5, '2025-05-18 12:00:00', '2025-05-18 13:00:00', 'completed'),
(4, 7, '2025-05-19 14:00:00', '2025-05-19 14:30:00', 'cancelled'),
(5, 8, '2025-05-21 19:00:00', '2025-05-21 21:00:00', 'confirmed');

-- =====================================================================
--  SECTION 3 : SQL QUERIES
--  Each query is preceded by what it demonstrates.
-- =====================================================================

-- Q1. Simple filtered SELECT: all chargers that are out of service.
SELECT charger_id, station_id, power_kw, status
FROM charger
WHERE status = 'out_of_service';

-- Q2. INNER JOIN (2 tables): list each vehicle with its owner's name.
SELECT d.full_name AS driver, v.plate_no, v.model
FROM driver d
JOIN vehicle v ON v.driver_id = d.driver_id
ORDER BY d.full_name;

-- Q3. Multi-table JOIN (5 tables): full detail of every charging session.
SELECT cs.session_id,
       d.full_name        AS driver,
       v.model            AS vehicle,
       st.station_name,
       c.charger_no,
       cs.energy_kwh,
       cs.total_cost
FROM charging_session cs
JOIN charger  c  ON c.charger_id  = cs.charger_id
JOIN station  st ON st.station_id = c.station_id
JOIN vehicle  v  ON v.vehicle_id  = cs.vehicle_id
JOIN driver   d  ON d.driver_id   = v.driver_id
ORDER BY cs.session_id;

-- Q4. Aggregation + GROUP BY + HAVING: total energy and revenue per station,
--     keeping only stations that delivered more than 50 kWh.
SELECT st.station_name,
       COUNT(cs.session_id)  AS sessions,
       SUM(cs.energy_kwh)    AS total_kwh,
       SUM(cs.total_cost)    AS revenue
FROM station st
JOIN charger c           ON c.station_id  = st.station_id
JOIN charging_session cs ON cs.charger_id = c.charger_id
WHERE cs.end_time IS NOT NULL  -- only completed sessions
GROUP BY st.station_id, st.station_name
HAVING SUM(cs.energy_kwh) > 50
ORDER BY revenue DESC;

-- Q5. Scalar subquery: sessions whose energy is above the overall average.
SELECT session_id, energy_kwh
FROM charging_session
WHERE energy_kwh > (SELECT AVG(energy_kwh) FROM charging_session)
ORDER BY energy_kwh DESC;

-- Q6. LEFT JOIN to find non-matches: drivers who own no vehicle.
SELECT d.driver_id, d.full_name
FROM driver d
LEFT JOIN vehicle v ON v.driver_id = d.driver_id
WHERE v.vehicle_id IS NULL;

-- Q7. Correlated subquery with EXISTS: chargers that have at least one historical session
--     with a pending payment.
SELECT DISTINCT c.charger_id, st.station_name
FROM charger c
JOIN station st ON st.station_id = c.station_id
WHERE EXISTS (
    SELECT 1
    FROM charging_session cs
    JOIN payment p ON p.session_id = cs.session_id
    WHERE cs.charger_id = c.charger_id
      AND p.status = 'pending'
);

-- Q8. Date/time function + CASE: session duration and a speed label.
SELECT session_id,
       TIMESTAMPDIFF(MINUTE, start_time, end_time) AS minutes,
       energy_kwh,
       CASE
           WHEN end_time IS NULL THEN 'ongoing'
           WHEN TIMESTAMPDIFF(MINUTE, start_time, end_time) <= 30 THEN 'fast'
           ELSE 'slow'
       END AS speed_label
FROM charging_session
ORDER BY session_id;

-- Q9. Aggregation across joined lookup data: revenue grouped by region.
SELECT r.region_name,
       SUM(cs.total_cost) AS region_revenue
FROM region r
JOIN city    ci ON ci.region_id  = r.region_id
JOIN station st ON st.city_id    = ci.city_id
JOIN charger c  ON c.station_id  = st.station_id
JOIN charging_session cs ON cs.charger_id = c.charger_id
WHERE cs.end_time IS NOT NULL  -- only completed sessions
GROUP BY r.region_id, r.region_name
ORDER BY region_revenue DESC;

-- Q10. Subquery with IN: drivers who currently have an active reservation.
SELECT driver_id, full_name
FROM driver
WHERE driver_id IN (
    SELECT driver_id FROM reservation WHERE status = 'active'
);

-- Q11. Window function (MySQL 8+): rank drivers by total money spent.
SELECT d.full_name,
       SUM(cs.total_cost) AS total_spent,
       RANK() OVER (ORDER BY SUM(cs.total_cost) DESC) AS spend_rank
FROM driver d
JOIN vehicle v           ON v.driver_id  = d.driver_id
JOIN charging_session cs ON cs.vehicle_id = v.vehicle_id
GROUP BY d.driver_id, d.full_name;

-- Q12. View: a reusable monthly billing report joining many entities.
CREATE OR REPLACE VIEW v_session_billing AS
SELECT cs.session_id,
       d.full_name      AS driver,
       st.station_name,
       cs.start_time,
       cs.energy_kwh,
       t.tariff_name,
       cs.total_cost,
       p.status         AS payment_status
FROM charging_session cs
JOIN charger c  ON c.charger_id = cs.charger_id
JOIN tariff  t  ON t.tariff_id  = c.tariff_id
JOIN station st ON st.station_id = c.station_id
JOIN vehicle v  ON v.vehicle_id  = cs.vehicle_id
JOIN driver  d  ON d.driver_id   = v.driver_id
LEFT JOIN payment p ON p.session_id = cs.session_id;

-- Use the view:
SELECT * FROM v_session_billing WHERE payment_status = 'pending' OR payment_status IS NULL;

-- Q13. Maintenance workload per technician (open tasks only).
SELECT te.full_name,
       COUNT(*) AS open_tasks
FROM technician te
JOIN maintenance_task mt ON mt.technician_id = te.technician_id
WHERE mt.status IN ('scheduled','in_progress')
GROUP BY te.technician_id, te.full_name
ORDER BY open_tasks DESC;

-- Q14. Utilisation: number of chargers per connector type and their average power.
SELECT ct.type_name,
       COUNT(c.charger_id)   AS charger_count,
       ROUND(AVG(c.power_kw),1) AS avg_power_kw
FROM connector_type ct
LEFT JOIN charger c ON c.connector_type_id = ct.connector_type_id
GROUP BY ct.connector_type_id, ct.type_name
ORDER BY charger_count DESC;

-- =====================================================================
--  END OF FILE
-- =====================================================================