Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

You are tasked with designing and administering the database for a postal company. Given the database specification below, your job is to turn the specification

You are tasked with designing and administering the database for a postal company. Given the database specification below, your job is to turn the specification into an ER diagram. You may use diagrams.net (also known as draw.io) or dia (see web site for dia) to draw your ER diagram. You may use any other diagramming tool as long as you are delivering the correct notation. Handwritten diagrams are NOT accepted. Your notation must be consistent with the notation used in our lecture notes. You can use all constructs and notations discussed in our lecture notes and nothing else (i.e., even notations used in the textbook but not in our lectures cannot be used). Your ER diagram should capture all the information and constraints in the specification, but at the same time be minimal, meaning redundant entities, relationships, attributes and constraints should be avoided.If you are making any assumptions in your modeling or mapping, state them clearly in your README.MD; note that your assumptions cannot violate the specification given here and any possible clarification posted later on top of this page or the course forums. Database SpecificationThe postal company has several facilities, where each facility has a unique ID and the system should record the address and city for each facility. Each facility must be managed by an employee.The company offers regular and expedited mailing service for any parcel. When a parcel is dropped out, the system will generate a tracking number dedicated to that parcel and keep track of the time when the package is dropped, from address, to address, weight, and a list of goods in that parcel. During the transportation process, each parcel may enter and exit several distinct facilities. When a parcel enters a facility, the time and next destination facility must be recorded. When a parcel exits a facility, the time and transportation type (e.g., air, ground, etc.) must be indicated. Regular parcels include a barcode for internal use and in expedited parcels, the sender may declare customs clearance forms which include, the total value of the goods, the destination country, the date, and a distinct reference number that can include letters and digits.An employee is identified by a unique ID and the system must store their name, address, and the facility they work in. Each employee has a role (e.g., driver, warehouse supervisor, packaging staff, etc.) and a supervisor. The employees must be employed in one facility only.When a parcel is out for delivery, the system records the delivery time, the employee responsible for delivery, and the status (e.g., successful or failed) of that attempt, so, if a delivery fails, they may make another attempt. Also, at the delivery time, the feedback may be collected, which is formatted as a number for their satisfaction (shown with 15 stars).

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Databases Illuminated

Authors: Catherine Ricardo

2nd Edition

1449606008, 978-1449606008

More Books

Students also viewed these Databases questions