Introduction
This database project is related to the video game industry and was completed for my database design and modeling class in college. The database consists of many different categories that are listed below. The problem domain comes down to the environments of my target audience. The target audience for this database is gamers, gaming enthusiasts, gaming influencers, developers, gaming companies, and bloggers. The target audience needs a way to obtain certain information relating to video games. I think this database fulfills that need for them.
Database Description
The database tracks the top 25 games for the Xbox Series X (Xbox), Playstation 5 (PS5), Personal Computer (PC), and Nintendo Switch during 2021. The categories that I was most interested in are the genres, the number of sales per game, the individual publishers, the languages that the game is available to play in, and whether or not the game is cross-system compatible.
Logical Design
The ERD was instrumental in developing the database and building relationships between tables, their columns, and their data. I started with the game table and have four tables that bridge off of it; genre, game_platform, sales, and game_language. I created two linking tables to be able to join data and keep data integrity; game_language and game_platform. These linking tables relate back to game on game_id so that I could build the relationships. I also have four tables containing data that I thought was important to include in the database; the game publisher, languages, the game system, and genre. Each of these tables has its own respective id column to identify what language, game publisher, game system, or genre the game is or has.
Physical Database
To create the database and the tables that it holds, I used the EER diagram tool within MySQL Workbench. Once I created a working ERD model, I forward-engineered it to create the database.
To populate the database and tables, I first made separate spreadsheets with all of the data that we collected for each table. I then downloaded the spreadsheets via CSV files and used the data import wizard to import the data into the database and respective tables.
Queries testing the database
Filtering
DROP VIEW IF EXISTS games_under_8mil_sales;
CREATE VIEW games_under_8mil_sales AS
SELECT game_name AS "Title of the Game", num_sales_in_millions AS "Number of
Game Sales in Millions"
FROM game JOIN sales USING(game_id)
WHERE num_sales_in_millions < 8.00;
SELECT * FROM games_under_8mil_sales;
DROP VIEW IF EXISTS games_made_after_2019;
CREATE VIEW games_made_after_2019 AS
SELECT game_name AS "Title of the Game", release_year AS "The Year the Game
was Released", publisher_name AS "The Name of the Publisher Who Made the Game"
FROM game JOIN game_platforms USING(game_id) JOIN publisher
USING(publisher_id)
WHERE release_year > 2019
ORDER BY release_year DESC;
SELECT * FROM games_made_after_2019;
Aggregation
DROP VIEW IF EXISTS average_sales_per_system; CREATE VIEW average_sales_per_system AS SELECT system_name AS "The Game System Name", AVG(num_sales_in_millions) AS "The Number of Sales (in millions)" FROM game_system JOIN game_platforms USING(system_id) JOIN sales USING(game_id) GROUP BY system_id; SELECT * FROM average_sales_per_system;
Thank you for reading about my video game database project!
Comments