Database (Data) - MsCornell/2425Repo GitHub Wiki

In this application we are using SQL Server.

Data Model

This data model is a sample representing the structure of the Tic Tac Toe game. It uses Mermaid syntax documented here and edited here.

Class diagram for Data Model

classDiagram
    class Player {
        +int Id
        +string Name
        +datetime Created
        +string Email
        -string _password
    }

    class Game {
        +int Id
        +datetime Started
        +datetime Ended
        +bool AiCharacter
        +int PlayerId
        +string PlayerCharacter
        +char GameWinner
        +string GameMode
        +int GameScore
    }

    class Board {
        +int Id
        +char BoardWinner
    }

    class Character {
        +string CharacterName
    }

    class Game_Board {
        +int GameId
        +int BoardId
    }

    Player --> Game
    Game --> Character
    Game --> Board
    Board --> Character
    Game_Board --> Game
    Game_Board --> Board

Class Diagram for Views

classDiagram
    class GameBoardDetail {
        +int GameId
        +int BoardId
        +char BoardWinner
    }

    class GameDetail {
        +int GameId
        +string PlayerName
        +string PlayerCharacter
        +bool AiCharacter
        +string GameMode
        +datetime Started
        +datetime Ended
        +char GameWinner
        +int GameScore
    }

    class PlayerWinRate {
        +int PlayerId
        +string PlayerName
        +string GameMode
        +int TotalGamesInMode
        +int TotalScoreInMode
        +int WinsInMode
        +float WinRateInMode
        +int TotalGames
        +int TotalWins
        +float OverallWinRate
        +int TotalScore
    }

    GameBoardDetail --> Game
    GameBoardDetail --> Board
    GameBoardDetail --> Game_Board

    GameDetail --> Game
    GameDetail --> Player

    PlayerWinRate --> Player
    PlayerWinRate --> Game

ER Diagram for Data Model

