Reference no: EM132476917
MIS 315 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 <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:- Wine Rating Database Assignment File.rar