—————————————————————————

Student Name : Sachid Deshmukh

—————————————————————————

Following are the pre-requisites for running this script

  • Local installation of MySQL Server
  • Local installation of MySQL Workbench
  • MovieRatings.sql: Git Hub url is provided in the header section above
  • R Packages RMySQL, htmlTable

Take following steps to execute the R script

  • Download MovieRatings.sql file and run it against active schema on your MySQL server
  • Make sure MovieRatings.sql script is executed successfully
  • Open R mark up script in R Studio
  • Change variables login.user, login.password and db.name variables to match your envronment
  • Execute R mark up script
library(RMySQL)
## Loading required package: DBI
library(htmlTable)

1] Configurable variables. Make sure to change the variables to match your environment

login.user = 'sachid'
login.password = 'DatabasePassword@1'
db.name = 'moviecatalogue'

mydb = dbConnect(MySQL(), user= login.user, password=login.password, dbname=db.name)

2] Read Movie Ratings data into R Data Frame

Movie.Ratings <- dbGetQuery(mydb, "select moviename, username, userrating from users join userratings 
                  on users.userid = userratings.userid join movies on userratings.movieid= movies.movieid order by movies.moviename")

3] Display movie ratings in data frame. Displayed in matrix format

x = matrix(Movie.Ratings$userrating,ncol=5,byrow=T, dimnames = list(unique(Movie.Ratings$moviename), unique(Movie.Ratings$username)))
htmlTable(x, caption="Most populer movie ratings")
Most populer movie ratings
James Seth Anant Vishal Eric
Star Wars 5 2 2 4 3
Terminator 1 3 2 5 3
The Godfather 5 5 4 3 1
The Lion King 4 2 4 2 1
The Lord of the Rings 2 4 1 5 2
Titanic 1 5 5 2 3

4] Display movie ratings in data frame.

htmlTable(Movie.Ratings, caption="Most populer movie ratings",rnames=F)
Most populer movie ratings
moviename username userrating
Star Wars James 5
Star Wars Seth 2
Star Wars Anant 2
Star Wars Vishal 4
Star Wars Eric 3
Terminator James 1
Terminator Seth 3
Terminator Anant 2
Terminator Vishal 5
Terminator Eric 3
The Godfather James 5
The Godfather Seth 5
The Godfather Anant 4
The Godfather Vishal 3
The Godfather Eric 1
The Lion King James 4
The Lion King Seth 2
The Lion King Anant 4
The Lion King Vishal 2
The Lion King Eric 1
The Lord of the Rings James 2
The Lord of the Rings Seth 4
The Lord of the Rings Anant 1
The Lord of the Rings Vishal 5
The Lord of the Rings Eric 2
Titanic James 1
Titanic Seth 5
Titanic Anant 5
Titanic Vishal 2
Titanic Eric 3