🪴 TJ's Notes 1.0

DBMS Project Schema

11 min read

Based on the diagram provided, here is a schema that represents the entities and relationships involved:

-- Publishers Table
CREATE TABLE Publishers (
    publisher_id INT PRIMARY KEY,
    publisher_name VARCHAR(255),
    publisher_country VARCHAR(255)
);
 
-- Developers Table
CREATE TABLE Developers (
    developer_id INT PRIMARY KEY,
    developer_name VARCHAR(255),
    developer_rating FLOAT,
    developer_country VARCHAR(255)
);
 
-- Games Table
CREATE TABLE Games (
    game_id INT PRIMARY KEY,
    name VARCHAR(255),
    release_date DATE,
    publisher_id INT,
    developer_id INT,
    genre_id INT,
    FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id),
    FOREIGN KEY (developer_id) REFERENCES Developers(developer_id)
);
 
-- Media Table
CREATE TABLE Media (
    game_id INT,
    screenshots CLOB,
    video CLOB,
    trailer CLOB,
    FOREIGN KEY (game_id) REFERENCES Games(game_id)
);
 
-- Game Platform Table
CREATE TABLE GamePlatform (
    game_platform_id INT PRIMARY KEY,
    platform_name VARCHAR(255),
    controller_support BOOLEAN
);
 
-- Price History Table
CREATE TABLE PriceHistory (
    pricelist_id INT PRIMARY KEY,
    package_id INT,
    game_id INT,
    price DECIMAL(10, 2),
    discount DECIMAL(5, 2),
    timestamp TIMESTAMP,
    FOREIGN KEY (game_id) REFERENCES Games(game_id)
);
 
-- DLC Table
CREATE TABLE DLC (
    package_id INT PRIMARY KEY,
    game_id INT,
    price DECIMAL(10, 2),
    currency VARCHAR(10),
    discount DECIMAL(5, 2),
    last_updated TIMESTAMP,
    FOREIGN KEY (game_id) REFERENCES Games(game_id)
);
 
-- Users Table
CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255),
    password_hash VARCHAR(255),
    last_login TIMESTAMP,
    created_at TIMESTAMP
);
 
-- Reviews Table
CREATE TABLE Reviews (
    review_id INT PRIMARY KEY,
    user_id INT,
    game_id INT,
    review_text TEXT,
    review_date DATE,
    rating DECIMAL(3, 2),
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (game_id) REFERENCES Games(game_id)
);
 
-- Additional Relationships
-- Sequel Game
CREATE TABLE SequelGames (
    original_game_id INT,
    sequel_game_id INT,
    FOREIGN KEY (original_game_id) REFERENCES Games(game_id),
    FOREIGN KEY (sequel_game_id) REFERENCES Games(game_id)
);
 
-- Leaderboard Ranking
CREATE TABLE LeaderboardRanking (
    game_id INT,
    ranking INT,
    FOREIGN KEY (game_id) REFERENCES Games(game_id)
);
 
-- Purchases Table
CREATE TABLE Purchases (
    user_id INT,
    package_id INT,
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (package_id) REFERENCES DLC(package_id)
);
 
-- Achievements Table
CREATE TABLE Achievements (
    user_id INT,
    game_id INT,
    play_time DECIMAL(5, 2),
    achievements_unlocked INT,
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (game_id) REFERENCES Games(game_id)
);

Explanation:

  1. Publishers: Contains details about the publisher of games, including their name and country.
  2. Developers: Stores the details of developers, including their name, rating, and country.
  3. Games: Contains the information about each game, linking to publishers and developers.
  4. Media: Stores media associated with games, such as screenshots, videos, and trailers.
  5. Game Platform: Contains platform details for games, such as the platform name and whether controller support is available.
  6. PriceHistory: Stores price history for each game, including the timestamp, price, and discount details.
  7. DLC: Stores downloadable content (DLC) information for games, including the price, currency, and discount.
  8. Users: Stores user information, including their username, email, and account creation and login timestamps.
  9. Reviews: Stores user reviews for games, including the review text, rating, and review date.
  10. SequelGames: Creates a relationship between original games and their sequels.
  11. LeaderboardRanking: Stores leaderboard ranking information for each game.
  12. Purchases: Stores information about the purchases made by users for different DLC packages.
  13. Achievements: Stores user achievements, including the playtime and number of achievements unlocked for each game.

