TP3 : Tables et contraintes

Consignes

Création d'une table et contraintes de colonnes

1. Créez une table ayant pour colonnes :
   • : une clef primaire auto-increment.
   • : un texte dont la valeur par défaut sera .
   • : un entier qui ne peut être .
   • : un entier impair (utilisez ).
2. Via une requête SQL affichez le schéma de la table
3. Via une requête SQL affichez la requête SQL de création de la table
4. Insérez 3 entrées dans la table en ne renseignant que les colonnes et .
5. Affichez les entrées de la table .

Contraintes de tables

1. Créez une table ayant pour colonnes :
   • : un entier qui ne peut être .
   • : un entier qui doit être supérieur à

Clefs étrangères

⚠ Vous devrez activer les clefs étrangères via la commande

1. Créez une table ayant pour colonnes :
   • : une clef étrangère non-nulle vers la table .
2. Insérez une entrée dans référençant la dernière entrée de .
3. Incrémentez la colonne de l'entrée référencée. Que se passe-t-il ? Pourquoi ?
4. Supprimez l'entrée référencée. Que se passe-t-il ? Pourquoi ?
5. Supprimez la table .
6. Recommencez les opérations précédentes, cette fois de sorte à ce que la clef étrangères soit supprimée/modifiée lors de la suppression/modification de la clef primaire.
   1. Création de
   2. Que se passe-t-il lors de la modification ? Pourquoi ?
   3. Que se passe-t-il lors de la suppression ? Pourquoi ?
7. Recommencez les opérations précédentes, cette fois de sorte à ce que la clef étrangères soit mise à lors de la suppression de la clef primaire, et modifiée lors de la modification de la clef primaire.
   1. Création de
   2. Que se passe-t-il lors de la suppression ? Pourquoi ?

Mesurer la performance des index

⚠ Pour des raisons de performances, vous devrez utiliser une base de données "in memory". Pour cela, lancez une nouvelle instance de SQLite sans préciser ni ouvrir de fichiers.

Nous allons désormais voir à quel point les index sont vitaux lors de requêtes SQL sur de très larges tables. Pour cela nous suivrons le protocole suivant :

• créer 3 tables () :
  • table : sans contraintes supplémentaires.
  • table : .
  • table : .
• remplir ces tables de manière identique, avec = entrées unique.
• pour chaque, mesurer la durée de recherche de l'entrée où .

Création et remplissage des tables

Dans un premier temps, nous créons une table contenant nos entrées :

💡 On ne vous demandera pas de comprendre le fonctionnement de .

1. Créez les tables et que vous remplirez ensuite à partir de .

Mesurer la durée d'exécution d'une requête

1. Activez la mesure de la durée d'exécution des requêtes via .
2. Activez l'explication des requêtes via .
   • : lis les lignes de la table unes à unes ;
   • : recherche des entrées dans la table via un index,
3. Sur , , et , mesurez la durée d'exécution de la requête suivante :
4. Comment expliquez-vous la différence de résultats entre , et / ?
5. Exécutez sur et , qu'observez-vous ?
6. Exécutez la requête suivante sur et :
7. Comment pourriez-vous expliquer la différence sur les temps d'exécution ?

💡 Préfixer une requête SQL par permet de l'expliquer sans l'exécuter.

Créer un INDEX

Comme nous l'avons vu en CM, les SGBD ajoutent un index sur les contraintes . Cela s'explique par le fait qu'à chaque insertions, le SGBD doit vérifier la contrainte, et donc rechercher si la valeur existe déjà dans la(les) colonne(s).

💡 La commande permet d'afficher la liste des index de la table .

1. Vérifiez que possède bien un index, et que n'en possède pas.
2. Créez un index sur via .
3. Listez les index de , puis exécutez la requête précédente.
4. Qu'observez-vous quant à la vitesse d'exécution ?
5. Créez une table .
6. Remplissez cette table via la requête SQL suivante (insère duplicats) :
7. Mesurez la vitesse d'execution de la requête sur cette table :
8. Créez un index sur cette table.
9. Mesurez à nouveau la vitesse d'execution, qu'observez-vous ?

⚠ Les index ont un coût lors des //, il ne faut pas en abuser.

💡 En réalité, il est rare de devoir créer des index manuellement.