This project will be done in a group of a maximum of THREE (3) students.
Consider the following set of requirements for a worldwide package delivery database that is used to keep track of couriers who ship parcels and customers who receive parcels.
Each parcel must be identifiable and trackable, so the database must be able to store the parcel tracking number, and its history of locations. The database must also record the weight of the parcel, and the name, email and phone number of sender. Each sender sends one or more parcels.
A courier must deliver at least 50 parcels a day. Each parcel is delivered by one and only one courier. Each courier has unique employee id, name, salary, position, phone numbers, address and email.
Each parcel is shipped to a recipient. The sender must provide complete and accurate recipient information to the courier company that includes recipient name, phone number, and address. The complete address must include city, state, and postcode.
A recipient may receive one or more parcels at the same time. The time and date of the delivery, and the name of the recipient will be recorded when the parcel is delivered to the recipient successfully.
i. Draw the ER diagram. Identify the relationships between entities. You can make logical assumptions with concrete justifications.
ii. Design the database based on the information given and your own creativity. Identify the suitable data types, primary key(s) and foreign key(s) for tables created based on the case study above. Screenshot your database structure and all relevant SQL queries.
iii. Insert sample data into the tables in package delivery database.
iv. Create TWO inner join queries to retrieve data from multiple tables