Let me know if you need further adjustments or more details!

Normalization is a systematic approach to organizing databases to reduce redundancy and ensure data integrity. This database schema has been designed in such a way that it meets several normal forms, helping to eliminate data anomalies. Let’s go through the normal forms and see how they apply to your schema:

1. First Normal Form (1NF)

Definition: A table is in 1NF if:

  • All columns contain atomic (indivisible) values.
  • Each record (row) is unique.

Application to Schema:

  • Each column in all tables holds atomic values. For example, columns like publisher_name in Publishers and username in Users only contain single, indivisible values.
  • All rows are unique, ensured by primary keys (e.g., publisher_id in Publishers and game_id in Games).
  • The Media table has individual columns (screenshots, video, and trailer) for media types, which avoids storing multiple URLs or file paths in a single column.

So, the schema meets the requirements of 1NF.

2. Second Normal Form (2NF)

Definition: A table is in 2NF if:

  • It is already in 1NF.
  • Every non-key attribute is fully functionally dependent on the entire primary key.

Note: 2NF is relevant when a table has a composite primary key. If a table has a single-column primary key, it is automatically in 2NF if it is in 1NF.

Application to Schema:

  • In this schema, tables with single-column primary keys, such as Publishers, Developers, and Games, meet 2NF because there’s no partial dependency (i.e., each non-key attribute depends entirely on the primary key).
  • In tables where the primary key consists of multiple columns (e.g., Purchases and Achievements), each non-key attribute depends on the full primary key. For example, in Achievements, play_time and achievements_unlocked depend on both user_id and game_id, ensuring 2NF.

Thus, the schema satisfies 2NF.

3. Third Normal Form (3NF)

Definition: A table is in 3NF if:

  • It is in 2NF.
  • It has no transitive dependencies, meaning no non-key attribute depends on another non-key attribute.

Application to Schema:

  • In each table, all non-key attributes depend only on the primary key and not on other non-key attributes. For example:
    • In the Games table, name, release_date, publisher_id, developer_id, and genre_id depend only on game_id.
    • In the PriceHistory table, price, discount, and timestamp depend only on pricelist_id, which is the primary key for this table.
  • There are no transitive dependencies within any table. For example, in Publishers, each attribute (publisher_name and publisher_country) depends directly on publisher_id with no intermediate dependency.

Therefore, the schema meets 3NF requirements.

4. Boyce-Codd Normal Form (BCNF)

Definition: A table is in BCNF if:

  • It is in 3NF.
  • Every determinant is a candidate key (a minimal superkey).

Application to Schema:

  • In most of the tables, each attribute is functionally dependent only on the primary key, making them compliant with BCNF.
  • However, in cases like Purchases (where both user_id and package_id are part of the primary key), each attribute is fully dependent on both columns, ensuring BCNF is satisfied.
  • Similarly, in Achievements, both user_id and game_id serve as a composite primary key, and all other columns depend entirely on this combination.

So, the schema satisfies BCNF as well.

5. Fourth Normal Form (4NF)

Definition: A table is in 4NF if:

  • It is in BCNF.
  • It has no multi-valued dependencies, meaning a non-key attribute cannot have multiple independent values associated with a single key value.

Application to Schema:

  • In this schema, each table avoids multi-valued dependencies. For example:
    • The Media table has separate columns for different media types (screenshots, video, trailer), so there’s no need to store multiple values in a single column.
    • Each game in the Games table is associated with a single publisher_id, developer_id, and genre_id, avoiding any multi-valued dependency.
  • Since each table has been designed to have atomic values in each column, 4NF is satisfied.

