On-the-Vine Vineyard, Inc., is one of Californias largest winemaking facilities in Sonoma Valley, striving to make both a visit to the vineyard and the wine tasting an unforgettable experience. On-the-Vine is a small, family-owned winery, specializing in the limited production of premium quality Chardonnay, Sauvignon Blanc, Merlot, Syrah, Zinfandel, Sangiovese, Viognier, and Cabernet.

The Task:

You have been hired to assist On-the-Vine Vineyard with creating a database structure that will incorporate all the features and business rules mentioned below. You should develop a Normalized ERD (Entity Relationship Diagram).

*Be sure to include:

Table names (Entities)

All fields necessary

Primary Key for each table

Foreign Keys (as needed to create relationships)

Relationships

Details:

The Employees:

On-the-Vine currently employs over 12 full-time employees, with positions ranging from administrative assistant to winemaker. Among the employees, supervisors have been appointed to manage the work of other employees. Each supervised employee reports to only one supervisor. Each employee is assigned a unique identification number. In addition to the employees name, position, and identification number, the company also records each employees Social Security number, address, phone number, and an emergency contact.

The Vineyard:

The grounds of On-the-Vine Vineyard include the Estate house with an award-winning rose garden, winery, and two vineyard plots of 40 acres each in separate locations. Each 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 dedicated to the growing of a single grape variety per year.

As mentioned above, On-the-Vine Vineyard currently grows eight different grape varieties:

1.Chardonnay

2.Sauvignon Blanc

3.Merlot

4.Syrah

5.Zinfandel

6.Sangiovese

7.Viognier

8.Cabernet

The Winery:

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, 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 identification number, and this number is recorded along with their address and phone number. Individual customers also have their first name, last name, and date of birth, in order to demonstrate legal age, recorded. Restaurants and wine shops have their company name and tax identification number 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 status of pending is assigned to an order until it is actually shipped, whereupon the status is then changed to shipped.