NBA Database Design
NBA Database Design using Postgres!
Enity-Relationship Diagram
- Reverse Engineer from MySQL Workbench ‘insert diagram here’
Create Tables
Players Table
CREATE TABLE IF NOT EXISTS "Player" (
"id" TEXT,
"full_name" TEXT,
"first_name" TEXT,
"last_name" TEXT,
"is_active" INTEGER
);
Teams Table
CREATE TABLE IF NOT EXISTS "Team" (
"id" TEXT,
"full_name" TEXT,
"abbreviation" TEXT,
"nickname" TEXT,
"city" TEXT,
"state" TEXT,
"year_founded" INTEGER
);
Team_Attributes Table
CREATE TABLE IF NOT EXISTS "Team_Attributes" (
"ID" TEXT,
"ABBREVIATION" TEXT,
"NICKNAME" TEXT,
"YEARFOUNDED" TEXT,
"CITY" TEXT,
"ARENA" TEXT,
"ARENACAPACITY" REAL,
"OWNER" TEXT,
"GENERALMANAGER" TEXT,
"HEADCOACH" TEXT,
"DLEAGUEAFFILIATION" TEXT,
"FACEBOOK_WEBSITE_LINK" TEXT,
"INSTAGRAM_WEBSITE_LINK" TEXT,
"TWITTER_WEBSITE_LINK" TEXT
);
Game Table (Statistics)
CREATE TABLE IF NOT EXISTS "Game" (
"GAME_ID" TEXT,
"SEASON_ID" TEXT,
"TEAM_ID_HOME" TEXT,
"TEAM_ABBREVIATION_HOME" TEXT,
"TEAM_NAME_HOME" TEXT,
"GAME_DATE" TEXT,
"MATCHUP_HOME" TEXT,
"WL_HOME" TEXT,
"MIN_HOME" INTEGER,
"FGM_HOME" REAL,
"FGA_HOME" TEXT,
"FG_PCT_HOME" REAL,
"FG3M_HOME" TEXT,
"FG3A_HOME" TEXT,
"FG3_PCT_HOME" REAL,
"FTM_HOME" REAL,
"FTA_HOME" REAL,
"FT_PCT_HOME" REAL,
"OREB_HOME" TEXT,
"DREB_HOME" TEXT,
"REB_HOME" TEXT,
"AST_HOME" TEXT,
"STL_HOME" TEXT,
"BLK_HOME" TEXT,
"TOV_HOME" TEXT,
"PF_HOME" REAL,
"PTS_HOME" INTEGER,
"PLUS_MINUS_HOME" INTEGER,
"VIDEO_AVAILABLE_HOME" INTEGER,
"TEAM_ID_AWAY" TEXT,
"TEAM_ABBREVIATION_AWAY" TEXT,
"TEAM_NAME_AWAY" TEXT,
"MATCHUP_AWAY" TEXT,
"WL_AWAY" TEXT,
"MIN_AWAY" INTEGER,
"FGM_AWAY" REAL,
"FGA_AWAY" TEXT,
"FG_PCT_AWAY" REAL,
"FG3M_AWAY" TEXT,
"FG3A_AWAY" TEXT,
"FG3_PCT_AWAY" REAL,
"FTM_AWAY" REAL,
"FTA_AWAY" REAL,
"FT_PCT_AWAY" REAL,
"OREB_AWAY" TEXT,
"DREB_AWAY" TEXT,
"REB_AWAY" TEXT,
"AST_AWAY" TEXT,
"STL_AWAY" TEXT,
"BLK_AWAY" TEXT,
"TOV_AWAY" TEXT,
"PF_AWAY" REAL,
"PTS_AWAY" INTEGER,
"PLUS_MINUS_AWAY" INTEGER,
"VIDEO_AVAILABLE_AWAY" INTEGER,
"GAME_DATE_EST" TEXT,
"GAME_SEQUENCE" TEXT,
"GAME_STATUS_ID" TEXT,
"GAME_STATUS_TEXT" TEXT,
"GAMECODE" TEXT,
"HOME_TEAM_ID" TEXT,
"VISITOR_TEAM_ID" TEXT,
"SEASON" TEXT,
"LIVE_PERIOD" REAL,
"LIVE_PC_TIME" TEXT,
"NATL_TV_BROADCASTER_ABBREVIATION" TEXT,
"LIVE_PERIOD_TIME_BCAST" TEXT,
"WH_STATUS" REAL,
"TEAM_CITY_HOME" TEXT,
"PTS_PAINT_HOME" TEXT,
"PTS_2ND_CHANCE_HOME" TEXT,
"PTS_FB_HOME" TEXT,
"LARGEST_LEAD_HOME" TEXT,
"LEAD_CHANGES_HOME" TEXT,
"TIMES_TIED_HOME" TEXT,
"TEAM_TURNOVERS_HOME" TEXT,
"TOTAL_TURNOVERS_HOME" TEXT,
"TEAM_REBOUNDS_HOME" TEXT,
"PTS_OFF_TO_HOME" TEXT,
"TEAM_CITY_AWAY" TEXT,
"PTS_PAINT_AWAY" TEXT,
"PTS_2ND_CHANCE_AWAY" TEXT,
"PTS_FB_AWAY" TEXT,
"LARGEST_LEAD_AWAY" TEXT,
"LEAD_CHANGES_AWAY" TEXT,
"TIMES_TIED_AWAY" TEXT,
"TEAM_TURNOVERS_AWAY" TEXT,
"TOTAL_TURNOVERS_AWAY" TEXT,
"TEAM_REBOUNDS_AWAY" TEXT,
"PTS_OFF_TO_AWAY" TEXT,
"LEAGUE_ID" TEXT,
"GAME_DATE_DAY" TEXT,
"ATTENDANCE" TEXT,
"GAME_TIME" TEXT,
"TEAM_CITY_NAME_HOME" TEXT,
"TEAM_NICKNAME_HOME" TEXT,
"TEAM_WINS_LOSSES_HOME" TEXT,
"PTS_QTR1_HOME" TEXT,
"PTS_QTR2_HOME" TEXT,
"PTS_QTR3_HOME" TEXT,
"PTS_QTR4_HOME" TEXT,
"PTS_OT1_HOME" TEXT,
"PTS_OT2_HOME" TEXT,
"PTS_OT3_HOME" TEXT,
"PTS_OT4_HOME" TEXT,
"PTS_OT5_HOME" TEXT,
"PTS_OT6_HOME" TEXT,
"PTS_OT7_HOME" TEXT,
"PTS_OT8_HOME" TEXT,
"PTS_OT9_HOME" TEXT,
"PTS_OT10_HOME" TEXT,
"PTS_HOME_y" REAL,
"TEAM_CITY_NAME_AWAY" TEXT,
"TEAM_NICKNAME_AWAY" TEXT,
"TEAM_WINS_LOSSES_AWAY" TEXT,
"PTS_QTR1_AWAY" TEXT,
"PTS_QTR2_AWAY" TEXT,
"PTS_QTR3_AWAY" TEXT,
"PTS_QTR4_AWAY" TEXT,
"PTS_OT1_AWAY" TEXT,
"PTS_OT2_AWAY" TEXT,
"PTS_OT3_AWAY" TEXT,
"PTS_OT4_AWAY" TEXT,
"PTS_OT5_AWAY" TEXT,
"PTS_OT6_AWAY" TEXT,
"PTS_OT7_AWAY" TEXT,
"PTS_OT8_AWAY" TEXT,
"PTS_OT9_AWAY" TEXT,
"PTS_OT10_AWAY" TEXT,
"LAST_GAME_ID" TEXT,
"LAST_GAME_DATE_EST" TEXT,
"LAST_GAME_HOME_TEAM_ID" TEXT,
"LAST_GAME_HOME_TEAM_CITY" TEXT,
"LAST_GAME_HOME_TEAM_NAME" TEXT,
"LAST_GAME_HOME_TEAM_ABBREVIATION" TEXT,
"LAST_GAME_HOME_TEAM_POINTS" TEXT,
"LAST_GAME_VISITOR_TEAM_ID" TEXT,
"LAST_GAME_VISITOR_TEAM_CITY" TEXT,
"LAST_GAME_VISITOR_TEAM_NAME" TEXT,
"LAST_GAME_VISITOR_TEAM_CITY1" TEXT,
"LAST_GAME_VISITOR_TEAM_POINTS" TEXT,
"HOME_TEAM_WINS" REAL,
"HOME_TEAM_LOSSES" REAL,
"SERIES_LEADER" TEXT,
"VIDEO_AVAILABLE_FLAG" REAL,
"PT_AVAILABLE" REAL,
"PT_XYZ_AVAILABLE" REAL,
"HUSTLE_STATUS" REAL,
"HISTORICAL_STATUS" REAL
);
Draft Table
+--------------------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+--------+------+-----+---------+-------+
| yearDraft | double | YES | | NULL | |
| numberPickOverall | double | YES | | NULL | |
| numberRound | double | YES | | NULL | |
| numberRoundPick | double | YES | | NULL | |
| namePlayer | text | YES | | NULL | |
| slugTeam | text | YES | | NULL | |
| nameOrganizationFrom | text | YES | | NULL | |
| typeOrganizationFrom | text | YES | | NULL | |
| idPlayer | double | YES | | NULL | |
| idTeam | double | YES | | NULL | |
| nameTeam | text | YES | | NULL | |
| cityTeam | text | YES | | NULL | |
| teamName | text | YES | | NULL | |
| PLAYER_PROFILE_FLAG | double | YES | | NULL | |
| slugOrganizationTypeFrom | text | YES | | NULL | |
| locationOrganizationFrom | text | YES | | NULL | |
+--------------------------+--------+------+-----+---------+-------+
draft_combine table
+--------------------------------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------------------+--------+------+-----+---------+-------+
| yearCombine | int | YES | | NULL | |
| idPlayer | double | YES | | NULL | |
| nameFirst | text | YES | | NULL | |
| nameLast | text | YES | | NULL | |
| namePlayer | text | YES | | NULL | |
| slugPosition | text | YES | | NULL | |
| heightWOShoesInches | double | YES | | NULL | |
| heightWOShoes | text | YES | | NULL | |
| weightLBS | double | YES | | NULL | |
| wingspanInches | double | YES | | NULL | |
| wingspan | text | YES | | NULL | |
| reachStandingInches | double | YES | | NULL | |
| reachStandingO | text | YES | | NULL | |
| verticalLeapStandingInches | text | YES | | NULL | |
| verticalLeapMaxInches | double | YES | | NULL | |
| timeLaneAgility | double | YES | | NULL | |
| timeThreeQuarterCourtSprint | text | YES | | NULL | |
| repsBenchPress135 | text | YES | | NULL | |
| pctBodyFat | text | YES | | NULL | |
| heightWShoesInches | text | YES | | NULL | |
| heightWShoes | text | YES | | NULL | |
| lengthHandInches | text | YES | | NULL | |
| widthHandInches | text | YES | | NULL | |
| timeModifiedLaneAgility | text | YES | | NULL | |
| setSpot15CornerLeft | text | YES | | NULL | |
| setSpot15BreakLeft | text | YES | | NULL | |
| setSpot15TopKey | text | YES | | NULL | |
| setSpot15BreakRight | text | YES | | NULL | |
| setSpot15CornerRight | text | YES | | NULL | |
| setSpot15CornerLeftCollege | text | YES | | NULL | |
| setSpot15BreakLeftCollege | text | YES | | NULL | |
| setSpot15TopKeyCollege | text | YES | | NULL | |
| setSpot15BreakRightCollege | text | YES | | NULL | |
| setSpot15CornerRightCollege | text | YES | | NULL | |
| setSpot15CornerLeftNBA | text | YES | | NULL | |
| setSpot15BreakLeftNBA | text | YES | | NULL | |
| setSpot15TopKeyNBA | text | YES | | NULL | |
| setSpotBreakRightNBA | text | YES | | NULL | |
| setSpotCornerRightNBA | text | YES | | NULL | |
| setSpot15CornerLeftMade | text | YES | | NULL | |
| setSpot15CornerLeftAttempted | text | YES | | NULL | |
| setSpot15CornerLeftPct | text | YES | | NULL | |
| setSpot15BreakLeftMade | text | YES | | NULL | |
| setSpot15BreakLeftAttempted | text | YES | | NULL | |
| setSpot15BreakLeftPct | text | YES | | NULL | |
| setSpot15TopKeyMade | text | YES | | NULL | |
| setSpot15TopKeyAttempted | text | YES | | NULL | |
| setSpot15TopKeyPct | text | YES | | NULL | |
| setSpot15BreakRightMade | text | YES | | NULL | |
| setSpot15BreakRightAttempted | text | YES | | NULL | |
| setSpot15BreakRightPct | text | YES | | NULL | |
| setSpot15CornerRightMade | text | YES | | NULL | |
| setSpot15CornerRightAttempted | text | YES | | NULL | |
| setSpot15CornerRightPct | text | YES | | NULL | |
| setSpot15CornerLeftCollegeMade | text | YES | | NULL | |
| setSpot15CornerLeftCollegeAttempted | text | YES | | NULL | |
| setSpot15CornerLeftCollegePct | text | YES | | NULL | |
| setSpot15BreakLeftCollegeMade | text | YES | | NULL | |
| setSpot15BreakLeftCollegeAttempted | text | YES | | NULL | |
| setSpot15BreakLeftCollegePct | text | YES | | NULL | |
| setSpot15TopKeyCollegeMade | text | YES | | NULL | |
| setSpot15TopKeyCollegeAttempted | text | YES | | NULL | |
| setSpot15TopKeyCollegePct | text | YES | | NULL | |
| setSpot15BreakRightCollegeMade | text | YES | | NULL | |
| setSpot15BreakRightCollegeAttempted | text | YES | | NULL | |
| setSpot15BreakRightCollegePct | text | YES | | NULL | |
| setSpot15CornerRightCollegeMade | text | YES | | NULL | |
| setSpot15CornerRightCollegeAttempted | text | YES | | NULL | |
| setSpot15CornerRightCollegePct | text | YES | | NULL | |
| setSpot15CornerLeftNBAMade | text | YES | | NULL | |
| setSpot15CornerLeftNBAAttempted | text | YES | | NULL | |
| setSpot15CornerLeftNBAPct | text | YES | | NULL | |
| setSpot15BreakLeftNBAMade | text | YES | | NULL | |
| setSpot15BreakLeftNBAAttempted | text | YES | | NULL | |
| setSpot15BreakLeftNBAPct | text | YES | | NULL | |
| setSpot15TopKeyNBAMade | text | YES | | NULL | |
| setSpot15TopKeyNBAAttempted | text | YES | | NULL | |
| setSpot15TopKeyNBAPct | text | YES | | NULL | |
| setSpotBreakRightNBAMade | text | YES | | NULL | |
| setSpotBreakRightNBAAttempted | text | YES | | NULL | |
| setSpotBreakRightNBAPct | text | YES | | NULL | |
| setSpotCornerRightNBAMade | text | YES | | NULL | |
| setSpotCornerRightNBAAttempted | text | YES | | NULL | |
| setSpotCornerRightNBAPct | text | YES | | NULL | |
| setOffDrib15BreakLeft | text | YES | | NULL | |
| setSpotOffDrib15TopKey | text | YES | | NULL | |
| setOffDrib15BreakRight | text | YES | | NULL | |
| setOnMove15 | text | YES | | NULL | |
| setOnMoveCollege | text | YES | | NULL | |
| setOffDrib15BreakLeftMade | text | YES | | NULL | |
| setOffDrib15BreakLeftAttempted | text | YES | | NULL | |
| setOffDrib15BreakLeftPct | text | YES | | NULL | |
| setSpotOffDrib15TopKeyMade | text | YES | | NULL | |
| setSpotOffDrib15TopKeyAttempted | text | YES | | NULL | |
| setSpotOffDrib15TopKeyPct | text | YES | | NULL | |
| setOffDrib15BreakRightMade | text | YES | | NULL | |
| setOffDrib15BreakRightAttempted | text | YES | | NULL | |
| setOffDrib15BreakRightPct | text | YES | | NULL | |
| setOnMove15Made | text | YES | | NULL | |
| setOnMove15Attempted | text | YES | | NULL | |
| setOnMove15Pct | text | YES | | NULL | |
| setOnMoveCollegeMade | text | YES | | NULL | |
| setOnMoveCollegeAttempted | text | YES | | NULL | |
| setOnMoveCollegePct | text | YES | | NULL | |
| setOffDribBreakLeftCollege | text | YES | | NULL | |
| setOffDribTopKeyCollege | text | YES | | NULL | |
| setOffDribBreakRightCollege | text | YES | | NULL | |
| setOffDribBreakLeftCollegeMade | text | YES | | NULL | |
| setOffDribBreakLeftCollegeAttempted | text | YES | | NULL | |
| setOffDribBreakLeftCollegePct | text | YES | | NULL | |
| setOffDribTopKeyCollegeMade | text | YES | | NULL | |
| setOffDribTopKeyCollegeAttempted | text | YES | | NULL | |
| setOffDribTopKeyCollegePct | text | YES | | NULL | |
| setOffDribBreakRightCollegeMade | text | YES | | NULL | |
| setOffDribBreakRightCollegeAttempted | text | YES | | NULL | |
| setOffDribBreakRightCollegePct | text | YES | | NULL | |
+--------------------------------------+--------+------+-----+---------+-------+
game_inactive_players
+-------------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------+------+-----+---------+-------+
| PLAYER_ID | int | YES | | NULL | |
| FIRST_NAME | text | YES | | NULL | |
| LAST_NAME | text | YES | | NULL | |
| JERSEY_NUM | int | YES | | NULL | |
| TEAM_ID | int | YES | | NULL | |
| TEAM_CITY | text | YES | | NULL | |
| TEAM_NAME | text | YES | | NULL | |
| TEAM_ABBREVIATION | text | YES | | NULL | |
| GAME_ID | text | YES | | NULL | |
+-------------------+------+------+-----+---------+-------+
game_officials
+-------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------+------+-----+---------+-------+
| OFFICIAL_ID | int | YES | | NULL | |
| FIRST_NAME | text | YES | | NULL | |
| LAST_NAME | text | YES | | NULL | |
| JERSEY_NUM | int | YES | | NULL | |
| GAME_ID | text | YES | | NULL | |
+-------------+------+------+-----+---------+-------+
player_attributes Table
+----------------------------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------+--------+------+-----+---------+-------+
| ID | int | YES | | NULL | |
| FIRST_NAME | text | YES | | NULL | |
| LAST_NAME | text | YES | | NULL | |
| DISPLAY_FIRST_LAST | text | YES | | NULL | |
| DISPLAY_LAST_COMMA_FIRST | text | YES | | NULL | |
| DISPLAY_FI_LAST | text | YES | | NULL | |
| PLAYER_SLUG | text | YES | | NULL | |
| BIRTHDATE | text | YES | | NULL | |
| SCHOOL | text | YES | | NULL | |
| COUNTRY | text | YES | | NULL | |
| LAST_AFFILIATION | text | YES | | NULL | |
| HEIGHT | double | YES | | NULL | |
| WEIGHT | double | YES | | NULL | |
| SEASON_EXP | int | YES | | NULL | |
| JERSEY | int | YES | | NULL | |
| POSITION | text | YES | | NULL | |
| ROSTERSTATUS | text | YES | | NULL | |
| GAMES_PLAYED_CURRENT_SEASON_FLAG | text | YES | | NULL | |
| TEAM_ID | int | YES | | NULL | |
| TEAM_NAME | text | YES | | NULL | |
| TEAM_ABBREVIATION | text | YES | | NULL | |
| TEAM_CODE | text | YES | | NULL | |
| TEAM_CITY | text | YES | | NULL | |
| PLAYERCODE | text | YES | | NULL | |
| FROM_YEAR | int | YES | | NULL | |
| TO_YEAR | int | YES | | NULL | |
| DLEAGUE_FLAG | text | YES | | NULL | |
| NBA_FLAG | text | YES | | NULL | |
| GAMES_PLAYED_FLAG | text | YES | | NULL | |
| DRAFT_YEAR | int | YES | | NULL | |
| DRAFT_ROUND | int | YES | | NULL | |
| DRAFT_NUMBER | int | YES | | NULL | |
| PTS | double | YES | | NULL | |
| AST | double | YES | | NULL | |
| REB | double | YES | | NULL | |
| ALL_STAR_APPEARANCES | double | YES | | NULL | |
| PIE | text | YES | | NULL | |
+----------------------------------+--------+------+-----+---------+-------+
Example Queries to Run on Database
`