CUNY SPS DATA 607 Assignment 2

Assignment Instructions

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.

Load the Raw Data into R from CSV File

library(RCurl) 

## Loading required package: bitops

x <- getURL("https://raw.githubusercontent.com/betsyrosalen/DATA_607_Data_Acquisition_and_Management/master/Assignment2/Movie%20Ratings.csv") 
movies <-data.frame(read.csv(text=x, header=TRUE))
dim(movies)

## [1] 26 13

head(movies, 3)

##      Timestamp         Name Guardians.of.the.Galaxy.2 Wonder.Woman
## 1 2/5/18 16:13          Eva                         0            3
## 2 2/5/18 16:13 Mike Gilbert                         5            5
## 3 2/5/18 16:18         Gino                         5            5
##   Star.Wars..The.Last.Jedi Thor..Ragnarok Blade.Runner.2049
## 1                        4              0                 0
## 2                        5              5                 0
## 3                        4              5                 4
##   Spider.Man..Homecoming Alien..Covenant Ghost.in.the.Shell
## 1                      0               0                  0
## 2                      5               0                  0
## 3                      0               3                  0
##   War.for.the.Planet.of.the.Apes
## 1                              0
## 2                              0
## 3                              3
##   Valerian.and.the.City.of.a.Thousand.Planets Number.of.Movies.Seen
## 1                                           0                     2
## 2                                           0                     5
## 3                                           0                     7

Connect to MySQL and create new DB

library(DBI)
library(RMySQL)
mysql <- dbDriver("MySQL")
moviesdb <- dbConnect(mysql, 
            user=usr,
            password=pw,
            host='localhost')
#on.exit(dbDisconnect(moviesdb))
dbSendQuery(moviesdb, "CREATE DATABASE IF NOT EXISTS movie_reviews;")

## <MySQLResult:0,0,0>

moviesdb <- dbConnect(mysql, 
            user=usr,
            password=pw,
            host='localhost',
            dbname = 'movie_reviews')

Create Tables

CREATE TABLE IF NOT EXISTS movie_reviews.reviewers (
  ID INT NOT NULL AUTO_INCREMENT,
  Name VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`));

CREATE TABLE IF NOT EXISTS movie_reviews.movies (
  ID INT NOT NULL AUTO_INCREMENT,
  movie VARCHAR(65) NOT NULL,
  PRIMARY KEY (`ID`));

CREATE TABLE IF NOT EXISTS movie_reviews.ratings (
  reviewerID INT NOT NULL,
  movieID INT NOT NULL,
  rating INT NOT NULL,
  PRIMARY KEY (reviewerID, movieID),
  FOREIGN KEY (reviewerID) REFERENCES reviewers (ID) ON UPDATE CASCADE,
  FOREIGN KEY (movieID) REFERENCES movies (ID) ON UPDATE CASCADE
  );

Get Data to Insert into Tables

reviewers <- as.character(movies$Name)
moviesNames <- colnames(movies)
moviesNames <- moviesNames[c(3:12)]
#ratings <- movies[3:12]
ratings <- for(rw in 1:26) {
  for(clmn in 1:10){
    c(rw, clmn, movies[rw, clmn+2])
  }
}
reviewers

##  [1] "Eva"                "Mike Gilbert"       "Gino"              
##  [4] "Heather Ahram"      "Diane"              "Anon1"             
##  [7] "Ryan Graziano "     "Mercedes"           "Kerry"             
## [10] "Samantha Esposito"  "Anon2"              "Tushar "           
## [13] "Hugo Walker"        "Cheri"              "Joseph Arminante"  
## [16] "Lorie Honor"        "Katharine rosalen " "Adam"              
## [19] "Chris VA"           "Anon3"              "Anon4"             
## [22] "Michelle"           "Betsy"              "Jody Stoll"        
## [25] "Caroline"           "Brian"

moviesNames

##  [1] "Guardians.of.the.Galaxy.2"                  
##  [2] "Wonder.Woman"                               
##  [3] "Star.Wars..The.Last.Jedi"                   
##  [4] "Thor..Ragnarok"                             
##  [5] "Blade.Runner.2049"                          
##  [6] "Spider.Man..Homecoming"                     
##  [7] "Alien..Covenant"                            
##  [8] "Ghost.in.the.Shell"                         
##  [9] "War.for.the.Planet.of.the.Apes"             
## [10] "Valerian.and.the.City.of.a.Thousand.Planets"

head(ratings, 3)

## NULL

Insert Data into Database Tables

#dbWriteTable(moviesdb, "reviewers", reviewers, overwrite=TRUE)
#dbListTables(moviesdb)
#dbRollback(moviesdb)
#dbRemoveTable(moviesdb.reviewers)

INSERT INTO reviewers (Name)
VALUES  ("Eva"),
        ("Mike Gilbert"),
        ("Gino"),
        ("Heather Ahram"),
        ("Diane"),
        ("Anon1"),
        ("Ryan Graziano"),
        ("Mercedes"),
        ("Kerry"),
        ("Samantha Esposito"),
        ("Anon2"),
        ("Tushar "),
        ("Hugo Walker"),
        ("Cheri"),
        ("Joseph Arminante"),
        ("Lorie Honor"),
        ("Katharine rosalen"),
        ("Adam"),
        ("Chris VA"),
        ("Anon3"),
        ("Anon4"),
        ("Michelle"),
        ("Betsy"),
        ("Jody Stoll"),
        ("Caroline"),
        ("Brian");

SELECT * FROM reviewers;

INSERT INTO movies (movie)
VALUES  ("Guardians of the Galaxy 2"),
        ("Wonder Woman"),
        ("Star Wars The Last Jedi"),
        ("Thor Ragnarok"),
        ("Blade Runner 2049"),
        ("Spider Man Homecoming"),
        ("Alien Covenant"),
        ("Ghost in the Shell"),
        ("War for the Planet of the Apes"),
        ("Valerian and the City of a Thousand Planets");

SELECT * FROM movies;