Bases de données et SQL : relier, interroger, modifier sans erreur

Un atelier a un identifiant, un titre, une matière et une durée. Dans le modèle relationnel, chaque atelier est un n-uplet ; chaque propriété nommée est un attribut. Une relation est un ensemble de n-uplets décrits par les mêmes attributs. On la représente par une table, mais la position d’une ligne ne constitue pas son identité. L’atelier 1 reste le même lorsque l’affichage est trié autrement.

Le schéma Atelier(id, titre, matiere, duree) décrit la structure et ses règles. L’instance est le contenu à un instant donné, par exemple Graphes et SQL. Ajouter Logique change cette instance, pas le schéma. Ajouter un attribut salle modifie le schéma. Pour chaque attribut, indique aussi un domaine : ici un identifiant entier de 1 à 9 999, un titre non vide, une matière parmi NSI, HLP et MATHS, une durée entière de 15 à 240 minutes.

Une déclaration de type n’exprime pas toutes ces règles. NOT NULL impose une valeur et CHECK impose une condition. Le script utilise INT NOT NULL PRIMARY KEY pour l’identifiant fourni par le programme. Avec SQLite, l’affinité entière peut convertir une entrée numérique ; typeof(duree) = 'integer' vérifie donc le type stocké, pas le type Python d’origine. La durée 15,5 doit être refusée, tandis que 15 et 240 sont autorisées.

NULL marque une valeur absente ou inconnue : ce n’est ni zéro ni le texte vide. Si une durée est facultative dans un autre schéma, la recherche des valeurs absentes utilise IS NULL, pas = NULL. Une comparaison ordinaire avec NULL ne donne pas vrai. Le transfert de cette étape rend cette différence observable, puis distingue le nombre de lignes du nombre de durées renseignées.

Les scripts se copient dans Python et créent une base SQLite en mémoire : tu peux recommencer avec les mêmes données fictives. La fonction connecter choisit explicitement le mode permettant BEGIN, COMMIT et ROLLBACK. Commence par lire les commandes SQL à l’intérieur des chaînes ; Python sert à installer les exemples et à comparer les résultats. Une absence d’erreur prouve seulement que l’instruction a été acceptée : lis aussi l’état obtenu.

Le titre d’un atelier peut changer ou être partagé par deux ateliers. On choisit donc id pour identifier chaque atelier sans ambiguïté. Une clé primaire peut comporter plusieurs attributs : dans Inscription(atelier_id, groupe_code), le couple identifie une inscription. Chaque atelier peut accueillir plusieurs groupes et chaque groupe plusieurs ateliers ; rendre atelier_id seul unique interdirait à tort le deuxième groupe.

Une clé étrangère indique à quelle clé d’une autre table se réfère une valeur. Inscription.atelier_id référence Atelier.id ; Inscription.groupe_code référence Groupe.code. Une valeur peut donc apparaître plusieurs fois dans une colonne de clé étrangère. Pour qu’une inscription désigne réellement un atelier et un groupe, on impose aussi NOT NULL sur les deux colonnes. Une référence absente n’est pas un identifiant inconnu.

Le contrôle doit être effectivement activé. Chaque connexion SQLite du laboratoire exécute PRAGMA foreign_keys = ON, puis vérifie la réponse avant toute transaction. Le schéma ajoute explicitement NOT NULL à la clé composée : certaines tables SQLite ordinaires acceptent sinon des NULL dans une telle clé primaire. La théorie dit « identifiant unique et renseigné » ; le test doit vérifier que l’implantation respecte bien ce contrat.

Pourquoi séparer les tables ? Imagine une table aplatie contenant le titre et la durée de Graphes une fois pour G-A et une autre pour G-B. Modifier une seule copie crée deux durées contradictoires. Supprimer la dernière inscription peut faire perdre la description de l’atelier. Enregistrer un atelier avant sa première inscription devient difficile si le groupe est obligatoire. Ce sont des anomalies de modification, de suppression et d’insertion.

La décomposition conserve la description de l’atelier une fois dans Atelier, le groupe une fois dans Groupe et leurs associations dans Inscription. Dans le schéma proposé, supprimer un atelier encore référencé est refusé : aucune suppression en cascade n’est déclarée. Si l’on veut retirer définitivement l’atelier, on doit d’abord décider du sort des inscriptions et traiter l’ensemble dans une transaction. Le SGBD fait respecter une politique ; il ne choisit pas à ta place ce que signifie supprimer.

