Lesson 2 Exercise 3 Creating Fact and Dimension Tables with Star Schema.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lesson 2 Exercise 3: Creating Fact and Dimension Tables with Star Schema\n",
    "\n",
    "<img src=\"images/postgresSQLlogo.png\" width=\"250\" height=\"250\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Walk through the basics of modeling data using Fact and Dimension tables. You will create both Fact and Dimension tables and show how this is a basic element of the Star Schema. \n",
    "\n",
    "#### Where you see ##### you will need to fill in code. \n",
    "\n",
    "### This exercise will be more challenging than the last. Use the information provided to create the tables and write the insert statements. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import the library \n",
    "Note: An error might popup after this command has exectuted. If it does read it careful before ignoring. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create a connection to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    conn = psycopg2.connect(\"host=127.0.0.1 dbname=studentdb user=student password=student\")\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Could not make connection to the Postgres database\")\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Next use that connect to get a cursor that we will use to execute queries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur = conn.cursor()\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Could not get cursor to the Database\")\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### For this demo we will use automactic commit so that each action is commited without having to call conn.commit() after each command. The ability to rollback and commit transactions is a feature of Relational Databases. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.set_session(autocommit=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Imagine you work at an online Music Store. There will be many tables in our database, but let's just focus on 4 tables around customer purchases. \n",
    "\n",
    "<img src=\"images/starSchema.png\" width=\"750\" height=\"750\">\n",
    "\n",
    "### From this representation you can start to see the makings of a \"STAR\". You will have one fact table (the center of the star) and 3  dimension tables that are coming from it."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TO-DO: Create the Fact table and insert the data into the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur.execute('CREATE TABLE IF NOT EXISTS customer_transactions(customer_id int, store_id int, spent numeric);')\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Issue creating table\")\n",
    "    print (e)\n",
    "    \n",
    "#Insert into all tables \n",
    "try: \n",
    "    cur.execute('INSERT INTO customer_transactions(customer_id, store_id, spent)\\\n",
    "    VALUES(%s,%s,%s)', \\\n",
    "                (1,1,20.50))\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Inserting Rows\")\n",
    "    print (e)\n",
    "try: \n",
    "    cur.execute('INSERT INTO customer_transactions(customer_id, store_id, spent)\\\n",
    "    VALUES(%s,%s,%s)', (2, 1, 35.21))\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Inserting Rows\")\n",
    "    print (e)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TO-DO: Create the Dimension tables and insert data into those tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur.execute('CREATE TABLE IF NOT EXISTS items_purchased(customer_id int, item_number int, item_name varchar);')\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Issue creating table\")\n",
    "    print (e)\n",
    "    \n",
    "try: \n",
    "    cur.execute('INSERT INTO items_purchased(customer_id, item_number, item_name)\\\n",
    "   VALUES(%s,%s,%s)', (1,1,\"Rubber Soul\"))\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Inserting Rows\")\n",
    "    print (e)\n",
    "    \n",
    "try: \n",
    "    cur.execute('INSERT INTO items_purchased(customer_id, item_number, item_name)\\\n",
    "    VALUES(%s,%s,%s)', (2,3,\"Let it Be\"))\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Inserting Rows\")\n",
    "    print (e)\n",
    "    \n",
    "try: \n",
    "    cur.execute('CREATE TABLE IF NOT EXISTS store(store_id int, state varchar);')\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Issue creating table\")\n",
    "    print (e)\n",
    "    \n",
    "try: \n",
    "    cur.execute('INSERT INTO store(store_id, state)\\\n",
    "    VALUES(%s,%s)', (1,\"CA\"))\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Inserting Rows\")\n",
    "    print (e)\n",
    "try: \n",
    "    cur.execute('INSERT INTO store(store_id, state)\\\n",
    "    VALUES(%s,%s)', (2,'WA'))\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Inserting Rows\")\n",
    "    print (e)\n",
    "    \n",
    "try: \n",
    "    cur.execute('CREATE TABLE IF NOT EXISTS customer(customer_id int, name varchar, rewards boolean);')\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Issue creating table\")\n",
    "    print (e)\n",
    "    \n",
    "try: \n",
    "    cur.execute('INSERT INTO customer(customer_id, name, rewards)\\\n",
    "    VALUES(%s,%s,%s)',(1, 'Amanda', \"Y\"))\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Inserting Rows\")\n",
    "    print (e)   \n",
    "                \n",
    "try: \n",
    "    cur.execute('INSERT INTO customer(customer_id, name, rewards)\\\n",
    "    VALUES(%s,%s,%s)',(2, 'Toby', \"N\"))\n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Inserting Rows\")\n",
    "    print (e)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now run the following queries on this data easily because of utilizing the Fact/ Dimension and Star Schema\n",
    " \n",
    "#### Query 1: 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.\n",
    "\n",
    "#### Query 2: How much did Customer 2 spend?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 1:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Toby', 2, 'CA', 'Let it Be', False)\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    cur.execute('SELECT name, customer_transactions.customer_id, state, item_name, rewards \\\n",
    "    FROM (((customer_transactions JOIN items_purchased ON customer_transactions.customer_id = items_purchased.customer_id)\\\n",
    "    JOIN store ON customer_transactions.store_id = store.store_id) \\\n",
    "    JOIN customer on customer_transactions.customer_id = customer.customer_id)\\\n",
    "    WHERE spent > 30')\n",
    "    \n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: select *\")\n",
    "    print(e)\n",
    "\n",
    "row = cur.fetchone()\n",
    "while row:\n",
    "   print(row)\n",
    "   row = cur.fetchone()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Your output from the above cell should look like this:\n",
    "('Toby', 1, 'CA', 'Let It Be', False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query 2: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(2, Decimal('35.21'))\n"
     ]
    }
   ],
   "source": [
    "try: \n",
    "    cur.execute(\"SELECT customer_id, spent FROM customer_transactions WHERE customer_id = 2\")\n",
    "    \n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: select *\")\n",
    "    print (e)\n",
    "\n",
    "row = cur.fetchone()\n",
    "print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Your output from the above cell should include Customer 2 and the amount: \n",
    "(2, 35.21)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Summary: You can see here from this elegant schema that we were: 1) able to get \"facts/metrics\" from our fact table (how much each store sold), and 2) information about our customers that will allow us to do more indepth analytics to get answers to business questions by utilizing our fact and dimension tables. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### TO-DO: Drop the tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "try: \n",
    "    cur.execute(\"DROP TABLE customer_transactions, items_purchased, store, customer\")\n",
    "    \n",
    "except psycopg2.Error as e: \n",
    "    print(\"Error: Dropping table\")\n",
    "    print (e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### And finally close your cursor and connection. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}