Question

ABC Inc is a package delivery company that has branches all over Canada. The
Sender (person who is sending a package) can drop a package to any of the
branches and pay the applicable fee and taxes. ABC staff members sort and forward
the packages to an office close to the destination addressees. Each branch office
has a manager along with other staff members. The manager creates a weekly
schedule for the staff members for the hours they are supposed to work in that
branch office. When a package arrives at the destination branch, the staff members
hand over this package to one of their couriers (ABC employee who delivers the
packages). Each courier is assigned to a delivery truck and given a route for
delivering packages. The courier delivers the package to Receiver’s address (person
who receivers the package) and takes his or her signatures which completes the
package delivery process. Please design and create a database to store important
data about ABC Company’s business operations.

a) Please create a Conceptual Data Model (may use Draw.io or any other
similar software), containing entities and their relationships as they exist in
the problem domain (including any M:M relationships)

b) Please create a Physical (Logical) Data Model using MySQL Workbench®
based on your conceptual model. Create a new schema named
Schema100123456 (replace 100123456 with your own student Id) and then
create a physical EER model in it, containing fully normalized tables with
appropriate columns and relationships among these tables (resolved as 1:M
only). Assign proper data types to columns and add appropriate keys &
constraints. Make sure that all relations are in 3NF

c) Now forward engineer this EER model to create tables and relationships and
INSERT 10-15 records per table.


d) Create the following Five queries (create and submit a separate script file for
these queries as explained later in this document- Please use intuitive
column names for queries. Add comments at the beginning of each query
with the Query No and briefly explain what this query achieves)

1. Create a query that returns Senders full names and addresses,
receiver’s full names and addresses, no of packages delivered and the
names of the couriers who delivered these packages. (Five results
minimum).

2. Create a query that lists all packages delivered by each courier during
the last week. (Five results minimum).

3. Create a query that returns the total number of packages received by
each branch during the last month and the total fee amount received
by that branch. (Five results minimum).

4. Create a query that would return the full names of all employees and
the hours they worked during a particular week for any of the
branches of ABC Inc. (Five results minimum).

5. During the holiday season, ABC company wants to send holiday
greeting cards to all people involved with ABC (clients, employees,
couriers) etc. so please create a query that returns a comprehensive
list consisting of Full names and addresses of every one in order to
create mailing labels.

Answer 1

Let’s follow below steps to create the EERD for ABC Company:

Step 1: List entities and sub entities:

• Branch: The branch where a package can be dropped to be sent
• Person: Entity that is of below types:
•                 Sender: Person who sends the package
•                 Receiver: Person who receives the package
• StaffMember
•                 Manager: Manager staff member
•                 CourierStaff: The staff who delivers courier
• Package: The actual package to be delivered from Sender to receiver by ABC staff

Step 2: List relationship between the entities:

1. Sender sends the package at Branch

2. Branch has a Manager

3. Manager creates weekly schedule for StaffMembers

4. A package has one sender, one reciver, one sending branch, one receiving branch and a courier staff.

Step 3: Assign attributes to entities:

**Primary keys are bold and foreign key has * before attribute name

1. Person(person_id, name, contact_number)

2. Sender(*person_id, sender_address)

3. Receiver(*person_id, receiving_address, isHome, isOffice, receiving_timing)

4. StaffMember(staff_id, name, contact_number, address, salary, position, branch): branch for a manager is where he is managing it and for CourierStaff is where he is working

5. Manager(*staff_id, mgr_start_date)

6. CourierStaff(*staff_id, driving_lic_no)

7. Package(pack_id, *sender_id, *receiver_id, sent_date, expected_delivery_date, deliver_date, weight, courier_staff_id, *sending_branch, *receing_branch)

8. StaffSchedule(*manager_id, *staff_id, week_start_date, work_start_time, work_end_time)

Step 4: create EERD in MYSQL Workbench:

____________________________________________________________________

Please up vote this answer. Your up vote is so valuable to me. Thank you so much.

I worked a lot to write a perfect answer.