Demandez le programme !

Le développement des traitements informatiques nécessite la manipulation de données de plus en plus nombreuses. Leur organisation et leur stockage constituent un enjeu essentiel de performance. Le recours aux bases de données relationnelles est aujourd’hui une solution très répandue. Ces bases de données permettent d’organiser, de stocker, de mettre à jour et d’interroger des données structurées volumineuses utilisées simultanément par différents programmes ou différents utilisateurs. Cela est impossible avec les représentations tabulaires étudiées en classe de première. Des systèmes de gestion de bases de données (SGBD) de très grande taille (de l’ordre du pétaoctet) sont au centre de nombreux dispositifs de collecte, de stockage et de production d’informations. L’accès aux données d’une base de données relationnelle s’effectue grâce à des requêtes d’interrogation et de mise à jour qui peuvent par exemple être rédigées dans le langage SQL (Structured Query Language). Les traitements peuvent conjuguer le recours au langage SQL et à un langage de programmation. Il convient de sensibiliser les élèves à un usage critique et responsable des données.

Les chapitres qui peuvent être mis en relation avec ce chapitre sont :

Langage d'interrogation d'une base de données.

L’accès aux données d’une base de données relationnelle s’effectue grâce à des requêtes d’interrogation et de mise à jour qui peuvent par exemple être rédigées dans le langage SQL (Structured Query Language). Il n'est pas au programme de TNSI de créer une base de données.

Nous utiliserons plusieurs bases de données pour travailler cette partie :

Quelques commandes du langage SQL

Les commandes d'interrogation de la base

Il existe plusieurs types de requêtes pour interroger une table.

La projection

La projection d'une table permet d'obtenir une partie des champs de la table. Elle utilise la commande SELECT.

On sélectionne les colonnes de notre relation.

Relation : Joueur(IdJoueur,nomJoueur,pnomJoueur)

Un exemple :

La sélection

La sélection d'une table permet de choisir une partie des lignes de la table. On utilise le mot clé WHERE suivie du (ou des) critère(s).

Un exemple :

Voici une liste d'opérateurs :
Commande Explications
LIKE Comme. Exemple nomJoueur LIKE 'Terez'
BETWEEN Entre
IN Dans
AND Intersection
OR Union
NOT Négation
<,>, <=,>= Utilisables sur des données numériques
% Symbole qui représente une chaîne de caractères.
_ Symbole qui représente un caractère

Relation : personne(Id,nom,pNom,ville,tel,email)

La jointure

Vous pouvez revisiter le cours sur les tables et les jointures de première : accès direct aux jointures sur les tables (pour rappel)

La jointure permet de mettre en relation plusieurs tables. La jointure utilise les clés primaires et étrangères des tables qu'elle met en relation. Elle utilise les mots clés JOIN ON.

Dans cet exemple, nous allons utiliser une base de données tournoi définie comme ceci :

Avec les tables :

Vous remarquerez qu'il y a des erreurs d'informations concernant les vainqueurs. Nous allons voir un peu plus loin comment modifier ces erreurs.

Schéma relationnel :

Les clés idJoueur1,idJoueur2 et idVainqueur sont des clés étrangères reliées à idJoueur

Je cherche à connaître les identifiants des parties gagnées par Bob LEPONGE (idjoueur=3).

Première requête :

J'aimerais que soit affiché le nom du gagnant et non pas son identifiant. Pour cela il faut que je relie les tables joueur et partie car le nom des joueurs est une information de la table joueur mais l'id du vainqueur est une information de la table partie :

En cas de conflit possible entre les noms des attributs, on peut préfixer les noms par le nom des tables :

Les calculs et fonctions sur un champ ou plusieurs champs

Nous conservons notre base de données tournoi.

Nous vous proposons soit des requêtes, soit des résultats de requêtes.

Pour répondre vous pouvez retourner aux tables ci-dessus.

  1. Donner les résultats de la requête.

  2. Ecrire la requête.

  3. Donner les résultats de la requête.
  4. Donner les résultats de la requête.
  5. Ecrire la requête.

Code de déblocage de la correction :

Les commandes de modification de la base

Mise à jour des données

Il est possible d'insérer, de supprimer ou de mettre à jour des données.

Les syntaxes sont les suivantes :

