CREATE TABLE awards_players
(
    playerID TEXT not null,
    award    TEXT not null,
    year     INTEGER          not null,
    lgID     TEXT null,
    note     TEXT null,
    pos      TEXT null,
    primary key (playerID, year, award),
    foreign key (playerID) references players (playerID)
            on update cascade on delete cascade
);
awards_players
CREATE TABLE coaches
(
    coachID     TEXT not null,
    year        INTEGER          not null,
    tmID        TEXT not null,
    lgID        TEXT null,
    stint       INTEGER          not null,
    won         INTEGER          null,
    lost        INTEGER          null,
    post_wins   INTEGER          null,
    post_losses INTEGER          null,
    primary key (coachID, year, tmID, stint),
    foreign key (tmID, year) references teams (tmID, year)
            on update cascade on delete cascade
);
coaches
CREATE TABLE draft
(
    id             INTEGER default 0 not null
        primary key,
    draftYear      INTEGER           null,
    draftRound     INTEGER           null,
    draftSelection INTEGER           null,
    draftOverall   INTEGER          null,
    tmID           TEXT  null,
    firstName      TEXT  null,
    lastName       TEXT  null,
    suffixName     TEXT  null,
    playerID       TEXT  null,
    draftFrom      TEXT  null,
    lgID           TEXT  null,
    foreign key (tmID, draftYear) references teams (tmID, year)
            on update cascade on delete cascade
);
draft
CREATE TABLE player_allstar
(
    playerID        TEXT not null,
    last_name       TEXT null,
    first_name      TEXT null,
    season_id       INTEGER          not null,
    conference      TEXT null,
    league_id       TEXT null,
    games_played    INTEGER          null,
    minutes         INTEGER          null,
    points          INTEGER          null,
    o_rebounds      INTEGER          null,
    d_rebounds      INTEGER          null,
    rebounds        INTEGER          null,
    assists         INTEGER          null,
    steals          INTEGER          null,
    blocks          INTEGER          null,
    turnovers       INTEGER          null,
    personal_fouls  INTEGER          null,
    fg_attempted    INTEGER          null,
    fg_made         INTEGER          null,
    ft_attempted    INTEGER          null,
    ft_made         INTEGER          null,
    three_attempted INTEGER          null,
    three_made      INTEGER          null,
    primary key (playerID, season_id),
    foreign key (playerID) references players (playerID)
            on update cascade on delete cascade
);
player_allstar
CREATE TABLE players
(
    playerID      TEXT not null
        primary key,
    useFirst      TEXT null,
    firstName     TEXT null,
    middleName    TEXT null,
    lastName      TEXT null,
    nameGiven     TEXT null,
    fullGivenName TEXT null,
    nameSuffix    TEXT null,
    nameNick      TEXT null,
    pos           TEXT null,
    firstseason   INTEGER          null,
    lastseason    INTEGER         null,
    height        REAL        null,
    weight        INTEGER         null,
    college       TEXT null,
    collegeOther  TEXT null,
    birthDate     DATE         null,
    birthCity     TEXT null,
    birthState    TEXT null,
    birthCountry  TEXT null,
    highSchool    TEXT null,
    hsCity        TEXT null,
    hsState       TEXT null,
    hsCountry     TEXT null,
    deathDate     DATE         null,
    race          TEXT null
);
players
CREATE TABLE teams
(
    year        INTEGER          not null,
    lgID        TEXT null,
    tmID        TEXT not null,
    franchID    TEXT null,
    confID      TEXT null,
    divID       TEXT null,
    `rank`      INTEGER          null,
    confRank    INTEGER          null,
    playoff     TEXT null,
    name        TEXT null,
    o_fgm       INTEGER          null,
--     o_fga       int          null,
    o_ftm       INTEGER          null,
--     o_fta       int          null,
--     o_3pm       int          null,
--     o_3pa       int          null,
--     o_oreb      int          null,
--     o_dreb      int          null,
--     o_reb       int          null,
--     o_asts      int          null,
--     o_pf        int          null,
--     o_stl       int          null,
--     o_to        int          null,
--     o_blk       int          null,
     o_pts       INTEGER          null,
--     d_fgm       int          null,
--     d_fga       int          null,
--     d_ftm       int          null,
--     d_fta       int          null,
--     d_3pm       int          null,
--     d_3pa       int          null,
--     d_oreb      int          null,
--     d_dreb      int          null,
--     d_reb       int          null,
--     d_asts      int          null,
--     d_pf        int          null,
--     d_stl       int          null,
--     d_to        int          null,
--     d_blk       int          null,
     d_pts       INTEGER          null,
--     o_tmRebound int          null,
--     d_tmRebound int          null,
    homeWon     INTEGER          null,
    homeLost    INTEGER          null,
    awayWon     INTEGER          null,
    awayLost    INTEGER          null,
--     neutWon     int          null,
--     neutLoss    int          null,
--     confWon     int          null,
--     confLoss    int          null,
--     divWon      int          null,
--     divLoss     int          null,
--     pace        int          null,
    won         INTEGER          null,
    lost        INTEGER          null,
    games       INTEGER          null,
--     min         int          null,
    arena       TEXT null,
--     attendance  int          null,
--     bbtmID      varchar(255) null,
    primary key (year, tmID)
);
teams
CREATE TABLE "awards_coaches"
(
    id      INTEGER
        primary key autoincrement,
    year    INTEGER,
    coachID TEXT,
    award   TEXT,
    lgID    TEXT,
    note    TEXT,
    foreign key (coachID, year) references coaches (coachID, year)
        on update cascade on delete cascade
);
awards_coaches
CREATE TABLE "players_teams"
(
    id                 INTEGER
        primary key autoincrement,
    playerID           TEXT not null
        references players
            on update cascade on delete cascade,
    year               INTEGER,
    stint              INTEGER,
    tmID               TEXT,
    lgID               TEXT,
    GP                 INTEGER,
    GS                 INTEGER,
    minutes            INTEGER,
    points             INTEGER,
    oRebounds          INTEGER,
    dRebounds          INTEGER,
    rebounds           INTEGER,
    assists            INTEGER,
    steals             INTEGER,
    blocks             INTEGER,
    turnovers          INTEGER,
    PF                 INTEGER,
    fgAttempted        INTEGER,
    fgMade             INTEGER,
    ftAttempted        INTEGER,
    ftMade             INTEGER,
    threeAttempted     INTEGER,
    threeMade          INTEGER,
    PostGP             INTEGER,
    PostGS             INTEGER,
    PostMinutes        INTEGER,
    PostPoints         INTEGER,
    PostoRebounds      INTEGER,
    PostdRebounds      INTEGER,
    PostRebounds       INTEGER,
    PostAssists        INTEGER,
    PostSteals         INTEGER,
    PostBlocks         INTEGER,
    PostTurnovers      INTEGER,
    PostPF             INTEGER,
    PostfgAttempted    INTEGER,
    PostfgMade         INTEGER,
    PostftAttempted    INTEGER,
    PostftMade         INTEGER,
    PostthreeAttempted INTEGER,
    PostthreeMade      INTEGER,
    note               TEXT,
    foreign key (tmID, year) references teams (tmID, year)
        on update cascade on delete cascade
);
players_teams
CREATE TABLE "series_post"
(
    id         INTEGER
        primary key autoincrement,
    year       INTEGER,
    round      TEXT,
    series     TEXT,
    tmIDWinner TEXT,
    lgIDWinner TEXT,
    tmIDLoser  TEXT,
    lgIDLoser  TEXT,
    W          INTEGER,
    L          INTEGER,
    foreign key (tmIDWinner, year) references teams (tmID, year)
        on update cascade on delete cascade,
    foreign key (tmIDLoser, year) references teams (tmID, year)
        on update cascade on delete cascade
);
series_post
original_column_name,column_name,column_description,data_format,value_description
id,,the unique number to determine one row of the data,integer,
draftYear,,which year does this draft happens,integer,
draftRound,,the Round number of this draft,integer,"If the value is 0, it means that there is no draft in this year

