DATABASES FOR BUSINESS ANALYTICS: Assignment 5:
SOLUTIONS
Kristen Sosulski
10/3/2024
For this assignment, please create a GoogleDoc in your .nyu.edu account. Be sure to include your name and
clearly label the answers to each question. Then, upload your document from your GoogleDrive to Content
> Lesson 5 > Assignment 5 in Brightspace.
Assignment 5 - GRADING CRITERIA
** Take -5 points off for every incorrect answer.**
Use the Michelin one-star rated restaurant data set from Kaggle: https://www.kaggle.com/datasets/
jackywang529/michelin-restaurants?select=one-star-michelin-restaurants.csv. Download the one-star-
michelin-restaurants.csv file. Please open the file in Excel and examine it. Then complete the following and
provide answers for each step.
Step 1. Format the columns to ensure it has the fields required for the Restaurant table. Be sure to pay
attention to those fields that are NOT NULL. You have two choices:
Choice A: Add data for those fields per record. Choice B: Alter the table to eliminate the column constraint.
If you selected CHOICE A: Include a screenshot of the formatted Excel document to show the data added.
If you select CHOICE B: Show the SQL code used to alter the table and justify your decision with a “business
rule”.
Here is an example of how to modify a column constraint on an existing table:
ALTER TABLE Restaurant MODIFY borough varchar(255);
** SOLUTION CHOICE A:**
A screenshot of the Kaggle data that includes data for borough, yearEst, avgPrice, streetaddress, city, state,
zip, and status. These were all the NOT NULL fields that need to be populated in the table.
** SOLUTION CHOICE B:**
USE restaurants;
ALTER TABLE Restaurant MODIFY borough varchar(255), #org NOT NULL MODIFY yearEst int, #org
NOT NULL MODIFY avgPrice int, #org NOT NULL MODIFY COLUMN streetaddress VARCHAR(255),
#org NOT NULL MODIFY COLUMN city VARCHAR(255), #org NOT NULL MODIFY COLUMN state
VARCHAR(2), #org NOT NULL MODIFY COLUMN zip VARCHAR(5),# org NOT NULL MODIFY
COLUMN status VARCHAR(225); #org NOT NULL
DESCRIBE Restaurant;
SELECT * FROM Restaurant;
1
Step 2. Use Excel to omit any records for restaurants not in New York City (You can do this by filtering
the data).
Save the file in Excel as a .csv file with utf-8 encoding. This file you save will likely need to be converted
from a utf-8 with BOM encoding to utf-8. You use a text editor like BBEdit (on Mac) or Notebook++
(on PC). Use either of these programs to save it in a utf-8 only encoding.
Step 3. Use BBEdit or Notepad++ to modify the restaurant names that include unknown characters. For
example, Le Grill de Jo*l Robuchon should be renamed to Le Grill de Joel Robuchon. At least 5 or
so places where this data needs to be edited.
Include a screenshot of your formatted restaurant names.
SOLUTION
A SCREEN SHOT of the formatted restaurant names
Step 4. Insert the records into the Restaurant table using the Table Data Import Wizard in MySQLWork-
Bench. Write a query to show the new records in the Restaurant table. Include the SQL query and the
output as a screenshot.
SOLUTION
A SELECT query that shows the new records.
For example, select * from restaurants where rest_id > 10 # or whatever the index is.
Step 5. Write a query to show the restaurant count by cuisine type. Include the SQL query and the output
as a screenshot.
SOLUTION
SELECT cuisine, COUNT(*) AS cuisine_count FROM Restaurant GROUP BY cuisine ORDER BY cui-
sine_count DESC;
Step 6. Describe how you might redesign the database to accommodate different star ratings, such as 1-5
stars versus Michelin stars.
There are many solutions. Give full credit if a logical solution is provided.
Here are some examples:
First, we could create two ratings columns, one for Michelin stars (which could be null, as many restaurants
would not be Michelin starred) and one for Google/Yelp ratings (which would not allow null values). Alter-
natively, we could modify the column to a VARCHAR, allowing the inputs to be more specific to the type
of rating that the restaurant has received (i.e. G5 for Google 5 stars, M3 for Michelin 3 stars).
