Week 3

:: Assignment 3.1

Add the column names 'Natural Key Column Indicator' and 'Primary Key Column Indicator' to your table and column list from last weeks assignment.

Natural Key Column(s): Enter an 'X' in the column with the heading 'Natural Key Column Indicator', if the column is part of the Natural Key.

Primary Key Column(s): Enter a new row in your spead sheet and add the table name, the primary key column name under the 'Column Name' heading, put an 'X' in the Primary Key Column Indicator column, enter the Data Format and an Example of the data.

~ I am using surrogate keys for all my primary keys. The Data Format will be Auto Number in MS Access.

Legend:
NK - Natural Key
PK - Primary Key

Entity Attribute Table Name Column Name NK PK Data Format Data Example
    Wine Wine_ID   x Auto Number 0001
Wine Wine Name Wine Wine_name x   Text Isosceles
Wine Quantity Quantity Wine Wine_qty x   Number 03
Wine Price Amount Paid Wine Price_amt x   Currency $35.95
Wine Vintage Vintage Wine Vintage_yr x   Number 1997
Wine Drink time Drink Year Wine Drink_yr x   Text Drink 2007-2009
Wine Notes Serving and Tasting notes Wine Serve/Taste_notes x   Memo Good with beef, lamb, pork, game, hard cheeses.
    Rating_Category Rating_cat_ID   x Auto Number 10
Wine Rating Rating Points Rating_Category Rating_pts x   Number 92
    Rating_Category_type_luv Rating_cat_type_ID   x Auto Number 0001
Rating Type Rating Category look-up value Rating_Category_type_luv Rating_cat_type_code x   Text 95-100: Classic, 90-94: Outstanding, 80-89: Good to Very Good, 70-79: Average, 60-69: Below Average, 50-59: Poor
Rating Type Rating Category look-up value description Rating_Category_type_luv Rating_cat_type_desc     Text Outstanding - superior character and style.
    Classification Class_ID   x Auto Number 0001
Wine Classification Class name Classification Class_name x   Text Red Wine
    Classification_type_luv Class_type_ID   x Auto Number 0001
Classification Type Wine Classification look-up value Classification_type_luv Class_type_code x   Text Red Wine, White Wine, Dessert, Sparkling
Classification Type Wine Classification look-up value description Classification_type_luv Class_type_desc     Text Red wine - A wine made from dark-skinned grapes (red, purple, black, blue), which remains in contact with the grape skins (from which color is extracted) during fermentation.
    Varietal Varietal_ID   x Auto Number 0001
Wine Varietal Varietal Composition Varietal Varietal_comp x   Memo 63% Cabernet Sauvignon, 19% Cabernet Franc, 18% Merlot
    Varietal_type_luv Varietal_type_ID   x Auto Number 0001
Varietal Type Varietal Composition look-up value Varietal_type_luv Varietal_type_code x   Text Cabernet Sauvignon
Varietal Type Varietal Composition look-up value description Varietal_type_luv Varietal_type_desc     Memo Cabernet Sauvignon - The undisputed king of red wines, Cabernet is a remarkably steady and consistent performer throughout much of the state. While it grows well in many appellations, in specific appellations it is capable of rendering wines of uncommon depth, richness, concentration and longevity.
    Winery Winery_ID   x Auto Number 0001
Winery Winery/Vineyard Name Winery Winery/Vineyard_name x   Text Justin Vineyards & Winery
    Region Region_ID   x Auto Number 0001
Region Country of Origin Region Country_name x   Text USA
Region Growing Region Region Subregion_name x   Text CA: South Central Coast
    Address Addr_ID   x Auto Number 0001
Address Street Address Address Street_addr x   Text 11680 Chimney Rock Road
Address City Name Address City_name x   Text Paso Robles
Address State/Province Address State/Prov_name x   Text CA
Address Zip Code Address Zip_code x   Text 93446
    Address_type_luv Addr_type_ID   x Auto Number 0001
Address Type Address look-up value Address_type_luv Addr_type_code x   Text Vineyard, Mailing
Address Type Address look-up value description Address_type_luv Addr_type_desc     Text Vineyard address-where people can visit, tour, taste and buy wine.
    Phone Phone_ID   x Auto Number 0001
Phone Phone number Phone Phone_nbr x   Text 1 (800) 726-0049
    Phone_type_luv Phone_type_ID   x Auto Number 0001
Phone Type Phone look-up value Phone_type_luv Phone_type_code x   Text Winery, Wine Club, Tasting Room, Fax
Phone Type Phone look-up value description Phone_type_luv Phone_type_desc     Text Winery-for general winery information.
    Email Email_ID   x Auto Number 0001
Email Email Address Email Email_addr x   Text info@justinwine.com
    Email_type_luv Email_type_ID   x Auto Number 0001
Email Type Email look-up value Email_type_luv Email_type_code x   Text General, Wine Club, Tasting Room
Email Type Email look-up value description Email_type_luv Email_type_desc     Text General email for winery information
    Website Web_ID   x Auto Number 0001
Website Website URL Website Web_url x   Hyperlink www.justinwine.com

 

:: Assignment 3.2

Create a database in MS Access.
Create one table in MS Access from your database design.

~ My database in MS Access

 

:: Assignment 3.3

What were the difficult areas in this assignment for you and why?

~ Assignment 3.1 is the most difficult area in this assignment particularly natural key, surrogate key and primary key. I looked for meaning and explanation of what natural key and surrogate key in MS Access Help but found nothing. It would be very helpful if we have a book on database design to supplement lectures or at a minimum, have concrete examples that expound important concepts in the lectures. For example, on the Piano Student Database, how would one do it if the primary keys are based from natural keys (for comparison with surrogate keys as primary keys)? I based my assignment 3.1 answers with the Piano Student Database by analogy.

How long did the assignment take you?

~ About 4 hours