Initial Project Proposal

Course: CSCI 2020

Initial Design

WARNING

This potentially will change as we develop our ideas more, but it’s more of a rough outline of what the database will include.

Preliminary ERD

erDiagram
    POCKETMON {
        int id PK
        string name
        int base_hp
        int base_attack
        int base_defense
        int base_speed
        int base_special_attack
        int base_special_defense
    }
    
    TYPE {
        int id PK
        string name
    }
    
    ABILITY {
        int id PK
        string name
        string effect
    }
    
    MOVE {
        int id PK
        string name
        int power
        int accuracy
        int pp
        string category
        int type_id FK
    }
    
    POCKETMON_TYPE {
        int pocketmon_id FK
        int type_id FK
    }
    
    POCKETMON_ABILITY {
        int pocketmon_id FK
        int ability_id FK
    }
    
    POCKETMON_MOVE {
        int pocketmon_id FK
        int move_id FK
        string method
    }
    
    POCKETMON ||--o{ POCKETMON_TYPE : has
    TYPE ||--o{ POCKETMON_TYPE : categorized_as
    
    POCKETMON ||--o{ POCKETMON_ABILITY : has
    ABILITY ||--o{ POCKETMON_ABILITY : grants
    
    POCKETMON ||--o{ POCKETMON_MOVE : learns
    MOVE ||--o{ POCKETMON_MOVE : associated_with
    
    TYPE ||--o{ MOVE : determines

Relationship Mappings & Cardinality

Entity A	Relationship	Entity B	Cardinality	Description
POCKETMON	has	POCKETMON_TYPE	1 to many	A Pocketmon can have one or two types
POCKETMON_TYPE	categorized as	TYPE	many to 1	A type can be assigned to many Pocketmon
POCKETMON_ABILITY	grants	ABILITY	many to 1	An ability can belong to multiple Pocketmon
POCKETMON	learns	POCKETMON_MOVE	1 to many	A Pocketmon can learn many moves
POCKETMON_MOVE	associated with	MOVE	many to 1	A move can be learned by many Pocketmon
MOVE	determines	TYPE	many to 1	Each move belongs to a single type

Attribute Lists

• POCKETMON
  • id → primary key
  • name → unique, name of the Pocketmon
  • base_hp → HP stat
  • base_attack → attack stat
  • base_defense → defense stat
  • base_speed → speed stat
  • base_special_attack → special attack stat
  • base_special_defense → special defense stat
• TYPE
  • id → primary key
  • name → unique, such as ‘fire’ or ‘water’
• ABILITY
  • id → primary key
  • name → unique, such as ‘intimidate’
  • effect → the effect of the ability, such as lowering the opponents attack
• MOVE
  • id → primary key
  • name → unique, such as ‘flamethrower’
  • power → move base power, nullable for status moves
  • accuracy → percentage of the move accuracy
  • pp → number of times the move can be used
  • category → category of the move, ‘physical’, ‘special’, or ‘status’
• POCKETMON_TYPE
  • pocketmon_id → foreign key → POKEMON.id
  • type_id → foreign key → TYPE.id
• POCKETMON_ABILITY
  • pocketmon_id → foreign key → POKEMON.id
  • ability_id → foreign key → ABILITY.id
• POCKETMON_MOVE
  • pocketmon_id → foreign key → POKEMON.id
  • move_id → foreign key → MOVE.id
  • method → how the move is learned, such as level-up or TM

Primary & Key Strategies

Table	Primary Key	Foreign Keys
POCKETMON	id (PK)
TYPE	id (PK)
ABILITY	id (PK)
MOVE	id (PK)	type_id → TYPE.id
POCKETMON_TYPE	(pocketmon_id, type_id) (composite PK)	pocketmon_id → POCKETMON.id type_id → TYPE.id
POCKETMON_ABILITY	(pocketmon_id, ability_id) (composite PK)	pocketmon_id → POCKETMON.id ability_id → ABILITY.id
POCKETMON_MOVE	(pocketmon_id, move_id) (composite PK)	pocketmon_id → POCKETMON.id move_id → MOVE.id