Earn Higher Grades With Instant Assignment Help.Ask Question!

Others
(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:

  1. RegionID in WINE_RATING must exist in RegionID in REGION
  2. 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:

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

menus.

  1. Click Specify Input File and browse to the location of the saved CSV
  2. Follow the prompts in the dialog and the data will be loaded into an SQL Server table
  3. 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



Attachments:
(5/5)

Related Questions

CSI 1420 Introduction to C Programming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two majorconstructs of the C programming language – Fu

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

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

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

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

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:

1 Project 1 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

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:

1 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

Ask This Assignment To Be Done By Our ExpertsGet A+ Grade Solution Guaranteed

expert
joyComputer science
(4/5)
12 Answers Hire Me
expert
Robert DLaw
(4.8/5)
991 Answers Hire Me
expert
Dr Samuel BarberaStatistics
(5/5)
504 Answers Hire Me
expert
Tutor For YouEconomics
(5/5)
937 Answers Hire Me