Exercice 4 · Bac Numérique et Sciences Informatiques Sujets zéro et spécimens sujet 2021 (sujet 1)

Exercice 4

Cet exercice porte sur les bases de données relationnelles et le langage SQL.

L'énoncé de cet exercice utilise les mots du langage SQL suivant:

SELECT, FROM, WHERE, JOIN, INSERT INTO, VALUES, COUNT, ORDER BY.

Dans un lycée imaginaire, les données relatives aux élèves de secondes sont regroupées dans un fichier nommé seconde_lyc.csv. Un extrait de son contenu est représenté figure 1.

num_eleve

nom

prenom

datenaissance

langue1

langue2

option

classe

133310FE · ACHIR · Mussa · 01/01/2005 · anglais · espagnol · · 2A

156929JJ · ALTMEYER · Yohan · 05/05/2005 · allemand · anglais · théâtre · 2D

500633KH · BELEY · Thibaut · 05/05/2005 · anglais · espagnol · · 2A

911887GA · BELEY · Marie · 05/05/2005 · anglais · espagnol · · 2A

905089JJ · BELEY · Manon · 10/01/2005 · anglais · allemand · · 2E

488697GA · CAILLE · Marie · 30/03/2004 · italien · anglais · · 2D

193514FB · CHARPENTIER · Jules · 26/12/2005 · espagnol · anglais · · 2C

321188FA · CLAUDEL · Benjamin · 09/09/2005 · espagnol · anglais · · 2E

081282GF · EISEN · Carla · 23/06/2004 · anglais · allemand · · 2A

026946KB · EL AYAR · Amir · 11/09/2005 · anglais · arabe · cinéma · 2D

108308KG · GEHIN · Arthur · 26/02/2005 · allemand · anglais · · 2D

057934BK · GROSJEAN · Alexandre · 09/11/2005 · anglais · espagnol · · 2C

571113KE · HENRY · Paul · 12/03/2005 · allemand · anglais · · 2E

488820DE · JACQUEY · Marc · 13/11/2005 · anglais · italien · · 2D

024810CE · JULIANO · Alberto · 21/04/2005 · anglais · espagnol · · 2C

249992EJ · KLEIBER · Gusti · 20/02/2005 · anglais · espagnol · cinéma · 2E

492698AF · LACOUR · Julie · 06/04/2005 · italien · anglais · · 2D

026454FA · LARBI · Nourdine · 14/07/2005 · espagnol · anglais · · 2C

309341GD · LEFZA · Yasmina · 26/11/2005 · espagnol · anglais · · 2E

076725HD · MARTIN · Victor · 13/03/2005 · anglais · espagnol · · 2A

815183CB · NGUYEN · Ngong · 16/03/2005 · anglais · espagnol · · 2D

094002FC · PELTIER · Romane · 14/06/2005 · allemand · anglais · · 2D

321262HD · RENAULT · Zoé · 06/08/2005 · anglais · espagnol · latin · 2E

075421AK · ROTH · Ursule · 03/01/2005 · anglais · allemand · · 2A

121001CK · SERHANI · Sabrina · 01/09/2005 · italien · anglais · · 2D

538965DJ · TUDJANE · Yourik · 31/01/2005 · espagnol · anglais · · 2D

389873GC · VIALET · Priscille · 28/02/2005 · espagnol · anglais · · 2C

980306CA · WADE · Marcelin · 03/05/2005 · allemand · anglais · · 2E

807158DH · WENGER · Alexandre · 20/08/2005 · allemand · anglais · · 2A

666702FA · YAMAN · Elamine · 23/04/2005 · anglais · arabe · · 2D

Extrait du fichier seconde_lyc.csv

Pour les besoins de l'organisation du lycée, le chef d'établissement exploite la base de données par des requêtes en langage SQL. Il a pour cela créé une table (ou relation) SQL dénommée seconde dans son système de gestion de bases de données dont la structure est la suivante:

seconde

num_eleve (clef primaire)

langue1

langue2

option

classe

L'attribut num_eleve est un entier, les autres sont des chaines de caractère (le type CHAR).

Question 1

1.

Dans le modèle relationnel, quel est l'intérêt de l'attribut num_eleve.

2.

Écrire une requête SQL d'insertion permettant d'enregistrer l'élève ACHIR Mussa dans la table seconde. Les informations relatives à cet élève sont données dans la ligne 1 du fichier seconde_lyc.csv.

3.

Lors de l'insertion de l'élève ALTMEYER Yohan (ligne 2 du fichier seconde_lyc.csv), une erreur de saisie a été commise sur la première langue, qui devrait être allemand. Écrire une requête SQL de mise à jour corrigeant les données de cet élève.

Question 2

On suppose maintenant que la table seconde contient les informations issues de la figure 1 (ni plus, ni moins, même si la figure 1 n'est qu'un extrait du fichier seconde_lyc.csv).

1.

Quel est le résultat de la requête:

SELECT num_eleve FROM seconde;

2.

On rappelle qu'en SQL, la fonction d'agrégation COUNT() permet de compter le nombre d'enregistrements dans une table. Quel est le résultat de la requête:

SELECT COUNT(num_eleve) FROM seconde;

3.

Écrire la requête permettant de connaître le nombre d'élèves qui font allemand en langue1 ou langue2.

Question 3

Le chef d'établissement souhaite faire évoluer la structure de sa base de données. Pour ce faire, il créé une nouvelle table eleve dont la structure est la suivante:

eleve

num_eleve (clef primaire, clef étrangère de la table seconde)

nom

prenom

datenaissance

Là encore, l'attribut num_eleve est un entier, les autres sont des chaines de caractère (le type CHAR).

1.

Expliquer ce qu'apporte l'information clef étrangère pour l'attribut num_eleve de cette table en termes d'intégrité et de cohérence.

2.

On suppose la table eleve correctement créée et complétée. Le chef d'établissement aimerait lister les élèves (nom, prénom, date de naissance) de la classe 2A. Écrire la commande qui permet d'établir cette liste à l'aide d'une jointure entre eleve et seconde.

Question 4

Proposer la structure d'une table coordonnees dans laquelle on pourra indiquer, pour chaque élève, son adresse, son code postal, sa ville, son adresse mail. Préciser la clef primaire et/ou la clé étrangère en vue de la mise en relation avec les autres tables.