Aller au contenu

🏫 Base de donnée d'un lycée⚓︎

Attention

Le programme de NSI de Terminale demande de maîtriser les requêtes d'insertion et de suppression.

La création de table présentée ci-dessous est donc hors-programme.

On souhaite créer une base de donnée permettant de stocker les informations décrivant les élèves d'un lycée ainsi que leur classe.

Un élève est décrit par :

  • un identifiant unique (un entier) ;
  • son nom ;
  • son prénom ;
  • sa classe.

Une classe est décrite par :

  • un identifiant unique (un entier) ;
  • son nom (par exemple « Terminale 2 ») ;
  • son niveau, nombre entier (2 pour les secondes, 1 pour les premières, 0 pour les terminales) ;
  • sa filière (« Générale », « STI2D », etc...). Les classes de secondes seront dans la filière « Seconde ».

Afin d'éviter les répétitions dans les tables, les informations sur les filières seront regroupées dans une table séparée.

La structure de la base est donc la suivante :

  • Table filieres :

    • id : identifiant (nombre entier, clé primaire) ;
    • filiere : description de la filière (texte).

  • Table classes :

    • id : identifiant (nombre entier, clé primaire) ;
    • nom : nom de la classe (texte) ;
    • niveau : niveau de la classe (nombre entier) ;
    • filiere: filière de la classe (entier, clé étrangère faisant référence à filieres.id).

  • Table eleves :

    • id : identifiant (nombre entier, clé primaire) ;
    • nom : nom de l'élève (texte) ;
    • prenom : prénom de l'élève (texte) ;
    • classe: classe de l'élève (entier, clé étrangère faisant référence à classes.id).

Création des tables (hors du programme de NSI)⚓︎

L'instruction SQL permettant de créer la table filieres est :









    

  1. 

    Exécuter cette cellule puis afficher ci-dessous le contenu de la table filieres.
    

    

    Votre réponse
    


    


    



    

    

    


    

    
Réponse

    🗂️ Requête SQL
    SELECT * 
FROM filieres;

    

    La requête s'est exécutée normalement mais on obtient aucune sortie car la table est vide ! Elle sera peuplée plus bas.
    

    

    2. 

  2. 

    Créer la table classes.
    

    

    Déclarer une clé étrangère
    

    La déclaration d'une clé étrangère se fait ainsi : FOREIGN KEY (attribut) REFERENCES table_etrangere (attribut).
    

    Dans le cas présent, on rajoutera donc : FOREIGN KEY (filiere) REFERENCES filieres (id).
    

    

    

    Votre réponse
    


    


    



    

    

    


    

    
Réponse

    🗂️ Requête SQL
    CREATE TABLE classes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nom TEXT,
    niveau INTEGER,
    filiere INTEGER,
    FOREIGN KEY (filiere) REFERENCES filieres (id)
);

    

    

    3. 

  3. 

    Créer la table eleves.
    

    

    Votre réponse
    


    


    



    

    

    


    

    
Réponse

    🗂️ Requête SQL
    CREATE TABLE eleves (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nom TEXT,
    prenom TEXT,
    classe INTEGER,
    FOREIGN KEY (classe) REFERENCES classes (id)
);

    

    

    4. 



Peuplement des tables (dans le programme de NSI)⚓︎


Les tables étant créées, il faut désormais les peupler. Nous utiliserons la structure suivante :


🗂️ Requête SQL
INSERT INTO table (attribut_1, attribut_2, ...)
VALUES (valeur_1, valeur_2, ...), (valeur_1, valeur_2, ...), ...



Il ne faut bien entendu pas recopier les points de suspension !


Par exemple pour insérer les filières :

















    

  1. 

    Exécuter cette cellule puis afficher ci-dessous le contenu de la table filieres.
    

    

    Votre réponse
    


    


    



    

    

    


    

    
Réponse

    🗂️ Requête SQL
    SELECT * 
