Instructions:

Based on the scenario presented below about Express Logistics (EL) and the

attached data file (which is primarily intended to supplement the information

provided in the scenario), do the following in Lucidchart:

(1) Create a conceptual model that includes the correct PKs (50 pts);

(2) Transform your conceptual model to a normalized relational/logical model in

3NF (where possible) that is complete with all necessary keys and constraint

notations (50pts)

Note 1: Please affix a label to each diagram to make it easier to determine which

is the conceptual model and which is the logical model

Note 2: Please ensure that each diagram is restricted to a single page and that

all text and symbols are clearly readable. Think professionalism first.

Scenario:

Express Logistics (EL) is a 3PL that works closely with its multitude of clients to

ensure on-time delivery of customized, made-to-order goods, while providing a

high level of end-to-end visibility. As a nearly full service 3PL, EL specializes in

warehousing, transportation, shipping, and receiving. EL owns several

warehouses in which it stores many of the components one of its primary clients,

BradSong, Inc. (BSI), needs to manufacture custom products for its customers.

BSI is a direct to consumer company and does not deal with retailers or

wholesalers. Once BSI receives a customer order, it converts it to a sales order

and passes the sales order along to EL to expedite the shipping process. EL

typically receives notice 48-hours prior to the order(s) being ready for shipment.

This typically leaves EL 24 hours to fulfill the order, as BSI has a standing policy

to fulfill all orders within 72 hours of receipt.

As part of the sales order data, EL receives the following information from BSI:

sales order #, customer ID, order date, estimated pick up date, and customer

promised date (estimated delivery date). EL supplements this information with

the date it receives the sales order from BSI. Although BSI strips all product

related data from the sales order before transmitting it to EL, EL uses some of

the sales order data to retrieve vital customer information (i.e., customer name,

delivery address, special delivery, and handling instructions), which is stored

elsewhere, that would have a bearing on its fulfillment activities.

Although EL has limited direct contact with the customer, it maintains a real-time

copy of BSIs customer list (customer ID, first and last name, street address, city,

state, and zip code). EL also maintains impeccable records of its shipments

including a unique shipment ID, service type, number of packages loaded onto

the vehicle, etc. All details are required before a shipment can be released.

Delivery confirmations for all packages are extremely important and necessary to

facilitate invoicing, especially since EL can only invoice its clients for deliveries

made, not merely goods shipped. EL invoices its clients monthly, so all client

deliveries made in a given month are consolidated into a single invoice for each

client. Given that BSI, and ELs other clients, incentivizes repeat business, it is

quite common for EL to deliver goods to the same customer on multiple

occasions, sometimes on the same day.

Given the high quality of service and the vastness of its logistics network, EL is

the sole 3PL for BSI, but BSI is not ELs only clientjust its largest. Per its core

values and operating principles, EL has a highly effective customer relationship

management program. As such, all clients are assigned a single client

ambassador, at the point of onboarding. Each client ambassador caters to no

more than two clients at any point in time and is only assigned to one client until

he/she completes the initial training and successfully passes the ambassador

excellence assessment.

EL tries to improve its OTIF percentage by combining all packages associated

with a given sales order into a single shipment. Despites BSIs effort to run an

efficient production environment, there are times when not every product on the

originial sales order is ready to be shipped with all the other products in the

order. As a result, it occasionaly creates a situation in which EL has to split

orders over multiple shipments. Although EL can handle LTL, its drivers are

instructed to try to fill their trucks with as many packages as it can hold.

Furthermore, to minimize confusion when providing reverse logistics services (on

behalf of BSI), EL enforces a policy that a delivery must be tied to a single

customer account.

Although, EL is not aware of the contents of the packages, BSI has this level of

visibility due to unique tracking numbers affixed to each package. As such, a

shipment can consist of any number of packages across a multitude of orders.

With respect to those packages, EL captures the data elements specified in the

accompanying data file (e.g., insurance amount, actual pickup date). Typically,

the first column listed denotes the unique identifier. It should also be noted that

tracking numbers are assigned to packages as soon as all products for that

container are assembled and sealed, which is usually several hours to a day

before they are actually shipped.