DATA 607 assignment 2A:SQL and R
Lionel jr. desroses
2026-09-05
Introduction
The purpose of this project is to collect and analyze movie ratings using a PostgreSQL database and R. Six recent movies were selected. Six participants were asked to rate each movie they had seen on a scale from 1 to 5, where 1 represents “strongly dislike” and 5 represents “strongly like.” The collected ratings were stored in a normalized PostgreSQL database consisting of three tables: users, movies, and ratings. This design demonstrates the use of primary keys, foreign keys, and many-to-many relationships. After the data was stored in PostgreSQL, it was imported into R for analysis. Using packages such as dplyr and ggplot2, summary statistics and visualizations were created to identify trends in movie ratings and movie popularity. Missing ratings were treated as movies that participants had not seen and were tracked separately during the analysis.
con <- dbConnect(
RPostgres::Postgres(),
dbname = "LDATA607",
host = "localhost",
port = 5433,
user = "postgres",
)postgreSQL database creation and data population
The database for this project was created manually in PostgreSQL. Three tables were created: users, movies, and ratings. The users table stores participant information, the movies table stores movie information, and the ratings table stores individual movie ratings.Data was collected directly from six participants and entered into the database manually. The ratings table links users and movies through foreign key relationships, allowing each user to rate multiple movies and each movie to receive ratings from multiple users.
--create table users(users_id Serial PRIMARY KEY, users_name VARCHAR(50)NOT NULL);
--create table movies (movies_id serial primary key, movie_title varchar(100)not null, genres varchar(50), release_year integer);
--create table ratings (rating_id serial primary key, user_id integer not null,movies_id integer not null, rating integer check (rating between 1 and 5), constraint fk_user FOREIGN key(user_id)references users(users_id),constraint fk_movie FOREIGN key(movies_id)references movies(movies_id))
--select * from users;
--insert into movies (movies_id, movie_title,genres,release_year)values(6,'Minions & Monsters','Comedy/Adventure',2026);
--select * from movies;
--insert into users (users_name) VALUES ('MIKE'),('paul'),('zege'),('aly'),('church');
--select * from ratings;
--INSERT INTO ratings (user_id, movies_id, rating)VALUES(1,1,5),(1,2,4),(1,3,5), (2,1,4) ,(2,2,5),(2,5,3),(3,1,5),(3,4,4),(3,6,5),(4,1,5),(4,3,1),(5,1,5),(5,6,5),(6,3,5);
--INSERT INTO ratings (user_id, movies_id, rating) VALUES (1,4,NULL), (1,5,NULL), (1,6,3),(2,3,NULL), (2,4,NULL), (2,6,NULL),(3,2,NULL), (3,5,NULL),(4,2,NULL),(4,4,NULL),(4,5,2), (4,6,NULL), (5,2,NULL), (5,3,3), (5,4,NULL), (5,5,5),(6,1,5),(6,2,NULL),(6,4,NULL),(6,5,4), (6,6,NULL);
--SELECT
u.users_name,
m.movie_title,
r.rating
FROM ratings r
JOIN users u
ON r.user_id = u.users_id
JOIN movies m
ON r.movies_id = m.movies_id
ORDER BY u.users_name, m.movie_title;
--select * from ratings;
importing data into R
In this section, the data stored in PostgreSQL is imported into R using the RPostgres package. Separate queries were used to retrieve the users, movies, and ratings tables. The ratings data was joined with the users and movies tables to create a single dataframe for analysis. Missing ratings were converted into a “Not Seen” category to better represent movies that participants had not watched. To improve reproducibility, the final dataframe was exported to a CSV file and included in the GitHub repository.
users_df<-dbGetQuery(con,"select * from users")
users_df## users_id users_name
## 1 1 lionel
## 2 2 MIKE
## 3 3 paul
## 4 4 zege
## 5 5 aly
## 6 6 churchmovies_df <-dbGetQuery(con,"select * from movies;")
movies_df ## movies_id movie_title genres release_year
## 1 1 spider man brand new day Action/Adventure 2026
## 2 2 The Odyssey Action/Fantasy 2026
## 3 3 Toy Story 5 Comedy/Adventure 2026
## 4 4 The Super Mario Galaxy Movie Family/Adventure 2026
## 5 5 Michael Musical/Music 2026
## 6 6 Minions & Monsters Comedy/Adventure 2026ratings_df<- dbGetQuery(con,
"SELECT
u.users_name,
m.movie_title,
r.rating
FROM ratings r
JOIN users u
ON r.user_id = u.users_id
JOIN movies m
ON r.movies_id = m.movies_id
ORDER BY u.users_name, m.movie_title;")
ratings_df <- ratings_df %>%
mutate(
rating_status = ifelse(is.na(rating),
"Not Seen",
as.character(rating))
)
write.csv(
ratings_df,
"movie_ratings.csv",
row.names = FALSE
)
ratings_df## users_name movie_title rating rating_status
## 1 aly Michael 5 5
## 2 aly Michael 5 5
## 3 aly Minions & Monsters 5 5
## 4 aly spider man brand new day 5 5
## 5 aly The Odyssey NA Not Seen
## 6 aly The Odyssey NA Not Seen
## 7 aly The Super Mario Galaxy Movie NA Not Seen
## 8 aly The Super Mario Galaxy Movie NA Not Seen
## 9 aly Toy Story 5 3 3
## 10 aly Toy Story 5 3 3
## 11 church Michael 4 4
## 12 church Michael 4 4
## 13 church Minions & Monsters NA Not Seen
## 14 church Minions & Monsters NA Not Seen
## 15 church spider man brand new day 5 5
## 16 church spider man brand new day 5 5
## 17 church The Odyssey NA Not Seen
## 18 church The Odyssey NA Not Seen
## 19 church The Super Mario Galaxy Movie NA Not Seen
## 20 church The Super Mario Galaxy Movie NA Not Seen
## 21 church Toy Story 5 5 5
## 22 lionel Michael NA Not Seen
## 23 lionel Michael NA Not Seen
## 24 lionel Minions & Monsters 3 3
## 25 lionel Minions & Monsters 3 3
## 26 lionel spider man brand new day 5 5
## 27 lionel The Odyssey 4 4
## 28 lionel The Super Mario Galaxy Movie NA Not Seen
## 29 lionel The Super Mario Galaxy Movie NA Not Seen
## 30 lionel Toy Story 5 5 5
## 31 MIKE Michael 3 3
## 32 MIKE Minions & Monsters NA Not Seen
## 33 MIKE Minions & Monsters NA Not Seen
## 34 MIKE spider man brand new day 4 4
## 35 MIKE The Odyssey 5 5
## 36 MIKE The Super Mario Galaxy Movie NA Not Seen
## 37 MIKE The Super Mario Galaxy Movie NA Not Seen
## 38 MIKE Toy Story 5 NA Not Seen
## 39 MIKE Toy Story 5 NA Not Seen
## 40 paul Michael NA Not Seen
## 41 paul Michael NA Not Seen
## 42 paul Minions & Monsters 5 5
## 43 paul spider man brand new day 5 5
## 44 paul The Odyssey NA Not Seen
## 45 paul The Odyssey NA Not Seen
## 46 paul The Super Mario Galaxy Movie 4 4
## 47 zege Michael 2 2
## 48 zege Michael 2 2
## 49 zege Minions & Monsters NA Not Seen
## 50 zege Minions & Monsters NA Not Seen
## 51 zege spider man brand new day 5 5
## 52 zege The Odyssey NA Not Seen
## 53 zege The Odyssey NA Not Seen
## 54 zege The Super Mario Galaxy Movie NA Not Seen
## 55 zege The Super Mario Galaxy Movie NA Not Seen
## 56 zege Toy Story 5 1 1Summary Statistic
In this section, I created a multiple summary table of the Data that was collected to get a view and a better understanding of the rating of the movies. The first summary table calculates the average rating for each movie. The second summary table counts the number of participants who had not seen each movie. Finally, a combined summary table was created to show average ratings, the number of ratings received, and the number of “Not Seen” responses for each movie. These summaries provide a clearer understanding of both movie popularity and movie exposure among participants.
movie_summary <- ratings_df %>%
group_by(movie_title) %>%
summarize(
avg_rating = mean(rating,na.rm=TRUE),
num_ratings = n()
)
movie_summary## # A tibble: 6 × 3
## movie_title avg_rating num_ratings
## <chr> <dbl> <int>
## 1 Michael 3.57 11
## 2 Minions & Monsters 4 10
## 3 The Odyssey 4.5 10
## 4 The Super Mario Galaxy Movie 4 11
## 5 Toy Story 5 3.4 7
## 6 spider man brand new day 4.86 7not_seen_summary <- ratings_df%>%
group_by(movie_title) %>%
summarize(
not_seen = sum(is.na(rating))
)
not_seen_summary## # A tibble: 6 × 2
## movie_title not_seen
## <chr> <int>
## 1 Michael 4
## 2 Minions & Monsters 6
## 3 The Odyssey 8
## 4 The Super Mario Galaxy Movie 10
## 5 Toy Story 5 2
## 6 spider man brand new day 0movie_summary_wna <- ratings_df %>%
group_by(movie_title) %>%
summarize(
avg_rating = mean(rating,na.rm=TRUE),
rating_received = sum(!is.na(rating)),
num_ratings = n(),
not_seen = sum(is.na(rating))
)
movie_summary_wna## # A tibble: 6 × 5
## movie_title avg_rating rating_received num_ratings not_seen
## <chr> <dbl> <int> <int> <int>
## 1 Michael 3.57 7 11 4
## 2 Minions & Monsters 4 4 10 6
## 3 The Odyssey 4.5 2 10 8
## 4 The Super Mario Galaxy Movie 4 1 11 10
## 5 Toy Story 5 3.4 5 7 2
## 6 spider man brand new day 4.86 7 7 0Data Visualizations
in this session, Two visualizations were created using ggplot2. The first chart displays the average rating received by each movie. This visualization makes it easy to compare overall movie performance and identify the highest-rated movies. The second chart displays the number of participants who had not seen each movie. This provides additional insight into movie familiarity and helps explain differences in the number of ratings received for each movie.
ggplot(movie_summary,
aes(x = reorder(movie_title, avg_rating),
y = avg_rating)) +
geom_col(fill = "steelblue") +
coord_flip() +
labs(
title = "Average Movie Ratings",
x = "Movie",
y = "Average Rating"
)
ggplot(movie_summary_wna,
aes(x = reorder(movie_title, not_seen),
y = not_seen)) +
geom_col(fill = "tomato") +
coord_flip() +
labs(
title = "Number of Participants Who Have Not Seen Each Movie",
x = "Movie",
y = "Not Seen Count"
) +
theme_minimal()
conclusion
in conclusion, This project demonstrated how PostgreSQL and R can be used together to collect, store, analyze, and visualize data. A normalized database structure was created using separate users, movies, and ratings tables. The data was then imported into R, where summary statistics and visualizations were generated to evaluate movie ratings and identify viewing trends among participants. One challenge addressed during the project was handling missing ratings. Movies that participants had not seen were stored as NULL values in PostgreSQL and tracked as “Not Seen” responses in R. This approach allowed missing data to be incorporated into the analysis without affecting average rating calculations.
dbDisconnect(con)