Design, load data and query a wine rating database

Question

Class 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

Required:

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 (atached), 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: Design, load data and query a wine rating database

Reference No:- TGS03049149

Expected delivery within 24 Hours

Have a Question? (oR Write a Review)

Write atleast 100 words!!

   Recent Questions Asked Database Management System

Q : Summarize the findings of the article you selected Summarize the findings of the article you selected. Explain what you would do about this issue as a manager, and demonstrate your understanding.

Q : Determine primary means of communication used by your leader Determine the primary means of communication used by your leader. Was your leader an effective communicator and mediator?

Q : Explain the foundational philosophical assumptions Explain the foundational philosophical assumptions and principles of the selected leadership paradigms.

Q : How does your article interpret the research study Given the information you found, how does your article interpret the research study?

Q : Design, load data and query a wine rating database MIS 315 Class Project : For the class project you will model, design, load data, and query a wine rating database.

Q : What should then occur with the findings Once skills are assessed, what should then occur with the findings in order to make an informed decision with instruction and data collection?

Q : What type of communication intervention will be appropriate What type of communication intervention will be appropriate for Alex? Explain

Q : Write a request email Write a request email. As outlined in the Business Report Project Map, begin to locate secondary research sources.

Q : What is generalization in language acquisition PSYC 218- What is generalization in language acquisition? How does naturalistic teaching promote generalization in language acquisition?