Part $1$: Database Design

Consider the following Supply Chain information system description. The system should

support a collaborative supply chain composed of suppliers, manufacturers, shippers and

end$-$customers.

Items of different kinds are being moved in the supply chain.Manufacturers use Items of materials

to manufacture Items of products for customers. Suppliers supply Items of materials to

manufacturers; they also supply Items directly to Customers.

Shippers $($e$.$g$.,$ UPS, FedEx, etc.$)$ move items from one businessentity $($supplier$,$ manufacturer,

customer, etc.$)$ to another.

Items have a unique id and weight. Every business entity $($suppliers$,$ manufacturers,

customers etc.$)$ is identified by its id$,$ and has a shipping location $($to be used by Shippers for

shippingorders$),$ address, phone, web location, and contact information.

Every product item $($e$.$g$.,$ a table$)$ has a number of associated material$/$part items in certain

quantities necessary to produce $1$unit of the product item. For example, a table product item,

requires $1$ tabletop item, $4$ leg items, and $8$screw items. Suppliers supply Items, using price

per unit, whichmay vary among different Suppliers for the same Item.

Suppliers have volume discount applied on the dollar amountcomputed based on price

per unit.

Volume discount is described by a percentage of deduction for amount above a

predetermined bound.

Manufacturers produce product Items; this production has an associated setUpCost and product

cost per unit.

Manufacturers may offer volume discounts to customers applied the same way suppliers apply

volume discounts.

Shippers price shipping services per pairs of $($source$,$ destination$)$pairs$,$ where sources and

destinations are shipping locations of business entities.

The pricing of each shipper is based on the total weight of shipment from source to

destination, using price per lb$.,$ and avolume discount applied on the total dollar amount.

Customers have demand quantity for certain Items.

The orders are recorded separately for shipping, manufacturing, and supply.

Shipping orders capture information about a shipper, sender$,$ and recipient $($who are business

entities$)$ and the Item being shipped, and record the quantity of the Item shipped.

Manufacturing orders capture information about a manufacturer, amanufactured Item and the

ordered quantity; and

Supply orders capture information about a supplier, Item and thequantity supplied.

$1.$ Create an ER diagram and specify all integrity constraints. If some information is

missing, suggest additional assumptionsand briefly explain their rationale.

$2.$ Translate the ER design into CREATE TABLE definitionsusing SQL$,$ and add

CHECK constraints as necessary