6. Fifth Normal Form (5NF)

Definition: A table is in 5NF (also known as Project-Join Normal Form) if:

  • It is in 4NF.
  • It cannot be further decomposed without losing information.

Application to Schema:

  • This form is typically applied when there are complex many-to-many relationships. In this schema, each many-to-many relationship is handled through separate tables with well-defined relationships.
  • For example, SequelGames manages the many-to-many relationship of sequels by referencing Games, while Purchases and Achievements tables define the relationships between Users and Games or DLC.

Since each relationship is defined in a separate table without losing information, the schema meets the requirements of 5NF.

Summary of Normalization Levels in This Schema

  • 1NF: All tables have atomic values and unique rows.
  • 2NF: All non-key attributes depend on the entire primary key.
  • 3NF: No transitive dependencies are present; all non-key attributes depend only on the primary key.
  • BCNF: Each determinant is a candidate key.
  • 4NF: No multi-valued dependencies exist in any table.
  • 5NF: Tables cannot be decomposed further without losing data.

Additional Note on Denormalization

While this schema is well-normalized, in real-world applications, sometimes denormalization is done to optimize for performance, especially for complex queries. Denormalization could mean combining certain tables or duplicating some data to speed up data retrieval at the cost of slightly increased storage or potential data redundancy.

Let me know if you have questions about any specific normal form or normalization concept!

Here’s a sample SQL script to populate the database with entries for Users, Games, Reviews, Media, Purchases, SequelGames, and LeaderboardRanking as per your request. This includes creating random usernames, reviews, and placeholder links for media.

SQL Script to Populate Data

-- Insert Users
INSERT INTO Users (user_id, username, email, password_hash, last_login, created_at) VALUES
(1, 'SpicyPotatoCat', '[email protected]', 'hashed_password1', '2023-10-15 10:05:00', '2022-01-10 08:30:00'),
(2, 'Unnat', '[email protected]', 'hashed_password2', '2023-10-12 15:25:00', '2021-11-20 13:40:00'),
(3, 'Uday07', '[email protected]', 'hashed_password3', '2023-09-25 18:45:00', '2021-10-05 17:55:00'),
(4, 'TJ', '[email protected]', 'hashed_password4', '2023-10-20 09:10:00', '2022-06-15 14:00:00'),
(5, 'ZanyGamer', '[email protected]', 'hashed_password5', '2023-08-18 12:30:00', '2021-09-18 08:15:00'),
(6, 'PixelPhantom', '[email protected]', 'hashed_password6', '2023-07-29 11:50:00', '2022-05-12 19:20:00'),
(7, 'EpicWin95', '[email protected]', 'hashed_password7', '2023-10-07 16:45:00', '2021-08-01 15:00:00');
 
-- Insert Games
INSERT INTO Games (game_id, name, release_date, publisher_id, developer_id, genre_id) VALUES
(1, 'God of War 2018', '2018-04-20', 1, 1, 1),
(2, 'God of War Ragnarok', '2022-11-09', 1, 1, 1),
(3, 'Doom Eternal', '2020-03-20', 2, 2, 2),
(4, 'Call of Duty Modern Warfare', '2019-10-25', 3, 3, 3),
(5, 'Call of Duty Black Ops', '2010-11-09', 3, 3, 3),
(6, 'GTAV', '2013-09-17', 4, 4, 4),
(7, 'Stray', '2022-07-19', 5, 5, 5),
(8, 'Resident Evil Village', '2021-05-07', 6, 6, 6),
(9, 'FIFA 21', '2020-10-09', 7, 7, 7),
(10, 'NBA 2K21', '2020-09-04', 8, 8, 8),
(11, 'Battlefield V', '2018-11-20', 9, 9, 9),
(12, 'Among Us', '2018-11-16', 10, 10, 10),
(13, 'CS GO', '2012-08-21', 11, 11, 11),
(14, 'Last of Us', '2013-06-14', 12, 12, 12);
 
