Drawing a ERD:

Holt Distributions is a regional furniture distributor that sells office furniture to governments and businesses. Customers order items from the company and each customer is assisted with a sales representative. Each order may consist of several different items such as five arm chairs and four office desks. At the time of ordering, customers enter two addresses: shipping address and billing address. Customers sometimes provide their own purchase order (P.O.) number so that the purchase can be tracked and entered into the customer's own systems.

An order may be shipped in multiple shipments and each shipment has to be separately invoiced. For each shipment, Holt Distributions produces an invoice (as shown below). This invoice contains invoice date (top-left corner), invoice number (top right corner), information about the customer, the order, the sales representative, and a list of the shipped items. The invoice specifies the original order quantity, quantity shipped in this order, and quantity back ordered (B/O). For each invoice, there is also a freight charge.

Internally, Holt Distributors maintains a catalog of items with item number, description and MSRP price. It also keeps track of sales representatives including their name and ID.

In addition, we assume that

A customer can place multiple orders over time.

Holt Distribution does not combine different orders into one shipment.

Each sales representative may represent multiple customers but each customer is assigned to one sales representative.

Each customer can have multiple billing addresses and shipping addresses.

All shipments of an order use the same billing/shipping address.

Please design a normalized relational database to support the order and invoicing (shipping) transactions at Holt Distributions. The final design should generally follow the requirements of 3rd normal form. If you decide that violation of the 3rd normal form is necessary, you should provide verbal justifications as to why. You should generally follow the description in coming up the database design. If you feel that the description is unclear and additional assumption must be made, then you should use common sense to make assumptions and state such assumptions verbally.

For your convenience, the field names are provided for fields on the invoice. Note that you do not necessarily use all of the fields in the table, nor should you be limited to fields in the table.

Suggested Field Names	Suggested Field Names
InvoiceNumber	OrderNumber
InvoiceDate	OrderDate
CustomerNumber	ShipDate
CustomerName (typically same as "bill to" name)	customerRepNumber
BillingName	customerRepLastName
BillingAddressLinel	customerRepFirstName
BillingAddressLine2	ItemNumber
BillingCity	ItemDescription
BillingState	QuantityOrdered
BillingZip	QuantityShipped
ShipToName	QuantityBackordered
ShipToAddress	Price
ShipToCity	ItemAmount
ShipToState	Freight
ShipToZip	InvoiceTotal
PONumber

You should choose appropriate data types for the fields. We do not have strict requirement for field length but do expect you to choose the right data type.

10/15/2010 Invoice 11025 HOLT DISTRIBUTORS 146 NELSON PLACE BRONSTON, MI 49802 SOLD SHIP TO: TO: Smith Rentals 153 Main St Suite 102 Grandville, MI 49494 A & B Supplies 2180 Halton Pl Arendville, MI 49232 Customer PO. No. Our Order No. Order Date Ship Date Sales 1354 335 12424 10/02/2007 10/15/2007 10-Brown Order Ship B/O Item Number Description Price 1 AT414 0 BT222 Lounge Chair $42.00 Arm Chair $210.00 $204.00 4 $51.00 Freight $42.50 Amount $456.50 10/15/2010 Invoice 11025 HOLT DISTRIBUTORS 146 NELSON PLACE BRONSTON, MI 49802 SOLD SHIP TO: TO: Smith Rentals 153 Main St Suite 102 Grandville, MI 49494 A & B Supplies 2180 Halton Pl Arendville, MI 49232 Customer PO. No. Our Order No. Order Date Ship Date Sales 1354 335 12424 10/02/2007 10/15/2007 10-Brown Order Ship B/O Item Number Description Price 1 AT414 0 BT222 Lounge Chair $42.00 Arm Chair $210.00 $204.00 4 $51.00 Freight $42.50 Amount $456.50