A fully designed and implemented relational database system for a drone-based grocery delivery service — modelling orders, delivery routes, drone fleet management, and inventory from EER diagram through to optimised MySQL implementation.
<img src="drone-01.png" alt="Drone Delivery EER Diagram">drone-02.pngdrone-03.pngdrone-04.pngThis project designed and implemented a complete relational database system for a hypothetical drone-based grocery delivery service. The scope covered the full design pipeline: starting with requirements analysis, then modelling the domain as an Enhanced Entity-Relationship (EER) diagram, and finally implementing and optimising the database in MySQL.
The domain required modelling complex relationships between customers, orders, drone fleet status, delivery routes, and store inventory — with constraints that reflect real operational requirements like drone capacity, route uniqueness, and inventory consistency.
EER Diagram DesignDesigned a full Enhanced Entity-Relationship diagram capturing all entities, attributes, and relationships in the delivery system — including specialisation hierarchies for drone types and weak entities for order line items. This served as the authoritative blueprint for the database implementation.
System FlowchartsCreated system flowcharts documenting the data flow through the delivery process — from order placement through drone assignment, route planning, and delivery confirmation — giving a clear picture of how the database supports operational workflows.
MySQL ImplementationTranslated the EER diagram into a normalised relational schema and implemented it in MySQL, writing DDL for all tables, constraints, and relationships. Applied normalisation principles to eliminate redundancy and ensure data integrity.
Query OptimisationWrote and optimised complex SQL queries for key operational reports — available drone capacity, order status by route, and inventory levels — ensuring efficient performance through appropriate indexing and join strategies.