1 refers that this player was picked in the first round"
draftSelection,league ID,the position that the player was picked,integer,"0：This signifies that it doesn't contain the draft information of this player

7：this player was selected in the 7th position in this round

commonsense evidence:

draftRound: 1; 
draftSelection: 7 
represents: this player was selected in the round 1, 7th position."
draftOverall,draft overall rank,The player's overall draft position for the year,integer,this is the overall draft rank of the player.
tmID,team ID,team name,text,abbreviated name
firstName,,the first name of the player,text,
lastName,,the last name of the player,text,
suffixName,,the suffix name of the player,text,
playerID,,ID number identifying the unique number of the player,text,
draftFrom,,the university that the drafted players come from,text,the name of the university
lgID,league ID,the league name,text,"mainly categories: ""NBA"" & ""ABA"""
draft
original_column_name,column_name,column_description,data_format,value_description
playerID,,ID number identifying the unique number of the player,text,
first_name,,the first name of the player,text,
last_name,,the last name of the player,text,
season_id,,the id number of the season,integer,
conference,,which conference that players belong to,text,two categories: west; east
league_id,league ID,the league name,text,two categories: NBA; ABA
games_played,,how many all star games that this player played in this season,integer,mostly it's 1
minutes,,minutes of attendance,integer,18：played in 18 minutes
points,,points,integer,"19: get 19 points；

