sqlite-0.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Over deze opdrachten\n",
    "\n",
    "* dit is Jupyter Notebook `sqlite-0.ipynb` - voor het aanmaken van de database.\n",
    "* voor een inleiding over het gebruik van Jupyter Notebooks: [Inleiding Jupyter Notebook](Inleiding-Jupyter.ipynb)\n",
    "* de hele reeks SQLite opdrachten:\n",
    "    * [SQLite 0 - init database](sqlite-0.ipynb) (om met een schone lei te beginnnen)\n",
    "    * [SQLite 1 - selectie en projectie](sqlite-1.ipynb)    \n",
    "    * [SQLite 2 - joins](sqlite-2.ipynb)\n",
    "    * [SQLite 3 - CRUD](sqlite-3.ipynb)\n",
    "    * [SQLite 4 - Schema](sqlite-4.ipynb)\n",
    "\n",
    "### Voorbeeld\n",
    " \n",
    "Bij deze opdrachten gebruiken we een voorbeeld-database met drie tabellen: `leden`, `inschrijvingen`, en `events`.\n",
    "Deze database komt uit een webtoepassing; deze vind je op glitch.com. REF\n",
    "Daar kun je de toepassing bekijken, uitproberen, en er een eigen versie (\"remix\") van maken."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aanmaken van de database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In de volgende opdrachten voer je allerlei queries uit op een database.\n",
    "Je moeten dan eerst wel een database met inhoud hebben.\n",
    "Met de onderstaande opdrachten maak je deze database.\n",
    "Deze opdrachten hoef je maar één keer uit te voeren: de database blijft bestaan, met je veranderingen.\n",
    "Je kunt deze opdrachten later ook uitvoeren om opnieuw te beginnen in een goed-gedefinieerde toestand."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We maken de tabel(len) aan.\n",
    "We verwijderen eerst een eventueel bestaande versie van de tabel(len):\n",
    "we hebben dan een goed gedefinieerde toestand.\n",
    "\n",
    "> Opmerking: er zijn kleine verschillen in de notatie van de constraints bij het aanmaken van een tabel; MySQL gebruikt bijvoorbeeld een andere notatie dan Oracle."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Eerste tabel: leden\n",
    "\n",
    "De opdracht bestaat uit de volgende onderdelen:\n",
    "\n",
    "1. het opstarten van `sqlite` (de eerste twee regels). Hierna kunnen we SQL opdrachten geven;\n",
    "2. het verwijderen van de `leden`-tabel als deze bestaat (`DROP TABLE`);\n",
    "3. het aanmaken van de `leden`-tabel (`CREATE TABLE`);\n",
    "4. het vullen van de tabel uit een csv-bestand (dit zijn geen SQL-opdrachten);\n",
    "5. een SQL-`SELECT`-opdracht om te controleren of de tabel inderdaad ingelezen is."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "DROP TABLE IF EXISTS leden;\n",
    "\n",
    "CREATE TABLE leden(\n",
    "  lidnr INTEGER PRIMARY KEY,\n",
    "  voornaam VARCHAR(255) NOT NULL,\n",
    "  achternaam VARCHAR(255) NOT NULL,\n",
    "  email VARCHAR(255) NOT NULL UNIQUE\n",
    ");\n",
    "\n",
    ".mode csv\n",
    ".import leden.csv leden\n",
    "\n",
    "SELECT lidnr, voornaam, achternaam, email\n",
    "FROM leden;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We hebben een voorbeeld-inhoud van de tabel(len) in csv-bestanden.\n",
    "Zo'n csv-bestand kun je gemakkelijk aanpassen in een teksteditor.\n",
    "Voor het importeren van een csv-bestand gebruiken we een speciale SQLite-opdracht."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tweede tabel: events\n",
    "\n",
    "De tabel `events` bevat de events waarvoor de leden kunnen inschrijven.\n",
    "Elk event heeft een datum en een beschrijving.\n",
    "\n",
    "Hiervoor volgen we hetzelfde patroon:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "DROP TABLE IF EXISTS events;\n",
    "\n",
    "CREATE TABLE events(\n",
    "  eventnr INTEGER, \n",
    "  datum VARCHAR(10) NOT NULL,\n",
    "  beschrijving VARCHAR(255),\n",
    "  PRIMARY KEY (eventnr),\n",
    "  CONSTRAINT name UNIQUE (datum, beschrijving)\n",
    ");\n",
    "\n",
    ".mode csv\n",
    ".import events.csv events\n",
    "\n",
    "SELECT eventnr, datum, beschrijving\n",
    "FROM events;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Derde tabel: inschrijvingen\n",
    "\n",
    "Deze tabel beschrijft een N-M relatie tussen leden en inschrijvingen.\n",
    "Naast de verwijzingen (via *foreign keys*) naar de andere tabellen vindt je hier de gegevens over de inschrijving (maaltijd-keuze)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "DROP TABLE IF EXISTS inschrijvingen;\n",
    "\n",
    "CREATE TABLE inschrijvingen(\n",
    "  eventnr INTEGER, \n",
    "  lidnr INTEGER,\n",
    "  maaltijd VARCHAR(255),\n",
    "  PRIMARY KEY (lidnr, eventnr),\n",
    "  FOREIGN KEY (lidnr) REFERENCES leden (lidnr),\n",
    "  FOREIGN KEY (eventnr) REFERENCES events (eventnr)\n",
    ");\n",
    "\n",
    ".mode csv\n",
    ".import inschrijvingen.csv inschrijvingen\n",
    "\n",
    "SELECT eventnr, lidnr, maaltijd\n",
    "FROM inschrijvingen;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Demonstratie: alle inschrijvingen\n",
    "\n",
    "Voor een overzicht van alle inschrijvingen met de gegevens van de leden en van de events gebruiken  we een join. \n",
    "Dit is een voorproefje - in een volgend notebook werken we dit verder uit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "sqlite3 example.db\n",
    "\n",
    "SELECT evt.datum\n",
    ",      evt.beschrijving\n",
    ",      lid.voornaam\n",
    ",      lid.achternaam\n",
    ",      lid.email\n",
    ",      ins.maaltijd\n",
    "FROM leden lid, events evt, inschrijvingen ins\n",
    "WHERE lid.lidnr=ins.lidnr AND evt.eventnr=ins.eventnr;"
   ]
  }
 ],
 "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
}