Commande/mot-clé Explications/syntaxe
INSERT INTO nom table (champ1,champ2, ...) VALUES (valeur1,valeur2, ...) ; Insertion des données. On ajoute un $n$-uplet.

Exemple : INSERT INTO JOUEUR (idJoueur,nomJoueur,pnomJoueur) VALUES (6,'Smatik','Karim')

UPDATE nom_table SET ... WHERE ... Modifier des données.

Exemple : UPDATE partie SET idvainqueur = 1 WHERE idpartie=1

DELETE FROM nom_table WHERE .... Suppression des données. On supprime toute la ligne identifiée par le critère qui suit le mot-clé WHERE.

Exemple : DELETE FROM partie WHERE idpartie=10

Dans notre base de données tournoi, il y a des erreurs de déclaration des vainqueurs :

  1. Proposer une modification de la table pour corriger ces erreurs.
  2. Insérer le nouveau joueur avec un id=7 de nom 'Bros' et de prénom 'Mario'
  3. Vérifier la présence par une requête de votre nouveau joueur
  4. Supprimer ce nouveau joueur

Code de déblocage de la correction :

Tableau synthétique des mots clés

Certaines commande du langage SQL ne font pas des attendues du programme de terminale NSI. En cas de doute, remontez à la section : "demandez le programme !".

Commandes exigibles : SELECT, FROM, WHERE, JOIN, UPDATE, INSERT, DELETE, DISTINCT, ORDER BY, fonctions d'agrégations

Commande/mot-clé Explications/syntaxe
SELECT attribut1,attribut2, ... FROM nom_table Sélection en projection. Exemple : SELECT * FROM joueur
SELECT DISTINCT attribut1,attribut2, ... FROM nom_table Idem commande précédente en évitant de retourner des doublons
JOIN ... ON Jointure pour relier deux ou plusieurs tables. Exemple : SELECT nomJoueur,idpartie FROM joueur JOIN partie ON idjoueur=idvainqueur WHERE idvainqueur=3;
AS Renommer une colonne. SELECT nomJoueur AS Vainqueur FROM Joueur WHERE idVainqueur=3
ORDER BY attribut DESC Trier par ordre croissant.
SUM(attribut) Fonction d'agrégations : additionner les valeurs d'un champ numérique.
AVG(attribut) Fonction d'agrégations : calculer la moyenne des valeurs d'un champ numérique.
MIN(attribut) et MAX(attribut) Fonction d'agrégations : : obtenir la valeur maximale ou minimale d'un champ numérique.
COUNT(Attribut) Fonction d'agrégations : compter des enregistrements
LIKE Comme. Exemple nomJoueur LIKE 'Terez'
BETWEEN Entre
IN Dans
AND Intersection
OR Union
NOT Négation
<,>, <=,>= Utilisables sur des données numériques
% Symbole qui représente une chaîne de caractères.
_ Symbole qui représente un caractère
INSERT INTO nom table (champ1,champ2, ...) VALUES (valeur1,valeur2, ...) ; Insertion des données. On ajoute un $n$-uplet. Exemple : INSERT INTO JOUEUR (idJoueur,nomJoueur,pnomJoueur) VALUES (6,Smatik,Karim)
UPDATE nom_table SET ... WHERE ... Modifier des données. Exemple : UPDATE partie SET idvainqueur = 1 WHERE idpartie=1
DELETE FROM nom_table WHERE .... Suppression des données. On supprime toute la ligne identifiée par le critère qi suit le mot-clé WHERE. Exemple : DELETE FROM partie WHERE idpartie=10

Des accès pour vous entraîner

Il existe quelques bases de données accessibles sur le web pour vous entraîner dans vos requêtes.

Exercices

Dans ce TP, vous utiliserez la base de données sante avec le mode d'utilisation de votre choix : ligne de commande, raspberry, phpMyadmin. Si votre niveau le permet, vous pouvez tester différentes approches.

Voici une liste de requêtes à exécuter. A vous d'écrire la requête en langage SQL et d'indiquer vos (ou une partie) réponses.

  1. Nom des patients habitant la ville de reims
  2. Compter le nombre de médecins dans la base
  3. Recherche des patients n'ayant pas de médecin
  4. Afficher la liste des médecins dans l'ordre alphabétique des prénoms
  5. Trouver les patients qui habitent Reims
  6. Rechercher des patients de Reims nés entre 1970 et 2010
  7. Rechercher des patients nés en 2009, qui habitent Reims. Le résultat est trié dans l'ordre alphabétique.
  8. Nom et prénom des patients suivis par le docteur Vecanror
  9. Rechercher des noms de remèdes permettant de soigner la Grippe
  10. Rechercher les médecins des patients ('CHIRAC','DUPONT','JACOB')
  11. Rechercher des médecins ayant prescrit de l'aspirine

