Valley Vineyards Design Case

BUS 630

Valley Vineyard's customers are mainly restaurants and wine shops around the world, but the winery also sells to distributors, restaurants, and individuals via a mail-order membership and website where wine deliveries are legal. All customers are assigned a unique customer identification number and this number is recorded along with their billing address, shipping address, contact information, website address and phone. Individual customers also have their first and last names recorded along with their date of birth, to demonstrate legal age. Restaurants and wine shops have their company name and tax identification number recorded. Valley self-distributes so an internal sales employee is assigned to each customer and Valley would like to track the interaction between the customer and employee.

Valley has multiple plots where they grow several varieties of grapes. Tracking the plots is beyond the scope of this application but they do want to track employee information.Each plot is managed by one employee and employees are assigned to customers and orders. An employee works with multiple customers and orders, but each customer and order is assigned to only one employee.

All customers obtain their wine and food products from VVI by placing orders. Customers use a product catalog for the ordering process. Each product is identified by an SKU. Most wines are sold by case lot. The product catalog includes the SKU, category, size, name, description, weight, cost, and price. Each order is assigned a unique order number, and the date the order is received is recorded along with the product or products ordered and the quantity or quantities desired. A shipment status of "pending" is assigned to an order until it is actually shipped, whereupon the status is then changed to "shipped". Customer orders are usually filled in a single shipment. Once VVI is out of a given wine for a particular year, no more can be produced. VVI needs to record which employee fulfilled a particular order.

VVI also wishes to track the preferences of a customer that may go beyond products that they have purchased in the past. This would make it possible to "pre-sell" a certain wine.

Produce an entity relationship conceptual model of this problem domain. Be sure everything is properly labeled and is readable.