null --> doesn't get points"
o_rebounds,offense rebounds,offense rebounds,integer,"empty or null refers to none offense rebounds，

1: one offense rebound"
d_rebounds,defense rebounds,defense rebounds,integer,"empty or null refers to none defense rebounds，

1: one defense rebound"
rebounds,,total rebounds,integer,"empty or null refers to none total rebounds，

3: totally gets 3 rebounds including offence and defense rebounds

commensense evidence:

total rebounds = offense rebounds + defense rebounds"
assists,assistants,assistants,integer,"null or empty refers to none

2: 2 assistants"
steals,,steals,integer,"null or empty refers to none

2: 2 steals"
blocks,,blocks,integer,"null or empty refers to none

2: 2 blocks"
turnovers,,turnovers,integer,"null or empty refers to none

2: 2 turnovers"
personal_fouls,,personal fouls,integer,"null or empty refers to none

2: 2 personal fouls"
fg_attempted,field goal attempted,field goal attempted,integer,"null or empty refers to none

2: 2 field goal attempts"
fg_made,field goal made,field goal made,integer,"null or empty refers to none

2: 2 field goal made"
ft_attempted,free throw attempted,free throw attempted,integer,"null or empty refers to none

2: 2 free throw attempts"
ft_made,free throw made,free throw made,integer,"null or empty refers to none

2: 2 free throw made"
three_attempted,three point attempted,three point attempted,integer,"null or empty refers to none

2: 2 three point attempts"
three_made,three point made,three point made ,integer,"null or empty refers to none

2: 2 three point made"
player_allstar
original_column_name,column_name,column_description,data_format,value_description
playerID,,ID number identifying the unique number of the player,text,
useFirst,use first name,,text,not useful
firstName,,the first name of the player,text,
middleName,,the middle name of the player,text,
lastName,,the last name of the player,text,
nameGiven,,,text,not useful
fullGivenName,,,text,not useful
nameSuffix,,the suffix name of the player,text,
nameNick,nick name,,text,
pos,position,the position of the player,text,"C: Center

F: Forward

G: Guard 

commonsense evidence:
some player can have two positions simultaneously"
firstseason,,,integer,nothing special
lastseason,,,integer,nothing special
height,,inch,real,inch
weight,,lb,integer,null or empty means it doesn't have this information
college,,college,text,"null or empty means it doesn't have this information

which colledge does this player graduate from"
collegeOther,,the college that transferred from ,text,"commonsense evidence:

some players may have the record of transferring from other colleges"
birthDate,,birthdate,date,
birthCity,,birthcity,text,null / empty refers to not recorded
birthState,,birth state,text,
birthCountry,,birth country,text,
highSchool,,high school,text,null / empty refers to not recorded
hsCity,high school city,high school city,text,null / empty refers to not recorded
hsState,high school state,high school state,text,null / empty refers to not recorded
hsCountry,high school country,high school country,text,null / empty refers to not recorded
deathDate,,deathdate,date,"0000-00-00 means this player is still alive

