(5/5)

For the class project you will model, design, load data, and query a wine rating database. This database is a simplified version of a database that stores wine ratings. The database contains three table.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

The Wine Rating Database

For the class project you will model, design, load data, and query a wine rating database. This database is a simplified version of a database that stores wine ratings. The database contains three tables:

WINE_RATING (RatingID, WineName, Producer, WineType, Rating, VarietalID, RegionID) REGION (RegionID, RegionName, CountryCode, Statecode)

VARIETAL (VarietalID, VarietalName, VarietalDesc)

The underlined columns are the primary keys, and the italicized columns are the foreign keys. The WINE_RATING table has two referential integrity constraints:

RegionID in WINE_RATING must exist in RegionID in REGION
VarietalID in WINE_RATING must exist in VarietalID in VARIETAL in the Wine Rating Database

Modeling the Wine Rating Database

Chapter 4 of the textbook covers data modeling and the entity-relationship model. As part of the work for chapter 4 you will use a free entity-relationship modeling tool, ERDPlus, for an in-class exercise and the chapter 4 exercise assignment. For the class project you will use the ERDPlus application to model the Wine Rating database. The Start Here folder in the BlackBoard course contains more information about ERDPlus and a tutorial video.

Creating the Wine Rating Database

Chapter 5 of the textbook describes the process of transforming a database model into database tables. As part of the work for Chapter 5 you will perform an in-class exercise and the Chapter 5 using ERDPlus to generate SQL TABLE Create statements. For the class project you will use the ERDPlus application to generate TABLE CREATE statements for the Wine Rating database.

Loading the Wine Rating Database

Chapter 3 of the textbook described SQL INSERT statements. You will apply what you learned in Chapter 3 to write SQL INSERT statements to load the three tables of the Wine Rating database with the required data.

Querying the Wine Rating Database

Chapter 3 of the textbook covered SQL queries. You will apply what you learned from chapter 3 to write the required queries.

Required Work and Due Dates

Database Modeling

Complete an entity-relationship diagram containing the three tables, their columns and the relationships between the tables. You will be required to submit an image of the entity-relationship model in an assignment for this part of the class project.

The assignment for this part of the class project is due Friday, March 13.

Database Design

Generate and modify the SQL statements to create the tables, their relationships, and the referential integrity constraints using the ERDPlus tool. Successfully run the SQL TABLE CREATE statements to create the Wine Rating database objects in your personal database in the AWS SQL Server. You will be required to submit the SQL TABLE CREATE statements in an assignment for this part of the class project.

The assignment for this part of the class project is due Friday, March 27.

Database Load

Write the SQL INSERT statements to load the tables with the specified data in your personal database in the AWS SQL Server. Successfully run the SQL INSERT statements in your personal database. You will be required to submit the SQL INSERT statements in an assignment for this part of the class project. The data for the tables will be uploaded from a flat file using a bulk load process. You will need to write a single SQL INSERT INTO SELECT statement for each table that selects data from a staging table and inserts it into the actual table (see below).

The assignment for this part of the class project is due Friday, April 17.

Database Queries

Write the required queries and successfully run them in your personal database to produce output. You will be required to submit the SQL queries and a copy of the query result in an assignment for this part of the class project. The required queries will be posted in the assignment.

The assignment for this part of the class project is due Friday, May 1.

Wine Rating Data

If you peruse the table data below, you will note that the WINE_RATING and REGION tables have dozens of rows. You are not expected to write separate SQL INSERT statements for each row of data. The data for each of these tables will be loaded using a bulk load process that the class will learn in an in-class exercise. Here are the steps for performing a bulk load of a table using SQL Server Management Studio:

Download the data files from the WINE_RATING Table folder. Name the files <table name>_STAGE.csv.
Login to the server with SQL Server Management
Right click on your personal database and select Tasks / Import Flat File … from the dropdown

menus.

Click Specify Input File and browse to the location of the saved CSV
Follow the prompts in the dialog and the data will be loaded into an SQL Server table
Write the SQL for an INSERT INTO … SELECT … to load the data from the staging table into the actual table in the Wine Rating

REGION Table

