01-2-entity_integrity_check_constraints.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Entity Integrity and Check Constraints"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Amongst other requirements, three that are **vital** for a database are:\n",
    "\n",
    "  * **C**onfidentiality\n",
    "  * **I**ntegrity\n",
    "  * **A**vailability \n",
    "  \n",
    "these are shortly known as the CIA requirements.  \n",
    "\n",
    "If you lookup the meaning of the word _integrity_ in a dictionary amongst the definitions you will see \"internal consistency or lack of corruption in electronic data\".  You will also see synonyms such as \"scrupulousness, sincerity, truthfulness, trustworthiness\". \n",
    "\n",
    "There are several ways in which the integrity of a database can be compromised and equally several preventive measures to protect integrity.  In this notebook we will look at **entity integrity**, one of the simplest and foundational types of integrity, and **check constraints**.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Entity Integrity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_Entity Integrity_ simply means that no two records in the database can be identical in the values of all their fields.  The reason we desire this is to avoid ambiguity.  We need a way of uniquely refering to any record in a database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Load the SQL Jupyter notebook extension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%load_ext sql "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Connect to the PG database\n",
    "1. Ensure PG is running first :-)\n",
    "2. If you are using Windows you need to specify the password for the roll\n",
    "`%sql postgres://isdb16:PASSWORD@localhost/postgres`\n",
    "\n",
    "Since I use a Mac, my connection command below doesn't specify a password."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql postgres://isdb16@localhost/postgres "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Create a very simple table"
   ]
  },
  {
   "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",
    "   gpa  numeric(3,2)\n",
    "); "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Insert some sample records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "INSERT INTO Students (name, gpa)\n",
    "     VALUES ('Jack', 3.5),\n",
    "            ('Jill', 3.8);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM Students;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Insert couple more records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "INSERT INTO Students (name, gpa)\n",
    "     VALUES ('Bill', 3.5),\n",
    "            ('Pat', 3.8);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now if you look at the contents of the Students table we see all four records as expected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM Students;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### The execution of the following statement violates entity integrity i.e., we will have two records that are identical in the values of all their attributes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "INSERT INTO Students (name, gpa)\n",
    "     VALUES ('Jack', 3.5);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM Students;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### We obtain entity integrity by using PRIMARY KEYs.  \n",
    "\n",
    "Once we declare a particular attribute to be a primary key, SQL will flag Integrity Violation errors if we try to insert a record into the table with a pre-existing primary key.\n",
    "\n",
    "Lets drop the original table and re-create it, this time specifying a primary key.  There are a number of syntatic ways of specifying the primary key.  We will conform to the guidelines given in in the SQL style guide http://www.sqlstyle.guide/ .  \n",
    "\n",
    "Do spent time going over the style guide and familiarizing yourself with its recommendations.  We expect all SQL deliverables to conform to the guideline."
   ]
  },
  {
   "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",
    "   PRIMARY KEY(name), \n",
    "   name  text, \n",
    "   gpa   numeric(3,2)\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "INSERT INTO Students (name, gpa)\n",
    "     VALUES ('Jack', 3.5),\n",
    "            ('Jill', 3.8);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%sql SELECT * FROM Students;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Now, if we try to insert a new record with a pre-existing primary key we get an integrity violation error.\n",
    "\n",
    "**Note:** _Cells that have SQL code that will cause an error are in 'raw' mode.  To convert to code, go to command mode (press ESC) and then press 'y'  or go to the drop down menu at the top and select code. To switch a cell from 'code' mode to 'raw', press go to command mode (press ESC) and press 'r' or select 'RawNBConvert' from the drop down menu._\n",
    "\n",
    "Note that the error message is a bit long as we get two error messages (1) one from SQL and (2) one from the Python interface to SQL"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "%%sql\n",
    "--error\n",
    "\n",
    "INSERT INTO Students (name, gpa)\n",
    "     VALUES ('Jack', 3.9);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check Constraints"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the Students table we have an attribute for gpa which has been declared to be of type numeric with a total of three digits and two digits to the right of the decimal points.  Data types are our first line of defense against improper data being entered.  For example, we can not assign a string such as \"hi\" to the gpa attribute.\n",
    "\n",
    "_But_, at the moment nothing prevents us from entering GPA of 9.7, which business logic wise is not correct.  How can we ensure that values entered into the GPA field at potentially valid GPAs?  SQL provides _check constraints_ to guard against this type of integrity violation. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Lets go back to a clean slate by droping the Students table and creating it afresh, but whic time with a check constraint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We specify that values in the gpa field have got to be in [0,4].  Syntactically there are a couple of ways we can specify this.  To check if a value lies in a given range, it is usually conceptually more clear to use `between` as opposed to a conjunction."
   ]
  },
  {
   "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",
    "   PRIMARY KEY( name), \n",
    "   name text, \n",
    "   gpa numeric(3,2)\n",
    "       -- check( 0 <= gpa and gpa <= 4 )\n",
    "        check( gpa between 0 and 4)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets insert our example students, Jack ad Jill"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "INSERT INTO Students (name, gpa)\n",
    "     VALUES ('Jack', 3.5),\n",
    "            ('Jill', 3.8);\n",
    "        \n",
    "SELECT *\n",
    "  FROM Students;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Everything is fine.\n",
    "\n",
    "But if we now try to insert a student whose GPA is outside the interval [0,4] an integrity violation is flagged."
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "%%sql\n",
    "--error\n",
    "\n",
    "INSERT INTO Students (name, gpa)\n",
    "     VALUES ('Bill', 5.5);\n"
   ]
  }
 ],
 "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
}