Dylan Pham
Professor Tong
Database Systems (CSCI-3432-01E)
12/3/2025
Course Project (Final Report)
Final Report - Video Game Tracker Database
Introduction
The Video Game Tracker Database is a relational database designed to help users track their
video game collections, progress, platforms, reviews, and wishlists. The motivation behind this
project stems from a personal interest in video games and the desire to combine practical
database management skills with a project that is both engaging and useful.
This project allowed me to apply concepts learned in class, including database normalization,
SQL querying, and data consistency, while building a system that can handle multiple users and
games efficiently. The main components of the project include:
- Users: Profiles for players with personal data.
- Games: Records containing game details, release dates, and descriptions.
- Platforms: Information on gaming platforms (PC, Console, etc.).
- UserGame: Tracks which user plays which game on which platform, along with status
and progress.
- Reviews & Wishlist: User-submitted reviews and wishlist entries for games.
- Genre & Developer: Game classifications and creators.
Since this was a solo project, the focus was entirely on implementing a fully functional database
in MySQL Workbench without the web-based interface bonus option.
Database Details
Entity-Relationship Model
The database consists of core entities including User, Game, Platform, UserGame, Review,
Wishlist, Genre, Developer, and Progress. Many-to-many relationships are managed via bridge
tables such as GamePlatform, GameGenre, and GameDeveloper.
ER Diagram & Schema Highlights:
- Primary keys are defined on all main entities.
- Foreign keys ensure relational integrity.
- All tables are normalized to 3rd Normal Form (3NF), eliminating redundancy and
minimizing update anomalies.
- Optional tables like Wishlist and Progress provide enhanced personalization.
Relational Model and Constraints
- Each table has constraints ensuring data integrity (e.g., UNIQUE, CHECK, NOT NULL).
- UserGame uniquely links users, games, and platforms.
- Functional dependencies rely primarily on primary keys and UNIQUE attributes.
- The database complies with 3NF, ensuring non-key attributes do not depend on other
non-key attributes.
Functionality Details
Basic Functions
The database implements complete CRUD operations:
1. Insertion: Add new users, games, platforms, reviews, and wishlist entries.
2. Searching & Displaying Records: Queries return user collections, reviews, and game
progress.
3. Updating Records: Update game progress, status, or review information.
4. Deleting Records: Remove users or wishlist entries safely with cascading deletes.
5. Advanced Queries:
- JOIN Queries: Combine User, UserGame, Game, Platform, and Review to show full user
activity.
- Aggregate Queries: Compute average ratings, game counts per genre, and other
summaries.
Example SQL Query:
SELECT [Link] AS UserName, [Link] AS GameTitle, [Link] AS Platform, [Link],
[Link], [Link]
FROM UserGame ug
LEFT JOIN Review r ON [Link] = [Link]
JOIN User u ON [Link] = [Link]
JOIN Game g ON [Link] = [Link]
JOIN Platform p ON [Link] = [Link]
ORDER BY [Link], [Link];
Advanced Functions
While this was a solo project and the web-based interface was not implemented, the database
design allows for potential advanced features, including:
- Personalized Game Progress Tracking: Users can track the percentage completion and
hours played per game.
- Aggregate Insights: Queries can calculate average ratings per game, most-played
platforms, and genre popularity.
- Edge Case Handling: Updates and deletes include checks to avoid errors for
non-existent entries.
Implementation Details
- Platform & Language: MySQL Workbench using SQL.
- Front-End: Not implemented (solo project, bonus web interface option skipped).
- Backend Logic: Managed entirely through SQL queries for CRUD and advanced
operations.
- Constraints & Integrity: All tables normalized to 3NF; foreign key constraints enforce
relational integrity; cascading deletes ensure dependent data is properly handled.
Experiences & Reflections
Developing this database provided a deeper understanding of relational database design,
normalization, and SQL querying. Key takeaways include:
- Debugging complex queries strengthened problem-solving skills.
- Implementing cascading deletes and edge case handling ensured data integrity.
- Planning the schema and relationships carefully in the ER model reduced redundancy.
- Solo work emphasized time management and careful prioritization, particularly for live
demos and reports.
Future enhancements could include:
- Adding a web-based interface using Flask or another framework to provide a
user-friendly portal.
- Implementing real-time dashboards showing game statistics and progress.
- Integrating automated recommendations or alerts based on user activity.
References
Tongweitian. “Database Systems.” Professor Tong’s Class Website,
[Link] Accessed 3 Dec. 2025.
SmartDraw. “Entity Relationship Diagram (ER Diagram) Tool.” SmartDraw,
[Link]
BOoq_v2ksDMecDbEmeoNafPND_ZBagTxczJl2FUGX9vrDu2Zj935v. Accessed 3 Dec.
2025.
W3Schools. “MySQL Tutorial.” W3Schools, [Link]
Accessed 3 Dec. 2025.
GeeksforGeeks. “MySQL Tutorial.” GeeksforGeeks,
[Link] Accessed 3 Dec. 2025.
GeeksforGeeks. “How to Install MySQL Workbench on macOS.” GeeksforGeeks,
[Link]
cos/. Accessed 3 Dec. 2025.
Google. General search engine used to locate resources, tutorials, and solutions throughout the
project.