3 part assignment in information systems.
Mis 315 Class Project Spring 2020 The Wine Rating Database For the class project you will model , design , load data, and quer y a wine rating database. This database is a simplified vers ion o f 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 italic ized 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 text book 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 y ou will use the ERDPlus application to model the Wine Rating database. The Start Here folder in the BlackBoard course contains m ore information about ERDPlus and a tutorial video . Creat ing the Wine Rating Database Chapter 5 of the text book describes the process of transform ing a database model into database tables. As part of the work for Chapter 5 you will perform an in -class exercise and the Chapt er 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 da tabase. 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 d iagram 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 assig nment 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 T ABLE 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. Data base Load Write the SQL INSERT statements to load the tables with the specified data in your personal database in the AWS SQL Server . Successfully ru n the SQL INSERT stat ements in your personal database . You will be req uired 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 1 7. 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 step s 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 file sMichelle 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 61 Domaine Dujac Clos de la Roche Grand Cru Domaine Dujac Red 85 14 12 62 San Giusto A Rentennano Chianti Classico DOCG Fattoria San Giusto a Rentennano Red 84 15 10 63 Ruffino Riserva Ducale, Chianti Classico Riserva DOCG Ruffino Red 83 15 10 64 Grifone Sangiovese Grifone Red 82 15 22 65 Stolpman Vineyards 'Love You Bunches' Carbonic Sangiovese Stolpman Vineyards Red 81 15 3 66 Didier Dagueneau Pouilly -Fume Silex Didier Dagueneau White 80 16 37 67 Kim Crawford Sauvignon Blanc Kim Crawford White 79 16 26 68 Screaming Eagle Sauvignon Blanc Screaming Eagle White 78 16 32 69 Pavillon Blanc du Chateau Margaux Chateau Margaux White 77 16 6 70 Oyster Bay Sauvignon Blanc Oyster Bay White 76 16 26 71 Didier Dagueneau Pouilly -Fume Pur Sang Didier Dagueneau White 75 16 37 72 Villa Maria Private Bin Sauvignon Blanc Villa Maria Estate White 74 16 26 73 Yalumba 'Y Series' Viognier Yalumba White 73 17 45 74 Domaine Yves et Mathilde Gangloff Condrieu Domaine Yves et Mathilde Gangloff White 74 17 14 75 Darioush Signature Viognier Darioush Winery White 75 17 30 76 Domaine de la Baume Elisabeth Viognier Domaine de la Baume White 76 17 21 77 Chateau -Grillet Cotes du Rhone 'Pontcin' Chateau -Grillet White 77 17 15 78 Record Family Wines Viognier Record Family Wines White 85 17 34 79 Cave Yves Cuilleron 'Les Vignes d'a Cote' Viognier Domaine Yves Cuilleron White 86 17 50 80 Stags' Leap Winery Viognier Stags' Leap Winery White 87 17 30 81 Yalumba The Virgilius Viognier Yalumba White 88 17 18 82 Cline Cellars North Coast Viognier Cline Cellars White 89 17 33 83 Caymus Vineyards Zinfandel Caymus Vineyards Red 90 18 30 84 Seghesio Family Vineyards Zinfandel Seghesio Family Vineyards Red 91 18 44 85 Michael David Winery 'The Seven Deadly Zins' Zinfandel Michael David Winery Red 92 18 25 86 1000 Stories Bourbon Barrel Aged Zinfandel 1000 Stories Wine Red 88 18 27 87 Carlisle Papera Ranch Zinfandel, Carlisle Winery & Vineyards Red 89 18 40 88 A. Rafanelli Zinfandel A. Rafanelli Winery Red 88 18 17 95 F E Trimbach Riesling Schlossberg Maison Trimbach White 87 19 2 96 Grosset Springvale Riesling Grosset Wines White 86 19 11 97 Chateau Ste. Michelle & Dr. Loosen Eroica Riesling Eroica White 85 19 13 98 Penfolds Bin 51 Riesling Penfolds White 84 19 18 99 Paul Cluver Riesling Paul Cluver White 86 19 19 100 Seppelt Drumborg Riesling Seppelt White 87 19 20 101 Dr. Loosen Urziger Wurzgarten Riesling Spatlese Weingut Dr.
Loosen White 88 19 49 102 Weingut Franz Hirtzberger Singerriedel Riesling Smaragd Weingut Franz Hirtzberger White 89 19 54 103 Van Volxem Scharzhofberger 'P.
Pergentsknopp' Riesling Grosses Van Volxem White 90 19 55 104 Markus Molitor Zeltinger Sonnenuhr Riesling Auslese Trocken Weingut Markus Molito White 91 19 56