SELECT titre, duree FROM Atelier WHERE duree >= 90 ORDER BY duree DESC, titre ASC, id ASC décrit le résultat voulu. Lis d’abord la source après FROM, puis les lignes conservées par WHERE, les colonnes demandées par SELECT et enfin l’ordre. C’est une méthode de raisonnement, pas la promesse que le moteur exécutera matériellement ces étapes dans cet ordre.

Sur les quatre ateliers du laboratoire, le filtre conserve Graphes et Argumenter, tous deux de 90 minutes. Le tri décroissant des durées ne les départage pas ; le titre croissant place Argumenter avant Graphes. Le dernier critère id rend l’ordre déterministe si deux ateliers ont aussi le même titre. Sans ORDER BY, ne déduis aucun ordre contractuel de celui observé dans un essai.

WHERE matiere = 'NSI' AND duree >= 60 exige les deux conditions : Graphes et SQL passent, Logique ne passe pas. Avec OR, une ligne satisfaisant une seule condition suffit. Les parenthèses rendent les combinaisons explicites. SELECT matiere peut renvoyer plusieurs fois NSI ; SELECT DISTINCT matiere retire les lignes de résultat répétées. Une projection SQL peut donc contenir des doublons alors qu’une relation du modèle mathématique est un ensemble.

Un agrégat résume les lignes sélectionnées. Ici COUNT(*) vaut 4, MIN(duree) vaut 45, MAX(duree) vaut 90, SUM(duree) vaut 285 et AVG(duree) vaut 71,25. Avec un filtre qui ne garde aucune ligne, COUNT(*) vaut zéro ; ces quatre autres agrégats valent NULL. Si des durées sont absentes, COUNT(duree) et AVG(duree) ne les comptent pas comme des zéros.

Le seuil variable est lié au paramètre ? par execute(requete, (seuil,)). La virgule fabrique un tuple d’une valeur. La fonction refuse un seuil qui n’est pas un entier Python compris entre 0 et 240, y compris un booléen. Pour tester le résultat, filtre et trie séparément une liste Python : ce calcul indépendant est plus utile que recopier dans le test la même requête erronée.

Une jointure combine des lignes de plusieurs tables selon une condition. FROM Inscription AS i JOIN Atelier AS a ON i.atelier_id = a.id associe chaque inscription à l’atelier portant l’identifiant référencé. Les alias i et a raccourcissent l’écriture ; ils ne créent pas de nouvelles tables permanentes. Qualifier les attributs évite de confondre des colonnes qui portent le même nom.

Le schéma visuel part de deux ateliers et de trois inscriptions. Pour l’inscription (1, 'G-B'), seule la ligne d’Atelier dont id vaut 1 convient : elle apporte le titre Graphes. Chaque inscription possède ici exactement une référence non nulle vers une clé primaire existante. Cette jointure produit donc exactement trois lignes. Cette conclusion dépend des contraintes : toute jointure ne conserve pas systématiquement le nombre de lignes.

Sans condition de rapprochement, les deux ateliers peuvent être associés à chacune des trois inscriptions : le produit cartésien comporte six couples. SQL peut accepter cette instruction et fournir un résultat faux pour la question posée. Inversement, joindre sur le titre est fragile : deux ateliers distincts peuvent s’appeler Graphes. Une égalité de textes n’est pas une preuve d’identité.

SELECT i.groupe_code, a.titre choisit les colonnes après rapprochement. G-A apparaît deux fois puisqu’il est inscrit à deux ateliers. Ajouter DISTINCT peut masquer une multiplication indésirable, mais ne répare pas une mauvaise condition ON ; cela peut aussi supprimer des lignes légitimement identiques après projection. Prévois d’abord le nombre de couples, puis les valeurs et l’ordre demandés.

Pour interroger trois tables, ajoute une condition par lien utile : Inscription vers Atelier et vers Groupe. Le transfert affiche la matière avec le titre pour un groupe donné, puis applique un filtre de durée. À chaque étape, relie les attributs du schéma avant d’écrire la requête. Les petites tables servent à tout calculer à la main ; le projet reprend la méthode sur deux cents ateliers fictifs.

