02-1a-enumerated_data_types_exercise.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Enumerated Data Types and Row Assertions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In continuing our discussion of _integrity_, in this NB we look are two additional constructs provided by SQL.\n",
    "\n",
    "**The Problem:** We motivate the need for these two constructs with the following situation.  Suppose you have a table of `Students` with three attributes: `names, status (UG or G), gpa`.  You also have the domain constraint that undergraduate GPAs are in the range `[0,4]` where as graduate GPAs are in the range `[0,5]`. How can you ensure that when  student information is inserted in the table the domain constraints are not violated (i.e., integrity is not compromised)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Enumerated Data Types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we define a _data type_ we are defining two things (1) a set of values and (2) the set of operators possible on those set of values.  For example the integer data type is a set of values from (-2^31..0..2^31-1) and a set of arithmetic operators.  The data type with the smallest set of values is Boolean which has only two values.\n",
    "\n",
    "All languages provide a number of builtin data types and also provide facilities for defining quite sophisticated data structures. PostgreSQL  provides a very rich set of data types (above and beyond those required by the SQL:2011 standard.  You can read more about the builtin data types at https://www.postgresql.org/docs/current/static/datatype.html \n",
    "\n",
    "In the given problem, consider the attribute status.  In can only have two values `undergraduate` or `graduate`.  How can we ensure this constraint?\n",
    "\n",
    "We can achieve this in multiple three ways:\n",
    "1. Use check constraints\n",
    "2. Use an enumerated data type\n",
    "3. Use a foreign key\n",
    "\n",
    "We will look at the first two ways in this NB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load_ext sql\n",
    "\n",
    "# Windows users do specify your password\n",
    "%sql postgres://isdb16@localhost/postgres"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The reason we need to do a 'cascaded drop' at this point will be explained later in the course"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql DROP TABLE IF EXISTS Students CASCADE;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could define our Students table using `text` data types.  For simplicity we don't specify a primary key."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "CREATE TABLE Students (\n",
    "    name    text,\n",
    "    status  text,\n",
    "    gpa     numeric(3,2) default 0\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The obvious problem is that the `status` is unconstrainted and we could enter **any** text for `status` where as it should take on only one of two values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO Students (name, status)\n",
    "     VALUES ('Jack', 'part time');\n",
    "    \n",
    "select * from Students;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How can this be avoided?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Option 1: check constraints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS Students CASCADE;\n",
    "\n",
    "CREATE TABLE Students (\n",
    "    name    text,\n",
    "    status  text \n",
    "            check( status in ('undergraduate', 'graduate')),\n",
    "            -- ** come back here **\n",
    "    gpa     numeric(3,2) default 0\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The below inserts will work fine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "INSERT INTO Students (name, status, gpa)\n",
    "     VALUES ('Jack', 'undergraduate', 3.6),\n",
    "            ('Jill', 'graduate', 4.2);\n",
    "    \n",
    "SELECT * FROM Students;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But, if we try to insert a student with a different status, an exception will be raised.  The below cell is in 'raw' format and hence is not executable.  Convert to 'code' format to execute and then convert back to 'raw' format"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "--error\n",
    "\n",
    "INSERT INTO Students (name, status, gpa)\n",
    "     VALUES ('Bob', 'part time', 3.6);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Option 2: Enumerated Data Types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create our own data type by specifying the values it can take.  Details are at https://www.postgresql.org/docs/current/static/datatype-enum.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "DROP TYPE IF EXISTS Student_Status;\n",
    "CREATE TYPE Student_Status as enum ('undergraduate', 'graduate');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "DROP TABLE IF EXISTS Students CASCADE;\n",
    "\n",
    "CREATE TABLE Students (\n",
    "    name    text,\n",
    "    status  Student_Status, \n",
    "    gpa     numeric(3,2) default 0\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The below executes fine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "INSERT INTO Students (name, status, gpa)\n",
    "     VALUES ('Jack', 'undergraduate', 3.6),\n",
    "            ('Jill', 'graduate', 4.2);\n",
    "    \n",
    "SELECT * FROM Students;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "again, the below will be flagged"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "--error\n",
    "\n",
    "INSERT INTO Students (name, status, gpa)\n",
    "     VALUES ('Bob', 'part time', 3.6);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Option 3: Foreign Keys\n",
    "We will look at this option later."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question: what are the pros and cons of using check constraints vs. enumerated data types? \n",
    "[raja pg_enum, pg_type]\n",
    "--solution\n",
    "\n",
    "1. \n",
    "2. \n",
    "3. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lab Exercise \n",
    "\n",
    "_Suppose we have the restriction that the GPA of UG students can be in [0,4] where as the GPA for graduate students can be in [0,5]._ \n",
    "\n",
    "*How can we ensure these dual constraints on both the `status` and `gpa`? This is termed a tuple or row constraint. Start with the below code. Hint: use (a) the enumerated data type `Student_Status` and (b) an explicit `constraint` clause. If a student is inserted with out explicitly specifying a GPA enter a default GPA of 0.*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "DROP TABLE IF EXISTS Students;\n",
    "\n",
    "CREATE TABLE Students (\n",
    "    name    text,\n",
    "    status  text,\n",
    "    gpa     numeric(3,2)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Place your solution in the below cell.  Note that the cell is currently in 'raw' format"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "%%sql\n",
    "--SOLUTION\n",
    "\n",
    "DROP TABLE IF EXISTS Students CASCADE;\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After you created the newly constrained `Students` table, the following should execute fine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "INSERT INTO Students (name, gpa, status)\n",
    "     VALUES ('Jack', 3.3, 'undergraduate'),\n",
    "            ('Jill', 3.9, 'graduate');\n",
    "\n",
    "SELECT * FROM Students;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Suppose we insert Bill without specifying a GPA for Bill, we should be ok"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "INSERT INTO Students (name, status)\n",
    "     VALUES ('Bill', 'graduate');\n",
    "\n",
    "SELECT * FROM Students;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since Bill is a Graduate student, if we set Bill's GPA to 4.8 we should be fine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    " UPDATE Students\n",
    "    SET gpa = 4.8\n",
    "  WHERE name='Bill';\n",
    "\n",
    "SELECT * FROM students;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But if we now increment Bill's GPA to 5.8 an error should be raised"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "%%sql\n",
    "--error\n",
    "\n",
    " UPDATE Students\n",
    "    SET gpa = gpa + 1.0\n",
    "  WHERE name='Bill';\n",
    "\n",
    "SELECT * FROM students;"
   ]
  }
 ],
 "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.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}