Assignment 2: R and SQL

Part 1: Build Table

Movies: Rebel Ridge, The Deliverance, The Union, Incoming, Gemini Man. Person1 ratings: 5, 5, 5, 3, 4 Person2 ratings: 3, 3, 4, 4, 5 Person3 ratings: 4, 5, 4, 3, 3 Person4 ratings: 5, 4, 5, 5, 4 Person5 ratings: 3, 3, 3, 4, 2

Part 2: Store Data in SQL Database

CREATE TABLE movie_ratings ( popular_movie VARCHAR(200) NULL, person1_rating INT NULL, person2_rating INT NULL, person3_rating INT NULL, person4_rating INT NULL, person5_rating INT NULL);

INSERT INTO movie_ratings(popular_movie, person1_rating, person2_rating, person3_rating, person4_rating, person5_rating) -> VALUES -> (‘Rebel Ridge’, 5, 3, 4, 5, 3), -> (‘The Deliverance’, 5, 3, 5, 4, 3), -> (‘The Union’, 5, 4, 4, 5, 3), -> (‘Incoming’, 3, 4, 3, 5, 4), -> (‘Gemini Man’, 4, 5, 3, 4, 2);

Part 3: Transfer Data from SQL database to R dataframe

library("RMySQL")

## Loading required package: DBI

mysqlconnection = dbConnect(RMySQL::MySQL(),
                            dbname='movie_reviews',
                            host='localhost',
                            port=3306,
                            user='root',
                            password='@Helih254')

dbListTables(mysqlconnection)

## [1] "movie_ratings"

movie_result = dbSendQuery(mysqlconnection, "select * from movie_ratings")

data.frame = fetch(movie_result)
print(data.frame) 

##      popular_movie person1_rating person2_rating person3_rating person4_rating
## 1      Rebel Ridge              5              3              4              5
## 2  The Deliverance              5              3              5              4
## 3        The Union              5              4              4              5
## 4         Incoming              3              4              3              5
## 5       Gemini Man              4              5              3              4
## 6      Rebel Ridge              5              3              4              5
## 7  The Deliverance              5              3              5              4
## 8        The Union              5              4              4              5
## 9         Incoming              3              4              3              5
## 10      Gemini Man              4              5              3              4
##    person5_rating
## 1               3
## 2               3
## 3               3
## 4               4
## 5               2
## 6               3
## 7               3
## 8               3
## 9               4
## 10              2

Part 4: Missing Data Strategy

My approach to missing data is to remove the missing data to perform calculations such as mean and median. I decided to take this approach because it allows for straightforward calculations without the complications introduced by missing values. Removing missing data ensures that the data set is clean and prevents errors that could arise from attempting calculations on incomplete data. This method is particularly useful for maintaining the integrity of the dataset and ensuring accurate results in subsequent analyses, such as implementing a recommender system later in the course. …