Mini project: courier delivery database

60 min · Intermediate

A courier delivery app is a tidy set of relationships: customers order from shops, orders hold products, and couriers deliver them. Get the tables and keys right, and every business question becomes a few joins.

What you'll build

Requirement from a start-up in Bengaluru: "Shops have a name, an area and a city, sell in one or more categories, and each has its own catalogue of products with a price and a fragile or not-fragile mark. Customers sign up with a phone number. A customer places an order with one shop, for one or more products and quantities, to a delivery address. A courier (with a phone and a vehicle number) is assigned once the shop starts packing, and an order moves through placed, packing, out for delivery, delivered or cancelled."

You turn this into an ER diagram, a normalised set of MySQL tables with primary keys, foreign keys, UNIQUE and CHECK constraints, sample data, and five queries the business asks every day. Everything lives in one script, queries.sql, that can be run again at any time. Sample data to load (the output is compared with the sample run): customers Aarti, Bilal, Chirag, Deepa (phones 9811000001 to 9811000004, city Bengaluru); shops Gadget Hub (Jayanagar), Campus Books (Koramangala), Style Street (Indiranagar), all in Bengaluru; categories Electronics, Books, Stationery, Clothing; couriers Sunil, Kavya, Ramesh (phones 9900000001 to 9900000003, vehicles KA01AB1001 to KA01AB1003); the shop-category links and products, orders and order lines exactly as in the sample run, so every result table matches.

Sample run (the five business queries)

+--------------+--------+---------+
| shop         | orders | revenue |
+--------------+--------+---------+
| Campus Books |      2 | 1060.00 |
| Style Street |      1 |  420.00 |
| Gadget Hub   |      1 |  260.00 |
+--------------+--------+---------+
+--------------------------+--------------+-------+
| product                  | shop         | units |
+--------------------------+--------------+-------+
| USB Cable                | Gadget Hub   |     4 |
| Cable Tie Pack           | Gadget Hub   |     3 |
| Data Structures Textbook | Campus Books |     3 |
+--------------------------+--------------+-------+
+---------+------------+
| courier | deliveries |
+---------+------------+
| Kavya   |          2 |
| Sunil   |          2 |
| Ramesh  |          0 |
+---------+------------+
+----------+----------+--------------+---------+
| order_id | customer | shop         | status  |
+----------+----------+--------------+---------+
|        6 | Deepa    | Campus Books | PACKING |
|        7 | Chirag   | Gadget Hub   | PLACED  |
+----------+----------+--------------+---------+
+-------------+-------+
| category    | shops |
+-------------+-------+
| Stationery  |     2 |
| Books       |     1 |
| Clothing    |     1 |
| Electronics |     1 |
+-------------+-------+

Requirements

Your program should do all of these.

The ER diagram shows every entity, every relationship and its crow's foot marks, and matches the script. Every table has a primary key; phone numbers, vehicle numbers and product names per shop are UNIQUE.
Categories are stored in their own table with a join table, never as a list in a cell. Orders link to one customer and one shop (required) and to one courier (optional until assigned).
Order items store quantity and the unit_price paid; a CHECK rejects a product price of 0 or less and an order-item quantity of 0 or less (these are the only two CHECK constraints). The script drops and recreates its tables, loads the sample data and runs five business queries with ORDER BY.
Tables and columns, in this order (all columns are NOT NULL except dl_orders.courier_id): dl_customers (customer_id INT AUTO_INCREMENT PK, name VARCHAR(40), phone CHAR(10) UNIQUE, city VARCHAR(30)); dl_shops (shop_id INT AUTO_INCREMENT PK, name VARCHAR(40), area VARCHAR(30), city VARCHAR(30), UNIQUE (name, area)); dl_categories (category_id INT AUTO_INCREMENT PK, name VARCHAR(30) UNIQUE); dl_shop_categories (shop_id INT, category_id INT, PK of both).
dl_products (product_id INT AUTO_INCREMENT PK, shop_id INT FK, name VARCHAR(40), price DECIMAL(8,2), is_fragile BOOLEAN, UNIQUE (shop_id, name)); dl_couriers (courier_id INT AUTO_INCREMENT PK, name VARCHAR(40), phone CHAR(10) UNIQUE, vehicle_no VARCHAR(12) UNIQUE).
dl_orders (order_id INT AUTO_INCREMENT PK, customer_id INT FK, shop_id INT FK, courier_id INT FK NULL, delivery_address VARCHAR(120), status ENUM('PLACED', 'PACKING', 'OUT_FOR_DELIVERY', 'DELIVERED', 'CANCELLED') DEFAULT 'PLACED', placed_at DATETIME); dl_order_items (order_id INT FK, product_id INT FK, quantity TINYINT, unit_price DECIMAL(8,2), PK of order_id and product_id).
Query 1, delivered orders only: shop, orders, revenue, highest revenue first, ties by shop. Query 2, orders that are not cancelled, top 3: product, shop, units, most units first, ties by product. Query 3: courier, deliveries (delivered orders, couriers with none included), most first, ties by courier.
Query 4, orders with no courier yet and status PLACED or PACKING: order_id, customer, shop, status, oldest placed_at first, ties by order id. Query 5: category, shops, most shops first, ties by category name.