-- Insert Reviews
INSERT INTO Reviews (review_id, user_id, game_id, review_text, review_date, rating) VALUES
(1, 1, 1, 'Incredible storyline and graphics!', '2023-01-20', 4.8),
(2, 2, 3, 'Fast-paced and thrilling. A must-play for FPS fans.', '2023-02-15', 4.5),
(3, 3, 6, 'One of the best open-world experiences.', '2023-03-10', 4.7),
(4, 4, 7, 'Cute game with an interesting concept.', '2023-04-22', 4.2),
(5, 5, 8, 'Absolutely terrifying but brilliant!', '2023-05-12', 4.6),
(6, 6, 12, 'Fun with friends, but limited solo play.', '2023-06-14', 3.9),
(7, 7, 13, 'Classic shooter that still holds up.', '2023-07-18', 4.3),
(8, 1, 14, 'Emotional journey with beautiful storytelling.', '2023-08-23', 4.9);
 
-- Insert Media with Placeholder Links
INSERT INTO Media (game_id, screenshots, video, trailer) VALUES
(1, 'god_of_war_2018_screenshots', 'god_of_war_2018_video', 'god_of_war_2018_trailer'),
(2, 'god_of_war_ragnarok_screenshots', 'god_of_war_ragnarok_video', 'god_of_war_ragnarok_trailer'),
(3, 'doom_eternal_screenshots', 'doom_eternal_video', 'doom_eternal_trailer'),
(6, 'gtav_screenshots', 'gtav_video', 'gtav_trailer'),
(7, 'stray_screenshots', 'stray_video', 'stray_trailer'),
(8, 'resident_evil_village_screenshots', 'resident_evil_village_video', 'resident_evil_village_trailer'),
(12, 'among_us_screenshots', 'among_us_video', 'among_us_trailer');
 
-- Insert Purchases
INSERT INTO Purchases (user_id, package_id) VALUES
(1, 101),
(2, 102),
(3, 103),
(4, 104),
(5, 105),
(6, 106),
(7, 107);
 
-- Insert Sequel Games
INSERT INTO SequelGames (original_game_id, sequel_game_id) VALUES
(1, 2),  -- God of War 2018 -> God of War Ragnarok
(4, 5),  -- Call of Duty Modern Warfare -> Call of Duty Black Ops
(6, 8),  -- GTAV -> Resident Evil Village (Example)
(13, 14); -- CS GO -> Last of Us (Example)
 
-- Insert Leaderboard Ranking
INSERT INTO LeaderboardRanking (game_id, ranking) VALUES
(1, 1),  -- God of War 2018 at Rank 1
(3, 2),  -- Doom Eternal at Rank 2
(6, 3),  -- GTAV at Rank 3
(7, 4),  -- Stray at Rank 4
(8, 5),  -- Resident Evil Village at Rank 5
(12, 6), -- Among Us at Rank 6
(13, 7), -- CS GO at Rank 7
(14, 8); -- Last of Us at Rank 8

Explanation of Entries

  1. Users: We created seven users with unique usernames, emails, and last login timestamps.
  2. Games: A selection of popular games is added, each with unique game IDs and associated fictional publishers and developers.
  3. Reviews: Each user reviews different games, with ratings between 3.9 and 4.9 to simulate varied feedback.
  4. Media: Placeholder links are added for screenshots, videos, and trailers. These could be actual URLs if this were a real-world implementation.
  5. Purchases: Each user has a purchase record for various fictional DLC package IDs.
  6. SequelGames: Sequel relationships are set for games with known sequels (e.g., God of War 2018 → God of War Ragnarok).
  7. LeaderboardRanking: A ranking for each game is established based on popularity, with “God of War 2018” ranked at the top.

This script should populate your tables with initial data, making it easier to test queries and relationships in your database. Let me know if you need further customizations or additional entries!

Graph View

Backlinks

  • No backlinks found