python-sqlite-4.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Over deze opdrachten\n",
    "\n",
    "* dit is Jupyter Notebook `python-sqlite-4.ipynb` (Schema)\n",
    "* voor een inleiding over het gebruik van Jupyter Notebooks: [Inleiding Jupyter Notebook](Inleiding-Jupyter.ipynb)\n",
    "* andere Python SQlite opdrachten:\n",
    "    * [Python SQLite - aanmaken database](python-sqlite-0.ipynb)\n",
    "    * [Python SQLite - select](python-sqlite-1.ipynb)\n",
    "    * [Python SQLite - joins](python-sqlite-2.ipynb)\n",
    "    * [Python SQLite - CRUD](python-sqlite-3.ipynb)\n",
    "    * [Python SQLite - Schema](python-sqlite-4.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overzicht\n",
    "\n",
    "In dit notebook behandelen we het *schema* van de voorbeeld-database.\n",
    "Het schema beschrijft de structuur van de database: de namen en types van tabellen en kolommen.\n",
    "Het schema beschrijft ook welke waarden in de database opgenomen kunnen worden.\n",
    "Via SQL-opdrachten kun je dit schema opvragen.\n",
    "\n",
    "Een schema kan ook *constraints* bevatten: eisen die aan de data in de database gesteld worden,\n",
    "zoals de uniciteit van de waarden in een kolom, of de \"referential integrity\" (zie verderop) van foreign keys.\n",
    "We geven voorbeelden van opdrachten om te demonstreren dat het DBMS opdrachten die deze constraints schenden niet uitvoert: we kunnen er dan zeker van zijn dat de waarden in de database aan deze constraints voldoen."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Initialisatie"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aanmaken van de database\n",
    "\n",
    "Zorg ervoor dat de database aangemaakt is. \n",
    "Gebruik hiervoor [Python SQLite - aanmaken database](python-sqlite-0.ipynb)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Database connection en cursor\n",
    "\n",
    "De eerste stap in het gebruik van database-opdrachten in Python is het maken van een verbinding (connection) met de database.\n",
    "Met behulp van deze connection maak je een cursor-object, waarmee je de database-queries kunt uitvoeren.\n",
    "\n",
    "In dit geval zorgen we er ook voor dat de controle op \"referential integrity\" van foreign keys ingeschakeld is. (Voor SQLite zijn deze default uitgeschakeld; voor MySQL is de default 1 (ON).)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "db = sqlite3.connect('example.db')\n",
    "cursor = db.cursor()\n",
    "cursor.execute('''PRAGMA foreign_keys = ON;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Test \n",
    "\n",
    "We gebruiken het volgende als test voor de verbinding, en voor de inhoud van de database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''SELECT * FROM leden;''')\n",
    "for row in cursor:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Schema\n",
    "\n",
    "Het *schema* van een database beschrijft de structuur: de tabellen, kolomnamen, types en constraints.\n",
    "Het schema van een database is in het DBMS (hier: SQLite) opgeslagen, in een aparte database (`sqlite_master`).\n",
    "Een schema kun je bijvoorbeeld weergeven in de vorm van de \"create table\" opdrachten.\n",
    "In het geval van SQLite kun je deze opvragen met de `.schema`-opdracht."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    ".databases\n",
    ".schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Schema opvragen via SQL\n",
    "\n",
    "### Alle tabellen\n",
    "\n",
    "Met de volgende SQL-query vraag je (in SQLite) alle tabellen van de huidige database op:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''SELECT name FROM sqlite_master WHERE type='table';''')\n",
    "for row in cursor:\n",
    "    print(row[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Schema van een tabel\n",
    "\n",
    "Het schema (de structuur) van een tabel vraag je als volgt op in SQL:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''SELECT sql FROM sqlite_master WHERE name='leden';''')\n",
    "for row in cursor:\n",
    "    print(row[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Voor de kolomnamen van de sqlite_master tabel, zie: https://www.techonthenet.com/sqlite/sys_tables/index.php\n",
    "\n",
    "* type (table, index, trigger, view)\n",
    "* name\n",
    "* tbl_name\n",
    "* rootpage\n",
    "* sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Opdracht\n",
    "\n",
    "Vraag op dezelfde manier het schema van de tabel `inschrijvingen` op."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Opdracht\n",
    "\n",
    "Vergelijk het database-schema met de onderstaande figuur van het logische model:\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Constraints\n",
    "\n",
    "Het schema van een database bepaalt welke gegevens in de database opgenomen kunnen worden.\n",
    "De constraints in het schema geven verdere eisen aan deze gegevens.\n",
    "Voorbeelden van constraints in de voorbeeld-database:\n",
    "\n",
    "* de voornaam en de achternaam mogen niet leeg (NULL) zijn;\n",
    "* de email-waarde van een lid moet uniek zijn;\n",
    "* de combinatie (datum, beschrijving) van een event moet uniek zijn;\n",
    "* de verwijzingen (*foreign keys*) in een inschrijving moeten verwijzen naar een bestaand lid en een bestaand event.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Opdracht\n",
    "\n",
    "In deze opdracht zie je hoe het DMBS probeert de constraints van het schema probeert te handhaven.\n",
    "\n",
    "(Zie de eerdere opdrachten in [Python SQLite - CRUD](python-sqlite-3.ipynb))\n",
    "\n",
    "Voer de volgende opdracht uit:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "donald = (\"Donald\", \"Duck\", \"donald@disney.com\")\n",
    "cursor.execute('''INSERT INTO leden (voornaam, achternaam, email) \n",
    "                  VALUES (?, ?, ?);''', \n",
    "                  donald)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Voer de bovenstaande opdracht *nog een keer* uit.\n",
    "Welke foutmelding krijg je in dit geval?\n",
    "Welke constraint betreft dit?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Opdracht\n",
    "\n",
    "* maak een opdracht om een tweede lid met de naam \"Donald Duck\" aan de tabel toe te voegen, met een ander email-adres;\n",
    "* heeft dat zin in de praktijk?\n",
    "* voer de opdracht uit; en laat zien dat er inderdaad meerdere leden met die naam zijn."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''SELECT * FROM leden;''')\n",
    "for row in cursor:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ruim je experimentele gegevens op: verwijder alle leden met de naam Donald Duck:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''DELETE FROM leden\n",
    "                  WHERE voornaam = 'Donald' AND achternaam = 'Duck' ;''')\n",
    "cursor.execute('''SELECT * FROM leden;''')\n",
    "for row in cursor:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Toevoegen van een inschrijving\n",
    "\n",
    "### Referential integrity \n",
    "\n",
    "In de volgende voorbeelden proberen we opdrachten uit te voeren die de \"referential integrity\" van de *foreign keys* dreigen te verstoren.\n",
    "\n",
    "> Een *foreign key* is een *key* die verwijst naar een rij in een andere (doel)tabel. Voorbeelden hiervan zijn `eventnr` en `lidnr` in de `inschrijvingen`-tabel. We moeten garanderen dat elke rij waar een foreign key naar verwijst inderdaad bestaat in de doel-tabel: dit heet *referential integrity*. Het DBMS moet dit controleren bij het toevoegen van een verwijzing (hier: een *inschrijving*), en bij het verwijderen van rijen waarnaar verwezen kan worden (zoals een `lid` of een `event`). Ook bij het veranderen van een foreign key moet dit gecontroleerd worden.\n",
    "\n",
    "### Check aan in SQLite?\n",
    "\n",
    "We controleren eerst of de (SQLite) database juist ingesteld is voor het controleren van de *foreign key constraints*.\n",
    "In het geval van MySQL staat die controle altijd aan.\n",
    "Het resultaat van de onderstaande opdracht moet zijn `(1,)`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''PRAGMA foreign_keys;''')\n",
    "for row in cursor:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Toevoegen van inschrijving voor niet-bestaand event\n",
    "\n",
    "Als eerste proberem we een inschrijving voor een niet-bestaande event toe te voegen.\n",
    "\n",
    "**Opdracht** Voer de onderstaande cel uit.\n",
    "\n",
    "* welke foutmelding krijg je?\n",
    "* verander de `22` in `2`; welke foutmelding krijg je dan?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inschrijving = (22, 1, 'maaltijd A')\n",
    "cursor.execute('''INSERT INTO inschrijvingen (eventnr, lidnr, maaltijd)\n",
    "                  VALUES (?, ?, ?);''',\n",
    "                  inschrijving)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Verwijderen van een event met inschrijvingen\n",
    "\n",
    "Een volgende poging is om een rij te verwijderen waar een foreign key van een andere tabel naar verwijst.\n",
    "Als voorbeeld gebruiken we een event waarvoor inschrijvingen bestaan.\n",
    "\n",
    "Eerst een overzicht van de inschrijvingen:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''SELECT eventnr, lidnr, maaltijd FROM inschrijvingen;''')\n",
    "for row in cursor:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Opdracht** Voer de volgende cel uit, voor het verwijderen van een event.\n",
    "\n",
    "* Welke foutmelding krijg je?\n",
    "* Ga na welke persoon niet gebruikt wordt in een inschrijving (via de cel daaronder).\n",
    "* Verander de `x` in de daarop volgende cel door het nummer van een niet-gerefereerde event, en voer de cel uit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''DELETE FROM events\n",
    "                  WHERE eventnr = 4 ;''')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Opdracht**\n",
    "\n",
    "* Pas de `x` in de cel hieronder aan zodat je een lid probeert te verwijderen waarvoor een inschrijving bestaat, en voer de cel uit;\n",
    "* Welke foutmelding krijg je?\n",
    "* Verander de `x` in het nummer van een lid zonder inschrijvingen, en voer de opdracht uit;\n",
    "* Maak ter controle een overzicht van de leden."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''DELETE FROM leden\n",
    "                  WHERE lidnr = x ;''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Het volgende voorbeeld is een SQL-demonstratie.\n",
    "\n",
    "**Voorbeeld:** een SQL-query om de leden zonder inschrijvingen op te vragen.\n",
    "Geef hiervan de voornaam, achternaam en email-adres."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute('''SELECT *\n",
    "                  FROM leden lid\n",
    "                  WHERE lid.lidnr NOT IN \n",
    "                  (SELECT ins.lidnr FROM inschrijvingen ins);''')\n",
    "for row in cursor:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}