The goal of this assignment is to collect a small dataset of movie ratings from different users, store the data in a PostgreSQL database, and analyze it using R. This project demonstrates the complete process of collecting data, storing it in a relational database, querying the data, and bringing it into R for analysis. It also shows how missing movie ratings can be handled appropriately.
Responses will be collected in a simple format (e.g., a small table) and then inserted into PostgreSQL.
DBI,
RPostgres, tidyverseSecurity Note: Database passwords will not be included in the code. Connection credentials will be stored using environment variables or masked placeholders.
Database Design (Normalized Schema)
To keep the solution professional and interview-ready, I will use a normalized relational schema to represent the many-to-many relationship between users and movies.
Planned tables:
users(user_id, name)movies(movie_id, title, release_year)ratings(rating_id, user_id, movie_id, rating) `Keys and Constraints (Planned)
users.user_id and movies.movie_id will be
primary keysratings.user_id and ratings.movie_id will
be foreign keys(user_id, movie_id) will prevent duplicate ratingsThis structure mirrors how real recommendation datasets are stored: users and movies are separate entities, and ratings are stored in a junction table.
Missing ratings are expected because participants may not have seen every movie.
I will handle missing data in two ways:
NULL (or omitted rows if a participant did not rate an
item).NA. Summary statistics (means, medians) will be
computed with na.rm = TRUE to avoid bias from
missingness.To document missingness, I will report:
After the database is populated, I will connect PostgreSQL to R using DBI and RPostgres. I will then use R to:
-Combine the users, movies, and ratings tables into one tidy
dataset.
-Calculate basic summaries, including:
-The average
rating for each movie along with the number of ratings.
-The average
rating given by each user.
-The overall distribution of ratings from
1 to 5.
-Optionally, convert the data into a user–item matrix in
wide format to show how the dataset can be structured for collaborative
filtering and recommendation systems. An advanced recommendation model
is not required for this assignment.
Even if I use pgAdmin to run queries, I will include the full SQL scripts required to:
CREATE TABLE)INSERT INTO)SELECT ... JOIN ...)All code (SQL + R + Quarto) will be stored in a GitHub repository for submission, with sensitive credentials removed or masked.
library(DBI)
library(RPostgres)
library(dplyr)
library(tidyr)
con <- dbConnect(
RPostgres::Postgres(),
host = "localhost",
port = 5432,
dbname = "postgres",
user = "postgres",
password = "admin"
)
dbListTables(con) [1] "movies" "ratings" "users" users_df <- dbGetQuery(con, "SELECT * FROM users;")
movies_df <- dbGetQuery(con, "SELECT * FROM movies;")
ratings_df <- dbGetQuery(con, "SELECT * FROM ratings;")ratings_joined <- ratings_df %>%
left_join(users_df, by = "user_id") %>%
left_join(movies_df, by = "movie_id") %>%
select(name, title, release_year, rating) %>%
arrange(name, title)
ratings_joined name title release_year rating
1 Anush Avatar The Way of Water 2022 4
2 Anush Dune Part Two 2024 4
3 Anush Lilo & Stitch 2025 4
4 Anush The Batman 2022 3
5 Arpi Barbie 2023 4
6 Arpi Lilo & Stitch 2025 5
7 Arpi The Holdovers 2023 5
8 Das Avatar The Way of Water 2022 5
9 Das Barbie 2023 3
10 Das The Holdovers 2023 4
11 Mihir Avatar The Way of Water 2022 5
12 Mihir Dune Part Two 2024 3
13 Mihir The Batman 2022 4
14 Mihir The Holdovers 2023 5
15 Prakash Dune Part Two 2024 5
16 Prakash Lilo & Stitch 2025 4
17 Prakash The Holdovers 2023 5Demonstrate missing ratings handling (Part 1)
ratings_complete <- users_df %>%
crossing(movies_df) %>%
left_join(ratings_df, by = c("user_id", "movie_id"))
ratings_complete# A tibble: 30 × 7
user_id name movie_id title release_year rating_id rating
<int> <chr> <int> <chr> <int> <int> <int>
1 1 Prakash 1 Dune Part Two 2024 1 5
2 1 Prakash 2 The Holdovers 2023 2 5
3 1 Prakash 3 Barbie 2023 NA NA
4 1 Prakash 4 The Batman 2022 NA NA
5 1 Prakash 5 Lilo & Stitch 2025 3 4
6 1 Prakash 6 Avatar The Way of Wat… 2022 NA NA
7 2 Das 1 Dune Part Two 2024 NA NA
8 2 Das 2 The Holdovers 2023 4 4
9 2 Das 3 Barbie 2023 5 3
10 2 Das 4 The Batman 2022 NA NA
# ℹ 20 more rowsDemonstrate missing ratings handling (Part 2)
ratings_complete %>%
summarise(
total_possible = n(),
missing_ratings = sum(is.na(rating)),
observed_ratings = sum(!is.na(rating))
)# A tibble: 1 × 3
total_possible missing_ratings observed_ratings
<int> <int> <int>
1 30 13 17ratings_complete %>%
group_by(title) %>%
summarise(avg_rating = mean(rating, na.rm = TRUE),
n_ratings = sum(!is.na(rating)))# A tibble: 6 × 3
title avg_rating n_ratings
<chr> <dbl> <int>
1 Avatar The Way of Water 4.67 3
2 Barbie 3.5 2
3 Dune Part Two 4 3
4 Lilo & Stitch 4.33 3
5 The Batman 3.5 2
6 The Holdovers 4.75 4This assignment demonstrates how relational databases and R can be used together in a data science workflow. PostgreSQL provides structured data storage and helps maintain data integrity through constraints, while R is used for data manipulation, handling missing values, and performing analysis. The final dataset is organized in a user–item ratings format, which is commonly used in recommendation systems.