Data 607 Week-2 Assignment - SQL and R

This document has been created using Rmarkdown document, and is meant for assignment for week-2 for the Data 607 Fall 2018 semester course. It assumes that there is a MySQL database available called ‘movies’, which has a table called ‘movie_ratings’. The table has 3 columns - movie_id, person_id and movie_rating. Also the data base has 2 look up tables - one each for the movie_names and person which must have the movie entry and person entry respectively in the data base before the movie_ratings has the entry. The table movie_ratings basically lists the ratings of all the 6 movies present on the movie_names table given by all the 7 people whose data is present in the table person.

This code reads the table from the MySQL database and loads it into a R data.frame. And then it performs some basic data manipulations to make the data more meaningful.

#Installing and Loading the required packages
#install.packages("RMySQL")
library(RMySQL)

## Loading required package: DBI

library(ggplot2)

Connecting to the database - MySQL

## Note here that the password entered here is a temporary passdword set up for the root user in MySQL so that the actual root password is not required. This increases the reproducible nature of the code, and both the SQL file and the R markdown file can work together well on any computer with no issues.
mydb = dbConnect(MySQL(), user='root', password='test1234', dbname='movies', host='localhost')
summary(mydb)

## <MySQLConnection:0,0>
##   User:   root 
##   Host:   localhost 
##   Dbname: movies 
##   Connection type: localhost via TCP/IP 
## 
## Results:

Reading the table - movie_ratings from the connected database - movies

movie.ratings.df <- dbGetQuery(mydb, "
                              select    b.movie_name as movie_name,
                                   a.person_id as person_id,
                               a.movie_rating as movie_rating
                               from movie_ratings a,
                               movie_names b
                               where    a.movie_id = b.movie_id
                               ")

movie.ratings.df

##                movie_name person_id movie_rating
## 1  Avengers: Infinity War        P1            3
## 2  Avengers: Infinity War        P2            3
## 3  Avengers: Infinity War        P3            5
## 4  Avengers: Infinity War        P4            4
## 5  Avengers: Infinity War        P5            5
## 6  Avengers: Infinity War        P6            5
## 7  Avengers: Infinity War        P7            4
## 8       Crazy Rich Asians        P1            2
## 9       Crazy Rich Asians        P2            3
## 10      Crazy Rich Asians        P3            3
## 11      Crazy Rich Asians        P4            3
## 12      Crazy Rich Asians        P5            2
## 13      Crazy Rich Asians        P6            3
## 14      Crazy Rich Asians        P7            4
## 15             Deadpool 2        P1            3
## 16             Deadpool 2        P2            4
## 17             Deadpool 2        P3            4
## 18             Deadpool 2        P4            3
## 19             Deadpool 2        P5            3
## 20             Deadpool 2        P6            3
## 21             Deadpool 2        P7            3
## 22          Incredibles 2        P1            4
## 23          Incredibles 2        P2            3
## 24          Incredibles 2        P3            5
## 25          Incredibles 2        P4            5
## 26          Incredibles 2        P5            4
## 27          Incredibles 2        P6            4
## 28          Incredibles 2        P7            3
## 29              Searching        P1            3
## 30              Searching        P2            2
## 31              Searching        P3            3
## 32              Searching        P4            4
## 33              Searching        P5            5
## 34              Searching        P6            4
## 35              Searching        P7            4
## 36           The Predator        P1            3
## 37           The Predator        P2            3
## 38           The Predator        P3            3
## 39           The Predator        P4            2
## 40           The Predator        P5            3
## 41           The Predator        P6            2
## 42           The Predator        P7            3

Basic findings from the observations:

## [1] "Average rating of the observations for : Avengers: Infinity War is 4.14"

## [1] "Average rating of the observations for : Crazy Rich Asians is 2.86"

## [1] "Average rating of the observations for : Deadpool 2 is 3.29"

## [1] "Average rating of the observations for : Incredibles 2 is 4"

## [1] "Average rating of the observations for : Searching is 3.57"

## [1] "Average rating of the observations for : The Predator is 2.71"