VoltLink — EV Charging Network Database

A relational database system for managing an electric-vehicle charging network, covering drivers, vehicles, charging stations, chargers, charging sessions, payments, reservations, and maintenance operations.

Overview

VoltLink is a database systems project designed around the operational needs of an EV charging network.

The system models the complete charging workflow, from managing customers and vehicles to recording charging sessions and payments, while also supporting charger reservations, maintenance operations, and operational reporting.

The project demonstrates the complete database design process:

• Requirements analysis
• Entity-Relationship modeling
• ER-to-relational mapping
• Normalization to Third Normal Form (3NF)
• Relational schema implementation
• Constraints and referential integrity
• Sample data and SQL queries

System Scope

VoltLink manages three main categories of data:

Reference Data

• Regions and cities
• Membership plans
• Connector types
• Tariffs

Master Data

• Drivers
• Vehicles
• Charging stations
• Chargers
• Technicians

Transactional Data

• Charging sessions
• Payments
• Reservations
• Maintenance tasks

The database supports operations such as recording charging sessions and payments, updating charger status, querying revenue, tracking maintenance workload, and identifying chargers requiring service.

Database Architecture

The conceptual model contains 14 entities, including a weak entity (CHARGER) and several associative entities used to resolve many-to-many relationships.

The main relationships include:

• Drivers → Vehicles
• Stations → Chargers
• Vehicles → Charging Sessions
• Chargers → Charging Sessions
• Drivers → Reservations
• Chargers → Reservations
• Technicians → Maintenance Tasks
• Chargers → Maintenance Tasks

Database Design

Conceptual ER Diagram

The conceptual ER diagram models the main entities, relationships, cardinalities, and business rules independently of the physical database implementation.

Physical Schema

The physical schema shows the implemented MySQL database, including tables, column types, nullability, and foreign-key relationships.

Normalization

The database design was normalized through Third Normal Form (3NF).

The normalization process demonstrates:

1. Identification of a hypothetical unnormalized relation
2. Functional dependencies
3. Conversion to First Normal Form (1NF)
4. Removal of partial dependencies for Second Normal Form (2NF)
5. Removal of transitive dependencies for Third Normal Form (3NF)

The resulting relations satisfy the project requirements while reducing update, insertion, and deletion anomalies.

Database Constraints

The physical schema uses several integrity constraints, including:

• Primary keys
• Foreign keys
• NOT NULL
• UNIQUE
• CHECK
• ENUM
• Referential integrity
• Cascading deletes where appropriate

Examples include:

• Unique driver email addresses
• Unique vehicle plate numbers
• Unique charger numbers within each station
• Valid membership discount percentages
• Valid session and reservation time ranges
• At most one payment per charging session

Reservation and charging-session interval overlap detection is handled at the application layer rather than directly by the database.

Sample Data

The database includes deliberately selected sample data and edge cases for demonstrating different SQL operations.

The dataset contains:

• 4 regions
• 6 cities
• 3 membership plans
• 3 connector types
• 3 tariffs
• 6 drivers
• 5 stations
• 8 chargers
• 8 charging sessions
• 7 payments
• 4 maintenance tasks
• 5 reservations

The sample data also includes cases such as a driver with no vehicle, an ongoing charging session, and a pending payment.

SQL Demonstrations

The voltlink.sql script contains the database schema, sample data, and 14 demonstration queries covering a range of SQL techniques.

Examples include:

• Filtering with WHERE
• Single and multi-table JOIN
• GROUP BY and HAVING
• Scalar subqueries
• Correlated subqueries with EXISTS
• LEFT JOIN
• CASE
• TIMESTAMPDIFF
• Multi-level aggregation
• IN subqueries
• Window functions with RANK() OVER
• CREATE VIEW
• Aggregate functions such as SUM, COUNT, and AVG

Project Structure

VoltLink-Database-System/
├── conceptual-erd.png
├── physical-schema.png
├── voltlink_erd.dbml
├── voltlink.sql
├── docs/ VoltLink_Database_Report.pdf
└── README.md

Technologies & Tools

• MySQL 8.0
• MySQL Workbench
• SQL
• dbdiagram.io
• Draw.io

Running the Project

1. Install MySQL 8.0.
2. Open voltlink.sql in MySQL Workbench.
3. Execute the script to create the database and populate the sample data.
4. Run the included demonstration queries.

The ERD can be regenerated from voltlink_erd.dbml using dbdiagram.io.

Project Documentation

The accompanying report documents:

Full Database Report (PDF)

• System requirements
• Business rules
• ERD and conceptual design
• Relational schema
• Normalization to 3NF
• Functional dependencies
• SQL implementation
• Sample data
• Demonstration queries

Academic Context

Database Systems — Theoretical Project

Computer Engineering
Damascus University