DBMS Connection Using MySQL

Choose six recent popular movies. Ask at least five people that you know (friends, family, classmates, imaginary friends) to rate each of these movie that they have seen on a scale of 1 to 5. Take the results (observations) and store them in a SQL database. Load the information into an R dataframe.

# 2. Settings
db_user <- 'root'
db_password <- ''
db_name <- 'MovieDB'
db_host <- '127.0.0.1' # localhost for local access
db_port <- 3306

# 3. Read data from db
mydb <-  dbConnect(MySQL(), user = db_user, password = db_password,
                 dbname = db_name, host = db_host, port = db_port)
s <- paste0("SELECT v.*, r.User, r.Rating FROM Movies v INNER JOIN Rating r ON v.Id = r.MovieId;")
rs <- dbSendQuery(mydb, s)
df <-  fetch(rs, n = -1)
df
##    Id             Title    User Rating
## 1   1             Polar   Asher      4
## 2   3 Bohemian Rhapsody     Cyd      2
## 3   2             Glass    Mary      3
## 4   4           Aquaman     Joe      4
## 5   6     The Favourite Charles      2
## 6   5        Green Book     Ana      3
## 7   4           Aquaman   Larry      3
## 8   3 Bohemian Rhapsody  Martin      1
## 9   2             Glass    Mary      4
## 10  1             Polar     Ana      5
## 11  3 Bohemian Rhapsody     Joe      2
## 12  6     The Favourite  Martin      4
on.exit(dbDisconnect(mydb))
## Warning: Closing open result sets

Summary

summary(df)
##        Id           Title               User               Rating     
##  Min.   :1.000   Length:12          Length:12          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.333                                         Mean   :3.083  
##  3rd Qu.:4.250                                         3rd Qu.:4.000  
##  Max.   :6.000                                         Max.   :5.000

Including Plots

## [1] "Aquaman"           "Bohemian Rhapsody" "Glass"            
## [4] "Green Book"        "Polar"             "The Favourite"


Deliverables should include your SQL scripts and your R Markdown code, posted to GitHub.

This is by design a very open ended assignment. A variety of reasonable approaches are acceptable. You can (and should) blank out your SQL password if your solution requires it; otherwise, full credit requires that your code is ‘reproducible’, with the assumption that I have the same database server and R software.





Please email to: kleber.perez@live.com for any suggestion.