Week#2

DBMS Connection Using MySQL

# Setting things up

db_user <- 'root'
db_password <- 'elaine317'# I cannot remove password on MySQL
db_name <- 'Reviews'
db_host <- '127.0.0.1' # localhost for local access
db_port <- 3306

# Reading data from database

mydb <-  dbConnect(MySQL(), user = db_user, password = db_password,
                   dbname = db_name, host = db_host, port = db_port)

selection <- paste0("SELECT m.*, r.UserName, r.MovieRating 
                         FROM Movie m 
                              INNER JOIN Rating r 
                                  ON m.Id = r.MovieId;")

recordset <- dbSendQuery(mydb, selection)
dataf <-  fetch(recordset, n = -1) %>% na.omit() # not counting N/A Values if we had any
dataf

on.exit(dbDisconnect(mydb))

## Warning: Closing open result sets

Summary

summary(dataf)

##        Id           Title             UserName          MovieRating   
##  Min.   :1.000   Length:22          Length:22          Min.   :1.000  
##  1st Qu.:2.000   Class :character   Class :character   1st Qu.:2.000  
##  Median :3.000   Mode  :character   Mode  :character   Median :3.000  
##  Mean   :3.227                                         Mean   :3.091  
##  3rd Qu.:5.000                                         3rd Qu.:4.000  
##  Max.   :6.000                                         Max.   :5.000

Some Plots

## [1] "Encanto"                 "Ghostbusters: Afterlife"
## [3] "Jackass Forever"         "Nightmare Alley"        
## [5] "Spider-Man: No Way Home" "The Batman"