{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Activity 6-1\n",
    "------------\n",
    "\n",
    "The goal for this activity will be to compute some BCNF decompositions, using the tools from last lecture\n",
    "\n",
    "First we'll load those tools, and some sample data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from closure import compute_closure, display_side_by_side, print_setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/tarabalakrishnan/.local/lib/python2.7/site-packages/IPython/config.py:13: ShimWarning: The `IPython.config` package has been deprecated. You should import from traitlets.config instead.\n",
      "  \"You should import from traitlets.config instead.\", ShimWarning)\n",
      "/Users/tarabalakrishnan/.local/lib/python2.7/site-packages/IPython/utils/traitlets.py:5: UserWarning: IPython.utils.traitlets has moved to a top-level traitlets package.\n",
      "  warn(\"IPython.utils.traitlets has moved to a top-level traitlets package.\")\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Connected: None@None'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%load_ext sql\n",
    "%sql sqlite://"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql DROP TABLE IF EXISTS T;\n",
    "CREATE TABLE T(course VARCHAR, classroom INT, time INT);\n",
    "INSERT INTO T VALUES ('CS 364', 132, 900);\n",
    "INSERT INTO T VALUES ('CS 245', 140, 1000);\n",
    "INSERT INTO T VALUES ('EE 101', 210, 900);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 1\n",
    "\n",
    "First, let's decompose `T` into BCNF!  Explicitly go through the steps of the BCNF algorithm using the `compute_closure` function, then decompose the following table (i.e. by creating new SQL tables) into BCNF:\n",
    "\n",
    "We've also made a function, `display_side_by_side`, for nicer display!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM T;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are given the following FDs:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "A = set(['course', 'classroom', 'time'])\n",
    "F = [('course', 'classroom'), (set(['classroom', 'time']), 'course')]\n",
    "print_setup(A, F)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Q:** What real-world constraints do these FDs express?\n",
    "\n",
    "Now, use the `compute_closure` function to help decompose this table to BCNF:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compose into two tables, $T_1$ and $T_2$:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS T1;\n",
    "CREATE TABLE T1 AS SELECT DISTINCT * FROM (\n",
    "        # TODO\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS T2;\n",
    "CREATE TABLE T2 AS SELECT DISTINCT * FROM (\n",
    "        # TODO\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now run the below to display the decomposed tables side-by-side:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "l = %sql SELECT * FROM T1;\n",
    "r = %sql SELECT * FROM T2;\n",
    "display_side_by_side(l,r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Q:** Is this now in BCNF?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise 2\n",
    "\n",
    "In the next section of lecture, we'll discuss a shortcoming of BCNF decompositions; let's see if we can get a glimpse of this now.\n",
    "\n",
    "See if you can insert rows into $T_1$ and/or $T_2$ _which respect the local FDs that still hold_, such that **when $T_1$ and $T_2$ are now recomposed, the original FDs do not hold!**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, reconstruct and print the re-composed table using a SQL query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Q:** What went wrong??  And how could we prevent this from occuring?"
   ]
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}