PostgreSQL & PgAdmin using docker-compose in Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# PostgreSQL & PgAdmin using docker-compose in Python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Installations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "cell_style": "center"
   },
   "outputs": [],
   "source": [
    "# !pip install psycopg2\n",
    "# !pip install sqlalchemy \n",
    "# !pip install ipython-sql\n",
    "# !pip install pgspecial"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The link between **ipython-sql** and the **postgresql API** is done by **psycopg2**. It is the most popular PostgreSQL adapter for the Python programming language. Its main features are the complete implementation of the Python DB API 2.0 specification and the thread safety."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "## import postgreSQL adapter for the Python\n",
    "import psycopg2\n",
    "\n",
    "# ORM for databases\n",
    "import sqlalchemy\n",
    "\n",
    "# SQL magic function\n",
    "%load_ext sql\n",
    "\n",
    "# to run postgreSQL special commands\n",
    "# import pgspecial"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Docker Environment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Docker Setup\n",
    "* Ensure **docker** and **docker-compose** is installed in your system (**Docker Desktop** is easy to use)\n",
    "* Create a folder **Postgres** and go inside this directory,  `cd Postgres`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Creating a docker-compose.yaml file\n",
    "```shell\n",
    "version: '3'\n",
    "services:\n",
    "  postgres:\n",
    "    container_name: postgres\n",
    "    image: postgres:latest\n",
    "    restart: unless-stopped\n",
    "    ports:\n",
    "      - \"5433:5432\"\n",
    "    command: postgres -c logging_collector=on -c log_destination=stderr -c log_directory=/logs\n",
    "    volumes:\n",
    "      - ./postgres:/var/lib/postgresql/data\n",
    "      - ./logs:/logs\n",
    "    environment:\n",
    "      - POSTGRES_USER=jamwine\n",
    "      - POSTGRES_PASSWORD=password\n",
    "      - POSTGRES_DB=postgresdb\n",
    "      - POSTGRES_HOST_AUTH_METHOD=trust\n",
    "      - PGDATA=/var/lib/postgresql/data/\n",
    "    networks:\n",
    "      - postgres\n",
    "\n",
    "  pgadmin:\n",
    "    container_name: pgadmin\n",
    "    image: dpage/pgadmin4\n",
    "    environment:\n",
    "      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL:-admin@admin.com}\n",
    "      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD:-admin}\n",
    "      PGADMIN_CONFIG_SERVER_MODE: 'False'\n",
    "    volumes:\n",
    "       - pgadmin:/root/.pgadmin\n",
    "    ports:\n",
    "      - \"${PGADMIN_PORT:-5050}:80\"\n",
    "    networks:\n",
    "      - postgres\n",
    "    restart: unless-stopped\n",
    "    \n",
    "networks:\n",
    "  postgres:\n",
    "    driver: bridge\n",
    "\n",
    "volumes:\n",
    "    postgres:\n",
    "    pgadmin:\n",
    "```\n",
    "---\n",
    "\n",
    "This Compose file contains the following environment variables:\n",
    "\n",
    "* `POSTGRES_USER` the default value is **jamwine**\n",
    "* `POSTGRES_PASSWORD` the default value is **password**\n",
    "* `POSTGRES_DB` the default value is **postgresdb**\n",
    "* `POSTGRES_HOST_AUTH_METHOD` the default value is **trust**\n",
    "* `PGDATA` the default value is **/var/lib/postgresql/data/**\n",
    "\n",
    "\n",
    "* `PGADMIN_PORT` the default value is **5050**\n",
    "* `PGADMIN_DEFAULT_EMAIL` the default value is **admin@admin.com**\n",
    "* `PGADMIN_DEFAULT_PASSWORD` the default value is **admin**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Start Services in Docker\n",
    "Run this command in the terminal `docker-compose up -d`. The containers will be created in Docker.\n",
    "\n",
    "### Access to postgres: \n",
    "* `localhost:5432`\n",
    "\n",
    "### Access to PgAdmin: \n",
    "* **URL:** `http://localhost:5050`\n",
    "\n",
    "### Add a new server in PgAdmin:\n",
    "*In the **General** tab:*\n",
    "* **Name** `postgresdb`\n",
    "\n",
    "\n",
    "*In the **Connection** tab:*\n",
    "* **Host name/address** `postgres` (same name as the Docker container name of PostgreSQL.)\n",
    "* **Port** `5432`\n",
    "* **Username** as `POSTGRES_USER`, by default: `jamwine`\n",
    "* **Password** as `POSTGRES_PASSWORD`, by default `password`\n",
    "\n",
    "\n",
    "### Run the interactive shell\n",
    "`docker exec -it postgres bash`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## PostgreSQL Environment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "* `psql` is command line utility to connect to the Postgres database server. It is typically used for the following by advanced Database users:\n",
    "  * Manage Databases\n",
    "  * Manage Tables\n",
    "  * Load data into tables for testing purposes\n",
    "* Postgres Client must be installed on the server from which psql is to be connected to Postgres Server.\n",
    "* On the server where **Postgres Database Server** (docker image) is installed, `psql` will be automatically available.\n",
    "* **postgres** is the super user for the postgres server and hence typically developers will not have access to it in non development environments.\n",
    "* IDEs such as **SQL Alchemy** might be better for regular usage as part of development and unit testing process.\n",
    "\n",
    "### Start using SQL inside Docker shell\n",
    "`psql -U jamwine -d postgresdb`\n",
    "\n",
    "* To connect to a database setup on postgres server using user credentials.\n",
    "```shell\n",
    "psql  -h <host_ip_or_dns_alias> -d <db_name> -U <user_name> -W\n",
    "```\n",
    "\n",
    "### Grant Permissions to the user *jamwine*\n",
    "`GRANT ALL ON DATABASE postgresdb TO jamwine;`\n",
    "\n",
    "\n",
    "### Troubleshooting Common Errors:\n",
    "* https://www.codegrepper.com/code-examples/sql/psql+FATAL%3A+password+authentication+failed+for+user\n",
    "\n",
    "### Special commands\n",
    "* Connection Information - `\\conninfo`\n",
    "* Listing Databases - `\\l`\n",
    "* Switching to a Database - `\\c <DATABASE_NAME>`\n",
    "* Get help for **psql** - `\\?`\n",
    "* Listing tables - `\\d`\n",
    "* Listing users - `\\du`\n",
    "* Listing schemas - `\\dn`\n",
    "* Create table - `CREATE TABLE t (i SERIAL PRIMARY KEY)`\n",
    "* Get details related to a table - `\\d <table_name>`\n",
    "* Running Scripts - `\\i <SCRIPT_PATH>`\n",
    "\n",
    "### Using PgAdmin to run queries\n",
    "#### Populate the database with tables/queries. For demo purpose, use this link:\n",
    "> https://raw.githubusercontent.com/lifeparticle/PostgreSql-Snippets/main/infile\n",
    "\n",
    "* In the left section *Browser*, goto **postgresdb->Databases->postgresdb->Schemas->public->Tables**\n",
    "* Right click and select **Query Tool**.\n",
    "* Paste the queries from the sample link above, and run.\n",
    "* To check the created tables, run this query to display information:\n",
    "```sql\n",
    "SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## References"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "* https://www.postgresqltutorial.com/\n",
    "* https://www.psycopg.org/\n",
    "* https://www.pgadmin.org/\n",
    "* https://linuxhint.com/postgresql_docker/\n",
    "* https://towardsdatascience.com/how-to-run-postgresql-and-pgadmin-using-docker-3a6a8ae918b5\n",
    "* https://towardsdatascience.com/jupyter-magics-with-sql-921370099589\n",
    "* https://towardsdatascience.com/local-development-set-up-of-postgresql-with-docker-c022632f13ea\n",
    "* https://phoenixnap.com/kb/deploy-postgresql-on-docker\n",
    "* https://stackoverflow.com/questions/769683/postgresql-show-tables-in-postgresql\n",
    "* https://stackoverflow.com/questions/19674456/run-postgresql-queries-from-the-command-line\n",
    "* https://github.com/itversity/mastering-postgresql\n",
    "* https://github.com/khezen/compose-postgres\n",
    "* https://franckpachot.medium.com/postgresql-and-jupyter-notebook-e7b68cb6427d\n",
    "* https://shravan-kuchkula.github.io/sql/postgres-jupyter\n",
    "* https://towardsdatascience.com/a-data-scientist-approach-running-postgres-sql-using-docker-1b978122e5e6\n",
    "* https://auth0.com/blog/sqlalchemy-orm-tutorial-for-python-developers/\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Python Connection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### Connection to the database using `psycopg2`\n",
    "1. Connect to the local instance of PostgreSQL (*127.0.0.1*)\n",
    "2. Use the database/schema from the instance. \n",
    "3. The connection reaches out to the *database* (**postgresdb**) and use the correct privilages to connect to the database (*user* = **jamwine** and *password* = **password**)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def connect_database(dbName):\n",
    "    \"\"\"\n",
    "    The function to create database\n",
    "\n",
    "    Returns:\n",
    "        cur  : Use the connection to get a cursor that will be used to execute queries.\n",
    "        conn : The connection towards current connecting database.\n",
    "    \"\"\"\n",
    "\n",
    "    #Define our connection string\n",
    "    conn_string = \"host='localhost' dbname={} port='5433' user='jamwine' password='password'\".format(dbName)\n",
    "\n",
    "    # print the connection string we will use to connect\n",
    "    print (\"Connecting to database\\n    -> {}\".format(conn_string))\n",
    "    \n",
    "    try:\n",
    "        # get a connection, if a connect cannot be made an exception will be raised here\n",
    "        conn = psycopg2.connect(conn_string)\n",
    "    \n",
    "        # after each call during the session, commit one action and do not hold open the transaction for any other actions.\n",
    "        # i.e. One action = one transaction.\n",
    "        conn.set_session(autocommit=True)\n",
    "    \n",
    "    except psycopg2.Error as e: \n",
    "        print(\"Error: Could not make connection to the Postgres database\")\n",
    "        print(e)\n",
    "    \n",
    "    try:\n",
    "        # conn.cursor() will return a cursor object, use this cursor to perform queries\n",
    "        cur = conn.cursor()\n",
    "        print (\"\\nConnected!\")\n",
    "\n",
    "    except psycopg2.Error as e: \n",
    "        print(\"Error: Could not get cursor to the Database\")\n",
    "        print(e)\n",
    "    \n",
    "        \n",
    "    return cur, conn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "By using automatic commit, each action is commited without having to call `conn.commit()` after each command. **The ability to rollback and commit transactions are a feature of Relational Databases.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connecting to database\n",
      "    -> host='localhost' dbname=postgresdb port='5433' user='jamwine' password='password'\n",
      "\n",
      "Connected!\n"
     ]
    }
   ],
   "source": [
    "cur, conn = connect_database('postgresdb')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# Do stuff here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection Closed\n"
     ]
    }
   ],
   "source": [
    "# Close the cursor and connection\n",
    "\n",
    "cur.close()\n",
    "conn.close()\n",
    "print('Connection Closed')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Create environment variable `DATABASE_URL` using SQL Alchemy format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "env: DATABASE_URL=postgresql://jamwine:password@localhost:5433/postgresdb\n"
     ]
    }
   ],
   "source": [
    "# %env is another magic function that sets environment variables.\n",
    "\n",
    "%env DATABASE_URL=postgresql://jamwine:password@localhost:5433/postgresdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n",
      "+------------------------------------------------------------------------------------------------------------------+\n",
      "|                                                     version                                                      |\n",
      "+------------------------------------------------------------------------------------------------------------------+\n",
      "| PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit |\n",
      "+------------------------------------------------------------------------------------------------------------------+\n"
     ]
    }
   ],
   "source": [
    "pg_version=%sql select version()\n",
    "print(pg_version)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Displaying tables in Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>schemaname</th>\n",
       "        <th>tablename</th>\n",
       "        <th>tableowner</th>\n",
       "        <th>tablespace</th>\n",
       "        <th>hasindexes</th>\n",
       "        <th>hasrules</th>\n",
       "        <th>hastriggers</th>\n",
       "        <th>rowsecurity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>students</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>True</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>marks</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>True</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('public', 'students', 'jamwine', None, True, False, False, False),\n",
       " ('public', 'marks', 'jamwine', None, True, False, False, False)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_all_tables = \"SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';\"\n",
    "tables = %sql $get_all_tables\n",
    "tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Total Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(30,)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select count(*) from students;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "### Get minimum and maximum marks in subjects beginning with 'Foreign'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>min</th>\n",
       "        <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>54</td>\n",
       "        <td>98</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(54, 98)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select min(mark),max(mark) from marks where subject like 'Foreign%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Normalization and Denormalization"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating a database 'music_db'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "(psycopg2.errors.DuplicateDatabase) database \"music_db\" already exists\n",
      "\n",
      "[SQL: create database music_db;]\n",
      "(Background on this error at: http://sqlalche.me/e/14/f405)\n"
     ]
    }
   ],
   "source": [
    "%sql create database music_db;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Show all Databases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>datname</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>postgres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>template1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>template0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>postgresdb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>music_db</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('postgres',), ('template1',), ('template0',), ('postgresdb',), ('music_db',)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT datname FROM pg_database;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Display current database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_database</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>postgresdb</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('postgresdb',)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT current_database();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Switching from current database 'postgresdb' to new database 'music_db'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* In PostgreSQL, any connection can work only with one database at any given time. \n",
    "* To change the database, reconnect by pecifying another database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>current_database</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>music_db</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('music_db',)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql postgresql://jamwine:password@localhost:5433/music_db\n",
    "SELECT current_database();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>schemaname</th>\n",
       "        <th>tablename</th>\n",
       "        <th>tableowner</th>\n",
       "        <th>tablespace</th>\n",
       "        <th>hasindexes</th>\n",
       "        <th>hasrules</th>\n",
       "        <th>hastriggers</th>\n",
       "        <th>rowsecurity</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql $get_all_tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are no tables in music_db currently."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "### Creating a table 'music_library' \n",
    "\n",
    "`Table Name: music_library\n",
    "column 0: Album Id\n",
    "column 1: Album Name\n",
    "column 2: Artist Name\n",
    "column 3: Year \n",
    "column 4: List of songs`\n",
    "\n",
    "<img src=\"images/table1.png\" width=\"650\" height=\"650\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "Done.\n",
      "2 rows affected.\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>album_id</th>\n",
       "        <th>album_name</th>\n",
       "        <th>artist_name</th>\n",
       "        <th>year</th>\n",
       "        <th>songs</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1965</td>\n",
       "        <td>[&#x27;Michelle&#x27;, &#x27;Think For Yourself&#x27;, &#x27;In My Life&#x27;]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1970</td>\n",
       "        <td>[&#x27;Let It Be&#x27;, &#x27;Across The Universe&#x27;]</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Rubber Soul', 'The Beatles', 1965, ['Michelle', 'Think For Yourself', 'In My Life']),\n",
       " (2, 'Let It Be', 'The Beatles', 1970, ['Let It Be', 'Across The Universe'])]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE IF NOT EXISTS music_library (album_id int, album_name varchar, artist_name varchar, year int, songs text[]);\n",
    "\n",
    "INSERT INTO music_library (album_id, album_name, artist_name, year, songs)\n",
    "VALUES (1, 'Rubber Soul', 'The Beatles', 1965, ARRAY['Michelle', 'Think For Yourself', 'In My Life']),\n",
    "(2, 'Let It Be', 'The Beatles', 1970, ARRAY['Let It Be', 'Across The Universe']);\n",
    "\n",
    "SELECT * FROM music_library;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "### Moving to 1st Normal Form (1NF)\n",
    "This data has not been normalized. To get this data into 1st normal form, remove any collections or list of data by breaking up the list of songs into individuals rows. \n",
    "\n",
    "`Table Name: music_library2\n",
    "column 0: Album Id\n",
    "column 1: Album Name\n",
    "column 2: Artist Name\n",
    "column 3: Year \n",
    "column 4: Song Name`\n",
    "\n",
    "<img src=\"images/table2.png\" width=\"650\" height=\"650\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "Done.\n",
      "5 rows affected.\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>album_id</th>\n",
       "        <th>album_name</th>\n",
       "        <th>artist_name</th>\n",
       "        <th>year</th>\n",
       "        <th>song_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1965</td>\n",
       "        <td>Michelle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1965</td>\n",
       "        <td>Think For Yourself</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1965</td>\n",
       "        <td>In My Life</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1970</td>\n",
       "        <td>Let It Be</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1970</td>\n",
       "        <td>Across The Universe</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Rubber Soul', 'The Beatles', 1965, 'Michelle'),\n",
       " (1, 'Rubber Soul', 'The Beatles', 1965, 'Think For Yourself'),\n",
       " (1, 'Rubber Soul', 'The Beatles', 1965, 'In My Life'),\n",
       " (2, 'Let It Be', 'The Beatles', 1970, 'Let It Be'),\n",
       " (2, 'Let It Be', 'The Beatles', 1970, 'Across The Universe')]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE IF NOT EXISTS music_library_1nf (album_id int, album_name varchar, artist_name varchar, year int, song_name varchar);\n",
    "\n",
    "INSERT INTO music_library_1nf (album_id, album_name, artist_name, year, song_name)\n",
    "VALUES (1, 'Rubber Soul', 'The Beatles', 1965, 'Michelle'),\n",
    "(1, 'Rubber Soul', 'The Beatles', 1965, 'Think For Yourself'),\n",
    "(1, 'Rubber Soul', 'The Beatles', 1965, 'In My Life'),\n",
    "(2, 'Let It Be', 'The Beatles', 1970, 'Let It Be'),\n",
    "(2, 'Let It Be', 'The Beatles', 1970, 'Across The Universe');\n",
    "\n",
    "SELECT * FROM music_library_1nf;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "### Moving to 2nd Normal Form (2NF)\n",
    "Moving data to 1NF is the first step, next move it to 2nd Normal Form. While each of the records in table is unique, Primary key (*album id*) is not unique. Thus, two tables will be created, *album library* and *song library*. \n",
    "\n",
    "`Table Name: album_library \n",
    "column 0: Album Id\n",
    "column 1: Album Name\n",
    "column 2: Artist Name\n",
    "column 3: Year `\n",
    "\n",
    "`Table Name: song_library\n",
    "column 0: Song Id\n",
    "column 1: Song Name\n",
    "column 3: Album Id` \n",
    "\n",
    "<img src=\"images/table3.png\" width=\"450\" height=\"450\"> <img src=\"images/table4.png\" width=\"450\" height=\"450\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "Done.\n",
      "2 rows affected.\n",
      "Done.\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE IF NOT EXISTS album_library (album_id int, album_name varchar, artist_name varchar, year int);\n",
    "INSERT INTO album_library (album_id, album_name, artist_name, year)\n",
    "VALUES (1, 'Rubber Soul', 'The Beatles', 1965),\n",
    "(2, 'Let It Be', 'The Beatles', 1970);\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS song_library (song_id int, album_id int, song_name varchar);\n",
    "INSERT INTO song_library (song_id, album_id, song_name)\n",
    "VALUES (1, 1, 'Michelle'),\n",
    "(2, 1, 'Think For Yourself'),\n",
    "(3, 1, 'In My Life'),\n",
    "(4, 2, 'Let It Be'),\n",
    "(5, 2, 'Across the Universe');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "cell_style": "split",
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>album_id</th>\n",
       "        <th>album_name</th>\n",
       "        <th>artist_name</th>\n",
       "        <th>year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1970</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Rubber Soul', 'The Beatles', 1965),\n",
       " (2, 'Let It Be', 'The Beatles', 1970)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM album_library;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "cell_style": "split",
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>song_id</th>\n",
       "        <th>album_id</th>\n",
       "        <th>song_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>Michelle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "        <td>Think For Yourself</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>In My Life</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>2</td>\n",
       "        <td>Across the Universe</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1, 'Michelle'),\n",
       " (2, 1, 'Think For Yourself'),\n",
       " (3, 1, 'In My Life'),\n",
       " (4, 2, 'Let It Be'),\n",
       " (5, 2, 'Across the Universe')]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM song_library;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "#### Doing a `JOIN` on above tables to get all the information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>album_id</th>\n",
       "        <th>album_name</th>\n",
       "        <th>artist_name</th>\n",
       "        <th>year</th>\n",
       "        <th>song_id</th>\n",
       "        <th>album_id_1</th>\n",
       "        <th>song_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1965</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>Michelle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1965</td>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "        <td>Think For Yourself</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1965</td>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>In My Life</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1970</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1970</td>\n",
       "        <td>5</td>\n",
       "        <td>2</td>\n",
       "        <td>Across the Universe</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Rubber Soul', 'The Beatles', 1965, 1, 1, 'Michelle'),\n",
       " (1, 'Rubber Soul', 'The Beatles', 1965, 2, 1, 'Think For Yourself'),\n",
       " (1, 'Rubber Soul', 'The Beatles', 1965, 3, 1, 'In My Life'),\n",
       " (2, 'Let It Be', 'The Beatles', 1970, 4, 2, 'Let It Be'),\n",
       " (2, 'Let It Be', 'The Beatles', 1970, 5, 2, 'Across the Universe')]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM album_library JOIN song_library ON album_library.album_id = song_library.album_id;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "### Moving to 3rd Normal Form (3NF)\n",
    "Check our table for any transitive dependencies. *Album_library* can move *Artist_name* to its own table, called *Artists*, which will leave us with 3 tables. \n",
    "\n",
    "`Table Name: album_library2 \n",
    "column 0: Album Id\n",
    "column 1: Album Name\n",
    "column 2: Artist Id\n",
    "column 3: Year `\n",
    "\n",
    "`Table Name: song_library\n",
    "column 0: Song Id\n",
    "column 1: Song Name\n",
    "column 3: Album Id`\n",
    "\n",
    "`Table Name: artist_library\n",
    "column 0: Artist Id\n",
    "column 1: Artist Name `\n",
    "<img src=\"images/table4.png\" width=\"450\" height=\"450\"> <img src=\"images/table5.png\" width=\"450\" height=\"450\"> <img src=\"images/table6.png\" width=\"350\" height=\"350\">\n",
    "\n",
    "Table `song_library` is exactly same as previous. Hence, create the other two tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "Done.\n",
      "2 rows affected.\n",
      "Done.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE IF NOT EXISTS album_library2 (album_id int, album_name varchar, artist_id int, year int);\n",
    "INSERT INTO album_library2 (album_id, album_name, artist_id, year)\n",
    "VALUES (1, 'Rubber Soul', 1, 1965),\n",
    "(2, 'Let It Be', 1, 1970);\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS artist_library (artist_id int, artist_name varchar);\n",
    "INSERT INTO artist_library (artist_id, artist_name)\n",
    "VALUES (1, 'The Beatles');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "cell_style": "split",
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>album_id</th>\n",
       "        <th>album_name</th>\n",
       "        <th>artist_id</th>\n",
       "        <th>year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>1</td>\n",
       "        <td>1965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>1</td>\n",
       "        <td>1970</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Rubber Soul', 1, 1965), (2, 'Let It Be', 1, 1970)]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM album_library2;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "cell_style": "split",
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>artist_id</th>\n",
       "        <th>artist_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>The Beatles</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'The Beatles')]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM artist_library;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "#### Doing two `JOIN` on these 3 tables to get all the information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>artist_id</th>\n",
       "        <th>artist_name</th>\n",
       "        <th>album_id</th>\n",
       "        <th>album_name</th>\n",
       "        <th>artist_id_1</th>\n",
       "        <th>year</th>\n",
       "        <th>song_id</th>\n",
       "        <th>album_id_1</th>\n",
       "        <th>song_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>1</td>\n",
       "        <td>1965</td>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>Michelle</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>1</td>\n",
       "        <td>1965</td>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "        <td>Think For Yourself</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>1</td>\n",
       "        <td>1965</td>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>In My Life</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>1</td>\n",
       "        <td>1970</td>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>1</td>\n",
       "        <td>1970</td>\n",
       "        <td>5</td>\n",
       "        <td>2</td>\n",
       "        <td>Across the Universe</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'The Beatles', 1, 'Rubber Soul', 1, 1965, 1, 1, 'Michelle'),\n",
       " (1, 'The Beatles', 1, 'Rubber Soul', 1, 1965, 2, 1, 'Think For Yourself'),\n",
       " (1, 'The Beatles', 1, 'Rubber Soul', 1, 1965, 3, 1, 'In My Life'),\n",
       " (1, 'The Beatles', 2, 'Let It Be', 1, 1970, 4, 2, 'Let It Be'),\n",
       " (1, 'The Beatles', 2, 'Let It Be', 1, 1970, 5, 2, 'Across the Universe')]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM (artist_library JOIN album_library2 ON artist_library.artist_id = album_library2.artist_id) JOIN song_library ON album_library2.album_id=song_library.album_id;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**The dataset is Normalized.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "### Adding a new table 'song_length' to (3NF) dataset\n",
    "\n",
    "`Table Name: album_library \n",
    "column 0: Album Id\n",
    "column 1: Album Name\n",
    "column 2: Artist Id\n",
    "column 3: Year `\n",
    "\n",
    "`Table Name: song_library\n",
    "column 0: Song Id\n",
    "column 1: Song Name\n",
    "column 3: Album Id`\n",
    "\n",
    "`Table Name: artist_library\n",
    "column 0: Artist Id\n",
    "column 1: Artist Name `\n",
    "\n",
    "`Table Name: song_length\n",
    "column 0: Song Id\n",
    "column 1: Song length in seconds\n",
    "`\n",
    "\n",
    "<img src=\"images/table4.png\" width=\"450\" height=\"450\"> <img src=\"images/table5.png\" width=\"450\" height=\"450\"> <img src=\"images/table6.png\" width=\"350\" height=\"350\"> <img src=\"images/table7.png\" width=\"350\" height=\"350\">\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "Done.\n",
      "5 rows affected.\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>song_id</th>\n",
       "        <th>song_length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>227</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 163), (2, 137), (3, 145), (4, 240), (5, 227)]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE IF NOT EXISTS song_length (song_id int, song_length int);\n",
    "INSERT INTO song_length (song_id, song_length)\n",
    "VALUES (1, 163),\n",
    "(2, 137),\n",
    "(3, 145),\n",
    "(4, 240),\n",
    "(5, 227);\n",
    "\n",
    "SELECT * FROM song_length;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "#### Need to perform 3 `JOIN` on the 4 tables to get all information\n",
    "\n",
    "`artist_id \n",
    "artist_name \n",
    "album_id \n",
    "album_name \n",
    "year \n",
    "song_id\n",
    "song_name \n",
    "song_length`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>artist_id</th>\n",
       "        <th>artist_name</th>\n",
       "        <th>album_id</th>\n",
       "        <th>album_name</th>\n",
       "        <th>year</th>\n",
       "        <th>song_id</th>\n",
       "        <th>song_name</th>\n",
       "        <th>song_length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>1965</td>\n",
       "        <td>1</td>\n",
       "        <td>Michelle</td>\n",
       "        <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>1965</td>\n",
       "        <td>2</td>\n",
       "        <td>Think For Yourself</td>\n",
       "        <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>1965</td>\n",
       "        <td>3</td>\n",
       "        <td>In My Life</td>\n",
       "        <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>1970</td>\n",
       "        <td>4</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>1970</td>\n",
       "        <td>5</td>\n",
       "        <td>Across the Universe</td>\n",
       "        <td>227</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'The Beatles', 1, 'Rubber Soul', 1965, 1, 'Michelle', 163),\n",
       " (1, 'The Beatles', 1, 'Rubber Soul', 1965, 2, 'Think For Yourself', 137),\n",
       " (1, 'The Beatles', 1, 'Rubber Soul', 1965, 3, 'In My Life', 145),\n",
       " (1, 'The Beatles', 2, 'Let It Be', 1970, 4, 'Let It Be', 240),\n",
       " (1, 'The Beatles', 2, 'Let It Be', 1970, 5, 'Across the Universe', 227)]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT artist_library.artist_id, artist_name, album_library2.album_id, album_name, year, song_library.song_id, song_name, song_length \n",
    "FROM ((artist_library JOIN album_library2 ON artist_library.artist_id = album_library2.artist_id) \n",
    "JOIN song_library ON album_library2.album_id=song_library.album_id)\n",
    "JOIN song_length ON song_library.song_id=song_length.song_id;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Denormalization\n",
    "Remember that `JOINS` are slow and during a read heavy workload that required low latency queries, the number of `JOINS` are to be reduced. With **denormalization**, the queries are optimized by reducing the number of `JOINS` even if that means duplicating data."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Query 1 : Get a list of all songs\n",
    "`select artist_name, album_name, year, song_name, song_length FROM <min number of tables>`\n",
    "\n",
    "To reduce the number of tables, add `song_length` to the `song_library` table and `artist_name` to `album_library`. \n",
    "\n",
    "`Table Name: album_library \n",
    "column 0: Album Id\n",
    "column 1: Album Name\n",
    "column 2: Artist Name\n",
    "column 3: Year `\n",
    "\n",
    "`Table Name: song_library\n",
    "column: Song Id\n",
    "column: Album Id  \n",
    "column: Song Name\n",
    "column: Song Length`\n",
    "\n",
    "<img src=\"images/table9.png\" width=\"450\" height=\"450\">\n",
    "<img src=\"images/table8.png\" width=\"350\" height=\"350\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "Done.\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE IF NOT EXISTS song_library2 (song_id int, album_id int, song_name varchar, song_length int);\n",
    "INSERT INTO song_library2 (song_id, album_id, song_name, song_length)\n",
    "VALUES (1, 1, 'Michelle',163),\n",
    "(2, 1, 'Think For Yourself',137),\n",
    "(3, 1, 'In My Life',145),\n",
    "(4, 2, 'Let It Be',240),\n",
    "(5, 2, 'Across the Universe',227);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "cell_style": "split"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>song_id</th>\n",
       "        <th>album_id</th>\n",
       "        <th>song_name</th>\n",
       "        <th>song_length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>Michelle</td>\n",
       "        <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "        <td>Think For Yourself</td>\n",
       "        <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>1</td>\n",
       "        <td>In My Life</td>\n",
       "        <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>2</td>\n",
       "        <td>Across the Universe</td>\n",
       "        <td>227</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1, 'Michelle', 163),\n",
       " (2, 1, 'Think For Yourself', 137),\n",
       " (3, 1, 'In My Life', 145),\n",
       " (4, 2, 'Let It Be', 240),\n",
       " (5, 2, 'Across the Universe', 227)]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select * from song_library2;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "cell_style": "split"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>album_id</th>\n",
       "        <th>album_name</th>\n",
       "        <th>artist_name</th>\n",
       "        <th>year</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>The Beatles</td>\n",
       "        <td>1970</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Rubber Soul', 'The Beatles', 1965),\n",
       " (2, 'Let It Be', 'The Beatles', 1970)]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM album_library;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>artist_name</th>\n",
       "        <th>album_name</th>\n",
       "        <th>year</th>\n",
       "        <th>song_name</th>\n",
       "        <th>song_length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Beatles</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>1965</td>\n",
       "        <td>Michelle</td>\n",
       "        <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Beatles</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>1965</td>\n",
       "        <td>Think For Yourself</td>\n",
       "        <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Beatles</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>1965</td>\n",
       "        <td>In My Life</td>\n",
       "        <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Beatles</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>1970</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Beatles</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>1970</td>\n",
       "        <td>Across the Universe</td>\n",
       "        <td>227</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('The Beatles', 'Rubber Soul', 1965, 'Michelle', 163),\n",
       " ('The Beatles', 'Rubber Soul', 1965, 'Think For Yourself', 137),\n",
       " ('The Beatles', 'Rubber Soul', 1965, 'In My Life', 145),\n",
       " ('The Beatles', 'Let It Be', 1970, 'Let It Be', 240),\n",
       " ('The Beatles', 'Let It Be', 1970, 'Across the Universe', 227)]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT artist_name, album_name, year, song_name, song_length FROM song_library2 JOIN album_library ON song_library2.album_id = album_library.album_id;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using only one `JOIN` statement, the information from the query is obtained."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "#### Query 2: Get the length of each album in seconds \n",
    "`select album_name SUM(song_length) FROM <min number of tables> GROUP BY album_name` \n",
    "\n",
    "A `JOIN` can be performed on the tables created above to get this information. However, we can skip the `JOIN` statement by creating a new table. \n",
    "\n",
    "`Table Name: album_length\n",
    "col: Song Id\n",
    "Col: Album Id\n",
    "col: Song Length\n",
    "`\n",
    "<img src=\"images/table11.png\" width=\"350\" height=\"350\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "Done.\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE IF NOT EXISTS album_length (song_id int, album_name varchar, song_length int);\n",
    "INSERT INTO album_length (song_id, album_name, song_length)\n",
    "VALUES (1, 'Rubber Soul',163),\n",
    "(2, 'Rubber Soul',137),\n",
    "(3, 'Rubber Soul',145),\n",
    "(4, 'Let It Be',240),\n",
    "(5, 'Let It Be',227);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "cell_style": "split",
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>song_id</th>\n",
       "        <th>album_name</th>\n",
       "        <th>song_length</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3</td>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>227</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Rubber Soul', 163),\n",
       " (2, 'Rubber Soul', 137),\n",
       " (3, 'Rubber Soul', 145),\n",
       " (4, 'Let It Be', 240),\n",
       " (5, 'Let It Be', 227)]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM album_length;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "cell_style": "split",
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>album_name</th>\n",
       "        <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rubber Soul</td>\n",
       "        <td>445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Let It Be</td>\n",
       "        <td>467</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Rubber Soul', 445), ('Let It Be', 467)]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT album_name, SUM(song_length) FROM album_length GROUP BY album_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**The normalized table have been denormalized in order to speed up the performance and allow for simplier queries to be executed.** "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Visualizing all tables and dropping them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "9 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>schemaname</th>\n",
       "        <th>tablename</th>\n",
       "        <th>tableowner</th>\n",
       "        <th>tablespace</th>\n",
       "        <th>hasindexes</th>\n",
       "        <th>hasrules</th>\n",
       "        <th>hastriggers</th>\n",
       "        <th>rowsecurity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>album_library2</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>artist_library</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>song_length</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>music_library</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>music_library_1nf</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>album_library</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>song_library</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>song_library2</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>album_length</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('public', 'album_library2', 'jamwine', None, False, False, False, False),\n",
       " ('public', 'artist_library', 'jamwine', None, False, False, False, False),\n",
       " ('public', 'song_length', 'jamwine', None, False, False, False, False),\n",
       " ('public', 'music_library', 'jamwine', None, False, False, False, False),\n",
       " ('public', 'music_library_1nf', 'jamwine', None, False, False, False, False),\n",
       " ('public', 'album_library', 'jamwine', None, False, False, False, False),\n",
       " ('public', 'song_library', 'jamwine', None, False, False, False, False),\n",
       " ('public', 'song_library2', 'jamwine', None, False, False, False, False),\n",
       " ('public', 'album_length', 'jamwine', None, False, False, False, False)]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql $get_all_tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP table music_library;\n",
    "DROP table music_library_1nf;\n",
    "DROP table album_library;\n",
    "DROP table song_library;\n",
    "DROP table album_library2;\n",
    "DROP table artist_library;\n",
    "DROP table song_length;\n",
    "DROP table song_library2;\n",
    "DROP table album_length;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fact and Dimension tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating both Fact and Dimension tables for an online Music Store\n",
    "\n",
    "`Table Name: customer_transactions\n",
    "column: Customer Id\n",
    "column: Store Id\n",
    "column: Spent`\n",
    "\n",
    "`Table Name: Customer\n",
    "column: Customer Id\n",
    "column: Name\n",
    "column: Rewards`\n",
    "\n",
    "`Table Name: store\n",
    "column: Store Id\n",
    "column: State`\n",
    "\n",
    "`Table Name: items_purchased\n",
    "column: customer id\n",
    "column: Item Name`\n",
    "\n",
    "<img src=\"images/starSchema.png\" width=\"750\" height=\"750\">\n",
    "\n",
    "#### This representation suggests a \"STAR Schema\", one fact table (the center of the star) and 3 dimension tables that are coming from it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "Done.\n",
      "2 rows affected.\n",
      "Done.\n",
      "2 rows affected.\n",
      "Done.\n",
      "2 rows affected.\n",
      "Done.\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE IF NOT EXISTS customer_transactions (customer_id int, store_id int, spent numeric);\n",
    "INSERT INTO customer_transactions (customer_id, store_id, spent)\n",
    "VALUES (1, 1, 20.50),\n",
    "(2, 1, 35.21);\n",
    "\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS items_purchased (customer_id int, item_number int, item_name varchar);\n",
    "INSERT INTO items_purchased (customer_id, item_number, item_name)\n",
    "VALUES (1, 1, 'Rubber Soul'),\n",
    "(2, 3, 'Let It Be');\n",
    "\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS store (store_id int, state varchar);\n",
    "INSERT INTO store (store_id, state)\n",
    "VALUES (1, 'CA'),\n",
    "(2, 'WA');\n",
    "\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS customer (customer_id int, name varchar, rewards boolean);\n",
    "INSERT INTO customer (customer_id, name, rewards)\n",
    "VALUES (1, 'Amanda', True),\n",
    "(2, 'Toby', False);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "cell_style": "split"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>customer_id</th>\n",
       "        <th>store_id</th>\n",
       "        <th>spent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>20.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "        <td>35.21</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1, Decimal('20.50')), (2, 1, Decimal('35.21'))]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fact Table\n",
    "%sql SELECT * FROM customer_transactions;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "cell_style": "split"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>customer_id</th>\n",
       "        <th>item_number</th>\n",
       "        <th>item_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>1</td>\n",
       "        <td>Rubber Soul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "        <td>Let It Be</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 1, 'Rubber Soul'), (2, 3, 'Let It Be')]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dimension Table\n",
    "%sql SELECT * FROM items_purchased;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "cell_style": "split"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>store_id</th>\n",
       "        <th>state</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>WA</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'CA'), (2, 'WA')]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dimension Table\n",
    "%sql SELECT * FROM store;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "cell_style": "split"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>customer_id</th>\n",
       "        <th>name</th>\n",
       "        <th>rewards</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>Amanda</td>\n",
       "        <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>Toby</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 'Amanda', True), (2, 'Toby', False)]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dimension Table\n",
    "%sql SELECT * FROM customer;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**A variety of queries can be done on this data easily because of utilizing the fact/dimension and Star Schema**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### _`Query 1:`_  Find all the customers that spent more than 30 dollars, who are they, what did they buy and if they are a rewards member"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>item_name</th>\n",
       "        <th>rewards</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Toby</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Toby', 'Let It Be', False)]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, item_name, rewards \n",
    "FROM ((customer_transactions\n",
    "JOIN customer ON customer.customer_id=customer_transactions.customer_id)\n",
    "JOIN items_purchased ON customer_transactions.customer_id=items_purchased.customer_id)\n",
    "WHERE spent > 30 ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### _`Query 2:`_  How much did Store 1 sell?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>store_id</th>\n",
       "        <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>55.71</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, Decimal('55.71'))]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT store_id, SUM(spent) FROM customer_transactions WHERE store_id = 1 GROUP BY store_id;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### _`Query 3:`_  How much did Customer 2 spend?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>customer_id</th>\n",
       "        <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2</td>\n",
       "        <td>35.21</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2, Decimal('35.21'))]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT customer_id, SUM(spent) FROM customer_transactions WHERE customer_id = 2 GROUP BY customer_id;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### _`Query 4:`_  Find all the customers that spent more than 30 dollars, who are they, which store they bought it from, location of the store, what they bought and if they are a rewards member"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>store_id</th>\n",
       "        <th>state</th>\n",
       "        <th>item_name</th>\n",
       "        <th>rewards</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Toby</td>\n",
       "        <td>1</td>\n",
       "        <td>CA</td>\n",
       "        <td>Let It Be</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Toby', 1, 'CA', 'Let It Be', False)]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql SELECT name, store.store_id, store.state, item_name, customer.rewards\n",
    "FROM (((customer_transactions\n",
    "JOIN customer ON customer.customer_id=customer_transactions.customer_id)\n",
    "JOIN store ON customer_transactions.store_id=store.store_id)\n",
    "JOIN items_purchased ON customer_transactions.customer_id=items_purchased.customer_id)\n",
    "WHERE spent > 30;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Summary:** By utilizing fact and dimension tables (STAR schema), \"facts/metrics\" from the fact table (how much each store sold) are easily fetched. Also, it is feasible to get information about customers and to do more indepth analytics for business questions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dropping Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>schemaname</th>\n",
       "        <th>tablename</th>\n",
       "        <th>tableowner</th>\n",
       "        <th>tablespace</th>\n",
       "        <th>hasindexes</th>\n",
       "        <th>hasrules</th>\n",
       "        <th>hastriggers</th>\n",
       "        <th>rowsecurity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>store</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>customer</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>customer_transactions</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>public</td>\n",
       "        <td>items_purchased</td>\n",
       "        <td>jamwine</td>\n",
       "        <td>None</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('public', 'store', 'jamwine', None, False, False, False, False),\n",
       " ('public', 'customer', 'jamwine', None, False, False, False, False),\n",
       " ('public', 'customer_transactions', 'jamwine', None, False, False, False, False),\n",
       " ('public', 'items_purchased', 'jamwine', None, False, False, False, False)]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql $get_all_tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://jamwine:***@localhost:5433/music_db\n",
      "   postgresql://jamwine:***@localhost:5433/postgresdb\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP table customer_transactions;\n",
    "DROP table items_purchased;\n",
    "DROP table store;\n",
    "DROP table customer;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}