{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL 1/3\n",
    "> Cours NSI Terminale - Thème 2.\n",
    "- toc: true \n",
    "- badges: true\n",
    "- comments: false\n",
    "- categories: [NSI, Terminale, Bases de données, SQL, TP]\n",
    "- image: /images/nsi2.png"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "cee8e933b07ec562a7ac002fbe442c68",
     "grade": false,
     "grade_id": "cell-731e862b0a98531c",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "# Découverte du langage SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "0c50cf013b8b3f94879cbc1d2d8520b7",
     "grade": false,
     "grade_id": "cell-916b8b114b6f2c6f",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "Dans ce TP jous allons découvrir le langage SQL (*Structured Query Language*) qui est le langage utilisé pour\n",
    "effectuer des requêtes sur une base de données relationnelles. Nous apprendrons lors de ce TP à\n",
    "- créer des tables avec les attributs que l'on souhaite\n",
    "- insérer des enregistrements\n",
    "- faire des requêtes sur la base pour extraire des informations\n",
    "\n",
    "Contrairement aux apparences, nous n'utiliserons pas Python dans ce classeur, mais directement SQL grâce à l'extension **ipython-sql** qu'il faut activer en validant la cellule suivante :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "7b53d2abf3df2a5b614bebe70957ced4",
     "grade": false,
     "grade_id": "cell-390143509df6c8a7",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "71ca2b4c445c28e31f6b73fff7535d94",
     "grade": false,
     "grade_id": "cell-3e66312a97ed9a24",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "En cas d'erreurs lors du chargement de l'extension, vous pouvez tenter de réinstaller les paquets nécessaires via les commandes\n",
    " ```bash\n",
    "pip install jupyter-sql\n",
    "pip install ipython-sql\n",
    "sudo apt install python3-sql\n",
    "```\n",
    "puis relancer jupyter.\n",
    "\n",
    "## Créer la base de données\n",
    "\n",
    "Nous allons commencer par créer une base vide dans laquelle nous allons travailler :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "07c0881634a3b7bdbc718a3fa27ea520",
     "grade": false,
     "grade_id": "cell-44a1fca04afad327",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "%sql sqlite:///livres_db"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "634a0c21c22dc0dcbc38b2ee44cb47e5",
     "grade": false,
     "grade_id": "cell-658db7b8f11cf54e",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "La base s'appelle **livres_db** et est au format *sqlite* qui est un gestionnaire de base de données relationnelles léger et facile à prendre en main.\n",
    "\n",
    "Les données seront inscrites dans le fichier **livres_db** qui vient d'être créé à côté de ce classeur et que vous pourrez télécharger quand vous aurez fini ce TP.\n",
    "\n",
    "L'objectif est de peupler cette base de données avec la base **livres** que l'on a étudié lors de la découverte du modèle relationnel. Cette base sera constituée de 4 tables :\n",
    "- Auteurs\n",
    "- Livres\n",
    "- Langues\n",
    "- Themes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "44cc9cec5e67a988dac3172541dea1c3",
     "grade": false,
     "grade_id": "cell-d3b96ced193ac2d1",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "## Créer une table\n",
    "\n",
    "Il est temps de commencer à peupler notre base de données. Nous allons commencer par créer la table **Langues** en saisissant notre première requête :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "fd707a29b215463f1b361b58ff49f8e9",
     "grade": false,
     "grade_id": "cell-39ce5b283f7d4825",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "CREATE TABLE Langues \n",
    "(\n",
    "IdLangue INTEGER  PRIMARY KEY,\n",
    "Langue   TEXT\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "8d82bd9c56dd9d14429e0ffc1d5e6b77",
     "grade": false,
     "grade_id": "cell-20d4b7cc5439a4fe",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "### Quelques explications :\n",
    "\n",
    "Pour commencer, dans jupyter lorsque nous voulons taper une commande **SQL** et non du langage python. Pour cela, nous inscrirons **en première ligne de cellule** la commande *magique* `%%sql`. N'oubliez jamais de commencer toutes vos cellules ainsi car sinon, la commande sera interprétée par *python* et non *SQL*.\n",
    "\n",
    "La première requête **SQL** que nous allons apprendre est la requête `CREATE TABLE`\n",
    "- on indique le nom de la table à créer\n",
    "- entre parenthèse on liste les *attributs* à mettre ainsi que leur *type*.\n",
    "- une requête se termine **toujours** par ;\n",
    "\n",
    "Nous avons deux types différents dans notre base de données :\n",
    "- le type TEXT pour tout ce qui est chaîne de caractères\n",
    "- le type INTEGER pour les entiers\n",
    "\n",
    "L'attribut **IdLangue** est la *clé primaire* de la table. C'est un entier qui commence à 1 et qui sera automatiquement incrémenté au fur à mesure que l'on insère des données dans la table. C'est en indiquant `PRIMARY KEY` après le type dans la déclaration de l'attribut **IdLangue** que *sqlite* se comporte ainsi."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "d53eadd2bf962b91b6dfb781050b79e7",
     "grade": false,
     "grade_id": "cell-29801329779f4f15",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "## Insérer des enregistrements dans la table\n",
    "\n",
    "Maintenant que nous avons une table vide, il nous faut la remplir avec les données sur les auteurs. Nous utiliserons pour cela la requête **INSERT**. Voici son utilisation :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "2e09d8eb35ce5c6d800820f4ee0cf48f",
     "grade": false,
     "grade_id": "cell-e21f4409fe9aadc0",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO Langues \n",
    "    (Langue)\n",
    "VALUES\n",
    "    (\"Anglais\"),\n",
    "    (\"Français\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "9a43c4cd33e5413e68dd112b347ca7fa",
     "grade": false,
     "grade_id": "cell-01b63250bc7b5d4a",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "### Quelques explications :\n",
    "\n",
    "La requête **INSERT** s'utilise ainsi :\n",
    "```\n",
    "INSERT INTO ##TABLE##\n",
    "    (## attributs dont on donne les valeurs##)\n",
    "VALUES\n",
    "    (## enregistrement 1 ##),\n",
    "    ...\n",
    "    (## enregistrement n ##);\n",
    "```\n",
    "\n",
    "On peut refaire une autre requête **INSERT** à la suite si on souhaite ajouter encore des données au bout de la table. \n",
    "\n",
    "Vous remarquez que l'on ne donne pas de valeur pour l'attribut **IdLangue**. C'est parce qu'on l'a déclaré en `PRIMARY KEY`. Il est donc automatiquement géré par sqlite. Nous verrons cela en lisant le contenu complet de la table.\n",
    "\n",
    "On est pas obligé de préciser tous les attributs. **IdLangue** est un exemple particulier, mais il est possible d'omettre d'autres attributs. Ils seront alors affectés d'une valeur nulle.\n",
    "\n",
    "A l'issue de la requête, sqlite nous informe que 2 lignes ont été créées."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "13a272e33c8c1f0c4f6b3781873da0f9",
     "grade": false,
     "grade_id": "cell-86faa650596b82e0",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "## Lire le contenu d'une table\n",
    "\n",
    "Nous allons à présent utiliser une requête **SELECT** afin de récupérer le contenu de la table. Ces requêtes peuvent être très sophistiquées comme on va le voir en fin de TP. Pour le moment, nous nous contenterons de la forme la plus simple :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "dd079d45222502ed71e93d80e5f70cf7",
     "grade": false,
     "grade_id": "cell-83bd3a93a2cdf04b",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM Langues;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "38340cd72e532062b558394ee734d587",
     "grade": false,
     "grade_id": "cell-41c695377db1a2fc",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "Vous voyez donc appraître le contenu de la table. Vous constatez que la clé primaire **IdLangue** a bien été générée correctement.\n",
    "\n",
    "Il est possible de stocker le résultat de cette requête dans une variable pour l'exploiter plus facilement dans jupyter. Voici comment procéder en modifiant légèrement la première ligne :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "0a6ee97c4ac9f9007f306dfdb8f4b346",
     "grade": false,
     "grade_id": "cell-f237c83ff2656a96",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "resultat = %sql SELECT * FROM Langues;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "012a1deb7bb8be3234095f6bd64cc233",
     "grade": false,
     "grade_id": "cell-f7f1c166af4f90e9",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "Vous voyez au passage la syntaxe concise permettant de récupérer le résultat d'une requête dans une variable. Cette variable `résultat` est exploitable dans ce classeur, y compris par python !!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "resultat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(resultat)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "resultat[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "b164c4f6f7aadde6681f2cc578b6f1cf",
     "grade": false,
     "grade_id": "cell-cc70d278076413c5",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "On a donc ici le meilleur des deux mondes : des requêtes **SQL** et une base de données pour stocker efficacement les données, le langage python pour traiter ces données grâce à des algorithmes faciles à écrire."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "5ec48c34fd7b2477647da212de79490b",
     "grade": false,
     "grade_id": "cell-73aa64b758698af0",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "## A vous de jouer\n",
    "\n",
    "Créez la table Auteurs afin que celle-ci reflète les informations suivantes :\n",
    "\n",
    "|   Nom    |    Prenom    | annee naissance |  langue  |\n",
    "|----------|--------------|-----------|----------|\n",
    "|  Orwell  |    George    |    1903   | Anglais  |\n",
    "| Herbert  |    Frank     |    1920   | Anglais  | \n",
    "|  Asimov  |    Isaac     |    1920   | Anglais  |\n",
    "|  Huxley  |    Aldous    |    1894   | Anglais  |  \n",
    "| Bradbury |     Ray      |    1920   | Anglais  | \n",
    "| K. Dick  |    Philip    |    1928   | Anglais  |  \n",
    "| Barjavel |     René     |    1911   | Français | \n",
    "|  Boulle  |    Pierre    |    1912   | Français |  \n",
    "| Van Vogt | Alfred Elton |    1912   | Anglais  | \n",
    "|  Verne   |    Jules     |    1828   | Français |  \n",
    "\n",
    "Pour cela, vous utiliserez la commande suivante pour créer la table et vous adapterez les données à cette structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "3b54666f72f91d709a3374a0310c83ff",
     "grade": false,
     "grade_id": "cell-ee0396e996089856",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "CREATE TABLE Auteurs (\n",
    "    IdAuteur       INTEGER  PRIMARY KEY,\n",
    "    NomAuteur      TEXT,\n",
    "    PrenomAuteur   TEXT,\n",
    "    IdLangue       INTEGER,\n",
    "    AnneeNaissance INTEGER,\n",
    "    FOREIGN KEY(IdLangue) REFERENCES Langues(IdLangue)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Une nouveauté apparaît ici dans la création de la table : La table **Auteurs** possède une *clé étangère* : *IdLangue*. Cette clé est un entier.\n",
    "\n",
    "Remarquez la ligne `FOREIGN KEY(IdLangue) REFERENCES Langues(IdLangue)`. Celle-ci permet de déclarer une *contrainte* sur cette clé afin d'indiquer à SQLite que *IdLangue* est une clé étrangère. SQLite sera alors responsable de maintenir la cohérence entre les deux tables que l'on a ainsi reliée.\n",
    "\n",
    "Cette déclaration n'est pas obligatoire mais fortement conseillée."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "d27157fae880b21956e2d8a6e3a35890",
     "grade": false,
     "grade_id": "cell-b18fd314917d7ddf",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "/* \n",
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()\n",
    "*/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "7ee8c8df7d5d97eb37ceabc3bdb11397",
     "grade": false,
     "grade_id": "cell-f48b620f9d01140f",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "## A vous de jouer\n",
    "\n",
    "Notre base n'est pas encore complète : il nous reste à créer les tables **Livres** et **Themes** qui doivent refléter le contenu suivant :\n",
    "\n",
    "|           Titre           |   NomAuteur    |    PrenomAuteur    | AnneeNaissance |  Langue  | AnneePubli | Themes |\n",
    "|---------------------------|----------|--------------|-----------|----------|-----------|------|\n",
    "|            1984           |  Orwell  |    George    |    1903   | Anglais  |    1949   |  Totalitarisme, science-fiction, anticipation, Dystopie  |\n",
    "|            Dune           | Herbert  |    Frank     |    1920   | Anglais  |    1965   | science-fiction, anticipation   |\n",
    "|         Fondation         |  Asimov  |    Isaac     |    1920   | Anglais  |    1951   |  science-fiction, Economie  |\n",
    "|   Le meilleur des mondes  |  Huxley  |    Aldous    |    1894   | Anglais  |    1931   |  Totalitarisme, science fiction, dystopie   |\n",
    "|       Fahrenheit 451      | Bradbury |     Ray      |    1920   | Anglais  |    1953   |   \tscience-fiction, Dystopie  |\n",
    "|            Ubik           | K. Dick  |    Philip    |    1928   | Anglais  |    1969   |  \tscience-fiction, anticipation   |\n",
    "|   Chroniques martiennes   | Bradbury |     Ray      |    1920   | Anglais  |    1950   |   \tscience-fiction, anticipation   |\n",
    "|     La nuit des temps     | Barjavel |     René     |    1911   | Français |    1968   |   \tscience-fiction, tragédie   |\n",
    "|        Blade Runner       | K. Dick  |    Philip    |    1928   | Anglais  |    1968   |   \tIntelligence artificielle, science fiction  |\n",
    "|         Les Robots        |  Asimov  |    Isaac     |    1920   | Anglais  |    1950   |  science fiction, Intelligence artificielle   |\n",
    "|   La Planète des singes   |  Boulle  |    Pierre    |    1912   | Français |    1963   |   \tscience fiction, Dystopie   |\n",
    "|           Ravage          | Barjavel |     René     |    1911   | Français |    1943   |  Science-Fiction, anticipation   |\n",
    "| Le Maître du Haut Château | K. Dick  |    Philip    |    1928   | Anglais  |    1962   |   \tDystopie, Uchronie  |\n",
    "|       Le monde des A      | Van Vogt | Alfred Elton |    1912   | Anglais  |    1945   |   \tscience fiction, IA   |\n",
    "|    La Fin de l’éternité   |  Asimov  |    Isaac     |    1920   | Anglais  |    1955   |  science-fiction, voyage dans le temps  |\n",
    "|   De la Terre à la Lune   |  Verne   |    Jules     |    1828   | Français |    1865   |   \tScience-Fiction, aventure  |\n",
    "\n",
    "### La table LIVRES\n",
    "\n",
    "La table **LIVRES** devra avoir la structure décrite dans l'extrait suivant :\n",
    "\n",
    "| IdLivre |           Titre           | IdAuteur | AnneePubli |\n",
    "|----|---------------------------|-----------|-----------|\n",
    "| ...  |            ...          |     ...     |    ...   |\n",
    "| 8  |     La nuit des temps     |     7     |    1968   |\n",
    "| ...  |            ...          |     ...     |    ...   |\n",
    "\n",
    "- l'année de publication est de type **INTEGER**\n",
    "- **IdLivre** désigne bien sûr la clé primaire\n",
    "- **IdAuteur** est une *clé externe* faisant référence à l'auteur.\n",
    "- dans l'extrait, la clé **IdAuteur** vaut 7. L'auteur de *La nuit des temps* est donc *Barjavel*\n",
    "- on ne renseigne pas la langue ou l'année de naissance de l'auteur car ces informations sont déjà présentes dans la table **Auteurs**.\n",
    "- On traitera la problématique des thèmes plus tard...\n",
    "\n",
    "Dans la cellule ci-dessous, saisissez la requête pour créer la table **Livres**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "a9671cc6c3eff1e271ab8033345b2c1f",
     "grade": false,
     "grade_id": "cell-cb6e3a0ab598131d",
     "locked": false,
     "schema_version": 3,
     "solution": true
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "/* \n",
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()\n",
    "*/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "ace01b4ae1b5a0a683103aacf4369fc9",
     "grade": false,
     "grade_id": "cell-4c791e843c936517",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "A présent, saisissez les données à l'intérieur de la table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "ca56dcf3bc9ba009248de78993356da1",
     "grade": false,
     "grade_id": "cell-4255de3f9026e4bc",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "/* \n",
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()\n",
    "*/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "544b30b7602b8edcec60d709c69bbdae",
     "grade": false,
     "grade_id": "cell-16f0fe25a834d091",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "Vérifiez votre travail en listant tous les enregistrements de la table **LIVRES** dans la variable `resultat`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "6086e214b05330fd4406fe8e588aaba2",
     "grade": false,
     "grade_id": "cell-1d7d786452feb004",
     "locked": false,
     "schema_version": 3,
     "solution": true
    }
   },
   "outputs": [],
   "source": [
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()\n",
    "resultat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "51e933a7d36f9535376f2176e85b59a4",
     "grade": true,
     "grade_id": "cell-bcf2fb5cae26d98e",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "assert (1, '1984', 1, 1949) in resultat"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "3c55700fb847b81b0687b5ba84fdd295",
     "grade": false,
     "grade_id": "cell-1bfbd2a02c2a73e6",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "### La table Themes\n",
    "\n",
    "Traitons à présent la problématique des Thèmes. La table Themes devra avoir la structure décrite dans l'extrait suivant :\n",
    "\n",
    "| IdTheme | Intitule |\n",
    "|----|----------|\n",
    "| 1 |    Science-fiction     |\n",
    "| ...  |   ...     |\n",
    "\n",
    "- **IdTheme** désigne bien sûr la clé primaire\n",
    "- **Intitule** est un champ texte contenant l'intitulé du thème tel qu'il apparaît dans le tableau global.\n",
    "\n",
    "Dans la cellule ci-dessous, vous saisirez donc 2 requêtes :\n",
    "- Une pour créer la table Themes\n",
    "- Une pour insérer les données dans la table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "95f60ae911038ca5839e1012f6139ce8",
     "grade": false,
     "grade_id": "cell-46cc53689e60030a",
     "locked": false,
     "schema_version": 3,
     "solution": true
    }
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "/* \n",
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()\n",
    "*/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "8649fa0556d810f75a96648aaf460d8b",
     "grade": false,
     "grade_id": "cell-6ed462a3bc3f7cf1",
     "locked": true,
     "schema_version": 3,
     "solution": false
    }
   },
   "source": [
    "Vérifiez votre travail en lisant tous les enregistrements de la table **Themes** dans la variable `resultat`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "1ccf1b78c798f69caed489c03c33e61c",
     "grade": false,
     "grade_id": "cell-cc2f33781006ca1b",
     "locked": false,
     "schema_version": 3,
     "solution": true
    }
   },
   "outputs": [],
   "source": [
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()\n",
    "resultat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "a7660b7df6a87065076a58f0c266bd06",
     "grade": true,
     "grade_id": "cell-8676060641ad67a1",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false
    }
   },
   "outputs": [],
   "source": [
    "assert (1, \"Science-fiction\") in resultat"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "c8f90ff54ecebfbf6fd2f70a80da885a",
     "grade": false,
     "grade_id": "cell-c9f2379a7a570cf1",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "### Une table manquante !\n",
    "\n",
    "La saisie de notre base de donnée est incomplète ! Nous avons en effet saisi tous les auteurs, tous les livres, toutes les langues, tous les thèmes et pourtant il manque une information. Laquelle ?\n",
    "\n",
    "Quelle solution envisager pour saisir cette information ?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "35920ca28e0c043b088f7933c2bc4562",
     "grade": false,
     "grade_id": "cell-49ca5f4f9daea1e2",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "Créer une table **RelationsLivreTheme** mettant en relation les livres et les thèmes associés. Saisir le contenu de cette table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "05eda678e52fb463da94f1287e8e79a7",
     "grade": false,
     "grade_id": "cell-8cff1f377323d3c6",
     "locked": false,
     "schema_version": 3,
     "solution": true,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "%%sql \n",
    "\n",
    "/* \n",
    "# YOUR CODE HERE\n",
    "raise NotImplementedError()\n",
    "*/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "code",
     "checksum": "d0801a9959664adfc595a6f5d73366c9",
     "grade": true,
     "grade_id": "cell-dc78f23fda30d6f2",
     "locked": true,
     "points": 1,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "outputs": [],
   "source": [
    "resultat = %sql SELECT IdLivre, IdTheme FROM RelationsLivreTheme;\n",
    "assert (1, 1) in resultat"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": false,
    "editable": false,
    "nbgrader": {
     "cell_type": "markdown",
     "checksum": "0e48de42665f66bc3fe84ab2db660519",
     "grade": false,
     "grade_id": "cell-97be65bb7b284e5a",
     "locked": true,
     "schema_version": 3,
     "solution": false,
     "task": false
    }
   },
   "source": [
    "## Cardinalité\n",
    "\n",
    "En regardant notre base de données et les relations que nous avons créé entre les tables, on remarque que celles-ci ont des *cardinalités* différentes. La *cardinalité* d'une relation entre deux tables **A** et **B** exprime à combien d'enregistrements de **A** peut être relié chaque enregistrement de **B**.\n",
    "\n",
    "Par exemple, à un livre est associé un auteur unique, mais pour un auteur donné, il peut y avoir plusieurs livres. On parle alors de **relation de 1 à n**\n",
    "\n",
    "Lorsque plusieurs enregistrements de la table **A** peuvent être associés à plusieurs enregistrements de la table **B**, on parle alors d'une **relation de n à n**\n",
    "\n",
    "### A vous de jouer\n",
    "\n",
    "Citez dans la base de données\n",
    "- une relation de 1 à n\n",
    "- une relation de n à n\n",
    "\n",
    "### Table de relation\n",
    "\n",
    "Pour une **relation de n à n**, nous aurons en général recours à la création d'une nouvelle table de relation contenant les clés externes des tables à mettre en relation. C'est ce que nous avons mis en oeuvre pour la table **RelationsLivreTheme**."
   ]
  }
 ],
 "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}