Reference no: EM132474176

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

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

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.

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.

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.

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.

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 Studio.
3. Right click on your personal database and select Tasks / Import Flat File ... from the dropdown
menus.
4. Click Specify Input File and browse to the location of the saved CSV file.
5. Follow the prompts in the dialog and the data will be loaded into an SQL Server table
6. Write the SQL for an INSERT INTO ... SELECT ... to load the data from the staging table into the actual table in the Wine Rating database.

Attachment:- Class Project.rar