Create an entity related diagram and its database design for the hld diagram shown below using its data stores shown.

case study:

The Deliver It Right Company provides worldwide delivery for lightning fast shipping at relatively low prices via air and land networks for its retail customers. Deliver It Right has recently launched an app where retail stores that wish to ship packages can register, arrange for pick-up and pay for shipment via the app. This app is part of the Customer Support System (CSS), which runs on the Delivery Service Information System.

First customers should sign up for the service by selecting the Registration option on the app. A registration form appears requesting the Company Name, Contact Name, Company Phone Number, Company Email Address, Company Mailing Address and Pick-Up Address. It is assumed there is a single pick-up address instead of multiple possibilities. When the customer submits the completed form, it is sent to the companys CSS for the creation of a new record in the Customer Database. The Database automatically creates a username and password, which the CSS accesses and emails to the customer.

When retail stores have packages that require shipment, the app requests login information and the customer provides their username and password. The CSS checks the entered username and password against its records to verify the customer. The Quote Shipment option is selected and the customer enters the Number of packages, Size of package, Weight of package, Contents of package, Destination Address and any Handling Instructions (fragile, must remain upright, etc.). The information is sent to the CSS, which creates a new record in the Delivery File where a Barcode is created and the Status is set to Inactive. The CSS transfers the inactive records to the Accounting System where an estimated cost is calculated. The app then displays the estimated cost for further action by the customer. A new quote should be selected for packages going to a different destination.

The customer then selects the Quote Accepted option and the CSS sets the records Status to Initiate in the Delivery File. The CSS sends an alert to the Transport Division, which dispatches a driver to pick-up the packages. The driver confirms the size and weight of the packages, and differences are entered on the drivers wireless, handheld device connected to the Delivery Service Information System. Any difference updates the Delivery record and the information is sent to the Accounting System, which sends an invoice (listing the sub total, additional handling cost and date) to the CSS. The invoice is stored in the Invoice Database. The customer enters their payment information and once approved, the packages are collected. The driver then updates the deliveries record Status to Active, prints the Barcode and sticks it on each package.

The driver takes the packages to the Package Center. The Package Center sorts packages and sends to the Transport Division for delivery. Once Shipment is arranged, the Transport Division updates the CSS of the shipment of the deliveries. The CSS creates a record in the Shipment Log, storing information on the mode of transport, Departure Date and Time and Arrival Date and Time. Note that packages from the same quoted delivery can be placed on different shipments depending on their handling instructions.

Upon delivery of the packages at the destination, the driver confirms the delivery to the Transport Division. The Transport Division sends an alert to the CSS, where the deliveries record is updated to Closed status and the customer receives an update via the app.

Reg istration o pton 1.0 D 1 Login query Login respons Login request D2 D olivery File Arran ging D elive ry Quote Shipm on Package update update D3 Invoice D atab aso 3.0 P ackage update Picking up Packages Accounting Actve status Quote Accopted D2 Delivery File C us tom er 4.0 Pick-up alert Ship ping Shipm ent Log S hipm ont Transport Division Shipm ont information Delivery update Reg istration o pton 1.0 D 1 Login query Login respons Login request D2 D olivery File Arran ging D elive ry Quote Shipm on Package update update D3 Invoice D atab aso 3.0 P ackage update Picking up Packages Accounting Actve status Quote Accopted D2 Delivery File C us tom er 4.0 Pick-up alert Ship ping Shipm ent Log S hipm ont Transport Division Shipm ont information Delivery update