within clear date：
2011-11-10, which means this guy was died on ""2011-11-10"""
race,,,text,"B：black,
W: white

null or empty: nothing"
players
original_column_name,column_name,column_description,data_format,value_description
year,,year,integer,
lgID,league ID,league name,text,"main categories:
NBA, ABA"
tmID,team ID,team id,text,abbreviated name
franchID,,,text,not useful
confID,,,text,not useful
divID,division ID,division ID,text,
rank,,rank,integer,less is better
confRank,,,,not useful
playoff,,another name: post season,text,"for brevity, if the value is not null or empty, it means this team attends the playoffs, otherwise, means not attend"
name,,full name of the team,text,
o_fgm,offense field goal made,how many offense field goal made by this team in this season,integer,
o_ftm,offense free throw made,how many offense free throw made by this team in this season,integer,
o_pts,offense points,offense points,integer,
d_pts,defense points,defense points,integer,
homeWon,home wins,wins in the home,integer,
homeLost,home loses,loses in the home,integer,
awayWon,away wins,wins in the away,integer,
awayLost,away loses,loses in the away,integer,
won,,,integer,total wins of this team in this year.
lost,,,integer,total losts of this team in this year.
games,,,integer,total number of games that this team played in this year (season)
arena,,arena,text,"null or empty refers to the fact that this team doesn't have individual arenas, otherwise, it has individual arenas"
teams
original_column_name,column_name,column_description,data_format,value_description
id,,id of this row of data,integer,
year,,which year did the coach receive the honor?,integer,
coachID,,id number of the coaches,text,
award,,the award names,text,"mainly categories: ""NBA"" & ""ABA"""
lgID,league ID,the name of the league,text,"mainly categories: ""NBA"" & ""ABA"""
note,,special notification,text,"”null“ refers to that no special things
”tie“ represents that coaches are tied for some awards"
awards_coaches
original_column_name,column_name,column_description,data_format,value_description
id,,unique number of this record,integer,
year,,year,integer,
round,,round,text,"known abbreviations：

F：final 
SF: semi-final 

QF：quater-final 1/4

DF：division-final 

DSF：division semi-final"
series,,,text,not useful
tmIDWinner,team id winner,team id winner,text,
lgIDWinner,league id winner,league id winner,text,
tmIDLoser,team id loser,team id loser,text,
lgIDLoser,league id loser,league id loser,text,
W,wins,wins,integer,
L,loses,loses,integer,
series_post
original_column_name,column_name,column_description,data_format,value_description
id,,the unique number to identify this record,integer,
playerID,,ID number identifying the unique number of the player,text,
year,,year,integer,
stint,,the period of the time player spent played for this team,integer,
tmID,team ID,team name,text,abbreviated name
lgID,,,,
GP,game presentatons,game presentatons (attendance),integer,"min: 0, max: 82

commonsense evidence:

if this number is equal to 82, means full attendance"
GS,game starting,game starting,,"min: 0, max: 82,

commonsense evidence:

when GP = GS, it means that this player plays as the stating lineup fully. "
minutes,,minutes,integer,
points,,points,integer,
oRebounds,offense rebounds,offense rebounds,integer,"empty or null refers to none offense rebounds，

1: one offense rebound"
dRebounds,defense rebounds,defense rebounds,integer,"empty or null refers to none defense rebounds，

1: one defense rebound"
rebounds,,total rebounds,integer,"empty or null refers to none total rebounds，

3: totally gets 3 rebounds including offense and defense rebounds

commensense evidence:

total rebounds = offense rebounds + defense rebounds"
assists,assistants,assistants,integer,"null or empty refers to none

2: 2 assistants"
steals,,steals,integer,"null or empty refers to none

2: 2 steals"
blocks,,blocks,integer,"null or empty refers to none