INSERT INTO ajoute des lignes, UPDATE ... SET change des valeurs et DELETE FROM retire des lignes. Pour modifier un seul atelier, cible sa clé dans WHERE : un titre n’est pas nécessairement unique. Sans WHERE, UPDATE ou DELETE vise toutes les lignes de la table. Avant la modification, formule la sélection attendue ; après, contrôle les lignes touchées et celles qui devaient rester intactes.

Une opération métier peut nécessiter plusieurs instructions. Imaginons deux réserves fictives de minutes pour des ateliers : A contient 100 minutes, B en contient 20. Transférer 30 minutes doit donner A = 70 et B = 50, sans changer le total. Une transaction délimite l’ensemble avec BEGIN ; COMMIT valide les modifications et ROLLBACK les annule. Le tout ou rien concerne toutes les instructions de cette transaction.

Un UPDATE sans ligne correspondante n’est pas forcément une erreur SQL. Si la source Z n’existe pas, débiter Z ne change rien : créditer B ensuite créerait pourtant des minutes. Le programme doit donc contrôler rowcount après chaque UPDATE. Le contrat de transferer exige des codes distincts, une quantité entière de 1 à 240 et aucune transaction déjà ouverte ; une demande mal formée lève ValueError sans modifier la base.

Une demande bien formée renvoie True si les deux modifications réussissent, False si un code manque, si A est insuffisant ou si B dépasserait sa capacité de 240 minutes. En cas de refus, l’état initial est restauré. Essaie surtout un échec de la seconde instruction : A = 100, B = 230 et un transfert de 20. A passe provisoirement à 80 ; B ne peut pas passer à 250 ; ROLLBACK remet A à 100 et B reste à 230.

Ne suppose pas qu’une erreur SQL annule toujours les instructions précédentes : ce comportement dépend de l’erreur et de la politique du moteur. La correction annule explicitement la transaction qu’elle a ouverte, puis propage les autres erreurs SQLite. Elle refuse d’intervenir dans une transaction déjà ouverte pour ne pas valider ou annuler le travail de l’appelant. Ces laboratoires isolés mettent en évidence l’atomicité ; la gestion de plusieurs connexions est une autre propriété du SGBD.

Le système de gestion de base de données, ou SGBD, reçoit les requêtes et gère leur exécution. La persistance permet de retrouver des données enregistrées après l’arrêt de l’application. La gestion des accès concurrents coordonne plusieurs utilisateurs ; l’optimisation cherche un traitement efficace ; les contrôles d’accès limitent ce que chaque utilisateur peut lire ou modifier. Ces services complètent les contraintes qui maintiennent la cohérence des tables.

SQL décrit les données attendues sans imposer ici un algorithme de parcours. Le moteur peut choisir un plan d’exécution et utiliser des index. Une requête rapide sur quatre lignes ne démontre donc pas sa performance à grande échelle. De même, une seule connexion à une base en mémoire permet de travailler les requêtes et l’atomicité, tandis qu’un test de persistance doit fermer puis rouvrir un stockage conservé.

Une donnée saisie ne doit pas devenir un morceau de syntaxe SQL. Si un programme concatène un code dans WHERE code = '...', une apostrophe peut fermer la chaîne et changer la condition. Avec WHERE code = ?, le texte est lié comme une valeur. Les essais restent dans une base éphémère de ressources fictives ; ils montrent à la fois une recherche contenant une apostrophe et une tentative qui ne doit pas élargir le résultat.

Paramétrer ne décide pas du droit d’accès. Une fonction qui recherche seulement le code R2 peut retourner un brouillon si elle ne vérifie pas son statut. La fonction chercher_public ajoute donc une condition fixe statut = 'publie'. L’application qui gère des comptes doit en plus vérifier l’identité et les autorisations côté serveur ; un rôle fourni librement par le navigateur ne constitue pas une autorisation.

Les paramètres servent aux valeurs, pas aux noms de colonnes ni aux mots clés. Pour proposer un tri, le transfert choisit une clause complète dans un dictionnaire fermé défini par le programme. Il n’interpole jamais directement le choix fourni. Associe chaque protection à sa preuve : valeur liée littéralement, brouillon absent du résultat public, choix de tri inconnu refusé et données inchangées après une lecture.