05-set-operations-with-sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data Visualization with Modern Data Science\n",
    "\n",
    "> Set operations with SQL\n",
    "\n",
    "Yao-Jen Kuo <yaojenkuo@ntu.edu.tw> from [DATAINPOINT](https://www.datainpoint.com)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "%LOAD sqlite3 db=data/taiwan_election_2024.db timeout=2 shared_cache=true"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## View"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What are views?\n",
    "\n",
    "> In a database, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary.\n",
    "\n",
    "Source: <https://en.wikipedia.org/wiki/View_(SQL)>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Views are like\n",
    "\n",
    "- A persistent sub-query.\n",
    "- A stored `SELECT` statement that feels like a table for users.\n",
    "- A feature lies between sub-queries and creating a table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `CREATE VIEW` statement\n",
    "\n",
    "```sql\n",
    "CREATE VIEW view_name\n",
    "    AS\n",
    "SELECT ...;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "DROP VIEW IF EXISTS presidents_summary;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [],
   "source": [
    "CREATE VIEW presidents_summary\n",
    "    AS\n",
    "SELECT number,\n",
    "       candidate_id,\n",
    "       SUM(votes) AS sum_votes\n",
    "  FROM presidents\n",
    " GROUP BY number;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Once a view is created, use it like a table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number</th>\n",
       "<th>candidate_id</th>\n",
       "<th>sum_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>330</td>\n",
       "<td>3690466</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>331</td>\n",
       "<td>5586019</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>329</td>\n",
       "<td>4671021</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.07 sec)"
      ],
      "text/plain": [
       "+--------+--------------+-----------+\n",
       "| number | candidate_id | sum_votes |\n",
       "+--------+--------------+-----------+\n",
       "| 1      | 330          | 3690466   |\n",
       "+--------+--------------+-----------+\n",
       "| 2      | 331          | 5586019   |\n",
       "+--------+--------------+-----------+\n",
       "| 3      | 329          | 4671021   |\n",
       "+--------+--------------+-----------+\n",
       "3 rows in set (0.07 sec)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM presidents_summary;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## View is stored in a persistent format\n",
    "\n",
    "- View is stored persistently in the connected database.\n",
    "- We have to explicitly `DROP` view after `CREATE`.\n",
    "\n",
    "```sql\n",
    "DROP VIEW IF EXISTS presidents_summary;\n",
    "\n",
    "SELECT *\n",
    "  FROM presidents_summary;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Common Table Expression, CTE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is a common table expression\n",
    "\n",
    "> Common Table Expressions or CTEs act like temporary views that exist only for the duration of a single SQL statement.\n",
    "\n",
    "Source: <https://www.sqlite.org/lang_with.html>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## CTEs are like\n",
    "\n",
    "- A non-persistent sub-query.\n",
    "- A temporary `SELECT` statement that feels like a table/view for users.\n",
    "- A feature lies between sub-queries and creating a view."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `WITH` statement\n",
    "\n",
    "```sql\n",
    "WITH cte_name AS (\n",
    "    SELECT ...\n",
    ")\n",
    "SELECT ...\n",
    "  FROM cte_name;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "```sql\n",
    "WITH presidents_summary_cte AS (\n",
    "    SELECT number,\n",
    "           candidate_id,\n",
    "           SUM(votes) AS sum_votes\n",
    "      FROM presidents\n",
    "     GROUP BY number\n",
    ")\n",
    "SELECT *\n",
    "  FROM presidents_summary_cte;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Multiple CTEs\n",
    "\n",
    "```sql\n",
    "WITH cte_name_1 AS (\n",
    "    SELECT ...\n",
    "), cte_name_2 AS (\n",
    "    SELECT ...\n",
    ")\n",
    "SELECT ...\n",
    "  FROM cte_name_1\n",
    "SELECT ...\n",
    "  FROM cte_name_2;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "```sql\n",
    "WITH presidents_summary_cte AS (\n",
    "    SELECT number,\n",
    "           candidate_id,\n",
    "           SUM(votes) AS sum_votes\n",
    "      FROM presidents\n",
    "     GROUP BY number\n",
    "), party_legislator_summary_cte AS (\n",
    "    SELECT number,\n",
    "           party_id,\n",
    "           SUM(votes) AS sum_votes\n",
    "      FROM party_legislators\n",
    "     GROUP BY number\n",
    ")\n",
    "SELECT *\n",
    "  FROM presidents_summary_cte;\n",
    "/*\n",
    "SELECT *\n",
    "  FROM party_legislator_summary_cte;\n",
    "*/\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Joining Tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## (Recap) What is a relational database\n",
    "\n",
    "> A relational database is a digital database based on the relational model of data.\n",
    "\n",
    "Source: <https://en.wikipedia.org/wiki/Relational_database>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Why relational model\n",
    "\n",
    "Using the relational model, we can build tables that eliminate duplicate data, are easier to maintain, and provide for increased flexibility in writing queries to get just the data we want."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Joining tables using `JOIN...ON...` statement\n",
    "\n",
    "The query examines both tables and then returns columns from both tables where the values match in the columns specified in the `ON` statement.\n",
    "\n",
    "```sql\n",
    "SELECT left_table.columns,\n",
    "       right_table.columns\n",
    "  FROM left_table \n",
    "  JOIN right_table\n",
    "    ON table_name.join_key = table_name.primary_key\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Joining `presidents` with `candidates`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number</th>\n",
       "<th>candidates_name</th>\n",
       "<th>sum_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>柯文哲/吳欣盈</td>\n",
       "<td>3690466</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>賴清德/蕭美琴</td>\n",
       "<td>5586019</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>侯友宜/趙少康</td>\n",
       "<td>4671021</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.03 sec)"
      ],
      "text/plain": [
       "+--------+---------------------+-----------+\n",
       "| number | candidates_name     | sum_votes |\n",
       "+--------+---------------------+-----------+\n",
       "| 1      | 柯文哲/吳欣盈 | 3690466   |\n",
       "+--------+---------------------+-----------+\n",
       "| 2      | 賴清德/蕭美琴 | 5586019   |\n",
       "+--------+---------------------+-----------+\n",
       "| 3      | 侯友宜/趙少康 | 4671021   |\n",
       "+--------+---------------------+-----------+\n",
       "3 rows in set (0.03 sec)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT presidents.number,\n",
    "       candidates.name AS candidates_name,\n",
    "       SUM(presidents.votes) AS sum_votes\n",
    "  FROM presidents\n",
    "  JOIN candidates\n",
    "    ON presidents.candidate_id = candidates.id\n",
    " GROUP BY presidents.number;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Joining `presidents` with `candidates` and `parties`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number</th>\n",
       "<th>candidates_name</th>\n",
       "<th>party_name</th>\n",
       "<th>sum_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>柯文哲/吳欣盈</td>\n",
       "<td>台灣民眾黨</td>\n",
       "<td>3690466</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>賴清德/蕭美琴</td>\n",
       "<td>民主進步黨</td>\n",
       "<td>5586019</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>侯友宜/趙少康</td>\n",
       "<td>中國國民黨</td>\n",
       "<td>4671021</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.04 sec)"
      ],
      "text/plain": [
       "+--------+---------------------+-----------------+-----------+\n",
       "| number | candidates_name     | party_name      | sum_votes |\n",
       "+--------+---------------------+-----------------+-----------+\n",
       "| 1      | 柯文哲/吳欣盈 | 台灣民眾黨 | 3690466   |\n",
       "+--------+---------------------+-----------------+-----------+\n",
       "| 2      | 賴清德/蕭美琴 | 民主進步黨 | 5586019   |\n",
       "+--------+---------------------+-----------------+-----------+\n",
       "| 3      | 侯友宜/趙少康 | 中國國民黨 | 4671021   |\n",
       "+--------+---------------------+-----------------+-----------+\n",
       "3 rows in set (0.04 sec)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT presidents.number,\n",
    "       candidates.name AS candidates_name,\n",
    "       parties.name AS party_name,\n",
    "       SUM(presidents.votes) AS sum_votes\n",
    "  FROM presidents\n",
    "  JOIN candidates\n",
    "    ON presidents.candidate_id = candidates.id\n",
    "  JOIN parties\n",
    "    ON candidates.party_id = parties.id\n",
    " GROUP BY presidents.number;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Joining tables with key columns: Primary key\n",
    "\n",
    "One or more columns whose values uniquely identify each row in a table.\n",
    "\n",
    "- Each column in the key must have a unique value for each row.\n",
    "- No column in the key can have missing values.\n",
    "- A primary key constraint is often set on `id`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## We can examine key columns via metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>cid</th>\n",
       "<th>name</th>\n",
       "<th>type</th>\n",
       "<th>notnull</th>\n",
       "<th>dflt_value</th>\n",
       "<th>pk</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>0</td>\n",
       "<td>id</td>\n",
       "<td>INTEGER</td>\n",
       "<td>0</td>\n",
       "<td>NULL</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----+------+---------+---------+------------+----+\n",
       "| cid | name | type    | notnull | dflt_value | pk |\n",
       "+-----+------+---------+---------+------------+----+\n",
       "| 0   | id   | INTEGER | 0       | NULL       | 1  |\n",
       "+-----+------+---------+---------+------------+----+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM PRAGMA_TABLE_INFO('presidents')\n",
    " WHERE pk >= 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>cid</th>\n",
       "<th>name</th>\n",
       "<th>type</th>\n",
       "<th>notnull</th>\n",
       "<th>dflt_value</th>\n",
       "<th>pk</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>0</td>\n",
       "<td>id</td>\n",
       "<td>INTEGER</td>\n",
       "<td>0</td>\n",
       "<td>NULL</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----+------+---------+---------+------------+----+\n",
       "| cid | name | type    | notnull | dflt_value | pk |\n",
       "+-----+------+---------+---------+------------+----+\n",
       "| 0   | id   | INTEGER | 0       | NULL       | 1  |\n",
       "+-----+------+---------+---------+------------+----+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM PRAGMA_TABLE_INFO('candidates')\n",
    " WHERE pk >= 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>cid</th>\n",
       "<th>name</th>\n",
       "<th>type</th>\n",
       "<th>notnull</th>\n",
       "<th>dflt_value</th>\n",
       "<th>pk</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>0</td>\n",
       "<td>id</td>\n",
       "<td>INTEGER</td>\n",
       "<td>0</td>\n",
       "<td>NULL</td>\n",
       "<td>1</td>\n",
       "</tr>\n",
       "</table>\n",
       "1 row in set (0.00 sec)"
      ],
      "text/plain": [
       "+-----+------+---------+---------+------------+----+\n",
       "| cid | name | type    | notnull | dflt_value | pk |\n",
       "+-----+------+---------+---------+------------+----+\n",
       "| 0   | id   | INTEGER | 0       | NULL       | 1  |\n",
       "+-----+------+---------+---------+------------+----+\n",
       "1 row in set (0.00 sec)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT *\n",
    "  FROM PRAGMA_TABLE_INFO('parties')\n",
    " WHERE pk >= 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Joining tables with join key\n",
    "\n",
    "- One or more columns in a table that match the primary key of another table.\n",
    "- A join key is not a constraint.\n",
    "- Two common naming styles for a join key:\n",
    "    -  A join key has the same name as the primary key of another table.\n",
    "    -  A join key is named as the singular form of another table followed by `_id`. e.g. `candidate_id` -> `candidates`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## `JOIN` returns rows from the left and the right table where matching values are found"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>number</th>\n",
       "<th>candidates_name</th>\n",
       "<th>sum_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>1</td>\n",
       "<td>柯文哲/吳欣盈</td>\n",
       "<td>3690466</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>2</td>\n",
       "<td>賴清德/蕭美琴</td>\n",
       "<td>5586019</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>3</td>\n",
       "<td>侯友宜/趙少康</td>\n",
       "<td>4671021</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.04 sec)"
      ],
      "text/plain": [
       "+--------+---------------------+-----------+\n",
       "| number | candidates_name     | sum_votes |\n",
       "+--------+---------------------+-----------+\n",
       "| 1      | 柯文哲/吳欣盈 | 3690466   |\n",
       "+--------+---------------------+-----------+\n",
       "| 2      | 賴清德/蕭美琴 | 5586019   |\n",
       "+--------+---------------------+-----------+\n",
       "| 3      | 侯友宜/趙少康 | 4671021   |\n",
       "+--------+---------------------+-----------+\n",
       "3 rows in set (0.04 sec)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT presidents.number,\n",
    "       candidates.name AS candidates_name,\n",
    "       SUM(presidents.votes) AS sum_votes\n",
    "  FROM presidents\n",
    "  JOIN candidates\n",
    "    ON presidents.candidate_id = candidates.id\n",
    " GROUP BY presidents.number;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## In contrast to `JOIN`, the `LEFT JOIN` keyword returns all rows from the left table and display blank rows from the other table if no matching values are found\n",
    "\n",
    "```sql\n",
    "SELECT left_table.columns,\n",
    "       right_table.columns\n",
    "  FROM left_table \n",
    "  LEFT JOIN right_table\n",
    "    ON table_name.join_key = table_name.primary_key\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>candidates_name</th>\n",
       "<th>sum_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>賴清德/蕭美琴</td>\n",
       "<td>5586019</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>侯友宜/趙少康</td>\n",
       "<td>4671021</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>柯文哲/吳欣盈</td>\n",
       "<td>3690466</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>黃秀芳</td>\n",
       "<td>NULL</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>黃瑞傳</td>\n",
       "<td>NULL</td>\n",
       "</tr>\n",
       "</table>\n",
       "5 rows in set (0.11 sec)"
      ],
      "text/plain": [
       "+---------------------+-----------+\n",
       "| candidates_name     | sum_votes |\n",
       "+---------------------+-----------+\n",
       "| 賴清德/蕭美琴 | 5586019   |\n",
       "+---------------------+-----------+\n",
       "| 侯友宜/趙少康 | 4671021   |\n",
       "+---------------------+-----------+\n",
       "| 柯文哲/吳欣盈 | 3690466   |\n",
       "+---------------------+-----------+\n",
       "| 黃秀芳           | NULL      |\n",
       "+---------------------+-----------+\n",
       "| 黃瑞傳           | NULL      |\n",
       "+---------------------+-----------+\n",
       "5 rows in set (0.11 sec)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT candidates.name AS candidates_name,\n",
    "       SUM(presidents.votes) AS sum_votes\n",
    "  FROM candidates\n",
    "  LEFT JOIN presidents\n",
    "    ON presidents.candidate_id = candidates.id\n",
    " GROUP BY candidates.name\n",
    " ORDER BY sum_votes DESC\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Other `JOIN` types that are not directly supported by SQLite\n",
    "\n",
    "- `RIGHT JOIN`\n",
    "- `FULL JOIN`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## We can perform `RIGHT JOIN` in SQLite by switching left table to right"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>candidates_name</th>\n",
       "<th>sum_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>賴清德/蕭美琴</td>\n",
       "<td>5586019</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>侯友宜/趙少康</td>\n",
       "<td>4671021</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>柯文哲/吳欣盈</td>\n",
       "<td>3690466</td>\n",
       "</tr>\n",
       "</table>\n",
       "3 rows in set (0.05 sec)"
      ],
      "text/plain": [
       "+---------------------+-----------+\n",
       "| candidates_name     | sum_votes |\n",
       "+---------------------+-----------+\n",
       "| 賴清德/蕭美琴 | 5586019   |\n",
       "+---------------------+-----------+\n",
       "| 侯友宜/趙少康 | 4671021   |\n",
       "+---------------------+-----------+\n",
       "| 柯文哲/吳欣盈 | 3690466   |\n",
       "+---------------------+-----------+\n",
       "3 rows in set (0.05 sec)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT candidates.name AS candidates_name,\n",
    "       SUM(presidents.votes) AS sum_votes\n",
    "  FROM presidents\n",
    "  LEFT JOIN candidates\n",
    "    ON presidents.candidate_id = candidates.id\n",
    " GROUP BY candidates.name\n",
    " ORDER BY sum_votes DESC\n",
    " LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Joining tables is like concatenating tables horizontally\n",
    "\n",
    "![Imgur](https://i.imgur.com/hq7fS67.png)\n",
    "\n",
    "Source: [Pandas User Guide](https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## We can also concatenating tables vertically via `UNION` keyword\n",
    "\n",
    "![Imgur](https://i.imgur.com/B7xawvp.png)\n",
    "\n",
    "Source: [Pandas User Guide](https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr>\n",
       "<th>election_type</th>\n",
       "<th>party_name</th>\n",
       "<th>sum_votes</th>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>區域立委得票數</td>\n",
       "<td>中國國民黨</td>\n",
       "<td>4764576</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>區域立委得票數</td>\n",
       "<td>台灣民眾黨</td>\n",
       "<td>3040615</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>區域立委得票數</td>\n",
       "<td>民主進步黨</td>\n",
       "<td>4982062</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>總統副總統得票數</td>\n",
       "<td>中國國民黨</td>\n",
       "<td>4671021</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>總統副總統得票數</td>\n",
       "<td>台灣民眾黨</td>\n",
       "<td>3690466</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>總統副總統得票數</td>\n",
       "<td>民主進步黨</td>\n",
       "<td>5586019</td>\n",
       "</tr>\n",
       "</table>\n",
       "6 rows in set (0.20 sec)"
      ],
      "text/plain": [
       "+--------------------------+-----------------+-----------+\n",
       "| election_type            | party_name      | sum_votes |\n",
       "+--------------------------+-----------------+-----------+\n",
       "| 區域立委得票數    | 中國國民黨 | 4764576   |\n",
       "+--------------------------+-----------------+-----------+\n",
       "| 區域立委得票數    | 台灣民眾黨 | 3040615   |\n",
       "+--------------------------+-----------------+-----------+\n",
       "| 區域立委得票數    | 民主進步黨 | 4982062   |\n",
       "+--------------------------+-----------------+-----------+\n",
       "| 總統副總統得票數 | 中國國民黨 | 4671021   |\n",
       "+--------------------------+-----------------+-----------+\n",
       "| 總統副總統得票數 | 台灣民眾黨 | 3690466   |\n",
       "+--------------------------+-----------------+-----------+\n",
       "| 總統副總統得票數 | 民主進步黨 | 5586019   |\n",
       "+--------------------------+-----------------+-----------+\n",
       "6 rows in set (0.20 sec)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SELECT '總統副總統得票數' AS election_type,  \n",
    "       parties.name AS party_name,\n",
    "       SUM(presidents.votes) AS sum_votes\n",
    "  FROM presidents\n",
    "  JOIN candidates\n",
    "    ON presidents.candidate_id = candidates.id\n",
    "  JOIN parties\n",
    "    ON candidates.party_id = parties.id\n",
    " GROUP BY parties.name\n",
    " UNION\n",
    "SELECT '區域立委得票數' AS election_type,\n",
    "       parties.name AS party_name,\n",
    "       SUM(party_legislators.votes) AS sum_votes\n",
    "  FROM party_legislators\n",
    "  JOIN parties\n",
    "    ON party_legislators.party_id = parties.id\n",
    " WHERE parties.name IN ('中國國民黨', '台灣民眾黨', '民主進步黨')\n",
    " GROUP BY parties.name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Putting what we have so far all together\n",
    "\n",
    "SQL is about the order of keywords, so follow this convention:\n",
    "\n",
    "```sql\n",
    "SELECT column_names,\n",
    "       CASE WHEN conditions THEN result\n",
    "            ...\n",
    "            ELSE result_else END AS alias\n",
    "  FROM left_table\n",
    "  JOIN | LEFT JOIN right_table\n",
    "    ON table_name.join_key = table_name.primary_key\n",
    " WHERE conditions\n",
    " GROUP BY column_names\n",
    "HAVING aggregated_conditions\n",
    " UNION ...\n",
    " ORDER BY column_names\n",
    " LIMIT m OFFSET m;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## The `taiwan_election_2024.db` database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What are tables?\n",
    "\n",
    "> A table is a collection of related data held in a table format within a database. It consists of columns and rows.In relational databases, and flat file databases, a table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows.\n",
    "\n",
    "Source: <https://en.wikipedia.org/wiki/Table_(database)>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Planning a database\n",
    "\n",
    "1. Design questions.\n",
    "2. Data questions.\n",
    "3. Security questions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Design questions\n",
    "\n",
    "- What are the business requirements?\n",
    "- What tables will I need to fulfill those requirements?\n",
    "- What columns will each table contain?\n",
    "- ...etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Data questions\n",
    "\n",
    "- How much data will be populated into these tables?\n",
    "- Where will the data come from?\n",
    "- Do we need processes to automatically populate the tables?\n",
    "- ...etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Security questions\n",
    "\n",
    "- Who should have access to this database?\n",
    "- Who should have access to which tables? Read-only access? Write access?\n",
    "- Is this database critical to business operations?\n",
    "- ...etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Data source\n",
    "\n",
    "https://db.cec.gov.tw/ElecTable/Election"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## The ETL steps\n",
    "\n",
    "1. Extract.\n",
    "2. Transform.\n",
    "3. Load and normalize."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Extract and Transform\n",
    "\n",
    "- Import multiple excel workbooks as data frames.\n",
    "- Tidy data frames.\n",
    "- Transform data frames from wide formats to long formats."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Classfiy tables into parents and children\n",
    "\n",
    "- Parents are tables being referenced.\n",
    "- Children are tables referencing parents.\n",
    "    - e.g. In a `presidents.candidate_id` -> `candidates.id` relationship, `presidents` is a child table while `candidates` being a parent table.\n",
    "    - e.g. In a `candidates.party_id` -> `parties.id` relationship, `candidates` is a child table while `parties` being a parent table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Load\n",
    "\n",
    "```python\n",
    "con = sqlite3.connect(\"normalized_datasets/taiwan_election_2024.db\")\n",
    "cur = con.cursor()\n",
    "folder_name = \"normalized_datasets\"\n",
    "file_names = [\"districts.csv\", \"parties.csv\", \"election_types.csv\", \"candidates.csv\", \"polling_places.csv\",\n",
    "              \"presidents.csv\", \"regional_legislators.csv\", \"aboriginal_legislators.csv\", \"party_legislators.csv\",\n",
    "              \"villages.csv\"]\n",
    "for file_name in file_names:\n",
    "    file_path = f\"{folder_name}/{file_name}\"\n",
    "    df = pd.read_csv(file_path)\n",
    "    table_name = file_name.split(\".\")[0]\n",
    "    df.to_sql(table_name, con, if_exists=\"replace\", index=False)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Load: `districts`\n",
    "\n",
    "```sql\n",
    "table_name = \"districts\"\n",
    "create_table_long_str = f\"\"\"\n",
    "PRAGMA foreign_keys=off;\n",
    "ALTER TABLE {table_name} RENAME TO {table_name}_no_keys;\n",
    "CREATE TABLE {table_name} (\n",
    "    id INTEGER,\n",
    "    county CHAR(3),\n",
    "    town VARCHAR(200),\n",
    "    polling_place INTEGER,\n",
    "    vote_tallied_at CHAR(19),\n",
    "    PRIMARY KEY (id)\n",
    ");\n",
    "INSERT INTO {table_name} SELECT * FROM {table_name}_no_keys;\n",
    "PRAGMA foreign_keys=on;\n",
    "\"\"\"\n",
    "cur.executescript(create_table_long_str)\n",
    "con.commit()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Load: `villages`\n",
    "\n",
    "```sql\n",
    "table_name = \"villages\"\n",
    "create_table_long_str = f\"\"\"\n",
    "PRAGMA foreign_keys=off;\n",
    "ALTER TABLE {table_name} RENAME TO {table_name}_no_keys;\n",
    "CREATE TABLE {table_name} (\n",
    "    id INTEGER,\n",
    "    name VARCHAR(200),\n",
    "    PRIMARY KEY (id)\n",
    ");\n",
    "INSERT INTO {table_name} SELECT * FROM {table_name}_no_keys;\n",
    "PRAGMA foreign_keys=on;\n",
    "\"\"\"\n",
    "cur.executescript(create_table_long_str)\n",
    "con.commit()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Load: `parties`\n",
    "\n",
    "```sql\n",
    "table_name = \"parties\"\n",
    "create_table_long_str = f\"\"\"\n",
    "PRAGMA foreign_keys=off;\n",
    "ALTER TABLE {table_name} RENAME TO {table_name}_no_keys;\n",
    "CREATE TABLE {table_name} (\n",
    "    id INTEGER,\n",
    "    name VARCHAR(100),\n",
    "    PRIMARY KEY (id)\n",
    ");\n",
    "INSERT INTO {table_name} SELECT * FROM {table_name}_no_keys;\n",
    "PRAGMA foreign_keys=on;\n",
    "\"\"\"\n",
    "cur.executescript(create_table_long_str)\n",
    "con.commit()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Load: `election_types`\n",
    "\n",
    "```sql\n",
    "table_name = \"election_types\"\n",
    "create_table_long_str = f\"\"\"\n",
    "PRAGMA foreign_keys=off;\n",
    "ALTER TABLE {table_name} RENAME TO {table_name}_no_keys;\n",
    "CREATE TABLE {table_name} (\n",
    "    id INTEGER,\n",
    "    election_type VARCHAR(10),\n",
    "    PRIMARY KEY (id)\n",
    ");\n",
    "INSERT INTO {table_name} SELECT * FROM {table_name}_no_keys;\n",
    "PRAGMA foreign_keys=on;\n",
    "\"\"\"\n",
    "cur.executescript(create_table_long_str)\n",
    "con.commit()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Load: `candidates`\n",
    "\n",
    "```sql\n",
    "table_name = \"candidates\"\n",
    "create_table_long_str = f\"\"\"\n",
    "PRAGMA foreign_keys=off;\n",
    "ALTER TABLE {table_name} RENAME TO {table_name}_no_keys;\n",
    "CREATE TABLE {table_name} (\n",
    "    id INTEGER,\n",
    "    name VARCHAR(100),\n",
    "    party_id INTEGER,\n",
    "    election_type_id INTEGER,\n",
    "    PRIMARY KEY (id),\n",
    "    FOREIGN KEY (party_id) REFERENCES parties (id),\n",
    "    FOREIGN KEY (election_type_id) REFERENCES election_types (id)\n",
    ");\n",
    "INSERT INTO {table_name} SELECT * FROM {table_name}_no_keys;\n",
    "PRAGMA foreign_keys=on;\n",
    "\"\"\"\n",
    "cur.executescript(create_table_long_str)\n",
    "con.commit()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Load: `polling_places`\n",
    "\n",
    "```sql\n",
    "table_name = \"polling_places\"\n",
    "create_table_long_str = f\"\"\"\n",
    "PRAGMA foreign_keys=off;\n",
    "ALTER TABLE {table_name} RENAME TO {table_name}_no_keys;\n",
    "CREATE TABLE {table_name} (\n",
    "    id INTEGER,\n",
    "    district_id INTEGER,\n",
    "    polling_place INTEGER,\n",
    "    effective_votes INTEGER,\n",
    "    wasted_votes INTEGER,\n",
    "    issued_votes INTEGER,\n",
    "    remained_votes INTEGER,\n",
    "    election_type_id INTEGER,\n",
    "    PRIMARY KEY (id),\n",
    "    FOREIGN KEY (district_id) REFERENCES districts (id),\n",
    "    FOREIGN KEY (election_type_id) REFERENCES election_types (id)\n",
    ");\n",
    "INSERT INTO {table_name} SELECT * FROM {table_name}_no_keys;\n",
    "PRAGMA foreign_keys=on;\n",
    "\"\"\"\n",
    "cur.executescript(create_table_long_str)\n",
    "con.commit()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Load: `presidents`\n",
    "\n",
    "```sql\n",
    "table_name = \"presidents\"\n",
    "create_table_long_str = f\"\"\"\n",
    "PRAGMA foreign_keys=off;\n",
    "ALTER TABLE {table_name} RENAME TO {table_name}_no_keys;\n",
    "CREATE TABLE {table_name} (\n",
    "    id INTEGER,\n",
    "    number INTEGER,\n",
    "    district_id INTEGER,\n",
    "    candidate_id INTEGER,\n",
    "    votes INTEGER,\n",
    "    election_type_id INTEGER,\n",
    "    village_id INTEGER,\n",
    "    PRIMARY KEY (id),\n",
    "    FOREIGN KEY (district_id) REFERENCES districts (id),\n",
    "    FOREIGN KEY (candidate_id) REFERENCES candidates (id),\n",
    "    FOREIGN KEY (village_id) REFERENCES villages (id),\n",
    "    FOREIGN KEY (election_type_id) REFERENCES election_types (id)\n",
    ");\n",
    "INSERT INTO {table_name} SELECT * FROM {table_name}_no_keys;\n",
    "PRAGMA foreign_keys=on;\n",
    "\"\"\"\n",
    "cur.executescript(create_table_long_str)\n",
    "con.commit()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Load: `presidents`\n",
    "\n",
    "```sql\n",
    "table_name = \"regional_legislators\"\n",
    "create_table_long_str = f\"\"\"\n",
    "PRAGMA foreign_keys=off;\n",
    "ALTER TABLE {table_name} RENAME TO {table_name}_no_keys;\n",
    "CREATE TABLE {table_name} (\n",
    "    id INTEGER,\n",
    "    district_id INTEGER,\n",
    "    candidate_id INTEGER,\n",
    "    number INTEGER,\n",
    "    votes INTEGER,\n",
    "    legislator_region VARCHAR(50),\n",
    "    election_type_id INTEGER,\n",
    "    village_id INTEGER,\n",
    "    PRIMARY KEY (id),\n",
    "    FOREIGN KEY (district_id) REFERENCES districts (id),\n",
    "    FOREIGN KEY (candidate_id) REFERENCES candidates (id),\n",
    "    FOREIGN KEY (village_id) REFERENCES villages (id),\n",
    "    FOREIGN KEY (election_type_id) REFERENCES election_types (id)\n",
    ");\n",
    "INSERT INTO {table_name} SELECT * FROM {table_name}_no_keys;\n",
    "PRAGMA foreign_keys=on;\n",
    "\"\"\"\n",
    "cur.executescript(create_table_long_str)\n",
    "con.commit()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Load: `aboriginal_legislators`\n",
    "\n",
    "```sql\n",
    "table_name = \"aboriginal_legislators\"\n",
    "create_table_long_str = f\"\"\"\n",
    "PRAGMA foreign_keys=off;\n",
    "ALTER TABLE {table_name} RENAME TO {table_name}_no_keys;\n",
    "CREATE TABLE {table_name} (\n",
    "    id INTEGER,\n",
    "    district_id INTEGER,\n",
    "    candidate_id INTEGER,\n",
    "    number INTEGER,\n",
    "    votes INTEGER,\n",
    "    election_type_id INTEGER,\n",
    "    village_id INTEGER,\n",
    "    PRIMARY KEY (id),\n",
    "    FOREIGN KEY (district_id) REFERENCES districts (id),\n",
    "    FOREIGN KEY (candidate_id) REFERENCES candidates (id),\n",
    "    FOREIGN KEY (village_id) REFERENCES villages (id),\n",
    "    FOREIGN KEY (election_type_id) REFERENCES election_types (id)\n",
    ");\n",
    "INSERT INTO {table_name} SELECT * FROM {table_name}_no_keys;\n",
    "PRAGMA foreign_keys=on;\n",
    "\"\"\"\n",
    "cur.executescript(create_table_long_str)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Load: `party_legislators`\n",
    "\n",
    "```sql\n",
    "table_name = \"party_legislators\"\n",
    "create_table_long_str = f\"\"\"\n",
    "PRAGMA foreign_keys=off;\n",
    "ALTER TABLE {table_name} RENAME TO {table_name}_no_keys;\n",
    "CREATE TABLE {table_name} (\n",
    "    id INTEGER,\n",
    "    district_id INTEGER,\n",
    "    party_id INTEGER,\n",
    "    number INTEGER,\n",
    "    votes INTEGER,\n",
    "    election_type_id INTEGER,\n",
    "    village_id INTEGER,\n",
    "    PRIMARY KEY (id),\n",
    "    FOREIGN KEY (district_id) REFERENCES districts (id),\n",
    "    FOREIGN KEY (party_id) REFERENCES parties (id),\n",
    "    FOREIGN KEY (village_id) REFERENCES villages (id),\n",
    "    FOREIGN KEY (election_type_id) REFERENCES election_types (id)\n",
    ");\n",
    "INSERT INTO {table_name} SELECT * FROM {table_name}_no_keys;\n",
    "PRAGMA foreign_keys=on;\n",
    "\"\"\"\n",
    "cur.executescript(create_table_long_str)\n",
    "con.commit()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Load: Drop duplicate tables\n",
    "\n",
    "```python\n",
    "table_names = [fn.split(\".\")[0] for fn in file_names]\n",
    "for table_name in table_names:\n",
    "    drop_table_long_str = f\"\"\"\n",
    "    PRAGMA foreign_keys=off;\n",
    "    DROP TABLE IF EXISTS {table_name}_no_keys;\n",
    "    PRAGMA foreign_keys=on;\n",
    "    \"\"\"\n",
    "    cur.executescript(drop_table_long_str)\n",
    "    con.commit()\n",
    "con.close()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## ER diagram\n",
    "\n",
    "> An entity–relationship model (or ER model) describes interrelated things of interest in a specific domain of knowledge. A basic ER model is composed of entity types (which classify the things of interest) and specifies relationships that can exist between entities (instances of those entity types).\n",
    "\n",
    "Source: <https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## ER diagram of `taiwan_election_2024.db`\n",
    "\n",
    "![](images/taiwan_election_2024_erd.png)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "xsql",
   "language": "sqlite",
   "name": "xsql"
  },
  "language_info": {
   "codemirror_mode": "sql",
   "file_extension": "",
   "mimetype": "",
   "name": "mysql",
   "version": "0.2.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}