2: 2 blocks"
turnovers,,turnovers,integer,"null or empty refers to none

2: 2 turnovers"
PF,personal fouls,personal fouls,integer,"null or empty refers to none

2: 2 personal fouls"
fgAttempted,field goal attempted,field goal attempted,integer,"null or empty refers to none

2: 2 field goal attempts"
fgMade,field goal made,field goal made,integer,"null or empty refers to none

2: 2 field goal made"
ftAttempted,free throw attempted,free throw attempted,integer,"null or empty refers to none

2: 2 free throw attempts"
ftMade,free throw made,free throw made,integer,"null or empty refers to none

2: 2 free throw made"
threeAttempted,three point attempted,three point attempted,integer,"null or empty refers to none

2: 2 three point attempts"
threeMade,three point made,three point made,integer,"null or empty refers to none

2: 2 three point made"
PostGP,post season game presentations,post season game presentations,integer,0: this player doesn't present in the post season (playoffs)
PostGS,post season game starting,post season game starting,integer,
PostMinutes,post season minutes,post season minutes,integer,
PostPoints,post season points,post season points,integer,
PostoRebounds,post season offense rebounds,post season offense rebounds,integer,"null or empty refers to none

1: 1 offense rebounds in the post season"
PostdRebounds,post season defense rebounds,post season defense rebounds,integer,"null or empty refers to none

1: 1 defense rebounds in the post season"
PostRebounds,post season defense rebounds,post season defense rebounds,integer,"null or empty refers to none

3: 3 rebounds in the post season totally"
PostAssists,post season assistants,,integer,"null or empty refers to none

1: 1 assistance in the post season"
PostSteals,post season steals,,integer,"null or empty refers to none

1: 1 offense steals in the post season"
PostBlocks,post season blocks,,integer,"null or empty refers to none

1: 1 block in the post season"
PostTurnovers,post season turnovers,,integer,"null or empty refers to none

1: 1 turnover in the post season"
PostPF,post season personal fouls,,integer,"null or empty refers to none

1: 2 personal fouls in the post season"
PostfgAttempted,post season field goal attempted,,integer,"null or empty refers to none

1: 1 field goal attempts in the post season"
PostfgMade,post season field goal made,,integer,"null or empty refers to none

1: 1 field goal made in the post season"
PostftAttempted,post season field free throw attempted,,integer,"null or empty refers to none

1: 1 free throw attempts in the post season"
PostftMade,post season free throw made,,integer,"null or empty refers to none

1: 1 free throw made in the post season"
PostthreeAttempted,post season three point attempted,,integer,"null or empty refers to none

1: 1 three point attempts in the post season"
PostthreeMade,post season three point made,,integer,"null or empty refers to none

1: 1 three point made in the post season"
note,,,,
players_teams
original_column_name,column_name,column_description,data_format,value_description
playerID,,ID number identifying the unique number of the player,text,
award,,the name of the award,text,
year,,the time of this award,integer,
lgID,league ID,the name of the league,text,"mainly categories: ""NBA"" & ""ABA"""
note,,notification,text,"”null“ refers to that no special things
”tie“ represents that coaches are tied for some awards"
pos,position,the position of the player,text,"C: Center
F: Forward
G: Guard "
awards_players
original_column_name,column_name,column_description,data_format,value_description
coachID,,ID number identifying the unique number of the coach,text,
year,,,integer,
tmID,team ID,team name,text,abbreviated name
lgID,league ID,league name,text,"mainly categories: ""NBA"" & ""ABA"""
stint,,the period of the time coaching this team,integer,
won,,the number of won games,integer,0: no wins
lost,,the number of lost games,integer,0: win all the games
post_wins,post season wins,the number of games won in the post-season (playoffs) games,integer,"0: no wins

commonsense evidence:

If the team's post-season wins and losses are all zero, it implies that the team did not participate in the post-season (playoffs) that year."
post_losses,post season losses,the number of games lost in the post-season (playoffs) games,integer,"0: win all the games

commonsense evidence:

If the team's post-season wins and losses are all zero, it implies that the team did not participate in the post-season (playoffs) that year."
coaches