PfZ2k9kc

1CREATE TABLE IF NOT EXISTS POKEMON (
2  POKE_NUM SMALLINT,
3  POKE_NAME varchar(50),
4  POKE_HEIGHT DECIMAL(2,1),
5  POKE_WEIGHT DECIMAL(4,1),
6  GEN_NUM tinyint,
7  PRIMARY KEY (POKE_NUM),
8  FOREIGN KEY (GEN_NUM) REFERENCES GEN(GEN_NUM)
9);
10
11CREATE TABLE IF NOT EXISTS STATS (
12  POKE_NUM smallint,
13  STATS_BASE_HP smallint,
14  STATS_BASE_ATTACK smallint,
15  STATS_BASE_DEFENSE smallint,
16  STATS_BASE_SPATTACK smallint,
17  STATS_BASE_SPDEFENSE smallint,
18  STATS_BASE_SPEED smallint,
19  STATS_MAX_HP smallint,
20  STATS_MAX_ATTACK smallint,
21  STATS_MAX_DEFENSE smallint,
22  STATS_MAX_SPATTACK smallint,
23  STATS_MAX_SPDEFENSE smallint,
24  STATS_MAX_SPEED smallint,
25  PRIMARY KEY (POKE_NUM),
26  FOREIGN KEY (POKE_NUM) REFERENCES POKEMON(POKE_NUM)
27);
28
29CREATE TABLE IF NOT EXISTS GEN (
30  GEN_NUM tinyint,
31  GEN_YEAR smallint,
32  PRIMARY KEY (GEN_NUM)
33);
34
35CREATE TABLE IF NOT EXISTS BELONGS (
36  POKE_NUM smallint,
37  TYPE_ID tinyint,
38  PRIMARY KEY (POKE_NUM), FOREIGN KEY (POKE_NUM) REFERENCES POKEMON(POKE_NUM),
39  FOREIGN KEY (TYPE_PRIMARY_ID) REFERENCES POKEMON(TYPE_ID)
40  FOREIGN KEY (TYPE_SECONDARY_ID) REFERENCES POKEMON(TYPE_ID)
41);
42
43CREATE TABLE IF NOT EXISTS LEARNS (
44	POKE_NUM SMALLINT,
45	MOVE_ID smallint,
46	LEARN_LEVEL tinyint,
47  PRIMARY KEY (POKE_NUM), FOREIGN KEY (POKE_NUM) REFERENCES POKEMON(POKE_NUM),
48  PRIMARY KEY (MOVE_ID), FOREIGN KEY (MOVE_ID) REFERENCES POKEMON(MOVE_ID)
49);
50
51CREATE TABLE IF NOT EXISTS MOVE (
52	MOVE_NAME varchar(50),
53  MOVE_ID smallint,
54	MOVE_CATEGORY varchar(25),
55	MOVE_POWER smallint,
56	MOVE_ACCURACY smallint,
57	MOVE_PP tinyint,
58	GEN_NUM tinyint,
59	TYPE_ID tinyint,
60  PRIMARY KEY (MOVE_ID),
61  FOREIGN KEY (GEN_NUM) REFERENCES GEN(GEN_NUM),
62  FOREIGN KEY (TYPE_ID) REFERENCES TYPE(TYPE_ID)
63);
64
65CREATE TABLE IF NOT EXISTS TYPE (
66  TYPE_ID tinyint,
67  TYPE_NAME varchar(50),
68  TYPE_AVG_HP decimal(3,2),
69  TYPE_AVG_ATTACK decimal(3,2),
70  TYPE_AVG_DEFENSE decimal (3,2),
71  TYPE_AVG_SPATTACK decimal (3,2),
72  TYPE_AVG_SPDEFENSE decimal (3,2),
73  TYPE_AVG_SPEED decimal (3,2),
74  PRIMARY KEY (TYPE_ID)
75);