Exercice 2 · Bac Numérique et Sciences Informatiques Centres étrangers (Afrique) 2023 (jour 2)

EXERCICE 2 (4 points)

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

L'énoncé de cet exercice utilise les mots-clés du langage SQL suivants: SELECT, FROM, WHERE, JOIN...ON, UPDATE...SET, INSERT INTO...VALUES..., COUNT, ORDER BY.

La clause ORDER BY suivie d'un attribut permet de trier les résultats par ordre croissant de l'attribut.

SELECT COUNT(*) renvoie le nombre de lignes d'une requête.

Un zoo souhaite pouvoir suivre ses animaux et ses enclos. Tous les représentants d'une espèce sont réunis dans un même enclos. Plusieurs espèces, si elles peuvent cohabiter ensemble, pourront partager le même enclos.

Il crée une base de données utilisant le langage SQL avec une relation (ou table) animal qui recense chaque animal du zoo. Vous trouverez un extrait de cette relation ci-dessous (les unités des attributs age, taille et poids sont respectivement ans, m et kg):

animal

id_animal

nom

age

taille

poids

nom_espece

145 · Romy · 18 · 2.3 · 130 · tigre du Bengale

52 · Boris · 30 · 1.10 · 48 · bonobo

... ·... ·... ·... ·... ·...

225 · Hervé · 10 · 2.4 · 130 · lama

404 · Moris · 6 · 1.70 · 100 · panda

678 · Léon · 4 · 0.30 · 1 · varan

Il crée la relation enclos dont vous trouverez un extrait ci-dessous (l'unité de l'attribut surface est m²):

enclos

num_enclos

ecosysteme

surface

struct

date_entretien

40 · banquise · 50 · bassin · 04/12/2024

18 · forêt tropicale · 200 · vitré · 05/12/2024

... ·... ·... ·... ·...

24 · savane · 300 · clôture · 04/12/2024

68 · désert · 2 · vivarium · 05/12/2024

Il crée également la relation espece dont vous trouverez un extrait ci-dessous:

espece

nom_espece

classe

alimentation

num_enclos

impala · mammifères · herbivore · 15

ara de Buffon · oiseaux · granivore · 77

... ·... ·... ·...

tigre du Bengale · mammifères · carnivore · 18

caïman · reptiles · carnivore · 45

manchot empereur · oiseaux · carnivore · 40

lama · mammifères · herbivore · 13

1. Cette question porte sur la lecture et l'écriture de requêtes SQL simples.

a.

Écrire le résultat de la requête ci-dessous:

SELECT age, taille, poids FROM animal WHERE nom = 'Moris';

b.

Écrire une requête qui permet d'obtenir le nom et l'âge de tous les animaux de l'espèce bonobo, triés du plus jeune au plus vieux.

2. Cette question porte sur le schéma relationnel.

a.

Citer, en justifiant, la clé primaire et la clé étrangère de la relation espece.

b.

Donner le modèle relationnel de la base de données du zoo. On soulignera les clés primaires et on fera précéder les clés étrangères d'un #.

3. Cette question porte sur les modifications d'une table.

L'espèce ornithorynque a été entrée dans la base comme étant de la classe des oiseaux alors qu'il s'agit d'un mammifère.

a.

Écrire une requête qui corrige cette erreur dans la table espece.

Le couple de lamas du zoo vient de donner naissance au petit lama nommé "Serge" qui mesure 80 cm et pèse 30 kg.

b.

Écrire une requête qui permet d'enregistrer ce nouveau venu au zoo dans la base de données, sachant que les clés primaires de 1 à 178 sont déjà utilisées.

4. Cette question porte sur la jointure entre deux tables

a.

Recopier et compléter les [...] afin de recenser le nom et l'espèce de tous les animaux carnivores vivant en vivarium dans le zoo:

SELECT [...]

FROM animal

JOIN espece ON [...]

JOIN enclos ON [...]

WHERE enclos.struct = 'vivarium' and [...];

On souhaite connaître le nombre d'animaux dans le zoo qui font partie de la classe des oiseaux.

b.

Écrire la requête qui permet de compter le nombre d'oiseaux dans tout le zoo.