I need help building an Entityrelationship model diagram using the following information.

There are many limitations to their existing infrastructure, including the ability to run queries and custom reports. After a review of the ERP applications for small wineries they concluded that several of the packages could be tailored to fit their needs as a winery, but they need a good data and process model to proceed. They have invited several software or database consulting companies, including yours, to evaluate their business needs and to propose an automated solution to the record keeping challenge. The results of the preliminary investigation recently conducted by an analyst from your company are presented below.

PERSONNEL

Valley Vineyards, Inc. (VVI) currently employs 16 full-time employees and 20 part-time employees with positions ranging from clerks to grape farmers to wine makers. Among the employees, supervisors have been appointed that oversee the work of other employees. Each supervised employee reports to only one supervisor. Each employee is assigned a unique employee identification number. In addition to the employees name, position, and ID number, the company also records each employees social security number, address, and phone.

VINEYARD HOLDINGS AND GRAPE VARIETIES

VVIs vineyard sources its wine from ten plots of land in separate locations from two acres to twenty acres. Each vineyard has its own unique name, such as Rattlesnake Canyon, Red Fox, Theresas, etc. and is either owned by VVI or by an independent third party. Each is managed by a single employee. No employee manages more than one vineyard. The location and size, measured in acres, of each vineyard is recorded. Each vineyard is dedicated to the growing of a single grape variety per vintage year. However, over time a particular vineyard may be replanted to a different grape variety, depending on market demand for particular types of wine. The winery maintains a record of these plantings.

VVI currently grows six different grape varieties: Cabernet Sauvignon, Merlot, Zinfandel, Pinot Noir, Chardonnay, and Sauvignon Blanc. Certain measures related to the grapes that vary with the specific vintage year harvest are also recorded including the

vineyard the grapes come from, the total amount (weight) of grapes harvested, and the ripeness of the grapes (expressed in % sugar).

WINE PRODUCTS AND BOTTLES

Information on the wines created from the grapes must, of course, be recorded. Each wine produced is given a unique identification number in addition to its name. Other information recorded for each wine is its vintage year, category (e.g., dry red, dessert, etc.), and percent alcohol, a legal requirement. Also recorded is the employee in charge of making that wine. Wine makers may be responsible for more than one wine at a time.

The composition of a wine may be entirely from a single grape variety or may be a blend of more than one variety. The proportion of juice from each grape variety for each wine produced must be recorded. Several of the grape varieties are used in more than one blended wine. None of VVI wines are vineyard specified; that is, the wines are labeled by the grape varieties contained in the wines only, without reference to specific vineyard plots.

The wines are sold in case lots. The winery refers to these case lots as products. A product is a specific wine in a specific bottle size in a specific case quantity sold at a specific price. Each product type is given a unique product identification number. VVI does not sell partial cases, nor does it mix wines or bottle types in a single case.

CUSTOMERS

VVIs customers are mainly restaurants and wine shops around the world, but the winery also sells to distributors, restaurants, and individuals via a mail-order newsletter and website where allowed. All customers are assigned a unique customer identification number and this number is recorded along with their address and phone. Individual customers also have their first and last names recorded along with their date of birth, in order to demonstrate legal age. Restaurants and wine shops have their company name and tax identification number recorded.

All customers obtain their products from VVI by placing orders. 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 always filled in a single shipment, as no back orders are possible. Once VVI is out of a given wine for a particular year, no more can be produced.

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.