Modeling and creating the wine rating database

Question

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

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.

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 (see below).

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

_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:- Wine Rating Database.rar

View Complete Question

Request for Solution File

Ask an Expert for Answer!!

Database Management System: Modeling and creating the wine rating database

Reference No:- TGS03045721

Expected delivery within 24 Hours

Have a Question? (oR Write a Review)

Write atleast 100 words!!

   Recent Questions Asked Database Management System

Q : Identify trend in information systems and technology support Identify a trend in Information Systems and Technology supported by three pieces of research to support why you think it is a trend (cite sources).

Q : Importance of a child developing responsibility As a parent am I helping my child or hurting her education if I help with school projects? 1. She has an assignment for a school project

Q : Identify the purpose and principles behind branding Identify the purpose and principles behind branding and positioning using specific examples to illustrate. How important is branding?

Q : What the results of your volunteering could do for others Rather than looking at a leader in the hierarchical sense, look at it in terms of what the results of your volunteering could do for others.

Q : Modeling and creating the wine rating database For the class project you will model, design, load data, and query a wine rating database.

Q : Create a ppt that could be used for presenting the results Create a 10 slides PowerPoint that could be used for presenting the results of your hypothetical program evaluation plan, if you were able to carry it out.

Q : Discuss how does the group described in the article address Discuss How does the group described in the article address the special challenges presented in the text regarding leading groups for children or adolescents?

Q : Calculate the sample variance-standard deviation for data Calculate the sample variance and standard deviation for this data.

Q : How is asd identified and diagnosed How is ASD identified and diagnosed? Name and describe some of the measurement tools. What are the components of effective instruction for students with ASD?