RegionID	RegionName	CountryCode	StateCode
1	Alexander Valley	US	CA
2	Alsace	FR	NULL
3	Ballard Canyon	US	CA
4	Barbaresco	IT	NULL
5	Barolo	IT	NULL
6	Bordeaux	FR	NULL
7	California	US	CA
8	Carneros	US	CA
9	Chablis	FR	NULL
10	Chianti Classico	IT	NULL
11	Clare Valley	AU	NULL
12	Clos de la Roche	FR	NULL
13	Columbia Valley	US	WA
14	Condrieu	FR	NULL
15	Cotes du Rhone	FR	NULL
16	Dolcetto d'Alba	IT	NULL
17	Dry Creek Valley	US	CA
18	Eden Valley	AU	NULL
19	Elgin	ZA	NULL
20	Henty	AU	NULL
21	IGP Pays d'Oc	FR	NULL
22	Italy	IT	NULL
23	La Tache	FR	NULL
24	Langhe	IT	NULL
25	Lodi	US	CA
26	Marlborough	NZ	NULL
27	Mendocino County	US	CA
28	Mendoza	AR	NULL
29	Mercurey	FR	NULL
30	Napa Valley	US	CA
31	Napa Valley, Oak Knoll District	US	CA
32	Napa Valley, Oakville	US	CA
33	North Coast	US	CA
34	Paso Robles	US	CA
35	Pomerol	FR	NULL
36	Pouilly-Fuisse	FR	NULL
37	Pouilly-Fume	FR	NULL
38	Rias Baixas	ES	NULL
39	Romanee-Conti	FR	NULL

40	Russian River Valley	US	CA
41	Santa Lucia Highlands	US	CA
42	Santa Rita Hills	US	CA
43	Sonoma Coast	US	CA
44	Sonoma County	US	CA
45	South Australia	AU	NULL
46	Suisun Valley	US	CA
47	Toscana IGT	IT	NULL
48	Uco Valley	AR	NULL
49	Urzig	DE	NULL
50	Vin de Table	FR	NULL
51	Vins de Pays d'Oc	FR	NULL
52	Vouvray	FR	NULL
53	Vouvray Petillant - Mousseux	FR	NULL
54	Wachau	AT	NULL
55	Wiltingen	DE	NULL
56	Zeltingen-Rachtig	DE	NULL

VARIETAL Table

VarietalID	VarietalName	VarietalDesc
1	Albarino	Spanish white wine grape that makes crisp, refreshing, and light- bodied wines.
2	Beaujolais	Typically light, fresh, fruity red wines from and area south of Burgundy, near Lyons, in eastern France.
3	Cabernet Franc	Red wine grape used in Bordeaux for blending with Cabernet Sauvignon.
4	Cabernet Sauvignon	Full-bodied wines with great depth that improve with aging. Cabernet spends from 15 to 30 months aging in American & French Oak barrels
5	Chardonnay	Apple, Pear, Vanilla, Fig, Peach, Pineapple, Melon, Citrus, Lemon, Grapefruit, Honey, Spice, Butterscotch, Butter & Hazelnut.
6	Chenin Blanc	Native of the Loire where it’s the basis of the famous whites: Vouvray, Anjou, Quarts de Chaume and Saumer.
7	Dolcetto	From northwest Piedmont it produces soft, round, fruity wines fragrant with licorice and almonds.
8	Gewurztramine r	A distinctive floral bouquet & spicy flavor are hallmarks of this medium-sweet wine.

9	Malbec	Once important in Bordeaux and the Loire in various blends, this not- very-hardy grape has been steadily replaced by Merlot and the two Cabernets.
10	Merlot	Herbs, Green Olive, Cherry & Chocolate. Softer & medium in weight with fewer tannins than Cabernet and ready to drink sooner.
11	Nebbiolo	The great grape of Northern Italy, which excels there in Barolo and Barbaresco, strong, ageable wines.
12	Petit Verdot	From the Bordeaux Region of France it is used for blending with Cabernet Sauvignon.
13	Petite Sirah	Plum & blackberry flavors mark this deep, ruby colored wine. Usually full-bodied with chewy tannins.
14	Pinot Noir	This is the great, noble grape of Burgundy. Difficult to grow but at its best it is smooth & richer than Cabernet Sauvignon with less tannin.
15	Sangiovese	Known for its supple texture, medium to full-bodied spice flavors, raspberry cherry & anise. Sangiovese is used in many fine Italian wines including Chianti.
16	Sauvignon Blanc	Grassy & herbaceous flavors and aromas mark this light and medium- bodied wine, sometimes with hints of gooseberry & black currant. In California
17	Viognier	Viognier, is one of the most difficult grapes to grow. It makes a floral and spicy white wine, medium to full-bodied and very fruity, with apricot and peach aromas.
18	Zinfandel	With predominant raspberry flavors and a spicy aroma, Zinfandels can be bold and intense as well as light and fruity.
19	Riesling	Riesling is a light-skinned, aromatic grape of German origin which is – if the majority of top wine critics are to be believed – the world's finest white wine grape variety.
VarietalID	VarietalName	VarietalDesc
20	Albarino	Spanish white wine grape that makes crisp, refreshing, and light- bodied wines.

