<jupyter_start><jupyter_text>Designing and Creating a Database**Attention: This project is based on Dataquest.io Guided Project: Designing and Creating a Database. all decisions  about how to normalize database is taken from that guided project but with a different codeIn this project we are going to create a data  based on [Major League Baseball](https://en.wikipedia.org/wiki/Major_League_Baseball) games from [Retrosheet](https://www.retrosheet.org/)all the necessary files for this project included in the datasets folder in this repo. Reading files and exploringin this part we are going to read .csv files and explore data sets<jupyter_code>import pandas as pd
import numpy as np
import sqlite3 

db = "mlb.db"

pd.set_option('max_columns', 180)
pd.set_option('max_rows', 200000)
pd.set_option('max_colwidth', 5000)

game_log = pd.read_csv("datasets/game_log.csv")
park_codes = pd.read_csv("datasets/park_codes.csv")
person_codes = pd.read_csv("datasets/person_codes.csv")
team_codes = pd.read_csv("datasets/team_codes.csv")

print(game_log.shape)
game_log.head()
game_log.tail()<jupyter_output><empty_output><jupyter_text>as you can see in two tables above the game)log file is about games from 1871 to 2016. there is some information about each games like the date, each players and their position<jupyter_code>print(park_codes.shape)
park_codes.head()
park_codes.tail()<jupyter_output><empty_output><jupyter_text>the park_codes file contains information about the places where games took place. some information about their id and city and etc<jupyter_code>print(person_codes.shape)
person_codes.head()
person_codes.tail()<jupyter_output><empty_output><jupyter_text>person_codes file contain information about each player like id first and last names and etc<jupyter_code>print(team_codes.shape)
team_codes.head()
team_codes.tail()<jupyter_output><empty_output><jupyter_text>the team_codes is about each team name, league start and end date city and nicknames  Defencive positionsBaseball is a bat-and-ball game played between two opposing teams who take turns batting and fielding. Within the game there are positions in which each player can play in. The defensive players are known as 'fielders.There are nine fielding positions in baseball. Each position conventionally has an associated number, which is used to score putouts:1 (pitcher)                 2 (catcher)                    3 (first baseman)                4 (second baseman)                5 (third baseman)              6 (shortstop)                     7 (left fielder)                       8 (center fielder)                         9 (right fielder)                [source](https://en.wikipedia.org/wiki/Baseball_positions) Leagues in BaseballThere are just two League.  A total of 30 teams play in the National League (NL) and American League (AL), with 15 teams in each league. [source](https://en.wikipedia.org/wiki/Major_League_Baseball). but in our data base there are some other league belong to past like (PL)Players League (FL)Federal League (UA)Union Association (AA)American Association            importing data into SQLitelets first define our necessary functions like run_query() and run_command() and etc.<jupyter_code>def run_query(q):
    with sqlite3.connect(db) as conn:
        return pd.read_sql(q, conn)
    
    
def run_command(c):
    with sqlite3.connect(db) as conn:
        conn.execute('PRAGMA foreign_keys = ON;')
        conn.isolation_level = None
        conn.execute(c)
        
def create_table(dataframe, name):
    with sqlite3.connect(db) as conn:
        run_command("DROP TABLE IF EXISTS {};".format(name))
        dataframe.to_sql(name, conn, index=False)

def data_to_exist_table(dataframe, name):
    with sqlite3.connect(db) as conn:
        dataframe.to_sql(name, conn, index=False, if_exists='append')
        

def show_tables():
    q = '''
    SELECT
        name,
        type
    FROM sqlite_master
    WHERE type IN ("table","view");
    '''
    return run_query(q)<jupyter_output><empty_output><jupyter_text>Now we are going to create a table for each data set using pandas. we defined our create table function in the cell above<jupyter_code>create_table(game_log, "game_log")