Code de déblocage de la correction :

Voici un simulateur de requêtes proposé par un collègue de la liste de diffusion NSI : Jacques Le Coupanec

Accès direct à la ressource

Pour les élèves intéressés, dans le cadre d'un projet, vous pouvez étudier la source qui permet de créer ce simulateur : accès direct.

La base de données blanchisserie vient de la formation DIU que nous avons suivie de l'université de Reims. Certaines requêtes sont difficiles.

A vous d'utiliser la base de données blanchisserie et de formuler les requêtes suivantes (ce sont les requêtes proposées dans le cadre du DIU) :

Pour vous aider, voici le dictionnaire des données :

Ainsi que le modèle physique des données :

Code de déblocage de la correction :

Un projet à développer.

Reprenez les exercices 4 et 5 et inventez vos propres requêtes. Vous pouvez les soumettre avec la correction à vos enseignants en message privé, dans un seul fichier au format sql.

Si nous récoltons un nombre suffisant de requêtes, nous pourrions organiser une "bataille de requêtes en équipes".

  1. Inventez une requête, testez la, conservez un screen de la solution. Donnez un nombre de points à votre requête (entre 1 et 3 selon la difficulté).
  2. Constituez une équipe (entre 2 et 4) pour tester vos requêtes.
  3. Inventez des règles simples pour un jeu éventuel.
Exercice de type bac en construction !

Dans le cadre de votre entraînement aux épreuves du baccalauréat, il faut vous attendre à un exercice sur ce chapitre sans la possibilité de manipulation informatique.

Il faut donc traiter cet exercice sur votre cahier sans vous donner la possibilité de traitement informatique.

Partie informations

Imaginons une société de surveillance qui possède un parc de véhicules de fonction ainsi qu'un ensemble de talkie-walkie (émetteur-récepteur radio longue portée). Le but est de créer une base de données qui permettent d'associer un véhicule de fonction et un talkie-walkie à un employé de la société.

Voici le schéma relationnel de la base :

Quelques commentaires :

On considère que la structure de la base de données est existante et que vous avez accès à cette base par l'intermédiaire d'un SGBD avec un utilisateur ayant tous les droits sauf celui de suppression.

Voici une liste d'informations :

  1. Véhicule 1 (idVeh=1) immatriculé AB-001-CD
  2. Véhicule 2 (idVeh=2) immatriculé AZ-012-CD
  3. Véhicule 3 (idVeh=3) immatriculé KH-012-CD
  4. talkie_walkie 1 (idTalk=1)
  5. talkie_walkie 2 (idTalk=2)
  6. talkie_walkie 3 (idTalk=3)
  7. talkie_walkie 4 (idTalk=4)
  8. Employé 1 (id=1) TEREZ Paskual
  9. Employé 2 (id=2) MONTAIGNE Juan
  10. Employé 3 (id=3) GERALD Juany
  11. Employé 4 (id=4) LOURDES Toma
  12. Toma est en service avec le véhicule 3 et la talkie-walkie 2 sur le canal 30
  13. Junay est en service avec le véhicule 2 et la talkie-walkie 1 sur le canal 50

Partie Questions
  1. Ecrire la requête qui traite l'information n°2
  2. Ecrire la requête qui traite l'information n°8
  3. Ecrire la requête qui traite l'information n°12

A vous de créer votre base de données (vous pouvez utiliser une base d'exercices déjà traités). Attention, la création d'une base de données n'est pas un attendu du programme.

Un outil pour créer vos diagrammes : dbdiagram

Bibliographie et sitographie

Vu sur "Lumni lycée"

Sur le site Lumni, vous avez des ressources et des vidéos dans le cadre de la "nation apprenante"

Accès à la vidéo

Savoir et Savoir faire

Licence Creative Commons
Les différents auteurs mettent l'ensemble du site à disposition selon les termes de la licence Creative Commons Attribution - Pas d’Utilisation Commerciale - Partage dans les Mêmes Conditions 4.0 International.