WINE_RATING Table

RatingID	WineName	Producer	WineType	Rating	VarietalID	RegionID
1	La Cana Albarino	La Cana	White	85	1	38
2	Ramon Bilbao Valinas Albarino	Ramon Bilbao	White	90	1	38
3	The Franc	Cosentino Winery	Red	81	3	25
4	Inkblot	Michael David Winery	Red	85	3	25
5	Caymus Vineyards Cabernet Sauvignon	Caymus Vineyards	Red	90	4	30
6	Silver Oak Cellars Cabernet Sauvignon	Silver Oak Cellars	Red	92	4	1
7	Stag's Leap Wine Cellars 'Artemis' Cabernet Sauvignon	Stag's Leap Wine Cellars	Red	85	4	30
8	Robert Mondavi Winery Cabernet Sauvignon	Robert Mondavi Winery	Red	86	4	30
9	Sonoma Loeb Chardonnay Private Reserve	Sonoma Loeb	White	87	5	8
10	Testarossa Chardonnay Santa Lucia Highlands	Testarossa	White	91	5	41
11	Chateau de Chamirey Mercurey Blanc	Chateau de Chamirey	White	87	5	29
12	Titus Chardonnay Napa Valley	Titus	White	88	5	30
13	Migration Chardonnay	Duckhorn	White	91	5	44
14	BR Cohn Chardonnay Sangiacomo Vineyard Carneros	BR Cohn	White	92	5	8
15	Athair Chardonnay	Athair	White	93	5	40
16	Domaine Chenevieres Chablis Premier Cru Cote de Lechet	Domaine Chenevieres	White	87	5	9
17	Beringer Chardonnay Luminus	Beringer	White	88	5	31
18	Domaine Trouillet Pouilly Fuisse	Domaine Trouillet	White	89	5	36
19	The Cutrer	Sonoma-Cutrer	White	91	5	40
20	Champalou Vouvray Brut NV	Domaine Champalou	White	88	6	53

21	François Chidaine Pétillant Brut NV	Francois Chidaine	White	87	6	53
22	Domaine Huet ‘Le Haut-Lieu’ Vouvray Sec	Domaine Huet	White	91	6	52
23	Mengler Family Wines Alegria Vineyards Dolcetto	Alegria Vineyards	Red	89	7	40
24	Klinker Brick Dolcetto	Klinker Brick	Red	77	7	25
25	Maryhill Vineyard Series Art Den Hoed Vineyard Painted Hills Dolcetto	Maryhill	Red	78	7	13
26	Giribaldi Dolcetto d 'Alba	Giribaldi	Red	88	7	16
27	F E Trimbach Gewurztraminer	Maison Trimbach	White	83	8	2
28	Fetzer Valley Oaks Gewurztramine	Fetzer Vineyards	White	82	8	7
29	Chateau Ste. Michelle Gewurztramine	Chateau Ste. Michelle	White	93	8	13
30	Arthur Metz Gewurztraminer	Arthur Metz	White	77	8	2
31	Phebus Malbec Reserva	Phebus	Red	94	9	28
32	Mascota Vineyards La Mascota Malbec	Mascota Vineyards	Red	93	9	28
33	D'Autrefois Malbec	D'Autrefois	Red	89	9	51
34	HandCraft Malbec	Handcraft	Red	88	9	7
35	Petrus	Petrus	Red	87	10	35
36	Masseto Toscana IGT	Masseto	Red	86	10	47
37	Tua Rita Redigaffi Toscana IGT	Azienda Agricola Tua Rita	Red	88	10	47
38	Duckhorn Vineyards Three Palms Vineyard Merlot	Duckhorn Wine Company	Red	84	10	30
39	Chateau La Fleur de Gay	Chateau La Fleur de Gay	Red	83	10	35
40	Giacomo Conterno Monfortino, Barolo Riserva DOCG	Giacomo Conterno	Red	82	11	5
41	Gaja Sori San Lorenzo Langhe-Barbaresco	Gaja	Red	87	11	24
42	Produttori del Barbaresco 'Barbaresco DOCG'	Produttori del Barbaresco	Red	88	11	4

