The Task: Develop a Normalized ERD (Entity Relationship Diagram) or database tables.

*Be sure to include:

Table names (Entities)

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.

*(Hint:There will need to be about 10 tables):*

Employees people who work at the winery

Vineyards fields, an area to grow grapes

Varietals types of grapes

Harvests instances when a varietal was harvested from a particular vineyard

Blends some wines are made from only 1 harvest but most are made up of a blend of harvests

Wines Giant vats of wine, each has a name and a specific person is in charge of it, the contents of the wine are kept track of in the blends table

Products Wine in a bottle of a specific size with a usual price

Order Line Items the specific products each order contains

Orders an instance of a customer buying wine

Customers people or organizations who buy wine

-