Task:

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.

Your 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.

Solution:

if (!require('RMySQL')) install.packages('RMySQL')
## Loading required package: RMySQL
## Loading required package: DBI
library(RMySQL)

Database Access:

Database properties to access the database-
myLocalUserName <- 'root'
myLocalPassword <- 'root'
myLocalDbName <- 'moviesta'
myLocalHost <- 'localhost'
Connecting the database-
mydb = dbConnect(MySQL(), user=myLocalUserName, password=myLocalPassword, dbname=myLocalDbName, host=myLocalHost)
Getting list of database tables-
dbListTables(mydb)
## [1] "movies"  "ratings" "users"

Queries into the database:

Retreiving the ‘users’ table from database-
users <- dbSendQuery(mydb, 'SELECT * FROM users')
dbFetch(users)
##   userID     userName
## 1      1 Hoshne Jahan
## 2      2   Mike Jones
## 3      3 Maria Garcia
## 4      4  Sarah Smith
## 5      5 Nazrum Jahan
Retreiving the ‘movies’ table from database-
movies <- dbSendQuery(mydb, 'SELECT * FROM movies')
dbFetch(movies)
##   movieID            movieName
## 1       1              Dunkirk
## 2       2   The Shape of Water
## 3       3                   It
## 4       4                Logan
## 5       5             The Post
## 6       6 Call Me by Your Name
Retreiving the ‘ratings’ table from database-
ratings <- dbSendQuery(mydb, 'SELECT * FROM ratings')
dbFetch(ratings)
##    ratingID movie user rating        comment
## 1         1     1    1      5 Amazing Movie!
## 2         2     1    2      4               
## 3         3     2    3      5        Superb!
## 4         4     2    4      5          Best!
## 5         5     3    5      3    Incredible!
## 6         6     3    1      5   Amazing one!
## 7         7     4    2      3           Wow!
## 8         8     4    3      5     Satisfied!
## 9         9     5    4      4   Tom is best!
## 10       10     5    5      5    True story!
## 11       11     6    1      4   Magnificant!
## 12       12     6    2      5      Worth it!
Retreiving all relational data for the ‘rating’ table-
moviesta = dbSendQuery(mydb, "SELECT 
M.movieName As 'Movie',
U.userName As 'User',
R.rating As 'Rating',
R.comment AS 'Comment'
FROM movies AS M
JOIN ratings AS R
ON M.movieID = R.movie
JOIN users AS U
ON U.userID = R.user;")
dbFetch(moviesta)
##                   Movie         User Rating        Comment
## 1               Dunkirk Hoshne Jahan      5 Amazing Movie!
## 2                    It Hoshne Jahan      5   Amazing one!
## 3  Call Me by Your Name Hoshne Jahan      4   Magnificant!
## 4               Dunkirk   Mike Jones      4               
## 5                 Logan   Mike Jones      3           Wow!
## 6  Call Me by Your Name   Mike Jones      5      Worth it!
## 7    The Shape of Water Maria Garcia      5        Superb!
## 8                 Logan Maria Garcia      5     Satisfied!
## 9    The Shape of Water  Sarah Smith      5          Best!
## 10             The Post  Sarah Smith      4   Tom is best!
## 11                   It Nazrum Jahan      3    Incredible!
## 12             The Post Nazrum Jahan      5    True story!