DBMS Report

Database Management System Project Report

Project by:

• K057 Tejas Sahoo
• K039 Unnat Mishra
• K062 Uday Singh

---

1. Introduction

This project presents a comprehensive relational database schema designed to manage information on games, their developers and publishers, user interactions, and purchase history. Our main objective is to create a well-structured and normalized database that efficiently organizes data for easy retrieval and reduces redundancy. This database is particularly suitable for use in applications or platforms focused on gaming content, such as review sites, digital marketplaces, and leaderboards.

Project Objectives

• Manage Game Information: Store details of games, their developers, publishers, and related media.
• User Interactions: Capture user interactions with games, including reviews, achievements, and purchases.
• Price and Sales Tracking: Record the price history of games to help users make informed decisions about purchases.
• Data Integrity: Ensure data integrity and consistency across the database with normalization and constraints.
• Efficient Data Retrieval: Design the schema to allow efficient retrieval of key information, supporting a variety of queries for different use cases.

Why a Relational Database?

Relational databases provide a robust framework for organizing structured data, making them ideal for applications that require complex relationships and data integrity. With features like foreign keys, constraints, and normalization, a relational database ensures that our data remains consistent and free from unnecessary redundancy.

---

2. ER Diagram and Schema

In this section, we provide an Entity-Relationship (ER) Diagram and a detailed database schema. The ER diagram visualizes the entities and their relationships, while the schema defines each table, its attributes, primary and foreign keys, and constraints.

---

3. Deliverables

The database schema offers the following tables and features to fulfill the project objectives:

1. Games

   • Stores essential information on games, including name, release_date, publisher_id, and developer_id.
   • Enables tracking of each game’s genre and association with developers and publishers.

2. Publishers and Developers

   • Publishers table stores data about the companies responsible for publishing games, such as publisher_name and publisher_country.
   • Developers table captures details of developers, including developer_name, developer_rating, and developer_country.

3. Price History

   • Tracks changes in the price of each game over time, providing information such as price, discount, and timestamp.
   • Helps users view price trends and decide the best time to make purchases.

4. DLC (Downloadable Content)

   • Manages additional content packages associated with each game, including price, currency, and discount.
   • Works with the Purchases table to record which DLC packages users have purchased.

5. User Interactions

   • Reviews: Records user reviews of games, including review_text, review_date, and rating, allowing for detailed user feedback.
   • Achievements: Tracks each user’s achievements in a game, including play_time and achievements_unlocked, supporting a richer user experience.
   • Leaderboard Ranking: Stores ranking information for games, providing insight into game popularity or performance.

6. Sequels and Relationships

   • The SequelGames table establishes relationships between original games and their sequels, allowing users to explore game series.

7. Media Management

   • Stores media associated with games, such as screenshots, video, and trailer links, enhancing the user experience with multimedia elements.

8. Users

   • Manages user information, including username, email, last_login, and created_at, providing a foundation for personalizing interactions within the platform.

9. Purchases

   • Records information on DLC purchases made by users, linking to both Users and DLC tables, to maintain a history of transactions.

---

4. Normalization

Normalization reduces redundancy and ensures that data is organized efficiently. Below, we discuss the normalization levels applied to the schema:

1. First Normal Form (1NF)

• Definition: A table is in 1NF if all columns contain atomic values and each row is unique.
• Application: All tables have atomic values (e.g., publisher_name in Publishers and username in Users). Each table has a primary key to ensure uniqueness.

2. Second Normal Form (2NF)

• Definition: A table is in 2NF if it is in 1NF and every non-key attribute is fully dependent on the primary key.
• Application: Tables with single-column primary keys, like Publishers and Games, satisfy 2NF, as all non-key attributes depend on the primary key. Composite-key tables, such as Achievements, also satisfy 2NF, as non-key attributes depend on the full composite key.

3. Third Normal Form (3NF)

• Definition: A table is in 3NF if it is in 2NF and has no transitive dependencies.
• Application: Each non-key attribute in our schema depends only on the primary key, with no transitive dependencies (e.g., in Reviews, review_text and rating depend only on the primary key composed of user_id and game_id).

4. Boyce-Codd Normal Form (BCNF)

• Definition: BCNF is a stricter version of 3NF, where every determinant is a candidate key.
• Application: All tables meet BCNF standards, as every determinant is a candidate key.

5. Fourth Normal Form (4NF)

• Definition: A table is in 4NF if it is in BCNF and has no multi-valued dependencies.
• Application: By organizing data into tables like GamePlatformAssociation, multi-valued dependencies are avoided, ensuring that each table contains atomic values.

---

5. SQL Queries

This section provides sample SQL queries for retrieving data based on our schema.

Query 1: Retrieve all reviews for a specific game

SELECT Users.username, Reviews.review_text, Reviews.rating
FROM Reviews
JOIN Users ON Reviews.user_id = Users.user_id
WHERE Reviews.game_id = 1;

Query 2: List all games developed by a specific developer

SELECT Games.name
FROM Games
WHERE Games.developer_id = 2;

Query 3: Get leaderboard rankings for all games

SELECT Games.name, LeaderboardRanking.ranking
FROM LeaderboardRanking
JOIN Games ON LeaderboardRanking.game_id = Games.game_id
ORDER BY LeaderboardRanking.ranking ASC;

Query 4: Find the price history for a specific game

SELECT PriceHistory.price, PriceHistory.discount, PriceHistory.timestamp
FROM PriceHistory
WHERE PriceHistory.game_id = 3
ORDER BY PriceHistory.timestamp DESC;

Query 5: List all users who purchased a specific DLC package

SELECT Users.username
FROM Purchases
JOIN Users ON Purchases.user_id = Users.user_id
WHERE Purchases.package_id = 105;

---

6. Example Data for understanding

**