Question

. On-the-Vine Vineyard Valley. On-the-Vine Vineyard, Inc., is one of California's largest winemaking facilities in Sonoma striving to make both a visit to the vineyard and the wine tasting an unforgettable On-the-Vine is a small, family-owned winery, specializing in limited production of Chardonnay, Sauvignon Blanc, Merlot, Syrah, Zinfandel, Sangiovese, Viognier, exper and Cabernet. currently employs over 12 full-time employees, with positions have rts to only one supervisor. Each employee is assigned a unique identification number. In addition to the employee's with an award- The Employees: On-the-Vine ranging from administrative assistant to winemaker. Among the employees, supervisors been appointed to manage the work of other employees. Each supervised employee repo employee's name, position, and identification number, the company also records each Social Security number, address, phone number, and emergency contact The Vineyard The grounds of On-the-Vine Vineyard include the Estate house winning rose garden, winery, and two vineyard plots of 40 acres each in separate locations. E vineyard is managed by a single employee and is referred to by its own unique name, Sonoma Cellar and Sonoma Barrel. No employee manages more than one vineyard. Each vineyard is dedi- cated to the growing of a single grape variety per year ach ieties: As mentioned above, On-the-Vine Vineyard currently grows eight different grape var 1. Chardonnay 2. Sauvignon Bland 3. Merlot 4. Syrah 5. Zinfandel 6. Sanglovese 7. Viognier 8. Cabernet The Wineryc Each wine produced is given a unique identfication number in addition to its name. Other information recorded for each wine is its vintage year, category (e.g., dry red, dessert), and percent alcohol, which is a legal requirement.Also recorded is the employee in charge of making that wine. Winemakers 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. Several of the grape varieties are used in more than one blended wine. The Customers: On-the-Vine customers are mainly restaurants and wine shops, but the winery also sells to individuals via the Internet. All customers are assigned a unique customer identifica- tion number, and this number is recorded along with their address and phone number. For indi- vidual customers, first name, last name, and date of birth (in order to demonstrate legal age) are also recorded. For restaurants and wine shops, company name and tax identification number are recorded All customers obtain their products by placing orders directly with On-the-Vine. Each order is assigned a unique order number; and the date the order is received, the product or products ordered, and the quantity or quantities desired are all recorded at the same time. A shipment sta- tus of "pending" is assigned to an order until it is shipped, whereupon the status is then changed to "shipped The Task: You have been hired to assist On-the-Vine Vineyard with creating a database struc- ture that will incorporate all the features and business rules mentioned above. You should start out developing an ERD and then create a normalization structure in 3NF entity relationship Dm

this is for CIS

Answer 1

Simple ER Diagram:

Complete ER Diagram:

Wine Number Description Region Name Name Year Wine Produces Winery Location Country BusinesS Number Addres Red Wine White Wine Rosé Wine Bottled Comments Bottle Stored in Inventory Bottle Number Address Quality Mark Price Orders list Attribute

on hand cost inventory ariety HGrape variety date added (inve wine id wine ssed regionme password wine winery wirery (winery name ion id region name users description firstname sells ine type zipoode Wine type id rder id state wine type tem id of address qty creditcard)(expirydate instructions cty birth date cust id Titled Tities d countrytitle id