erDiagram
    Player {
        int Id "Primary Key, Auto-increment"
        varchar Name "Unique Player's Name"
        datetime Created "Account Creation Date"
        varchar Email "Unique Email"
        varchar _password "Player's Password"
    }

    Game {
        int Id "Primary Key, Auto-increment"
        datetime Started "Game Start Time"
        datetime Ended "Game End Time"
        bit AiCharacter "AI or Human (0/1)"
        int PlayerId "Foreign Key to Player(Id)"
        varchar PlayerCharacter "Foreign Key to Character(CharacterName)"
        char GameWinner "Winner ('X', 'O', '-')"
        varchar GameMode "Game Mode"
        int GameScore "Score of the Game"
    }

    Board {
        int Id "Primary Key, Auto-increment"
        char BoardWinner "Winner of the Board ('X', 'O', '-')"
    }

    Character {
        varchar CharacterName "Primary Key, Character ('X', 'O')"
    }

    Game_Board {
        int GameId "Composite Primary Key, Foreign Key to Game(Id)"
        int BoardId "Composite Primary Key, Foreign Key to Board(Id)"
    }

    Player ||--o{ Game : "plays"
    Game ||--|| Character : "uses"
    Game ||--o{ Board : "contains"
    Board ||--|| Character : "can be won by"
    Game_Board ||--|| Game : "links"
    Game_Board ||--|| Board : "links"

ER Diagrams for Views

erDiagram
    GameBoardDetail {
        int GameId "Foreign Key to Game(Id)"
        int BoardId "Foreign Key to Board(Id)"
        char BoardWinner "Winner of the Board ('X', 'O', '-')"
    }

    GameDetail {
        int GameId "Foreign Key to Game(Id)"
        varchar PlayerName "Name of the Player"
        varchar PlayerCharacter "Player's Chosen Character"
        bit AiCharacter "AI or Human (0/1)"
        varchar GameMode "Mode of the Game"
        datetime Started "Game Start Time"
        datetime Ended "Game End Time"
        char GameWinner "Winner ('X', 'O', '-')"
        int GameScore "Score of the Game"
    }

    PlayerWinRate {
        int PlayerId "Foreign Key to Player(Id)"
        varchar PlayerName "Name of the Player"
        varchar GameMode "Mode of the Game"
        int TotalScoreInMode "Total Score Earned in Mode"
        int TotalGamesInMode "Number of Games Played in Mode"
        int WinsInMode "Number of Wins in Mode"
        float WinRateInMode "Win Rate in Mode (%)"
        int TotalGames "Total Number of Games Played"
        int TotalWins "Total Number of Wins"
        float OverallWinRate "Overall Win Rate (%)"
        int TotalScore "Total Score"
    }

    Player {
        int Id "Primary Key, Auto-increment"
        varchar Name " Unique Player's Name"
    }

    Game {
        int Id "Primary Key, Auto-increment"
        varchar GameMode "Mode of the Game"
    }

    Board {
        int Id "Primary Key, Auto-increment"
        char BoardWinner "Winner of the Board ('X', 'O', '-')"
    }

    Game_Board {
        int GameId "Composite Primary Key, Foreign Key to Game(Id)"
        int BoardId "Composite Primary Key, Foreign Key to Board(Id)"
    }

    GameBoardDetail ||--o{ Game : "Based On"
    GameBoardDetail ||--o{ Board : "Based On"
    GameBoardDetail ||--o{ Game_Board : "Based On"

    GameDetail ||--o{ Game : "Based On"
    GameDetail ||--o{ Player : "Based On"

    PlayerWinRate ||--o{ Player : "Based On"
    PlayerWinRate ||--o{ Game : "Based On"

Entity Description

Player

Column Description
Player.id Unique identifier for each player.
Player.Name The unique name of the player.
Player.Created Timestamp when the player account was created.
Player.Email The unique Email used by the player for login.
Player._password The password used by the player for login.

Game

Column Description
Game.id Unique identifier for each game.
Game.Started Timestamp when the game started.
Game.Ended Timestamp when the game ended.
Game.AiCharacter Specifies whether the AI or a human is playing another character(0 for human, 1 for AI).
Game.PlayerId Foreign key linking to Player.id.
Game.PlayerCharacter Foreign key linking to Character.CharacterName. The character chosen by the player for the game.
Game.GameWinner The winner of the game ('X', 'O', or '-').
Game.GameMode The mode of the game. If AiCharacter is 1, it can be 'Easy', 'Medium', or 'Hard'. If AiCharacter is 0, the mode is 'Local'.
Game.GameScore The score achieved in the game.

Board

Column Description
Board.id Unique identifier for each board.
Board.BoardWinner The winner of the board ('X', 'O', or '-').

Character

Column Description
Character.CharacterName Primary key representing the in-game character ('X' or 'O').

Game_Board

Column Description
Game_Board.GameId Composite primary key linking to Game.id.
Game_Board.BoardId Composite primary key linking to Board.id.

Views Description

GameBoardDetail

Column Description
GameBoardDetail.GameId Foreign key linking to Game.id.
GameBoardDetail.BoardId Foreign key linking to Board.id.
GameBoardDetail.BoardWinner The winner of the board ('X', 'O', or '-').

GameDetail

Column Description
GameDetail.GameId Foreign key linking to Game.id.
GameDetail.PlayerName The name of the player.
GameDetail.PlayerCharacter The character chosen by the player.
GameDetail.AiCharacter Indicates whether the game involves an AI player (0 for human, 1 for AI).
GameDetail.GameMode The mode of the game.
GameDetail.Started Timestamp when the game started.
GameDetail.Ended Timestamp when the game ended.
GameDetail.GameWinner The winner of the game ('X', 'O', or '-').
GameDetail.GameScore The score achieved in the game.

PlayerWinRate

Column Description
PlayerWinRate.PlayerId Foreign key linking to Player.id.
PlayerWinRate.PlayerName The name of the player.
PlayerWinRate.GameMode The mode of the game.
PlayerWinRate.TotalScoreInMode Total game score earned in the mode.
PlayerWinRate.TotalGamesInMode The total number of games played in the mode.
PlayerWinRate.WinsInMode The total number of wins achieved in the mode.
PlayerWinRate.WinRateInMode The win rate in the specific mode (percentage).
PlayerWinRate.TotalGames The total number of games played overall.
PlayerWinRate.TotalWins The total number of wins achieved overall.
PlayerWinRate.OverallWinRate The overall win rate (percentage).
PlayerWinRate.TotalScore Total game Score

Sample Data

Player

Character

Game

Board

Game_Board

GameBoardDetail

GameDetail

PlayerWinRate

Data API