AssignmentQuestion

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.

Data gathering methodology used

The author created the form shown below using google spreadsheet. The form was then printed and given to members of her immediate household to be completed. The data was collected and stored in a database named MoviesTable. The database was created using MySQL workbench.

Image of Form

Image of Form

#load package to enable connection between R and the database
library(RMySQL)
## Warning: package 'RMySQL' was built under R version 3.4.4
## Loading required package: DBI
## Warning: package 'DBI' was built under R version 3.4.4
#establish connection between R and database
conn <- DBI::dbConnect(RMySQL::MySQL(), dbname = "MoviesTable", user="root", password="macisawesome")

# List the name/s of table/s in database.This database has only one table
dbListTables(conn)
## [1] "Reviewer"

Dataframe

#Load data from database table into a dataframe
rs = dbSendQuery(conn, "select * from Reviewer")
data = fetch(rs, n=-1)
data
##   idReviewer Table Reviewer name Black Panther Oceans 8 The Predator
## 1                1     Juanelle              5        3            4
## 2                2        Martin             5        5            5
## 3                3       Elander             5        4            5
## 4                4       Brianna             5        3            3
## 5                5         Breon             5        2            3
## 6                6         Tracy             5        4            3
##   Infinity War DeadPool Mile22
## 1            5        2      2
## 2            5        4      4
## 3            5        3      3
## 4            5        2      1
## 5            5        1      1
## 6            5        2      1

Data Dictionary

  1. idReviewerTable: index
  2. Reviewer name: first names of persons who completed the form
  3. Black Panther: integer values ranging from 1 to 5 indicating how many stars the movie was rated by the person.
  4. Oceans 8: integer values ranging from 1 to 5 indicating how many stars the movie was rated by the person.
  5. The Predator:integer values ranging from 1 to 5 indicating how many stars the movie was rated by the person.
  6. Infinity War:integer values ranging from 1 to 5 indicating how many stars the movie was rated by the person.
  7. Dead Pool:integer values ranging from 1 to 5 indicating how many stars the movie was rated by the person.
  8. Mile22:integer values ranging from 1 to 5 indicating how many stars the movie was rated by the person.