FROM filieres;

    

    Cette fois-ci on a bien un résultat.
    

    

    2. 

  2. 

    On se propose de créer quatre classes :
    

      

    • la Seconde 1,
      • 

    • la Première 2 (de filière « STI2D »),
      • 

    • la Terminale 3 (de filière « Générale »),
      • 

    • la Terminale 4 (de filière « Générale »).
      • 

    

    Une classe est caractérisée par les attributs id, nom, niveau et filiere. 
    

    L'id étant la clé primaire et auto-incrémentée, elle est gérée automatiquement par le système de gestion de la base de données. Il n'est donc pas nécessaire d'insérer les id.
    

    L'attribut filiere est quant à lui une clé étrangère. On ne peut donc pas utiliser la valeur 'STI2D' pour cet attribut lors de l'insertion de la Première 2. Il faut utiliser la valeur correspondant à la clé étrangère de référence dans la table filieres. 
    

    Si par exemple on constate que dans la table filieres, STI2D est associé à l'id 4 on fera : INSERT INTO classes (nom, niveau, filiere) VALUES ('Première 2', 1, 4). On a fourni 4 comme valeur de l'attribut filiere.
    

    Insérer les valeurs dans la table classes. 
    

    

    Votre réponse
    


    


    



    

    

    


    

    
Réponse

    On considère que la filière « Seconde » a pour id le 1, 2 pour « Générale » et 4 pour « STI2D ».
    

    🗂️ Requête SQL
    INSERT INTO classes (nom, niveau, filiere)
VALUES ("Seconde 1", 2, 1), ("Première 2", 1, 4), ("Terminale 3", 0, 2), ("Terminale 4", 0, 2);

    

    

    3. 

  3. 

    Insérer les élèves suivants dans la base :
    

    


    
Nom
Prénom
Classe

    
    


    
Faux
Marc
Terminale 3

    

    
Bidon
Paul
Première 2

    

    
Fake
Marie
Seconde 1

    

    
False
Isabelle
Première 2

    

    
Nope
Arnaud
Terminale 4

    
    

    

    

    Votre réponse
    


    


    



    

    

    


    

    
Réponse

    On considère que la classe « Seconde 1" a pour id le 1, 2 pour « Première 2", 3 pour Terminale 3 et 4 pour Terminale 4.
    

    🗂️ Requête SQL
    INSERT INTO eleves (nom, prenom, classe)
VALUES ("Faux", "Marc", 3), ("Bidon", "Paul", 2), ("Fake", "Marie", 1), ("False", "Isabelle", 2), ("Nope", "Arnaud", 4);

    

    

    4. 



Mise à jour de valeurs (dans le programme de NSI)⚓︎


Le proviseur du lycée souhaite changer la dénomination des classes : il souhaite utiliser des lettres à la place des numéros.


Il faut donc mettre à jour les données. La structure d'une instruction SQL de mise à jour est : UPDATE table SET attribut = nouvelle_valeur WHERE condition.


Par exemple, pour renommer la « Seconde 1 » en « Seconde A » :

















    

  1. 

    Mettre à jour les autres noms de classes : « Première 2 » devient « Première B », « Terminale 3 » devient « Terminale C ».
    

    

    Astuce
    

    Il est possible de saisir plusieurs instructions à la suite en les séparant avec un ;
    

    

    

    Votre réponse
    


    


    



    

    

    


    

    
Réponse

    🗂️ Requête SQL
    UPDATE classes SET nom = "Première B" WHERE nom = "Première 2";
UPDATE classes SET nom = "Terminale C" WHERE nom = "Terminale 3";

    

    

    2. 



Suppression de valeurs (dans le programme de NSI)⚓︎


La structure générale d'une requête de suppression de valeurs dans une table est :


🗂️ Requête SQL
DELETE FROM table
WHERE condition;



Imaginons par exemple que nous Paul Bidon (élève de Première 2) quitte le lycée. Il faut le supprimer de la base de données.


Pour ce faire, on exécute la requête ci-dessous (cette requête renvoie une erreur si l'élève n'a pas été inséré précédemment):

















    

  1. 

    Les élèves et le proviseur du lycée sont satisfait : tous les élèves de Terminale ont eu leur bac en fin d'année.
    

    Courant août, le proviseur doit donc supprimer tous les élèves de Terminale de la base de données.
    

    Saisir la requête permettant de supprimer les élèves de Terminale.
    

    

    Votre réponse
    


    


    



    

    

    


    

    
Réponse

    🗂️ Requête SQL
    DELETE FROM eleves
WHERE classe = 3 OR classe = 4;

    

    

    2.