DROP DATABASE somiba;
CREATE DATABASE somiba;
use somiba;

CREATE TABLE player (
       name  VARCHAR(9) NOT NULL,
       seasons TINYINT UNSIGNED NOT NULL DEFAULT 0,
       PRIMARY KEY (name)
);

CREATE TABLE season (
       name  VARCHAR(9) NOT NULL,
       year  CHAR(4) NOT NULL,
       team  CHAR(9) NOT NULL,
       gp    SMALLINT UNSIGNED NOT NULL,
       gs    SMALLINT UNSIGNED NOT NULL,
       mp    MEDIUMINT UNSIGNED NOT NULL,
       fgm   SMALLINT UNSIGNED NOT NULL,
       fga   SMALLINT UNSIGNED NOT NULL,
       tpm   SMALLINT UNSIGNED NOT NULL,
       tpa   SMALLINT UNSIGNED NOT NULL,
       ftm   SMALLINT UNSIGNED NOT NULL,
       fta   SMALLINT UNSIGNED NOT NULL,
       pts   MEDIUMINT UNSIGNED NOT NULL,
       off   SMALLINT UNSIGNED NOT NULL,
       def   SMALLINT UNSIGNED NOT NULL,
       ast   SMALLINT UNSIGNED NOT NULL,
       pf    SMALLINT UNSIGNED NOT NULL,
       dq    SMALLINT UNSIGNED NOT NULL,
       stl   SMALLINT UNSIGNED NOT NULL,
       trn   SMALLINT UNSIGNED NOT NULL,
       blk   SMALLINT UNSIGNED NOT NULL,
       PRIMARY KEY (name, year, team),
       FOREIGN KEY (name) REFERENCES player(name)
);

CREATE TABLE mapping (
       original CHAR(9) NOT NULL,
       custom	CHAR(9) NOT NULL
);

CREATE INDEX OIndex ON mapping(original);
CREATE INDEX CIndex ON mapping(custom);

CREATE TABLE yearTeam (
       original CHAR(9) NOT NULL,
       custom	CHAR(9) NOT NULL,
       team	CHAR(4) NOT NULL,
       year	CHAR(4) NOT NULL,
       PRIMARY KEY (original, custom, team, year),
       FOREIGN KEY (original) REFERENCES mapping(original),
       FOREIGN KEY (custom) REFERENCES mapping(custom)
);
