Requirements:

Create a high level ERD for the following case. Show entities in rectangle boxes. Show relationships as diamonds and linked by lines between boxes. Entity attributes are not required. Use arrows, single lines and double lines to indicate:

1. whether relationships are 1-1, 1-M, M-1, or M-M (cardinalities)

2. whether entities are required to fully participate (every row must have 1 or more partners in the table being linked to)

______________________________________________________________________________

American Pickle Company had been a local pickle factory in North Dakota. However, shipping costs are now becoming a huge factor in its profits, because its products are beingsold throughout the Midwest. Therefore, American Pickle management has decided to expand to four manufacturing plants.

As part of this expansion, management thinks it should contract with a consulting firm to create a proposal for a new ERD for a revamped corporate database. You have theopportunity to meet with a key person in management, who over lunch describes the corporate situation as follows.

He hands you a blank sheet of paper and asks you to show him what sort of corporate database you would create. Based on how well you do in the next few minutes, yourconsulting firm may be among the few considered for the contract to create a revamped corporate database, or your firm may be dropped from further consideration.

He explains the situation as follows:

American Pickle (referred to as AmPick hereafter) is publicly owned, and has a board of directors. The CEO, who reports to the board, has

• a VP for marketing

• a VP for operations

• a VP for sales, and

• a VP for planning/market expansion

Accounting department reports directly to the CEO.

There will be manufacturing plants in Bismarck, Minneapolis, Des Moines, and St Louis, which fit well with where their sales are strongest.

Each plant will make the entire line of pickles, and therefore can be the sole supplier for the warehouses which it is responsible to keep stocked.

There are over 20 warehouses planned, with about 5 warehouses per manufacturing plant. However, Minneapolis may supply 6 warehouses, and Des Moines only 4, but that is allto be sorted out in the next year.

Each manufacturing plant has an operations manager, and each warehouse has an operations manager. There is staff at both the plants and the warehouses.

There are a number of standard routes out of each warehouse. There are NO customer deliveries from the plants. (Customers are big food chains, such as Rainbow and Cub.)

There has been product recalls in the past. The FDA has told AmPick that if it is not able to identify the exact location of cases of product still in a plant or still in a warehouse anddispose of them, that the entire inventory in all plants and warehouses will have to be destroyed, should ANOTHER recall occur. The FDA identifies batches needing recall by abatch number. Batches are used in creating cases of pickles. A case is NEVER made from more than one batch. (In other words, if there are not enough pickles left in a batch to make the next case, then that remnant is discarded.)

Sales reps get bonuses for number of cases sold each month. (You can assume the order lines have:

• product number

• quantity (in terms of number of cases).

Warehouse managers get bonuses for fast order fulfillment. (Assume each order has

• date order received,

• date order delivered.)

Orders are assigned to routes for delivery. Drivers are assigned to routes. A driver may be assigned to more than one route. A route is never assigned to more than one driver.

Trucks are assigned to drivers. If deliveries are made round the clock, as they have been recently in order to keep up with demand, then multiple drivers may be assigned to onetruck. A driver never has a second truck.

As cases of pickles are created, they are put into plant inventory. Then when they are trucked to a warehouse, they go into warehouse inventory. Then when they are trucked to acustomer, they disappear from inventory. With this inventory system, the sales organization knows at any time what the amount of pickles available to be sold is. Once a sale is made (an order taken), there is a link from the order to specific cases of pickles. So the amount to be sold is the inventory amount minus any that are tagged to an order.