Week 5

:: Assignment 5.1

Update your database model in MS Access, print screen, paste into word then post. If you had no corrections from last week it should be the same as week 4. Be sure to check all naming conventions for the table names.

:: Assignment 5.2

Update the Business Requirements Document to show any changes made to your database model in the past 4 weeks then post.

~ Business Requirements Document (revised)

Project Name: Wine Stash/Collection Database

Client's Name: Mark W. Kiner

Designer Name: Aileen Kiner

Team Member Name/Title: Aileen Kiner/DBA and Programmer

Due Date: 09 November 2003

Scope Statement: Design a database model to collect wine and winery details needed for organizing wine stash/collection.

Requirements Definition: The database will include wine details and winery with contact information of the wine collection. In details:

Every wine has the following:

• name of the wine
• wine quantity in stash
• price of wine per unit
• vintage (year)
• drink year/s (best time to consume wine)
• serving and tasting notes
• rating points with rating category type code and description)
• classification (red, white, rosé, fortified, sparkling/champagne) with classification type code and description
• varietal composition (cabernet sauvignon, chardonnay, merlot, pinot blanc, zinfandel, etc.) with varietal type code and description
• winery that produced the wine

To be able to arrange tours, visits, wine tastings, wine ordering and joining wine clubs, each winery has the following in their contact information:

• name of winery
• wine region (country and growing region)
• address type (vineyard, mailing) and complete address (street, city, state, zip)
• Phone type (winery, wine club, tasting room, fax) and phone number
• email type (general, wine club, tasting room) and email address
• website url

:: Assignment 5.3

1. Write a DDL statement to create a table in your database.

~ To create the table Wine:

CREATE TABLE Wine
(
Wine_ID integer,
Wine_name text,
Wine_qty number,
Wine_price currency,
Vintage_yr text,
Drink_yr text,
ServeTaste_notes memo
)

2. Write DML statements to insert, update and delete a record in one of the tables in your database.

~ To INSERT a record INTO the Wine table:

INSERT INTO Wine ( Wine_name, Wine_qty, Wine_price, Drink_yr, ServeTaste_notes )
VALUES ('Korbel Rouge', 2, 25.95, 'Drink now', 'Korbel Rouge is a medium-dry champagne created from Pinot Noir and Cabernet Sauvignon grapes. With a distinctive red color, the aroma and flavor are those of intense black cherry, strawberry and plum. Approximately 1% residual sugar keeps it as dry as the Korbel Brut but removes any tannic bitter edge. It teams perfectly with hearty pastas, red meats and robust seafood dishes. It is excellent with a holiday turkey dinner.');

~ To UPDATE a record on the Wine table:

UPDATE Wine SET Vintage_yr = non-vintage
WHERE Wine_name='Korbel Rouge';

~ To DELETE a record on the Wine table:

DELETE *
FROM Wine
WHERE Wine_name='Korbel Rouge';

3. What score did you get on the quiz?

~ 20 out of 20