Question

Create an Entity relationship diagram (ERD) conceptual model for this case.

VINEYARD HOLDINGS AND GRAPE VARIETIES

VVI’s 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, Theresa’s, 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. Information that is specific for

each grape variety and must be recorded includes the juice conversion ratio, a measure of

how much juice, on average, can be extracted from a given weight of a grape variety. The

wine storage requirement, which is the type of storage container (typically either stainless

steel tank or oak barrel) used to hold the juice while it ferments into wine, is also

recorded for each grape variety, as is the wine aging requirement, which is the measure of

how long the wine produced from the juice should be stored before bottling. 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.

The bottles used for the wines vary in capacity, shape, and glass color. Each bottle type is

assigned a unique bottle identification code. VVI maintains an inventory count of how

many of each bottle type is currently on hand in their warehouse. The winery prefers to

keep at least a month’s worth of bottle inventory on hand. The usual cost per bottle is also

recorded for each bottle type to aid in pricing the products and as a guide in calculating

expected future bottle order costs.

The bottles must be purchased from outside glass vendors. Each of these vendors is

assigned a unique identification number. In addition to this number, each vendor’s name,

address, and phone are recorded. Also recorded, for each vendor, is the name of the

principal contact (i.e., account representative) at the vendor that handles the VVI account.

Bottles are acquired from the vendors by placing orders. Some bottle types may be

ordered from more than one vendor. Each order involves only a single vendor but may

include more than one bottle type. Usually orders are filled completely by the vendors,

but occasionally an order must be filled with multiple shipments, due to a back-order

condition at the vendor. VVI maintains careful records of what quantities are ordered and

what quantities are received, as well as when the bottles are ordered and when they are

received, and the actual price charged for the bottles.

Answer 1

An Entity relationship diagram (ERD) conceptual model :

Some important sentences for making the ERD ---  

VINEYARD HOLDINGS AND GRAPE VARIETIES ---

VVI’s 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, Theresa’s, 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.VVI currently grows six different grape varieties.

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.The wines are sold in case lots.VVI does not sell partial cases, nor does it mix wines or bottle types in a single case.The bottles used for the wines vary in capacity, shape, and glass color. Each bottle type is assigned a unique bottle identification code.The usual cost per bottle is also recorded for each bottle type to aid in pricing the products and as a guide in calculating expected future bottle order costs.The bottles must be purchased from outside glass vendors. Each of these vendors is assigned a unique identification numbers.

The ERD is shown bellow and described in brief for better understanding ---

The 'Square' shapes are 'Entity'.

The 'Oval' shapes are 'Attribute'.

The 'Double-Oval' shapes are 'Multivalued-Attribute'.

The 'Diamond' shapes are 'Relationship'.

The 'Double-Diamond' shapes are 'Weak-Relationship'.

(#SORRY FOR MY DRAWING HOPE YOU CAN UNDERSTAND)