Relational Algebra.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# The Relational Algebra in Python\n",
    "\n",
    "Copyright Jens Dittrich & Marcel Maltry, [Big Data Analytics Group](https://bigdata.uni-saarland.de/), [CC-BY-SA](https://creativecommons.org/licenses/by-sa/4.0/legalcode)\n",
    "\n",
    "### Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from os import listdir\n",
    "from graphviz import Source\n",
    "\n",
    "from ra.relation import Relation\n",
    "from ra.operators_log import *\n",
    "from ra.operators_phys import *\n",
    "from ra.utils import load_csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------------\n",
      "\u001b[1mmovies_directors\u001b[0m \n",
      "--------------------------\n",
      "\u001b[1mdirector_id  movie_id     \u001b[0m \n",
      "--------------------------\n",
      "43095        121538       \n",
      "78273        223710       \n",
      "78273        118367       \n",
      "78273        164572       \n",
      "78273        267038       \n",
      "43095        299073       \n",
      "11652        10920        \n",
      "11652        333856       \n",
      "78273        176711       \n",
      "43095        291833       \n",
      "\n",
      "WARNING: skipping 30 out of 40 tuples...\n",
      "------\n",
      "\u001b[1mactors\u001b[0m \n",
      "--------------------------------------------------------------------------------------------\n",
      "\u001b[1mid                     first_name             last_name              gender                 \u001b[0m \n",
      "--------------------------------------------------------------------------------------------\n",
      "654019                 Brenda                 Hillhouse              F                      \n",
      "160622                 Ken (I)                Fritz                  M                      \n",
      "684623                 Diana                  Körner                 F                      \n",
      "198005                 James B.               Harris                 M                      \n",
      "154493                 Hadrian                Follett                M                      \n",
      "50074                  Roger                  Booth                  M                      \n",
      "264813                 James                  Lancaster              M                      \n",
      "383596                 David                  Prowse                 M                      \n",
      "271543                 Christopher (I)        Lee                    M                      \n",
      "213646                 William                Hope                   M                      \n",
      "\n",
      "WARNING: skipping 1081 out of 1091 tuples...\n",
      "---------\n",
      "\u001b[1mdirectors\u001b[0m \n",
      "------------------------------------\n",
      "\u001b[1mid          first_name  last_name   \u001b[0m \n",
      "------------------------------------\n",
      "11652       James (I)   Cameron     \n",
      "43095       Stanley     Kubrick     \n",
      "78273       Quentin     Tarantino   \n",
      "\n",
      "-------------\n",
      "\u001b[1mmovies_genres\u001b[0m \n",
      "--------------------------\n",
      "\u001b[1mmovie_id     genre        \u001b[0m \n",
      "--------------------------\n",
      "105938       Documentary  \n",
      "65764        Sci-Fi       \n",
      "118367       Drama        \n",
      "223710       Drama        \n",
      "159665       Drama        \n",
      "176712       Thriller     \n",
      "10920        Horror       \n",
      "322652       Short        \n",
      "333856       Romance      \n",
      "369522       Fantasy      \n",
      "\n",
      "WARNING: skipping 92 out of 102 tuples...\n",
      "----------------\n",
      "\u001b[1mdirectors_genres\u001b[0m \n",
      "---------------------------------------\n",
      "\u001b[1mdirector_id  genre        prob         \u001b[0m \n",
      "---------------------------------------\n",
      "43095        Sci-Fi       0.1875       \n",
      "78273        Action       0.5          \n",
      "11652        Drama        0.25         \n",
      "11652        Action       0.5          \n",
      "11652        Thriller     0.416667     \n",
      "78273        Mystery      0.125        \n",
      "43095        Romance      0.1875       \n",
      "11652        Adventure    0.166667     \n",
      "43095        Thriller     0.1875       \n",
      "43095        Short        0.1875       \n",
      "\n",
      "WARNING: skipping 25 out of 35 tuples...\n",
      "------\n",
      "\u001b[1mmovies\u001b[0m \n",
      "----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\u001b[1mid                                                                    name                                                                  year                                                                  rank                                                                  \u001b[0m \n",
      "----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "121538                                                                Full Metal Jacket                                                     1987                                                                  8.2                                                                   \n",
      "299073                                                                Shining, The                                                          1980                                                                  8.2                                                                   \n",
      "328285                                                                Terminator, The                                                       1984                                                                  7.9                                                                   \n",
      "387728                                                                ER                                                                    1994                                                                  7.7                                                                   \n",
      "106666                                                                Eyes Wide Shut                                                        1999                                                                  7.0                                                                   \n",
      "116239                                                                Flying Padre                                                          1951                                                                  5.9                                                                   \n",
      "393538                                                                Jimmy Kimmel Live!                                                    2003                                                                  6.7                                                                   \n",
      "1711                                                                  2001: A Space Odyssey                                                 1968                                                                  8.3                                                                   \n",
      "250612                                                                Paths of Glory                                                        1957                                                                  8.6                                                                   \n",
      "333856                                                                Titanic                                                               1997                                                                  6.9                                                                   \n",
      "\n",
      "WARNING: skipping 30 out of 40 tuples...\n",
      "-----\n",
      "\u001b[1mroles\u001b[0m \n",
      "---------------------------------------------------------------------------------------------\n",
      "\u001b[1mactor_id                       movie_id                       role                           \u001b[0m \n",
      "---------------------------------------------------------------------------------------------\n",
      "650090                         106666                         Masked party principal         \n",
      "348858                         333856                         Fabrizio De Rossi              \n",
      "809699                         106666                         Gayle                          \n",
      "5871                           121538                         Marine                         \n",
      "321439                         1711                           Mission controller             \n",
      "50074                          30431                          King George III                \n",
      "282811                         310455                         Tigranes Levantus (pirate env  \n",
      "144555                         333856                         Olaus Gunderson                \n",
      "373935                         92616                          Maj. T.J. 'King' Kong          \n",
      "792995                         79846                          Herself (female fan in crowd)  \n",
      "\n",
      "WARNING: skipping 1196 out of 1206 tuples...\n"
     ]
    }
   ],
   "source": [
    "# Data source: https://relational.fit.cvut.cz/dataset/IMDb\n",
    "# Information courtesy of IMDb (http://www.imdb.com). Used with permission.\n",
    "# Notice: The data can only be used for personal and non-commercial use and must not\n",
    "# be altered/republished/resold/repurposed to create any kind of online/offline\n",
    "# database of movie information (except for individual personal use).\n",
    "\n",
    "path = 'data/IMDb_sample'  \n",
    "# create a list of all files in that directory that end with \"*.csv\":\n",
    "files = [file for file in listdir(path) if file.endswith('.csv')]\n",
    "\n",
    "relations = [load_csv(path + '/' + file, file[:-4], delimiter='\\t') for file in files]\n",
    "\n",
    "relationsDict = {}\n",
    "for rel in relations:\n",
    "    relationsDict[rel.name] = rel\n",
    "    #globals()[rel.name] = rel\n",
    "    rel.print_table(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# build LeafRelation object from relations dictionary\n",
    "movies_directors = LeafRelation(relationsDict['movies_directors'])\n",
    "actors = LeafRelation(relationsDict['actors'])\n",
    "directors = LeafRelation(relationsDict['directors'])\n",
    "movies_genres = LeafRelation(relationsDict['movies_genres'])\n",
    "directors_genres = LeafRelation(relationsDict['directors_genres'])\n",
    "movies = LeafRelation(relationsDict['movies'])\n",
    "roles = LeafRelation(relationsDict['roles'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[directors] : {[id:int, first_name:str, last_name:str]}\n",
      "{\n",
      "\t(11652, James (I), Cameron),\n",
      "\t(43095, Stanley, Kubrick),\n",
      "\t(78273, Quentin, Tarantino)\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "directors.evaluate().print_set()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\\definecolor{tableheadercolor}{rgb}{0.8,0.8,0.8}\\begin{tabular}{|l|l|l|}\\hline\n",
      "\\multicolumn{3}{|l|}{\\cellcolor{tableheadercolor}{\\textbf{directors}}}\\\\\\hline\n",
      "\t\\cellcolor{tableheadercolor}{\\textbf{id}} & \\cellcolor{tableheadercolor}{\\textbf{first\\textunderscore name}} & \\cellcolor{tableheadercolor}{\\textbf{last\\textunderscore name}} \\\\\n",
      "\t\\hline\\hline\n",
      "\t11652 & James (I) & Cameron \\\\\n",
      "\t43095 & Stanley & Kubrick \\\\\n",
      "\t78273 & Quentin & Tarantino \\\\\n",
      "\\hline\n",
      "\\end{tabular}\n"
     ]
    }
   ],
   "source": [
    "directors.evaluate().print_latex()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"91pt\" height=\"44pt\"\n",
       " viewBox=\"0.00 0.00 91.19 44.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 40)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-40 87.19,-40 87.19,4 -4,4\"/>\n",
       "<!-- Rel -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Rel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"41.6\" cy=\"-18\" rx=\"41.69\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"41.6\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">directors</text>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x124558dc0>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = directors.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Basisoperatoren\n",
    "\n",
    "## Selection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "newmovies =  Selection_ScanBased(movies, 'year>2000')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "σ_ScanBased[year>2000](movies)\n"
     ]
    }
   ],
   "source": [
    "print(newmovies)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "------\n",
       "\u001b[1mResult\u001b[0m \n",
       "----------------------------------------------------------------------------------------\n",
       "\u001b[1mid                    name                  year                  rank                  \u001b[0m \n",
       "----------------------------------------------------------------------------------------\n",
       "127297                Ghosts of the Abyss   2003                  6.7                   \n",
       "10934                 Aliens of the Deep    2005                  6.5                   \n",
       "105938                Expedition: Bismarck  2002                  7.5                   \n",
       "176711                Kill Bill: Vol. 1     2003                  8.4                   \n",
       "176712                Kill Bill: Vol. 2     2004                  8.2                   \n",
       "96779                 Earthship.TV          2001                  5.6                   \n",
       "393538                Jimmy Kimmel Live!    2003                  6.7                   \n",
       "159665                Inglorious Bastards   2006                  8.3                   "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newmovies.evaluate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Result'"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newmovies.evaluate().name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"213pt\" height=\"116pt\"\n",
       " viewBox=\"0.00 0.00 213.38 116.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 112)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-112 209.38,-112 209.38,4 -4,4\"/>\n",
       "<!-- Sel -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Sel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"102.69\" cy=\"-90\" rx=\"102.88\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.69\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[year&gt;2000]</text>\n",
       "</g>\n",
       "<!-- IRel -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>IRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"102.69\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.69\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- IRel&#45;&gt;Sel -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>IRel&#45;&gt;Sel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M102.69,-36.3C102.69,-44.02 102.69,-53.29 102.69,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"99.19,-61.9 102.69,-71.9 106.19,-61.9 99.19,-61.9\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x124561ca0>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = newmovies.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Projection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "exp2 = Projection_ScanBased(newmovies, 'id, year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "π_ScanBased[id, year](σ_ScanBased[year>2000](movies))\n"
     ]
    }
   ],
   "source": [
    "print(exp2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "------\n",
       "\u001b[1mResult\u001b[0m \n",
       "----------------\n",
       "\u001b[1mid      year    \u001b[0m \n",
       "----------------\n",
       "176711  2003    \n",
       "393538  2003    \n",
       "10934   2005    \n",
       "96779   2001    \n",
       "176712  2004    \n",
       "105938  2002    \n",
       "159665  2006    \n",
       "127297  2003    "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exp2.evaluate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"213pt\" height=\"188pt\"\n",
       " viewBox=\"0.00 0.00 213.38 188.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 184)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-184 209.38,-184 209.38,4 -4,4\"/>\n",
       "<!-- Pro -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Pro</title>\n",
       "<ellipse fill=\"#76d6ff\" stroke=\"#76d6ff\" cx=\"102.69\" cy=\"-162\" rx=\"90.98\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.69\" y=\"-158.3\" font-family=\"Times,serif\" font-size=\"14.00\">π_ScanBased[id, year]</text>\n",
       "</g>\n",
       "<!-- ISel -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>ISel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"102.69\" cy=\"-90\" rx=\"102.88\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.69\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[year&gt;2000]</text>\n",
       "</g>\n",
       "<!-- ISel&#45;&gt;Pro -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>ISel&#45;&gt;Pro</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M102.69,-108.3C102.69,-116.02 102.69,-125.29 102.69,-133.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"99.19,-133.9 102.69,-143.9 106.19,-133.9 99.19,-133.9\"/>\n",
       "</g>\n",
       "<!-- IIRel -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>IIRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"102.69\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.69\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- IIRel&#45;&gt;ISel -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>IIRel&#45;&gt;ISel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M102.69,-36.3C102.69,-44.02 102.69,-53.29 102.69,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"99.19,-61.9 102.69,-71.9 106.19,-61.9 99.19,-61.9\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x124623280>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = exp2.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Union"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "goodmovies = Selection_ScanBased(movies, 'rank>=7.5')\n",
    "goodOrNew = Union_HashBased(goodmovies, newmovies)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(σ_ScanBased[rank>=7.5](movies)) ∪ (σ_ScanBased[year>2000](movies))\n"
     ]
    }
   ],
   "source": [
    "print(goodOrNew)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[Result] : {[id:int, name:str, year:int, rank:float]}\n",
      "{\n",
      "\t(121538, Full Metal Jacket, 1987, 8.2),\n",
      "\t(299073, Shining, The, 1980, 8.2),\n",
      "\t(328285, Terminator, The, 1984, 7.9),\n",
      "\t(387728, ER, 1994, 7.7),\n",
      "\t(393538, Jimmy Kimmel Live!, 2003, 6.7),\n",
      "\t(1711, 2001: A Space Odyssey, 1968, 8.3),\n",
      "\t(250612, Paths of Glory, 1957, 8.6),\n",
      "\t(10920, Aliens, 1986, 8.2),\n",
      "\t(267038, Pulp Fiction, 1994, 8.7),\n",
      "\t(276217, Reservoir Dogs, 1992, 8.3),\n",
      "\t(127297, Ghosts of the Abyss, 2003, 6.7),\n",
      "\t(177019, Killing, The, 1956, 8.1),\n",
      "\t(310455, Spartacus, 1960, 8.0),\n",
      "\t(92616, Dr. Strangelove or: How I Learned to Stop Worrying and Love the Bomb, 1964, 8.7),\n",
      "\t(30431, Barry Lyndon, 1975, 7.9),\n",
      "\t(176711, Kill Bill: Vol. 1, 2003, 8.4),\n",
      "\t(96779, Earthship.TV, 2001, 5.6),\n",
      "\t(328277, Terminator 2: Judgment Day, 1991, 8.1),\n",
      "\t(10934, Aliens of the Deep, 2005, 6.5),\n",
      "\t(105938, Expedition: Bismarck, 2002, 7.5),\n",
      "\t(65764, Clockwork Orange, A, 1971, 8.3),\n",
      "\t(176712, Kill Bill: Vol. 2, 2004, 8.2),\n",
      "\t(164572, Jackie Brown, 1997, 7.5),\n",
      "\t(193519, Lolita, 1962, 7.6),\n",
      "\t(159665, Inglorious Bastards, 2006, 8.3)\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "goodOrNew.evaluate().print_set()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"436pt\" height=\"188pt\"\n",
       " viewBox=\"0.00 0.00 435.73 188.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 184)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-184 431.73,-184 431.73,4 -4,4\"/>\n",
       "<!-- SOp -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>SOp</title>\n",
       "<ellipse fill=\"#ff7e79\" stroke=\"#ff7e79\" cx=\"213.04\" cy=\"-162\" rx=\"61.99\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"213.04\" y=\"-158.3\" font-family=\"Times,serif\" font-size=\"14.00\">∪_HashBased</text>\n",
       "</g>\n",
       "<!-- LSel -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>LSel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"102.04\" cy=\"-90\" rx=\"102.08\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.04\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[rank&gt;=7.5]</text>\n",
       "</g>\n",
       "<!-- LSel&#45;&gt;SOp -->\n",
       "<g id=\"edge3\" class=\"edge\">\n",
       "<title>LSel&#45;&gt;SOp</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M128.07,-107.41C143.42,-117.1 163.02,-129.45 179.52,-139.86\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"178,-143.04 188.32,-145.41 181.73,-137.12 178,-143.04\"/>\n",
       "</g>\n",
       "<!-- LIRel -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>LIRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"102.04\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.04\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- LIRel&#45;&gt;LSel -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>LIRel&#45;&gt;LSel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M102.04,-36.3C102.04,-44.02 102.04,-53.29 102.04,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"98.54,-61.9 102.04,-71.9 105.54,-61.9 98.54,-61.9\"/>\n",
       "</g>\n",
       "<!-- RSel -->\n",
       "<g id=\"node4\" class=\"node\">\n",
       "<title>RSel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"325.04\" cy=\"-90\" rx=\"102.88\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"325.04\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[year&gt;2000]</text>\n",
       "</g>\n",
       "<!-- RSel&#45;&gt;SOp -->\n",
       "<g id=\"edge4\" class=\"edge\">\n",
       "<title>RSel&#45;&gt;SOp</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M298.78,-107.41C283.29,-117.1 263.52,-129.45 246.87,-139.86\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"244.61,-137.14 237.98,-145.41 248.32,-143.08 244.61,-137.14\"/>\n",
       "</g>\n",
       "<!-- RIRel -->\n",
       "<g id=\"node5\" class=\"node\">\n",
       "<title>RIRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"325.04\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"325.04\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- RIRel&#45;&gt;RSel -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>RIRel&#45;&gt;RSel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M325.04,-36.3C325.04,-44.02 325.04,-53.29 325.04,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"321.54,-61.9 325.04,-71.9 328.54,-61.9 321.54,-61.9\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x12462a670>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = goodOrNew.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, without union but with two conditions in the selection instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "goodOrNewSel = Selection_ScanBased(movies, 'year>2000 or rank>=7.5')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "σ_ScanBased[year>2000 or rank>=7.5](movies)\n"
     ]
    }
   ],
   "source": [
    "print(goodOrNewSel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[Result] : {[id:int, name:str, year:int, rank:float]}\n",
      "{\n",
      "\t(121538, Full Metal Jacket, 1987, 8.2),\n",
      "\t(299073, Shining, The, 1980, 8.2),\n",
      "\t(328285, Terminator, The, 1984, 7.9),\n",
      "\t(387728, ER, 1994, 7.7),\n",
      "\t(393538, Jimmy Kimmel Live!, 2003, 6.7),\n",
      "\t(1711, 2001: A Space Odyssey, 1968, 8.3),\n",
      "\t(250612, Paths of Glory, 1957, 8.6),\n",
      "\t(10920, Aliens, 1986, 8.2),\n",
      "\t(267038, Pulp Fiction, 1994, 8.7),\n",
      "\t(276217, Reservoir Dogs, 1992, 8.3),\n",
      "\t(127297, Ghosts of the Abyss, 2003, 6.7),\n",
      "\t(177019, Killing, The, 1956, 8.1),\n",
      "\t(310455, Spartacus, 1960, 8.0),\n",
      "\t(92616, Dr. Strangelove or: How I Learned to Stop Worrying and Love the Bomb, 1964, 8.7),\n",
      "\t(30431, Barry Lyndon, 1975, 7.9),\n",
      "\t(176711, Kill Bill: Vol. 1, 2003, 8.4),\n",
      "\t(96779, Earthship.TV, 2001, 5.6),\n",
      "\t(328277, Terminator 2: Judgment Day, 1991, 8.1),\n",
      "\t(10934, Aliens of the Deep, 2005, 6.5),\n",
      "\t(105938, Expedition: Bismarck, 2002, 7.5),\n",
      "\t(65764, Clockwork Orange, A, 1971, 8.3),\n",
      "\t(176712, Kill Bill: Vol. 2, 2004, 8.2),\n",
      "\t(164572, Jackie Brown, 1997, 7.5),\n",
      "\t(193519, Lolita, 1962, 7.6),\n",
      "\t(159665, Inglorious Bastards, 2006, 8.3)\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "goodOrNewSel.evaluate().print_set()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"312pt\" height=\"116pt\"\n",
       " viewBox=\"0.00 0.00 312.17 116.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 112)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-112 308.17,-112 308.17,4 -4,4\"/>\n",
       "<!-- Sel -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Sel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"152.09\" cy=\"-90\" rx=\"152.17\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"152.09\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[year&gt;2000 or rank&gt;=7.5]</text>\n",
       "</g>\n",
       "<!-- IRel -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>IRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"152.09\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"152.09\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- IRel&#45;&gt;Sel -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>IRel&#45;&gt;Sel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M152.09,-36.3C152.09,-44.02 152.09,-53.29 152.09,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"148.59,-61.9 152.09,-71.9 155.59,-61.9 148.59,-61.9\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x1246239d0>"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = goodOrNewSel.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Difference\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "badmovies = Selection_ScanBased(movies, 'rank<7.5')\n",
    "newGoodMovies = Difference_HashBased(newmovies, badmovies)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(σ_ScanBased[year>2000](movies)) − (σ_ScanBased[rank<7.5](movies))\n"
     ]
    }
   ],
   "source": [
    "print(newGoodMovies)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "------\n",
       "\u001b[1mResult\u001b[0m \n",
       "----------------------------------------------------------------------------------------\n",
       "\u001b[1mid                    name                  year                  rank                  \u001b[0m \n",
       "----------------------------------------------------------------------------------------\n",
       "176711                Kill Bill: Vol. 1     2003                  8.4                   \n",
       "159665                Inglorious Bastards   2006                  8.3                   \n",
       "176712                Kill Bill: Vol. 2     2004                  8.2                   \n",
       "105938                Expedition: Bismarck  2002                  7.5                   "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newGoodMovies.evaluate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"426pt\" height=\"188pt\"\n",
       " viewBox=\"0.00 0.00 425.53 188.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 184)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-184 421.53,-184 421.53,4 -4,4\"/>\n",
       "<!-- SOp -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>SOp</title>\n",
       "<ellipse fill=\"#ff7e79\" stroke=\"#ff7e79\" cx=\"211.69\" cy=\"-162\" rx=\"59.59\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"211.69\" y=\"-158.3\" font-family=\"Times,serif\" font-size=\"14.00\">−_HashBased</text>\n",
       "</g>\n",
       "<!-- LSel -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>LSel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"102.69\" cy=\"-90\" rx=\"102.88\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.69\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[year&gt;2000]</text>\n",
       "</g>\n",
       "<!-- LSel&#45;&gt;SOp -->\n",
       "<g id=\"edge3\" class=\"edge\">\n",
       "<title>LSel&#45;&gt;SOp</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M128.52,-107.59C143.61,-117.28 162.79,-129.6 178.93,-139.96\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"177.23,-143.03 187.54,-145.49 181.02,-137.14 177.23,-143.03\"/>\n",
       "</g>\n",
       "<!-- LIRel -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>LIRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"102.69\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.69\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- LIRel&#45;&gt;LSel -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>LIRel&#45;&gt;LSel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M102.69,-36.3C102.69,-44.02 102.69,-53.29 102.69,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"99.19,-61.9 102.69,-71.9 106.19,-61.9 99.19,-61.9\"/>\n",
       "</g>\n",
       "<!-- RSel -->\n",
       "<g id=\"node4\" class=\"node\">\n",
       "<title>RSel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"320.69\" cy=\"-90\" rx=\"96.68\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"320.69\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[rank&lt;7.5]</text>\n",
       "</g>\n",
       "<!-- RSel&#45;&gt;SOp -->\n",
       "<g id=\"edge4\" class=\"edge\">\n",
       "<title>RSel&#45;&gt;SOp</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M295.14,-107.41C280.06,-117.1 260.81,-129.45 244.61,-139.86\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"242.49,-137.06 235.96,-145.41 246.27,-142.95 242.49,-137.06\"/>\n",
       "</g>\n",
       "<!-- RIRel -->\n",
       "<g id=\"node5\" class=\"node\">\n",
       "<title>RIRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"320.69\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"320.69\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- RIRel&#45;&gt;RSel -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>RIRel&#45;&gt;RSel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M320.69,-36.3C320.69,-44.02 320.69,-53.29 320.69,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"317.19,-61.9 320.69,-71.9 324.19,-61.9 317.19,-61.9\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x124561070>"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = newGoodMovies.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, without intersection but with two conditions in the selection instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "newGoodMoviesSel = Selection_ScanBased(movies, 'year>2000 and not rank<7.5')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "------\n",
       "\u001b[1mResult\u001b[0m \n",
       "----------------------------------------------------------------------------------------\n",
       "\u001b[1mid                    name                  year                  rank                  \u001b[0m \n",
       "----------------------------------------------------------------------------------------\n",
       "176711                Kill Bill: Vol. 1     2003                  8.4                   \n",
       "176712                Kill Bill: Vol. 2     2004                  8.2                   \n",
       "105938                Expedition: Bismarck  2002                  7.5                   \n",
       "159665                Inglorious Bastards   2006                  8.3                   "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newGoodMoviesSel.evaluate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"339pt\" height=\"116pt\"\n",
       " viewBox=\"0.00 0.00 339.47 116.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 112)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-112 335.47,-112 335.47,4 -4,4\"/>\n",
       "<!-- Sel -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Sel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"165.74\" cy=\"-90\" rx=\"165.97\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"165.74\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[year&gt;2000 and not rank&lt;7.5]</text>\n",
       "</g>\n",
       "<!-- IRel -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>IRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"165.74\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"165.74\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- IRel&#45;&gt;Sel -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>IRel&#45;&gt;Sel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M165.74,-36.3C165.74,-44.02 165.74,-53.29 165.74,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"162.24,-61.9 165.74,-71.9 169.24,-61.9 162.24,-61.9\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x124558d60>"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = newGoodMoviesSel.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cartesian Product"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "cartesianProduct = Cartesian_Product_NestedLoop(directors, directors_genres)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(directors) × (directors_genres)\n"
     ]
    }
   ],
   "source": [
    "print(cartesianProduct)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "---------\n",
       "\u001b[1mdirectors\u001b[0m \n",
       "------------------------------------\n",
       "\u001b[1mid          first_name  last_name   \u001b[0m \n",
       "------------------------------------\n",
       "11652       James (I)   Cameron     \n",
       "43095       Stanley     Kubrick     \n",
       "78273       Quentin     Tarantino   "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "directors.evaluate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "----------------\n",
       "\u001b[1mdirectors_genres\u001b[0m \n",
       "---------------------------------------\n",
       "\u001b[1mdirector_id  genre        prob         \u001b[0m \n",
       "---------------------------------------\n",
       "43095        Sci-Fi       0.1875       \n",
       "78273        Action       0.5          \n",
       "11652        Drama        0.25         \n",
       "11652        Action       0.5          \n",
       "11652        Thriller     0.416667     \n",
       "78273        Mystery      0.125        \n",
       "43095        Romance      0.1875       \n",
       "11652        Adventure    0.166667     \n",
       "43095        Thriller     0.1875       \n",
       "43095        Short        0.1875       \n",
       "\n",
       "WARNING: skipping 25 out of 35 tuples..."
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "directors_genres.evaluate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(35, 3)"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(directors_genres.evaluate()), len(directors.evaluate())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "number of expected tuples in cross product:  105\n"
     ]
    }
   ],
   "source": [
    "print('number of expected tuples in cross product: ', len(directors_genres.evaluate())*len(directors.evaluate()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "------\n",
       "\u001b[1mResult\u001b[0m \n",
       "------------------------------------------------------------------------------\n",
       "\u001b[1mid           first_name   last_name    director_id  genre        prob         \u001b[0m \n",
       "------------------------------------------------------------------------------\n",
       "43095        Stanley      Kubrick      43095        Action       0.0625       \n",
       "78273        Quentin      Tarantino    43095        Mystery      0.0625       \n",
       "43095        Stanley      Kubrick      11652        Sci-Fi       0.5          \n",
       "43095        Stanley      Kubrick      43095        Mystery      0.0625       \n",
       "78273        Quentin      Tarantino    78273        Mystery      0.125        \n",
       "78273        Quentin      Tarantino    43095        Documentary  0.1875       \n",
       "11652        James (I)    Cameron      43095        Documentary  0.1875       \n",
       "11652        James (I)    Cameron      43095        Action       0.0625       \n",
       "43095        Stanley      Kubrick      78273        Thriller     0.5          \n",
       "11652        James (I)    Cameron      78273        Crime        0.5          \n",
       "\n",
       "WARNING: skipping 95 out of 105 tuples..."
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cartesianProduct.evaluate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"246pt\" height=\"116pt\"\n",
       " viewBox=\"0.00 0.00 246.49 116.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 112)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-112 242.49,-112 242.49,4 -4,4\"/>\n",
       "<!-- Car -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Car</title>\n",
       "<ellipse fill=\"#d4fb79\" stroke=\"#d4fb79\" cx=\"105.6\" cy=\"-90\" rx=\"63.89\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"105.6\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">×_NestedLoop</text>\n",
       "</g>\n",
       "<!-- LRel -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>LRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"41.6\" cy=\"-18\" rx=\"41.69\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"41.6\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">directors</text>\n",
       "</g>\n",
       "<!-- LRel&#45;&gt;Car -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>LRel&#45;&gt;Car</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M56.12,-34.88C64.15,-43.67 74.3,-54.77 83.32,-64.63\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"80.91,-67.18 90.24,-72.2 86.07,-62.46 80.91,-67.18\"/>\n",
       "</g>\n",
       "<!-- RRel -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>RRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"169.6\" cy=\"-18\" rx=\"68.79\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"169.6\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">directors_genres</text>\n",
       "</g>\n",
       "<!-- RRel&#45;&gt;Car -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>RRel&#45;&gt;Car</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M154.43,-35.59C146.5,-44.27 136.63,-55.05 127.85,-64.66\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"125.26,-62.3 121.1,-72.04 130.43,-67.03 125.26,-62.3\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x12461c0a0>"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = cartesianProduct.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Renaming Relation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "exp11 = Renaming_Relation_ScanBased(goodOrNew, 'good_or_new')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[good_or_new] : {[id:int, name:str, year:int, rank:float]}\n",
      "{\n",
      "\t(121538, Full Metal Jacket, 1987, 8.2),\n",
      "\t(299073, Shining, The, 1980, 8.2),\n",
      "\t(328285, Terminator, The, 1984, 7.9),\n",
      "\t(387728, ER, 1994, 7.7),\n",
      "\t(393538, Jimmy Kimmel Live!, 2003, 6.7),\n",
      "\t(1711, 2001: A Space Odyssey, 1968, 8.3),\n",
      "\t(250612, Paths of Glory, 1957, 8.6),\n",
      "\t(10920, Aliens, 1986, 8.2),\n",
      "\t(267038, Pulp Fiction, 1994, 8.7),\n",
      "\t(276217, Reservoir Dogs, 1992, 8.3),\n",
      "\t(127297, Ghosts of the Abyss, 2003, 6.7),\n",
      "\t(177019, Killing, The, 1956, 8.1),\n",
      "\t(310455, Spartacus, 1960, 8.0),\n",
      "\t(92616, Dr. Strangelove or: How I Learned to Stop Worrying and Love the Bomb, 1964, 8.7),\n",
      "\t(30431, Barry Lyndon, 1975, 7.9),\n",
      "\t(176711, Kill Bill: Vol. 1, 2003, 8.4),\n",
      "\t(96779, Earthship.TV, 2001, 5.6),\n",
      "\t(328277, Terminator 2: Judgment Day, 1991, 8.1),\n",
      "\t(10934, Aliens of the Deep, 2005, 6.5),\n",
      "\t(105938, Expedition: Bismarck, 2002, 7.5),\n",
      "\t(65764, Clockwork Orange, A, 1971, 8.3),\n",
      "\t(176712, Kill Bill: Vol. 2, 2004, 8.2),\n",
      "\t(164572, Jackie Brown, 1997, 7.5),\n",
      "\t(193519, Lolita, 1962, 7.6),\n",
      "\t(159665, Inglorious Bastards, 2006, 8.3)\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "exp11.evaluate().print_set()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"436pt\" height=\"260pt\"\n",
       " viewBox=\"0.00 0.00 435.73 260.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 256)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-256 431.73,-256 431.73,4 -4,4\"/>\n",
       "<!-- ReR -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>ReR</title>\n",
       "<ellipse fill=\"#ff8ad8\" stroke=\"#ff8ad8\" cx=\"213.04\" cy=\"-234\" rx=\"112.38\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"213.04\" y=\"-230.3\" font-family=\"Times,serif\" font-size=\"14.00\">ρ_ScanBased[good_or_new]</text>\n",
       "</g>\n",
       "<!-- ISOp -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>ISOp</title>\n",
       "<ellipse fill=\"#ff7e79\" stroke=\"#ff7e79\" cx=\"213.04\" cy=\"-162\" rx=\"61.99\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"213.04\" y=\"-158.3\" font-family=\"Times,serif\" font-size=\"14.00\">∪_HashBased</text>\n",
       "</g>\n",
       "<!-- ISOp&#45;&gt;ReR -->\n",
       "<g id=\"edge5\" class=\"edge\">\n",
       "<title>ISOp&#45;&gt;ReR</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M213.04,-180.3C213.04,-188.02 213.04,-197.29 213.04,-205.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"209.54,-205.9 213.04,-215.9 216.54,-205.9 209.54,-205.9\"/>\n",
       "</g>\n",
       "<!-- ILSel -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>ILSel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"102.04\" cy=\"-90\" rx=\"102.08\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.04\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[rank&gt;=7.5]</text>\n",
       "</g>\n",
       "<!-- ILSel&#45;&gt;ISOp -->\n",
       "<g id=\"edge3\" class=\"edge\">\n",
       "<title>ILSel&#45;&gt;ISOp</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M128.07,-107.41C143.42,-117.1 163.02,-129.45 179.52,-139.86\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"178,-143.04 188.32,-145.41 181.73,-137.12 178,-143.04\"/>\n",
       "</g>\n",
       "<!-- ILIRel -->\n",
       "<g id=\"node4\" class=\"node\">\n",
       "<title>ILIRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"102.04\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.04\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- ILIRel&#45;&gt;ILSel -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>ILIRel&#45;&gt;ILSel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M102.04,-36.3C102.04,-44.02 102.04,-53.29 102.04,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"98.54,-61.9 102.04,-71.9 105.54,-61.9 98.54,-61.9\"/>\n",
       "</g>\n",
       "<!-- IRSel -->\n",
       "<g id=\"node5\" class=\"node\">\n",
       "<title>IRSel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"325.04\" cy=\"-90\" rx=\"102.88\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"325.04\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[year&gt;2000]</text>\n",
       "</g>\n",
       "<!-- IRSel&#45;&gt;ISOp -->\n",
       "<g id=\"edge4\" class=\"edge\">\n",
       "<title>IRSel&#45;&gt;ISOp</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M298.78,-107.41C283.29,-117.1 263.52,-129.45 246.87,-139.86\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"244.61,-137.14 237.98,-145.41 248.32,-143.08 244.61,-137.14\"/>\n",
       "</g>\n",
       "<!-- IRIRel -->\n",
       "<g id=\"node6\" class=\"node\">\n",
       "<title>IRIRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"325.04\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"325.04\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- IRIRel&#45;&gt;IRSel -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>IRIRel&#45;&gt;IRSel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M325.04,-36.3C325.04,-44.02 325.04,-53.29 325.04,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"321.54,-61.9 325.04,-71.9 328.54,-61.9 321.54,-61.9\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x12461c400>"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = exp11.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Renaming Attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "exp12 = Renaming_Attributes_ScanBased(exp11, 'movies<-name, published<-year')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[Result] : {[id:int, movies:str, published:int, rank:float]}\n",
      "{\n",
      "\t(121538, Full Metal Jacket, 1987, 8.2),\n",
      "\t(299073, Shining, The, 1980, 8.2),\n",
      "\t(328285, Terminator, The, 1984, 7.9),\n",
      "\t(387728, ER, 1994, 7.7),\n",
      "\t(393538, Jimmy Kimmel Live!, 2003, 6.7),\n",
      "\t(1711, 2001: A Space Odyssey, 1968, 8.3),\n",
      "\t(250612, Paths of Glory, 1957, 8.6),\n",
      "\t(10920, Aliens, 1986, 8.2),\n",
      "\t(267038, Pulp Fiction, 1994, 8.7),\n",
      "\t(276217, Reservoir Dogs, 1992, 8.3)\n",
      "\tWARNING: skipping 15 out of 25 tuples...\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "exp12.evaluate().print_set(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"436pt\" height=\"332pt\"\n",
       " viewBox=\"0.00 0.00 435.73 332.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 328)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-328 431.73,-328 431.73,4 -4,4\"/>\n",
       "<!-- ReA -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>ReA</title>\n",
       "<ellipse fill=\"#ff8ad8\" stroke=\"#ff8ad8\" cx=\"213.04\" cy=\"-306\" rx=\"180.87\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"213.04\" y=\"-302.3\" font-family=\"Times,serif\" font-size=\"14.00\">ρ_ScanBased[movies&lt;&#45;name, published&lt;&#45;year]</text>\n",
       "</g>\n",
       "<!-- IReR -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>IReR</title>\n",
       "<ellipse fill=\"#ff8ad8\" stroke=\"#ff8ad8\" cx=\"213.04\" cy=\"-234\" rx=\"112.38\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"213.04\" y=\"-230.3\" font-family=\"Times,serif\" font-size=\"14.00\">ρ_ScanBased[good_or_new]</text>\n",
       "</g>\n",
       "<!-- IReR&#45;&gt;ReA -->\n",
       "<g id=\"edge6\" class=\"edge\">\n",
       "<title>IReR&#45;&gt;ReA</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M213.04,-252.3C213.04,-260.02 213.04,-269.29 213.04,-277.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"209.54,-277.9 213.04,-287.9 216.54,-277.9 209.54,-277.9\"/>\n",
       "</g>\n",
       "<!-- IISOp -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>IISOp</title>\n",
       "<ellipse fill=\"#ff7e79\" stroke=\"#ff7e79\" cx=\"213.04\" cy=\"-162\" rx=\"61.99\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"213.04\" y=\"-158.3\" font-family=\"Times,serif\" font-size=\"14.00\">∪_HashBased</text>\n",
       "</g>\n",
       "<!-- IISOp&#45;&gt;IReR -->\n",
       "<g id=\"edge5\" class=\"edge\">\n",
       "<title>IISOp&#45;&gt;IReR</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M213.04,-180.3C213.04,-188.02 213.04,-197.29 213.04,-205.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"209.54,-205.9 213.04,-215.9 216.54,-205.9 209.54,-205.9\"/>\n",
       "</g>\n",
       "<!-- IILSel -->\n",
       "<g id=\"node4\" class=\"node\">\n",
       "<title>IILSel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"102.04\" cy=\"-90\" rx=\"102.08\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.04\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[rank&gt;=7.5]</text>\n",
       "</g>\n",
       "<!-- IILSel&#45;&gt;IISOp -->\n",
       "<g id=\"edge3\" class=\"edge\">\n",
       "<title>IILSel&#45;&gt;IISOp</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M128.07,-107.41C143.42,-117.1 163.02,-129.45 179.52,-139.86\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"178,-143.04 188.32,-145.41 181.73,-137.12 178,-143.04\"/>\n",
       "</g>\n",
       "<!-- IILIRel -->\n",
       "<g id=\"node5\" class=\"node\">\n",
       "<title>IILIRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"102.04\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.04\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- IILIRel&#45;&gt;IILSel -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>IILIRel&#45;&gt;IILSel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M102.04,-36.3C102.04,-44.02 102.04,-53.29 102.04,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"98.54,-61.9 102.04,-71.9 105.54,-61.9 98.54,-61.9\"/>\n",
       "</g>\n",
       "<!-- IIRSel -->\n",
       "<g id=\"node6\" class=\"node\">\n",
       "<title>IIRSel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"325.04\" cy=\"-90\" rx=\"102.88\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"325.04\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[year&gt;2000]</text>\n",
       "</g>\n",
       "<!-- IIRSel&#45;&gt;IISOp -->\n",
       "<g id=\"edge4\" class=\"edge\">\n",
       "<title>IIRSel&#45;&gt;IISOp</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M298.78,-107.41C283.29,-117.1 263.52,-129.45 246.87,-139.86\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"244.61,-137.14 237.98,-145.41 248.32,-143.08 244.61,-137.14\"/>\n",
       "</g>\n",
       "<!-- IIRIRel -->\n",
       "<g id=\"node7\" class=\"node\">\n",
       "<title>IIRIRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"325.04\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"325.04\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- IIRIRel&#45;&gt;IIRSel -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>IIRIRel&#45;&gt;IIRSel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M325.04,-36.3C325.04,-44.02 325.04,-53.29 325.04,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"321.54,-61.9 325.04,-71.9 328.54,-61.9 321.54,-61.9\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x12461c130>"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = exp12.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Derived Operators\n",
    "\n",
    "## Intersection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "goodAndNew = Intersection_HashBased(newmovies, goodmovies)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "------\n",
       "\u001b[1mResult\u001b[0m \n",
       "----------------------------------------------------------------------------------------\n",
       "\u001b[1mid                    name                  year                  rank                  \u001b[0m \n",
       "----------------------------------------------------------------------------------------\n",
       "176711                Kill Bill: Vol. 1     2003                  8.4                   \n",
       "159665                Inglorious Bastards   2006                  8.3                   \n",
       "176712                Kill Bill: Vol. 2     2004                  8.2                   \n",
       "105938                Expedition: Bismarck  2002                  7.5                   "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodAndNew.evaluate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"436pt\" height=\"188pt\"\n",
       " viewBox=\"0.00 0.00 435.73 188.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 184)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-184 431.73,-184 431.73,4 -4,4\"/>\n",
       "<!-- SOp -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>SOp</title>\n",
       "<ellipse fill=\"#ff7e79\" stroke=\"#ff7e79\" cx=\"213.69\" cy=\"-162\" rx=\"61.99\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"213.69\" y=\"-158.3\" font-family=\"Times,serif\" font-size=\"14.00\">∩_HashBased</text>\n",
       "</g>\n",
       "<!-- LSel -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>LSel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"102.69\" cy=\"-90\" rx=\"102.88\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.69\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[year&gt;2000]</text>\n",
       "</g>\n",
       "<!-- LSel&#45;&gt;SOp -->\n",
       "<g id=\"edge3\" class=\"edge\">\n",
       "<title>LSel&#45;&gt;SOp</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M129,-107.59C144.36,-117.28 163.89,-129.6 180.33,-139.96\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"178.77,-143.12 189.1,-145.49 182.5,-137.19 178.77,-143.12\"/>\n",
       "</g>\n",
       "<!-- LIRel -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>LIRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"102.69\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"102.69\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- LIRel&#45;&gt;LSel -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>LIRel&#45;&gt;LSel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M102.69,-36.3C102.69,-44.02 102.69,-53.29 102.69,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"99.19,-61.9 102.69,-71.9 106.19,-61.9 99.19,-61.9\"/>\n",
       "</g>\n",
       "<!-- RSel -->\n",
       "<g id=\"node4\" class=\"node\">\n",
       "<title>RSel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"325.69\" cy=\"-90\" rx=\"102.08\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"325.69\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[rank&gt;=7.5]</text>\n",
       "</g>\n",
       "<!-- RSel&#45;&gt;SOp -->\n",
       "<g id=\"edge4\" class=\"edge\">\n",
       "<title>RSel&#45;&gt;SOp</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M299.43,-107.41C283.94,-117.1 264.17,-129.45 247.52,-139.86\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"245.26,-137.14 238.63,-145.41 248.97,-143.08 245.26,-137.14\"/>\n",
       "</g>\n",
       "<!-- RIRel -->\n",
       "<g id=\"node5\" class=\"node\">\n",
       "<title>RIRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"325.69\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"325.69\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- RIRel&#45;&gt;RSel -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>RIRel&#45;&gt;RSel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M325.69,-36.3C325.69,-44.02 325.69,-53.29 325.69,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"322.19,-61.9 325.69,-71.9 329.19,-61.9 322.19,-61.9\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x1246503d0>"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = goodAndNew.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Alternatively, without intersection but with two conditions in the selection instead:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "goodAndNewSel = Selection_ScanBased(movies, \"year>2000 and rank>=7.5\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "------\n",
       "\u001b[1mResult\u001b[0m \n",
       "----------------------------------------------------------------------------------------\n",
       "\u001b[1mid                    name                  year                  rank                  \u001b[0m \n",
       "----------------------------------------------------------------------------------------\n",
       "176711                Kill Bill: Vol. 1     2003                  8.4                   \n",
       "176712                Kill Bill: Vol. 2     2004                  8.2                   \n",
       "105938                Expedition: Bismarck  2002                  7.5                   \n",
       "159665                Inglorious Bastards   2006                  8.3                   "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goodAndNewSel.evaluate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"323pt\" height=\"116pt\"\n",
       " viewBox=\"0.00 0.00 322.57 116.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 112)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-112 318.57,-112 318.57,4 -4,4\"/>\n",
       "<!-- Sel -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Sel</title>\n",
       "<ellipse fill=\"#ffd479\" stroke=\"#ffd479\" cx=\"157.29\" cy=\"-90\" rx=\"157.07\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"157.29\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">σ_ScanBased[year&gt;2000 and rank&gt;=7.5]</text>\n",
       "</g>\n",
       "<!-- IRel -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>IRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"157.29\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"157.29\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- IRel&#45;&gt;Sel -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>IRel&#45;&gt;Sel</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M157.29,-36.3C157.29,-44.02 157.29,-53.29 157.29,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"153.79,-61.9 157.29,-71.9 160.79,-61.9 153.79,-61.9\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x124656160>"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = goodAndNewSel.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Theta Join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "directorsAndTheirMovies = Theta_Join_NestedLoop(directors, movies_directors, \"id==director_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------\n",
      "\u001b[1mResult\u001b[0m \n",
      "-----------------------------------------------------------------\n",
      "\u001b[1mid           first_name   last_name    director_id  movie_id     \u001b[0m \n",
      "-----------------------------------------------------------------\n",
      "11652        James (I)    Cameron      11652        369522       \n",
      "43095        Stanley      Kubrick      43095        110246       \n",
      "78273        Quentin      Tarantino    78273        393538       \n",
      "43095        Stanley      Kubrick      43095        291833       \n",
      "43095        Stanley      Kubrick      43095        193519       \n",
      "78273        Quentin      Tarantino    78273        276217       \n",
      "78273        Quentin      Tarantino    78273        159665       \n",
      "43095        Stanley      Kubrick      43095        310455       \n",
      "11652        James (I)    Cameron      11652        328277       \n",
      "11652        James (I)    Cameron      11652        256530       \n",
      "43095        Stanley      Kubrick      43095        65764        \n",
      "43095        Stanley      Kubrick      43095        1711         \n",
      "43095        Stanley      Kubrick      43095        250612       \n",
      "43095        Stanley      Kubrick      43095        176891       \n",
      "43095        Stanley      Kubrick      43095        177019       \n",
      "78273        Quentin      Tarantino    78273        387728       \n",
      "11652        James (I)    Cameron      11652        10920        \n",
      "43095        Stanley      Kubrick      43095        79846        \n",
      "11652        James (I)    Cameron      11652        333856       \n",
      "43095        Stanley      Kubrick      43095        106666       \n",
      "\n",
      "WARNING: skipping 20 out of 40 tuples...\n"
     ]
    }
   ],
   "source": [
    "directorsAndTheirMovies.evaluate().print_table(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"272pt\" height=\"116pt\"\n",
       " viewBox=\"0.00 0.00 272.23 116.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 112)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-112 268.23,-112 268.23,4 -4,4\"/>\n",
       "<!-- Join -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Join</title>\n",
       "<ellipse fill=\"#fffc79\" stroke=\"#fffc79\" cx=\"126.74\" cy=\"-90\" rx=\"126.98\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"126.74\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">⋈_NestedLoop[id==director_id]</text>\n",
       "</g>\n",
       "<!-- LRel -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>LRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"61.74\" cy=\"-18\" rx=\"41.69\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"61.74\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">directors</text>\n",
       "</g>\n",
       "<!-- LRel&#45;&gt;Join -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>LRel&#45;&gt;Join</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M76.49,-34.88C84.53,-43.54 94.66,-54.45 103.71,-64.2\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"101.31,-66.76 110.68,-71.71 106.44,-62 101.31,-66.76\"/>\n",
       "</g>\n",
       "<!-- RRel -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>RRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"192.74\" cy=\"-18\" rx=\"71.49\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"192.74\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies_directors</text>\n",
       "</g>\n",
       "<!-- RRel&#45;&gt;Join -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>RRel&#45;&gt;Join</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M177.1,-35.59C168.92,-44.27 158.74,-55.05 149.69,-64.66\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"147.04,-62.37 142.73,-72.04 152.13,-67.17 147.04,-62.37\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x124656490>"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = directorsAndTheirMovies.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extensions\n",
    "\n",
    "## Grouping\n",
    "\n",
    "### Idea\n",
    "Count the number of female/male actors.\n",
    "* group by gender\n",
    "* count the number of actors per group, count(*)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouping = Grouping_HashBased(actors, 'gender', 'count(*)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[Result] : {[gender:str, count_star:int]}\n",
      "{\n",
      "\t(M, 802),\n",
      "\t(F, 289)\n",
      "}\n"
     ]
    }
   ],
   "source": [
    "grouping.evaluate().print_set()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"254pt\" height=\"116pt\"\n",
       " viewBox=\"0.00 0.00 253.68 116.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 112)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-112 249.68,-112 249.68,4 -4,4\"/>\n",
       "<!-- Gro -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Gro</title>\n",
       "<ellipse fill=\"#7a81ff\" stroke=\"#7a81ff\" cx=\"122.84\" cy=\"-90\" rx=\"122.68\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"122.84\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">γ_HashBased[gender, count(*)]</text>\n",
       "</g>\n",
       "<!-- IRel -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>IRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"122.84\" cy=\"-18\" rx=\"31.7\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"122.84\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">actors</text>\n",
       "</g>\n",
       "<!-- IRel&#45;&gt;Gro -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>IRel&#45;&gt;Gro</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M122.84,-36.3C122.84,-44.02 122.84,-53.29 122.84,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"119.34,-61.9 122.84,-71.9 126.34,-61.9 119.34,-61.9\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x12466d100>"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = grouping.get_graph()\n",
    "graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#actors.relation.print_set()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouping2 = Grouping_HashBased(actors, 'first_name,last_name', 'count(*)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(γ_[first_name, last_name, count(*)] (actors)\n"
     ]
    }
   ],
   "source": [
    "print(grouping2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "------\n",
       "\u001b[1mResult\u001b[0m \n",
       "---------------------------------------------------------------------\n",
       "\u001b[1mfirst_name             last_name              count_star             \u001b[0m \n",
       "---------------------------------------------------------------------\n",
       "Joanna                 Barnes                 1                      \n",
       "Anders (I)             Falk                   1                      \n",
       "Liam                   Tuohy                  1                      \n",
       "Jerry                  Jarret                 1                      \n",
       "James (IV)             Allen                  1                      \n",
       "Nigel                  Goulding               1                      \n",
       "J. Kenneth             Campbell               1                      \n",
       "Fred                   Bell                   1                      \n",
       "Phil                   Stevenson              1                      \n",
       "Vince                  Edwards                1                      \n",
       "\n",
       "WARNING: skipping 1081 out of 1091 tuples..."
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouping2.evaluate()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Idea\n",
    "* group by year\n",
    "* count the number of movies per year, count(*)\n",
    "* sum up the movie ids per year, sum(id)\n",
    "* compute average movie id per year, avg(id)\n",
    "\n",
    "The latter two do not make sense but show that multiple aggregations can be computed in one grouping."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouping = Grouping_HashBased(movies, 'year', 'count(*), sum(id), avg(id)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "------\n",
       "\u001b[1mResult\u001b[0m \n",
       "--------------------------------------------------------------------------------\n",
       "\u001b[1myear                count_star          sum_id              avg_id              \u001b[0m \n",
       "--------------------------------------------------------------------------------\n",
       "1995                1                   118367              118367.0            \n",
       "1996                1                   322652              322652.0            \n",
       "1964                1                   92616               92616.0             \n",
       "1991                1                   328277              328277.0            \n",
       "2005                1                   10934               10934.0             \n",
       "2000                1                   385710              385710.0            \n",
       "1955                1                   176891              176891.0            \n",
       "1999                1                   106666              106666.0            \n",
       "2001                1                   96779               96779.0             \n",
       "1962                1                   193519              193519.0            \n",
       "\n",
       "WARNING: skipping 22 out of 32 tuples..."
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouping.evaluate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.47.2 (20210527.0053)\n",
       " -->\n",
       "<!-- Pages: 1 -->\n",
       "<svg width=\"362pt\" height=\"116pt\"\n",
       " viewBox=\"0.00 0.00 361.57 116.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 112)\">\n",
       "<polygon fill=\"white\" stroke=\"transparent\" points=\"-4,4 -4,-112 357.57,-112 357.57,4 -4,4\"/>\n",
       "<!-- Gro -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>Gro</title>\n",
       "<ellipse fill=\"#7a81ff\" stroke=\"#7a81ff\" cx=\"176.78\" cy=\"-90\" rx=\"176.57\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"176.78\" y=\"-86.3\" font-family=\"Times,serif\" font-size=\"14.00\">γ_HashBased[year, count(*), sum(id), avg(id)]</text>\n",
       "</g>\n",
       "<!-- IRel -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>IRel</title>\n",
       "<ellipse fill=\"none\" stroke=\"black\" cx=\"176.78\" cy=\"-18\" rx=\"36.29\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"176.78\" y=\"-14.3\" font-family=\"Times,serif\" font-size=\"14.00\">movies</text>\n",
       "</g>\n",
       "<!-- IRel&#45;&gt;Gro -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>IRel&#45;&gt;Gro</title>\n",
       "<path fill=\"none\" stroke=\"black\" d=\"M176.78,-36.3C176.78,-44.02 176.78,-53.29 176.78,-61.89\"/>\n",
       "<polygon fill=\"black\" stroke=\"black\" points=\"173.28,-61.9 176.78,-71.9 180.28,-61.9 173.28,-61.9\"/>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x1246932e0>"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph = grouping.get_graph()\n",
    "graph"
   ]
  }
 ],
 "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.5"
  },
  "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": 2
}