Data-Preparation/Netflix DataSet - Data Preparation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "9d4d3925",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3 as sql\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "61b23973",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8807, 12)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Loading the Dataset\n",
    "\n",
    "df = pd.read_csv('netflix_titles.csv')\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "d0a30556",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5332, 11)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Making some simple cleanning operations over the data\n",
    "\n",
    "df.drop(columns=['show_id'], inplace=True)\n",
    "df.dropna(how='any', axis=0, inplace=True)\n",
    "df.rename(columns={'listed_in':'genre'},inplace=True)\n",
    "df.reset_index(drop=True, inplace=True)\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "049d29ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Converting all attributes with multiple values (as a string) into a list\n",
    "\n",
    "attributes = ['director', 'cast', 'country', 'genre']\n",
    "for i in attributes:\n",
    "    df[i] = df[i].str.split(\", \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "5d88628b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>director</th>\n",
       "      <th>cast</th>\n",
       "      <th>country</th>\n",
       "      <th>date_added</th>\n",
       "      <th>release_year</th>\n",
       "      <th>rating</th>\n",
       "      <th>duration</th>\n",
       "      <th>genre</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Movie</td>\n",
       "      <td>Sankofa</td>\n",
       "      <td>[Haile Gerima]</td>\n",
       "      <td>[Kofi Ghanaba, Oyafunmike Ogunlano, Alexandra ...</td>\n",
       "      <td>[United States, Ghana, Burkina Faso, United Ki...</td>\n",
       "      <td>September 24, 2021</td>\n",
       "      <td>1993</td>\n",
       "      <td>TV-MA</td>\n",
       "      <td>125 min</td>\n",
       "      <td>[Dramas, Independent Movies, International Mov...</td>\n",
       "      <td>On a photo shoot in Ghana, an American model s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TV Show</td>\n",
       "      <td>The Great British Baking Show</td>\n",
       "      <td>[Andy Devonshire]</td>\n",
       "      <td>[Mel Giedroyc, Sue Perkins, Mary Berry, Paul H...</td>\n",
       "      <td>[United Kingdom]</td>\n",
       "      <td>September 24, 2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>TV-14</td>\n",
       "      <td>9 Seasons</td>\n",
       "      <td>[British TV Shows, Reality TV]</td>\n",
       "      <td>A talented batch of amateur bakers face off in...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Movie</td>\n",
       "      <td>The Starling</td>\n",
       "      <td>[Theodore Melfi]</td>\n",
       "      <td>[Melissa McCarthy, Chris O'Dowd, Kevin Kline, ...</td>\n",
       "      <td>[United States]</td>\n",
       "      <td>September 24, 2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>104 min</td>\n",
       "      <td>[Comedies, Dramas]</td>\n",
       "      <td>A woman adjusting to life after a loss contend...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      type                          title           director  \\\n",
       "0    Movie                        Sankofa     [Haile Gerima]   \n",
       "1  TV Show  The Great British Baking Show  [Andy Devonshire]   \n",
       "2    Movie                   The Starling   [Theodore Melfi]   \n",
       "\n",
       "                                                cast  \\\n",
       "0  [Kofi Ghanaba, Oyafunmike Ogunlano, Alexandra ...   \n",
       "1  [Mel Giedroyc, Sue Perkins, Mary Berry, Paul H...   \n",
       "2  [Melissa McCarthy, Chris O'Dowd, Kevin Kline, ...   \n",
       "\n",
       "                                             country          date_added  \\\n",
       "0  [United States, Ghana, Burkina Faso, United Ki...  September 24, 2021   \n",
       "1                                   [United Kingdom]  September 24, 2021   \n",
       "2                                    [United States]  September 24, 2021   \n",
       "\n",
       "   release_year rating   duration  \\\n",
       "0          1993  TV-MA    125 min   \n",
       "1          2021  TV-14  9 Seasons   \n",
       "2          2021  PG-13    104 min   \n",
       "\n",
       "                                               genre  \\\n",
       "0  [Dramas, Independent Movies, International Mov...   \n",
       "1                     [British TV Shows, Reality TV]   \n",
       "2                                 [Comedies, Dramas]   \n",
       "\n",
       "                                         description  \n",
       "0  On a photo shoot in Ghana, an American model s...  \n",
       "1  A talented batch of amateur bakers face off in...  \n",
       "2  A woman adjusting to life after a loss contend...  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dropping incorrect rows\n",
    "\n",
    "row  = 0\n",
    "for lista_country in df['country']:\n",
    "    if ('' in lista_country):\n",
    "        df.drop(row, inplace=True)\n",
    "    row += 1\n",
    "df.reset_index(drop=True, inplace=True)\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f749f2af",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8372e9f",
   "metadata": {},
   "source": [
    "<span style=\"font-family: TimesNewRoman;font-weight:20;font-size:30px\">\n",
    "<center><b>Dados da Tabela <i>Type</i></b></center>\n",
    "</span>\n",
    "<p></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "34e0d988",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Movie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TV Show</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            type\n",
       "type_id         \n",
       "0          Movie\n",
       "1        TV Show"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Organizing the data destined to the Type Table\n",
    "\n",
    "list_types = sorted(df['type'].unique())\n",
    "\n",
    "type_converter = {}\n",
    "\n",
    "for i in range(len(list_types)):\n",
    "    type_converter.update({list_types[i]:i})\n",
    "\n",
    "dict_types = {'type':list_types}\n",
    "Type_Table = pd.DataFrame(dict_types)\n",
    "Type_Table.index.name = 'type_id'\n",
    "Type_Table.to_csv('Output_Tables/Type_Table.csv', sep=',')\n",
    "Type_Table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27d596b6",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcde311e",
   "metadata": {},
   "source": [
    "<span style=\"font-family: TimesNewRoman;font-weight:20;font-size:30px\">\n",
    "<center><b>Dados da Tabela <i>Job</i></b></center>\n",
    "</span>\n",
    "<p></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "15894476",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>job_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cast</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>director</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            name\n",
       "job_id          \n",
       "0           cast\n",
       "1       director"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "job_list = ['cast', 'director']\n",
    "job_dict = {'name':job_list}\n",
    "\n",
    "job_converter = {'cast':0, 'director':1}\n",
    "\n",
    "Job_Table = pd.DataFrame(job_dict)\n",
    "Job_Table.index.name='job_id'\n",
    "Job_Table.to_csv('Output_Tables/Job_Table.csv', sep=',')\n",
    "Job_Table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce0e8430",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14beb3d6",
   "metadata": {},
   "source": [
    "<span style=\"font-family: TimesNewRoman;font-weight:20;font-size:30px\">\n",
    "<center><b>Dados da Tabela <i>Rating</i></b></center>\n",
    "</span>\n",
    "<p></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "46b68a89",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>acronym</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rating_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>G</td>\n",
       "      <td>General Audiences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NC-17</td>\n",
       "      <td>No children under 17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NR</td>\n",
       "      <td>Not Rated</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PG</td>\n",
       "      <td>Parental Guidance Suggested</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PG-13</td>\n",
       "      <td>Parents Strongly Cautioned, Some Material May ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>R</td>\n",
       "      <td>Restricted [Requires accompanying parent or ad...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TV-14</td>\n",
       "      <td>This program contains material that most paren...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>TV-G</td>\n",
       "      <td>General Audience</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>TV-MA</td>\n",
       "      <td>Mature Audience Only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>TV-PG</td>\n",
       "      <td>Parental Guidance Suggested</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          acronym                                        description\n",
       "rating_id                                                           \n",
       "0               G                                  General Audiences\n",
       "1           NC-17                               No children under 17\n",
       "2              NR                                          Not Rated\n",
       "3              PG                        Parental Guidance Suggested\n",
       "4           PG-13  Parents Strongly Cautioned, Some Material May ...\n",
       "5               R  Restricted [Requires accompanying parent or ad...\n",
       "6           TV-14  This program contains material that most paren...\n",
       "7            TV-G                                   General Audience\n",
       "8           TV-MA                               Mature Audience Only\n",
       "9           TV-PG                        Parental Guidance Suggested"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Organizing the data destined to the Rating Table\n",
    "\n",
    "list_ratings = sorted(df['rating'].unique())\n",
    "\n",
    "rating_converter = {}\n",
    "\n",
    "for i in range(len(list_ratings)):\n",
    "    rating_converter.update({list_ratings[i]:i})\n",
    "\n",
    "# Acronym description added\n",
    "dict_discription = {'G':'General Audiences',\n",
    "                   'NC-17':'No children under 17',\n",
    "                   'NR':'Not Rated',\n",
    "                   'PG':'Parental Guidance Suggested',\n",
    "                   'PG-13':'Parents Strongly Cautioned, Some Material May be Inappropriate for Children Under 13',\n",
    "                   'R':'Restricted [Requires accompanying parent or adult guardian]',\n",
    "                   'TV-14':'This program contains material that most parents would find unsuitable for children under 14',\n",
    "                   'TV-G':'General Audience',\n",
    "                   'TV-MA':'Mature Audience Only',\n",
    "                   'TV-PG':'Parental Guidance Suggested',\n",
    "                   'TV-Y':'Appropriate for All Ages',\n",
    "                   'TV-Y7':'Most Appropriate for Children age 7 and Up',\n",
    "                   'TV-Y7-FV':'Contains <fantasy violence> that may be intense or combative than other TV-Y7 programs',\n",
    "                   'UR':'Unrated'}\n",
    "\n",
    "list_discriptions = []\n",
    "for rating in list_ratings:\n",
    "    list_discriptions.append(dict_discription[rating])\n",
    "    \n",
    "dict_rating = {'acronym':list_ratings, 'description':list_discriptions}\n",
    "Rating_Table = pd.DataFrame(dict_rating)\n",
    "Rating_Table.index.name = 'rating_id'\n",
    "Rating_Table.to_csv('Output_Tables/Rating_Table.csv', sep=',')\n",
    "Rating_Table.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0eecb71c",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed8b0f6d",
   "metadata": {},
   "source": [
    "<span style=\"font-family: TimesNewRoman;font-weight:20;font-size:30px\">\n",
    "<center><b>Dados da Tabela <i>Genre</i></b></center>\n",
    "</span>\n",
    "<p></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "073ab928",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>genre_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Action &amp; Adventure</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anime Features</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Anime Series</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>British TV Shows</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Children &amp; Family Movies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Classic &amp; Cult TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Classic Movies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Comedies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Crime TV Shows</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Cult Movies</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              name\n",
       "genre_id                          \n",
       "0               Action & Adventure\n",
       "1                   Anime Features\n",
       "2                     Anime Series\n",
       "3                 British TV Shows\n",
       "4         Children & Family Movies\n",
       "5                Classic & Cult TV\n",
       "6                   Classic Movies\n",
       "7                         Comedies\n",
       "8                   Crime TV Shows\n",
       "9                      Cult Movies"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Organizing the data destined to the Genre Table\n",
    "\n",
    "list_genre = sorted(df['genre'].sum())\n",
    "new_list_genre = []\n",
    "genre_converter = {}\n",
    "idx = 0\n",
    "\n",
    "for genre in list_genre:\n",
    "    if (genre not in new_list_genre):\n",
    "        new_list_genre.append(genre)\n",
    "        genre_converter.update({genre:idx})\n",
    "        idx += 1\n",
    "\n",
    "dict_genre = {'name':new_list_genre}\n",
    "\n",
    "Genre_Table = pd.DataFrame(dict_genre)\n",
    "Genre_Table.index.name = 'genre_id'\n",
    "Genre_Table.to_csv('Output_Tables/Genre_Table.csv', sep=',')\n",
    "Genre_Table.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8c3520d",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b9e29b5",
   "metadata": {},
   "source": [
    "<span style=\"font-family: TimesNewRoman;font-weight:20;font-size:30px\">\n",
    "<center><b>Dados da Tabela <i>Country</i></b></center>\n",
    "</span>\n",
    "<p></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "4e34058b",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Angola</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name\n",
       "country_id             \n",
       "0           Afghanistan\n",
       "1               Albania\n",
       "2               Algeria\n",
       "3                Angola\n",
       "4             Argentina"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Organizing the data destined to the Country Table\n",
    "\n",
    "df['country'] = df['country'].apply(lambda x: [country_name.replace(',', '') for country_name in x])\n",
    "list_countries = sorted(df['country'].sum())\n",
    "new_list_countries = []\n",
    "country_converter = {}\n",
    "idx = 0\n",
    "\n",
    "for country in list_countries:\n",
    "    if (country not in new_list_countries):\n",
    "        new_list_countries.append(country)\n",
    "        country_converter.update({country:idx})\n",
    "        idx += 1\n",
    "    \n",
    "dict_countries = {'name':new_list_countries}\n",
    "\n",
    "Country_Table = pd.DataFrame(dict_countries)\n",
    "Country_Table.index.name = 'country_id'\n",
    "Country_Table.to_csv('Output_Tables/Country_Table.csv', sep=',')\n",
    "Country_Table.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bc41f73",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87f13a78",
   "metadata": {},
   "source": [
    "<span style=\"font-family: TimesNewRoman;font-weight:20;font-size:30px\">\n",
    "<center><b>Dados da Tabela <i>Person</i></b></center>\n",
    "</span>\n",
    "<p></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "a56fb742",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>person_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jr.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>\"Riley\" Lakdhar Dridi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>'Najite Dede</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2Mex</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>50 Cent</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            name\n",
       "person_id                       \n",
       "0                            Jr.\n",
       "1          \"Riley\" Lakdhar Dridi\n",
       "2                   'Najite Dede\n",
       "3                           2Mex\n",
       "4                        50 Cent"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Organizing the data destined to the Person Table\n",
    "\n",
    "df_people = pd.DataFrame(columns=['people'])\n",
    "\n",
    "df_people['people'] = df['director'] + df['cast']\n",
    "people_list = sorted(df_people['people'].sum())\n",
    "new_people_list = [] # To store all unique values\n",
    "people_converter = {}\n",
    "idx = 0\n",
    "\n",
    "for person in people_list:\n",
    "    if (person not in new_people_list):\n",
    "        people_converter.update({person:idx})\n",
    "        new_people_list.append(person)\n",
    "        idx += 1\n",
    "\n",
    "Person_Table = pd.DataFrame(columns=[\"person_id\", \"name\"])\n",
    "\n",
    "for key in people_converter:\n",
    "    Person_Table.loc[len(Person_Table)] = [people_converter[key], key]\n",
    "    \n",
    "Person_Table.set_index('person_id', inplace=True)\n",
    "Person_Table.to_csv('Output_Tables/Person_Table.csv', sep=',')\n",
    "Person_Table.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a9224e0",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0d0f46b",
   "metadata": {},
   "source": [
    "<span style=\"font-family: TimesNewRoman;font-weight:20;font-size:30px\">\n",
    "<center><b>Dados da Tabela <i>Show_Genre</i></b></center>\n",
    "</span>\n",
    "<p></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "d2fd3c76",
   "metadata": {
    "code_folding": [],
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>genre_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>show_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         genre_id\n",
       "show_id          \n",
       "0              12\n",
       "0              15\n",
       "0              16\n",
       "1               3\n",
       "1              23"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Show_Genre_Table = pd.DataFrame(columns=[\"show_id\", \"genre_id\"])\n",
    "\n",
    "for show_id in range (df.shape[0]):\n",
    "    list_genre = df.loc[show_id]['genre']\n",
    "    for genre in list_genre:\n",
    "        Show_Genre_Table.loc[len(Show_Genre_Table)] = [show_id, genre_converter[genre]]\n",
    "\n",
    "Show_Genre_Table.set_index('show_id', inplace=True)\n",
    "Show_Genre_Table.to_csv('Output_Tables/Show_Genre_Table.csv', sep=',')\n",
    "\n",
    "# Eliminação da Coluna 'genre'\n",
    "\n",
    "df.drop(columns=['genre'], inplace=True)\n",
    "\n",
    "Show_Genre_Table.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "faf7c912",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94021ee0",
   "metadata": {},
   "source": [
    "<span style=\"font-family: TimesNewRoman;font-weight:20;font-size:30px\">\n",
    "    <center><b>Dados da Tabela <i>Show_Country</i> </b></center>\n",
    "</span>\n",
    "<p></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "96a2e504",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>show_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         country_id\n",
       "show_id            \n",
       "0               102\n",
       "0                33\n",
       "0                13\n",
       "0               101\n",
       "0                32"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Show_Country_Table = pd.DataFrame(columns=[\"show_id\", \"country_id\"])\n",
    "\n",
    "for show_id in range (df.shape[0]):\n",
    "    list_country = df.loc[show_id]['country']\n",
    "    for country in list_country:\n",
    "        Show_Country_Table.loc[len(Show_Country_Table)] = [show_id, country_converter[country]]\n",
    "\n",
    "Show_Country_Table.set_index('show_id', inplace=True)\n",
    "Show_Country_Table.to_csv('Output_Tables/Show_Country_Table.csv', sep=',')\n",
    "\n",
    "# Eliminação da Coluna 'country'\n",
    "\n",
    "df.drop(columns=['country'], inplace=True)\n",
    "\n",
    "Show_Country_Table.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66d5c61b",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "131a5e67",
   "metadata": {},
   "source": [
    "<span style=\"font-family: TimesNewRoman;font-weight:20;font-size:30px\">\n",
    "    <center><b>Dados da Tabela <i>Show_Person_Job</i></b></center>\n",
    "</span>\n",
    "<p></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "02a79709",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>person_id</th>\n",
       "      <th>job_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>show_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14934</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20546</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1032</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19823</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>19186</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         person_id  job_id\n",
       "show_id                   \n",
       "0            14934       0\n",
       "0            20546       0\n",
       "0             1032       0\n",
       "0            19823       0\n",
       "0            19186       0"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Show_Person_Job_Table = pd.DataFrame(columns=['show_id','person_id', 'job_id'])\n",
    "\n",
    "for show_id in range (df.shape[0]):\n",
    "    list_cast = df.loc[show_id]['cast']\n",
    "    list_directors = df.loc[show_id]['director']\n",
    "    \n",
    "    for actor in list_cast:\n",
    "        Show_Person_Job_Table.loc[len(Show_Person_Job_Table)] = [show_id, people_converter[actor], 0]\n",
    "    \n",
    "    for director in list_directors:\n",
    "        Show_Person_Job_Table.loc[len(Show_Person_Job_Table)] = [show_id, people_converter[director], 1]\n",
    "\n",
    "\n",
    "Show_Person_Job_Table.drop_duplicates(keep='first', inplace=True)\n",
    "Show_Person_Job_Table.set_index('show_id', inplace=True)\n",
    "Show_Person_Job_Table.to_csv('Output_Tables/Show_Person_Job_Table.csv', sep=',')\n",
    "\n",
    "# Eliminação das Colunas 'director' e 'cast'\n",
    "\n",
    "df.drop(columns=['director', 'cast'], inplace=True)\n",
    "\n",
    "Show_Person_Job_Table.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51c34940",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1244a48",
   "metadata": {},
   "source": [
    "<span style=\"font-family: TimesNewRoman;font-weight:20;font-size:30px\">\n",
    "<center><b>Dados da Tabela <i>Show</i></b></center>\n",
    "</span>\n",
    "<p></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "da8cb367",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type_id</th>\n",
       "      <th>title</th>\n",
       "      <th>date_added</th>\n",
       "      <th>release_year</th>\n",
       "      <th>rating_id</th>\n",
       "      <th>duration</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>show_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Sankofa</td>\n",
       "      <td>September 24, 2021</td>\n",
       "      <td>1993</td>\n",
       "      <td>8</td>\n",
       "      <td>125 min</td>\n",
       "      <td>On a photo shoot in Ghana, an American model s...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>The Great British Baking Show</td>\n",
       "      <td>September 24, 2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>6</td>\n",
       "      <td>9 Seasons</td>\n",
       "      <td>A talented batch of amateur bakers face off in...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>The Starling</td>\n",
       "      <td>September 24, 2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>4</td>\n",
       "      <td>104 min</td>\n",
       "      <td>A woman adjusting to life after a loss contend...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>Je Suis Karl</td>\n",
       "      <td>September 23, 2021</td>\n",
       "      <td>2021</td>\n",
       "      <td>8</td>\n",
       "      <td>127 min</td>\n",
       "      <td>After most of her family is murdered in a terr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>Jeans</td>\n",
       "      <td>September 21, 2021</td>\n",
       "      <td>1998</td>\n",
       "      <td>6</td>\n",
       "      <td>166 min</td>\n",
       "      <td>When the father of the man she loves insists t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         type_id                          title          date_added  \\\n",
       "show_id                                                               \n",
       "0              0                        Sankofa  September 24, 2021   \n",
       "1              1  The Great British Baking Show  September 24, 2021   \n",
       "2              0                   The Starling  September 24, 2021   \n",
       "3              0                   Je Suis Karl  September 23, 2021   \n",
       "4              0                          Jeans  September 21, 2021   \n",
       "\n",
       "         release_year  rating_id   duration  \\\n",
       "show_id                                       \n",
       "0                1993          8    125 min   \n",
       "1                2021          6  9 Seasons   \n",
       "2                2021          4    104 min   \n",
       "3                2021          8    127 min   \n",
       "4                1998          6    166 min   \n",
       "\n",
       "                                               description  \n",
       "show_id                                                     \n",
       "0        On a photo shoot in Ghana, an American model s...  \n",
       "1        A talented batch of amateur bakers face off in...  \n",
       "2        A woman adjusting to life after a loss contend...  \n",
       "3        After most of her family is murdered in a terr...  \n",
       "4        When the father of the man she loves insists t...  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Simplificação de Colunas\n",
    "\n",
    "df['rating'] = df['rating'].apply(lambda x: rating_converter[x])\n",
    "df['type'] = df['type'].apply(lambda x: type_converter[x])\n",
    "\n",
    "# Renomeação de Colunas\n",
    "\n",
    "df.rename(columns={'rating':'rating_id',\n",
    "                   'type':'type_id'}, inplace=True)\n",
    "\n",
    "# No fim de contas o df final vai corresponder à tabela principal: 'Show'\n",
    "\n",
    "df.index.name = 'show_id'\n",
    "df.to_csv('Output_Tables/Show_Table.csv', sep=',')\n",
    "Show_Table = df\n",
    "Show_Table.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9865860c",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b67022d4",
   "metadata": {},
   "source": [
    "<span style=\"font-family: TimesNewRoman;font-weight:20;font-size:30px\">\n",
    "<center><b>Criação das Tabelas e Inserção dos Dados</b></center>\n",
    "</span>\n",
    "<p></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "52fc9303",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Criação da Base de Dados\n",
    "\n",
    "DATABASE_NAME = 'Netflix.db'\n",
    "\n",
    "# Se o Ficheiro da Base de Dados existir, é eliminado (visto poder ser uma versão antiga)\n",
    "if os.path.isfile(DATABASE_NAME):\n",
    "    os.remove(DATABASE_NAME)\n",
    "\n",
    "# Criar a Conexão (Como o ficheiro deixou de existir, é criado um novo)\n",
    "conn = sql.connect(DATABASE_NAME)\n",
    "cur = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "2e639ef8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Foram Adicionados 2 Tipos com Sucesso!\n"
     ]
    }
   ],
   "source": [
    "# Creating Type table\n",
    "\n",
    "Type_Table_Query = '''CREATE TABLE IF NOT EXISTS Type (\n",
    "                      type_id INT,\n",
    "                      type CHAR(25),\n",
    "                      PRIMARY KEY (type_id)\n",
    "                      ); '''\n",
    "\n",
    "cur.execute(Type_Table_Query)\n",
    "\n",
    "n_types = Type_Table.to_sql(name='Type', con=conn, if_exists='append')\n",
    "print(f'Foram Adicionados {n_types} Tipos com Sucesso!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "e8f4ae9d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Foram Adicionados 2 Trabalhos/Profissões com Sucesso!\n"
     ]
    }
   ],
   "source": [
    "# Creating Job table\n",
    "\n",
    "Job_Table_Query = '''CREATE TABLE IF NOT EXISTS Job (\n",
    "                      job_id INT,\n",
    "                      name CHAR(25),\n",
    "                      PRIMARY KEY (job_id)\n",
    "                      ); '''\n",
    "\n",
    "cur.execute(Job_Table_Query)\n",
    "\n",
    "n_jobs = Job_Table.to_sql(name='Job', con=conn, if_exists='append')\n",
    "print(f'Foram Adicionados {n_jobs} Trabalhos/Profissões com Sucesso!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "977f22c3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Foram Adicionados 14 Rating's com Sucesso!\n"
     ]
    }
   ],
   "source": [
    "# Creating Rating table\n",
    "\n",
    "Rating_Table_Query = '''CREATE TABLE IF NOT EXISTS Rating (\n",
    "                        rating_id INT,\n",
    "                        acronym CHAR(25),\n",
    "                        description CHAR(60),\n",
    "                        PRIMARY KEY (rating_id)\n",
    "                        ); '''\n",
    "\n",
    "cur.execute(Rating_Table_Query)\n",
    "\n",
    "n_ratings = Rating_Table.to_sql(name='Rating', con=conn, if_exists='append')\n",
    "print(f'Foram Adicionados {n_ratings} Rating\\'s com Sucesso!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "67dc3af2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Foram Adicionados 42 Géneros com Sucesso!\n"
     ]
    }
   ],
   "source": [
    "# Creating Genre table\n",
    "\n",
    "Genre_Table_Query = '''CREATE TABLE IF NOT EXISTS Genre (\n",
    "                       genre_id INT,\n",
    "                       name CHAR(30),\n",
    "                       PRIMARY KEY (genre_id)\n",
    "                       ); '''\n",
    "\n",
    "cur.execute(Genre_Table_Query)\n",
    "\n",
    "n_genres = Genre_Table.to_sql(name='Genre', con=conn, if_exists='append')\n",
    "print(f'Foram Adicionados {n_genres} Géneros com Sucesso!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "bd2af94f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Foram Adicionados 109 Países com Sucesso!\n"
     ]
    }
   ],
   "source": [
    "# Creating Country table\n",
    "\n",
    "Country_Table_Query = '''CREATE TABLE IF NOT EXISTS Country (\n",
    "                         country_id INT,\n",
    "                         name CHAR(30),\n",
    "                         PRIMARY KEY (country_id)\n",
    "                         ); '''\n",
    "\n",
    "cur.execute(Country_Table_Query)\n",
    "\n",
    "n_countries = Country_Table.to_sql(name='Country', con=conn, if_exists='append')\n",
    "print(f'Foram Adicionados {n_countries} Países com Sucesso!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "254cb919",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Foram Adicionados 29337 Pessoas com Sucesso!\n"
     ]
    }
   ],
   "source": [
    "# Creating Person table\n",
    "\n",
    "Person_Table_Query = '''CREATE TABLE IF NOT EXISTS Person (\n",
    "                        person_id INT,\n",
    "                        name CHAR(30),\n",
    "                        PRIMARY KEY (person_id)\n",
    "                        ); '''\n",
    "\n",
    "cur.execute(Person_Table_Query)\n",
    "\n",
    "n_persons = Person_Table.to_sql(name='Person', con=conn, if_exists='append')\n",
    "print(f'Foram Adicionados {n_persons} Pessoas com Sucesso!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "b343e88a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Foram Adicionados 5331 Shows com Sucesso!\n"
     ]
    }
   ],
   "source": [
    "# Creating Show table\n",
    "\n",
    "Show_Table_Query = '''CREATE TABLE IF NOT EXISTS Show (\n",
    "                      show_id INT,\n",
    "                      type_id INT,\n",
    "                      rating_id INT,\n",
    "                      title CHAR(20),\n",
    "                      date_added DATE,\n",
    "                      release_year INT,\n",
    "                      duration CHAR(20),\n",
    "                      description CHAR(60),\n",
    "                      PRIMARY KEY (show_id),\n",
    "                      FOREIGN KEY (type_id) REFERENCES Type(type_id),\n",
    "                      FOREIGN KEY (rating_id) REFERENCES Rating(rating_id)\n",
    "                      ); '''\n",
    "\n",
    "cur.execute(Show_Table_Query)\n",
    "\n",
    "n_shows = Show_Table.to_sql(name='Show', con=conn, if_exists='append')\n",
    "print(f'Foram Adicionados {n_shows} Shows com Sucesso!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "dac2f597",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "''"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating Show_Genre table\n",
    "\n",
    "Show_Genre_Table_Query = '''CREATE TABLE IF NOT EXISTS Show_Genre (\n",
    "                            show_id INT,\n",
    "                            genre_id INT,\n",
    "                            PRIMARY KEY (show_id, genre_id),\n",
    "                            FOREIGN KEY (show_id) REFERENCES Show(show_id),\n",
    "                            FOREIGN KEY (genre_id) REFERENCES Genre(genre_id)\n",
    "                            ); '''\n",
    "\n",
    "cur.execute(Show_Genre_Table_Query)\n",
    "\n",
    "Show_Genre_Table.to_sql(name='Show_Genre', con=conn, if_exists='append')\n",
    ";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "a8ea048c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "''"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating Show_Country table\n",
    "\n",
    "Show_Country_Table_Query = '''CREATE TABLE IF NOT EXISTS Show_Country (\n",
    "                              show_id INT,\n",
    "                              country_id INT,\n",
    "                              PRIMARY KEY (show_id, country_id),\n",
    "                              FOREIGN KEY (show_id) REFERENCES Show(show_id),\n",
    "                              FOREIGN KEY (country_id) REFERENCES Country(country_id)\n",
    "                              ); '''\n",
    "\n",
    "cur.execute(Show_Country_Table_Query)\n",
    "\n",
    "Show_Country_Table.to_sql(name='Show_Country', con=conn, if_exists='append')\n",
    ";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "c10ad914",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "''"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Creating Show_Person_Job table\n",
    "\n",
    "Show_Person_Job_Table_Query = '''CREATE TABLE IF NOT EXISTS Show_Person_Job (\n",
    "                              show_id INT,\n",
    "                              person_id INT,\n",
    "                              job_id INT,\n",
    "                              PRIMARY KEY (show_id, person_id, job_id),\n",
    "                              FOREIGN KEY (show_id) REFERENCES Show(show_id),\n",
    "                              FOREIGN KEY (person_id) REFERENCES Person(person_id),\n",
    "                              FOREIGN KEY (job_id) REFERENCES Job(job_id)\n",
    "                              ); '''\n",
    "\n",
    "cur.execute(Show_Person_Job_Table_Query)\n",
    "\n",
    "Show_Person_Job_Table.to_sql(name='Show_Person_Job', con=conn, if_exists='append')\n",
    ";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "b2af0976",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fechar a Conexão à Base de Dados\n",
    "\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8b619c2",
   "metadata": {},
   "source": [
    "---"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}