POKEMON CREATE TABLES

CREATE TABLE IF NOT EXISTS POKEMON (
  POKE_NUM SMALLINT,
  POKE_NAME varchar(50),
  POKE_HEIGHT DECIMAL(2,1),
  POKE_WEIGHT DECIMAL(4,1),
  GEN_NUM tinyint,
  PRIMARY KEY (POKE_NUM),
  FOREIGN KEY (GEN_NUM) REFERENCES GEN(GEN_NUM)
);

CREATE TABLE IF NOT EXISTS STATS (
  POKE_NUM smallint,
  STATS_BASE_HP smallint,
  STATS_BASE_ATTACK smallint,
  STATS_BASE_DEFENSE smallint,
  STATS_BASE_SPATTACK smallint,
  STATS_BASE_SPDEFENSE smallint,
  STATS_BASE_SPEED smallint,
  STATS_MAX_HP smallint,
  STATS_MAX_ATTACK smallint,
  STATS_MAX_DEFENSE smallint,
  STATS_MAX_SPATTACK smallint,
  STATS_MAX_SPDEFENSE smallint,
  STATS_MAX_SPEED smallint,
  PRIMARY KEY (POKE_NUM),
  FOREIGN KEY (POKE_NUM) REFERENCES POKEMON(POKE_NUM)
);

CREATE TABLE IF NOT EXISTS GEN (
  GEN_NUM tinyint,
  GEN_YEAR smallint,
  PRIMARY KEY (GEN_NUM)
);

CREATE TABLE IF NOT EXISTS BELONGS (
  POKE_NUM smallint,
  TYPE_ID tinyint,
  PRIMARY KEY (POKE_NUM), FOREIGN KEY (POKE_NUM) REFERENCES POKEMON(POKE_NUM),
  PRIMARY KEY (TYPE_ID), FOREIGN KEY (TYPE_ID) REFERENCES POKEMON(TYPE_ID)
);

CREATE TABLE IF NOT EXISTS LEARNS (
    POKE_NUM SMALLINT,
    MOVE_ID smallint,
    LEARN_LEVEL tinyint,
    PRIMARY KEY (POKE_NUM), FOREIGN KEY (POKE_NUM) REFERENCES POKEMON(POKE_NUM),
    PRIMARY KEY (MOVE_ID), FOREIGN KEY (MOVE_ID) REFERENCES POKEMON(MOVE_ID)
);

CREATE TABLE IF NOT EXISTS MOVE (
    MOVE_NAME varchar(50),
    MOVE_ID smallint,
    MOVE_CATEGORY varchar(25),
    MOVE_POWER smallint,
    MOVE_ACCURACY smallint,
    MOVE_PP tinyint,
    GEN_NUM tinyint,
    TYPE_ID tinyint,
    PRIMARY KEY (MOVE_ID),
    FOREIGN KEY (GEN_NUM) REFERENCES GEN(GEN_NUM),
    FOREIGN KEY (TYPE_ID) REFERENCES TYPE(TYPE_ID)
);

CREATE TABLE IF NOT EXISTS TYPE (
  TYPE_ID tinyint,
  TYPE_NAME varchar(50),
  TYPE_AVG_HP decimal(3,2),
  TYPE_AVG_ATTACK decimal(3,2),
  TYPE_AVG_DEFENSE decimal (3,2),
  TYPE_AVG_SPATTACK decimal (3,2),
  TYPE_AVG_SPDEFENSE decimal (3,2),
  TYPE_AVG_SPEED decimal (3,2),
  PRIMARY KEY (TYPE_ID)
);