Question

I need help. Valley Vineyard Conceptual Model Case

Read the case then create an entity-relationship diagram that captures the essence of the problem domain.

Identify the entities (diagrammed in a rectangle box)
Establish and label the relationships (diagrammed in a diamond)
Determine the cardinalities
Add the attributes, if you diagram is too busy the attributes can be on a separate page.
Be sure to note any assumptions you have made.

CASE:
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.

Answer 1

Ans: To design an Entity Relationship Diagram based on the business stand point. Explaining the Use Case with the possible ER diagram would be sufficient and getting into the detailing of attributes and dervided attributes would be required when getting into the detailing of actual Data modeling ER Diagram.

Assumption made is Valley Vineyard would track the performance of the employee and also the preferences of the customer based on the previous multiple orders made by the customers.

Attached is the image file of the designed ER diagram. However, if attibutes diagram is also required which I said would be required for physical data modelling required from the development stand point. It can be another detailed explaination altogether.