Build it step by step in the Build tab (5 milestones, each ends with a program that runs). Commit and push after each one: MentorNxt checks every push and shows the result in the Submit tab.

What's next

You finished the preview of Database Design & Normalisation

Python Programming has 21 sections, 110 modules and 609 lessons. An account opens every lesson and saves your progress.

Create account I already have an account

Next step

Start building

5 milestones

What you'll build

Sample run (the five business queries)

+--------------+--------+---------+
| shop         | orders | revenue |
+--------------+--------+---------+
| Campus Books |      2 | 1060.00 |
| Style Street |      1 |  420.00 |
| Gadget Hub   |      1 |  260.00 |
+--------------+--------+---------+
+--------------------------+--------------+-------+
| product                  | shop         | units |
+--------------------------+--------------+-------+
| USB Cable                | Gadget Hub   |     4 |
| Cable Tie Pack           | Gadget Hub   |     3 |
| Data Structures Textbook | Campus Books |     3 |
+--------------------------+--------------+-------+
+---------+------------+
| courier | deliveries |
+---------+------------+
| Kavya   |          2 |
| Sunil   |          2 |
| Ramesh  |          0 |
+---------+------------+
+----------+----------+--------------+---------+
| order_id | customer | shop         | status  |
+----------+----------+--------------+---------+
|        6 | Deepa    | Campus Books | PACKING |
|        7 | Chirag   | Gadget Hub   | PLACED  |
+----------+----------+--------------+---------+
+-------------+-------+
| category    | shops |
+-------------+-------+
| Stationery  |     2 |
| Books       |     1 |
| Clothing    |     1 |
| Electronics |     1 |
+-------------+-------+

Requirements

Your program should do all of these.

The ER diagram shows every entity, every relationship and its crow's foot marks, and matches the script. Every table has a primary key; phone numbers, vehicle numbers and product names per shop are UNIQUE.

Categories are stored in their own table with a join table, never as a list in a cell. Orders link to one customer and one shop (required) and to one courier (optional until assigned).

Order items store quantity and the unit_price paid; a CHECK rejects a product price of 0 or less and an order-item quantity of 0 or less (these are the only two CHECK constraints). The script drops and recreates its tables, loads the sample data and runs five business queries with ORDER BY.

Tables and columns, in this order (all columns are NOT NULL except dl_orders.courier_id): dl_customers (customer_id INT AUTO_INCREMENT PK, name VARCHAR(40), phone CHAR(10) UNIQUE, city VARCHAR(30)); dl_shops (shop_id INT AUTO_INCREMENT PK, name VARCHAR(40), area VARCHAR(30), city VARCHAR(30), UNIQUE (name, area)); dl_categories (category_id INT AUTO_INCREMENT PK, name VARCHAR(30) UNIQUE); dl_shop_categories (shop_id INT, category_id INT, PK of both).

dl_products (product_id INT AUTO_INCREMENT PK, shop_id INT FK, name VARCHAR(40), price DECIMAL(8,2), is_fragile BOOLEAN, UNIQUE (shop_id, name)); dl_couriers (courier_id INT AUTO_INCREMENT PK, name VARCHAR(40), phone CHAR(10) UNIQUE, vehicle_no VARCHAR(12) UNIQUE).

dl_orders (order_id INT AUTO_INCREMENT PK, customer_id INT FK, shop_id INT FK, courier_id INT FK NULL, delivery_address VARCHAR(120), status ENUM('PLACED', 'PACKING', 'OUT_FOR_DELIVERY', 'DELIVERED', 'CANCELLED') DEFAULT 'PLACED', placed_at DATETIME); dl_order_items (order_id INT FK, product_id INT FK, quantity TINYINT, unit_price DECIMAL(8,2), PK of order_id and product_id).

Query 1, delivered orders only: shop, orders, revenue, highest revenue first, ties by shop. Query 2, orders that are not cancelled, top 3: product, shop, units, most units first, ties by product. Query 3: courier, deliveries (delivered orders, couriers with none included), most first, ties by courier.

Query 4, orders with no courier yet and status PLACED or PACKING: order_id, customer, shop, status, oldest placed_at first, ties by order id. Query 5: category, shops, most shops first, ties by category name.