create_table(park_codes, "park_codes")
create_table(person_codes, "person_codes")
create_table(team_codes, "team_codes")<jupyter_output><empty_output><jupyter_text>Now lets add game_id column to game_log table. we are going to create this column values based on this [source](https://www.retrosheet.org/eventfile.htm). `For example, ATL198304080 should be read as follows. The first three characters identify the home team (the Braves). The next four are the year (1983). The next two are the month (April) using the standard numeric notation, 04, followed by the day (08). The last digit indicates if this is a single game (0), first game (1) or second game (2) if more than one game is played during a day, usually a double header The id record starts the description of a game thus ending the description of the preceding game in the file.`<jupyter_code>new_column_gameid = '''
ALTER TABLE game_log
ADD COLUMN game_id TEXT;
'''
try:
    run_command(new_column_gameid)
except:
    pass

update_id_column = '''

UPDATE game_log
SET game_id = h_name || date || number_of_game

'''
try:
    run_command(update_id_column)
    
except:
    pass

q = '''

SELECT * FROM game_log LIMIT 5
'''
run_query(q)<jupyter_output><empty_output><jupyter_text>Normalization and creating new tableshere we are going to create our new tables. we are going to have 8 new tables(I will add the schema as soon as I can) tables are`person`                  `park`                        `league`                  `appearance_type`                 `team`                    `game`                               `team_appearance`                     `person_appearance`         Now we are going to create `person`,`park`and `league` then add values from primary tables<jupyter_code>run_command("DROP TABLE IF EXISTS person;")

creat_person_table1 = '''
CREATE TABLE person (
person_id TEXT PRIMARY KEY,
first_name TEXT,
last_name TEXT
);
'''

try:
    run_command(creat_person_table1)
except:
    pass



insert_data_person = '''
INSERT OR IGNORE INTO person
SELECT id,
       first,
       last
FROM person_codes;
'''

try:
    run_command(insert_data_person)
except:
    pass

display_few_person = '''
SELECT * FROM person
LIMIT 5
'''
run_query(display_few_person)
run_command("DROP TABLE IF EXISTS park;")

creat_park_table1 = '''
CREATE TABLE park (
park_id TEXT PRIMARY KEY,
name TEXT,
nickname TEXT,
city TEXT,
state TEXT,
notes TEXT
);
'''


try:
    run_command(creat_park_table1)
except:
    pass



insert_data_park = '''
INSERT OR IGNORE INTO park
SELECT park_id,
       name,
       aka,
       city,
       state,
       notes
FROM park_codes;
'''

try:
    run_command(insert_data_park)
except:
    print("no insert")

display_few_park = '''
SELECT * FROM park
LIMIT 5;
'''
run_query(display_few_park)
run_command("DROP TABLE IF EXISTS league;")


creat_league_table1 = '''
CREATE TABLE league (
league_id TEXT PRIMARY KEY,
name TEXT
);
'''


try:
    run_command(creat_league_table1)
except:
    pass



insert_data_league = '''
INSERT OR IGNORE INTO league
     VALUES
     ("NL", "National League"),
     ("AL", "American League"),
     ("PL", "Players League"),
     ("FL", "Federal League"),
     ("UA", "Union Association"),
     ("AA", "American Association")
     ;
'''

try:
    run_command(insert_data_league)
except:
    print("no insert")

display_few_league = '''
SELECT * FROM league;
'''
run_query(display_few_league)<jupyter_output><empty_output><jupyter_text>For `appearance_type` table we add values from appearance_type.csv file as you can see in the code blow<jupyter_code>run_command("DROP TABLE IF EXISTS appearance_type;")

appearance_type = pd.read_csv("datasets/appearance_type.csv")


creat_appearance_type_table1 = '''
CREATE TABLE appearance_type (
appearance_type_id TEXT PRIMARY KEY,
name TEXT,
category TEXT
);
'''

try:
    run_command(creat_appearance_type_table1)
except:
    pass

data_to_exist_table(appearance_type, "appearance_type")


display_few_appearance_type = '''
SELECT * FROM appearance_type;
'''
run_query(display_few_appearance_type)
run_command("DROP TABLE IF EXISTS team;")

creat_team_table1 = '''
CREATE TABLE team (
team_id TEXT PRIMARY KEY,
league_id TEXT,
city TEXT,
nickname TEXT,
franch_id TEXT,
FOREIGN KEY (league_id) REFERENCES league(league_id)
);
'''


try:
    run_command(creat_team_table1)
except:
    pass




insert_data_team = '''
INSERT OR IGNORE INTO team
SELECT team_id,
       league,
       city,
       nickname,
       franch_id
FROM team_codes;
'''

try:
    run_command(insert_data_team)
except:
    print("no insert")

display_few_team = '''
SELECT * FROM team
LIMIT 5;
'''
run_query(display_few_team)
run_command("DROP TABLE IF EXISTS game;")


creat_game_table1 = '''
CREATE TABLE game (
      game_id TEXT PRIMARY KEY,
      date TEXT,
      number_of_game INTEGER,
      park_id TEXT,
      length_outs INTEGER,
      day BOOLEAN,
      completion TEXT,
      forefeit TEXT, 
      protest TEXT,
      attendance INTEGER,
      length_minutes INTEGER,
      additional_info TEXT,
      acquisition_info TEXT,
      FOREIGN KEY (park_id) REFERENCES park(park_id)
);
'''


try:
    run_command(creat_game_table1)
except:
    print("no table added")




insert_data_game = '''
INSERT OR IGNORE INTO game
SELECT 
      game_id, 
      date,
      number_of_game,
      park_id,
      length_outs,
      day_night,
      completion,
      forefeit, 
      protest,
      attendance,
      length_minutes,
      additional_info, 
      acquisition_info
FROM game_log;
'''

try:
    run_command(insert_data_game)
except:
    print("no insert")

display_few_game = '''
SELECT * FROM game
LIMIT 5;
'''
run_query(display_few_game)
run_command("DROP TABLE IF EXISTS team_appearance;")


creat_team_appearance_table1 = '''
CREATE TABLE team_appearance (
team_id TEXT,
game_id TEXT,
home BOOLEAN,
league_id TEXT,
score INTEGER,
line_score TEXT,
at_bats INTEGER,
hits INTEGER,
doubles INTEGER,
triples INTEGER,
homeruns INTEGER,
rbi INTEGER,
sacrifice_hits INTEGER,
sacrifice_flies INTEGER,
hit_by_pitch INTEGER,
walks INTEGER,
intentional_walks INTEGER,
strikeouts INTEGER,
stolen_bases INTEGER,
caught_stealing INTEGER,
grounded_into_double INTEGER,
first_catcher_interference INTEGER,
left_on_base INTEGER,
pitchers_used INTEGER,
individual_earned_runs INTEGER,
team_earned_runs INTEGER,
wild_pitches INTEGER,
balks INTEGER,
putouts INTEGER,
assists INTEGER,
errors INTEGER,
passed_balls INTEGER,
double_plays INTEGER,
triple_plays INTEGER,
PRIMARY KEY (team_id, game_id),
FOREIGN KEY (team_id) REFERENCES team(team_id),
FOREIGN KEY (game_id) REFERENCES game(game_id),
FOREIGN KEY (team_id) REFERENCES team(team_id)
);
'''


try:
    run_command(creat_team_appearance_table1)
except:
    print("no table added")




insert_data_team_appearance = '''
INSERT OR IGNORE INTO team_appearance
SELECT
    h_name,
    game_id,
    1 AS home,
    h_league,
    h_score,
    h_line_score,
    h_at_bats,
    h_hits,
    h_doubles,
    h_triples,
    h_homeruns,
    h_rbi,
    h_sacrifice_hits,
    h_sacrifice_flies,
    h_hit_by_pitch,
    h_walks,
    h_intentional_walks,
    h_strikeouts,
    h_stolen_bases,
    h_caught_stealing,
    h_grounded_into_double,
    h_first_catcher_interference,
    h_left_on_base,
    h_pitchers_used,
    h_individual_earned_runs,
    h_team_earned_runs,
    h_wild_pitches,
    h_balks,
    h_putouts,
    h_assists,
    h_errors,
    h_passed_balls,
    h_double_plays,
    h_triple_plays
    FROM game_log

UNION

SELECT    
    v_name,
    game_id,
    0 AS home,
    v_league,
    v_score,
    v_line_score,
    v_at_bats,
    v_hits,
    v_doubles,
    v_triples,
    v_homeruns,
    v_rbi,
    v_sacrifice_hits,
    v_sacrifice_flies,
    v_hit_by_pitch,
    v_walks,
    v_intentional_walks,
    v_strikeouts,
    v_stolen_bases,
    v_caught_stealing,
    v_grounded_into_double,
    v_first_catcher_interference,
    v_left_on_base,
    v_pitchers_used,
    v_individual_earned_runs,
    v_team_earned_runs,
    v_wild_pitches,
    v_balks,
    v_putouts,
    v_assists,
    v_errors,
    v_passed_balls,
    v_double_plays,
    v_triple_plays
from game_log;
'''

try:
    run_command(insert_data_team_appearance)
except:
    print("no insert")

display_few_team_appearance = '''
SELECT * FROM team_appearance
LIMIT 5;
'''
run_query(display_few_team_appearance)<jupyter_output><empty_output><jupyter_text>The difficult part is adding values for `person_appearance` table. for this task we created two template queries .for the first one we iterate over `dict1` dictionary and add different values for this table. for the second one we place 1 to 9 numbers for home and visitor teams in each match for offence and defence players<jupyter_code>run_command("DROP TABLE IF EXISTS person_appearance;")

#creating a table
creat_person_appearance_table1 = '''
CREATE TABLE person_appearance (
    appearance_id INTEGER PRIMARY KEY,
    person_id TEXT,
    team_id TEXT,
    game_id TEXT,
    appearance_type_id,
    FOREIGN KEY (person_id) REFERENCES person(person_id),
    FOREIGN KEY (team_id) REFERENCES team(team_id),
    FOREIGN KEY (game_id) REFERENCES game(game_id),
    FOREIGN KEY (appearance_type_id) REFERENCES appearance_type(appearance_type_id)
);
'''
try:
    run_command(creat_person_appearance_table1)
except:
    print("table is not created")


#adding values for table    
    
case1 = """ CASE
            WHEN h_score > v_score THEN h_name
            ELSE v_name
            END
        """
case2 = """ CASE
            WHEN h_score < v_score THEN h_name
            ELSE v_name
            END
        """


dict1 = {"hp_umpire_id": ["UHP",   "NULL"],
         "1b_umpire_id": ["U1B",   "NULL"],
         "2b_umpire_id": ["U2B",   "NULL"],
         "3b_umpire_id": ["U3B",   "NULL"],
         "lf_umpire_id": ["ULF",   "NULL"],
         "rf_umpire_id": ["URF",   "NULL"],
         "v_manager_id": ["MM",  "v_name"],
         "h_manager_id": ["MM",  "h_name"],
         "v_starting_pitcher_id": ["PSP",  "v_name"], 
         "h_starting_pitcher_id": ["PSP",  "h_name"],   
         "winning_pitcher_id": ["AWP",  case1], 
         "losing_pitcher_id": ["ALP",  case2], 
         "saving_pitcher_id": ["ASP",  case1], 
         "winning_rbi_batter_id": ["AWB",  case1], 
}

template = '''
INSERT INTO person_appearance (
    game_id,
    team_id,
    person_id,
    appearance_type_id
) 
    SELECT
        game_id,
        {team_name},
        [{col}],
        "{name}"
    FROM game_log
    WHERE [{col}] IS NOT NULL
'''

for key, value in dict1.items():
    run_command(template.format(col = key, name = value[0], team_name = value[1]))


template2 = '''
INSERT INTO person_appearance (
    game_id,
    team_id,
    person_id,
    appearance_type_id
) 
SELECT
    game_id,
    {h_or_v}_name,
    {h_or_v}_player_{number}_id,
    "O{number}"
FROM game_log
WHERE {h_or_v}_player_{number}_id IS NOT NULL

UNION

SELECT
    game_id,
    {h_or_v}_name,
    {h_or_v}_player_{number}_id,
    "D" || CAST({h_or_v}_player_{number}_def_pos AS INT)
FROM game_log
WHERE {h_or_v}_player_{number}_id IS NOT NULL;
'''
for h_v in ["h","v"]:
    for i in range(1,10):
        run_command(template2.format(h_or_v = h_v, number = i))

        
    
display_few_person_appearance = '''
SELECT * FROM person_appearance
WHERE game_id = "ANA200004030"
LIMIT 20;
'''
run_query(display_few_person_appearance)<jupyter_output><empty_output><jupyter_text>lets drop our primary tables and show the final tables list.<jupyter_code>run_command("DROP TABLE IF EXISTS game_log;")
run_command("DROP TABLE IF EXISTS park_codes;")
run_command("DROP TABLE IF EXISTS gameteam_codes_log;")
run_command("DROP TABLE IF EXISTS person_codes;")
show_tables()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08945786903718543, 'fraction_numerical': 0.007449605609114811, 'mean_word_length': 3.748810939357907, 'pattern_counts': {'":': 14, '<': 46, '<?xml version=': 0, '>': 46, 'https://': 5, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '87552', 'license': 'MIT', 'n_tokens_mistral': 5586, 'n_tokens_neox': 5253, 'n_words': 1453, 'path': 'database_design.ipynb', 'repo_name': 'alinasr/Database_create_SQLite3_python', 'size': '15974'}