Student: Aryan Sinha Roll no.: 2025UGCS070
A MySQL database for a package delivery company, like FedEx or DHL. It covers the two areas according to the the assignment:
- Package handling: customers, service types, packages, customs declarations, and the tracking history of every package across warehouses, trucks and planes.
- Billing: monthly account billing, credit-card payments, and prepaid returns.
| File | Contents |
|---|---|
README.md |
This file |
ER_Diagram.png |
E-R diagram |
Relational_Model.md / Relational_Model.png |
Tables, keys, foreign keys, normalization, constraints, indexes |
schema.sql |
Creates the database package_delivery_db: 6 tables with keys, constraints and indexes |
data.sql |
Small sample data set |
queries.sql |
The client's queries (1–6) plus 2 tracking queries |
From a terminal in this folder:
mysql -u root -p < schema.sql
mysql -u root -p < data.sql
mysql -u root -p --table < queries.sqlIn MySQL Workbench: open and run schema.sql, then data.sql, then queries.sql.
| Entity | Key | Notes |
|---|---|---|
| customer | customer_id | Contract customers have an account number and are billed monthly. Occasional customers pay by credit card. |
| service_type | service_id | Package type (envelope / small box / large box), speed (overnight / second day / ground / international), weight limit and rates. |
| package | tracking_no | Weight, hazardous-material class, ship time, promised delivery time, payment, charge, recipient and destination. |
| location | location_id | A warehouse, truck or plane. Trucks use their number as the id, e.g. 1721. |
| customs_declaration | (weak, owner package) | Contents and declared value of an international package. |
| tracking_event | (weak, owner package; discriminator event_time) | Every scan of a package: when, what happened, where, and who signed. |
| Relationship | Between | Cardinality |
|---|---|---|
| ships | customer – package | 1 : N. Each package has one sender. |
| pays_for | customer – package | 1 : N. Each package has one payer, usually the sender. For a prepaid return, the shop pays. |
| uses | service_type – package | 1 : N |
| declared_in | package – customs_declaration | 1 : 0..1 (identifying) |
| has_event | package – tracking_event | 1 : N (identifying) |
| scanned_at | location – tracking_event | 1 : N |
How tracking works: after each event, the package is at that event's location (a warehouse, truck or plane) until its next event. A 'Delivered' event ends its journey. To find where a package was at any past time, look up its latest event at or before that time. The same lookup gives what was on a truck, or in a warehouse, at a given time.
Assumptions:
- Charge = base rate + rate per kg × weight, plus ₹500 for hazardous material. It is stored when the package is shipped.
- A package is late if it was delivered after
promised_by, or if it is still undelivered after that time. - The recipient does not have to be a customer, so the recipient's details are stored with the package.
- Bills cover Contract customers: everything charged to their account last month, including prepaid returns they paid for. Credit-card shipments are paid at the time of shipping, so they don't appear on a bill.
- Truck routing and staff are left out, as the assignment says.
The sample data reflects the company on 15th Nov 2026. Truck 1721 crashed on 12th Nov 2026
at 14:30.
| # | Question | Result on the sample data |
|---|---|---|
| 1a | Customers with a package on truck 1721 at the crash | Aryan Sinha, Kumar Exports, StyleKart Fashion |
| 1b | Recipients with a package on truck 1721 at the crash | Delhi Crafts Bazaar, Meera Iyer, Sachin Paliwal |
| 1c | Last successful delivery by truck 1721 before the crash | PD100113, delivered 12:40 to Sanjay Verma |
| 2 | Customer who shipped the most packages in the past year | Aryan Sinha (4 packages) |
| 3 | Customer who spent the most on shipping in the past year | Aryan Sinha (₹4,050) |
| 4 | Street with the most customers | Linking Road, Mumbai (3 customers) |
| 5 | Packages not delivered within the promised time | 5: two delivered late, three lost on truck 1721 |
| 6a | Simple bill: customer, address, amount owed | 5 bills for October 2026 |
| 6b | Bill by type of service | the same 5 bills, with a TOTAL line per customer |
| 6c | Itemized bill, one line per shipment | the same 5 bills, with a TOTAL line per customer |
| 7 | Tracking history of package PD100111 | 5 events |
| 8 | Where every undelivered package is now | 4 packages |
| Table | Rows |
|---|---|
| customer | 8 (5 contract, 3 occasional) |
| service_type | 6 |
| location | 12 (4 warehouses, 4 planes, 4 trucks) |
| package | 18 (3 international, 1 hazardous, 1 prepaid return) |
| customs_declaration | 3 |
| tracking_event | 90 |
The company, customers and addresses are fictional. E-mail addresses use reserved example domains.