Exercice 3 - Partie B · Bac Numérique et Sciences Informatiques Centres étrangers (Afrique) 2024 (jour 2)

Cet exercice porte sur le codage binaire, les bases de données relationnelles et les requêtes SQL.

Cet exercice est composé de deux parties peu dépendantes entre elles.

Lorsqu'il y a un accident, les pompiers essaient d'intervenir sur les lieux le plus rapidement possible avec les équipes et le matériel adéquats. On s'intéresse à l'étude d'un système informatique simplifié permettant de répondre à certaines de leurs problématiques.

Chaque pompier possède des aptitudes opérationnelles qui lui permettent de tenir un rôle. Lors du départ d'un véhicule (on parle d'agrès), il faut a minima un conducteur et un chef d'agrès.

Pour simplifier, on considère que:

un Véhicule Tout Usage (VTU) ne requiert que le duo conducteur et chef d'agrès, donc deux pompiers;

un Véhicule de Secours et d'Assistance aux Victimes (VSAV) requiert, en plus du duo conducteur et chef d'agrès, un équipier, donc trois pompiers;

un Fourgon Pompe Tonne (FPT) requiert, en plus du duo conducteur et chef d'agrès, un chef d'équipe et un équipier, donc quatre pompiers.

On souhaite entrer dans une base de données l'ensemble de ces informations afin de pouvoir conserver un historique des interventions.

Partie B — Base de données relationnelle

On considère maintenant la base de données relationnelle suivante:

La table personnel est composée:

du matricule unique du pompier;

de son nom;

de sa qualification (selon le codage vu avant);

d'un attribut qui précise s'il est actif (1) ou inactif (0).

La table agres est composée:

de son identifiant;

du jour où l'agrès se tient prêt à intervenir;

du type de véhicule;

de l'identifiant du chef d'agrès;

de l'identifiant du conducteur;

de l'identifiant du chef d'équipe si le véhicule le nécessite, sinon le champ est à NULL;

de l'identifiant de l'équipier si le véhicule le nécessite, sinon le champ est à NULL.

La table moyen est composée:

de l'identifiant de l'agrès appelé sur intervention;

de l'identifiant de l'intervention.

La table intervention est composée:

de son identifiant;

du jour de début d'intervention (sauf pour les longues interventions où il correspond au jour de l'agrès);

de l'heure de début d'intervention.

On rappelle que COUNT(*) permet de compter le nombre de lignes extraites lors d'une requête. Par exemple, pour afficher le nombre de personnes dans la table personnel, on exécute la requête:

SELECT COUNT(*) FROM personnel;

DISTINCT permet de retirer les doublons des réponses. Par exemple, pour afficher tous les noms distincts de la table personnel, on exécute la requête:

SELECT DISTINCT(nom) FROM personnel;

On considère l'extrait de la base de données ci-dessous:

6.

Expliquer la différence entre une clé primaire et une clé étrangère.

7.

Expliquer pourquoi la requête suivante génère une erreur pour l'extrait de données.

INSERT INTO moyen (idagres, idinter) VALUES (1,5);

8.

Proposer une requête SQL qui met à jour l'heure de l'intervention du « 15 février 2024 de 01 heure 44 minutes et 06 secondes » à « 10 heures 44 minutes et 06 secondes ».

9.

Préciser le résultat de la requête suivante pour l'extrait de données.

SELECT nom FROM personnel

WHERE actif = 0;

10.

Proposer une requête SQL qui permet d'afficher les noms des personnels conducteurs actifs. On notera qu'un conducteur possède un attribut qualif supérieur ou égal à 16.

11.

Écrire l'affichage obtenu après exécution des deux requêtes ci-dessous sur l'extrait de la base de données. Expliquer ce que chacune des requêtes affiche en général.

Requête A

SELECT COUNT(*) FROM agres

WHERE jour = '2024-03-27';

Requête B

SELECT COUNT(*) FROM moyen AS m

INNER JOIN agres AS a ON a.id = m.idagres

WHERE a.jour = '2024-03-27';

12.

Proposer une requête qui renvoie sans répétition tous les noms des chefs d'agrès assignés à un véhicule le 15 février 2024.

13.

Proposer une requête qui renvoie sans répétition tous les noms des chefs d'agrès engagés en intervention le 11 juin 2024.