43	Prunotto Barolo DOCG	Prunotto	Red	89	11	5
44	Petra Zingari Toscana IGT	Petra Azienda Agricola	Red	90	12	47
45	Michael David Winery 'Inkblot' Petit Verdot	Michael David Winery	Red	91	12	25
46	Stags' Leap Winery Petite Sirah	Stags' Leap Winery	Red	92	13	30
47	Caymus-Suisun Grand Durif	Caymus-Suisun	Red	93	13	46
48	Ridge Vineyards Lytton Estate Petite Sirah	Ridge Vineyards	Red	94	13	17
49	Mettler Family Vineyards Petite Sirah	Mettler Family Vineyards	Red	95	13	25
50	Domaine de la Romanee-Conti Romanee-Conti Grand Cru	Domaine de la Romanee-Conti	Red	96	14	39
51	Domaine de la Romanee-Conti La Tache Grand Cru Monopole	Domaine de la Romanee-Conti	Red	95	14	23
52	DuMOL Finn Pinot Noir	DuMol	Red	94	14	40
53	Albert Mann Pinot Noir Grand P	Domaine Albert Mann	Red	93	14	2
54	Hartford Family Winery Hartford Court Land's Edge Vineyards Pinot Noir	Hartford Family Winery	Red	92	14	43
55	Melville Estate Small Lot Collection Sandy's Pinot Noir	Melville Winery	Red	91	14	42
56	Roar Wines Sierra Mar Vineyard Pinot Noir	Roar Wines	Red	90	14	41
57	Three Sticks 'PFV' Estate Pinot Noir	Three Sticks Winery	Red	89	14	43
58	Catherine Marshall Wines Pinot Noir on Clay Soils	Catherine Marshall Wines	Red	88	14	19
59	Bodegas Salentein 'Primus' Pinot Noir	Bodega Salentein	Red	87	14	48
60	Meiomi Pinot Noir	Meiomi Wines	Red	86	14	7

(5/5)

Use CA10RAM to get 10%* Discount.

For the class project you will model, design, load data, and query a wine rating database. This database is a simplified version of a database that stores wine ratings. The database contains three table.

ANSWER ALL QUESTIONS

The Wine Rating Database

Modeling the Wine Rating Database

Creating the Wine Rating Database

Loading the Wine Rating Database

Querying the Wine Rating Database

Required Work and Due Dates

Database Modeling

Database Design

Database Load

Database Queries

Wine Rating Data

REGION Table

WINE_RATING Table

Attachments:

Instructions Files

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

Ask This Question To Be Solved By Our ExpertsGet A+ Grade Solution Guaranteed

Our Experts

Um e HaniScience

Muhammad Ali HaiderFinance

Husnain SaeedComputer science

Atharva PatilComputer science

Other Services

For the class project you will model, design, load data, and query a wine rating database. This database is a simplified version of a database that stores wine ratings. The database contains three table.

ANSWER ALL QUESTIONS

The Wine Rating Database

Modeling the Wine Rating Database

Creating the Wine Rating Database

Loading the Wine Rating Database

Querying the Wine Rating Database

Required Work and Due Dates

Database Modeling

Database Design

Database Load

Database Queries

Wine Rating Data

REGION Table

WINE_RATING Table

Attachments:

Instructions Files

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

Ask This Question To Be Solved By Our ExpertsGet A+ Grade Solution Guaranteed

Our Experts

Um e HaniScience

Muhammad Ali HaiderFinance

Husnain SaeedComputer science

Atharva PatilComputer science