Week 4

:: Assignment 4.1

Cardinality Spreadsheet:

Min Rec Max Rec Table Relationship Verb Min Rec Max Rec Table Max Cardinality
1 M Wine has 1 1 Rating_Category M to 1
1 M Rating_Category is classified by 1 1 Rating_Category_type_luv M to 1
1 M Wine belongs to 1 1 Classification M to 1
1 M Classification is classified by 1 1 Classification_type_luv M to 1
1 1 Wine has 0 M Wine_Varietal 1 to M
0 M Wine_Varietal has 1 1 Varietal M to 1
1 M Varietal is classified by 1 1 Varietal_type_luv M to 1
1 M Wine belongs to 1 1 Winery M to 1
1 M Winery is located at 1 1 Region M to 1
1 1 Winery has 0 M Winery_Address 1 to M
0 M Winery_Address has 1 1 Address M to 1
1 M Address is classified by 1 1 Address_type_luv M to 1
1 1 Winery has 0 M Winery_Phone 1 to M
0 M Winery_Phone has 1 1 Phone M to 1
1 M Phone is classified by 1 1 Phone_type_luv M to 1
1 1 Winery has 0 M Winery_Email 1 to M
0 M Winery_Email has 1 1 Email M to 1
1 M Email is classified by 1 1 Email_type_luv M to 1
1 M Winery has 0 1 Website M to 1

 

:: Assignment 4.2

Table and Column Spreadsheet with Foreign Key column:

Legend:
NK - Natural Key
PK - Primary Key
FK - Foreign Key

Table Name Column Name NK PK FK Data Format Data Example
Wine Wine_ID   x   AutoNumber 0001
Wine Wine_name x     Text Isosceles
Wine Wine_qty x     Number 03
Wine Price_amt x     Currency $35.95
Wine Vintage_yr x     Number 1997
Wine Drink_yr x     Text Drink 2007-2009
Wine ServeTaste_notes x     Memo Good with beef, lamb, pork, game, hard cheeses.
 
Rating_Category Rating_cat_ID   x   AutoNumber 10
Rating_Category Wine_ID   x x Numeric 1
Rating_Category Rating_cat_type_ID   x x Numeric 2
Rating_Category Rating_pts x     Number 92
 
Rating_Category_type_luv Rating_cat_type_ID   x   AutoNumber 0001
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_Category_type_luv Rating_cat_type_desc       Text Outstanding - superior character and style.
 
Classification Class_ID   x   AutoNumber 0001
Classification Wine_ID   x x Numeric 55
Classification Class_type_ID   x x Numeric 3
Classification Class_name x     Text Red Wine
 
Classification_type_luv Class_type_ID   x   AutoNumber 0001
Classification_type_luv Class_type_code x     Text Red Wine, White Wine, Dessert, Sparkling
Classification_type_luv Class_type_desc       Memo 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   AutoNumber 0001
Varietal Varietal_type_ID   x x Numeric 4
Varietal Varietal_comp x     Memo 63% Cabernet Sauvignon, 19% Cabernet Franc, 18% Merlot
 
Varietal_type_luv Varietal_type_ID   x   AutoNumber 0001
Varietal_type_luv Varietal_type_code x     Text Cabernet Sauvignon
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   AutoNumber 0001
Winery Wine_ID   x x Numeric 5
Winery WineryVineyard_name x     Text Justin Vineyards & Winery
 
Region Region_ID   x   AutoNumber 0001
Region Winery_ID   x x Numeric 6
Region Country_name x     Text USA
Region Subregion_name x     Text CA: South Central Coast
 
Address Addr_ID   x   AutoNumber 0001
Address Addr_type_ID   x x Numeric 7
Address Street_addr x     Text 11680 Chimney Rock Road
Address City_name x     Text Paso Robles
Address State/Prov_name x     Text CA
Address Zip_code x     Text 93446
 
Address_type_luv Addr_type_ID   x   AutoNumber 0001
Address_type_luv Addr_type_code x     Text Vineyard, Mailing
Address_type_luv Addr_type_desc       Text Vineyard address-where people can visit, tour, taste and buy wine.
 
Phone Phone_ID   x   AutoNumber 0001
Phone Phone_type_ID   x x Numeric 8
Phone Phone_nbr x     Text 1 (800) 726-0049
 
Phone_type_luv Phone_type_ID   x   AutoNumber 0001
Phone_type_luv Phone_type_code x     Text Winery, Wine Club, Tasting Room, Fax
Phone_type_luv Phone_type_desc       Text Winery-for general winery information.
 
Email Email_ID   x   AutoNumber 0001
Email Email_type_ID   x x Numeric 9
Email Email_addr x     Text info@justinwine.com
 
Email_type_luv Email_type_ID   x   AutoNumber 0001
Email_type_luv Email_type_code x     Text General, Wine Club, Tasting Room
Email_type_luv Email_type_desc       Text General email for winery information
 
Website Web_ID   x   AutoNumber 0001
Website Winery_ID   x x Numeric 10
Website Web_url x     Hyperlink www.justinwine.com
 
Wine_Varietal Wine_ID   x x Numeric 11
Wine_Varietal Varietal_ID   x x Numeric 12
Wine_Varietal Varietal_type_ID   x x Numeric 13
 
Winery_Address Wine_ID   x x Numeric 14
Winery_Address Addr_ID   x x Numeric 15
Winery_Address Addr_type_ID   x x Numeric 16
 
Winery_Phone Wine_ID   x x Numeric 17
Winery_Phone Phone_ID   x x Numeric 18
Winery_Phone Phone_type_ID   x x Numeric 19
 
Winery_Email Email_ID   x x Numeric 20
Winery_Email Email_ID   x x Numeric 21
Winery_Email Email_type_ID   x x Numeric 22

 

:: Assignment 4.3

Relationships screen capture:

Wine Database Relationships