Approach

For this assignment, I plan to collect ratings for seven popular movies from at least five people. Each person will rate the movies they have seen on a scale from 1 to 5. I plan to use PostgreSQL to store the collected data and R to load and analyze the ratings.

I plan to organize the database using separate tables for users, movies, and ratings. The ratings table will connect each user with the movies they rated. If someone has not seen a movie, I will treat the rating as missing rather than giving it a value of zero.

After storing the ratings in PostgreSQL, I will load the data into R as a dataframe and perform simple analysis, such as comparing the number of ratings and average rating for each movie. Some challenges I expect are handling missing ratings, creating the relationships between the SQL tables, and connecting PostgreSQL with R.

Database Design

I organized the survey data into three PostgreSQL tables: users, movies, and ratings. The users table stores anonymous respondent IDs, the movies table stores the movie titles, and the ratings table connects each respondent to the movies they rated.

The ratings table acts as a junction table between users and movies. Each rating is limited to a value from 1 to 5. If a respondent selected “Haven’t Seen,” I did not create a rating row for that movie because it represents missing data rather than a rating of zero.


CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    user_name VARCHAR(50) NOT NULL
);

CREATE TABLE movies (
    movie_id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

CREATE TABLE ratings (
    user_id INTEGER REFERENCES users(user_id),
    movie_id INTEGER REFERENCES movies(movie_id),
    rating INTEGER CHECK (rating BETWEEN 1 AND 5),
    PRIMARY KEY (user_id, movie_id)
);

Populating the Database

The movie ratings were collected using an anonymous Google Forms survey. I received 12 responses. Since no personal information was collected, each response was assigned an anonymous respondent number.

The survey included seven movies. Ratings from 1 to 5 were stored in the ratings table. Responses marked “Haven’t Seen” were not inserted into the ratings table because they represent missing ratings.


INSERT INTO users (user_name)
VALUES
    ('Respondent 1'),
    ('Respondent 2'),
    ('Respondent 3'),
    ('Respondent 4'),
    ('Respondent 5'),
    ('Respondent 6'),
    ('Respondent 7'),
    ('Respondent 8'),
    ('Respondent 9'),
    ('Respondent 10'),
    ('Respondent 11'),
    ('Respondent 12');

INSERT INTO movies (title)
VALUES
    ('Oppenheimer'),
    ('Deadpool & Wolverine'),
    ('Moana 2'),
    ('The Super Mario Bros. Movie'),
    ('Avatar: The Way of Water'),
    ('Top Gun: Maverick'),
    ('Spider-Man: Across the Spider-Verse');

INSERT INTO ratings (user_id, movie_id, rating)
VALUES
    (1, 1, 4),
    (1, 2, 5),
    (1, 3, 3),
    (1, 5, 4),

    (2, 1, 5),
    (2, 2, 4),
    (2, 6, 4),
    (2, 7, 4),

    (3, 1, 5),
    (3, 2, 3),
    (3, 4, 3),
    (3, 5, 2),
    (3, 6, 5),
    (3, 7, 5),

    (4, 3, 3),
    (4, 5, 4),
    (4, 6, 4),
    (4, 7, 5),

    (5, 2, 2),
    (5, 4, 4),
    (5, 5, 5),
    (5, 7, 5),

    (6, 1, 2),
    (6, 5, 2),
    (6, 6, 5),
    (6, 7, 3),

    (7, 2, 4),
    (7, 7, 5),

    (8, 2, 4),
    (8, 3, 3),
    (8, 4, 3),
    (8, 5, 3),
    (8, 7, 4),

    (10, 1, 3),
    (10, 2, 3),
    (10, 3, 3),
    (10, 4, 3),
    (10, 5, 3),
    (10, 6, 3),
    (10, 7, 3),

    (11, 1, 5),
    (11, 2, 1),
    (11, 3, 1),
    (11, 4, 1),
    (11, 5, 1),
    (11, 6, 5),
    (11, 7, 3),

    (12, 6, 5);

Connecting R to PostgreSQL

I used the DBI and RPostgres packages to connect R to the PostgreSQL database. The database password is not stored in this R Markdown file so that private login information is not uploaded to GitHub.

library(DBI)
library(RPostgres)

pg_password <- rstudioapi::askForPassword("Enter PostgreSQL password")

con <- dbConnect(
  RPostgres::Postgres(),
  dbname = "movie_ratings",
  host = "localhost",
  port = 5432,
  user = "postgres",
  password = pg_password
)

Loading SQL Data into R

I used a SQL query from R to combine the ratings, users, and movies tables. This creates a dataframe with the respondent name, movie title, and rating.

SELECT
    u.user_name,
    m.title,
    r.rating
FROM ratings r
JOIN users u
    ON r.user_id = u.user_id
JOIN movies m
    ON r.movie_id = m.movie_id
ORDER BY
    u.user_id,
    m.movie_id;
movie_data <- read.csv(
  "https://raw.githubusercontent.com/Afwans/DATA607-Assignment2A/main/movie_ratings_data.csv"
)

Analyzing Movie Ratings in R

I used the dplyr package to summarize the movie ratings. For each movie, I calculated the number of ratings and the average rating.

library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
movie_summary <- movie_data %>%
  group_by(title) %>%
  summarise(
    number_of_ratings = n(),
    average_rating = round(mean(rating), 2)
  ) %>%
  arrange(desc(average_rating))

movie_summary
## # A tibble: 7 × 3
##   title                               number_of_ratings average_rating
##   <chr>                                           <int>          <dbl>
## 1 Top Gun: Maverick                                   7           4.43
## 2 Spider-Man: Across the Spider-Verse                 9           4.11
## 3 Oppenheimer                                         6           4   
## 4 Deadpool & Wolverine                                8           3.25
## 5 Avatar: The Way of Water                            8           3   
## 6 The Super Mario Bros. Movie                         5           2.8 
## 7 Moana 2                                             5           2.6

Handling Missing Ratings

Respondents were allowed to select “Haven’t Seen” instead of giving a movie a rating. I treated these responses as missing ratings rather than assigning them a value of zero. A zero would incorrectly lower a movie’s average even though the respondent did not actually rate the movie.

There were 12 respondents and 7 movies, so there were 84 possible ratings. I compared the number of actual ratings with the total possible ratings to determine how many responses were missing.

total_possible_ratings <- 12 * 7
actual_ratings <- nrow(movie_data)
missing_ratings <- total_possible_ratings - actual_ratings

total_possible_ratings
## [1] 84
actual_ratings
## [1] 48
missing_ratings
## [1] 36

Visualization

To make the results easier to compare, I created a bar chart showing the average rating for each movie.

library(ggplot2)

ggplot(
  movie_summary,
  aes(
    x = reorder(title, average_rating),
    y = average_rating,
    fill = title
  )
) +
  geom_col() +
  coord_flip() +
  labs(
    title = "Average Movie Ratings",
    x = "Movie",
    y = "Average Rating"
  ) +
  ylim(0, 5) +
  theme_minimal() +
  theme(
    legend.position = "none",
    plot.title = element_text(
      size = 16,
      face = "bold"
    )
  )

Findings and Conclusion

The survey collected responses from 12 anonymous participants across seven movies. There were 84 possible ratings, but only 48 actual ratings because respondents could select “Haven’t Seen.” The remaining 36 responses were treated as missing ratings and were not given a value of zero.

Based on the collected ratings, Top Gun: Maverick had the highest average rating at 4.43, followed by Spider-Man: Across the Spider-Verse at 4.11 and Oppenheimer at 4.00. Moana 2 had the lowest average rating at 2.60. The number of ratings also varied between movies, so the averages should be considered together with the number of people who rated each movie.

This assignment demonstrated how survey data can be organized in a relational PostgreSQL database and then loaded into R for analysis. Using separate users, movies, and ratings tables made the data easier to organize while also allowing missing ratings to be handled without affecting the movie averages.

dbDisconnect(con)